数据库原理与 SQL 全部答案.docx
《数据库原理与 SQL 全部答案.docx》由会员分享，可在线阅读，更多相关《数据库原理与 SQL 全部答案.docx（20页珍藏版）》请在冰豆网上搜索。
数据库原理与SQL全部答案
第一章习题答案
DDCBDADD
2、某医院病房计算机管理中需要如下信息：
一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个。
完成：
设计该计算机管理系统的E-R图
4．假设某公司的业务规则如下：
（1）公司下设几个部门，如技术部、财务部、市场部等；
（2）每个部门承担多个工程项目，每个工程项目属于一个部门；
（3）每个部门有多名职工，每一名职工只能属于一个部门；
（4）一个部门可能参与多个工程项目，且每个工程项目有多名职工参与施工。
根据职工在工程项目中完成的情况发放酬金；
（5）工程项目有工程号、工程名两个属性；部门有部门号、部门名称两个属性；职工有职工号、姓名、性别属性。
请根据上述规则设计E-R模型。
第3章设计题答案
1．学校有若干个系，每个系有若干班级和教研室，每个教研室有若干教师，每名教师只教一门课，每门课可由多个教师教；每个班有若干学生，每个学生选修若干课程，每门课程可由若干学生选修。
请用E-R图画出该学校的概念模型，注明联系类型，再将E-R模型转换为关系模型。
假定：
主码____或#
外码或_____
学院（院名，地址，成立日期，教师人数，学生人数）
系（系名，系址，系主任，院名）
班级（班号，专业名，入学年份，学生数，班级导师，系名）
学生（学号，姓名，性别，出生日期，联系电话，籍贯，班号）
教研室（教研室名称，教师人数，电话，办公室编号，系名）
教师（教工号，姓名，性别，出生日期，联系电话，教研室名称,课号）
课程（课号，课名，学时，学分，开课学期，必修课否）
选修（学号，课号，成绩）
2．工厂生产的每种产品由不同的零件组成，有的零件可用于不同的产品。
这些零件由不同的原材料制成，不同的零件所用的材料可以相同。
一个仓库存放多种产品，一种产品存放在一个仓库中。
零件按所属的不同产品分别放在仓库中，原材料按照类别放在若干仓库中（不跨仓库存放）。
请用E-R图画出此关于产品，零件，材料，仓库的概念模型，注明联系类型，再将E-R模型转换为关系模型。
产品（产品名，型号，重量，仓库号，数量c）
零件（零件名，规格）
材料（材料编号，名称，仓库号，数量a）
仓库（仓库号，地点，面积，保管员）
产品-零件（产品名，零件名，组装日期b）
零件-材料（材料编号，零件名，生产日期a）
零件-仓库（零件名，仓库号，数量b）
3、一个图书馆理系统中有如下信息：
图书：
书号、书名、数量、位置
借书人：
借书证号、姓名、单位
出版社：
出版社名、邮编、地址、电话、E-mail
其中约定：
任何人可以借多种书，任何一种书可以被多个人借，借书和还书时，要登记相应的借书日期和还书日期；一个出版社可以出版多种书籍，同一本书仅为一个出版社所出版，出版社名具有惟一性。
根据以上情况，完成如下设计：
（1）设计系统的E-R图；
（2）将E-R图转换为关系模式，并指出函数依赖；
（3）指出转换后的每个关系模式的关系键（主键，外键）。
解答：
（1）
图书关系模式：
图书（书号，书名，数量，位置，出版社名）
借书人关系模式：
借书人（借书证号，姓名，单位）
出版社关系模式：
出版社（出版社名，邮编，地址，电话，E-mail）
借阅关系模式：
借阅（借书证号，书号，借书日期，还书日期）
关系主键：
（借书证号，书号）
外键1：
借书证号
外键2：
书号
第五章二设计题答案
--3.
CREATEDATABASEzygl
ON
（NAME='zygl_Data',
FILENAME='E:
\sql\zygl_Data.MDF',
SIZE=1,MAXSIZE=30,FILEGROWTH=10%
）
LOGON
（NAME='zygl_Log',
FILENAME='E:
\sql\zygl_Log.LDF',
SIZE=3,FILEGROWTH=1
）
GO
--4
ATLERDATABASEZYGL
MODIFYFILE
（NAME=ZYGL_DATA,FILEGROWTH=5）
--5
ATLERDATABASEZYGL
ADDFILE
（NAME=ZYGLBAK,
FILENAME='E:
\sql\zygl_Data1.NDF',
SIZE=1,MAXSIZE=50,FILEGROWTH=10%）
--6
DROPDATABASEZYGL
第六章二设计题答案
--1.
createdatabasescd
go
Usescd
Go
CREATETABLEdepartment
（系号char（3）PRIMARYKEY,
系名char（20）notnullunique
）
Go
CREATETABLEclass
（班号char（3）PRIMARYKEY,
专业名char（20）,
系名char（20）referencesdepartment（系名）,
入学年份int
）
Go
CREATETABLEstudent
（学号char（7）PRIMARYKEY,
姓名char（8）NOTNULL,
年龄intconstraintck_nlCHECK（年龄>=15and年龄<=40）
constraintdf_nlDEFAULT18,--指定约束名ck_nl和df_nl,以便以后好删除
班号char（3）referencesclass（班号）
）
Insertintodepartmentvalues（'001','数学'）;
Insertintodepartmentvalues（'002','计算机'）;
Insertintodepartmentvalues（'003','化学'）
Insertintoclassvalues（'101','软件','计算机',2005）;
Insertintoclassvalues（'102','微电子','计算机',2006）;
Insertintoclassvalues（'111','无机化学','化学',2004）;
Insertintoclassvalues（'112','高分子化学','化学',2006）
Insertintostudentvalues（'2008101','张山',18,'101'）;
Insertintostudentvalues（'2008102','李斯',16,'102'）;
Insertintostudentvalues（'2008103','王玲',17,'111'）
Insertintostudentvalues（'2008104','李飞',19,'112'）
--2.
--1）
Insertintodepartmentvalues（‘006’,’物理系‘）
Insertintodepartmentvalues（‘008’,’经济系‘）
--2）
Deleteclasswhere入学年份<2004
--3）
Updatestudentset班号='111'where姓名='张山'
--4）
Updatestudentset年龄=年龄+1
--3.
--1）
--要删除年龄列，应先删除该列上的约束，再删除
Altertablestudentdropconstraintck_nl,df_nl
--删除年龄列
Altertablestudentdropcolumn年龄
Altertablestudentadd出生日期datetime
--2）
Altertableclass
Add班长学号char（7）referencesstudent（学号）
--上机题答案
--创建数据库ZYCL
createdatabaseZYGL
go
--使用数据库ZYGL
usezygl
go
--创建数据表
--1.部门表
createtable部门表
（
部门号char
（2）primarykey,
部门名char（10）notnullunique,
电话char（4）
）;
go
--2.职员表
createtable职员表
（
员工号char（3）primarykey,
姓名char（8）notnull,
性别char
（2）,
部门号char
（2）references部门表（部门号）,
出生日期smalldatetime,
手机号码char（11）,
工龄tinyintconstraintck_glcheck（工龄>=0and工龄<=35）,
备注text
）;
go
--3.工资表
createtable工资表
（
员工号char（3）nullreferences职员表（员工号）,
基本工资decimal（7,2）,
津贴decimal（5,2）,
三金扣款decimal（7,2）,
应发工资as基本工资+津贴,
实发工资as基本工资+津贴-三金扣款
）
go
--4.工资发放表
createtable工资发放表
（发放编号intidentity（200701,2）,
发放年月smalldatetime,
员工号char（3）nullreferences职员表（员工号）,
实发工资decimal（7,2）--以后可以用函数调用dbo.fn（员工号）
）
go
第7章习题答案
1.查询“数据库原理”这门课程的学分和开课学期。
Select课程号，开课学期
Fromkc
Where课程名=’数据库原理’
2.查询身高在175cm以上的男生信息。
Select*
Fromxs
Where性别=’男’and身高>175
3.查询所有姓“李”同学的基本情况。
Select*
Fromxs
Where姓名like‘李%’
4.查询至少选修了两门课程的学生学号。
Select学号
Fromxs_kc
Groupby学号
Havingcount（课程号）>=2
5.将所有学生信息按身高逆序输出。
Select*
Fromxs
Orderby身高desc
6检索平均成绩在75分以上的学生姓名、性别和专业。
方法一：
Select姓名，性别，专业名
Fromxs
Where学号in（
Select学号
Fromxs_kc
Groupby学号
Havingavg（成绩）>75）
方法二：
Select姓名，性别，专业名
Fromxs_kcJOINxsONxs.学号=xs_kc.学号
Groupbyxs.学号,姓名，性别，专业名
Havingavg（成绩）>75
7.检索“李林”同学不学的课程号。
Select课程号
Fromkc
Where课程号notin（
Select课程号fromxs,xs_kc
Wherexs.学号=xs_kc.学号andxs.姓名=’李林’）
8.查询只有两门课不及格的学生姓名。
Select姓名
Fromxs
Where学号in（Select学号
Fromxs_kc
Where成绩<60
Groupby学号
Havingcount（课程号）=2）
9.检索选修课程包含“程明”同学所选课程之一的学生学号。
Selectdistinct学号
Fromxs_kc
Where课程号in（
Select课程号fromxs,xs_kc
Wherexs.学号=xs_kc.学号andxs.姓名=’程明’）
10.检索同时选修了课程号为101和102这两门课程的学生学号。
方法一，使用自连接：
Selecta.学号
Fromxs_kca,xs_kcb
Wherea.学号=b.学号anda.课程号=’101’andb.课程号=’102’
方法二，使用子查询：
Select学号
Fromxs
Where学号in（select学号Fromxs_kcwhere课程号=’101’）
And学号in（select学号Fromxs_kcwhere课程号=’102’）
Select学号
Fromxs
Where学号in（select学号Fromxs_kcwhere课程号=’101’And学号in（select学号Fromxs_kcwhere课程号=’102’））
方法三、
Select学号
Fromxs_kc
Where课程号=’101’or课程号=’102’
groupby学号havingcount（学号）=2
11.检索选修课程名为“数据库原理”的学生学号和姓名。
Selecta.学号,姓名
Fromxsa,xs_kcb,kcc
Wherea.学号=b.学号andb.课程号=c.课程号and课程名=’数据库原理’
12.查询每门课的最高分的学生姓名。
相关查询法
SELECTA.学号,姓名,B.课程号,成绩
FROMXSA,XS_KCB
WHEREA.学号=B.学号AND成绩=
（SELECTMAX（成绩）FROMXS_KCC
WHEREB.课程号=C.课程号）
或派生表法
SELECTA.姓名,B.课程号,B.成绩,C.最高成绩
FROMXSA,XS_KCB,（SELECT课程号,MAX（成绩）最高成绩FROMXS_KCgroupby课程号）C
WHEREA.学号=B.学号ANDB.课程号=C.课程号andB.成绩=C.最高成绩
13.检索选修课程包含学号为081101的学生所修课程的学生学号。
方法一，
selectdistinct学号
fromxs_kc
where课程号in（select课程号
fromxs_kc
where学号='081101'）
groupby学号
havingcount（课程号）=
（selectcount（课程号）
fromxs_kc
where学号='081101'）
方法二，
SELECT学号，姓名
FROMxs
WHERENOTEXISTS（
SELECT*
FROMkc,xs_kca
WHEREkc.课程号=a.课程号and学号=’081103’
andNOTEXISTS（
SELECT*
FROMxs_kcb
WHEREb.学号=xs.学号AND课程号=kc.课程号））
14.创建一个名为avg75的视图，包含所有平均成绩在75分以上的学生信息。
Createviewavg75
As
Select*
Fromxs
Where学号in（
Select学号
Fromxs_kc
Groupby学号
Havingavg（成绩）>75）
Createviewavg75
As
Select姓名，性别，专业名，avg（成绩）
Fromxs_kcJOINxsONxs.学号=xs_kc.学号
Groupby姓名，性别，专业名
Havingavg（成绩）>75
第8章习题答案
对于数据库scd，库中包含以下系、学生、班级各表：
student（学号，姓名，年龄，班号）
class（班号，专业名，系名，入学年份）
department（系号，系名）
请使用T-SQL语言完成以下各题。
1．将student表的班号与姓名这两列组合创建一个升序的非聚集索引。
Createnonclusteredindexix_bj_xmonstudent（班号asc,姓名asc）
2．为department表的系名建立一个唯一索引，如果输入了重复的键，将忽略该INSERT或UPDATE语句，并使用填充因子FILLFACTOR为50%。
Createuniqueindexuqix_xmondepartment（系名asc）
WITHIGNORE_DUP_KEY，FILLFACTOR=50
3．为数据库scd建立一个默认对象，使其对应于年龄为18，将其绑定到student表的年龄列上。
Createdefaultd_ageas18
Go
Execsp_bindefault‘d_age’,‘student.年龄’
4．为数据库scd建立一个规则对象，并将其绑定到学生表的专业名列上，规定专业名的取值只能为‘护理学’、‘地质勘探’和‘考古学’之一。
Createruler_zymas@zymin（‘护理学’,‘地质勘探’,’考古学’）
Go
Execsp_bindrule‘r_zym’,‘xs.专业名’
5．修改class表为其建立一个CHECK约束，检查入学年份是否小于2008。
Altertableclass
Addconstraintck_rxnfcheck（入学年份<2008）
第9章答案：
三、设计题
1．使用流程控制语句编写程序：
（1）在CJGL数据库中，使用case函数处理：
如果课程的学时在80以上显示学习时间长,学时在54~80显示学习时间一般,否则显示学习时间短。
SELECT课程号，课程名，学习时间=
CASE
WHEN学时>=80THEN'学习时间长'
WHEN学时>=54THEN'学习时间一般'
ELSE‘学习时间短’
END
FROMKC
（2）在CJGL数据库中，使用case函数处理：
如果学生的专业是“计算机”，显示热门，是“通信工程”显示一般，否则显示冷门。
SELECT学号,姓名,专业热度=
CASE专业名
WHEN‘计算机’THEN'热门'
WHEN‘通信工程’THEN'一般'
ELSE'冷门'
END
FROMxs
（3）计算18！
=18*17*16…*2*1。
DECLARE@iint,@pbigint
SELECT@i=1,@p=1
WHILE@i<=18
BEGIN
SET@p=@p*@i
SET@i=@i+1
END
PRINT'18*17*16…*2*1='+CAST（@pASchar（20））
（4）计算S=1！
+2！
+3！
+……+10！
DECLARE@iint,@pbigint,@sbigint
SELECT@i=1,@p=1,@s=0
WHILE@i<=10
BEGIN
SET@p=@p*@i
SET@s=@s+@p
SET@i=@i+1
END
PRINT'1！
+2！
+3！
+……+10！
='+CAST（@sASchar（20））
2．以下各题在CJGL数据库中实现：
（1）创建一个自定义函数名为f_nl，根据一个出生日期计算年龄。
并用‘1989-8-8’调用该函数求年龄。
createfunctionf_年龄（@出生日期datetime）
returnsintas
Begin
Declare@年龄int
Set@年龄=year（getdate（））-year（@出生日期）
Return@年龄
end
Go
--调用
selectdbo.f_年龄（'1988-1-2'）
（2）创建一个自定义函数，可以求解任意数n的阶乘，即n!
。
并用n=10调用该函数。
CREATEFUNCTIONF_C（@nINT）
RETURNSINTAS
BEGIN
DECLARE@iint,@pbigint
SELECT@i=1,@p=1
WHILE@i<=@n
BEGIN
SET@p=@p*@i
SET@i=@i+1
END
Return@p
END
Go
--调用
selectdbo.F_C（10）
（3）创建一个名为“某学期开课一览表”的内嵌表值函数，实现输入某个学期，输出该学期所开课程的课程名，学时和学分的功能；调用该函数检索第一学期的开课情况。
Createfunctionf某学期开课一览表（@paraint）returnstable
Asreturn
（select课程名，学时,学分fromCJGL.dbo.kcwhere开课学期=@para）
Go
以下调用即为内嵌表值函数的调用：
Select*fromf某学期开课一览表
（1）
Go
（4）分别用函数或存储过程实现以下功能：
给定输入参数课程名，统计该课程成绩介于58~59分的学生人数。
使用输入参数“数据库原理”调用该函数或该存储过程。
----函数：
Createfunctionf_cj58（@cnachar（20））returnsint
As
BEGIN
DECLARE@cint
SELECT@c=count（*）fromCJGL.dbo.xs_kca,CJGL.dbo.kcb
wherea.课程号=b.课程号and课程名=@cnaand（成绩between58and59）
Return@c
END
Go
以下函数调用：
Selectdbo.f_cj58（‘数据库原理’）as[58~59分人数]
Go
-----存储过程：
createprocedurep_cj58
@cnachar（20）,@cintoutput
as
SELECT@c=count（*）fromCJGL.dbo.xs_kca,CJGL.dbo.kcb
wherea.课程号=b.课程号and课程名=@cnaand（成绩between58and59）
return
Go
--存储过程调用
DECLARE@c1int
Execp_cj58‘数据库原理’,@c1output
（5）创建存储过程来完成求解给定某个学生的学号返回该生的姓名和平均分的功能。
Createprocedur
升级会员 