CREATE TABLE People(
P_Num INT NOT NULL,
P_FirstName VARCHAR2(20 CHAR) NOT NULL,
P_LastName VARCHAR2(20 CHAR) NOT NULL,
P_Gender CHAR(1) NOT NULL,
P_DOB DATE NOT NULL,
P_Address VARCHAR2(150 BYTE) NOT NULL,
P_Nationality VARCHAR2 (15 CHAR) NOT NULL,
P_Postcode INT NOT NULL,
P_Photo CHAR(1) NOT NULL,
P_IC_Passport VARCHAR2(12 CHAR) NOT NULL,
Primary KEY (P_NUM)
);


CREATE TABLE Customer (
C_ID INT NOT NULL,
C_Phone INT NOT NULL,
C_Occupation VARCHAR2(20 CHAR) NOT NULL,
C_EmergencyContact INT NOT NULL,
C_HomeNum INT NOT NULL,
C_Age NUMBER(3) NOT NULL,
C_Signature CHAR(3) NOT NULL,
C_PickupService CHAR(3) NOT NULL,
C_DateOfJoin DATE NOT NULL,
C_CriminalRec CHAR(3) NOT NULL,
P_NUM INT NOT NULL,
Primary KEY (C_ID),
Foreign key (P_NUM) REFERENCES People(P_Num)
);

CREATE TABLE Staff (
S_ID INT NOT NULL,
S_MaritalStatus VARCHAR2(11 CHAR) NOT NULL,
S_HasVehicle CHAR(3) NOT NULL,
S_HireDate DATE NOT NULL,
S_LevelOfStudy VARCHAR2(20 CHAR) NOT NULL,
S_EmploymentStatus VARCHAR2(9 CHAR) NOT NULL,
S_AccNo VARCHAR2(20 CHAR) NOT NULL,
S_BankName VARCHAR2(20 CHAR) NOT NULL,
S_WorkSection VARCHAR2(15 CHAR) NOT NULL,
S_Title VARCHAR2(9 CHAR) NOT NULL,
P_NUM INT NOT NULL,
Primary key (S_ID),
Foreign key (P_NUM) REFERENCES People(P_Num)
);

CREATE TABLE Class_(
Class_ID INT NOT NULL,
Class_Num INT NOT NULL,
Class_Capacity NUMBER(2) NOT NULL,
Class_Location VARCHAR2(17 CHAR) NOT NULL,
Class_Name VARCHAR2(17 CHAR) NOT NULL,
Class_Type VARCHAR2(17 CHAR) NOT NULL,
Class_Description VARCHAR2(150 BYTE) NOT NULL,
Class_Facilities VARCHAR2(150 BYTE) NOT NULL,
Class_Period NUMBER(2,1) NOT NULL,
Class_Time DATE NOT NULL,
PRIMARY KEY (Class_ID)
);

CREATE TABLE Salary_Slip (
SS_Receipt INT NOT NULL,
SS_HrsPerMonth NUMBER(3) NOT NULL,
SS_Date DATE NOT NULL,
SS_GrossSalary NUMBER(7,2) NOT NULL,
SS_TotalEarning NUMBER(9,2) NOT NULL,
SS_NetIncome NUMBER(7,2) NOT NULL,
SS_Tax NUMBER(4,2) NOT NULL,
SS_Address VARCHAR2(150 BYTE) NOT NULL,
SS_Rate NUMBER(5,2) NOT NULL,
SS_Bonus NUMBER(7,2) NOT NULL,
S_ID INT NOT NULL,
Primary Key(SS_Receipt),
Foreign key (S_ID) REFERENCES Staff(S_ID)
);

CREATE TABLE Certification (
Cert_SerialNum INT NOT NULL,
Cert_Holder VARCHAR2(40 CHAR) NOT NULL,
Cert_ValidityPeriod VARCHAR(8 CHAR) NOT NULL,
Cert_Type VARCHAR2(7 CHAR) NOT NULL,
Cert_Description VARCHAR2(150 BYTE) NOT NULL,
Cert_Signature CHAR(3) NOT NULL,
Cert_Photo CHAR(3) NOT NULL,
Cert_Address VARCHAR2(150 BYTE) NOT NULL,
Cert_Logo CHAR(3) NOT NULL,
Cert_IssuedDate DATE NOT NULL,
S_ID INT NOT NULL,
Class_ID INT NOT NULL,
Primary key (Cert_SerialNum),
Foreign key (S_ID) REFERENCES Staff(S_ID),
Foreign key (Class_ID) REFERENCES Class_(Class_ID)
);

