Cis 336 Week 3 Lab

--STEP 1 DROP STATEMENTS

DROP TABLE ENROLLMENT CASCADE CONSTRAINTS PURGE;
DROP TABLE COURSE_SECTION CASCADE CONSTRAINTS PURGE;
DROP TABLE COURSE CASCADE CONSTRAINTS PURGE;
DROP TABLE TERM CASCADE CONSTRAINTS PURGE;
DROP TABLE STUDENT CASCADE CONSTRAINTS PURGE;
DROP TABLE FACULTY CASCADE CONSTRAINTS PURGE;
DROP TABLE LOCATION CASCADE CONSTRAINTS PURGE;

--STEP 2 CREATE STATEMENTS

CREATE TABLE LOCATION
(
LOCID NUMBER(5) CONSTRAINT PK_LOCATION_LOCID PRIMARY KEY,
BLDG_CODE VARCHAR2(10) NOT NULL,
ROOM VARCHAR2(10) NOT NULL,
CAPACITY NUMBER(5)
);

CREATE TABLE FACULTY
(
FID NUMBER(4) CONSTRAINT PK_FACULTY_FID PRIMARY KEY,
FLNAME VARCHAR2(25) NOT NULL,
FFNAME VARCHAr2(25) NOT NULL,
FMI CHAR(1),
FPHONE NUMBER(10),
F_RANK VARCHAr2(4) CONSTRAINT CC_FACULTY_F_RANK CHECK(F_RANK IN ('ASSO', 'FULL', 'ASST', 'ADJ')),
F_PIN NUMBER(4)
);

CREATE TABLE STUDENT
(
SID NUMBER(5) CONSTRAINT PK_STUDENT_SID PRIMARY KEY,
SLNAME VARCHAR2(25) NOT NULL,
SFNAME VARCHAR2(25) NOT NULL,
SMI CHAR(1),
SADDR VARCHAR2(30),
ScITY VARCHAR2(30),
SSTATE CHAR(2) DEFAULT 'WI',
SZiP NUMBER(9),
SPHONE NUMBER(10) NOT NULL,
SCLASS CHAR(2) DEFAULT 'FR' CONSTRAINT CC_STUDENT_SCLASS CHECK(SCLASS IN ('FR','SO','JR','SR','GR')),
SDOB DATE NOT NULL,
S_PIN NUMBER(4) NOT NULL,
FID NUMBER(4),
CONSTRAINT FK_STUDENT FOREIGN KEY (FID) REFERENCES FACULTY
);

CREATE TABLE TERM
(
TERMID NUMBER(5) CONSTRAINT PK_TERM_TERMID PRIMARY KEY,
TDESC VARCHAR2(20) NOT NULL,
STATUS VARCHAR2(20) NOT NULL CONSTRAINT CC_TERM_STATUS CHECK(STATUS IN ('OPEN', 'CLOSED'))
);

CREATE TABLE COURSE
(
CID NUMBER(6) CONSTRAINT PK_COURSE_CID PRIMARY KEY,
CALLID VARCHAR2(10) NOT NULL,
CNAME VARCHAR2(30) NOT NULL,
CCREDIT NUMBER(2) DEFAULT 3
);

CREATE TABLE COURSE_SECTION
(
CSECID NUMBER(8) CONSTRAINT PK_COURSE_SECTION_CSECID PRIMARY KEY,
CID NUMBER(6) NOT