DROP TABLE ENROLLMENT; 
DROP TABLE COURSE_SELECTION;
DROP TABLE COURSE;
DROP TABLE TERM;
DROP TABLE STUDENT;
DROP TABLE FACULTY;
DROP TABLE LOCATION;

Create table LOCATION
( 
 Locid NUMBER(5),
 Bldg_Code VARCHAR2(10) NOT NULL,
 Room VARCHAR2(6) NOT NULL,
 Capacity NUMBER(5),
 CONSTRAINT PK_LOCATION_LOCID PRIMARY KEY (Locid)
);

Create table FACULTY
( 
 Fid NUMBER(4),
 Flname	VARCHAR2(25) NOT NULL,
 Ffname	VARCHAR2(25) NOT NULL,
 Fmi CHAR(1),
 Locid NUMBER(5),
 Fphone NUMBER(10),
 F_rank	VARCHAR2(4),
 F_pin NUMBER(4),
 CONSTRAINT PK_FACULTY_FID PRIMARY KEY (Fid),
 CONSTRAINT FK_FACULTY_LOCID 
	FOREIGN KEY (Locid)
	REFERENCES LOCATION (Locid)
);

Create table STUDENT
(
 Sid NUMBER(5),
 Slname VARCHAR2(25) NOT NULL,
 Sfname VARCHAR2(25) NOT NULL,
 Smi CHAR(1),
 Saddr VARCHAR2(30),
 Scity VARCHAR2(30),
 Sstate CHAR(2),
 Szip NUMBER(9),
 Sphone NUMBER(10) NOT NULL,
 Sclass CHAR(2),
 Sdob DATE NOT NULL,
 S_pin NUMBER(4) NOT NULL,
 Fid NUMBER(4),
 CONSTRAINT PK_STUDENT_SID PRIMARY KEY (Sid),
 CONSTRAINT FK_STUDENT_FID
	FOREIGN KEY (Fid)
	REFERENCES FACULTY (Fid)
);

Create table TERM
(
 Termid NUMBER(5),
 Tdesc VARCHAR2(20) NOT NULL,
 Status VARCHAR2(20) NOT NULL,
 CONSTRAINT PK_TERM_TERMID PRIMARY KEY (Termid)
);

Create table COURSE
(
 Cid NUMBER(6),
 Callid VARCHAR2(10) NOT NULL,
 Cname VARCHAR2(30) NOT NULL,
 Ccredit NUMBER(2),
 CONSTRAINT PK_COURSE_CID PRIMARY KEY (Cid)
);

Create table COURSE_SELECTION
(
 Csecid NUMBER(8),
 Cid NUMBER(6) NOT NULL,
 Termid NUMBER(5) NOT NULL,
 Secnum NUMBER(2) NOT NULL,
 Fid NUMBER(4),
 Day VARCHAR2(10),
 Locid NUMBER(5),
 Maxenrl NUMBER(4) NOT NULL,
 Currenrl NUMBER(4) NOT NULL,
 CONSTRAINT PK_COURSE_SELECTION_CSECID 
	PRIMARY KEY (Csecid),
 CONSTRAINT FK_COURSE_SELECTION_CID 
	FOREIGN KEY (Cid)
	REFERENCES COURSE (Cid),
 CONSTRAINT FK_COURSE_SELECTION_TERMID
	FOREIGN KEY (Termid) 
	REFERENCES TERM (Termid),
 CONSTRAINT FK_COURSE_SELECTION_FID
	FOREIGN KEY (FID)
	REFERENCES FACULTY (Fid),
 CONSTRAINT FK_COURSE_SELECTION_LOCID
	FOREIGN KEY (Locid)
	REFERENCES LOCATION (Locid)
);

Create table ENROLLMENT
(
 Sid NUMBER(5),
 Csecid NUMBER(8),
 Grade CHAR(1),
 CONSTRAINT PK_ENROLLMENT_SID 
	PRIMARY KEY (Sid,Csecid),
 CONSTRAINT FK_ENROLLMENT_SID 
	FOREIGN KEY (Sid)
	REFERENCES STUDENT (SID),
 CONSTRAINT FK_ENROLLMENT_CSECID
	FOREIGN KEY (Csecid)
	REFERENCES COURSE_SELECTION (Csecid)
);

INSERT INTO LOCATION
VALUES (53, 'BUS', '424', 45);
INSERT INTO LOCATION
VALUES (54, 'BUS', '402', 35);
INSERT INTO LOCATION
VALUES (55, 'BUS', '433', 100);
INSERT INTO FACULTY
VALUES (10, 'Cox', 'Kim', 'J', 53, 7155551234, 'ASSO', 1181);
INSERT INTO FACULTY
VALUES (11, 'Blanchard', 'Frank', 'R', 54, 7155559087, 'FULL', 1075);
INSERT INTO FACULTY
VALUES (12, 'McClure', 'William', 'L', 55, 7155556409, 'ADJ', 1690);
INSERT INTO STUDENT
VALUES (100, 'McClure', 'Sarah', 'M', '144 Windridge Blvd.', 'Eau Claire', 'WI', 54703, 7155559876, 'SR', '14-JUL-1979', 8891, 10);
INSERT INTO STUDENT
VALUES (101, 'Bowie', 'Jim', 'D', '454 St. John Street', 'Eau Claire', 'WI', 54702, 7155552345, 'SR', '19-AUG-1979', 1230, 11);
INSERT INTO STUDENT
VALUES (102, 'Boone', 'Daniel', NULL, '8921 Circle Drive', 'Bloomer', 'WI', 54715, 7155553907, 'JR', '10-OCT-1977', 1613, 11);
INSERT INTO TERM
VALUES (1, 'Spring 2004', 'CLOSED');
INSERT INTO TERM
VALUES (2, 'Summer 2004', 'OPEN');
INSERT INTO COURSE
VALUES (1, 'MIS101', 'Intro. to Info. Systems', 3);
INSERT INTO COURSE
VALUES (2, 'MIS321', 'Systems Analysis and Design', 3);
INSERT INTO COURSE
VALUES (3, 'MIS349', 'Intro to Database Management', 3);
INSERT INTO COURSE_SELECTION
VALUES (1000, 1, 2, 1, 12, 'MWF', 55, 100, 35);
INSERT INTO COURSE_SELECTION
VALUES (1001, 1, 2, 2, 10, 'TTH', 54, 45, 35);
INSERT INTO COURSE_SELECTION
VALUES (1002, 2, 2, 3, 10, 'MWF', 53, 35, 32);
INSERT INTO COURSE_SELECTION
VALUES (1003, 3, 2, 1, 11, 'TTH', 54, 45, 35);
INSERT INTO ENROLLMENT
VALUES (100, 1000, 'A');
INSERT INTO ENROLLMENT
VALUES (100, 1003, 'A');
INSERT INTO ENROLLMENT
VALUES (101, 1000, 'C');
INSERT INTO ENROLLMENT
VALUES (102, 1000, 'C');
INSERT INTO ENROLLMENT
VALUES (102, 1001, NULL);
INSERT INTO ENROLLMENT
VALUES (102, 1003, 'I');