CREATE TABLE Cert_Class (
Cert_SerialNumber INT NOT NULL,
Class_ID INT NOT NULL,
Foreign key (Class_ID) REFERENCES Class_(Class_ID),
Foreign key (Cert_SerialNumber) REFERENCES Certification(Cert_SerialNum)
);

CREATE TABLE People_Class(
P_Num INT NOT NULL,
Class_ID INT NOT NULL,
Foreign KEY (P_num) REFERENCES People(P_num),
Foreign KEY (Class_ID) REFERENCES Class_(Class_ID)
);

CREATE TABLE Module (
M_ID INT NOT NULL,
M_Date DATE NOT NULL,
M_Time DATE NOT NULL,
M_Progress VARCHAR2 (10 CHAR) NOT NULL,
M_Comment VARCHAR2(100 BYTE) NOT NULL,
M_Name VARCHAR2(50 BYTE) NOT NULL,
M_Language VARCHAR2(15 CHAR) NOT NULL,
M_Type VARCHAR2(7 CHAR) NOT NULL,
M_Vol NUMBER(2) NOT NULL,
M_YearOfPubl DATE NOT NULL,
PRIMARY KEY (M_ID)
);

CREATE TABLE Attendance (
ATT_Num INT NOT NULL,
ATT_Name VARCHAR2(20 CHAR) NOT NULL,
ATT_Time DATE NOT NULL,
ATT_Department VARCHAR2(20 CHAR) NOT NULL,
ATT_Position VARCHAR(20 CHAR) NOT NULL,
ATT_Rate NUMBER(5,2) NOT NULL,
ATT_Workday CHAR(3) NOT NULL,
ATT_TotalHour NUMBER(4,2) NOT NULL,
ATT_Deduction NUMBER(7,2),
ATT_BalanceDue NUMBER(7,2),
Class_ID INT NOT NULL,
P_Num INT NOT NULL,
PRIMARY KEY (ATT_NUM),
FOREIGN KEY (CLASS_ID) REFERENCES Class_(CLass_ID),
FOREIGN KEY (P_num) REFERENCES People(P_num)
);

CREATE TABLE Insurance (
Ins_IdNum INT NOT NULL,
Ins_Type VARCHAR (15 CHAR),
Ins_CardNum VARCHAR2(20 CHAR) NOT NULL,
Ins_Date DATE NOT NULL,
Ins_Time DATE NOT NULL,
Ins_AmtofDamage VARCHAR2(15 CHAR) NOT NULL,
Ins_Description VARCHAR2(200 BYTE) NOT NULL,
Ins_Coverage VARCHAR2(200 BYTE) NOT NULL,
Ins_ExpDate DATE NOT NULL,
Ins_TotalCost NUMBER(7,2),
PRIMARY KEY (Ins_idNum)
);

CREATE TABLE Invoice (
I_Id INT NOT NULL,
I_DateSent DATE NOT NULL,
I_Status VARCHAR2(10 CHAR) NOT NULL,
I_Type VARCHAR2(10 CHAR) NOT NULL,
I_TotalAmount NUMBER(7,2),
I_ModifiedBy VARCHAR2(20 CHAR) NOT NULL,
I_ModifiedDate DATE NOT NULL,
I_CreatedDate DATE NOT NULL,
I_CreatedBy VARCHAR2(20 CHAR) NOT NULL,
I_PaymentTerm VARCHAR2(200 BYTE) NOT NULL,
M_ID INT NOT NULL,
PRIMARY KEY (I_id),
Foreign key (M_ID) REFERENCES Module(M_ID)
);

