\connect - deptdb CREATE TABLE "programmes" ( "ucascode" character(8) NOT NULL, "name" character varying(128) NOT NULL); REVOKE ALL on "programmes" from PUBLIC; GRANT SELECT on "programmes" to "nobody"; CREATE TABLE "staff" ( "staff" character(24) NOT NULL, "title" character(5), "surname" character varying(32) NOT NULL, "forename" character(20) NOT NULL, "othernames" character varying(64), "post" character varying(32) DEFAULT 'lecturer' NOT NULL, "adminrole" character varying(64), "room" character(8), "telephone" character(14), "officehours" character(24), "username" character(14), "email" character varying(32), "webpage" character varying(64), "webphoto" character varying(64), "homeaddress" character varying(128), "hometel" character(14), "homeemail" character varying(32)); REVOKE ALL on "staff" from PUBLIC; GRANT SELECT on "staff" to "nobody"; CREATE TABLE "students" ( "candno" character(12) NOT NULL, "student" character(24) NOT NULL, "title" character(5), "surname" character varying(32), "forename" character(20), "othernames" character varying(64), "dob" date, "address" character varying(128), "telephone" character(14), "homeaddress" character varying(128), "hometel" character(14), "mobile" character(14), "username" character(14), "email" character varying(32), "webphoto" character varying(64), "ucascode" character(8), "postgrad" character(8), "fees" character(10) DEFAULT 'home', "regstatus" character(4), "mode" character(4) DEFAULT 'ft', "slevel" int2 DEFAULT 1, "attempt" int2 DEFAULT 1, "ptutor" character(24), "tgroup" character(2), "studno" character(12), "comment" character varying(128), CONSTRAINT "students_slevel" CHECK (slevel BETWEEN 0 AND 9), CONSTRAINT "students_attempt" CHECK (attempt BETWEEN 0 AND 9)); REVOKE ALL on "students" from PUBLIC; GRANT SELECT on "students" to "nobody"; CREATE TABLE "shistories" ( "candno" character(12) NOT NULL, "student" character(24) NOT NULL, "ucascode" character(8) NOT NULL, "fees" character(10) DEFAULT 'home', "regstatus" character(4), "mode" character(4) DEFAULT 'ft', "slevel" int2 DEFAULT 1, "attempt" int2 DEFAULT 1 NOT NULL, "ayear" character(7) DEFAULT defaultayear ( ) NOT NULL, "progressioncode" character(4), "comment" character varying(64), "startdate" date, "enddate" date, CONSTRAINT "shistories_slevel" CHECK (slevel BETWEEN 0 AND 9), CONSTRAINT "shistories_attempt" CHECK (attempt BETWEEN 0 AND 9), CONSTRAINT "shistories_ayear" CHECK (ayear BETWEEN '1970-71' AND '2036-37')); CREATE TABLE "courses" ( "course" character(14) NOT NULL, "name" character varying(128) NOT NULL, "clevel" int2, "semester" character(2), "value" numeric(2,1), "examweight" numeric(5,2), "postgrad" bool, "comment" character varying(64), "syllabus" character varying(256) DEFAULT 'not entered', "webpage" character varying(64), CONSTRAINT "courses_clevel" CHECK (clevel BETWEEN 0 AND 9), CONSTRAINT "courses_value" CHECK (value BETWEEN 0 AND 2), CONSTRAINT "courses_examweight" CHECK (examweight BETWEEN 0 AND 100)); REVOKE ALL on "courses" from PUBLIC; GRANT SELECT on "courses" to "nobody"; CREATE TABLE "cstaffallocs" ( "course" character(14) NOT NULL, "staff" character(24) NOT NULL, "role" character(12) DEFAULT 'lecturer' NOT NULL, "semester" character(2) NOT NULL, "ayear" character(7) DEFAULT defaultayear ( ) NOT NULL, CONSTRAINT "cstaffallocs_ayear" CHECK (ayear BETWEEN '1970-71' AND '2036-37' OR ayear = '')); REVOKE ALL on "cstaffallocs" from PUBLIC; GRANT SELECT on "cstaffallocs" to "nobody"; CREATE TABLE "cregistrations" ( "candno" character(12), "student" character(24) NOT NULL, "course" character(14) NOT NULL, "cattempt" int2 DEFAULT 1 NOT NULL, "ayear" character(7) DEFAULT defaultayear ( ) NOT NULL, "cwptresult" numeric(5,2), "result" numeric(5,2), "finalresult" numeric(5,2), "resultcode" character, "progressioncode" character(4), CONSTRAINT "cregistrations_cattempt" CHECK (cattempt BETWEEN 1 AND 9), CONSTRAINT "cregistrations_ayear" CHECK (ayear BETWEEN '1970-71' AND '2036-37')); REVOKE ALL on "cregistrations" from PUBLIC; GRANT SELECT on "cregistrations" to "nobody"; CREATE TABLE "celements" ( "course" character(14) NOT NULL, "cetype" character(8) DEFAULT 'lecture' NOT NULL, "hoursweek" numeric(3,1), "compulsory" bool DEFAULT 'y'); CREATE TABLE "cegroups" ( "course" character(14) NOT NULL, "cetype" character(8) DEFAULT 'lecture' NOT NULL, "cegroup" character(4) DEFAULT 'n/a' NOT NULL); CREATE TABLE "timetable" ( "course" character(24) NOT NULL, "cetype" character(8) DEFAULT 'lecture' NOT NULL, "cegroup" character(4) DEFAULT 'n/a' NOT NULL, "semester" character(2) DEFAULT '?' NOT NULL, "room" character(8) DEFAULT '?', "day" character(3) DEFAULT '?' NOT NULL, "hour" character(2) DEFAULT '?' NOT NULL, "comment" character varying(64), CHECK (hour BETWEEN '1' AND '23' OR hour = '' OR hour = '?')); CREATE TABLE "cegstaffallocs" ( "course" character(14) NOT NULL, "cetype" character(8) DEFAULT 'lecture' NOT NULL, "cegroup" character(4) DEFAULT 'n/a' NOT NULL, "staff" character(24) NOT NULL, "semester" character(2), "ayear" character(7) DEFAULT defaultayear ( ) NOT NULL, CONSTRAINT "cegstaffallocs_ayear" CHECK (ayear BETWEEN '1970-71' AND '2036-37' OR ayear = '')); CREATE TABLE "cegstudallocs" ( "course" character(14) NOT NULL, "cetype" character(8) DEFAULT 'lecture' NOT NULL, "cegroup" character(4) DEFAULT 'n/a' NOT NULL, "student" character(24) NOT NULL, "ayear" character(7) DEFAULT defaultayear ( ) NOT NULL, CONSTRAINT "cegstudallocs_ayear" CHECK (ayear BETWEEN '1970-71' AND '2036-37' OR ayear = '')); CREATE TABLE "cwprojtests" ( "course" character(14) NOT NULL, "cetype" character(12) DEFAULT 'course work', "number" int2 NOT NULL, "description" character varying(64), "cwdueout" date, "deadline" date, "weight" numeric(5,2) DEFAULT 1 NOT NULL, "staff" character(24), CONSTRAINT "cwprojtests_number" CHECK (number BETWEEN 0 AND 99), CONSTRAINT "cwprojtests_weight" CHECK (weight BETWEEN 0 AND 100)); CREATE TABLE "absences" ( "course" character(14) NOT NULL, "cetype" character(8) DEFAULT 'tutorial' NOT NULL, "student" character(24) NOT NULL, "date" date DEFAULT text 'today' NOT NULL, "reason" character(8) DEFAULT 'not known', "comment" character varying(64), "considered" bool); CREATE TABLE "defaulters" ( "course" character(14) NOT NULL, "number" int2 NOT NULL, "student" character(24) NOT NULL, "date" date, "reason" character(8) DEFAULT 'not known', "comment" character varying(64), "considered" bool, CONSTRAINT "defaulters_number" CHECK (number BETWEEN 0 AND 99)); CREATE TABLE "csubmissions" ( "course" character(14) NOT NULL, "number" int2 NOT NULL, "complete" bool DEFAULT 'n', "date" date DEFAULT text 'today', CONSTRAINT "csubmissions_number" CHECK (number BETWEEN 0 AND 99)); CREATE TABLE "cwptresults" ( "course" character(14) NOT NULL, "number" int2 NOT NULL, "student" character(24) NOT NULL, "cwptattempt" int2 DEFAULT 1 NOT NULL, "date" date DEFAULT text 'today', "raw" numeric(5,2), "scaled" numeric(5,2), "comment" character varying(64), CONSTRAINT "cwptresults_number" CHECK (number BETWEEN 1 AND 99), CONSTRAINT "cwptresults_cwptattempt" CHECK (cwptattempt BETWEEN 1 AND 9)); CREATE TABLE "cwptoverall" ( "course" character(14) NOT NULL, "student" character(24) NOT NULL, "date" date DEFAULT text 'today', "raw" numeric(5,2), "scaled" numeric(5,2), "comment" character varying(64)); CREATE TABLE "examresults" ( "course" character(14) NOT NULL, "candno" character(12) NOT NULL, "date" date DEFAULT text 'today', "raw" numeric(5,2), "scaled" numeric(5,2), "comment" character varying(64)); CREATE TABLE "warnings" ( "student" character(24) NOT NULL, "date" datetime DEFAULT text 'now' NOT NULL, "cetype" character(12) NOT NULL, "description" character varying(64)); CREATE FUNCTION "currentsecond" ( ) RETURNS int4 AS 'SELECT int(date_part(''seconds'',''now''::datetime)) AS RESULT' LANGUAGE 'SQL'; CREATE FUNCTION "currentyear" ( ) RETURNS int4 AS 'SELECT int(date_part(''year'',current_date)) AS RESULT' LANGUAGE 'SQL'; CREATE FUNCTION "defaultayear" ( ) RETURNS text AS 'SELECT (text(currentyear()) || ''-'') || substr(text(currentyear()+1),3) AS RESULT' LANGUAGE 'SQL'; COPY "programmes" FROM stdin; G140 Mathematics GG14 Mathematics and Statistics GG15 Mathematics and Computer Science G5N2 Computing, Operational Research and Statistics for Business CG131 roger don't delete \. COPY "staff" FROM stdin; CJF Dr Fox Chris \N lecturer \N \N 7856 \N mas01cjf C.Fox@gold.ac.uk http://homepages.gold.ac.uk/chris/ \N \N \N \N Matt Mr Bernstein Matt \N sysadmin \N 6,25SJ 7855 \N mas01mb matt@gold.ac.uk \N \N \N \N matt@theBachChoir.org.uk none \N none none \N lecturer Null member of staff \N none \N \N \N \N \N \N \N \N RAS Dr Sugden Roger \N lecturer DST, DOS 14,25SJ 7861 \N maa01ras roger@mcs.gold.ac.uk \N \N \N \N \N LIP Dr Pettit Lawrence Ian lecturer EO 15,25SJ 7863 \N maa01lip \N \N \N \N \N \N \. COPY "students" FROM stdin; 01091823 Smith, J. \N Smith John \N \N \N \N \N \N \N \N \N \N CG131 \N home FF F 1 1 CJF \N \N \N 31415927 pieman Mr Simple Simon \N 03-12-1945 Broccoli Cross, London SE4 \N \N \N \N \N simplesimon@fairyland.uk.com \N CG131 \N home \N ft 1 1 Matt \N \N \N 27182818 eman Dr Goode Ebenezer R 06-06-1967 Cloud 9 \N \N \N \N \N ebenezer@prodigy.net \N CG132 \N home \N ft 1 1 Matt \N \N \N 2 two Mr Dentity Duel I 05-17-1979 2 The Tree Tops \N \N \N \N tutu tutu@hotmail.com \N CG132 \N home \N ft 1 1 Matt \N \N (as if we weren't "before") 1 one Miss Nitt Ursula \N 05-16-1979 Unit 1, Industrial Estate \N \N \N 0246 802468 unitt unitt@pobox.com \N CG131 \N home \N ft 1 1 CJF \N \N getting silly now 3 three Mr Pull Terry \N 02-23-1981 Three Tree Tops, Three Tree Tops Lane, Forest Dale, SE123 4FS 020 8312 2346 \N \N \N trio trinity@church.uk \N CG131 \N home \N ft 1 1 CJF \N \N \N 5 five Mr Brubeck Dave \N 05-05-1955 Back in 5 \N \N \N \N five takefive@jazz.net \N CG133 \N home \N ft 1 1 Matt \N \N \N 5657 James Miss James Sue \N 12-14-1999 Neat Street 999 666 \N \N \N \N \N \N CG133 \N home \N ft 1 1 none \N \N \N 1925 Astaire Mr Astaire Fred \N 01-01-2001 Can hardly deel the Earth beneath my feet 2367854934 \N \N \N fred \N \N CG133 \N home \N ex 2 1 CJF \N \N \N 5432 Rogers Ms Rogers Ginger \N 02-01-1934 \N \N \N \N \N \N \N \N \N \N home \N ft 1 1 \N \N \N \N 4 four Mr Davis Miles \N 04-04-1944 Jazz Heaven \N \N \N \N four \N \N CG131 \N home \N ft 1 1 RAS \N \N \N \. COPY "shistories" FROM stdin; 31415927 pieman CG131 home \N ft 1 1 1999-00 \N \N \N \N \. COPY "courses" FROM stdin; CISJGL Juggling 0 \N 0.3 80.00 f intellectually challenging not entered microsoft.com CIS199 Mattology 1 1 1.0 80.00 f \N not entered \N M407 Very Hard Sums 4 2 2.0 100.00 \N \N not entered \N ST177 Lies, Damn Lies and Statistics 1 1 1.0 50.00 f \N not entered \N M666 Introduction to Subtraction 1 2 0.5 20.00 \N \N not entered \N ST311 Sampling Techniques 3 1 0.5 80.00 \N impossible not entered \N CIS198 Chrisology 1 1 1.0 80.00 f \N not entered \N ST220 Simulation 2 1 0.5 80.00 f \N not entered \N \. COPY "cstaffallocs" FROM stdin; CIS198 CJF lecturer 1 1999-00 CIS199 Matt lecturer 2 1999-00 M666 CHC lecturer 2 1999-00 ST220 LIP lecturer 1 1999-00 \. COPY "cregistrations" FROM stdin; 3141527 pieman CIS198 1 1999-00 \N \N \N \N \N 4 four CIS199 1 1999-00 \N \N \N \N \N 5 five CIS199 1 1999-00 \N \N \N \N \N 3 three CIS198 3 1998-99 \N \N \N \N \N 4 four CIS198 2 1999-00 \N \N \N \N \N 2 two CIS198 9 1999-00 \N \N \N \N \N 5657 James CIS198 3 1999-00 \N \N \N \N \N 5657 James ST177 1 1998-99 \N \N \N \N \N 5657 James ST177 2 1999-00 \N \N \N \N \N 1925 Astaire ST177 1 1999-00 \N \N \N \N \N 1925 Astaire CIS198 1 1998-99 \N \N \N \N \N 1925 Astaire CISJGL 1 1998-99 \N \N \N \N \N 1925 Astaire M407 1 1999-00 \N \N \N \N \N 1925 Astaire CIS199 1 1998-99 \N \N \N \N \N 4 four M666 1 1998-99 \N \N \N \N \N 5 five M666 1 1998-99 \N \N \N \N \N 5 five M666 2 1999-00 \N \N \N \N \N \. COPY "celements" FROM stdin; CIS198 Exam \N t CIS199 EXAM \N t ST220 exam \N t st220 cwk \N t \. COPY "cegroups" FROM stdin; M666 Lab A \. COPY "timetable" FROM stdin; ST311 lecture n/a 1 10 1St J Mon ? too early! \. COPY "cegstaffallocs" FROM stdin; \. COPY "cegstudallocs" FROM stdin; \. COPY "cwprojtests" FROM stdin; \. COPY "absences" FROM stdin; \. COPY "defaulters" FROM stdin; \. COPY "csubmissions" FROM stdin; \. COPY "cwptresults" FROM stdin; st220 1 one 1 10-14-1999 38.00 56.00 \N st220 1 two 1 10-14-1999 45.00 60.00 \N st220 2 one 1 11-14-1999 50.00 50.00 \N st220 2 two 1 11-14-1999 60.00 60.00 \N \. COPY "cwptoverall" FROM stdin; st220 one 01-21-2000 \N \N \N \. COPY "examresults" FROM stdin; st220 1 05-17-2000 48.00 48.00 \N \. COPY "warnings" FROM stdin; \. CREATE UNIQUE INDEX "programmes_pkey" on "programmes" using btree ( "ucascode" "bpchar_ops" ); CREATE UNIQUE INDEX "staff_pkey" on "staff" using btree ( "staff" "bpchar_ops" ); CREATE UNIQUE INDEX "students_pkey" on "students" using btree ( "student" "bpchar_ops" ); CREATE UNIQUE INDEX "shistories_pkey" on "shistories" using btree ( "candno" "bpchar_ops", "ucascode" "bpchar_ops", "ayear" "bpchar_ops" ); CREATE UNIQUE INDEX "courses_pkey" on "courses" using btree ( "course" "bpchar_ops" ); CREATE UNIQUE INDEX "cstaffallocs_pkey" on "cstaffallocs" using btree ( "course" "bpchar_ops", "staff" "bpchar_ops", "role" "bpchar_ops", "semester" "bpchar_ops", "ayear" "bpchar_ops" ); CREATE UNIQUE INDEX "cregistrations_pkey" on "cregistrations" using btree ( "student" "bpchar_ops", "course" "bpchar_ops", "cattempt" "int2_ops" ); CREATE UNIQUE INDEX "celements_pkey" on "celements" using btree ( "course" "bpchar_ops", "cetype" "bpchar_ops" ); CREATE UNIQUE INDEX "cegroups_pkey" on "cegroups" using btree ( "course" "bpchar_ops", "cetype" "bpchar_ops", "cegroup" "bpchar_ops" ); CREATE UNIQUE INDEX "timetable_pkey" on "timetable" using btree ( "course" "bpchar_ops", "cetype" "bpchar_ops", "cegroup" "bpchar_ops", "semester" "bpchar_ops", "day" "bpchar_ops", "hour" "bpchar_ops" ); CREATE UNIQUE INDEX "cegstaffallocs_pkey" on "cegstaffallocs" using btree ( "course" "bpchar_ops", "cetype" "bpchar_ops", "cegroup" "bpchar_ops", "staff" "bpchar_ops", "ayear" "bpchar_ops" ); CREATE UNIQUE INDEX "cegstudallocs_pkey" on "cegstudallocs" using btree ( "course" "bpchar_ops", "cetype" "bpchar_ops", "cegroup" "bpchar_ops", "student" "bpchar_ops", "ayear" "bpchar_ops" ); CREATE UNIQUE INDEX "cwprojtests_pkey" on "cwprojtests" using btree ( "course" "bpchar_ops", "number" "int2_ops" ); CREATE UNIQUE INDEX "absences_pkey" on "absences" using btree ( "course" "bpchar_ops", "cetype" "bpchar_ops", "student" "bpchar_ops" ); CREATE UNIQUE INDEX "defaulters_pkey" on "defaulters" using btree ( "course" "bpchar_ops", "number" "int2_ops", "student" "bpchar_ops" ); CREATE UNIQUE INDEX "csubmissions_pkey" on "csubmissions" using btree ( "course" "bpchar_ops", "number" "int2_ops" ); CREATE UNIQUE INDEX "cwptresults_pkey" on "cwptresults" using btree ( "course" "bpchar_ops", "number" "int2_ops", "student" "bpchar_ops", "cwptattempt" "int2_ops" ); CREATE UNIQUE INDEX "cwptoverall_pkey" on "cwptoverall" using btree ( "course" "bpchar_ops", "student" "bpchar_ops" ); CREATE UNIQUE INDEX "examresults_pkey" on "examresults" using btree ( "course" "bpchar_ops", "candno" "bpchar_ops" ); CREATE UNIQUE INDEX "warnings_pkey" on "warnings" using btree ( "student" "bpchar_ops", "date" "datetime_ops", "cetype" "bpchar_ops" );