COMP9311 25T3: Project 1

Deadline: 5pm Monday, 27 October

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 PL/pgSQL functions to aid in satisfying requests for information.

•    The goal is to build some useful data access operations on the MyMyUNSW database. The

data may contain some data inconsistencies; however, they won’t affect your answers to the project.

2. How to do this project:

•     Read this specification carefully and completely.

• Familiarize yourself with the database schema (relation diagram.svg).

•     Make a private directory for this project, and put a copy of the proj1.sql template there.

•    You must use the create statements in proj1.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  in  the  ‘tasks’   section  and   put  your  completed  solutions into proj1.sql.

•     Check that your solution is correct  by verifying against the example outputs and by using the check_qX() functions (following the ‘AutoTest Checking’ section).

•    Test that your proj1.sql file will load without error into a database containing just the original MyMyUNSW data.

•     Double-check that your proj1.sql file loads in a single pass into a database containing just the original MyMyUNSW data.

•     Submit the project via Moodle.

•     For each question, you must output the result within 120 seconds on the vxdb01 server.

• Hardcode is strictly forbidden.

3. Introduction

All Universities require a significant information infrastructure to manage their affairs. This typically involves a large commercial DBMS installation. UNSW’s 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 often called NSS.

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, MyUNSW/NSS still has several 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 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  vxdb01  server,  simply   run  the  following  two commands:

$ createdb proj1

$ psql proj1 -f /home/cs9311/web/25T3/proj/mymyunsw.dump

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

SET

SET

SET

SET

SET

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 vsdb01, assuming that vxdb01 is not under heavy load. (If you leave your project until the last minute, loading the database on vxdb01 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 ‘/localstorage’ directory is not a good  idea).

If you have other large databases under your PostgreSQL server on vxdb01 or if you have large files under your ‘/localstorage/YOU/’ directory, it is possible that you will exhaust your vxdb01 disk quota. Regardless, it is certain that you will not be able to store two copies of the MyMyUNSW database under  your  vxdb01  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:

$ mkdir Project1Directory

$ cd Project1Directory

Download from WebCMS the proj1.sql and check.sql and put them in Project1Directory $ createdb proj1

$ psql proj1  -f  /home/cs9311/web/25T3/proj/mymyunsw.dump

$ psql proj1  -f  check.sql

Editing your proj1.sql, when you finished, loading it with

$ psql proj1  -f proj1.sql

