[Assignment Spec]  [SQL Schema]   [SQL Data]   [Grades+Rules]   [Examples]   [Testing]   [Submitting]

SQL Schema

The following is the SQL schema used in building the mymyunsw database:

-- COMP3311 25T2 Assignment 2
-- Schema for MyMyUNSW database

create domain TermCode as char(4) check (value ~'[12][01239]T[0123]');
create domain SubjectCode as char(8) check (value ~ '[A-Z]{4}[0-9]{4}');
create domain StreamCode as char(6) check (value ~ '[A-Z]{4}[A-Z-][A-Z123-]');
create domain ProgramCode as char(4) check (value ~ '[0-9]{4}');
create domain ZidValue as integer check (value between 1000000 and 9999999);
create type ResidentType as enum ('AUS','AUSPR','INTL','NZ');
create type UnitType as enum ('UNSW','faculty','school','department');
create type CareerType as enum ('UGRD','PGRD','RSCH','NAWD');
create type GradeType as enum
	('A', 'A+', 'A-', 'AF', 'AS', 'AW', 'B', 'B+', 'B-', 'C', 'C+',
	 'C-', 'CR', 'D', 'D+', 'D-', 'DN', 'E', 'E+', 'E-', 'EC',
	 'EM', 'F', 'FL', 'HD', 'NA', 'NC', 'NF', 'PE', 'PS', 'PW',
	 'RC', 'RD', 'RS', 'SY', 'UF', 'WD', 'WJ', 'XE');
create type ReqType as enum
	('core','elective','free','gened','stream','uoc');

create table Terms (
	id          integer,
	code        TermCode not null,
	starting    date,
	ending      date,
	description text,
	primary key (id)
);

create table Countries (
	id          integer,
	code        char(3) not null,
	name        text not null,
	primary key (id)
);

create table People (
	id          integer,
	zid         ZidValue unique not null,
	family_name text,
	given_names text,
	full_name   text,
	origin      integer references Countries(id),
	primary key (id)
);

create table Students (
	id          integer references People(id),
	status      ResidentType,
	primary key (id)
);

create table Staff (
	id          integer references People(id),
	primary key (id)
);

create table Orgunits (
	id          integer,
	code        varchar(10) not null,
	name        text not null,
	utype       UnitType not null,
	parent      integer references Orgunits(id),
	primary key (id)
);

create table Subjects (
	id          integer,
	code        SubjectCode not null, 
	title       text not null,
	uoc         integer not null check (uoc between 0 and 24),
	career      CareerType,
	owner       integer not null references Orgunits(id),
	primary key (id)
);

create table Courses (
	id          integer,
	subject     integer not null references Subjects(id),
	term        integer not null references Terms(id),
	convenor    integer references Staff(id),
	satisfact   integer check (satisfact between 0 and 100),
	nresponses  integer,
	primary key (id)
);

create table Streams (
	id          integer,
	code        StreamCode,
	name        text, -- not null,
	primary key (id)
);

create table Programs (
	id          integer,
	code        ProgramCode,
	name        text, -- not null,
	primary key (id)
);

create table Program_enrolments (
	id          integer,
	student     integer references Students(id),
	term        integer references Terms(id),
	program     integer references Programs(id),
	primary key (id)
);

create table Stream_enrolments (
	part_of     integer references Program_enrolments(id),
	stream      integer references Streams(id),
        primary key (part_of,stream)
);

create table Course_enrolments (
	student     integer references Students(id),
	course      integer references Courses(id),
	mark        integer check (mark between 0 and 100),
	grade       GradeType,
	primary key (student,course)
);

create table Requirements (
	id          integer,
	name        text not null,
	rtype       ReqType not null,
	min_req     integer check (min_req > 0),
	max_req     integer check (max_req > 0),
	acadobjs    text,
	for_stream  integer references Streams(id),
	for_program integer references Programs(id),
	constraint  OneOrOther check (
	                       for_stream is NULL and for_program is not null
	                       or
	                       for_stream is not null and for_program is null
	                       ),
	primary key (id)
);