[SOLVED] Database-sql: Project 2 PLpgSQL

Project 2 PLpgSQL Due: Fri 20 May 23:59

1. Aims
This project aims to give you practice in

• reading and understanding a moderately large relational schema (MyMyUNSW)
• implementing SQL queries and views to satisfy requests for information
• implementing PLpgSQL functions to aid in satisfying requests for information

  The goal is to build some useful data access operations on the MyMyUNSW database. A theme of this project is dirty data. As I was building the database, using a collection of reports from UNSWs information systems and the database for the academic proposal system (MAPPS), I discovered that there were some inconsistencies in parts of the data (e.g. duplicate entries in the table for UNSW buildings, or students who were mentioned in the student data, but had no enrolment records, and, worse, enrolment records with marks and grades for students who did not exist in the student data). I removed most of these problems as I discovered them, but no doubt missed some. Some of the exercises below aim to uncover such anomalies; please explore the database and let me know if you find other anomalies.

  2. Howtodothisproject:

• read this specification carefully and completely
• familiarise yourself with the database schema (description, SQL schema, summary)
• make a private directory for this project, and put a copy of the proj2.sql template there
• you must use the create statements in proj2.sql when defining your solutions
• look at the expected outputs in the expected_qX tables loaded as part of the check.sql file
• solve each of the problems below, and put your completed solutions into proj2.sql
• check that your solution is correct by verifying against the example outputs and by

  using the check_qX() functions

• test that your proj2.sql file will load without error into a database containing just the

  original MyMyUNSWdata

• double-check that your proj2.sql file loads in a single pass into a database containing just

  the originalMyMyUNSWdata

  3. Introduction

  All Universities require a significant information infrastructure in order to manage their affairs. This typically involves a large commercial DBMS installation. UNSWs student information system sits behind the MyUNSW web site. MyUNSW provides an interface to a PeopleSoft enterprise management system with an underlying Oracle database. This back-end system (Peoplesoft/Oracle) is oftencalledNSS.

  UNSW has spent a considerable amount of money ($80M+) on the MyUNSW/NSS system, and it handles much of the educational administration plausibly well. Most people gripe about the quality of the MyUNSW interface, but the system does allow you to carry out most basic enrolment tasks online.

Despite its successes, however, MyUNSW/NSS still has a number of deficiencies, including:

• no waiting lists for course or class enrolment
• no representation for degree program structures
• poor integration with the UNSW Online Handbook

  The first point is inconvenient, since it means that enrolment into a full course or class becomes a sequence of trial-and-error attempts, hoping that somebody has dropped out just before you attempt to enrol and that no-one else has grabbed the available spot.

  The second point prevents MyUNSW/NSS from being used for three important operations that would be extremely helpful to students in managing their enrolment:

• finding out how far they have progressed through their degree program, and what remains to becompleted
• checking what are their enrolment options for next semester (e.g. get a list of suggested courses)
• determining when they have completed all of the requirements of their degree program and areeligibletograduate

  NSS contains data about student, courses, classes, pre-requisites, quotas, etc. but does not contain any representation of UNSWs degree program structures. Without such information in the NSS database, it is not possible to do any of the above three. So, in 2007 the COMP9311 class devised a data model that could represent program requirements and rules for UNSW degrees. This was built on top of an existing schema that represented all of the core NSS data (students, staff, courses, classes, etc.). The enhanced data model was named the MyMyUNSW schema.

  The MyMyUNSW database includes information that encompasses the functionality of NSS, the UNSW Online Handbook, and the CATS (room allocation) database. The MyMyUNSW data model, schema and database are described in a separate document.

  4. SettingUp
  To install the MyMyUNSW database under your Grieg server, simply run the following two

  commands:

  $ createdb proj2
  $ psql proj2 -f /home/cs9311/web/16s1/proj/proj2/mymyunsw.dump

  If youve already set up PLpgSQL in your template1 database, you will get one error message as the database starts to load:

  psql:mymyunsw.dump:NN: ERROR: language plpgsql already exists

  You can ignore this error message, but any other occurrence of ERROR during the load needs to be investigated.

If everything proceeds correctly, the load output should look something like:

SET
SET
SET
SET
SET
psql:mymyunsw.dump:NN: ERROR: language plpgsql already exists if PLpgSQL is not already defined,

the above ERROR will be replaced by CREATE LANGUAGE SET
SET
SET

