数据库综合练习3.docx
《数据库综合练习3.docx》由会员分享，可在线阅读，更多相关《数据库综合练习3.docx（24页珍藏版）》请在冰豆网上搜索。
数据库综合练习3
数据库综合练习三
习题一
1•现有一个局部应用，包括两个实体：
“出版社”和“作者”，这两个实体是多对多的联系,请设计适当的属性，画出E-R图，再将其转换为关系模型（包括关系名、属性名、码和完整性约束条件）。
2.请设计一个图书馆数据库，此数据库中对每个借阅者保存的记录包括：
读者号，姓名，地址，性别，年龄，单位。
对每本书保存有：
书号，书名，作者，出版社。
对每本被借出的书保存有读者号、借出口期和应还口期。
要求：
给出该图书馆数据库的E-R图，再将其转换为关系模型。
3.图4-1是某个教务管理数据库的E-R图，请把它们转换为关系模型（图中关系、属性和联
系的含义，己在它旁边用汉字标出）。
空号&名*别教弄号姓名
*号潦尿名w*
图4-1教学管理数据库
4•图4-2是一个销售业务管理的E-R图，请把它转换成关系模型。
5•设有一家百货商店，已知信息有：
1）每个职工的数据是职工号、姓名、地址和他所在的商品部。
2）每一商品部的数据有：
它的职工，经理和它经销的商品。
3）每种经销的商品数有：
商品名.生产厂家、价格、型号（厂家定的）和内部商品代号（商店规定的）。
4）关于每个生产厂家的数据有：
厂名、地址、向商店提供的商品价格。
请设计该百货商店的概念模型，再将概念模型转换为关系模型。
注意某些信息可用属性表示，其他信息可用联系表示。
6•下列有关E-R模型向关系模型转换的叙述中。
不正确的是o
A.一个实体类型转换为一个关系模式
B.一个1:
1联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体所对应的
关系模式合并
C.一个l：
n联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体所对应的
关系模式合并
D.一个m：
n联系转换为一个关系模式
习题一解答
1.答：
转换后的关系模型如图6所示。
出版社（出版社名,地址，邮政编码）;
作者（姓名,性别，年龄，证件号码，单位）；出版（出版社名，作者姓名,出书数量，联系方式）。
?
出版彳仁出版社名.地址，邮政如码
作瓠迤性别.年龄.证件号码，单位？
?
?
?
?
?
2.答:
读者
该图书馆数据库的E-R图如图7
图7
其中：
读者：
读者号,姓名，地址，性别，年龄，单位；
图书.迤，书名，作者，出版社。
转换后的关系模型为：
借阅者（读者号,姓名，地址，性别，年龄，单位）:
书籍（书号,书名，作者，出版社）；
借阅（读者号,书号,借出口期，应还口期）。
3答：
转换后的关系模型如卜•:
Student（SNO,SN,SD,SA）；Teacher（TNO,TN,TD,TG）；Course（CNO,CN,PCNO）；ST（SNO,TNO）：
SC（SNO,CNO,G）：
TC（TNO,CNO）o
4・答：
转换后的关系模型如下工厂（厂名,厂长，地址）；产品（编号,型号，单价）；用户（姓名,地址，电话）；工厂■产品■用户（厂名，编号，姓名）。
5.答:
概念模型如图8所示。
关系模型为：
职工（职工号,姓名，住址，工作商品部）；商品部（商品部号,名称，经理职工号，经理名）;
商品（商品代号,价格，型号，出厂价格）；
生产厂家（厂名,地址）；
销售（商品代号，商品部号）:
生产（厂名,商品代号）。
©J.r
图8百货商店E-R图模型
图中：
职工：
职工号,姓名，住址；
商品部：
商品部号,名称；
商品：
商品代号,价格，型号，出厂价格:
生产厂家：
匚赛，地址。
6.答：
D
?
习题二
1.设职工社团数据库有三个基本表：
职工（职工号，姓名，年龄，性别）；
社会团体（编号，名称，负贵人，活动地点）；
参加（职工号，编号，参加口期）。
其中：
1）职工表的主码为职工号。
2）社会团体表的主码为编号；外码为负责人，被参照表为职工表，对应属性为职工号。
3〉参加表的职工号和编号为主码；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。
试用SQL语句表达下列操作：
1）定义职工表、社会团体表和参加表，并说明其主码和参照关系。
2）建立下列两个视图。
社团负贵人（编号，名称，负责人职工号，负贵人姓名，负贵人性别）；
参加人情况（职工号，姓名，社团编号，社团名称，参加口期）
3）查找参加唱歌队或篮球队的职工号和姓名。
4）查找没有参加任何社会团体的职工情况。
5）查找参加了全部社会团体的职工情况。
6）查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。
7）求每个社会团体的参加人数。
8）求参加人数最多的社会团体的名称和参加人数。
9）求参加人数超过100人的社会团体的名称和负责人。
10）把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。
2.SQL语言集数据查询、数据操作、数据定义和数据控制功能于一体，语句INSERT.DELETE.
UPDATA实现下列哪类功能o
A.数据查询B.数据操纵
C.数据定义D.数据控制
3.下面列出的关于视图（View）的条目中，不正确的是o
A.视图是外模式
B.视图是虚表
C.使用视图可以加快查询语句的执行速度
D.使用视图可以简化查询语句的编写
4.在SQL语言的SELECT语句中，能实现投影操作的是。
A.SELECTB.FROM
C.WHERED.GROUPBY
5.SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，语句ALTERTABLE
实现哪类功能o
A.数据查询B.数据操纵
C.数据定义D.数据控制
6.在关系数据库系统中，为了简化用户的查询操作，而又不增加数据的存储空间，常用的
方法是创建o
A.另一个表B.游标
C.视图D.索引
7.设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为：
学生（学号，姓名，性别，年龄，所在系）；
课程（课程号，课程名，先行课），
选课（学号，课程号，成绩）。
用Transact-SQL完成卜•列操作。
1）建立学生选课库。
2）建立学生、课程和选课表。
3）建立各表以主码为索引项的索引。
4）建立学生性别只能为“男”或“女”的规则，性别为“男”的缺省，并将它们绑定在学生表上。
5）建立在对选课表输入或更改数据时，必须服从参照完整性约束的INSERT和UPDATE触发器。
6）建立在删除学生记录时，同时也要把相应的选课记录删除的触发器。
7）查询各系及学生数，最后来出共有多少系和多少学生。
8）将学生表和选课表进行内连接、左外连接和右外连接。
9）列出学生学号、姓名及学习情况。
学习情况用好、较好、一般或较差表示。
当平均成绩人于85分时，学习情况为好：
当平均成绩在70〜85分之间，学习情况为较好；当平均成绩在60〜70分之间，学习成绩为一般；平均成绩在60分以下的为学习情况较差。
习题二解答
1.答：
1）CREATTABLE职工（职工号CHAR（10）NOTNULLUNIQUE.
姓名CHAR（8）NOTNULL,
年龄SMALLINT,
性别CHAR
（2）,
CONSTRAINTClCHECK（性别IN（“男"女'）））；
CREATTABLE社会团体（编号CHAR（8）NOTNULLUNIQUE,
名称CHAR（12）NOTNULL,
负贵人CHAR（8）,
活动地点VARCHAR（50）,
CONSTRAINTC2FOREIGNKEY（负贵人）REFERENCES职工（职工号））；
CREATTABLE参加（职工号CHAR（8）,
编号CHAR（8）,
参加口期DATE
CONSTRAINTC3PRIMARYKEY（职工号，编号），
CONSTRAINC3FOREIGNKEY（职工号）REFERENCES职工（职工号））；
2）CREATVIEW社团负责人（编号，名称，负贵人职工号，负责人姓名，负贵人性别）
ASSELECT编号，名称，负资人，姓名，性别
FROM社会团体，职工
WHERE社会团体，负资人=职工.职工号；
CREATVIEW参加人情况（职工号，姓名，社团编号，社团名称，参加口期）
ASSELECT参加.职工号，姓名，社会团体编号，名称，参加口期FROM职工，社会团体，参加
3）
WHERE职工.职工号=参加.职工号AND参加.编号=社会团体.编号；
SELECT职工号，姓名
FROM职工，社会团体，参加
WHERE职匚职工号=参加.职工号AND参加.编号=社会团体.编号
AND社会团体.名称IN（“唱歌队'篮球队'）
4）
SELECT*
FROM职工
WHERENOTEXISTS（SELECT*
FROM参加
WHERE参加.职工号=职工・职工号）:
SELECT*
FROM职工
WHERENOTEXISTS（SELECT*
FROM参加
WHERENOTEXISTS
（SELECT*
FROM社会团体
WHERE参加.职工号=职工・职工号AND
参加.编号=社会团体.编号））；
6）SELECT职工号
FROM职工
WHERENOTEXISTS（SELECT*
FROM参加参加1
WHERE参加1・职工号=T00I'ANDNOTEXISTS
（SELECT*
FROM参加参加2
WHERE参加2・编号=参加1・编号AND
参加2.职工号一职工.职工号））；
7）SELECTTCOUNT（职工号）
FROM参加
GROUPEY编号；
2.答：
B
3・答：
C
4.答：
A
5.答：
C
6.答：
C
7.答：
1）CREATEDATABASE学生选课库
ONPRIMARY（NANE=学生选课库，
FILENAME=‘C:
\msSQL\data\学生选课.mdf',
SIZE=4ME,
MAXSIZE=6MB>
FILEROWHT=2ME）
GO
2）CREATTABLE学生（学号CHAR10）PRIMARYKEYCLUSTERED,
姓名CHAR（8）,
性别CHAR
（2）,
年龄SMALLINT所在系VARCHAR（50））
GO
CREATTABLE课程（课程号CHAR（10）PRIMARYKEYCLUSTERED,
课程名VARCHAR（50）,
先行课VARCHAR（50））
GO
CREATETABLE选课（学号CHAR（10）,
课程号VARCHAR（50）,
成绩SMALLINT,
CONSTRAINTClPRIMARYKEY（学号，课程号），
CONSTRAINTC2FOREIGNKEY（学号）REFRENCES学生（学号），CONSTRAINTFOREIGNKEY（课程号）REFERENCES课程（课程号））GO
3）CREATEINDEXstudent_mdON学生（学号）
GO
REATEI**巨class.mdON课程（课程号）
GO
CREATEINDEXselecUndON选课（学号，课程号）
GO
4）CREATRULEvalue_mleAS©VALUEIN（'男，女'）
GO
CREATDEFAULE性别缺省AS'男’
GO
EXECsp_bmdmle4value_mle','学生•性别'
GO
EXECSp.binddefault'性别缺省学生•性别'
GO
5）CREATETRIGGERsc_insertON选课
FORINSERT
ASIF（SELECTCOLJNT（*）
FROM学生，mserted,课程
WHERE学生.学号=inswed・学号AND课程.课程号=mserted.课程号）=0
ROLLBACKTRANSACTION
GO
CREATETRIGGERsc_updat6ON选课
FORUPDATE
ASIF（SELECTCOUNT（*）
FROM学生，updated,课程
WHERE学生.学号updated.学号AND课程.课程号=updated.课程号）=0
ROLLBACKTRANSACTION
GO
6）CREATETRIGGERdelete_allON学生
FORDELETE
ASDELETE
FROM选课
WHERE选课.学号=deleted.学号
GO
7）SELECT所在系，COUNT（学号）
FROM学生
GROUPBY学生.所在系
COMPUTECOUNT（DISTINCT（所在系））
COMPUTECOUNT学号）BY所在系
GO
8）SELECT*
FROM学生INNERJOIN课程
GO
SELECT*
FROM学生LEFTOUTERJOIN课程
GO
SELECT*
FROM学生RIGHTOUTERJOIN课程
GO
9）SELECT选课.学号，学生.姓名，
学习情况=CASE
WHENAVG（成绩）〉=85THEN，好，
WHENAVG（成绩）＞=75ANDAVG（成绩产85THEN“较好'
WHENAVG（成绩）V60THEN，较差，
END
FROM学生选课
WHERE学生.学号=选课.学号
GROUPBY选课.学号
GO
习题三
1.设关系模式R,函数依赖集F={A-*C,C~A,B~AC,D-AC,BD-A}。
1）求出R的候选码。
2）求出F的最小函数依赖集。
3）将R分解为3NF,使其既具有无损连接性又具有函数依赖保持性。
2.设关系模式R〈A,B,C,D,E,F>,函数依赖集F={AB-E,AC-F,AD-B,B-C,C-*D}o
1）证明AB、AC、AD均是候选关键宇。
2）证明主属性C部分依赖于关键字AB,传递依赖于AD。
同时证明主属性D部分依赖于关键字AC,传递依赖于关键字AB。
3.设关系模式R,函数依赖集F={AB~E,BC-D,BE-C,CD-B,CE-AF,CF-BD,C-A,D-EF},求F的最小函数依赖集。
4.判断下面的关系模式是不是BCNF,为什么？
1）任何一个二元关系。
2）关系模式选课（学号，课程号，成绩），函数依赖集F={（学号，课程号）一成绩}。
3）关系模式R（A,B,C,D,E,F）,函数依赖集F={A-BC,BC-A,BCD-EF,E-C}。
5.设关系模式R（A,B,C,D,E,F）,函数依赖集F二{A-B,C-F,E~A,CE-A},将R分解为P={ABE,CDEFlo判断p是否是无损连接。
6.设关系模式R{B,0,I,S,Q.D},函数依赖集F二{S~D,I-S,1ST,B-Q}。
1）找出R的主码。
2）把R分解为ECNF,且具有无损连接性。
7.在关系模式选课（学号，课程号，成绩）中，“学号一课程号”正确吗？
为什么？
8.设有关系模式R（A,B,C）,数据依赖集F={AB—C,C—A},R属于第几范式？
为什么？
9.设有关系模式R（A,B,C,D）,数据依赖集F={A-*B,B~A,AC~D,EC-D,AD—C,BD~*C»A~*-*CD,B~*-*CD}o
1）求R的主码。
2）R是否为第4范式？
为什么？
3）R是否是BCNF?
为什么？
4）R是否是3NF?
为什么？
习题三解答
1.答：
1）R的候选码为BDo
2）①将F中的函数依赖都分解为右部为单属性的函数依赖。
F={A-C,CfA,E-A,B->C,D-A,D~C,ED-A}
2去掉F中冗余的函数依赖。
判断A-C是否冗余。
设：
Gl={C-A,B->AtB->C,D-A,D~C,ED-A},得：
（A）g=A
•••Cg（4）二.\A->C不冗余
判断C-A是不冗余。
设：
G2={A-C,B-*A,B-C,D~A,D-C,BD-A},得：
（U）s=C.*.c-*a不冗余
判断B~A是否冗余。
设：
G3={A-C,C-A,B-C,D~A,D-C,BD-A},得：
（B）：
3=ECA
VAe（B）：
3・•・£〜A冗余
判断E~C是否冗余。
设：
G4={A-C,C-A,DfA,D-C,ED-A},得：
（B）：
~=E
TCW（占）：
」・・.BfC不冗余
判断D-*A是否冗余。
设：
G5={A-C,C-A,B-*C,D~C,ED-A},得：
（°）：
°=DCA
.*.d-*a不冗余
判断D~C是否冗余。
设：
G6={A-C,C-A,B-*C,BD-*A},得：
TC@AD-*C不冗余
判断ED-A是否冗余。
设：
G7={A-C,C-A,EfC,D-C},得：
r=BDCA
・・a@
•••ED-*A冗余
F=（A^C,C~A,E~C,DfC}
3由于各函数依赖在部都为单属性.故：
Fm={A—C,C~A,EfC,D—C}。
3）T=（AC,BC,DC,BD）
4）
2.
答：
2）IB~C
・・.AB誌分》C
•:
AD-E,B-C
AAD>C
•?
C~D
AAC>C
•:
B~C,C-D
3.答：
AAB>C
•••AD为码
1将F中的函数依赖都分解为右部为单属性的函数依赖。
F={AB->E,EC-D,BE-C,CD-E,CE-A,CE-F,CF-E,CF~D,C->A,D~E,D~F}
2去掉F中冗余的函数依赖。
判断AB-E是否冗余。
设：
Gl={BC->D,EE~C,CD-E,CE-A,CE-F,CF-E,CF-D,C~A,D->E,D-F}
判断BC-D是否冗余。
设：
G2={AB->E,BE->C,CD-E,CE~A,CE-F,CF-E,CF~D,C~A,D~E,D-F}
得：
（BC）心=BCAEFD
IDE（〃C）gZ.BC-DTl余
判断BE->C是否冗余。
设：
G3={AB->E,CD->B,CE->A,CE~F,CF~E,CF-D,C~A,D-E,D~F}
得：
（BE）»be
：
C/（BE）g/.be-*c不冗余
判断CD-B是否冗余。
设：
G4={AB~E,EEfCCE~A、CE-F,CF-E,CF~D,C~A,D~E,D~F}
得：
CQ）5=CDAEFB
得：
（Og=c
*.*（Cs>/.C-*A不冗余
判断D-E是否冗余。
设：
G10={AB~E,EE-C,CE-F,CF-B,CF~D,C-A,D-F}
得：
（Q）go=DF
・.・e2）GIO••.D-E不冗余
判断D-F是否冗余。
设：
Gll={AB->E,BE->C,CE->F,CF~E,CF~D,C~A,D-E}
得：
（Q）gi=dE
E0（Q）gi.*.D-*F不冗余
AF={AB-E,EE-C,CE~F,CF-*B,CF~D,C-A,D-E,D~F}求得.Ff=F
•/DeFf・••不能以FfD代替CF~D
在决定因素中去掉F。
求得：
Cf=CA
•••D0Cp•••不能以C-*D代替CF-D
•••不能以CF-D不冗余
・・・F={AB~E,BE->C,CE-F,CF~E,CF~D,C-A,D~E,D-F}
4答：
1）是BCNFo二元关系中或为全码，或为一个单属性码候选码。
2）是BCNFo关系模式中只有一个候选码。
3）不是ECNF、因为模式中存在候选码为AD、BCD和EE。
显然C对AD是部分依赖。
5答：
••Pln5=EUi-U：
=AB
U1AU：
-*U1-U2={E->AB}={E-*A,E->B}
UiPUz-Ui-UzeF
・••该分解具备无损连接。
6答：
1）R的主码为IBOo
2）F={S->D,I-S,1->Q・E-Q}
令P=BOISQD
1由于R的码为IBO.选择S-D分解。
得出：
Q={Si,S2J
其中Si=SD,Fi={S~D}；
S2=BOISQ,F2={I-S,I-Q,E-Q}。
显然Sj不服从BCNF,需要继续分解：
2对Si分解，S2的码为IEO,选择IfS分解。
得出:
Q={Si,s2,S3}
其中：
S3=IS・f3={I-S}
S4=BOIQ・F尸{IfQ,EfQ}
显然.不服从BCNF,还需要继续分解。
3对S4分解。
S4的码为LBO,选择I-Q分解。
得出：
Q={Si,S3,S5,s（s}o
其中S5=IQ，f5={Ifq}；
S6=BIO,F6=0°
4最后的分解为：
P={SD,IS.IQ.EIO}。
7答：
正确。
因为学号能够多值决定课程号。
8答：
BCNFo由于A多值依赖于动而C不是码・故不服从4NF。
但在函数依赖式中C依赖于码AB・故该模式服从BCNFo
9答：
1）候选码为AC,BC.AD,ED、可选其中之一为主码。
2）不服从4NFo在多值依赖中泱定因素中不包含码。
3）不服从BCNFo在函数依赖中决定因素中不包含码。
4）服从3NF。
该模式中不存在非主属性。
1.今有两个关系模式：
职工（职工号，姓名，年龄，职务，工资，部门号）；部门（部门号，名称，经理名，地址，电话）。
请FRSQL的GRANT和REVOKE语句（加上视图机制），完成以卞授权定义或存取控制功能。
1）用户王明对两个表有SELECT权力。
2）用户李勇对两个表有INSERT和DELETE权力。
3）用户刘星对职工表有SELECT权利，对工资字段具有更新权力。
4）用户张新具有修改这两个表的结构的权力。
5）用户周平具有对两个表的所有权力（读、插、改、删数据），并具有给其他用户授权的权利。
6）用户杨兰具有从每个部门职工中SELECT最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。
2.假设有下面两个关系模式：
职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；部门（部门号，名称，经理名，电话），其中部门号为主码.
用SQL语言定义这两个关系模式.要求在模式中完成以卞完整性约束条件的定义：
1）定义每个模式的主码。
2）定义参照完整性。
升级会员 