INF 282 Final 1

CREATE TABLE stephensow1_final_Dept (
    Dept_Code CHAR(5) PRIMARY KEY, Dept_Name VARCHAR2(32),
    Dept_Phone VARCHAR2(16), College_Name VARCHAR2(32));

CREATE TABLE stephensow1_final_Advisor (
    Advisor_ID CHAR(5) PRIMARY KEY, Advisor_LName VARCHAR2(16),
    Advisor_Office VARCHAR2(4), Advisor_Bldg VARCHAR2(32),
    Advisor_Phone VARCHAR2(16), Dept_Code CHAR(5));

ALTER TABLE stephensow1_final_Advisor ADD CONSTRAINT stephensow1_final_fk1
    FOREIGN KEY (Dept_Code) REFERENCES stephensow1_final_Dept(Dept_Code)
    INITIALLY DEFERRED DEFERRABLE;

CREATE TABLE stephensow1_final_Student (
    Stu_Num CHAR(6) PRIMARY KEY, Stu_LName VARCHAR2(16),
    Stu_Major VARCHAR2(16), Stu_GPA NUMBER(10, 8),
    Stu_Hours NUMBER(3), Stu_Class VARCHAR2(10),
    Advisor_ID CHAR(5), Dept_Code CHAR(5));

ALTER TABLE stephensow1_final_Student ADD CONSTRAINT stephensow1_final_fk2
    FOREIGN KEY (Dept_Code) REFERENCES stephensow1_final_Dept(Dept_Code)
    INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE stephensow1_final_Student ADD CONSTRAINT stephensow1_final_fk3
    FOREIGN KEY (Advisor_ID) REFERENCES stephensow1_final_Advisor(Advisor_ID)
    INITIALLY DEFERRED DEFERRABLE;

INSERT INTO stephensow1_final_Dept VALUES ('ACCT', 'Accounting', '4356', 'Business Admin');
INSERT INTO stephensow1_final_Dept VALUES ('MKTG', 'Marketing', '4378', 'Business Admin');
SET DEFINE OFF;
INSERT INTO stephensow1_final_Dept VALUES ('MATH', 'Mathematics', '3420', 'Arts & Sciences');
SET DEFINE ON;
INSERT INTO stephensow1_final_Dept VALUES ('CS', 'Computer Science', '5234', 'Informatics');
INSERT INTO stephensow1_final_Dept VALUES ('IS', 'Business Informatics', '3951', 'Informatics');

INSERT INTO stephensow1_final_Advisor VALUES ('001', 'Grastand', 'T201', 'Torre Building', '2115', 'ACCT');
INSERT INTO stephensow1_final_Advisor VALUES ('002', 'Gentry', 'T228', 'Torre Building', '2123', 'MKTG');
INSERT INTO stephensow1_final_Advisor VALUES ('003', 'Tillery', 'T356', 'Torre Building', '2159', 'MKTG');
INSERT INTO stephensow1_final_Advisor VALUES ('004', 'Chen', 'J331', 'Jones Building', '3209', 'MATH');
INSERT INTO stephensow1_final_Advisor VALUES ('005', 'Lewis', '5132', 'Griffin', '1603', 'CS');
INSERT INTO stephensow1_final_Advisor VALUES ('006', 'Zang', '3452', 'Griffin', '3512', 'IS');
INSERT INTO stephensow1_final_Advisor VALUES ('007', 'Goh', '5612', 'Griffin', '7922', 'IS');

INSERT INTO stephensow1_final_Student VALUES ('211343', 'Stephanos', 'Accounting', 3.87, 75, 'Junior', '001', 'ACCT');
INSERT INTO stephensow1_final_Student VALUES ('200128', 'Smith', 'Accounting', 2.78, 45, 'Sophomore', '001', 'ACCT');
INSERT INTO stephensow1_final_Student VALUES ('199876', 'Jones', 'Marketing', 2.31, 117, 'Senior', '002', 'MKTG');
INSERT INTO stephensow1_final_Student VALUES ('200888', 'Ortiz', 'Marketing', 3.45, 113, 'Senior', '003', 'MKTG');
INSERT INTO stephensow1_final_Student VALUES ('223456', 'McKulski', 'Statistics', 3.58, 87, 'Junior', '004', 'MATH');
INSERT INTO stephensow1_final_Student VALUES ('123984', 'Thomas', 'CIT', 2.5, 93, 'Senior', '005', 'CS');
INSERT INTO stephensow1_final_Student VALUES ('995133', 'Thompson', 'BIS', 2.4, 58, 'Junior', '006', 'IS');
INSERT INTO stephensow1_final_Student VALUES ('367181', 'Meyers', 'BIS', 2.3, 63, 'Junior', '007', 'IS');