CREATE TABLE
CREATE TABLE
a whole bunch of these CREATE TABLE
ALTER TABLE
ALTER TABLE
a whole bunch of these ALTER TABLE

Apart from possible messages relating to plpgsql, you should get no error messages. The database loading should take less than 60 seconds on Grieg, assuming that Grieg is not under heavy load. (If you leave your project until the last minute, loading the database on Grieg will be considerably slower, thus delaying your work even more. The solution: at least load the database Right Now, even if you dont start using it for a while.) (Note that the mymyunsw.dump file is 50MB in size; copying it under your home directory or your /srvr directory is not a good idea).

If you have other large databases under your PostgreSQL server on Grieg or you have large files under your /srvr/YOU/ directory, it is possible that you will exhaust your Grieg disk quota. In particular, you will not be able to store two copies of the MyMyUNSW database under your Grieg server. The solution: remove any existing databases before loading your MyMyUNSW database.

If youre running PostgreSQL at home, the file proj2.tar.gz contains copies of the files: mymyunsw.dump, proj2.sql to get you started. You can grab the check.sql separately, once it becomes available. If you copy proj2.tar.gz to your home computer, extract it, and perform commands analogous to the above, you should have a copy of the MyMyUNSW database that you can use at home to do this project.

A useful thing to do initially is to get a feeling for what data is actually there. This may help you understand the schema better, and will make the descriptions of the exercises easier to understand. Look at the schema. Ask some queries. Do it now.

Examples

$ psql proj2
PostgreSQL welcome stuff
proj2=# d
look at the schema proj2=#
select * from Students;
look at the Students table
proj2=# select p.unswid,p.name from People p join Students s on (p.id=s.id);
look at the names and UNSW ids of all students
proj2=# select p.unswid,p.name,s.phone from People p join Staff s on (p.id=s.id); look at the names, staff ids, and phone #s of all staff
proj2=# select count(*) from Course_Enrolments;
how many course enrolment records
proj2=# select * from dbpop();
how many records in all tables
proj2=# etc. etc. etc.
proj2=# q

You will find that some tables (e.g. Books, Requirements, etc.) are currently unpopulated; their contents are not needed for this project. You will also find that there are a number of views and functions defined in the database (e.g. dbpop() from above), which may or may not be useful in this project.

Summary on Getting Started

To set up your database for this project, run the following commands in the order supplied:

$ createdb proj2
$ psql proj2 -f /home/cs9311/web/16s1/proj/proj2/mymyunsw.dump $ psql proj2
run some checks to make sure the database is ok
$ mkdir Project2Directory
make a working directory for Project 2
$ cp /home/cs9311/web/16s1/proj/proj2/proj2.sql Project2Directory

The only error messages produced by these commands should be those noted above. If you omit any of the steps, then things will not work as planned.

Notes
Read these before you start on the exercises:

• the marks reflect the relative difficulty/length of each question
• use the supplied proj2.sql template file for your work
• you may define as many additional functions and views as you need, provided that (a)

  the definitions in proj2.sql are preserved, (b) you follow the requirements in each questionon whatyouareallowedtodefine

• make sure that your queries would work on any instance of the MyMyUNSW schema;

  dont customise them to work just on this database; we may test them on a

  different database instance

• do not assume that any query will return just a single result; even if it phrased

  as most or biggest, there may be two or more equally big instances in

  the database

• you are not allowed to use limit in answering any of the queries in this project
• when queries ask for peoples names, use the Person.name field; its there

  preciselytoproduce displayablenames

• when queries ask for student ID, use the People.unswid field; the People.id field

  is an internal numeric key and of no interest to anyone outside the database

• unless specifically mentioned in the exercise, the order of tuples in the result

  does not matter; it can always be adjusted using order by. In fact, our check.sql

  will order your results automatically for comparison.

• the precise formatting of fields within a result tuple does matter; e.g. if you

  convertanumber toastringusingto_charitmaynolongermatchanumeric

  field containing the same value, even though the two fields may look similar

• develop queries in stages; make sure that any sub-queries or sub-joins

  that youre using actually work correctly before using them in the query for the final view/function

  An important note related to marking:

