COMP9311 25T1: Project 1

2025-04-02 Admin 写评论


Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

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)
  • 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.


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. 

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.
    • 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:
$ 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. 

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

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).

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.


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.

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. If multiple courses share the same subject code with a pass, you can treat them as distinct subjects. For example, some research courses (for instance, honours thesis A/B/C) must be enrolled in multiple times but share the same subject code.

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.

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. 

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.

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
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, youshould 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 vxdb01server. Failure to do so will incur a penalty, deducting half of the mark. This timeconstraint 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 beauto-tested as follows:

a. A fresh copy of the MyMyUNSW database will be created (using the schema frommymyunsw.dump).
b. The data in this database may be different from the database that you're using fortesting.
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.

Project Submission

Submission
  • 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 formatto submit: proj1_zID.sql (e.g., proj1_z5000000.sql).
    • Only the Latest Submission is marked. The Latest Submission after thedeadline will result in a late penalty.
  • In case the system is not working properly, please ensure to take these steps: keepa copy of your submitted file on the CSE server without any post-deadlinemodifications. 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 [email protected].
  • If there are issues with Moodle, send your assignment to the above email with thesubject 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 completelyderived 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.

发表评论

电子邮件地址不会被公开。 必填项已用*标注