DDL Create

-- NSCC Appication DDL Script
-- Group 2

CREATE TABLE nscc_Campus(
Campus_ID INTEGER PRIMARY KEY,
Campus_Name VARCHAR(50) NOT NULL
);

CREATE TABLE nscc_Program(
Program_ID INTEGER PRIMARY KEY,
Program_Name VARCHAR(100) NOT NULL,
Active CHAR(1) NOT NULL
);

CREATE TABLE nscc_Campus_Program(
Program_ID INTEGER REFERENCES nscc_Program(Program_ID),
Campus_ID INTEGER REFERENCES nscc_Campus(Campus_ID),
PRIMARY KEY (Program_ID, Campus_ID)
);

CREATE TABLE nscc_Country(
Country_Code CHAR(2) PRIMARY KEY,
Country_Name VARCHAR(50)
);

CREATE TABLE nscc_Province_State(
Province_State_Code CHAR(2),
Country_Code CHAR(2) REFERENCES nscc_Country(Country_Code),
PRIMARY KEY (Province_State_Code, Country_Code)
);

CREATE TABLE nscc_Phone(
Phone_ID INTEGER PRIMARY KEY,
Phone_Number VARCHAR(20) NOT NULL,
Is_Primary CHAR(1) NOT NULL
);

CREATE TABLE nscc_Citizenship(
Citizenship_ID INTEGER PRIMARY KEY,
Description VARCHAR(100) NOT NULL
);

CREATE TABLE nscc_Language(
Language_ID INTEGER PRIMARY KEY,
Language_Name VARCHAR(50) NOT NULL
);

CREATE TABLE nscc_School_Type(
School_Type_ID INTEGER PRIMARY KEY,
Type_School VARCHAR(50)
);

CREATE TABLE nscc_Applicant (
Applicant_ID INTEGER PRIMARY KEY,
SIN CHAR(9),
FirstName VARCHAR(30) NOT NULL,
MiddleName VARCHAR(30),
LastName VARCHAR(30) NOT NULL,
FirstName_Preferred VARCHAR(30),
LastName_Previous VARCHAR(30),
DOB DATE NOT NULL,
Gender CHAR(1) NOT NULL CHECK (Gender IN ('0','1')),
Apt_Number VARCHAR(10),
Street_Address1 VARCHAR(50) NOT NULL,
Street_Address2 VARCHAR(50),
City VARCHAR(30) DEFAULT 'Halifax',
County VARCHAR(30),
Province_State_Code CHAR(2) DEFAULT 'NS',
Province_State_Other VARCHAR(50),
Country_Code CHAR(2) NOT NULL,
Postal_Code VARCHAR(10) NOT NULL,
Phone_Home_ID INTEGER NOT NULL,
Phone_Work_ID INTEGER,
Phone_Cell_ID INTEGER,
Email VARCHAR(50) NOT NULL,
StudentNumber CHAR(8) UNIQUE,
Citizenship_ID INTEGER NOT NULL,
Citizenship_Other_Country_Code CHAR(2),
Criminal_Conviction CHAR(1) CHECK (Criminal_Conviction IN ('0','1')),
Child_Abuse_Registry CHAR(1) CHECK (Child_Abuse_Registry IN ('0','1')),
Disciplinary_Action CHAR(1) CHECK (Disciplinary_Action IN ('0','1')),
African_Canadian CHAR(1) CHECK (African_Canadian IN ('0','1')),
First_Nations CHAR(1) CHECK (First_Nations IN ('0','1')),
Current_ALP CHAR(1) CHECK (Current_ALP IN ('0','1')),
Has_Disability CHAR(1) CHECK (Has_Disability IN ('0','1')),
FirstLanguage_ID INTEGER NOT NULL,
FirstLanguage_Other VARCHAR(50)
);

CREATE TABLE nscc_Application(
Application_ID INTEGER PRIMARY KEY,
Submit_Date DATE NOT NULL,
Application_Fee NUMBER,
Paid CHAR(1) NOT NULL,
Applicant_ID NOT NULL REFERENCES nscc_Applicant(Applicant_ID)
);

CREATE TABLE nscc_Program_Choice(
Program_Choice_ID INTEGER PRIMARY KEY,
Preference INTEGER NOT NULL,
Application_ID INTEGER REFERENCES nscc_Application(Application_ID),
Program_ID INTEGER REFERENCES nscc_Program(Program_ID),
Campus_ID INTEGER REFERENCES nscc_Campus(Campus_ID)
);

CREATE TABLE nscc_Education(
Education_ID INTEGER PRIMARY KEY,
School_Name VARCHAR(50),
Date_Completed DATE,
Location VARCHAR(100),
School_Type INTEGER REFERENCES nscc_School_Type(School_Type_ID),
Applicant_ID INTEGER REFERENCES nscc_Applicant(Applicant_ID)
);

CREATE TABLE nscc_Payment(
Payment_ID INTEGER PRIMARY KEY,
Application_ID INTEGER REFERENCES nscc_Application(Application_ID),
Payment_Date DATE NOT NULL,
Amount NUMBER NOT NULL
);

CREATE TABLE nscc_Credit_Card_Company(
CC_Company_ID INTEGER PRIMARY KEY,
Company_Name VARCHAR(20) NOT NULL
);

CREATE TABLE Payment_Credit_Card(
Payment_ID INTEGER PRIMARY KEY REFERENCES nscc_Payment(Payment_ID),
CC_Company_ID INTEGER REFERENCES nscc_Credit_Card_Company(CC_Company_ID),
Transaction_ID VARCHAR(100)
);

CREATE TABLE nscc_Payment_Cheque(
Payment_ID INTEGER PRIMARY KEY REFERENCES nscc_Payment(Payment_ID),
Cheque_Number VARCHAR(20) NOT NULL,
Transit_Number VARCHAR(20) NOT NULL,
Financial_Institution VARCHAR(20) NOT NULL,
Account_Number VARCHAR(20) NOT NULL
);

CREATE TABLE nscc_Payment_Money_Order(
Payment_ID INTEGER PRIMARY KEY REFERENCES nscc_Payment(Payment_ID),
Money_Order_Number VARCHAR(20) NOT NULL
);