1、数据库原理与SQL Server应用 高金兰 著 武汉大学出版社 课后答案第一章习题答案DDCB DADD第3章 设计题 答案1学校有若干个系，每个系有若干班级和教研室，每个教研室有若干教师，每名教师只教一门课，每门课可由多个教师教；每个班有若干学生，每个学生选修若干课程，每门课程可由若干学生选修。请用E-R图画出该学校的概念模型，注明联系类型，再将E-R模型转换为关系模型。假定：主码_ 或 #外码 或 _学院（院名，地址，成立日期，教师人数，学生人数）系（系名，系址，系主任，院名）班级（班号，专业名，入学年份，学生数，班级导师，系名）学生（学号，姓名，性别，出生日期，联系电话，籍贯，班号）教
2、研室（教研室名称，教师人数，电话，办公室编号，系名）教师（教工号，姓名，性别，出生日期，联系电话，教研室名称）课程（课号，课名，学时，学分，开课学期，必修课否，教工号）选修（学号，课号，成绩）2工厂生产的每种产品由不同的零件组成，有的零件可用于不同的产品。这些零件由不同的原材料制成，不同的零件所用的材料可以相同。一个仓库存放多种产品，一种产品存放在一个仓库中。零件按所属的不同产品分别放在仓库中，原材料按照类别放在若干仓库中（不跨仓库存放）。请用E-R图画出此关于产品，零件，材料，仓库的概念模型，注明联系类型，再将E-R模型转换为关系模型。产品（产品名，型号，重量，仓库号，数量c）零件（零件名，
3、规格）材料（材料编号，名称，仓库号，数量a）仓库（仓库号，地点，面积，保管员）产品-零件（产品名，零件名，组装日期b）零件-材料（材料编号，零件名，生产日期a） 零件-仓库（零件名，仓库号，数量b）3、一个图书馆理系统中有如下 信息：图书：书号、书名、数量、位置借书人：借书证号、姓名、单位出版社：出版社名、邮编、地址、电话、E-mail其中约定：任何人可以借多种书，任何一种书可以被多个人借，借书和还书时，要登记相应的借书日期和还书日期；一个出版社可以出版多种书籍，同一本书仅为一个出版社所出版，出版社名具有惟一性。根据以上情况，完成如下设计：（1）设计系统的E-R图；（2）将E-R图转换为关系模
4、式，并指出函数依赖；（3）指出转换后的每个关系模式的关系键（主键，外键）。解答：（1）图书关系模式：图书（书号，书名，数量，位置，出版社名） 函数依赖：（书号 书名，书号数量，书号位置，书号出版社名）关系主键：书号 外键：出版社名借书人关系模式：借书人（借书证号，姓名，单位） 函数依赖：（借书证号姓名，借书证号单位）关系主键：借书证号出版社关系模式：出版社（出版社名，邮编，地址，电话，E-mail）函数依赖：（出版社名邮编，出版社名地址，出版社名电话，出版社名E-mail，地址邮编）关系主键：出版社名借阅关系模式：借阅（借书证号，书号，借书日期，还书日期）函数依赖：（借书证号，书号）借书日期，
5、（借书证号，书号）还书日期关系主键：（借书证号，书号）外键1：借书证号外键2：书号第五章 二 设计题 答案-3.CREATE DATABASE zygl ON (NAME = zygl_Data, FILENAME =E:zygl_Data.MDF , SIZE = 1, MAXSIZE = 30, FILEGROWTH = 10%) LOG ON (NAME = zygl_Log, FILENAME =E:zygl_Log.LDF ,SIZE = 3, FILEGROWTH = 1)GO-4ATLER DATABASE ZYGLMODIFY FILE ( NAME =ZYGL_DATA, F
6、ILEGROWTH =5 )-5ATLER DATABASE ZYGLADD FILE (NAME =ZYGLBAK , FILENAME =E:zygl_Data1 .NDF , SIZE = 1, MAXSIZE = 50, FILEGROWTH = 10% )-6 DROP DATABASE ZYGL 第六章 二 设计题 答案-1.Create database scdGoUse scdGoCREATE TABLE department(系号 char(3) PRIMARY KEY, 系名 char(20) not null unique)GoCREATE TABLE class(班号
7、char(3) PRIMARY KEY, 专业名 char(20), 系名 char(20) references department(系名) ,入学年份 int)GoCREATE TABLE student(学号 char(7) PRIMARY KEY,姓名 char(8) NOT NULL,年龄 int constraint ck_nl CHECK(年龄 =15 and 年龄=40) constraint df_nl DEFAULT 18, -指定约束名ck_nl 和df_nl，以便以后好删除班号 char(3) references class(班号)Insert into stude
8、nt values(2008101，张山，18，101)；Insert into student values(2008102，李斯，16，102)； Insert into student values( 2008103，王玲，17，111)Insert into student values( 2008105，李飞，19，112)；Insert into class values ( 101，软件，计算机，2005)； Insert into class values ( 102，微电子，计算机，2006)；Insert into class values (111，无机化学，化学，200
9、4)；Insert into class values (112，高分子化学，化学，2006 ) Insert into department values (001， 数学)；Insert into department values (002， 计算机)；Insert into department values (003， 化学) -2.-1)Insert into department values(006,物理系)Insert into department values(008,经济系) -2)Delete class where 入学年份=0 and 工龄1753. 查询所有姓“
10、李”同学的基本情况。Select *From xsWhere 姓名 like 李%4. 检索平均成绩在75分以上的学生姓名、性别和专业。方法一：Select 姓名，性别，专业名From xsWhere 学号 in ( Select 学号 From xs_kc Group by 学号Having avg(成绩)75)方法二：Select xs.学号, 姓名，性别，专业名From xs_kc , xsWhere xs.学号=xs_kc.学号Group by xs.学号, 姓名，性别，专业名Having avg(成绩)755. 将所有学生信息按身高逆序输出。Select * From xsOrder
11、 by 身高 desc6. 检索“李林”同学不学的课程号。Select 课程号From kcWhere 课程号 not in ( Select 课程号 from xs, xs_kc Where xs.学号=xs_kc.学号 and xs.姓名=李林)7. 检索至少选修了两门课程的学生学号。Select 学号 From xs_kcGroup by 学号Having count(课程号)=28. 查询只有两门课不及格的学生姓名。Select 姓名From xs Where 学号 in (Select 学号 From xs_kcWhere 成绩=2)9. 检索选修课程包含“程明”同学所选课程之一的学
12、生学号。Select 学号From xs_kcWhere 课程号 in ( Select 课程号 from xs, xs_kc Where xs.学号=xs_kc.学号 and xs.姓名= 程明)10. 检索同时选修了课程号为101和102这两门课程的学生学号。方法一，使用自连接：Select a.学号From xs_kc a , xs_kc bWhere a.学号=b.学号 and a.课程号=101 and b. 课程号=102方法二，使用子查询：Select 学号From xsWhere 学号 in (select 学号 From xs_kc where 课程号=101 ) And 学
13、号 in (select 学号 From xs_kc where 课程号=102)11. 检索选修课程名为“计算机原理”的学生学号和姓名。Select a.学号,姓名From xs a , xs_kc b , kc cWhere a.学号=b.学号 and b.课程号=c. 课程号 and 课程名= 计算机原理12. 查询每门课的最高分的学生姓名。SELECT A.学号, 姓名, B.课程号, 成绩FROM XS A, XS_KC BWHERE A.学号=B.学号 AND 成绩=(SELECT MAX(成绩) FROM XS_KC CWHERE B.课程号=C.课程号)13. 检索选修课程包含
14、学号为061103的学生所修课程的学生学号。SELECT 学号，姓名FROM xsWHERE NOT EXISTS( SELECT * FROM kc , xs_kc a WHERE kc.课程号=a. 课程号 and 学号= 061103 and NOT EXISTS( SELECT * FROM xs_kc b WHERE b.学号=xs.学号 AND 课程号=kc.课程号 ) )14. 创建一个名为avg75的视图，包含所有平均成绩在75分以上的学生信息。Create view avg75 As Select *From xsWhere 学号 in ( Select 学号 From xs
15、_kc Group by 学号Having avg(成绩)75)第8章习题答案对于数据库scd，库中包含以下系、学生、班级各表： student （学号，姓名，年龄，班号） class（班号，专业名，系名，入学年份） department （系号，系名） 请使用T-SQL语言完成以下各题。1为数据库scd建立一个默认对象，使其对应于年龄为18，将其绑定到student表的年龄列上。Create default d_age as 18Go Exec sp_bindefault d_age , student.年龄2为数据库scd建立一个规则对象，并将其绑定到学生表的专业名列上，规定专业名的取值只
16、能为护理学、 地质勘探和考古学之一。 Create rule r_zym as zym in (护理学, 地质勘探,考古学) Go Exec sp_bindrule r_zym, xs.专业名3修改class表为其建立一个CHECK约束，检查入学年份是否小于2008。Alter table class Add constraint ck_rxnf check (入学年份=80 THEN 学习时间长 WHEN 学时=54 THEN 学习时间一般 ELSE 学习时间短 ENDFROM KC（2）在CJGL数据库中，使用case函数处理：如果学生的专业是“计算机”，显示热门，是“通信工程”显示一般，
17、否则显示冷门。SELECT 学号, 姓名, 专业热度= CASE 专业名 WHEN 计算机 THEN 热门 WHEN 通信工程 THEN 一般 ELSE 冷门 ENDFROM xs（3）计算18！=18*17*16*2*1。DECLARE i int , p bigintSELECT i=1, p=1WHILE i=18 BEGIN SET p=p*i SET i=i+1 ENDPRINT 18*17*16*2*1=+CAST(p AS char(20)（4）计算S = 1！+2！+3！+10！DECLARE i int , p bigint, s bigintSELECT i=1, p=1,
18、 s=0WHILE i=10 BEGIN SET p=p*i SET s=s+p SET i=i+1 ENDPRINT 1！+2！+3！+10！=+CAST(s AS char(20)2以下各题在CJGL数据库中实现：（1）创建一个自定义函数名为f_nl，根据一个出生日期计算年龄。并用1989-8-8调用该函数求年龄。create function f_年龄(出生日期 datetime, jt datetime)returns int asBeginDeclare 年龄 intSet 年龄 =year( jt)- year(出生日期)Return 年龄 endGo-调用declare jt d
19、atetimeset jt=getdate()select dbo.f_年龄(1988-1-2, jt)（2）创建一个自定义函数，可以求解任意数n的阶乘，即n!。并用n=10调用该函数。CREATE FUNCTION F_C(n INT)RETURNS INT ASBEGINDECLARE i int , p bigintSELECT i=1, p=1WHILE i=n BEGIN SET p=p*i SET i=i+1 ENDReturn pEND Go-调用select dbo. F_C (10)（4）分别用函数或存储过程实现以下功能：给定输入参数课程名，统计该课程成绩介于5859分的学生
20、人数。使用输入参数“数据库原理”调用该函数或该存储过程。-函数：Create function f_cj58 (cna char(20) returns intAs BEGINDECLARE c intSELECT c=count(*) from CJGL.dbo.xs_kc a , CJGL.dbo. kc b where a.课程号= b.课程号 and 课程名=cna and (成绩 between 58 and 59)Return c ENDGo以下函数调用：Select dbo.f_cj58 (数据库原理) as 5859分人数Go -存储过程：create procedure p_
21、cj58 cna char(20), c int outputasSELECT c=count(*) from CJGL.dbo.xs_kc a , CJGL.dbo. kc b where a.课程号= b.课程号 and 课程名=cna and (成绩 between 58 and 59)returnGo-存储过程调用DECLARE c1 intExec p_cj58 数据库原理, c1 output（5）创建存储过程来完成求解给定某个学生的学号返回该生的姓名和平均分的功能。Create procedure p_pj sno char(8) , sna char(8) output , s
22、avg int outputasSELECT sna=姓名, savg=avg(成绩) from CJGL.dbo.xs a , CJGL.dbo.xs_kc bwhere a.学号= b.学号 and a.学号=snoreturnGo-调用DECLARE sna char(8) , savg int Exec p_pj 061101 , sna output , savg output3以下各题在第六章习题（设计题）给出的数据库SCD中实现：（1）创建一个自定义函数用于统计各班级的人数，将class表增加一个人数字段，然后调用该函数将人数定义为计算列。USE SCDGOCREATE FUNC
23、TION F_CNT (cnum char(8) RETURNS intASBEGIN DECLARE cnt int SELECT cnt= count(*) FROM student WHERE 班号=cnum RETURN CNTENDGOUSE SCD /*假定用户函数F_CNT在此数据库中已定义*/CREATE TABLE class (班号 char(3) PRIMARY KEY, 专业名 char(20), 系名 char(20) references department(系名) ,入学年份 int人数 AS dbo. F_CNT (班号)（2）创建一个可以按给定学号输出学生的姓名、系名信息的存储过程。CREATE PROCEDURE stu_info sno char (7) AS SELECT a.学号, 姓名, 系名 FROM student a INNER JOIN class b ON a.班号 = b.班号 WHERE 学号=sno GO调用：EXEC stu_info 2008101
