习题之前,我们先建立了11张表:
create table classroom
(building varchar(15),
room_number varchar(7),
capacity numeric(4,0),
primarykey (building, room_number)
);
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2) check (budget > 0),
primarykey (dept_name)
);
create table course
(course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0) check (credits > 0),
primarykey (course_id),
foreignkey (dept_name) references department
on deleteset null
);
create table instructor
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2) check (salary > 29000),
primarykey (ID),
foreignkey (dept_name) references department
on deleteset null
);
create table section
(course_id varchar(8),
sec_id varchar(8),
semester varchar(6)
check (semester in('Fall', 'Winter', 'Spring', 'Summer')),
year numeric(4,0) check (year > 1701 and year < 2100),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),
primarykey (course_id, sec_id, semester, year),
foreignkey (course_id) references course
on deletecascade,
foreignkey (building, room_number) references classroom
on deleteset null
);
create table teaches
(ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
primarykey (ID, course_id, sec_id, semester, year),
foreignkey (course_id,sec_id, semester, year) references section
on deletecascade,
foreignkey (ID) references instructor
on deletecascade
);
create table student
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0) check (tot_cred >= 0),
primarykey (ID),
foreignkey (dept_name) references department
on deleteset null
);
create table takes
(ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),
primarykey (ID, course_id, sec_id, semester, year),
foreignkey (course_id,sec_id, semester, year) references section
on deletecascade,
foreignkey (ID) references student
on deletecascade
);
create table advisor
(s_ID varchar(5),
i_ID varchar(5),
primarykey (s_ID),
foreignkey (i_ID) references instructor(ID)
on deleteset null,
foreignkey (s_ID) references student(ID)
on deletecascade
);
接下来我们再往各张表里插入数据(代码略)后,我们来看看11张表的信息:
classroom: department:
course: instructor: