[Assignment Spec]  [SQL Schema]  [Examples]

Overview

The mymyunsw database is a slimmed version of the real myUNSW system containing Engineering students, programs, courses and timetabled classes. The schema consists of 15 tables covering organisational structure, people, academic programs, subjects, course offerings, timetabled classes, and enrolments.

SQL Schema

The database uses the following schema:

-- mymyunsw (Engineering-only, slim schema)

-- Semesters / terms
CREATE TABLE semesters (
  id        integer PRIMARY KEY,
  unswid    integer UNIQUE NOT NULL,
  year      integer NOT NULL,
  term      text    NOT NULL,
  name      text,
  starting  date    NOT NULL,
  ending    date    NOT NULL
);

-- Organisational unit types (e.g., Faculty, School, ...)
CREATE TABLE orgunit_types (
  id   integer PRIMARY KEY,
  name text UNIQUE NOT NULL
);

-- Organisational units (faculties, schools, centres, ...)
CREATE TABLE orgunits (
  id       integer PRIMARY KEY,
  utype    integer NOT NULL REFERENCES orgunit_types(id),
  name     text    NOT NULL,
  longname text,
  unswid   text
);

-- Hierarchy: owner contains member
CREATE TABLE orgunit_groups (
  owner  integer NOT NULL REFERENCES orgunits(id),
  member integer NOT NULL REFERENCES orgunits(id),
  PRIMARY KEY(owner, member)
);

-- People (students, staff, ...)
CREATE TABLE people (
  id     integer PRIMARY KEY,
  unswid integer UNIQUE NOT NULL,  -- 7-digit zID numeric part
  family text,
  given  text,
  name   text NOT NULL,
  email  text NOT NULL
);

-- Students (subset of people)
CREATE TABLE students (
  id    integer PRIMARY KEY REFERENCES people(id),
  stype text NOT NULL
);

-- Academic programs (e.g., 3707 Computer Science)
CREATE TABLE programs (
  id        integer PRIMARY KEY,
  code      text NOT NULL,
  name      text NOT NULL,
  offeredby integer NOT NULL REFERENCES orgunits(id)
);

-- A student's enrolment in a program during a semester
CREATE TABLE program_enrolments (
  id       integer PRIMARY KEY,
  student  integer NOT NULL REFERENCES students(id),
  semester integer NOT NULL REFERENCES semesters(id),
  program  integer NOT NULL REFERENCES programs(id),
  wam      numeric
);

-- Subjects (e.g., COMP3311 Database Systems)
CREATE TABLE subjects (
  id        integer PRIMARY KEY,
  code      text NOT NULL,
  name      text NOT NULL,
  uoc       integer,
  offeredby integer NOT NULL REFERENCES orgunits(id)
);

-- A course is a subject offered in a particular semester
CREATE TABLE courses (
  id       integer PRIMARY KEY,
  subject  integer NOT NULL REFERENCES subjects(id),
  semester integer NOT NULL REFERENCES semesters(id)
);

-- Class types (LEC, TUT, LAB, ...)
CREATE TABLE class_types (
  id     integer PRIMARY KEY,
  unswid text UNIQUE NOT NULL,  -- e.g., LEC / TUT / LAB
  name   text NOT NULL
);

-- Buildings on campus
CREATE TABLE buildings (
  id     integer PRIMARY KEY,
  unswid text,
  name   text NOT NULL,
  campus text
);

-- Rooms within buildings
CREATE TABLE rooms (
  id       integer PRIMARY KEY,
  unswid   text,
  name     text NOT NULL,
  building integer REFERENCES buildings(id),
  capacity integer NOT NULL CHECK (capacity > 0)
);

-- Timetabled classes for a course
CREATE TABLE classes (
  id        integer PRIMARY KEY,
  course    integer NOT NULL REFERENCES courses(id),
  room      integer NOT NULL REFERENCES rooms(id),
  ctype     integer NOT NULL REFERENCES class_types(id),
  dayofwk   integer NOT NULL CHECK (dayofwk BETWEEN 0 AND 6),
      -- 0=Mon, 1=Tue, 2=Wed, 3=Thu, 4=Fri, 5=Sat, 6=Sun
  starttime integer NOT NULL CHECK (starttime BETWEEN 0 AND 24),
  endtime   integer NOT NULL CHECK (endtime BETWEEN 0 AND 24),
  startdate date    NOT NULL,
  enddate   date    NOT NULL,
  repeats   integer,
  CHECK (starttime < endtime),
  CHECK (startdate <= enddate)
);

-- A student's enrolment in a course
CREATE TABLE course_enrolments (
  student integer NOT NULL REFERENCES students(id),
  course  integer NOT NULL REFERENCES courses(id),
  mark    integer,
  grade   text,
  PRIMARY KEY(student, course)
);

Notes

• semesters.unswid is a 4-digit code (e.g., 5024 = 2002 S1). The id is an internal surrogate key.
• people.unswid is the numeric part of the zID (e.g., 2281773 for z2281773).
• orgunit_groups represents the organisational hierarchy: the owner contains the member. Walking this hierarchy upward from a program's offeredby leads to the Faculty.
• classes.dayofwk: 0 = Monday, 1 = Tuesday, ..., 6 = Sunday.
• classes.starttime / endtime: integer hours (e.g., 9 = 09:00, 14 = 14:00).
• class_types.unswid: short codes such as LEC, TUT, LAB.
• buildings.unswid may be NULL for some buildings; use COALESCE(b.unswid, '?') when displaying building codes.
• rooms.capacity gives the maximum number of students a room can hold.
• course_enrolments has a composite primary key (student, course), which prevents duplicate enrolments.

You should familiarise yourself with this schema before attempting the assignment. It would also be useful to explore the database contents to ensure you understand what all of the data represents.