Description
Aims
This assignment 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, You will find sorre empty tables It is OK as they are not
necessary for this assignment
This database has been built over time by Dr John Shepherd to capture the data behind many student record management systems at UNSW A
theme Of this assignment is “dirty data’ As John was building the database, using a collection Of reports from UNSVfs information systems, he
discovered that there were some inconsistencies in parts Of the data (e.g. 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). He removed most of these
problems as he discovered them, but no doubt missed some, Some Of the exercises below aim to uncover such anomalies; please explore the
database and see if you can find other anomalies
Note also that, as the database was constructed, he made some small changes to the schema, in order to deal with the data he had available, For
example, there was no starting time for some of the staff affiliations, and so had to remove the null constrainton Al-filial ion. start ing in
order to load a who e bunch of staff affiliation data. You should make sure that you check the latest version of the schema and use that; if in doubt,
check the meta—data in the database itself (using psql •s d command),
Obviously, for assignments and other learning exercises for the database course, John has altered and anonymise the data completely. This means
the student IDs are not real and the transcript:enrolment records arc not real. If you happen to find a student ID that exists, it will be an accidental
match and the matching records have no link to the real person. But we doubt that a real ID exists in the database
Summary
All Universities require a significant information infrastructure in order to manage their affairs, Th’s 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 (Peop esofYOracle) is Often called NSS
UNSW has spent a considerable amount Of money (S8CM*) on the MylJNSW/NSS system, and it handles much Of the educational administration
plausibly well. Most people gripe about the quality of the MylJNSW interface, but the system does allow you to carry out most basic enrolment tasks
on line
Despite its successes, however, MyUNSW/NSS still has a number of deficiem3ies, 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 enrol and that no-one else has grabbed the available spot
The second point prevents MylJNSW\NSS from being used for three important operations that wou d be extremely helpfu to students in managing
their enro ment:
https://cgi.CSe unsw.edu au/—csg311121 T3/assignments/two/spec php
2021/11,7
COMP9311 21T3 Assignment two
• 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 “suggested” courses)
• determining when they have completed all or the requirements Of their degree program and are eligible to graduate
NSS contains data about student, courses, classes, pre—requisites, quotas, etc but does not contain any representation Of UNSVfs 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 CCVP3311 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
of the core NSS data (students, staff, courses, classes, etc The enhanced data mode WES named the “MyMyLlNSI”‘r 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
Setting up
Note if
ng this fie But will
To VyMyUNSW database Grieg simply run
alma&y set up Pt_mSOL template I d8tat.se, wil get as detab.se starts to load:
of
or
ERROR,
-pipgsql-
You can ignore thig error meSSage, but Other dufng the load to be investigated.
If p—ds the bad like
ERROR:
. the
CREATE TABLE
CREATF. TANI_F_
CREATE TABLE
ALTER TAHLE
ALTH TAHLE
ALTER TAHLE
b, LANGUAGE
messages relating 1 yau should gel na error messages. Tha database loading should lass than 40 on
assuming is not last minute. loading on Greg “ill b”
considerably thus delaying Tho ‘ho databasg Right if you don’t il a
while.) {Note •hat
file 34VB •n mpying it und&• your or WY srvr,’ a
It you have large databases mastgreSCL on Greg nave large tles / srvr/YOL:,’ direct.x•y. it is possible that
you exhaust Grieg disk qucy.a_ In garlicular, will be able copes of “yMyLJNSW Greg
The any existing databases before loading your vyMvLJNSW database
A u+ful thing to do to get f—ling data s æually ther.. This may better. make the
of look at the Ask qu4-ieA. Do it
• students
t the .
n stud
n (p. id=s. i d). Also
aut—cs9311’21 php
COMP93*1 71T3 two
the within a matter; e.g. it you c
You will that some tables (e.g. Books, etc ) are unpopulated: their co-Ments are not needed assignment. You will
n (e.g. which may
be useful in this
Summary Getting Started
To up this —ds
ke the
mkdir
o,’ass2.
will
psal
nly s\wuld It you ot st$ß_ then thirgs as
It ask the that steps,
Re” stmt
• the diff. tv,’length of qAtio
pp d template
• you as many and need, (a) the definitions nss2, sq: (b’ you fallow
the in each queston what to deline
• make sure any vyMyuNSW we
may test them on a
• that any return just a single result; there
in ‘he database
• all—I to
in an—ing any of qy
n this
do of d if e if a questjw asks classes. do not use the fact tho i d Of
the class tvpei3 and
10, People. id
the dateba«
key no interest to
• in the the in the matter; it can als-ays Ere using
Æing
it may match
c fAd
similar
• ‘—lop in subq•Aies or in
for
Each is a description What’s required, want the details o’ the expected output, taka a at the
tables supplied in the necking “ript
utility Function termnameO — for Term ID and Year
utility Function termnameO — for Term ID and Year
miting the assignment, you may that witn Terns. i d values is not Zeal refeming to semesters. CSE typically
uses symbolic names like “ODs'” to give SOL takes as
when to
• their
rgums a
The defined as
SS
of digits of and
(with a
d— to a term, the the simply returns a VULL Value. You ere expected use this ‘L.nctio
sc 00 , s which gives details of the cument Heads
lollewing details about each
Since there is dirtplooking data in the ion table, will need ta ensure that you legitimate Heaas ot School. Apply the
filters:
• only people nat finished in the role
• people for this is ‘heir primary
units type actu% ‘
aur-cs9311/21 php
COMP93*1 71T3
02
name. school, which a table mtAining
of tuple in r%ult
serving’
recent’
stUtingZat8)
s hm_’ld rot Head of
You must also waming messages are generated mwing the loading view
Heads may in mle
n . , start ins) which gives details the cument Heads of The view the
lollewing details about each
their
Since there is dirtplooking data in the ion table, will need ta ensure that you legitimate Heaas ot School. Apply the
filters:
• is af Schml’
• only people nat finished in the role
• people for this is ‘heir primary
units type actu% ‘
d of If or hm mark,
may define au
aur-cs9311/21 php
COMP93*1 71T3 two
Q2
name. school, which a table mtAining
of tuple in r%ult
serving’
recent’
stUtingZat8)
You shm_’ld rot Head of
Heads may in mle the
You must also waming messages are generated mwing the loading view
Find which the mark and who was Exvoss this as an SQL query in a
ttw
all such
20 t9.nctly 20), rr.rk to
to
the
management expresses ‘hat the university is too heavily dependent on international student enrdrnents. Write a
will them by indetng %æ-.tage of in
the in 0951.0952,
• the
2)
management the university is too heavily dependent on international student enrolments. Write a
( ttÆtwiIl them this by indetng %æ-.tage of intemgional in
the in the 0951,
• the
Each as
mal æn deter-nined Students.
‘type You of
students by Égt You that is as bath
international in the only consider the main semesters (S’ and S?) and only 2005
calculation using Moating values to the precisian
13
pævious q.tio
in numur M students enmlled in the unWsity
(FTE) A
is student in worth of
the FTE by at the total JOC enrol@i
that and 24
rte)
(Sl S2) from 2CC•O Sl to SZ the of students
FTE tuple in tin
u o:wtain the following
the in 09s2.
• tho Students in in
• the FTE uOC/ semester as
You should enralrnent information from the (i.e. no need Progr
calculation using Moating point values to achieve the high’S’ precisian
Q6
Sone the data in this database is a little sparse: example, of courses have no Course
staff we
examples a has very many Limas.
oof rer
• code and •n
• of this a
ingo a list
the &ble). tuAe in
but hag had any star la it acc.-ding database
30 but have had any staff
It
With the new timetabling IC each is to tacilities they require in classrooms as the
inputs ta the timetabling process. Mile an SQL provide a lisl of that a given lacilily, that might used as part
The a parameter giving part name w,) and names
of that a facility matching the at luility. Th.
