该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

create table classroom

(buildingvarchar(15),

room_numbervarchar(7),

capacitynumeric(4,0),

primary key (building, room_number)

);

create table department

(dept_namevarchar(20),

buildingvarchar(15),

budget numeric(12,2) check (budget > 0),

primary key (dept_name)

);

create table course

(course_idvarchar(8),

titlevarchar(50),

dept_namevarchar(20),

creditsnumeric(2,0) check (credits > 0),

primary key (course_id),

foreign key (dept_name) references department

on delete set null

);

create table instructor

(IDvarchar(5),

namevarchar(20) not null,

dept_namevarchar(20),

salarynumeric(8,2) check (salary > 29000),

primary key (ID),

foreign key (dept_name) references department

on delete set null

);

create table section

(course_idvarchar(8),

sec_idvarchar(8),

semestervarchar(6)

check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),

yearnumeric(4,0) check (year > 1701 and year < 2100),

buildingvarchar(15),

room_numbervarchar(7),

time_slot_idvarchar(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

(IDvarchar(5),

course_idvarchar(8),

sec_idvarchar(8),

semestervarchar(6),

yearnumeric(4,0),

primary key (ID, course_id, sec_id, semester, year),

foreign key (course_id,sec_id, semester, year) references section

on delete cascade,

foreign key (ID) references instructor

on delete cascade

);

create table student

(IDvarchar(5),

namevarchar(20) not null,

dept_namevarchar(20),

tot_crednumeric(3,0) check (tot_cred >= 0),

primary key (ID),

foreign key (dept_name) references department

on delete set null

);

create table takes

(IDvarchar(5),

course_idvarchar(8),

sec_idvarchar(8),

semestervarchar(6),

yearnumeric(4,0),

grade varchar(2),

primary key (ID, course_id, sec_id, semester, year),

foreign key (course_id,sec_id, semester, year) references section

on delete cascade,

foreign key (ID) references student

on delete cascade

);

create table advisor

(s_IDvarchar(5),

i_IDvarchar(5),

primary key (s_ID),

foreign key (i_ID) references instructor (ID)

on delete set null,

foreign key (s_ID) references student (ID)

on delete cascade

);

create table time_slot

(time_slot_idvarchar(4),

dayvarchar(1),

start_hrnumeric(2) check (start_hr >= 0 and start_hr < 24),

start_minnumeric(2) check (start_min >= 0 and start_min < 60),

end_hrnumeric(2) check (end_hr >= 0 and end_hr < 24),

end_minnumeric(2) check (end_min >= 0 and end_min < 60),

primary key (time_slot_id, day, start_hr, start_min)

);

create table prereq

(course_idvarchar(8),

prereq_idvarchar(8),

primary key (course_id, prereq_id),

foreign key (course_id) references course

on delete cascade,

foreign key (prereq_id) references course

);