数据库系统概念_代码

人懒，不想自己输入，从官方网站 http://www.db-book.com/，把源码下载下来了。改了一点点（不改在ubuntu上不通过）

把所有代码直接复制粘贴到命令行即可。

1.大学数据系统

drop table prereq;
drop table time_slot;
drop table advisor;
drop table takes;
drop table student;
drop table teaches;
drop table section;
drop table instructor;
drop table course;
drop table department;
drop table classroom;

create table classroom
	(building varchar(15),
	 room_number varchar(7),
	 capacity numeric(4,0),
	 primary key (building, room_number)
	);

create table department
	(dept_name varchar(20), 
	 building varchar(15), 
	 budget         numeric(12,2) check (budget > 0),
	 primary key (dept_name)
	);

create table course
	(course_id varchar(8), 
	 title 	varchar(50), 
	 dept_name varchar(20),
	 credits numeric(2,0) check (credits > 0),
	 primary key (course_id),
	 foreign key (dept_name) references department(dept_name)
  
	);

create table instructor
	(ID 	varchar(5), 
	 name 	varchar(20) not null, 
	 dept_name varchar(20), 
	 salary 	numeric(8,2) check (salary > 29000),
	 primary key (ID),
	 foreign key (dept_name) references department(dept_name)
  
	);

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),
	 primary key (course_id, sec_id, semester, year),
	 foreign key (course_id) references course(course_id)
 on delete cascade,
	 foreign key (building, room_number) references classroom(building, room_number)
  
	);

create table teaches
	(ID 	varchar(5), 
	 course_id varchar(8),
	 sec_id 	varchar(8), 
	 semester varchar(6),
	 year 	numeric(4,0),
	 primary key (ID, course_id, sec_id, semester, year),
	 foreign key (course_id,sec_id, semester, year) references section(course_id,sec_id, semester, year) 
 on delete cascade,
	 foreign key (ID) references instructor(ID)
 on delete cascade
	);

create table student
	(ID 	varchar(5), 
	 name 	varchar(20) not null, 
	 dept_name varchar(20), 
	 tot_cred numeric(3,0) check (tot_cred >= 0),
	 primary key (ID),
	 foreign key (dept_name) references department(dept_name)
  
	);

create table takes
	(ID 	varchar(5), 
	 course_id varchar(8),
	 sec_id 	varchar(8), 
	 semester varchar(6),
	 year 	numeric(4,0),
	 grade         varchar(2),
	 primary key (ID, course_id, sec_id, semester, year),
	 foreign key (course_id,sec_id, semester, year) references section(course_id,sec_id, semester, year)
 on delete cascade,
	 foreign key (ID) references student(ID)
 on delete cascade
	);

create table advisor
	(s_ID 	varchar(5),
	 i_ID 	varchar(5),
	 primary key (s_ID),
	 foreign key (i_ID) references instructor (ID)
  ,
	 foreign key (s_ID) references student (ID)
 on delete cascade
	);

create table time_slot
	(time_slot_id varchar(4),
	 day 	varchar(1),
	 start_hr numeric(2) check (start_hr >= 0 and start_hr < 24),
	 start_min numeric(2) check (start_min >= 0 and start_min < 60),
	 end_hr 	numeric(2) check (end_hr >= 0 and end_hr < 24),
	 end_min numeric(2) check (end_min >= 0 and end_min < 60),
	 primary key (time_slot_id, day, start_hr, start_min)
	);

create table prereq
	(course_id varchar(8), 
	 prereq_id varchar(8),
	 primary key (course_id, prereq_id),
	 foreign key (course_id) references course(course_id)
 on delete cascade,
	 foreign key (prereq_id) references course(course_id)
	);







delete from prereq;
delete from time_slot;
delete from advisor;
dele