rem
rem  +---------------------------------------------------------------------+
rem  |  STUDENTRESULTS.SQL 						   |
rem  |                                                                     |
rem  |  Create tables for the Student Results Database	   	   	   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Department(
departmentNo NUMBER(8) NOT NULL, 
departmentName VARCHAR2(40) NOT NULL UNIQUE, 
location VARCHAR2(40) NOT NULL, 
HODstaffNo NUMBER(8),
PRIMARY KEY (departmentNo)
);

CREATE TABLE Staff(
staffNo NUMBER(8) NOT NULL, 
title VARCHAR2(10) NOT NULL, 
firstName VARCHAR2(30) NOT NULL, 
lastName VARCHAR2(30) NOT NULL, 
address VARCHAR2(80) NOT NULL, 
workTelExt VARCHAR2(4) NOT NULL, 
homeTelNo VARCHAR2(20) NOT NULL, 
empEmailAddress VARCHAR2(100), 
socialSecurityNumber VARCHAR2(12) NOT NULL UNIQUE, 
DOB DATE NOT NULL, 
position VARCHAR2(20) NOT NULL, 
sex CHAR NOT NULL, 
salary NUMBER(8,2) NOT NULL, 
dateStarted DATE NOT NULL,
departmentNo NUMBER(8) NOT NULL,
PRIMARY KEY (staffNo),
CONSTRAINT departmentnumber FOREIGN KEY (departmentNo) REFERENCES Department(departmentNo)
);

ALTER TABLE Department
ADD CONSTRAINT hodstaffnumber FOREIGN KEY (HODstaffNo) REFERENCES Staff(staffNo);

CREATE TABLE Course (
courseNo NUMBER(8) NOT NULL, 
courseName VARCHAR2(40) NOT NULL UNIQUE, 
courseLevel CHAR NOT NULL,
entranceRequirements VARCHAR2(40) NOT NULL,
maxNumber NUMBER(4) NOT NULL,
departmentNo NUMBER(8) NOT NULL, 
courseLeaderNo NUMBER(8) NOT NULL, 
PRIMARY KEY (courseNo),
CONSTRAINT departmentnumber1 FOREIGN KEY (departmentNo) REFERENCES Department(departmentNo),
CONSTRAINT courseleadernumber FOREIGN KEY (courseLeaderNo) REFERENCES Staff(staffNo)
);

CREATE TABLE Module(
moduleNo NUMBER(8) NOT NULL, 
moduleName VARCHAR2(40) NOT NULL UNIQUE, 
semesterDelivered VARCHAR2(3) NOT NULL, 
moduleAims VARCHAR2(100) NOT NULL, 
moduleObjectives VARCHAR2(100) NOT NULL, 
moduleSyllabus VARCHAR2(200) NOT NULL, 
moduleResources VARCHAR2(100) NOT NULL, 
moduleModeOfAssessment VARCHAR2(40) NOT NULL, 
moduleCoordinatorStaffNo NUMBER(8) NOT NULL, 
departmentNo NUMBER(8) NOT NULL,
PRIMARY KEY (moduleNo),
CONSTRAINT modulecoordinatorstaffnumber FOREIGN KEY (moduleCoordinatorStaffNo) REFERENCES Staff(staffNo),
CONSTRAINT departmentnumber2 FOREIGN KEY (departmentNo) REFERENCES Department(departmentNo)
);

CREATE TABLE Student(
studentNo NUMBER(8) NOT NULL, 
studentFirstName VARCHAR2(30) NOT NULL, 
studentMiddleName VARCHAR2(30), 
studentLastName VARCHAR2(30) NOT NULL, 
studentHomeStreet VARCHAR2(40) NOT NULL, 
studentHomeCity VARCHAR2(15) NOT NULL, 
studentHomeState VARCHAR2(15) NOT NULL, 
studentHomeZipCode VARCHAR2(9) NOT NULL, 
studentHomeTelNo VARCHAR2(20), 
familyHomeStreet VARCHAR2(40), 
familyHomeCity VARCHAR2(15), 
familyHomeState VARCHAR2(15), 
familyHomeZipCode VARCHAR2(9),  
familyHomeTelNo VARCHAR2(20), 
studentDOB DATE NOT NULL,  
studentSex CHAR NOT NULL, 
nationality VARCHAR2(40) NOT NULL, 
courseNo NUMBER(8) NOT NULL, 
advisorStaffNo NUMBER(8) NOT NULL,
PRIMARY KEY (studentNo),
CONSTRAINT coursenumber FOREIGN KEY (courseNo) REFERENCES Course(courseNo),
CONSTRAINT advisorstaffnumber FOREIGN KEY (advisorStaffno) REFERENCES Staff(staffNo)
);

CREATE TABLE StudentModule(
studentNo NUMBER(8) NOT NULL, 
moduleNo NUMBER(8) NOT NULL,
PRIMARY KEY (studentNo, moduleNo),
CONSTRAINT studentnumber FOREIGN KEY (studentNo) REFERENCES Student(studentNo),
CONSTRAINT modulenumber FOREIGN KEY (moduleNo) REFERENCES Module(moduleNo)
);

CREATE TABLE StudentResult(
studentNo NUMBER(8) NOT NULL, 
moduleNo NUMBER(8) NOT NULL, 
attempt NUMBER(1), 
attemptDate DATE NOT NULL, 
mark NUMBER(3) NOT NULL, 
proposal VARCHAR2(2) NOT NULL, 
additionalComments VARCHAR2(40) NOT NULL,
PRIMARY KEY (studentNo, moduleNo, attempt),
CONSTRAINT studentmodulenumber FOREIGN KEY (studentNo, moduleNo) REFERENCES StudentModule(studentNo, moduleNo)
);

CREATE TABLE CourseModule(
courseNo NUMBER(8) NOT NULL, 
moduleNo NUMBER(8) NOT NULL,
PRIMARY KEY (courseNo, moduleNo),
CONSTRAINT coursenumber1 FOREIGN KEY (courseNo) REFERENCES Course(courseNo),
CONSTRAINT studentnumber1 FOREIGN KEY (moduleNo) REFERENCES Module(moduleNo)
);