Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE classroom
- (building VARCHAR(15),
- room_number VARCHAR(7),
- capacity NUMBER(4,0),
- PRIMARY KEY (building, room_number)
- );
- CREATE TABLE department
- (dept_name VARCHAR(20),
- building VARCHAR(15),
- budget NUMBER(12,2) CHECK (budget > 0),
- PRIMARY KEY (dept_name)
- );
- CREATE TABLE course
- (course_id VARCHAR(8),
- title VARCHAR(50),
- dept_name VARCHAR(20),
- credits NUMBER(2,0) CHECK (credits > 0),
- PRIMARY KEY (course_id),
- FOREIGN KEY (dept_name) REFERENCES department
- ON DELETE SET NULL
- );
- CREATE TABLE instructor
- (ID VARCHAR(5),
- name VARCHAR(20) NOT NULL,
- dept_name VARCHAR(20),
- salary NUMBER(8,2) CHECK (salary > 29000),
- PRIMARY KEY (ID),
- FOREIGN KEY (dept_name) REFERENCES department
- ON DELETE SET NULL
- );
- CREATE TABLE SECTION
- (course_id VARCHAR(8),
- sec_id VARCHAR(8),
- semester VARCHAR(6)
- CHECK (semester IN ('Fall', 'Winter', 'Spring', 'Summer')),
- YEAR NUMBER(4,0) CHECK (YEAR > 1701 AND YEAR < 2100),
- building VARCHAR(15),
- room_number VARCHAR(7),
- time_slot_id VARCHAR(4),
- PRIMARY KEY (course_id, sec_id, semester, YEAR),
- FOREIGN KEY (course_id) REFERENCES course
- ON DELETE cascade,
- FOREIGN KEY (building, room_number) REFERENCES classroom
- ON DELETE SET NULL
- );
- CREATE TABLE teaches
- (ID varchar2(5),
- course_id varchar2(8),
- sec_id varchar2(8),
- semester varchar2(6),
- YEAR NUMBER(4,0),
- CONSTRAINT "teaches_PK" PRIMARY KEY (ID, course_id, sec_id, semester, YEAR),
- CONSTRAINT "teaches_FK1" FOREIGN KEY (course_id,sec_id, semester, YEAR) REFERENCES SECTION
- ON DELETE cascade,
- CONSTRAINT "teaches_FK2" FOREIGN KEY (ID) REFERENCES instructor (ID)
- ON DELETE cascade
- );
- CREATE TABLE student
- (ID varchar2(5),
- name varchar2(20) NOT NULL,
- dept_name varchar2(20),
- tot_cred NUMBER(3,0) CHECK (tot_cred >= 0),
- CONSTRAINT "student_PK" PRIMARY KEY (ID),
- CONSTRAINT "student_FK" FOREIGN KEY (dept_name) REFERENCES department
- ON DELETE SET NULL
- );
- CREATE TABLE takes
- (ID varchar2(5),
- course_id varchar2(8),
- sec_id varchar2(8),
- semester varchar2(6),
- YEAR NUMBER(4,0),
- grade varchar2(2),
- CONSTRAINT "takes_PK" PRIMARY KEY (ID, course_id, sec_id, semester, YEAR),
- CONSTRAINT "takes_FK1" FOREIGN KEY (course_id,sec_id, semester, YEAR) REFERENCES SECTION
- ON DELETE cascade,
- CONSTRAINT "takes_FK2" FOREIGN KEY (ID) REFERENCES student
- ON DELETE cascade
- );
- CREATE TABLE advisor
- (s_ID varchar2(5),
- i_ID varchar2(5),
- CONSTRAINT "advisor_PK" PRIMARY KEY (s_ID),
- CONSTRAINT "advisor_FK1" FOREIGN KEY (i_ID) REFERENCES instructor (ID)
- ON DELETE SET NULL,
- CONSTRAINT "advisor_FK2" FOREIGN KEY (s_ID) REFERENCES student (ID)
- ON DELETE cascade
- );
- CREATE TABLE time_slot
- (time_slot_id varchar2(4),
- DAY varchar2(1),
- start_hr NUMBER(2) CHECK (start_hr >= 0 AND start_hr < 24),
- start_min NUMBER(2) CHECK (start_min >= 0 AND start_min < 60),
- end_hr NUMBER(2) CHECK (end_hr >= 0 AND end_hr < 24),
- end_min NUMBER(2) CHECK (end_min >= 0 AND end_min < 60),
- CONSTRAINT "time_slot_PK" PRIMARY KEY (time_slot_id, DAY, start_hr, start_min)
- );
- CREATE TABLE prereq
- (course_id varchar2(8),
- prereq_id varchar2(8),
- PRIMARY KEY (course_id, prereq_id),
- FOREIGN KEY (course_id) REFERENCES course
- ON DELETE cascade,
- FOREIGN KEY (prereq_id) REFERENCES course
- );
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement