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;

Create table ENROLLMENT
(
SID NUMBER(5) CONSTRAINT ENRL_SID_PK PRIMARY KEY,
CONSTRAINT FK_CSECID REFERENCES (STUDENT),
Grade VARCHAR(1))
;

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');

create table COURSE_SECTION
(
CSECID NUMBER(8) 
CONSTRAINT COURSESECTION_PK_CORID PRIMARY KEY (ENROLLMENT),
CID NUMBER(6) NOT NULL,
CONSTRAINT COURSESECTION_CID_FK FOREIGN KEY (COURSE),
Termid NUMBER(5) NOT NULL,
CONSTRAINT COURSESECTION_TERMID_FK FOREIGN KEY (TERM),
Fid NUMBER (4) NOT NULL,
CONSTRAINT COURSESECTION_Fid_FK FOREIGN KEY (FACULTY),
Lucid NUMBER(5) NOT NULL,
CONSTRAINT COURSESECTION_Lucid_FK FOREIGN KEY (LOCATION),
Secnum NUMBER(2),
FID NUMBER(4),
DAY VARCHAR2(10),
Locid number(5),
Maxenrl NUMBER(4),
Currenrl NUMBER(4)
);

INSERT INTO COURSE_SECTION
VALUES (1000,'1','2','1','12','MWF','55','100','35');
INSERT INTO COURSE_SECTION
VALUES (1001,'1','2','2','10','TTH','54','45','35');
INSERT INTO COURSE_SECTION
VALUES (1002,'2','2','3','10','MWF','53','35','32');
INSERT INTO COURSE_SECTION
VALUES (1003,'3','2','1','11','TTH','54','45','35');

create table COURSE
(CID NUMBER(6) CONSTRAINT PK_COURSE PRIMARY KEY,
Callid VARCHAR2(10) NOT NULL,
Cname VARCHAR2(30) NOT NULL,
Ccredit NUMBER(2));

INSERT INTO COURSE
VALUES (1,'MIS101','Intro. to Info. Systems','3');
INSERT INTO COURSE
VALUES (2,'MIS321','System Analysis and Design','3');
INSERT INTO COURSE
VALUES (3,'MIS349','Intro to Database Management','3')
);

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

INSERT INTO TERM
VALUES ('Spring 2004','CLOSED')
INSERT INTO TERM
VALUES ('SUMMER 2004','OPEN');

create table STUDENT
(
Sid NUMBER(5) CONSTRAINT PK_STUDENT PRIMARY KEY,
Slame 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,
Spin NUMBER(4) NOT NULL,
Fid NUMBER (4) CONSTRAINT FK_STUDENT REFERENCES FACULTY,
);

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);

create table FACULTY
Fid NUMBER(4) CONSTRAINT PK_FACULTY PRIMARY KEY,
Flname VARCHAR2(25),
Ffname VARCHAR2(25),
Fmi CHAR(1),
Fphone NUMBER(10),
F_rank VARCHAR(4));

INSERT INTO FACULTY
VALUES (10,'Cox', 'Kim', 'J', '7155551234', 'ASSO', 1181);
INSERT INTO FACULTY
VALUES (11,'Blanchard', 'Frank', 'R', '7155559087', 'FULL', 1075);
INSERT INTO FACULTY
VALUES (12,'McClure', 'William', 'L', '7155556409', 'ADJ', 1690);

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

INSERT INTO FACULTY
VALUES (53,'BUS','424','45');
INSERT INTO FACULTY
VALUES (54,'BUS','402','35');
INSERT INTO FACULTY
VALUES (55,'BUS','433','100');

Commit;