drop table t_applicant CASCADE CONSTRAINTS;
drop table t_enrolled_student CASCADE CONSTRAINTS;
drop table t_app_interest CASCADE CONSTRAINTS;
drop table t_interest CASCADE CONSTRAINTS;
drop table t_app_email CASCADE CONSTRAINTS;
drop table t_attends CASCADE CONSTRAINTS;
drop table t_financial_aid CASCADE CONSTRAINTS;
drop table t_financial_aid_student CASCADE CONSTRAINTS;
drop table t_faculty CASCADE CONSTRAINTS;
drop table t_university CASCADE CONSTRAINTS;
drop table t_full_faculty CASCADE CONSTRAINTS;
drop table t_course CASCADE CONSTRAINTS;
drop table t_class CASCADE CONSTRAINTS;
drop table t_class_time_stamp CASCADE CONSTRAINTS;
drop table t_enroll_student_class CASCADE CONSTRAINTS;
drop table t_faculty_teach_class CASCADE CONSTRAINTS;
drop sequence applicant_seq;
drop sequence university_seq;
drop sequence faculty_seq;
drop sequence financial_aid_seq;
drop sequence class_seq;

/* Applicant information */
CREATE TABLE t_applicant (
PID NUMBER(9) CONSTRAINT applicant_pk PRIMARY KEY,
fname VARCHAR2(30) CONSTRAINT applicant_fname_nn NOT NULL,
mname VARCHAR2(30),
lname VARCHAR2(30) CONSTRAINT applicant_lname_nn NOT NULL,
p_address VARCHAR2(200),
p_city VARCHAR2(50),
p_state VARCHAR2(50),
p_code VARCHAR2(15),
p_country VARCHAR2(50),
c_address VARCHAR2(200) CONSTRAINT applicant_c_address_nn NOT NULL,
c_city VARCHAR2(50) CONSTRAINT applicant_c_city_nn NOT NULL,
c_state VARCHAR2(50),
c_code VARCHAR2(15),
c_country VARCHAR2(50),
bdate DATE CONSTRAINT applicant_bdate_nn NOT NULL,
h_phone VARCHAR2(20),
w_phone VARCHAR2(20),
residency CHAR(1) DEFAULT 'Y',
ethnic VARCHAR2(50),
gre_verbal NUMBER(3) CONSTRAINT applicant_greverb_nn NOT NULL,
gre_quant NUMBER(3) CONSTRAINT applicant_grequant_nn NOT NULL,
gre_analytical NUMBER(3) CONSTRAINT applicant_greanaly_nn NOT NULL,
sex CHAR(1),
toefl NUMBER(3),
status VARCHAR2(10) DEFAULT 'Enrolled',
comments VARCHAR2(2000),
deposit CHAR(1) DEFAULT 'Y',
exempt102 CHAR(1) DEFAULT 'Y',
gre_aw NUMBER(3) DEFAULT 000,
CONSTRAINT exempt_102_cc CHECK ((exempt102='Y') OR (exempt102='N')),
CONSTRAINT dep_paid_cc CHECK ((deposit='Y') OR (deposit='N')),
CONSTRAINT app_sex_cc CHECK ((sex='M') OR (sex='F')),
CONSTRAINT app_res_cc CHECK ((residency='Y') OR (residency='N')), 
CONSTRAINT app_status_cc CHECK ((status='Denied') OR (status='Deferred') OR (status='Withdrawn') OR (status = 'Enrolled') OR (status = 'Pending')));

CREATE TABLE t_faculty (
FID NUMBER(9) CONSTRAINT faculty_pk PRIMARY KEY,
fname VARCHAR2(30) CONSTRAINT faculty_fname_nn NOT NULL,
mname VARCHAR2(30),
lname VARCHAR2(30) CONSTRAINT faculty_lname_nn NOT NULL,
address VARCHAR2(200),
city VARCHAR2(50),
state VARCHAR2(50),
country VARCHAR2(50),
code VARCHAR2(15),
h_phone VARCHAR2(20),
o_phone VARCHAR2(20),
salary NUMBER(8,2),
bldg VARCHAR2(40),
room NUMBER(5),
first_app DATE,
curr_app DATE,
grants VARCHAR2(150),
startsdate DATE,
appttime NUMBER(4,1),
status VARCHAR2(10),
app_lenght VARCHAR2(20), /* This is only for the adjunct, it states how long they are expected to stay*/
CONSTRAINT fac_status_cc CHECK ((status='Full') OR (status='Adjunct')));

CREATE TABLE t_full_faculty (
full_fid NUMBER(9) CONSTRAINT full_fac_pk PRIMARY KEY,
prev_review DATE,
next_review DATE,
tenure CHAR(1),
rank VARCHAR2(50),
lst_leave DATE,
nxt_leave DATE,
CONSTRAINT full_faculty_fk FOREIGN KEY (full_fid) REFERENCES t_faculty (FID),
CONSTRAINT fac_tenure_cc CHECK ((tenure='Y') OR (tenure='N')));

CREATE TABLE t_enrolled_student (
PID NUMBER(9) CONSTRAINT enrolled_student_pk PRIMARY KEY,
studyarea CHAR(2),
program VARCHAR2(10),
sem_enrolled VARCHAR2(12),
advisor NUMBER(9),
CONSTRAINT advisor_fk FOREIGN KEY (advisor) REFERENCES t_full_faculty (full_fid),
CONSTRAINT program_cc CHECK ((program='MSIS') OR (program='MSLS') OR (program='Ph.D.') OR (program='undergrad') OR (program='CAS') OR (program='Exchange Student')), 
CONSTRAINT enrolled_fk FOREIGN KEY (PID) REFERENCES t_applicant(PID),
CONSTRAINT studyarea_cc CHECK ((studyarea='IS') OR (studyarea='LS')),
CONSTRAINT pid_uk UNIQUE(PID));