• make sure that your queries are reasonably efficient (defined as taking < 60 seconds to run)
• use psqls timing feature to check how long your queries are taking; they must each take less than 60000 ms
• queries that are too slow will be penalized by half of the mark for that question, even if they give the correct result

  Each question is presented with a brief description of whats required. If you want the full details of the expected output, take a look at the expected_qX tables supplied in the checking script.

  5. Exercises

  Note that the mymyunsw.dump used in project 2 is different from that used in project 1, please confirm that you load the correct database when you start your work.

  Q1 (8 marks)

  You may use any combination of views, SQL functions and PLpgSQL functions in this question. However, you must define at least a PLpgSQL function called Q1.

Write a PLpgSQL function Q1(integer) to generate transcript record for the given student. Each transcript tuple should contain the following information as follows: code, term, course, prog, name, mark, grade, uoc, rank and totalEnrols. The meaning of each attribute is explained in the following.

Use the following definition for the transcript tuples: create type TranscriptRecord as (

);

code char(8), term char(4), course integer, prog char(4),

UNSW-style course code (e.g. COMP1021)

semester code (e.g. 98s1) course ID

4-digit program code for the program being studied when the course was studied

name text,
mark integer, grade char(2),
uoc integer, rank integer, totalEnrols integer,

name of the courses subject
numeric mark acheived
grade code (e.g. FL,UF,PS,CR,DN,HD)

units of credit awarded for the course
the rank of the student in the corresponding course

the total number of students enrolled in this course with non-null mark

Sample results (details can be found in check.sql):

proj2=#select * from q1(2237675)

Q2 (8 marks)

You may use any combination of views, SQL functions and PLpgSQL functions in this question. However, you must define at least a PLpgSQL function called Q2.

In some scenarios, we are interested in finding the number of tuples that have a given pattern (represented by regular expression) for each attribute in a table. For example, given a table Subjects and a pattern COMPd{3}, we would like to know the columns of Subjects that COMPd{3} appears in the instances of these columns and also the number of such instances for each column(here we only count the number of matched rows).

Write a PLpgSQL function that displays all instances with the given pattern across all the fields in a given table in the database. Each tuple in the results should show (a) which table is involved, (b) which column in the table, and (c) the number of instances in this column.

You should only display the columns whose number of instances is greater than zero. Use the following type definition and function header:
create type MatchingRecord as (table text, column text, nexamples integer);
create or replace function Q2 (tableName text, pattern text) returns setof MatchingRecord

Sample results (details can be found in check.sql):

proj2=#select * from q2(subjects, COMPddd)

Q3 (9 marks)

You may use any combination of views, SQL functions and PLpgSQL functions in this question. However, you must define at least a PLpgSQL function called Q3.

UNSW staff members hold different roles at different periods of time. This information is recorded in Affiliations table. Given the id of an organization, we want to find all the
staff members who have had at least two non-concurrent roles in the given organization (i.e. they must have two roles where role1s ending date role 2s starting date). Note that you need to consider that the given organization may have lots of sub-organizations. For example, the faculty of engineering has 9 schools, such as biomedical engineering and CSE.

Give the id of an organization, write a PLpgSQL function to (a) find all the staff members who have had several roles in the given organization over time and (b) produce a list showing, for each staff member, their unswid, name, and the roles theyve had in the given organization (includes the name of staff role, the name of organization, and starting date and ending date). Note that the roles should be ordered by their starting date, and should be displayed as a single string with the details for each role on a separate line (terminated by
).

Use the following type definition and function header:
create type EmploymentRecord as (unswid integer, staff_name text, roles text);
create or replace function Q3(integer) returns setof EmploymentRecord
Note that PostgreSQL uses a + character to indicate an end-of-line in its output (as well as printing
). And the staff should be printed in order of their People.sortname.

Sample results (details can be found in check.sql):

proj2=#select * from q3(661)

6. Submission

You can submit this project by doing the following:

• Ensure that you are in the directory containing the files to be submitted.
• Submit the project by typing give cs9311 proj2 proj2.sql .
• If you submit your project more than once, the last submission will replace the

  previous one.

• To prove successful submission, please take a screenshot as assignment submission manual shows and keep it by yourself.
• If you have any problems in submissions, please email to [email protected]. You can also ask questions about this project in our project group, we will answer your question as soon as possible.

  Before you submit your solution, you should check that it will load correctly. If we need to manually fix problems with your file in order to test it, you will be fined via half of the mark penalty for each problem.

  7. LateSubmissionPenalty

  10% reduction for the 1st day, then 30% reduction.