•     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 enroll 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 be completed.

•     Checking  what  are their  enrolment options for  next semester  (e.g., get a  list of available courses).

•     Determining when they have completed all the requirements of their degree program and are eligible to graduate.

NSS contains data about students, courses, classes, pre-requisites, quotas, etc. but does not contain any  representation  of  UNSW's  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 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.  Setting Up

To install the MyMyUNSW database under your nw-syd-vxdb server, simply run the following two commands:

$ createdb proj1

$ psql proj1 -f /home/cs9311/web/24T1/proj/proj1/mymyunsw.dump

If you've 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 exist.

You can ignore the above error message, but all other occurrences of ERROR during the load need 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 nw-syd-vxdb, assuming that nw-syd-vxdb   is not under heavy load. (If you leave your project until the last minute, loading the database on nw-  syd-vxdb will be considerably slower, thus delaying your work even more. The solution: at least load  the database Right Now, even if you don't 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 nw-syd-vxdbor if you have large files under your ‘/srvr/YOU/’ directory, it is possible that you will exhaust your nw-syd-vxdb disk quota. Regardless, it is certain that you will not be able to store two copies of the MyMyUNSW database under your nw-syd-vxdb server. The solution: remove any existing databases before loading your MyMyUNSW database.

Summary on Getting Started

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

$ createdb proj1

$ psql proj1  -f  /home/cs9311/web/24T1/proj/proj1/mymyunsw.dump $ psql proj1

... run some checks to make sure the database is ok

$ mkdir Project1Directory

... make a working directory for Project 1

$ cp  /home/cs9311/web/24T1/proj/proj1/proj1.sql  Project1Directory

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.

5.  Important Advice Before You Start

The database instance you are given is not a small one. The first thing you should do is get a feeling for what data is there in the database. This will help you understand the schema better and will make the tasks easier to understand. Tip: study the schema of each table to see how tables are related and try write some queries to explore/ understand what each table is storing.

$ psql proj1

proj1=# \d

... study the schema ...

proj1=# select * from Students;

... look at the data in the Students table ...

proj1=# select p.unswid,p.name from People p join Students son (p.id=s.id); ... look at the names and UNSW ids of all students ...

proj1=# select p.unswid,p.name,s.phone from People p join Staffs on (p.id=s.id); ... look at the names, staff ids, and phone #s of all staff ...

proj1=# select count(*) from Course_Enrolments;

... get an idea of the number of records each table has...

proj1=# select * from dbpop();

... how many records in all tables ...

proj1=# ... etc. etc. etc.

proj1=# \q

Read these before you start on the exercises:

•     The marks reflect the relative difficulty/length of each question.

•     Work on the project on the supplied proj1.sql template file.

•     Make sure that your queries work on any instance of the MyMyUNSW schema; don't customize them to work just on this database; we may test themona 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.

•     When  queries  ask  for  people's  names,  use  the Person.name field;  it's  there  precisely  to produce displayable names.

•     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 convert a number to a string using to_charit may no longer match a numeric field containing the same value, eventhough the two fields may look similar.

•     We  advise developing queries  in stages; make sure that any sub-queries or sub-joins that you're using works correctly before using them in the query for the final view/function

•     You  may  define  as  many  additional  views  as  you  need,  provided  that  (a)  the  definitions in proj1.sql are preserved, (b) you follow the requirements in each question on what you are allowed to define.

•     If you meet with error saying something like “cannot change name of view column”, you can drop the view you just created by using command “drop view VIEWNAME cascade;” then create your new view again.

Each question is presented with a brief description of what's required. If you want the full details of the expected output, look at the expected_qX tables supplied in the checking script (check.sql).

6.  Tasks

To facilitate the semi-auto marking, please pack all your SQL solutions into view/function as defined in each problem (see details from the solution template we provided).

Question 1 (3 marks)

Define a SQL view Q1(subject_code) that gives all the level-7 subjects that are offered by organi- zations whose type is school. And the school’s name should contain ‘Information’ .

•     subject_code should betaken from Subjects.code field.

•     School refers to the Orgunit_types.name field that contains ‘ School’ .

•     The school name refers to the orgunits.longname field.

•     Level-7 refers to the subject_code formatted as ‘XXXX7***’, where ‘X’ represents a letter and ‘*’ represents a number.

Question 2 (3 marks)

Define an SQL view Q2(course_id) that gives the ID of the COMP course only offering 'Lecture' and 'Laboratory'classes (the course has only 2 different types of classes).

•     course_id refers to the Courses.id field.

•     Lecture and Laboratory refer to the Class_types.name field.

•     COMP courses refer to the courses whose related Subjects.code starting with ‘COMP’.

Question 3 (4 marks)

Define a SQL view Q3(unsw_id) that gives the unswid of students who enrolled in at least five

courses in year between 2008 and 2012. Only consider the course that has at least two professors as staff and the student whose unswid starting with 320.

•     unsw_id should betaken from people.unswid field.

•     Professor denotes the ‘Prof’ in People.title.

•     Year refers to Semesters.year field.

Question 4 (5 marks)

Define an SQL view Q4(course_id, avg_mark) that gives the  ID  of course along with the average mark of students who achieved 'above distinction' for each course.  The view only includes the courses that have the highest average 'above distinction' mark when compared to other courses offered by the same faculty and within the same semester in the year 2012.  If there are multiple courses sharing the same maximum average mark, list all the course_id and its avg_mark of these courses.

Note: Round avg_mark to the nearest 0.01 in in numeric type (i.e., 85.014≈85.01, 85.016≈85.02, 85≈85.00).

•     course_id should betaken from Courses.id.

•     Faculty refer to the organization units where their Orgunit_types.name is ‘Faculty’.

•     The ‘above distinction’ means the course grade of a student is ‘DN’ or ‘HD’.

Question 5 (5 marks)

Define a SQL view Q5(course_id, staff_name) that gives the ID of course which enrolled more than 500 students and had at least two professors as staff in year between 2005 and 2015. Show the ordered given name of the professors in the staff_name section.

Note: Concatenating the given name with ‘ ; ’ (i.e., Jack; Michele).

•     Professor refers to the ‘ Prof’ in People.title field.

•     The given name refers to the People.given field.

Question 6 (5 marks)

Define SQL view Q6(room_id, subject_code)that gives the ID of room(s) that were used most frequently by different classes in the year 2012, along with the subject code(s) that occupied it the most. If there are multiple rooms or subjects sharing the identical maximum usage, listing all of them.

•     room_id should betaken from rooms.id field.

•     subject_code should betaken from subject.code field.

Question 7 (5 marks)

Define  SQL view  Q7(student_id, program_id) that  gives  the  IDs  of  students  who  have completed two or more programs offered by the same organizations. Additionally, the completion of all programs from the same organization occurred within a duration of 1000 days. The program_id column should list the IDs of these programs.

•     student_id should betaken from people.unswid field.

•     Organization refers to orgunits.id field.

•     program_id refers to   programs.id field.

•     Assuming students can register at most 1 program in each semester. Note:

•    A student will pass the course and earn the UOC if she/he receives the mark ≥ 50.

•    The student is valid for graduation if the total UOC earned in the program (hint: subjects.uoc) is no less than the required UOC of the program (refer to programs.uoc).

•    If a student has enrolled in several different programs, you need to calculate the UOC separately according to different programs. A course is considered part of a program if the student enrolls in both the course and the program during the same semester.

•    For each student, the duration of one program is the number of days between the earliest date (hint:  Semesters.starting) and the  latest date  (hint:  Semesters.ending) among all her/his course enrolments for the same program.

•    ‘ 1000 days’ refers to the duration from the start of the first program (hint: Semesters.starti ng)  to  the  completion  of  the   latest   program  (hint:  Semesters.ending)  among  all  the programs the student completed from the same organization does not exceed 1000 days. The program_id field should enumerate the IDs of the programs involved.

Question 8 (6 marks)

Define SQL view Q8(staff_id, sum_roles, hdn_rate) that gives the id of the staff, the total number of roles (affiliations) the staff held in the pastor currently across all organizations, and the total ‘ above distinction’ rate of all the courses where the staff member serves as course convenor in the year 2012. The view displays only staff who previously held three or more roles (affiliations) in same organizations, showcasing the top 20 results based on hdn_rate.

Note: Round the rate to the nearest 0.01 with the same rule as Question 4.

•     Above distinction means that Course_enrolments.mark is = 75.

•     A  person  is  employed  as a course convenor for a course  if her/his  staff_roles.name is ‘Course Convenor’ in course_staff.

•     The number of staff roles (affiliations) in one organization refers to her/his staff_roles.id in affiliations.orgunit field.

•     staff_id should betaken from People.unswid field.

•     hdr_rate should be in numeric type.

Question 9 (6 marks)

Define a PL/pgSQL function Q9(unswid integer) that takes an unswid of a student and returns the subject code of the course the given student enrolled in and finished with a valid mark, and the rank of the student within that course. Only consider the courses which have at least one same-prefix course as prerequisite.

•     Anunswid should betaken from People.unswid field.

•     Prerequisite refers to _prereq in the related table.

•     Same-prefix courses are the courses which have the same first four characters.

•     Ranking is based on the marks received by the students (refer to Course_enrolments.mark) in this course, from highest to lowest. If multiple students have achieved the same mark, they  should be assigned with the same ranking. The Rank()function in PostgreSQL will be able to do  this for you to generate the ranking column.

Each line of output (in text type) should contain the following two elements concatenated with a space:

•     Subject code: the subject code of the course should betaken from subjects.code field.

Special output:

•    If the student has enrolled in one program, but she/he did not register for any course or all the courses registered are not included in WAM calculation for that program, i.e., the divisor is zero, return ‘No WAM Available’ in the WAM section of the return line.

•    If the student has not enrolled in any programs (cannot find any programs for the student), return a line in the format of ‘WARNING: Invalid Student Input [X]’, where ‘X’ denotes the provided unswid.

7.  AutoTest Checking

Before you submit your solution, you should check and test its correctness by using the following operations. For each PostgreSQL question, we provide six testcases (E.g., for question 9, they are from q9ato q9f). The testcases can be found from the Line 222 in check.sql file:

$ dropdb proj1                                                                       ... remove any existing DB

$ createdb proj1                                                                   ... create an empty database $ psql proj1 -f /home/cs9311/web/24T1/proj/proj1/mymyunsw.dump

... load the MyMyUNSW schema & data $ psql proj1 -f /home/cs9311/web/24T1/proj/proj1/check.sql

... load the checking code

$ psql proj1 -fproj1.sql                                                      ... load your solution

$ psql proj1

proj1=# select check_q1();                                                 … check your solution to question1

…

Notes:

1.    You must ensure that your submitted proj1.sql file will be loaded and run correctly (i.e., it has no syntax errors, and it contains all your view definitions in the correct order).

a.    If your database contains any views that are not available in a file somewhere, you should put them into a file before you drop the database.

b.    For all the submission files, you must make sure there is no error occurring when using the autotest provided above. If we need to manually fix problems in your proj1.sql file to test it (e.g., change the order of some definitions), you will be fined half of the mark as penalty for each problem.

2.    In addition, write queries that are reasonably efficient.

a.    For each question, the result must be produced within 120 seconds on the nw-syd-vxdb server.

Failure to do so will incur a penalty, deducting half of the mark. This time constraint applies to executing the command ‘select * from check_Qn()’ .