PostgreSQL Database System Assignment - Project 2 PLpgSQL

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 PLpgSQL functions to aid in satisfying requests for information

The goal is to build some useful data access operations on the MyMyUNSW database. A theme of this project is "dirty data". As I was building the database, using a collection of reports from UNSW's information systems and the database for the academic proposal system (MAPPS), I discovered that there were some inconsistencies in parts of the data (e.g. duplicate entries in the table for UNSW buildings, or students who were mentioned in the student data, but had no enrolment records, and, worse, enrolment records with marks and grades for students who did not exist in the student data). I removed most of these problems as I discovered them, but no doubt missed some. Some of the exercises below aim to uncover such anomalies; please explore the database and let me know if you find other anomalies.

Tasks -

Q1 - You may use any combination of views, SQL functions and PLpgSQL functions in this question. However, you must define at least a PLpgSQL function called Q1.

Please write a PLpgSQL function Q1(course_id integer) that takes a course_id as parameter and outputs two numbers: (1) among all the rooms in UNSW, the number of rooms which can carry all the students enrolled in this course (i.e., rooms.capacity >= the total number of students enrolled in this course); (2) among all the rooms in UNSW, the number of rooms which can carry all the students enrolled in this course and also carry all the students in the enrolment waitlist of this course (i.e., rooms.capacity >= the total number of students enrolled in this course + the total number of students in the enrolment waitlist of this course).

Q2 - You may use any combination of views, SQL functions and PLpgSQL functions in this question. However, you must define at least a PLpgSQL function called Q2.

Please write a PLpgSQL function Q2(staff_id integer) that takes a staff's id as parameter and returns all teaching records of the given staff. Each transcript tuple should contain the following information: cid, term, code, name, uoc, avg_mark, highest_mark, median_mark, totalEnrols.

Q3 - You may use any combination of views, SQL functions and PLpgSQL functions in this question. However, you must define at least a PLpgSQL function called Q3.

Given the id of an organizational unit, please write a PLpgSQL function Q3 (org_id integer, num_courses integer, min_score integer) to help the UNSW administrative officers to find out all the students satisfying the following: (1) he/she has taken more than num_courses courses offered by the given organization (note that a student may fail a course and take it again, thus we need to count duplicate courses); (2) he/she has got score no less than min_score for at least one course offered by the given organization. Each tuple should include:

• His/Her unswid (should be taken from People.unswid)
• His/Her student_name (should be taken from People.name)
• His/Her course_records

course_records of a student is a concatenation of several records with ','. Each record is about a course he/she has taken and is offered by the given organization. Each record should include the code of the course (Subjects.code), the name of the course (Subjects.name), the semester that he/she has learned this course (Semesters.name), the name of the organization that offers the course (OrgUnits.name), and the score he/she got (Course_enrolments.mark).

Attachment:- Assignment Files - Project 2 PLpgSQL.rar