COMP3311 Final Exam 26T2

The University of New South Wales COMP3311 Database Systems Final Exam 26T2

Database Systems

The database for the exam is concerned with university classes. Classes occur at a particular day/time each week in a particular room. Rooms have facilities like overhead projectors or microphones. There are different types of rooms: lecture theatres, tutorial rooms, etc. Different kinds of classes are scheduled in an appropriate kind of room (e.g. a lab class is scheduled in a computer lab). Students are scheduled to attend classes; whether they actually attend is another matter.

The following ER design gives an overview of a database to model this scenario:

The SQL schema below gives all of the implementation details.

Notes on the schema:

• all entity tables have a unique integer primary key called id
• the id values are not contiguous (i.e. not 1,2,3,4,5,...)
• a Class describes a particular class offering on a specified day/time
• Students can be scheduled to attend several Classes
• the attribute names used in the database are not necessarily exactly the same as those in the ER diagram

-- COMP3311 26T2 Final Exam Classes Database

create table Students (
	id          integer,
	name        text not null,
	d_o_birth	date not null,
	primary key (id)
);

create type RoomType as enum
	('Lecture Theatre','Tutorial Room','Computer Lab','Meeting Room');

create table Rooms (
	id          integer,
	name        text not null,
	rtype       RoomType not null,
	capacity    integer not null check (capacity > 0),
	primary key (id)
);

-- Tute-Lab classes involve a tutorial class in a tutorial room
-- followed immediately by a lab class generally in a computer lab

create type ClassType as enum
	('Lecture','Tutorial','Seminar','Lab Class','Tute-Lab');
create type Weekday as enum
	('Mon','Tue','Wed','Thu','Fri');

create table Classes (
	id          integer,
	course      char(8) not null check (course ~ '[A-Z]{4}[0-9]{4}'),
	ctype       ClassType not null,
	held_in     integer not null references Rooms(id),
	day_of_week Weekday not null,
	start_time  integer not null check (start_time between 9 and 20),
	end_time    integer not null check (end_time between 10 and 21),
	primary key (id)
);

create table Facilities (
	id          integer,
	name        text not null,
	primary key (id)
);

create table Has (
	room_id     integer references Rooms(id),
	facility_id integer references Facilities(id),
	nitems      integer not null check (nitems > 0),
	primary key (room_id,facility_id)
);

create table Attends (
	student_id  integer references Students(id),
	class_id    integer references Classes(id),
	primary key (student_id,class_id)
);

You should familiarise yourself with the schema before proceeding to solve the queries. It would also be useful to examine the database contents to ensure that you understand what all of the data represents. There is a dump of the database available in the file ~cs3311/web/26T2/exam-db/classes.dump, which you can also access online here.