rem
rem  +---------------------------------------------------------------------+
rem  |  STUDENTACCOMMODATION.SQL 					   |
rem  |                                                                     |
rem  |  Create tables for the Student Accommodation Database	   	   |
rem  |                                                                     |
rem  |  								   |
rem  +---------------------------------------------------------------------+
rem

rem
rem  Create all the necessary tables.
rem

CREATE TABLE Employee (
employeeNo NUMBER(8) NOT NULL, 
title VARCHAR2(10) NOT NULL, 
firstName VARCHAR2(30) NOT NULL, 
middleName VARCHAR2(30), 
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,
PRIMARY KEY (employeeNo)
);

CREATE TABLE PaymentMethod (
pMethodNo NUMBER(2) NOT NULL, 
paymentMethod VARCHAR2(40) NOT NULL,
PRIMARY KEY (pMethodNo)
);

CREATE TABLE Flat(
flatNo NUMBER(8) NOT NULL, 
flatAddress VARCHAR2(80) NOT NULL, 
noOfRoomsInFlat NUMBER(1), 
PRIMARY KEY (flatNo)
);

CREATE TABLE FlatInspection(
flatNo NUMBER(8) NOT NULL, 
dateOfInspection DATE NOT NULL, 
comments VARCHAR2(40) NOT NULL, 
status CHAR NOT NULL, 
employeeNo NUMBER(8) NOT NULL, 
PRIMARY KEY (flatNo, dateOfInspection),
CONSTRAINT flatnumber FOREIGN KEY (flatNo) REFERENCES Flat(flatNo),
CONSTRAINT employeenumber FOREIGN KEY (employeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE Hall(
hallName VARCHAR2(20) NOT NULL, 
hallAddress VARCHAR2(80) NOT NULL, 
hallTelNo VARCHAR2(20) NOT NULL UNIQUE, 
hallFaxNo VARCHAR2(20) NOT NULL UNIQUE, 
noOfRoomsInHall NUMBER(1), 
managerEmployeeNo NUMBER(8) NOT NULL,
PRIMARY KEY (hallName),
CONSTRAINT manageremployeenumber FOREIGN KEY (managerEmployeeNo) REFERENCES Employee(employeeNo)
);

CREATE TABLE Room(
placeNo NUMBER(8) NOT NULL, 
roomNo NUMBER(8) NOT NULL, 
rentPerSemester NUMBER(6, 2) NOT NULL, 
hallName VARCHAR2(20) NOT NULL, 
flatNo NUMBER(8) NOT NULL,
PRIMARY KEY (placeNo),
CONSTRAINT roomhall UNIQUE(roomNo, hallName),
CONSTRAINT roomflat UNIQUE(roomNo, flatNo),
CONSTRAINT hallname FOREIGN KEY (hallName) REFERENCES Hall(hallName),
CONSTRAINT flatnumber1 FOREIGN KEY (flatNo) REFERENCES Flat(flatNo)
);

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),
studentSex CHAR NOT NULL, 
studentDOB DATE NOT NULL, 
studentType CHAR NOT NULL, 
studentStatus CHAR NOT NULL, 
accommodationTypeRequired CHAR NOT NULL, 
accommodationDuration NUMBER(1) NOT NULL,
PRIMARY KEY (studentNo)
);

CREATE TABLE Lease(
leaseNo NUMBER(8) NOT NULL, 
duration CHAR NOT NULL, 
dateStart DATE NOT NULL, 
dateLeave DATE NOT NULL, 
studentNo NUMBER(8) NOT NULL, 
placeNo NUMBER(8) NOT NULL,
PRIMARY KEY (leaseNo), 
CONSTRAINT placedatestart UNIQUE(placeNo, dateStart),
CONSTRAINT studentdatestart UNIQUE(studentNo, dateStart),
CONSTRAINT studentnumber FOREIGN KEY (studentNo) REFERENCES Student(studentNo),
CONSTRAINT placenumber FOREIGN KEY (placeNo) REFERENCES Room(placeNo)
);

CREATE TABLE Invoice(
invoiceNo NUMBER(8) NOT NULL, 
semester CHAR NOT NULL, 
dateDue DATE NOT NULL, 
datePaid DATE, 
leaseNo NUMBER(8) NOT NULL, 
pMethodNo NUMBER(2) NOT NULL,
PRIMARY KEY (invoiceNo),
CONSTRAINT leasenumber FOREIGN KEY (leaseNo) REFERENCES Lease(leaseNo),
CONSTRAINT paymentmethodnumber FOREIGN KEY (pMethodNo) REFERENCES PaymentMethod(pMethodNo)
);

CREATE TABLE Reminder(
invoiceNo NUMBER(8) NOT NULL, 
dateReminder1Sent DATE NOT NULL, 
dateReminder2Sent DATE, 
dateInterview DATE, 
comments VARCHAR2(40), 
PRIMARY KEY (invoiceNo),
CONSTRAINT invoicenumber FOREIGN KEY (invoiceNo) REFERENCES Invoice(invoiceNo)
);