If you omit any of the steps, then things may 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 s on (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 Staff s 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 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.

• 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_char it may no longer match a numeric field containing the same value, even though 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 (4 marks)

Define a SQL view Q1(ID) that gives the ids of rooms that heId cIasses for course with codes (subjects.code) starting with (MUSC’ .

Question 2 (4 marks)

Define a SQL view Q2(code) that gives the distinct codes (subjects.code) of courses in year 2007 with more than 100 students enroIIed, where the average mark is greater than 50.

Question 3 (4 marks)

Define a SQL view Q3(name) that gives the distinct name (peopIe.name) of professors affiIiated with (SchooI of ChemicaI Sciences’  (orgunits.Iongname) who worked as Iecturers for courses in semester S1 2008. (professors refer to those whose staff_roIes.name contains (professor’ regardIess of case,  Iecturers refer to those whose staff_roIes.name contains (Iecturer’ regardIess of case).

Question 4 (5 marks)

Define a SQL view Q4(code) that gives the code of the subjects (subjects.code) which used the Iaboratory rooms (room_types.description = 'Laboratory') in the same buiIding in 2010 during both semester S1 and S2 (refer to semesters.term). Do not consider the subjects that did not use any Iaboratory room.

Question 5 (5 marks)

Define a SQL view Q5(facuIty_name, program_name) that gives the distinct facuIty names (orgunits.Iongname) and program names (programs.name) which offer master programs (degree_types.prefix = (Master of’). For each facuIty, incIude onIy those master programs that have either the minimum or the maximum UOC among aII master programs offered by that facuIty (orgunit_types.name = (FacuIty’).

Question 6 (4 marks)

Define a SQL view Q6(name) that gives the distinct names (peopIe.name) of students who begin  their first stream enroIments onIy in the second year of the program that the stream enroIments beIong to, where the stream is offered by the CoIIege of fine arts (orgunits.Iongname = 'CoIIege of Fine Arts (COFA)').

•    For simpIicity, the second year of a program is defined as the earIiest year of enroIment in that program + 1.

•    If a student has enroIIed in muItipIe programs, they shouId stiII be incIuded in the resuIt if at Ieast one of those programs has their first stream enroIment occurring in the program’s second year.

•    If a student has enroIIed in muItipIe streams in a program, onIy consider the one that enroIIed earIiest.

Question 7 (5 marks)

Define a SQL view Q7(name) that gives the name of students (peopIe.name) who compIeted Iess  than 18 UOC in a semester when enroIIed in a program offered by (SchooI of the Arts and Media’ (orgunits.Iongname = (SchooI of the Arts and Media’). ExcIude semesters in which the student had fewer than 18 UOC remaining in their program.

For UOC caIcuIation,

•    a course counts toward a program onIy if the course enroIment and the program enroIments occur in the same semester.

•    For semester X, a student’s finished UOC in a program is the totaI UOC compIeted for

courses in earIier semesters (i.e., semesters that ended (semesters.ending) before semester

X began (semesters.starting)).

•    Ignore any program or course that does not provide UOC.

•    For simpIicity, grades or marks do not need to be considered when determining earned UOC.

Question 8 (6 marks)

Define a SQL view Q8(ID) that gives the id of the courses where each Iecture session’s room has enough capacity to accommodate aII enroIIed students, but the combined capacity of aII tutoriaI rooms is insufficient for aII enroIIed students. If a room does not provide a capacity, treat it as 0.

ExcIude the courses that do not have tutoriaIs. (TutoriaI rooms are defined as rooms where cIass_types.description contains (tutoriaI’ regardIess of case, Iecture rooms are defined as rooms where cIass_types.description contains (Iecture’ regardIess of case)

Question 9 (6 marks)

Define a PL/pgSQL function Q9 (subject_code character(8)) that finds the semesters of courses with the given subject code which have the highest number of students achieving HD (mark >= 85) and   the name of these students (peopIe.name). The function shouId return a set of texts, where each Iine contains the semester name(semesters.Iongname) and the student’s name(peopIe.name)

separated by a comma, for exampIe:

Semester 1 2009,EmiIy Johnson

Semester 1 2009,DanieI Chen

Semester 2 2010,Sophia Martinez

…

Question 10 (7 marks)

Define a PL/pgSQL function Q10(student_id integer, start_date date, end_date date) that returns the students’ wam during this period (rounded to 2 decimaI pIaces), weighting each course by its uoc.

IncIude a course onIy if the input start_date is before the semester of that course and the input end_date is after that semester. Do not consider the course if its mark is not provided.

Hint: Use the ROUND() function to round the resuIt to 2 decimaI pIaces.

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 several testcases. The testcases can be found in check.sql file.

$ dropdb proj1 ... remove any existing DB

$ createdb proj1 ... create an empty database

$ psql proj1 -f /home/cs9311/web/25T3/proj/mymyunsw.dump

... load the MyMyUNSW schema & data

$ psql proj1 -f

... load the checking code

$ psql proj1 -f ... load your solution

$ psql proj1

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

…

proj1=# select check_q6(); … check your solution to question6

…

proj1=# select check_q9a(); … check your solution to question9(a)

…

proj1=# select check_q10e(); … check your solution to question10(e)

…

proj1=# select check_all(); … check all your solutions

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 vxdb01 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()’.

8. Project Submission

Final check before submission

We’re aware that many students are doing this project on their own machine, this should be fine.

However, the PostgreSQL installed on your own laptop may not be compatible with the one installed in vxdb01, which may cause you to lose marks unnecessarily, as we will test your solution on the vxdb01 server. Hence, before you submit, you have to check your solution on the vxdb01 server with a newly created database. Please follow the detailed steps presented in Section 7: ‘Test your solution’.

Submission

You can submit this project by doing the following:

•    You are required to submit an electronic version of your answers via Moodle.

•    We  only accept the .sql format.  Please name your files in the following format to submit: proj1_zID.sql (e.g., proj1_z5000000.sql).

•    Only the Latest Submission is marked. The Latest Submission after the deadline will result in a late penalty.

•    In case the system is not working properly, please ensure to take these steps: keep a copy of your  submitted file  on the  CSE  server  without  any  post-deadline  modifications.  If  you're uncertain how to do this, refer to the guidelines on Taggi.

The proj1_zID.sql  file  should  contain  answers  to  all  the  exercises  for  this  project.  It  should  be completely self-contained and able to load in a single pass, so that it can be auto-tested as follows:

•    A fresh copy of the MyMyUNSW database will be created (using the schema from mymyunsw.dump).

•    The data in this database may differ from the database you're using for testing.

•    A new check.sql file may be loaded (with expected results appropriate for the database).

•    The contents of your proj1_zID.sql file will be loaded.

•     Each checking function will be executed, and the results will be recorded.

9. Late Submission Penalty

•     5% of the total mark (50 marks) will be deducted for each additional day.

•     Submissions that are more than five days late will not be marked.

10. Plagiarism

The work you submit must be your own work. Submission of work partially or completely derived from any other person or jointly written with any other person is not permitted. The penalties for such an offence may include negative marks, automatic failure of the course and possibly other academic discipline.

All submissions will be checked for plagiarism. The university regards plagiarism as a form. of academic misconduct and has very strict rules. Not knowing the rules will not be considered a valid excuse when you are caught.

•    For UNSW policies, penalties, and information to help avoid plagiarism, please see:

https://student.unsw.edu.au/plagiarism.

•    For guidelines in the online ELISE tutorials for all new UNSW students:

https://subjectguides.library.unsw.edu.au/elise/plagiarism.