CREATE TABLE Payment (
PY_ID INT NOT NULL,
PY_Amount NUMBER(7,2) NOT NULL,
PY_Date DATE NOT NULL,
PY_Type VARCHAR2(20 CHAR) NOT NULL,
PY_Description VARCHAR2(150 BYTE) NOT NULL,
PY_Fee NUMBER(7,2) NOT NULL,
PY_Status VARCHAR2(20 CHAR) NOT NULL,
PY_Method VARCHAR2(20 CHAR) NOT NULL,
PY_Subtotal NUMBER(7,2) NOT NULL,
PY_Change NUMBER(7,2) NOT NULL,
C_ID INT NOT NULL,
I_Id INT NOT NULL,
M_ID INT NOT NULL,
PRIMARY KEY (PY_ID),
FOREIGN KEY (C_ID) REFERENCES Customer(c_ID),
FOREIGN KEY (I_ID) REFERENCES Invoice(I_iD),
FOREIGN KEY (M_ID) REFERENCES Module(M_id)
);

CREATE TABLE Building (
B_ID INT NOT NULL,
B_NoOfRoom NUMBER(2) NOT NULL,
B_NoOfParkingSpace NUMBER(2)  NOT NULL,
B_NoOfCounter NUMBER(2) NOT NULL,
B_NoOfDepartment NUMBER(2) NOT NULL,
B_NoOfFurniture NUMBER(2) NOT NULL,
B_NoOfRestRoom NUMBER(2) NOT NULL,
B_Canteen CHAR(3) NOT NULL,
B_NoOfVendingMach NUMBER(2)  NOT NULL,
B_NoOfFloor NUMBER(2) NOT NULL,
B_Pantry CHAR (3) NOT NULL,
Ins_IdNum INT NOT NULL,
PRIMARY KEY (B_ID),
FOREIGN KEY (Ins_idnum) REFERENCES Insurance(Ins_idnum)
);

CREATE TABLE Equipment (
EQ_ID INT NOT NULL,
EQ_Type VARCHAR2(15 CHAR) NOT NULL,
EQ_Name VARCHAR2(20 CHAR) NOT NULL,
EQ_Location VARCHAR2(30 CHAR) NOT NULL,
EQ_Warranty VARCHAR2(8 CHAR) NOT NULL,
EQ_Weight NUMBER(4,1) NOT NULL,
EQ_Height NUMBER(3,1) NOT NULL,
B_ID INT NOT NULL,
PRIMARY KEY (EQ_ID),
FOREIGN KEY (B_ID) REFERENCES Building(B_ID)
);

CREATE TABLE GPS (
GPS_ID INT NOT NULL,
GPS_SerialNum VARCHAR2(8 CHAR) NOT NULL,
GPS_Time DATE NOT NULL,
GPS_Date DATE NOT NULL,
GPS_Heading NUMBER (4,1) NOT NULL,
GPS_Speed NUMBER (3) NOT NULL,
GPS_Longitude VARCHAR2(11 CHAR) NOT NULL,
GPS_Latitude VARCHAR2(11 CHAR) NOT NULL,
GPS_Brand VARCHAR2(20 CHAR) NOT NULL,
GPS_ModelNum VARCHAR2(15 CHAR) NOT NULL,
EQ_ID INT NOT NULL,
Ins_IdNum INT NOT NULL,
PRIMARY KEY (GPS_ID),
Foreign KEY (EQ_ID) REFERENCES Equipment(EQ_ID),
Foreign KEY (Ins_Idnum) REFERENCES Insurance(Ins_idnum)
);



DROP TABLE People CASCADE CONSTRAINTS;
DROP TABLE Customer CASCADE CONSTRAINT;
DROP TABLE STAFF CASCADE CONSTRAINTS;
DROP TABLE CLASS_ CASCADE CONSTRAINTS;
DROP TABLE Salary_slip;
DROP TABLE Certification CASCADE CONSTRAINTS;
DROP TABLE CERT_CLASS;
DROP TABLE People_Class;
DROP TABLE Module CASCADE CONSTRAINTS;
DROP TABLE Attendance;
DROP TABLE Insurance CASCADE CONSTRAINTS;
DROP TABLE Invoice CASCADE CONSTRAINTS;
DROP TABLE Payment;
DROP TABLE BUILDING CASCADE CONSTRAINT;
DROP TABLE Equipment Cascade Constraint;
DROP TABLE GPS;