Q1

With the new UNSW timetabling system, the LIC of each course is required to specify what facilities they require in their classrooms as one of the inputs to the timetabling process. Write an SQL function to provide a list of rooms that contain a given facility, that might be used as part of this process. The function takes a parameter giving part of the name of a facility (e.g. "Lectern microphone" or "lectern mic" or ...) and returns the names of all Rooms that have a facility matching the parameter and the name of the actual facility. The function is defined as:

create function Q1(text) returns set of FacilityRecord as $ SQL statement $ language sql;

Note that the FacilityRecord type is already included in the database as:

create type FacilityRecord as (room text, facility text);

The room field of each tuple should be a Rooms.longname value, while the facility filed should be a Facilities.description value.

To allow some of flexibility in search for facilities, the function parameter should be treated like a pattern, and should match all facilities whose description contains the string given as the parameter. For example, 'lectern mic' would matche ‘Lectern microphone', while ‘microph' would match 'Lectern microphone', 'Neck microphone' or 'Radio microphone'. Matching should be case insensitive.

Note that, according to the database, no rooms in UNSW have whiteboards. The database reflects reality to some extent, but is clearly not a 100% accurate and certainly not a 100% complete mapping of reality.

Q2 

A function returning the current semester is useful in many contexts (e.g. determining all currently enrolled students). We wish to write a generalisation of this function: one that takes a date and tells us which semester it falls in. This might seem simple enough, given that the Terms table contains starting and ending dates for each semester. However, we want to take a more liberal view of the extent of semesters. We will normally consider that each semester effectively starts one week before the starting date recorded in the Terms table. Also, we'll consider that the previous semester extends to the day before the effective starting date of the following semester. If the ending date of semester t1 is less than one week before the starting date of the next semester t2, then treat the starting date of t2 as the day after the ending date of t1.

Some examples to clarify.

If T1.starting='2005-02-28' and T2.ending='2005-01-31', then the effective starting date for T1 is '2005-02-21' and the effective ending date for T2 is '2005-02-20'(the day before the effective start of T1). If T3.starting='2009-11-30' and T4.ending='2009-11-24', then the effective starting date for T3 is '2009-11-25', and the effective ending date for T4stays the same '2009-11-24'. Write a PLpgSQL function called Q2 to find which semester a particular date falls in. Use the following function header:

create or replace function Q2(_day date) returns text as $ ... PLpgSQL code ... $ language plpgsql;

The parameter is a date (e.g. '2005-12-25'). The function returns the term name (e.g. 05s1) of the semester containing the specified date. If a date is given that corresponds to no known term in the database (e.g. before the first term in the database or after the last term in the database), then NULL is returned.

Q3

The transcript function supplied in the database assumes that the only way that a student can get credit towards their degree is by enrolling in a subject for which they have the pre-reqs and passing that subject. In fact, students can obtain various other kinds of "credit" towards their study to help them finish their degree:

• "advanced standing" gives students credit for some course at UNSW based on a similar course completed at another institution (or in an incomplete degree at UNSW); the student is allocated UOC for the UNSW subject, but it does not count towards their WAM; however, for purposes such as pre-requisites, it is as if the student took the UNSW course

• "substitution" allows a student to take one subject in place of a core subject in their program (e.g. if the original core subject is not available and it is the student's final semester of study); the student is given the UOC for the course actually taken and the course taken counts in their WAM; however, the course taken may be used as a "stand-in" for the substituted course in determining whether they have met their degree requirements

• "exemption" is where a student is deemed to have completed a course at UNSW based on a similar course at another institution, but is not awarded any UOC for the UNSW course; however, they can use it as a pre-requisite for further study at UNSW Information about such enrolment variations is stored in the two tables:

Variations(student, program, subject, vtype, intequiv, extequiv, ...)
ExternalSubjects(id, extsubj, institution, yearoffered, equivto)

where each Variations tuple shows a variation for one student for a given subject towards a particular program. The vtype field indicates what kind of variation it is (advstanding', 'substitution','eemption'). The intequiv field references a UNSW subject if the variation is based on a UNSW subject. The extequiv references a tuple in the ExternalSubjects table if the variation is based on a subject studied at another institution. Only one of intequiv or extequiv will be "not null". You should examine the contents of these two tables, as well as the file called
"variations.sql" containing details of some of the variations in the database.

A transcript function has already been loaded into the database, along with a definition of the TranscriptRecord type. You can grab a copy of the transcript()

function definition using PostgreSQL's \ef command (see the PostgreSQL manual  for details).
create type TranscriptRecord as (code char(8), -- e.g. 'COMP3311' term char(4), -- e.g. '12s1'
name text, -- e.g. 'Database Systems'
mark integer, -- e.g. 75
grade char(2), -- e.g. 'DN'
uoc integer -- e.g. 6);

create function transcript(_sid integer) returns setof TranscriptRecord as $ ... PLpgSQL code ...
$ language plpgsql;

You should write a new version of the transcript() function called Q3() which includes variations as well as regular course enrolments. You can use any or all of the code from the supplied transcript() function in developing your Q9() function.

Any variations are displayed at the end of the transcript, after the regular courses, but before the WAM calculation. It should still produce the WAM and UOC count, like the original transcript function did, but they will be computed slightly differently (see below). Note that the Q3() function has exactly the same type signature as that noted above for thetranscript() function.

Each variation produces two TranscriptRecord tuples. The first tuple gives details of which UNSW subject is being "varied", while the second tuple gives details of the equivalent subject that is used as the basis for the variation.

Download:- check.rar