CREATE TABLE t_interest (
IID NUMBER(9) CONSTRAINT interest_pk PRIMARY KEY,
description VARCHAR2(70),
int_type CHAR(2));

CREATE TABLE t_app_interest (
AID NUMBER(9), 
IID NUMBER(9),
CONSTRAINT app_interest PRIMARY KEY (AID, IID),
CONSTRAINT applicant_interest_fk FOREIGN KEY (AID) REFERENCES t_applicant (PID),
CONSTRAINT interest_fk FOREIGN KEY (IID) REFERENCES t_interest (IID));

CREATE TABLE t_app_email (
AppID NUMBER(9),
email VARCHAR2(50),
types VARCHAR2(15),
CONSTRAINT app_email_pk PRIMARY KEY (AppID, email),
CONSTRAINT aid_fk FOREIGN KEY (AppID) REFERENCES t_applicant (PID),
CONSTRAINT t_app_email_types_cc CHECK ((types='preferred') OR (types='secondary') OR (types='home') OR (types='work') OR (types='other')));

CREATE TABLE t_university (
UnivID NUMBER(9) CONSTRAINT university_pk PRIMARY KEY,
u_name VARCHAR2(255) CONSTRAINT university_name_nn NOT NULL, 
city VARCHAR2(50),
state VARCHAR2(50),
country VARCHAR2(50));

CREATE TABLE t_attends (
Univ_ID NUMBER(9),
App_ID NUMBER(9),
gpa number(3,2) CONSTRAINT attends_gpa_nn NOT NULL,
degree VARCHAR2(100),
major VARCHAR2(100),
st_date DATE,
end_date DATE,
grad_date DATE,
CONSTRAINT app_attends_pk PRIMARY KEY (Univ_ID, App_ID,degree),
CONSTRAINT attends_univ_fk FOREIGN KEY (Univ_ID) REFERENCES t_university (UnivID),
CONSTRAINT attends_app_fk FOREIGN KEY (App_ID) REFERENCES t_applicant (PID));

CREATE TABLE t_financial_aid (
FinID NUMBER(9) CONSTRAINT financial_pk PRIMARY KEY,
award VARCHAR(50), 
source VARCHAR(50),
criteria VARCHAR(500),
credithrs NUMBER(2,1));

CREATE TABLE t_financial_aid_student (
fin_ID NUMBER(9),
stu_ID NUMBER(9),
stime DATE,
etime DATE,
CONSTRAINT fin_aid_student_pk PRIMARY KEY (fin_ID, Stu_ID),
CONSTRAINT fin_aid_fk FOREIGN KEY (fin_ID) REFERENCES t_financial_aid(FinID),
CONSTRAINT stu_aid_fk FOREIGN KEY (stu_ID) REFERENCES t_enrolled_student(PID));

CREATE TABLE t_course (
CID VARCHAR2(10) CONSTRAINT course_pk PRIMARY KEY,
description VARCHAR2(1000),
name VARCHAR2(50),
interest_area VARCHAR2(100),
credit NUMBER(2,1));

CREATE TABLE t_class (
ClID NUMBER(9) CONSTRAINT class_pk PRIMARY KEY,
semester VARCHAR2(6),
CONSTRAINT semester_ck CHECK (semester in ('spring','fall','summer')),
year VARCHAR2(4),
course_id VARCHAR2(10),
section number(3) default '001',
CONSTRAINT class_course_fk FOREIGN KEY (course_id) REFERENCES t_course (CID));

CREATE TABLE t_enroll_student_class (
class_ID NUMBER(9),
stud_ID NUMBER(9),
CONSTRAINT enroll_stu_class_pk PRIMARY KEY (class_ID, stud_ID),
CONSTRAINT enr_class_fk FOREIGN KEY (class_id) REFERENCES t_class (ClID),
CONSTRAINT enr_stud_fk FOREIGN KEY (stud_ID) REFERENCES t_enrolled_student (PID));

CREATE TABLE t_faculty_teach_class (
f_ID NUMBER(9),
cl_id NUMBER(9),
CONSTRAINT faculty_teach_class_pk PRIMARY KEY (f_ID, cl_id),
CONSTRAINT faculty_teach_fk FOREIGN KEY (f_ID) REFERENCES t_faculty (FID),
CONSTRAINT class_taught_fk FOREIGN KEY (cl_id) REFERENCES t_class (ClID));

CREATE TABLE t_class_time_stamp (
cl_id NUMBER(9),
stime DATE,
etime DATE,
bldg_name varchar2(45),
room NUMBER(5),
day VARCHAR2(10),
CONSTRAINT time_stamp_pk PRIMARY KEY (cl_id, stime, bldg_name, room, day), 
CONSTRAINT time_stamp_fk FOREIGN KEY (cl_id) REFERENCES t_class (ClID));

CREATE SEQUENCE applicant_seq
START WITH 700000001;
CREATE SEQUENCE university_seq;
CREATE SEQUENCE faculty_seq;
CREATE SEQUENCE financial_aid_seq;
CREATE SEQUENCE class_seq;

CREATE INDEX app_ln_idx on t_applicant (lname);
CREATE INDEX faculty_ln_idx on t_faculty (lname);
CREATE INDEX course_name_idx on t_course (name);