scau数据库数据下载——数据库系统概念第三章大学模式smallRelationsInsertFile.sql下载和使用

本文链接：https://blog.csdn.net/weixin_53710281/article/details/120589826

1.进入Database System Concepts - 7th edition (db-book.com)官网，选择sample tables

2.选择箭头所指sql文件并下载

3.保存smallRelationsInsertFile.sql文件并用记事本的方式打开

4.删除方框内的代码

5.删除部分改为以下代码

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), 
 primary key(dept_name)
);

CREATE TABLE course
(course_id  VARCHAR(7), 
 title VARCHAR(50),
 dept_name VARCHAR(20),
 credits numeric(2,0), 
 primary key(course_id),
 foreign key(dept_name) references department(dept_name)
);

CREATE TABLE instructor 
(ID  VARCHAR(5), 
 name VARCHAR(20), 
 dept_name VARCHAR(20),
 salary numeric(8,2), 
 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),
year numeric(4,0),
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) );


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 ),
foreign key(ID) references instructor(ID)
);


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)
 on delete set 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),
 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) on delete set null,
 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)
);

6.打开navicat，新建数据库并选择运行sql文件

7.选择smallRelationsInsertFile.sql文件保存的位置并打开