COMP9311 26T1: Project 1
Deadline: 20:59:59 Sunday April 5th (Sydney Time)
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.
Background
All Universities require a significant information infrastructure to manage their affairs.
This typically involves a large commercial DBMS installation. UNSW’s student informat
-ion 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).
COMP9311 (26T1) Page 1 of 16
• 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.
How to do this project:
• Read this specification carefully and completely.
• Familiarize with the database schema (description, SQL schema, summary).
• 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 qX_expected tables loaded as part of
the check.sql file.
• Solve each of the problems in ‘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 result within 120 seconds on vxdb01 server.
• Hardcode is strictly forbidden.
COMP9311 (26T1) Page 2 of 16
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/26T1/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.
COMP9311 (26T1) Page 3 of 16
The database loading should take less than 60 seconds on vxdb01, assuming that vxdb01
is not under heavy load.
Note:
• If you leave your project until the last minute, loading the database on vxdb01 will
be considerably slower, thus delaying your work even more.
o 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.
o 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/26T1/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/26T1/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.
COMP9311 (26T1) Page 4 of 16
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 from People p join Students s on (p.id=s.id);
... look at the UNSW ids of all students ...
proj1=# select p.unswid, s.phone from People p join Staff s on (p.id=s.id);
... look at the 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
COMP9311 (26T1) Page 5 of 16
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.
• 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_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 qX_expected tables supplied in the checking
script (check.sql).
COMP9311 (26T1) Page 6 of 16
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 an SQL view Q1(unsw_id, student_name) that identifies students who have
enrolled in and passed (mark >= 50) at least 15 level-9 COMP subject (e.g., COMP9XXX)
that was offered in the year 2011.
• unsw_id refers to People.unswid.
• student_name refers to People.name.
Question 2 (3 marks)
Define an SQL view Q2(subject_code, mark_difference) that finds subjects where the
average passing mark (mark >= 50) in the year 2012 was at least 10 marks higher
than its average passing mark in 2008.
• Only consider subjects that had at least 10 passing students in both 2008 and
2012.
• mark_difference should be the 2012 average minus the 2008 average, rounded to
two decimal places (numeric type).
Question 3 (4 marks)
Define an SQL view Q3(staff_id, staff_name, faculty_name) to find staff members who are
exclusively loyal to a single Faculty.
• The staff member must have held at least 3 distinct roles (`staff_roles.id`)
within organizations belonging to a single Faculty(OrgUnit type 'Faculty').
• Furthermore, they must never have been affiliated with any organization
outside of that specific Faculty.
• staff_id should be taken from People.unswid.
COMP9311 (26T1) Page 7 of 16
Question 4 (5 marks)
Define an SQL view Q4(program_id, unsw_id, max_wam) that finds the student with the
highest overall WAM for every active program in 2010.
• An "active program in 2010" means the program had at least one student
enrolled in it during any term in 2010.
• The WAM calculation should include their entire historical course enrolments up
to and including 2010 (ignore courses taken after 2010).
• Use the standard WAM formula:
∑(mark × uoc)
WAM =
∑ uoc
for valid, standard-graded courses.
• A course should be counted toward a program’s WAM only if the student was
enrolled in that course and in that program in the same term.
• If there is a tie for the highest WAM in a program, return all tied students. Round
max_wam to two decimal places.
• Sort by the program_id and max_wam, and shown Top50 results.
Question 5 (5 marks)
Define an SQL view Q5(room_id, distinct_course_count) that identifies the most heavily
multi-purposed rooms.
• Find rooms (`Rooms.id`) that have hosted classes for at least 15 distinct courses
in a single semester (`Semesters.id`), AND those classes covered at least 4
different class types (e.g., Lecture, Laboratory, Tutorial) in that same semester.
• Output the absolute distinct course count across the entire database history for
that room.
Question 6 (5 marks)
Define an SQL view Q6(staff_id, staff_name, unique_co_teachers) to find highly
collaborative teaching staff.
• Find staff members who have co-taught courses (been assigned to the same
`Course_Staff.course`) with at least 30 distinct other staff members across
their entire history.
• For each such shared course, the staff member being counted must not have held
the role 'Course Convenor'.
• unique_co_teachers is the count of distinct staff members they have co-taught
with.
COMP9311 (26T1) Page 8 of 16
Question 7 (6 marks)
Define a PL/pgSQL function Q7(prog_code character(4), start_year integer) RETURNS
text.
The function computes the retention rate of a program cohort.
For this question,
• the cohort consists of all students whose first year of enrolment in the program
identified by prog_code is start_year.
• A student is considered retained after 2 years if the student has at least one
enrolment in the same program in year start_year + 2.
• prog_code refers to programs.code. If the same programs.code corresponds to
multiple program IDs, you should use the one associated with the smallest
semester ID.
The function must return exactly one line in the following format:
Cohort size: [X], Retained after 2 years: [Y]%
where Y is the retention percentage, rounded to 2 decimal places.
If the program code is invalid, or the cohort contains no students, return exactly:
WARNING: Invalid Program or Cohort
COMP9311 (26T1) Page 9 of 16
Question 8 (6 marks)
Define a PL/pgSQL function Q8(unswid integer, target_year integer, target_term
character(2)) RETURNS text.
The function calculates the student’s academic standing for a specified semester. Only
courses with non-null mark are considered.
For the target semester:
• Term WAM is the weighted average mark using Subjects.uoc as weight
• UOC attempted is the total UOC of all courses with non-null mark
• UOC passed is the total UOC of all courses with mark >= 50
• If a (year, term) pair corresponds to multiple semester IDs, you should use the
smallest semester ID.
A student has:
• Good Standing: Term WAM >= 50 AND passed >= 50% of the UOC attempted
in that term.
• Probation: Term WAM < 50 OR passed < 50% of UOC attempted.
• Suspension: The student meets the "Probation" criteria for the target term, AND
they also met the "Probation" criteria in their chronologically previous enrolled
term.
Output: Return just the standing: Good Standing, Probation, or Suspension.
If the student has no courses with non-null mark in the target semester, return exactly:
No valid enrolments for term
Otherwise, return exactly one of:
Good Standing
Probation
Suspension
COMP9311 (26T1) Page 10 of 16
Question 9 (6 marks)
Define a PL/pgSQL function Q9(unswid integer) RETURNS text.
The function calculates a student’s Overall WAM, Major WAM, and the difference
between them.
• Major WAM: Calculate the WAM only for courses where the subject code
matches the first 4 letters of the stream they are enrolled in (e.g., if enrolled in
stream 'COMPA1', only calculate WAM for 'COMP' subjects).
• Overall WAM: The standard WAM across all valid courses.
For this question,
• WAM is calculated across all courses taken by the student that satisfy both:
o mark IS NOT NULL
o grade NOT IN ('SY', 'XE', 'T', 'PE')
• the weight of each course is Subjects.uoc.
• If a student has multiple stream enrolments, use the stream with the smallest
stream ID.
• If the student has no valid courses matching their stream or in the history,
calculate Major WAM or Overall WAM as 0.00.
The function must return exactly one line in the following format:
Overall WAM: [XX.XX], Major WAM: [YY.YY], Difference: [+/-ZZ.ZZ]
where:
• all numeric values must be rounded to 2 decimal places
• Difference = Major WAM - Overall WAM
• if the difference is positive, it must include a leading +
• if the difference is zero or negative, print it in the normal numeric format
If unswid does not correspond to any student, return exactly:
WARNING: Invalid Student Input
COMP9311 (26T1) Page 11 of 16
Question 10 (7 marks)
Define a PL/pgSQL function Q10(unswid integer) RETURNS SETOF text.
The function generates a human-readable text transcript for the given student.
For each semester in which the student has at least one record in Course_enrolments,
output:
• a semester header line,
• one line for each enrolled course in that semester,
• a semester summary line.
Semesters must be output in ascending order of Semesters.starting. Within each
semester, courses must be output in ascending order of Subjects.code.
The required format is:
--- [Year] [Term] ---
[SubjectCode] - [Mark] ([Grade])
[SubjectCode] - [Mark] ([Grade])
...
Term WAM: [XX.XX] | Term UOC Passed: [Y]
For any enrolled course whose mark is NULL, output:
[SubjectCode] - N/A (N/A)
Calculation rules
For each semester:
• Term WAM is the weighted average of all courses in that semester that satisfy
both:
o mark IS NOT NULL
o grade NOT IN ('SY', 'XE', 'T', 'PE')
• the weight of each course is Subjects.uoc
• Term WAM must be rounded to 2 decimal places
• if no courses in that semester are eligible for WAM calculation, Term WAM
should be reported as 0.00
COMP9311 (26T1) Page 12 of 16
Term UOC Passed is the sum of Subjects.uoc for all courses in that semester with
mark >= 50, regardless of grade. After all semesters have been output, return one final
cumulative summary line in exactly the following format:
=== CUMULATIVE WAM: [ZZ.ZZ] | TOTAL UOC: [W] ===
Where,
• CUMULATIVE WAM calculation is same with the Term WAM above.
• If there are no eligible courses for cumulative WAM calculation, it should be
reported as 0.00.
• TOTAL UOC is the sum of Subjects.uoc for all courses with mark >= 50, across all
semesters, regardless of grade.
If unswid does not correspond to any student, the function must return exactly:
WARNING: Invalid Student Input
If the student exists but has no course enrolments, the function should return only the
final cumulative summary line.
COMP9311 (26T1) Page 13 of 16
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 three testcases (E.g.,
for question 9, they are from q9a to q9c). 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/26T1/proj/proj1/mymyunsw.dump
... load the MyMyUNSW schema & data
$ psql proj1 -f /home/cs9311/web/26T1/proj/proj1/check.sql
... load the checking code
$ psql proj1 -f proj1.sql
... 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_q10c();
… check your solution to question10(c)
proj1=# select check_all();
… check all your solutions
COMP9311 (26T1) Page 14 of 16
Note:
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()’.
3. The submission 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. A fresh copy of the MyMyUNSW database will be created (using the schema from
mymyunsw.dump).
b. The data in this database may be different from the database that you're using for
testing.
c. A new check.sql file may be loaded (with expected results appropriate for the
database).
d. The contents of your submission file will be loaded.
e. Each checking function will be executed, and the results will be recorded.
COMP9311 (26T1) Page 15 of 16
Project Submission
Submission
• You are required to submit an electronic version of your answers via Moodle.
o We only accept the .sql format. Please name your files in the following format
to submit: proj1_zID.sql (e.g., proj1_z5000000.sql).
o 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 about how to do this, refer to the guidelines
provided on Taggi.
Note:
• If you have problems relating to your submission, please email to xingyu.tan@un
sw.edu.au.
• If there are issues with Moodle, send your assignment to the above email with the
subject title “<zid> COMP9311 Proj1 Submission”.
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.
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.
COMP9311 (26T1) Page 16 of 16
