database.sql

CREATE TABLE DEAKIN_UNITS (
  	Apartment_Num 		NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 3 PRIMARY KEY,
  	Address			VARCHAR2 (100) 	NOT NULL,
  	Bedroom_Availability	NUMBER (3,0)	NOT NULL
);

CREATE TABLE DORMITORIES (
  	Flat_Num		NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 3 PRIMARY KEY,
  	Address			VARCHAR2 (100) 	NOT NULL,
  	Dorm_Availability	NUMBER (3,0)	NOT NULL
);

CREATE TABLE HALLS (
  	Hall_Num	NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 3 PRIMARY KEY,
  	Name		VARCHAR2 (50) 	NOT NULL,
  	Address		VARCHAR2 (100) 	NOT NULL,
  	Phone		VARCHAR2 (20)	NOT NULL,
  	Manager		VARCHAR2 (50)	NOT NULL
);


CREATE TABLE STAFFS (
  	ID 		NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 9 PRIMARY KEY,
  	Name 		VARCHAR2 (50) 	NOT NULL,
  	Gender 		VARCHAR2 (1) 		,
  	DOB		DATE 			,
  	Address		VARCHAR2 (100) 		,
  	Phone		VARCHAR2 (20)		,
 	Email		VARCHAR2 (50) 	NOT NULL,
  	Position	VARCHAR2 (20)		,
  	Department	VARCHAR2 (50) 		,
  	Dep_Phone	VARCHAR2 (20)		,
  	Office_Num	VARCHAR2 (5)
);

CREATE TABLE PROGRAMS (
  	Course_Num 	NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 6 PRIMARY KEY,
  	Title		VARCHAR2 (50) 	NOT NULL,
  	Room_Num	VARCHAR2 (5)	NOT NULL,
  	Department	VARCHAR2 (50) 	NOT NULL,
  	Director_ID	NUMBER (6,0)	NOT NULL,
CONSTRAINT FK_PROGRAMS_Director_ID
	FOREIGN KEY (Director_ID)
	REFERENCES STAFFS (ID)
	ON DELETE CASCADE
);

CREATE TABLE ROOMS
(
	Place_Num 	NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 18 PRIMARY KEY,
  	Room_Num 	VARCHAR2 (4) 	NOT NULL,
  	Bed_Num 	VARCHAR2 (4)		,
  	Rent_Rate 	NUMBER (6,2) 	NOT NULL, --daily rate in AUD
  	Residence_Num 	NUMBER (6,0)	NOT NULL,
CONSTRAINT FK_ROOMS_Residence_Num_to_Hall_Num
	FOREIGN KEY (Residence_Num)
	REFERENCES HALLS (Hall_Num),
CONSTRAINT FK_ROOMS_Residence_Num_to_Apartment_Num
	FOREIGN KEY (Residence_Num)
	REFERENCES DEAKIN_UNITS (Apartment_Num),
CONSTRAINT FK_ROOMS_Residence_Num_to_Flat_Num
	FOREIGN KEY (Residence_Num)
	REFERENCES DORMITORIES (Flat_Num)
);


CREATE TABLE STUDENTS (
  	ID 		NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 12 PRIMARY KEY,
  	Name 		VARCHAR2 (50) 	NOT NULL,
  	Gender 		VARCHAR2 (1) 	NOT NULL,
  	DOB 		DATE 		NOT NULL,
  	Address 	VARCHAR2 (100) 	NOT NULL,
  	Nationality 	VARCHAR2 (50) 	NOT NULL,
 	Email 		VARCHAR2 (50) 	NOT NULL,
  	Mobile 		VARCHAR2 (20)		,
 	Degree 		VARCHAR2 (20) 	NOT NULL,
 	School 		VARCHAR2 (20) 	NOT NULL,
 	Mentor_ID 	NUMBER (6,0)		,
 	Adviser_ID 	NUMBER (6,0)	NOT NULL,
 	Course_Num 	NUMBER (6,0)	NOT NULL,
 	Place_Num 	NUMBER (6,0)	NOT NULL,
CONSTRAINT FK_STUDENTS_Mentor_ID
	FOREIGN KEY (Mentor_ID)
	REFERENCES STUDENTS (ID)
	ON DELETE CASCADE,
CONSTRAINT FK_STUDENTS_Adviser_ID
	FOREIGN KEY (Adviser_ID)
	REFERENCES STAFFS (ID)
	ON DELETE CASCADE,
CONSTRAINT FK_STUDENTS_Course_Num
	FOREIGN KEY (Course_Num)
	REFERENCES PROGRAMS (Course_Num)
	ON DELETE CASCADE,
CONSTRAINT FK_STUDENTS_Place_Num
	FOREIGN KEY (Place_Num)
	REFERENCES ROOMS (Place_Num)
	ON DELETE CASCADE
);

