0

What am I missing... I have looked and searched. Any help / direction?

-- DANIWEBOS

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 Location
(Locid NUMBER(4),
Bldg_Code VARCHAR2(10),
Room VARCHAR2(6),
Capacity NUMBER(5));

ALTER TABLE Location
ADD CONSTRAINT Location_PK PRIMARY KEY (Locid);

INSERT INTO Location
Values (53,'BUS',424,45);
INSERT INTO Location
Values (54,'BUS',402,35);
INSERT INTO Location
Values (553,'BUS', 433, 100);

CREATE TABLE Faculty
(Fid NUMBER(4),
Locid NUMBER(5),
Flname VARCHAR2(25),
Ffname VARCHAR2(25),
Fmi CHAR(1),
Fphone NUMBER(10),
F_rank VARCHAR2(4),
F_pin NUMBER(4)
		CHECK(F_pin IN ('ASSO','FULL','ASST','ADJ')));
ALTER TABLE Faculty
ADD CONSTRAINT Faculty_PK PRIMARY KEY (Fid);

ALTER TABLE Faculty
ADD CONSTRAINT Faculty_FK FOREIGN KEY (Locid);

INSERT INTO Faculty
Values (10, 'Cox', 'Kim', 'J', 53, 71555551234, 'ASSO', 1184);
INSERT INTO Faculty
Values (11, 'Blanchard', 'Frank', 'R', 54, 71555559087,'FULL', 1075);
INSERT INTO Faculty
Values (12, 'McClure', 'William', 'L', 55, 71555556409,'ADJ', 1690);

CREATE TABLE Student
(Sid NUMBER(5),
Slname VARCHAR2(25),
Sfname VARCHAR2(25)
Smi CHAR(1),
Saddr VARCHAR2(30),
Scity VARCHAR2(10),
Sstate CHAR(2) DEFAULT 'WI',
Szip NUMBER(9),
Sphone NUMBER(10),
Sclass CHAR(2) DEFAULT 'FR'
		CHECK(Sclass IN ('FR','SC','JR','SR','GR'))),
Sdob DATE,
S_pin NUMBER(4),
Fid NUMBER(4);

ALTER TABLE Student
ADD CONSTRAINT Student_PK PRIMARY KEY (Sid);

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 Term
(Termid NUMBER(5),
Tdesc VARCHAR2(20),
Status VARCHAR2(20)
			CHECK(Status IN('OPEN',"CLOSED')));

ALTER TABLE Term
ADD CONSTRAINT Term_PK PRIMARY KEY (Termid);

INSERT INTO Term
Values (1, 'Spring 2004', 'CLOSED');
INSERT INTO Term
Values (2, 'Summer 2004', 'OPEN');

CREATE TABLE Course
(Cid NUMBER(8),
Callid VARCHAR2(10),
Cname VARCHAR2(30),
Ccredit NUMBER(2)
	DEFAULT 3);

ALTER TABLE Course
ADD CONSTRAINT Course_PK PRIMARY KEY (Cid);

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

CREATE TABLE Course_Section
(Csecid NUMBER(8),
Cid NUMBER(6),
Termid NUMBER(5),
Secnum NUMBER(2),
Fid NUMBER(4),
Day VARCHAR2(10),
Locid NUMBER(5),
Maxenrl NUMBER(4),
Currenrl NUMBER(4));

ALTER TABLE Course_Section
ADD CONSTRAINT Course_Section_PK PRIMARY KEY (Csecid);

ALTER TABLE Course_Section
ADD CONSTRAINT Course_Section_FK FOREIGN KEY (Cid);

ALTER TABLE Course_Section
ADD CONSTRAINT Course_Section_FK FOREIGN KEY (Termid);

ALTER TABLE Course_Section
ADD CONSTRAINT Course_Section_FK FOREIGN KEY (Fid);

ALTER TABLE Course_Section
ADD CONSTRAINT Course_Section_FK FOREIGN KEY (Locid);

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 Enrollment
(Sid NUMBER(5),
Csecid NUMBER(8),
Grade CHAR(1)
		CHECK(Grade IN('A','B','C','D','F','I')));

ALTER TABLE Enrollment
ADD CONSTRAINT Enrollment_PK PRIMARY KEY (Sid, Csecid);

ALTER TABLE Enrollment
ADD CONSTRAINT Enrollment_FK FOREIGN KEY (Sid, Csecid);	

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

Commit;
2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by debasisdas
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.