【建表导数】 《数据库概念 第六版》习题答案和数据库

最新推荐文章于 2023-12-07 16:53:34 发布
最新推荐文章于 2023-12-07 16:53:34 发布
阅读量1.1k 收藏 1
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/hellotracy/articles/5076474.html
版权

 

《数据库概念》 第六版

1.每个章节的练习题答案在这里：Solutions to Practice Exercises

2.整本书都在使用一个叫 “school”的数据库模式

 

建表的DDL语句与MySQL唯一不兼容的地方是，在添加引用约束中，没有加入parent表的具体字段

如 foreign key (dept_name) references department on delete set null

应改为 foreign key (dept_name) references department(dept_name) on delete set null

 

建表DDL语句

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

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

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) 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) on delete cascade
	);

　　

插入小量数据的DML语句

delete from prereq;
delete from time_slot;
delete from advisor;
delete from takes;
delete from student;
delete from teaches;
delete from section;
delete from instructor;
delete from course;
delete from department;
delete from classroom;
insert into classroom values ('Packard', '101', '500');
insert into classroom values ('Painter', '514', '10');
insert into classroom values ('Taylor', '3128', '70');
insert into classroom values ('Watson', '100', '30');
insert into classroom values ('Watson', '120', '50');
insert into department values ('Biology', 'Watson', '90000');
insert into department values ('Comp. Sci.', 'Taylor', '100000');
insert into department values ('Elec. Eng.', 'Taylor', '85000');
insert into department values ('Finance', 'Painter', '120000');
insert into department values ('History', 'Painter', '50000');
insert into department values ('Music', 'Packard', '80000');
insert into department values ('Physics', 'Watson', '70000');
insert into course values ('BIO-101', 'Intro. to Biology', 'Biology', '4');
insert into course values ('BIO-301', 'Genetics', 'Biology', '4');
insert into course values ('BIO-399', 'Computational Biology', 'Biology', '3');
insert into course values ('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', '4');
insert into course values ('CS-190', 'Game Design', 'Comp. Sci.', '4');
insert into course values ('CS-315', 'Robotics', 'Comp. Sci.', '3');
insert into course values ('CS-319', 'Image Processing', 'Comp. Sci.', '3');
insert into course values ('CS-347', 'Database System Concepts', 'Comp. Sci.', '3');
insert into course values ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', '3');
insert into course values ('FIN-201', 'Investment Banking', 'Finance', '3');
insert into course values ('HIS-351', 'World History', 'History', '3');
insert into course values ('MU-199', 'Music Video Production', 'Music', '3');
insert into course values ('PHY-101', 'Physical Principles', 'Physics', '4');
insert into instructor values ('10101', 'Srinivasan', 'Comp. Sci.', '65000');
insert into instructor values ('12121', 'Wu', 'Finance', '90000');
insert into instructor values ('15151', 'Mozart', 'Music', '40000');
insert into instructor values ('22222', 'Einstein', 'Physics', '95000');
insert into instructor values ('32343', 'El Said', 'History', '60000');
insert into instructor values ('33456', 'Gold', 'Physics', '87000');
insert into instructor values ('45565', 'Katz', 'Comp. Sci.', '75000');
insert into instructor values ('58583', 'Califieri', 'History', '62000');
insert into instructor values ('76543', 'Singh', 'Finance', '80000');
insert into instructor values ('76766', 'Crick', 'Biology', '72000');
insert into instructor values ('83821', 'Brandt', 'Comp. Sci.', '92000');
insert into instructor values ('98345', 'Kim', 'Elec. Eng.', '80000');
insert into section values ('BIO-101', '1', 'Summer', '2009', 'Painter', '514', 'B');
insert into section values ('BIO-301', '1', 'Summer', '2010', 'Painter', '514', 'A');
insert into section values ('CS-101', '1', 'Fall', '2009', 'Packard', '101', 'H');
insert into section values ('CS-101', '1', 'Spring', '2010', 'Packard', '101', 'F');
insert into section values ('CS-190', '1', 'Spring', '2009', 'Taylor', '3128', 'E');
insert into section values ('CS-190', '2', 'Spring', '2009', 'Taylor', '3128', 'A');
insert into section values ('CS-315', '1', 'Spring', '2010', 'Watson', '120', 'D');
insert into section values ('CS-319', '1', 'Spring', '2010', 'Watson', '100', 'B');
insert into section values ('CS-319', '2', 'Spring', '2010', 'Taylor', '3128', 'C');
insert into section values ('CS-347', '1', 'Fall', '2009', 'Taylor', '3128', 'A');
insert into section values ('EE-181', '1', 'Spring', '2009', 'Taylor', '3128', 'C');
insert into section values ('FIN-201', '1', 'Spring', '2010', 'Packard', '101', 'B');
insert into section values ('HIS-351', '1', 'Spring', '2010', 'Painter', '514', 'C');
insert into section values ('MU-199', '1', 'Spring', '2010', 'Packard', '101', 'D');
insert into section values ('PHY-101', '1', 'Fall', '2009', 'Watson', '100', 'A');
insert into teaches values ('10101', 'CS-101', '1', 'Fall', '2009');
insert into teaches values ('10101', 'CS-315', '1', 'Spring', '2010');
insert into teaches values ('10101', 'CS-347', '1', 'Fall', '2009');
insert into teaches values ('12121', 'FIN-201', '1', 'Spring', '2010');
insert into teaches values ('15151', 'MU-199', '1', 'Spring', '2010');
insert into teaches values ('22222', 'PHY-101', '1', 'Fall', '2009');
insert into teaches values ('32343', 'HIS-351', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-101', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-319', '1', 'Spring', '2010');
insert into teaches values ('76766', 'BIO-101', '1', 'Summer', '2009');
insert into teaches values ('76766', 'BIO-301', '1', 'Summer', '2010');
insert into teaches values ('83821', 'CS-190', '1', 'Spring', '2009');
insert into teaches values ('83821', 'CS-190', '2', 'Spring', '2009');
insert into teaches values ('83821', 'CS-319', '2', 'Spring', '2010');
insert into teaches values ('98345', 'EE-181', '1', 'Spring', '2009');
insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102');
insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32');
insert into student values ('19991', 'Brandt', 'History', '80');
insert into student values ('23121', 'Chavez', 'Finance', '110');
insert into student values ('44553', 'Peltier', 'Physics', '56');
insert into student values ('45678', 'Levy', 'Physics', '46');
insert into student values ('54321', 'Williams', 'Comp. Sci.', '54');
insert into student values ('55739', 'Sanchez', 'Music', '38');
insert into student values ('70557', 'Snow', 'Physics', '0');
insert into student values ('76543', 'Brown', 'Comp. Sci.', '58');
insert into student values ('76653', 'Aoi', 'Elec. Eng.', '60');
insert into student values ('98765', 'Bourikas', 'Elec. Eng.', '98');
insert into student values ('98988', 'Tanaka', 'Biology', '120');
insert into takes values ('00128', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('00128', 'CS-347', '1', 'Fall', '2009', 'A-');
insert into takes values ('12345', 'CS-101', '1', 'Fall', '2009', 'C');
insert into takes values ('12345', 'CS-190', '2', 'Spring', '2009', 'A');
insert into takes values ('12345', 'CS-315', '1', 'Spring', '2010', 'A');
insert into takes values ('12345', 'CS-347', '1', 'Fall', '2009', 'A');
insert into takes values ('19991', 'HIS-351', '1', 'Spring', '2010', 'B');
insert into takes values ('23121', 'FIN-201', '1', 'Spring', '2010', 'C+');
insert into takes values ('44553', 'PHY-101', '1', 'Fall', '2009', 'B-');
insert into takes values ('45678', 'CS-101', '1', 'Fall', '2009', 'F');
insert into takes values ('45678', 'CS-101', '1', 'Spring', '2010', 'B+');
insert into takes values ('45678', 'CS-319', '1', 'Spring', '2010', 'B');
insert into takes values ('54321', 'CS-101', '1', 'Fall', '2009', 'A-');
insert into takes values ('54321', 'CS-190', '2', 'Spring', '2009', 'B+');
insert into takes values ('55739', 'MU-199', '1', 'Spring', '2010', 'A-');
insert into takes values ('76543', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('76543', 'CS-319', '2', 'Spring', '2010', 'A');
insert into takes values ('76653', 'EE-181', '1', 'Spring', '2009', 'C');
insert into takes values ('98765', 'CS-101', '1', 'Fall', '2009', 'C-');
insert into takes values ('98765', 'CS-315', '1', 'Spring', '2010', 'B');
insert into takes values ('98988', 'BIO-101', '1', 'Summer', '2009', 'A');
insert into takes values ('98988', 'BIO-301', '1', 'Summer', '2010', null);
insert into advisor values ('00128', '45565');
insert into advisor values ('12345', '10101');
insert into advisor values ('23121', '76543');
insert into advisor values ('44553', '22222');
insert into advisor values ('45678', '22222');
insert into advisor values ('76543', '45565');
insert into advisor values ('76653', '98345');
insert into advisor values ('98765', '98345');
insert into advisor values ('98988', '76766');
insert into time_slot values ('A', 'M', '8', '0', '8', '50');
insert into time_slot values ('A', 'W', '8', '0', '8', '50');
insert into time_slot values ('A', 'F', '8', '0', '8', '50');
insert into time_slot values ('B', 'M', '9', '0', '9', '50');
insert into time_slot values ('B', 'W', '9', '0', '9', '50');
insert into time_slot values ('B', 'F', '9', '0', '9', '50');
insert into time_slot values ('C', 'M', '11', '0', '11', '50');
insert into time_slot values ('C', 'W', '11', '0', '11', '50');
insert into time_slot values ('C', 'F', '11', '0', '11', '50');
insert into time_slot values ('D', 'M', '13', '0', '13', '50');
insert into time_slot values ('D', 'W', '13', '0', '13', '50');
insert into time_slot values ('D', 'F', '13', '0', '13', '50');
insert into time_slot values ('E', 'T', '10', '30', '11', '45 ');
insert into time_slot values ('E', 'R', '10', '30', '11', '45 ');
insert into time_slot values ('F', 'T', '14', '30', '15', '45 ');
insert into time_slot values ('F', 'R', '14', '30', '15', '45 ');
insert into time_slot values ('G', 'M', '16', '0', '16', '50');
insert into time_slot values ('G', 'W', '16', '0', '16', '50');
insert into time_slot values ('G', 'F', '16', '0', '16', '50');
insert into time_slot values ('H', 'W', '10', '0', '12', '30');
insert into prereq values ('BIO-301', 'BIO-101');
insert into prereq values ('BIO-399', 'BIO-101');
insert into prereq values ('CS-190', 'CS-101');
insert into prereq values ('CS-315', 'CS-101');
insert into prereq values ('CS-319', 'CS-101');
insert into prereq values ('CS-347', 'CS-101');
insert into prereq values ('EE-181', 'PHY-101');

　　

插入大量数据的DML语句，很难得有一个例子可以提供如此多的数据(约2MB)，数据模拟真实的学校数据库，点击下载

如何让MySQL跑一个 sql file 可以参考这里：

转载于:https://www.cnblogs.com/hellotracy/articles/5076474.html

Jzn18604719929
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
数据库系统概念 | 第六章：形式化关系查询语言 | 含带答案习题
因为健忘，所以记点有的没的〒▽〒
01-24 2893
【数据库系统概念】第六章：形式化关系查询语言，关系代数＋元组关系演算＋域关系演算，含图解及带答案习题的笔记自留。
数据库系统概论（第6版）-课后习题答案-（第一章）
qq_72141173的博客
10-15 3177
什么是：概念模型是按用户的观点来对数据建模（把现实世界的具体事务抽象、组织为某一数据库管理系统支持的数据模型）。从现实世界到概念模型的建模任务由数据库设计人员完成，也可以通过数据库设计工具辅助设计人员完成。概念模型实际上是现实世界到机器世界的一个中间层次作用：概念模型用于信息世界的建模，是现实世界到信息世界的第一层抽象，是数据库设计人员进行数据库设计的有力工具，也是数据库设计人员和用户之间进行交流的语言。
数据库系统概论课后习题答案(第6版)第四章
最新发布
Hikigaya_Hachi的博客
12-07 1370
数据库系统概论课后习题答案(第6版)第四章
数据库系统概念中文原书第六版-第三章课后题答案
m0_55315930的博客
09-19 7806
3.1 使用大学模式，用SQL写出如下查询。（建议在一个数据库上实际运行这些查询，使用我们在本书的Web网站db-book.com上提供的样本数据，上述网站还提供了如何建立一个数据库和加载样本数据的说明。） Classroom(building, room_number, capacity) Department(dept_name, building, budget) Course(course_id, title, dept_name, credits学分)课程信息 Instructor(ID,
数据库系统概念第六版第六章6.1，6.2答案
weixin_51688149的博客
10-09 2109
6.1 Write the following queries in relational algebra, using the university schema.a. Find the titles of courses in the Comp. Sci. department that have 3 credits.b. Find the IDs of all students who were taught by an instructor named Einstein; make sure the
导数的概念练习题.pdf
10-17
导数的概念练习题.pdf
同济大学高等数学第六上 导数的概念.pptx
10-07
同济大学高等数学第六上 导数的概念.pptx
导数练习题答案.docx
10-05
导数练习题答案.docx
导数的四则运算法则练习题一.doc
09-15
导数的四则运算法则练习题一.doc
导数与微分 总结 高数同济第六版
12-10
这是 我们同济六版 老师叫做的作业 大家一起分享
数据库系统概念原书第六版课后习题答案
10-15
数据库系统概念原书第六版课后习题答案 免费的哦。
数据库系统第六版教材课后题答案完整版
07-30
数据库系统第六版教材的课后问题的答案完整版，从数据库老师那里好不容易得到的。
数据库第三章习题答案
01-10
这是关于数据库的第三章的课后的答案，希望对大家有帮助
数据库系统概念第六版答案
10-13
此答案为《数据库系统概念》第六版的答案(英文)， 欢迎下载！
数据库系统概念第六版课后习题详细答案
10-13
数据库系统概念 英文精编版 第6版 [美]Abraham 课后答案的描述 我从学院的网站上下载的，据说是代课老师做的答案，貌似版本有点低，但是大部分答案都有，要仔细找一下，顺序是乱的。 上学期买的答案书，用扫描仪一页一页扫的。不知道算不算侵权，需要的朋友下了自个看吧，别再传其他网站了。 此课后习题答案对应的教材信息如下： 书名：数据库系统概念 英文精编版 第6版 作者：[美]Abraham Silberschatz 杨冬青
《数据库系统概念》第六版答案
热门推荐
star0116的博客
06-16 4万+
每一章都只有部分答案，具体见下面这个链接 数据库系统概念第六版答案
《数据库系统概论》学习总结
峰中劲草
09-05 6122
1.      数据挖掘是从数据库中发现知识，或者主要是从大量的磁盘数据中发现知识。它与机器学习和统计分析不一样。 2.      数据库管理系统（Database-Management System, DBMS）由一个互相关联的数据的集合和一组用于访问这些数据的程序组成。这个数据集合通常称为数据库（database）。DBMS的主要目标是要提供一种可以方便、高效地存取数据库信息的途径。 3.
数据库系统概念第六版课后习题答案-第二章
kirito_lewis的博客
11-28 3万+
实践习题 2.1 考虑图2-14所示关系数据库。这些关系上适当的主码是什么？ Ans: 如图2.1中带下划线的码。（emmmm，发现答案不是很靠谱。比如员工的名字有重名的现象，显然name不能作为主码。但其实name+street+city也可能会有碰巧一样的情况。所以严格来说，这几张表的结构不是很好，项目中最好还是用id作为主键会比较好。） 2.2 考虑从instructor的dept...
oracle数据库导数方式
08-23
总之，Oracle数据库提供了Data Pump工具作为一种常用的导数方式，可以通过expdp和impdp命令行工具进行导数操作，并且需要确保用户具有足够的权限来执行导数操作。<span class="em">1</span><span class="em">2...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值