CREATE TABLE GUARDIANS (
  	ID		NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 6 PRIMARY KEY,
  	Name		VARCHAR2 (50) 	NOT NULL,
  	Relationship	VARCHAR2 (20) 	NOT NULL,
  	Address		VARCHAR2 (100) 	NOT NULL,
  	Phone		VARCHAR2 (20)	NOT NULL,
  	Student_ID	NUMBER (6,0)	NOT NULL,
CONSTRAINT FK_GUARDIANS_Student_ID
	FOREIGN KEY (Student_ID)
	REFERENCES STUDENTS (ID)
	ON DELETE CASCADE
);

CREATE TABLE Q_LIST (
  	Q_Num		NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 12 PRIMARY KEY,
	Current_Status 	VARCHAR2 (10) 	NOT NULL,
	Special_Needs 	VARCHAR2 (100)		,
	Comments 	VARCHAR2 (200)		,
  	Student_ID	NUMBER (6,0)	NOT NULL,
CONSTRAINT FK_Q_LIST_Student_ID
	FOREIGN KEY (Student_ID)
	REFERENCES STUDENTS (ID)
	ON DELETE CASCADE
);


CREATE TABLE LOGS (
  	Log_Num		NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 6 PRIMARY KEY,
	Access_Date	DATE 		NOT NULL,
  	User_ID		NUMBER (6,0)	NOT NULL,
CONSTRAINT FK_LOGS_User_ID_to_Students_ID 
	FOREIGN KEY (User_ID) 
	REFERENCES STUDENTS (ID) 
	ON DELETE CASCADE,
CONSTRAINT FK_LOGS_User_ID_to_Staff_ID 
	FOREIGN KEY (User_ID) 
	REFERENCES STAFFS (ID) 
	ON DELETE CASCADE
);

CREATE TABLE INSPECTIONS (
  	Inspection_Num	NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 6 PRIMARY KEY,
  	Inspection_Date	DATE 		NOT NULL,
  	Satisfaction	VARCHAR2 (1) 	NOT NULL,
  	Comments	VARCHAR2 (200)		,
  	Staff_ID	NUMBER (6,0)	NOT NULL,
	Place_Num	NUMBER (6,0)	NOT NULL, 
CONSTRAINT FK_INSPECTIONS_Staff_ID
	FOREIGN KEY (Staff_ID)
	REFERENCES STAFFS (ID)
	ON DELETE CASCADE,
CONSTRAINT FK_INSPECTIONS_Place_Num
	FOREIGN KEY (Place_Num)
	REFERENCES ROOMS (Place_Num)
	ON DELETE CASCADE
);


CREATE TABLE LEASES (
  	Lease_Num	NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 12 PRIMARY KEY,
  	Duration	NUMBER (1,0)	NOT NULL, -- semester
  	Start_Date	DATE 		NOT NULL,
  	End_Date	DATE 		NOT NULL,
  	Place_Num	NUMBER (6,0)	NOT NULL,
  	Student_ID	NUMBER (6,0)	NOT NULL,
CONSTRAINT FK_LEASES_Place_Num
	FOREIGN KEY (Place_Num)
	REFERENCES ROOMS (Place_Num)
	ON DELETE CASCADE,
CONSTRAINT FK_LEASES_Student_ID
	FOREIGN KEY (Student_ID)
	REFERENCES STUDENTS (ID)
	ON DELETE CASCADE
);


CREATE TABLE INVOICES (
  	Invoice_Num		NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 6 PRIMARY KEY,
  	Term			VARCHAR2 (10) 	NOT NULL, --Term_and_Year
  	Due_Date		DATE 		NOT NULL,
  	Paid_Date		DATE 		,
  	Pay_Method		VARCHAR2 (10) 	NOT NULL,
  	First_Remind_Date	DATE			,
  	Second_Remind_Date	DATE			,
  	Lease_Num		NUMBER (6,0)	NOT NULL,
CONSTRAINT FK_INVOICES_Lease_Num
	FOREIGN KEY (Lease_Num)
	REFERENCES LEASES (Lease_Num)
	ON DELETE CASCADE
);