COMP9311 (25T1)
Page 1 of 15
COMP9311 25T1: Project 1
Deadline: Friday 20:59:59 April 4th (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 (25T1)
Page 2 of 15
• 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 (25T1)
Page 3 of 15
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/25T1/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 (25T1)
Page 4 of 15
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/25T1/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/25T1/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 (25T1)
Page 5 of 15
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 (25T1)
Page 6 of 15
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 (25T1)
Page 7 of 15
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-5 and level-7 subjects that
are offered at least twice by the ‘School’ type organizations in 2012. And the
organization’s name should contain ‘Engineering’.
• subject_code should be taken from Subjects.code field.
• ‘School’ type refers to the Orgunit_types.name field that contains
‘School’.
• The organization’s name refers to the orgunits.longname field.
• Level-5 refers to the subject_code formatted as ‘XXXX5***’, 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 course offering at least
'Lecture' and 'Laboratory' classes. Only consider the course that has at least two
professors as staff and offers exactly two lectures in two distinct rooms.
• course_id refers to the Courses.id field.
• Lecture and Laboratory refer to the Class_types.name field.
• Lecture room refers to the Class.room field.
• Professor denotes the ‘Prof’ in People.title.
Question 3 (4 marks)
Define a SQL view Q3(unsw_id) that gives the unswid of students who are enrolled in
the 'Major', 'Minor', and 'Honours' streams simultaneously within the same program.
The student’s unswid starts with 326.
• unsw_id should be taken from people.unswid field.
• The stream types 'Major', 'Minor', and 'Honours' are identified using the refer to
stream_types.description field.
COMP9311 (25T1)
Page 8 of 15
Question 4 (5 marks)
Define an SQL view Q4(course_id, avg_mark) that gives the course ID along with
the average mark of master’s students who achieved a pass in each course. For each
faculty and for each year between 2005 and 2015, it includes only those courses that have
the highest average pass mark among all courses offered by that Faculty in that year. If
multiple courses have the same maximum average mark in that year, all such courses are
included.
Note: Round avg_mark to the nearest 0.01 in numeric type (i.e., 85.014 ≈ 85.01, 85.016 ≈ 85.02, 85 ≈85.00).
• course_id should be taken from Courses.id.
• Faculty refers to the organization units where their Orgunit_types.name is
‘Faculty’.
• A student is considered to have passed if their course mark ≥ 50.
• The ‘master students’ refers to the degree_types.name containing the word
‘Master’.
• The ‘year’ refers to the ‘semesters.year’ field.
Question 5 (5 marks)
Define a SQL view Q5(course_id, student_names, highest_mark) that gives
the ID of course which enrolled more than 500 students in year between 2005 and 2015.
Show the highest mark achieved by any student in the course. And the given names of the
students who achieved this highest mark. If multiple students share the highest mark,
their given names are concatenated in order, separated by "; " (e.g., "Jack;
Michele").
• The given name refers to the People.given field.
Question 6 (5 marks)
Define SQL view Q6(subject_id, year, room_id, usage_count) to return the
ID of each subject that used the largest number of distinct rooms in each year between
2000 and 2015, along with the room ID(s) that the subject used most frequently and the
number of times the subject used that room. If there are multiple subjects or rooms with
the same maximum usage, list all of them.
• subject_id should be taken from subjects.id field.
• The ‘year’ refers to the ‘semesters.year’ field.
• room_id should be taken from rooms.id field.
COMP9311 (25T1)
Page 9 of 15
Question 7 (6 marks)
Define SQL view Q7(student_id, orgunit_id, program_id, obtain_days)
that gives the IDs of students who completed a program in the shortest time for each
organization. The completed program is offered by the organization. If multiple students
achieve the same fastest completion time, all such students are included.
• student_id should be taken from people.unswid field.
• orgunit_id 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 his or her course enrollments for that program,
regardless of whether the course was passed.
Hint:
• In the same program, a student may register for the same subject multiple times
(multiple courses) because of possible retakes after failing. When determining
program completion, only subjects with marks ≥
50 are counted, but when
calculating the duration, all enrollment records are included. We assume each
subject has only one record with a mark over 50.
COMP9311 (25T1)
Page 10 of 15
Question 8 (6 marks)
Define SQL view Q8(staff_id, student_id, teach_times) This view retrieves
the staff ID (staff_id) of those who served as a course convenor at least three times in
the years between 2008 and 2012. Additionally, the total ‘above distinction’ rate (i.e., the
percentage of students with marks ≥ 75) across all courses in history where the staff
member as a course convenor must be ≥ 70%.
For each such staff member, the view also returns the student ID (student_id) of those
who enrolled in courses where the mentioned staff served but may not as a course
convenor, along with the number of times (teach_times) the student enrolled in those
courses across their entire enrollment history.
The result must include only the top-2 students with the highest teach_times (i.e.,
enrollment count). Additionally, only students with teach_times ≥ 3 should be displayed.
• A person is employed as a course convenor for a course if her/his
staff_roles.name is ‘Course Convenor’ in course_staff.
• ‘Above distinction’ means that Course_enrolments.mark is ≥ 75.
• We only consider the no-null mark in the ‘Above distinction’ rate calculation.
• student_id should be taken from people.unswid field.
• staff_id should be taken from People.unswid field.
• The Rank()function in PostgreSQL will be able to do this for you to generate the
ranking column.
COMP9311 (25T1)
Page 11 of 15
Question 9 (6 marks)
Define a PL/pgSQL function Q9(unswsid integer) This function takes a student's
unswid as input and returns the given name of the student's favorite teacher—defined as
the course convenor whose courses the student has enrolled in the most times (Note:
different with Q8).
Additionally, the function returns: 1) The number of times the student has enrolled in
courses convened by this teacher. 2) The rank of the student based on how many times
they have enrolled in the teacher’s courses compared to other students.
• An unswid should be taken from People.unswid field.
• A person is employed as a course convenor for a course if her/his
staff_roles.name is ‘Course Convenor’ in course_staff.
• Only courses where the student has received a non-null mark are considered.
• The teacher’s given name should be retrieved from ’people.given’.
• The student's unswid should be taken from ’people.unswid’.
• 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 three elements
concatenated with a space:
• Teacher name: the given name of the teacher refer to ’people.given’.
• Enrolled times: The number of times the student enrolled in courses convened
by this teacher.
• Rank: the register rank should be an integer. If she/he ranked 2nd in the
ranking, the result is 2.
Special output:
• If the student has not finished any required course with a valid mark, return a line
in the format of ‘WARNING: Invalid Student Input [X]’, where ‘X’
denotes the provided unswid.
COMP9311 (25T1)
Page 12 of 15
Question 10 (7 marks)
Define a PL/pgSQL function Q10(unswid integer) that takes the unswid of a student.
Output the students WAM for all the programs that the student enrolled.
• An unswid should be taken from People.unswid field.
• Unlike to the Question 7, in this question, a student passes a course if she/he
obtains a grade in setpass
=
{SY, PT, PC, PS, CR, DN, HD, A, B, C, XE, T, PE, RC, RS}
refers to course_enrolments.grade.
• All the grades in setpass ∩ setsy = {SY, XE, T, PE} or in setpass ∩ (course mark = null) , means the courses the student passed but won’t be included in WAM
calculation.
• All the grade not in setpass, excluding the course mark =
null,
means a fail.
These failed courses are still included in the WAM calculation, while courses with
null marks are excluded.
• If a student has enrolled in several different programs, you need to calculate the
WAM 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. Assuming students can register at most 1 program in each
semester.
WAM is calculated according to the following formula:
WAM = ∑(M × U) ∑ U
Where: M =
mark received in a course, U = units of credit for a course.
• For example, a student receives the following results for her/his courses: 80, 81,
82, 83, 84. The first three of these courses are 6 UOC and the last two are 3 UOC.
The WAM is calculated as:
[(80×6)+(81×6)+ (82×6)+(83×3)+(84×3)] (6 + 6 + 6 + 3 + 3)
=
81.625 ≈ 81.63 .
Each line of output (in text type) should contain the following three elements
concatenated with a space:
• Unswid: a unswid of student which is taken from People.unswid field.
• Program name: a program name which is taken from programs.name field.
• WAM: the WAM result and round it to the nearest 0.01. Use the same rule as Q4.
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.
COMP9311 (25T1)
Page 13 of 15
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 q9a to q9f). 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/25T1/proj/proj1/mymyunsw.dump
... load the MyMyUNSW schema & data
$ psql proj1 -f /home/cs9311/web/25T1/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_q10e();
… check your solution to question10(e)
proj1=# select check_all();
… check all your solutions
COMP9311 (25T1)
Page 14 of 15
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 (25T1)
Page 15 of 15
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 “ 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.
51作业君版权所有
