数据库综合练习.docx
《数据库综合练习.docx》由会员分享，可在线阅读，更多相关《数据库综合练习.docx（20页珍藏版）》请在冰豆网上搜索。
数据库综合练习
数据库综合练习三
习题一
1.现有一个局部应用，包括两个实体：
“出版社”和“作者”，这两个实体是多对多的联系，请设计适当的属性，画出E-R图，再将其转换为关系模型（包括关系名、属性名、码和完整性约束条件）。
2.请设计一个图书馆数据库，此数据库中对每个借阅者保存的记录包括：
读者号，姓名，地址，性别，年龄，单位。
对每本书保存有：
书号，书名，作者，出版社。
对每本被借出的书保存有读者号、借出日期和应还日期。
要求：
给出该图书馆数据库的E-R图，再将其转换为关系模型。
3.图4-1是某个教务管理数据库的E-R图，请把它们转换为关系模型（图中关系、属性和联系的含义，已在它旁边用汉字标出）。
图4-1教学管理数据库
4.图4-2是一个销售业务管理的E-R图，请把它转换成关系模型。
5.设有一家百货商店，已知信息有：
l）每个职工的数据是职工号、姓名、地址和他所在的商品部。
2）每一商品部的数据有：
它的职工，经理和它经销的商品。
3）每种经销的商品数有：
商品名、生产厂家、价格、型号（厂家定的）和内部
商品代号（商店规定的）
4）关于每个生产厂家的数据有：
厂名、地址、向商店提供的商品价格。
请设计该百货商店的概念模型，再将概念模型转换为关系模型。
注意某些信息
可用属性表示，其他信息可用联系表示。
6.下列有关E-R模型向关系模型转换的叙述中。
不正确的是。
A.一个实体类型转换为一个关系模式
B.—个1:
1联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体
所对应的关系模式合并
C.一个1:
n联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体
所对应的关系模式合并
D.一个mn联系转换为一个关系模式
习题一解答
1.答：
转换后的关系模型如图6所示。
出版社（出版社名，地址，邮政编码）;
作者（姓名，性别，年龄，证件号码，单位）
出版（出版社名，作者姓名，出书数量，联系方式）
图6
2.答:
该图书馆数据库的E—R图如图7
图7
其中：
读者：
读者号，姓名，地址，性别，年龄，单位；
图书•书号，书名，作者，出版社。
转换后的关系模型为：
借阅者（读者号，姓名，地址，性别，年龄，单位）
书籍（书号，书名，作者，出版社）;
借阅（读者号，书号，借出日期，应还日期）。
3答：
转换后的关系模型如下：
Student（SNOSNSDSA）;
Teacher（TNO,TN,TD,TG；
Course（CNQCNPCNO;
ST（SNQTNQ;
SC（SNQCNQG）;
TC（TNQCNQ）
4•答：
转换后的关系模型如下
工厂（厂名，厂长，地址）；
产品（编号，型号，单价）；
用户（姓名，地址，电话）；
工厂-产品-用户（厂名，编号，姓名）。
5.答：
概念模型如图8所示。
关系模型为：
职工（职工号，姓名，住址，工作商品部）；
商品部（商品部号，名称，经理职工号，经理名）
商品（商品代号，价格，型号，出厂价格）
生产厂家（厂名，地址）;
销售（商品代号，商品部号）;
生产（厂名，商品代号）。
图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、
DELETEUPDAT实现下列哪类功能。
A.数据查询B.数据操纵
C.数据定义D.数据控制
3.下面列出的关于视图（View）的条目中，不正确的是。
A.视图是外模式
B.视图是虚表
C.使用视图可以加快查询语句的执行速度
D.使用视图可以简化查询语句的编写
4.在SQL语言的SELECT吾句中，能实现投影操作的是。
A.SELECTB.FROM
C.WHERED.GROUPBY
5.
ALTER
SQL吾言集数据查询、数据操纵、数据定义和数据控制功能于一体，吾句
TABLE实现哪类功能o
A.数据查询B.数据操纵
C.数据定义D.数据控制
6.在关系数据库系统中，为了简化用户的查询操作，而又不增加数据的存储空间，常用的方法是创建o
A.另一个表B.游标
C.视图D.索引
7.设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为：
学生（学号，姓名，性别，年龄，所在系）；课程（课程号，课程名，先行课），选课（学号，课程号，成绩）o
用Transact-SQL完成下列操作。
1）建立学生选课库。
2）建立学生、课程和选课表。
3）建立各表以主码为索引项的索引
4）建立学生性别只能为“男”或“女”的规则，性别为“男”的缺省，并将它们绑定在学生表上。
5）建立在对选课表输入或更改数据时，必须服从参照完整性约束的INSERT和
UPDATE虫发器。
6）建立在删除学生记录时，同时也要把相应的选课记录删除的触发器。
7）查询各系及学生数，最后来出共有多少系和多少学生。
8）将学生表和选课表进行内连接、左外连接和右外连接。
9）列出学生学号、姓名及学习情况。
学习情况用好、较好、一般或较差表示。
当平均成绩大于85分时，学习情况为好；当平均成绩在70〜85分之间，学习情况为较好；当平均成绩在60〜70分之间，学习成绩为一般；平均成绩在60分以下的为学习情况较差。
习题二解答
1．答：
1）CREATTABL职工（职工号CHAR（10）NOTNULLUNIQUJE
姓名CHAR（8）NOTNULL，
年龄SMALLINT，
性别CHAR
（2）,
CONSTRAINTC1CHECK性另0IN（'男’，’女’）））；
CREATTABLED会团体（编号CHAR8）NOTNULLUNIQUE
名称CHAR12）NOTNUL，L
负责人CHAR（8），
活动地点VARCHAR50），
CONSTRAINC2FOREIGNKEY（负责人）REFERENC职工（职工号））;
CREATTABL参加（职工号CHAR（8）,
编号CHAR（8），
参加日期DATE
CONSTRAINTC3PRIMARYKEYR工号，编号）,
CONSTRAIC3FOREIGNKEY（职工号）REFERENC职工（职工号））;
2）CREATVIEW社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性另）
ASSELECTS号，名称，负责人，姓名，性别
FROMi会团体，职工
WHER社会团体，负责人二职工.职工号；
CREATVIEW参加人情况（职工号，姓名，社团编号，社团名称，参加日期）
ASSELECT参加.职工号，姓名，社会团体编号，名称，参加日期
FROM只工，社会团体，参加
WHER职工.职工号二参加.职工号AND参加.编号二社会团体.编号；
3）SELECT职工号，姓名
FROM职工，社会团体，参加
WHER职工.职工号二参加.职工号AND参加.编号二社会团体.编号
AND社会团体.名称IN（'唱歌队’，’篮球队’）
4）SELECT*
FROM职工
WHERENOTEXISTSSELECT*
FROM参加
WHER参加.职工号二职工.职工号）；
5）SELECT*
FROh职工
WHERENOTEXISTSSELECT*
FROM参加
WHERENOTEXISTS
（SELECT*
FROMtt会团体
WHER参加.职工号二职工.职工号AND
参加．编号=社会团体．编号））；
6）SELECT职工号
FROMRX
WHERENOTEXIST（SSELECT*
FROM^力口参加1
WHER参加1.职工号二’1001'ANDNOTEXISTS
（SELECT*
FROM^力口参加2
WHER参力口2.编号二参加1.编号AND
参加2.职工号一职工.职工号））；
7）SELECTTCOUNT职工号）
FROM参加
GROUPB编号；
2.答：
B
3.答：
C
4.答：
A
5.答：
C
6.答：
C
7.答：
l）CREATEDATABASE生选课库
ONPRIMARYNANE学生选课库，
FILENAM些’C:
\学生选课.mdf'
SIZE=4MB，
MAXSIZ&6MB
FILEROWHT=2M）B
GO
2）CREATTABLED生（学号CHAR10PRIMARYKEYCLUSTERED
姓名CHARY8），
性别CHARY2），
年龄SMALLINT
所在系VARCHAYR50））
GO
CREATTABLE?
程（课程号CHAR（10）PRIMARYKEYCLUSTERED
课程名VARCHAYR50），
先行课VARCHA（R50））
GO
CREATETABL选课（学号CHAR（10）,
课程号VARCHA（R50），
成绩SMALLINT，
CONSTRAINTC1PRIMARYKEY学号，课程号）,
CONSTRAINTC2FOREIGNKEY学号）REFRENCE学生（学号），
CONSTRAINTFOREIGNKEY?
程号）REFERENCES程（课程号））
GO
3）CREATEINDEXstudent_indON学生（学号）
GO
REATE＊I＊巨class_indON课程（课程号）
GO
CREATEINDEXselect_indON选课（学号，课程号）
GO
4）CREATRULEvalue_ruleAS@VALUEIN（'男'，'女'）
GO
CREATDEFAUL性别缺省AS'男’
GO
EXECsp_bindrule'value_rule'，'学生．性别'
GO
EXECSp_binddefault'性别缺省'，'学生．性别'
GO
5）CREATETRIGGERsc_insertON选课
FORINSERT
ASIF（SELECTCOLJN（T*）
FROM学生，inserted，课程
WHER学生.学号二inserted.学号AND课程.课程号二inserted.课
程号）=0
ROLLBACKTRANSACTION
GO
CREATETRIGGERsc_updat6ON选课
FORUPDATE
ASIF（SELECTCOUN（T*）
FROM学生，updated，课程
WHER学生.学号二updated.学号AND课程.课程号二updated.课程号）=0
ROLLBACKTRANSACTION
GO
6）CREATETRIGGERdelete_allON学生
FORDELETE
ASDELETE
FROM选课
WHER选课.学号二deleted.学号
GO
7）SELECT所在系，COUNT学号）
乡刑•韦煮'合煮®^L0313S（6
09
a>JI0Pd3丄no丄H9心韦訥ONT
』0313S
09
SlNiorda丄no丄dan韦訥obt
¥丄0313S09
3®NI0rd3NNI韦訥ONT
』0313S（809
玄丑均人日（帥noom丄ndi/\ioo
（（玄丑均）丄onllsicni^inoom丄ndi/\ioo
玄丑均-^adnodo
学习情况=CASE
WHENAVG成绩）＞=85THEN'好’
WHENAVG（绩）＞=75ANDAVG成绩）＜85THEN'较好’
WHENAVG（绩）v60THEN'较差’
END
FROM学生选课
WHER学生.学号二选课.学号
GROUPB选课.学号
GO
习题三
1.设关系模式R＜AB,C,D＞,函数依赖集F={AtC,A,B^AC,D^AC
BD^A。
1）求出R的候选码。
2）求出F的最小函数依赖集。
3）将R分解为3NF使其既具有无损连接性又具有函数依赖保持性
2.设关系模式R,函数依赖集F={ASE,ASF,AXB,
BtC,C^D}o
1）证明ABACAD均是候选关键宇。
2）证明主属性C部分依赖于关键字AB,传递依赖于AQ同时证明主属性D部分依赖于关键字AC,传递依赖于关键字ABo
3.设关系模式R,函数依赖集F={AB^E,BCTD,BE^C,
CD^B,CLAF,CFtBD,CtA,DTEF}，求F的最小函数依赖集。
4.判断下面的关系模式是不是BCNF为什么
1）任何一个二元关系。
2）关系模式选课（学号，课程号，成绩），函数依赖集F={（学号，课程号）
T成绩}o
3）关系模式R（A,B,C,D,E,F）,函数依赖集F={AtBCBCTA,BCTEF,
ETC}o
5.设关系模式R（ABCDEF）函数依赖集F={ATBCTFETACETA}将R分解为P={ABECDEFo判断p是否是无损连接。
6.设关系模式R{BOISQ．D}函数依赖集F={STDITSISTQBTQ}o
l）找出R的主码
2）把R分解为BCNF且具有无损连接性
7.
在关系模式选课（学号,课程号,成绩）中,
“学号tt课程号”正确吗为
什么
8.
设有关系模式
R（
A,B,C）,数据依赖集
F={ABtC,CttA},R属于第几
范式为什么
9.
设有关系模式
R（
A,B,C,D）,数据依赖集
F={AtB,BtA,ACtD,BCtD
AD^C,BD^C,CD,BnCD｝。
1）求R的主码。
2）R是否为第4范式为什么
3）R是否是BCNF为什么
4）R是否是3NF为什么
习题三解答
1．答：
1）R的候选码为BD。
2）①将F中的函数依赖都分解为右部为单属性的函数依赖。
F={2C,SA,BtaBtC,D^A,D^C,BD^A}
②去掉F中冗余的函数依赖
判断A-C是否冗余
设：
G1={
C—A，B—A，
B—C，D—A，
D—C，
BD—A}，
得：
（A）G1=A
•/C（a）g
1・・
A—C不冗余
判断C—A是不冗余。
设：
G2={
A—C，B—A，
B—C，D—A，
D—C，
BD—A}，
得：
（C）G2=C
A（C）gi/.C—A不冗余
判断B-A是否冗余。
设：
G3={A—C,C—A,B—C,D—A,D—C,BD^A},得：
（B）g3=bca
A（B）g3/.B—A冗余
判断B—C是否冗余。
设：
G4{A—C,C—A,D—AD—C,BD^A},得：
（B）G4=B
C（B）g4二B—C不冗余
判断D—A是否冗余
设：
G5={A^C,C—A,BfC,“C,BDHA},得：
（D）g5二DCA
•••A（D）g5DfA不冗余
判断DfC是否冗余
C（D）g6DfC不冗余
判断BDfA是否冗余。
•••A（BD）g7BD^A冗余
F={AfC,CfA,BfC,DfC}
③由于各函数依赖在部都为单属性．故：
Fm={AfC,CfA,BfC,DfC}。
3）T={AC,BC,DC,BD}
2．答：
.AB为码
1）v（AB）f二ABECDFABCDEF（AB）f
•••（AC）f
=ABECDF
ABCDEF
（AC）F
•••AC为码
•••（AD）f
=ABECDF
ABCDEF
（AD）F
•••AD为码
2）vBfC
部分
•••AB
C
BfC
传递
C
•/AD^B,
AD
部分
•/CfD
•••AC
C
•••BfC,
CfD
传递
C
AB传递
3．答：
①将F中的函数依赖都分解为右部为单属性的函数依赖。
F={ALE,BSD,BiC,CD^B,A,F,dB,CiD,SA,
DfE,D^F}
②去掉F中冗余的函数依赖。
判断A4E是否冗余。
设：
G1={BCfD,BE^C,CD^B,C「A,CE^F,CFfB,CFfD,CfA,
fE，DfF}
得：
（AB）G1=AB
•••E（A）giA4E不冗余
判断BC－D是否冗余。
设：
G2={ABtE,BiC,CXB,A,dF,CiB,CiD,A,
Dte,DTF}
得：
（BC）G2=BCAEFD
d€（Bc）g2BCTD冗余
判断BiC是否冗余。
设：
G3={ABTE,CDTB,CETA,CETF,CFTB,CFTD,CTA,DTE,DTF}
得：
（BE）G3=BE
•/C（BE）G3.betc不冗余
判断CD-B是否冗余。
设：
G4={ABTE,BETC,CETA,CETF,CFTB,CFTD,CTA,DTE,DTF}
得：
（CD）G4=CDAEFB
Z3（do）9•'•
d3QdO=-（d0）:
割
'OT日3TH}=Z9:
祺
°V3O=90（39）:
割
。
爭卫昱者TTO蛹赚
vaad3O=^（3g）:
割
{d^aUnWo'Geo'日to'tto'ot日（3^av}=§o:
祺
°设：
G8二{AB—E,BiC,dF,CiB,A,XE,F}
得：
（CF）G8=CFABE
•••D（CF人8cf—D不冗余
判断C—A是否冗余。
设：
G9={AB—E,BE—C,CE—F,CF—B,CF—D,D—E,D—F}
得：
（C）G9=C
TA（C）g9C—A不冗余
判断D－E是否冗余。
设：
G10={AB—E,BE—C,CE—F,CF—B,CF—D,C—A,D—F}
得：
（D）G10=DF
•••E（D）giod—E不冗余
判断D－F是否冗余。
设：
G11={AB—E,
BE—C,CE—F,CF—B,CF—D,C—A,D—E}
•••F={ABtE,BLC,dF,CiB,CiD,SA,E,D^F}
求得．FF=F
DFf不能以Ftd代替CFtD
在决定因素中去掉F。
求得：
CF=CA
DCp不能以CtD代替CFtD
不能以CFtd不冗余
F={ABtE,BEtC,CEtF,CFtB,CFtD,CtA,DtE,DtF}
4答：
1）是bcnf二元关系中或为全码，或为一个单属性码候选码。
2）是BCNF关系模式中只有一个候选码。
3）不是BCNF因为模式中存在候选码为ADBCD和B吕显然C对AD是部分依赖。
5答：
1－U2=AB
U1U2=E
UiL2tUi-U2={EtAB}二{EfA,iB}
UiUtUi-U2F
该分解具备无损连接。
6答：
l）R的主码为IBO。
2）F={StD,IfS,1fQBtQ}
令P=boisqd
1由于R的码为IBO.选择StD分解。
得出：
={S1,S2}
其中S1=SD,F1={StD}；
S2=BOISQ,F2={ItS,ItQ,BtQ}。
显然S2不服从BCNF需要继续分解：
2对Si分解，S2的码为IBO,选择Its分解
其中：
IS.F3={ITS}
S4=BOIQF4={ITQ,Q
显然.S不服从BCNF还需要继续分解。
3对S4分解。
S4的码为IBO,选择ItQ分解。
得出：
={S1,S3,S5,S6}。
其中S5=IQ,F5={ITQ};
S6=BIO,F6=。
4最后的分解为：
={SD,IS．IQ．BIO}。
7答：
正确。
因为学号能够多值决定课程号。
8答：
BCNF由于A多值依赖于动而C不是码.故不服从4NF。
但在函数依赖式中C
依赖于码AB.故该模式服从BCNF
9答：
l）候选码为AC,BC.AD,BD可选其中之一为主码。
2）不服从4NF。
在多值依赖中泱定因素中不包含码。
3）不服从BCNF在函数依赖中决定因素中不包含码。
4）服从3NF。
该模式中不存在非主属性。
习题四
1．今有两个关系模式：
职工（职工号，姓名，年龄，职务，工资，部门号）；部门（部门号，名称，经理名，地址，电话）。
请田SQL的GRAN和REVOKE句（加上视图机制），完成以下授权定义或存取
升级会员 