Database Development Guidelines

• Create a script that creates your tables and fills them with sample data
• Having "Drop Table" statements at the beginning allows the script to be run within the already created database to reset the structure and data.
• From with PostgreSQL:
  • start psql
  • use the command "\i scriptfilename" to run it
• From SQL Server:
  • start SQL Query Analyser
  • Open query file

Example of Script:

drop table prerequisite;
drop table enrolment;
drop table student;
drop table subject;
drop table lecturer;

CREATE TABLE "lecturer" (
 "lectno" int4 PRIMARY KEY,
 "surname" varchar(20),
 "firstname" varchar(20),
 "location" varchar(10),
 "supervisor" int4 DEFAULT null
   REFERENCES lecturer ( lectno )
   ON DELETE SET NULL
   ON UPDATE CASCADE
   );

REVOKE ALL on "lecturer" from PUBLIC;
GRANT SELECT, REFERENCES on "lecturer" to PUBLIC;
GRANT ALL on "lecturer" to "retallic";

CREATE TABLE "subject" (
 "subcode" character(7) PRIMARY KEY,
 "subname" varchar(30),
 "lecturer" int4
   REFERENCES lecturer(lectno)
   ON DELETE SET NULL
   ON UPDATE CASCADE,
 "fee" numeric(9,2));

REVOKE ALL on "subject" from PUBLIC;
GRANT SELECT, REFERENCES on "subject" to PUBLIC;
GRANT ALL on "subject" to "retallic";

CREATE TABLE "prerequisite" (
 "subcode" character(7)
   REFERENCES subject(subcode)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
 "prereq" character(7)
   REFERENCES subject(subcode)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
   PRIMARY KEY (subcode, prereq));

REVOKE ALL on "prerequisite" from PUBLIC;
GRANT SELECT, REFERENCES on "prerequisite" to PUBLIC;
GRANT ALL on "prerequisite" to "retallic";

CREATE TABLE "student" (
 "id" int4 PRIMARY KEY,
 "surname" varchar(30) NOT NULL,
 "firstname" varchar(20),
 "email" varchar(40),
 "course" varchar(30),
 "enrolled" date DEFAULT 'now()',
 "paid" numeric(9,2) DEFAULT 0.00);

REVOKE ALL on "student" from PUBLIC;
GRANT SELECT, REFERENCES on "student" to PUBLIC;
GRANT ALL on "student" to "retallic";

CREATE TABLE "enrolment" (
 "studid" int4 REFERENCES student ( id )
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 "subcode" character(7) REFERENCES subject ( subcode)
   ON DELETE RESTRICT
   ON UPDATE CASCADE, 
 "year" int4,
 "semester" int4 CHECK (semester IN (0, 1, 2)),
 "grade" character(2) CHECK (grade IN ('A','B','C','D','N','NA','S')),
   PRIMARY KEY (studid, subcode, year, semester));

REVOKE ALL on "enrolment" from PUBLIC;
GRANT SELECT, REFERENCES on "enrolment" to PUBLIC;
GRANT ALL on "enrolment" to "retallic";

   INSERT INTO "lecturer" VALUES (110,'Martin','Mary','B1.26 ',Null);
   INSERT INTO "lecturer" VALUES (111,'McEwan','Noel','B1.26 ',110);
   INSERT INTO "lecturer" VALUES (112,'Rice','Phil','B1.14 ',110);
   INSERT INTO "lecturer" VALUES (113,'Scott','Philip','B1.14 ',110);
   INSERT INTO "lecturer" VALUES (116,'Retallick','Brian','B2.20 ',112);

   INSERT INTO "subject" VALUES ('BITCA1','Computer Architecture 1',112,100.00);
   INSERT INTO "subject" VALUES ('BITCA2','Computer Architecture 2',112,120.00);
   INSERT INTO "subject" VALUES ('BITCNE','Computer Networks',113,150.00);
   INSERT INTO "subject" VALUES ('BITDBA','Database',116,100.0);
   INSERT INTO "subject" VALUES ('BITITF','IT Fun',111,150.00);

   INSERT INTO "student" VALUES
   (123456,'Retallick','Brian','B.Retallick@latrobe.edu.au','Computing','24-feb-2002',0.00);
   INSERT INTO "student" VALUES 
   (123458,'Hewitt','Lleyton','lhewitt@students.latrobe.edu.au', 
   'Business/Computing','24-feb-2003',800.00);
   INSERT INTO "student" VALUES 
   (987654,'Capriati','Jennifer','jcap2@smallpond', 
   'Business/Computing','24-apr-2003',200.00);
   INSERT INTO "student" VALUES 
   (123460,'Johansson','Thomas','tommy@tennis.se', 
   'Business','20-jul-2002',900.00);
   INSERT INTO "student" VALUES 
   (987655,'Hingis','Martina','martina@ironbark.geneve.ch', 
   'Computing','07-jul-2002',350.00);
   INSERT INTO "student" VALUES 
   (987660,'Hewitt','Jaslyn','jhewitt@students.latrobe.edu.au', 
   'Computing','24-feb-2003',350.00);

   INSERT INTO "enrolment" VALUES (123456,'BITDBA',2002,1,'N ');
   INSERT INTO "enrolment" VALUES (123456,'BITITF',2002,1,'B ');
   INSERT INTO "enrolment" VALUES (123456,'BITCA1',2002,2,'C ');
   INSERT INTO "enrolment" VALUES (123458,'BITITF',2003,1,'A ');
   INSERT INTO "enrolment" VALUES (123458,'BITDBA',2003,1,'B ');
   INSERT INTO "enrolment" VALUES (123458,'BITCNE',2003,2,'C ');
   INSERT INTO "enrolment" VALUES (123458,'BITCA1',2003,2,'A ');
   INSERT INTO "enrolment" VALUES (987654,'BITDBA',2003,1,'A ');
   INSERT INTO "enrolment" VALUES (987660,'BITDBA',2002,2,'NA');
   INSERT INTO "enrolment" VALUES (987660,'BITCA1',2003,2,'NA');
   INSERT INTO "enrolment" VALUES (987655,'BITDBA',2002,2,'C ');
   INSERT INTO "enrolment" VALUES (987655,'BITCNE',2003,1,'A ');
   INSERT INTO "enrolment" VALUES (987655,'BITCA1',2003,2,'N ');

   INSERT INTO "prerequisite" VALUES ('BITCNE','BITITF');
   INSERT INTO "prerequisite" VALUES ('BITCA1','BITITF');
   INSERT INTO "prerequisite" VALUES ('BITCA1','BITDBA');
   INSERT INTO "prerequisite" VALUES ('BITCA2','BITCA1');