数据库综合练习.docx
《数据库综合练习.docx》由会员分享，可在线阅读，更多相关《数据库综合练习.docx（26页珍藏版）》请在冰豆网上搜索。
数据库综合练习
数据库综合练习三
习题一
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.一个m:
n联系转换为一个关系模式
习题一解答
1．答：
转换后的关系模型如图6所示。
出版社（出版社名，地址，邮政编码）；
作者（姓名，性别，年龄，证件号码，单位）；
出版（出版社名，作者姓名，出书数量，联系方式）。
图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）。
4．答：
转换后的关系模型如下
工厂（厂名，厂长，地址）；
产品（编号，型号，单价）；
用户（姓名，地址，电话）；
工厂-产品-用户（厂名，编号，姓名）。
5．答：
概念模型如图8所示。
关系模型为：
职工（职工号，姓名，住址，工作商品部）；
商品部（商品部号，名称，经理职工号，经理名）；
商品（商品代号，价格，型号，出厂价格）；
生产厂家（厂名，地址）；
销售（商品代号，商品部号）；
生产（厂名，商品代号）。
图8百货商店E-R图模型
图中：
职工：
职工号，姓名，住址；
商品部：
商品部号，名称；
商品：
商品代号，价格，型号，出厂价格；
生产厂家：
厂名，地址。
6．答：
D
习题二
1.设职工社团数据库有三个基本表:
职工（职工号，姓名，年龄，性别）；
社会团体（编号，名称，负责人，活动地点）；
参加（职工号，编号，参加日期）。
其中：
1）职工表的主码为职工号。
2）社会团体表的主码为编号；外码为负责人，被参照表为职工表，对应属性为职工号。
3）参加表的职工号和编号为主码；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。
试用SQL语句表达下列操作：
l）定义职工表、社会团体表和参加表，并说明其主码和参照关系。
2）建立下列两个视图。
社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；
参加人情况（职工号，姓名，社团编号，社团名称，参加日期）
3）查找参加唱歌队或篮球队的职工号和姓名。
4）查找没有参加任何社会团体的职工情况。
5）查找参加了全部社会团体的职工情况。
6）查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。
7）求每个社会团体的参加人数。
8）求参加人数最多的社会团体的名称和参加人数。
9）求参加人数超过100人的社会团体的名称和负责人。
10）把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。
2.SQL语言集数据查询、数据操作、数据定义和数据控制功能于一体，语句INSERT、DELETE、UPDATA实现下列哪类功能________。
A.数据查询B.数据操纵
C.数据定义D.数据控制
3.下面列出的关于视图（View）的条目中，不正确的是________。
A.视图是外模式
B.视图是虚表
C.使用视图可以加快查询语句的执行速度
D.使用视图可以简化查询语句的编写
4.在SQL语言的SELECT语句中，能实现投影操作的是________。
A.SELECTB.FROM
C.WHERED.GROUPBY
5.SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，语句ALTERTABLE实现哪类功能_______。
A.数据查询B.数据操纵
C.数据定义D.数据控制
6.在关系数据库系统中，为了简化用户的查询操作，而又不增加数据的存储空间，常用的方法是创建_______。
A.另一个表B.游标
C.视图D.索引
7.设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为：
学生（学号，姓名，性别，年龄，所在系）；
课程（课程号，课程名，先行课），
选课（学号，课程号，成绩）。
用Transact-SQL完成下列操作。
l）建立学生选课库。
2）建立学生、课程和选课表。
3）建立各表以主码为索引项的索引。
4）建立学生性别只能为“男”或“女”的规则，性别为“男”的缺省，并将它们绑定在学生表上。
5）建立在对选课表输入或更改数据时，必须服从参照完整性约束的INSERT和UPDATE触发器。
6）建立在删除学生记录时，同时也要把相应的选课记录删除的触发器。
7）查询各系及学生数，最后来出共有多少系和多少学生。
8）将学生表和选课表进行内连接、左外连接和右外连接。
9）列出学生学号、姓名及学习情况。
学习情况用好、较好、一般或较差表示。
当平均成绩大于85分时，学习情况为好；当平均成绩在70～85分之间，学习情况为较好；当平均成绩在60～70分之间，学习成绩为一般；平均成绩在60分以下的为学习情况较差。
习题二解答
1．答：
1）CREATTABLE职工（职工号CHAR（l0）NOTNULLUNIQUE，
姓名CHAR（8）NOTNULL，
年龄SMALLINT，
性别CHAR
（2）,
CONSTRAINTC1CHECK（性别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职工，社会团体，参加
WHERE职工．职工号=参加．职工号AND参加．编号=社会团体．编号；
3）SELECT职工号，姓名
FROM职工，社会团体，参加
WHERE职工．职工号=参加．职工号AND参加．编号=社会团体．编号
AND社会团体．名称IN（‘唱歌队’，‘篮球队’）
4）SELECT*
FROM职工
WHERENOTEXISTS（SELECT*
FROM参加
WHERE参加．职工号=职工．职工号）；
5）SELECT*
FROM职工
WHERENOTEXISTS（SELECT*
FROM参加
WHERENOTEXISTS
（SELECT*
FROM社会团体
WHERE参加．职工号=职工．职工号AND
参加．编号=社会团体．编号））；
6）SELECT职工号
FROM职工
WHERENOTEXISTS（SELECT*
FROM参加参加1
WHERE参加1．职工号=‘1001’ANDNOTEXISTS
（SELECT*
FROM参加参加2
WHERE参加2．编号=参加1．编号AND
参加2．职工号一职工．职工号））；
7）SELECTTCOUNT（职工号）
FROM参加
GROUPBY编号；
2．答：
B
3．答：
C
4．答：
A
5．答：
C
6．答：
C
7．答：
l）CREATEDATABASE学生选课库
ONPRIMARY（NANE=学生选课库，
FILENAME＝‘C:
＼msSQL\data\学生选课．mdf’，
SIZE=4MB，
MAXSIZE＝6MB，
FILEROWHT=2MB）
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，
CONSTRAINTC1PRIMARYKEY（学号，课程号）,
CONSTRAINTC2FOREIGNKEY（学号）REFRENCES学生（学号）,
CONSTRAINTFOREIGNKEY（课程号）REFERENCES课程（课程号））
GO
3）CREATEINDEXstudent_indON学生（学号）
GO
REATEI＊＊巨class_indON课程（课程号）
GO
CREATEINDEXselect_indON选课（学号，课程号）
GO
4）CREATRULEvalue_ruleAS＠VALUEIN（‘男’，‘女’）
GO
CREATDEFAULE性别缺省AS‘男’
GO
EXECsp_bindrule‘value_rule’，‘学生．性别’
GO
EXECSp_binddefault‘性别缺省’，‘学生．性别’
GO
5）CREATETRIGGERsc_insertON选课
FORINSERT
ASIF（SELECTCOLJNT（*）
FROM学生，inserted，课程
WHERE学生．学号=inserted．学号AND课程．课程号=inserted．课程号）=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（成绩）＜60THEN‘较差’
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，函数依赖集F＝｛AB→E，AC→F，AD→B，B→C，C→D｝。
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，CDEF｝。
判断p是否是无损连接。
6.设关系模式R{B，O，I，S，Q．D｝，函数依赖集F={S→D，I→S，IS→Q，B→Q}。
l）找出R的主码。
2）把R分解为BCNF，且具有无损连接性。
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，BC→D，
AD→C，BD→C，A→→CD，B→→CD}。
1）求R的主码。
2）R是否为第4范式为什么
3）R是否是BCNF为什么
4）R是否是3NF为什么
习题三解答
1．答：
1）R的候选码为BD。
2）①将F中的函数依赖都分解为右部为单属性的函数依赖。
F＝｛A→C，C→A，B→A，B→C，D→A，D→C，BD→A｝
②去掉F中冗余的函数依赖。
判断A→C是否冗余。
设：
G1＝｛C→A，B→A，B→C，D→A，D→C，BD→A｝，得：
=A
∵C
∴A→C不冗余
判断C→A是不冗余。
设：
G2＝｛A→C，B→A，B→C，D→A，D→C，BD→A｝，得：
=C
∵A
∴C→A不冗余
判断B→A是否冗余。
设：
G3＝｛A→C，C→A，B→C，D→A，D→C，BD→A｝，得：
=BCA
∵A
∴B→A冗余
判断B→C是否冗余。
设：
G4＝｛A→C，C→A，D→A，D→C，BD→A｝，得：
=B
∵C
∴B→C不冗余
判断D→A是否冗余。
设：
G5＝｛A→C，C→A，B→C，D→C，BD→A｝，得：
=DCA
∵A
∴D→A不冗余
判断D→C是否冗余。
设：
G6＝｛A→C，C→A，B→C，BD→A｝，得：
=D
∵C
∴D→C不冗余
判断BD→A是否冗余。
设：
G7＝｛A→C，C→A，B→C，D→C｝，得：
=BDCA
∵A
∴BD→A冗余
F=｛A→C，C→A，B→C，D→C｝
③由于各函数依赖在部都为单属性．故：
Fm=｛A→C，C→A，B→C，D→C｝。
3）T＝｛AC，BC，DC，BD｝
2．答：
1）∵
=ABECDFABCDEF∈
∴AB为码
∵
=ABECDFABCDEF∈
∴AC为码
∵
=ABECDFABCDEF∈
∴AD为码
2）∵B→C∴AB
C
∵AD→B，B→C∴AD
C
∵C→D∴AC
C
∵B→C，C→D∴AB
C
3．答：
①将F中的函数依赖都分解为右部为单属性的函数依赖。
F＝｛AB→E，BC→D，BE→C，CD→B，CE→A，CE→F，CF→B，CF→D，C→A，
D→E，D→F｝
②去掉F中冗余的函数依赖。
判断AB→E是否冗余。
设：
G1＝｛BC→D，BE→C，CD→B，CE→A，CE→F，CF→B，CF→D，C→A，
D→E，D→F｝
得：
=AB
∵E
∴AB→E不冗余
判断BC－D是否冗余。
设：
G2=｛AB→E，BE→C，CD→B，CE→A，CE→F，CF→B，CF→D，C→A，
D→E，D→F｝
得：
=BCAEFD
∵D∈
∴BC→D冗余
判断BE→C是否冗余。
设：
G3=｛AB→E，CD→B,CE→A，CE→F，CF→B，CF→D，C→A，D→E，D→F｝
得：
=BE
∵C
∴BE→C不冗余
判断CD－B是否冗余。
设：
G4=｛AB→E,BE→C,CE→A,CE→F，CF→B，CF→D，C→A，D→E，D→F｝
得：
=CDAEFB
∵B∈
∴CD→B冗余
判断CE－A是否冗余。
设：
G5=｛AB→E，BE→C，CE→F，CF→B，CF→D，C→A，D→E，D→F｝
得：
=CEFBDA
∵A∈
∴CE→A冗余
判断CE→F是否冗余。
设：
G6=｛AB→E，BE→C，CF→B，CF→D，C→A，D→E，D→F｝
得：
=CEA
∵F
∴CE→F不冗余
判断CF－B是否冗余。
设：
G7=｛AB→E，BE→C，CE→F，CF→D，C→A，D→E，D→F｝
得：
=CFDEF
∵B
∴CF→B不冗余
判断CF→D是否冗余。
设：
G8=｛AB→E，BE→C，CE→F，CF→B，C→A，D→E，D→F｝
得：
=CFABE
∵D
∴CF→D不冗余
判断C→A是否冗余。
设：
G9=｛AB→E，BE→C，CE→F，CF→B，CF→D，D→E，D→F｝
得：
=C
∵A
∴C→A不冗余
判断D－E是否冗余。
设：
G10=｛AB→E，BE→C，CE→F，CF→B，CF→D，C→A，D→F｝
得：
=DF
∵E
∴D→E不冗余
判断D－F是否冗余。
设：
G11=｛AB→E，BE→C，CE→F，CF→B，CF→D，C→A，D→E｝
得：
=DE
∵E
∴D→F不冗余
∴F=｛AB→E，BE→C，CE→F，CF→B，CF→D，C→A，D→E，D→F｝
求得．FF=F
D
FF
不能以F→D代替CF→D
在决定因素中去掉F。
求得：
CF=CA
D
Cp
不能以C→D代替CF→D
不能以CF→D不冗余
F=｛AB→E，BE→C，CE→F，CF→B，CF→D，C→A，D→E，D→F｝
4答：
l）是BCNF。
二元关系中或为全码，或为一个单属性码候选码。
2）是BCNF。
关系模式中只有一个候选码。
3）不是BCNF、因为模式中存在候选码为AD、BCD和BE。
显然C对AD是部分依赖。
5答：
U1
U2＝EU1－U2=AB
U1
U2→U1－U2={E→AB}={E→A，E→B}
U1
U2→U1－U2
F
该分解具备无损连接。
6答：
l）R的主码为IBO。
2）F＝｛S→D，I→S，1→Q．B→Q｝
令P＝BOISQD
①由于R的码为IBO．选择S→D分解。
得出：
={S1，S2}
其中S1=SD，F1={S→D}；
S2＝BOISQ，F2=｛I→S，I→Q，B→Q｝。
显然S2不服从BCNF，需要继续分解：
②对S1分解，S2的码为IBO，选择I→S分解。
得出：
={S1，S2，S3}
其中：
S3＝IS．F3＝｛I→S｝
S4＝BOIQ．F4=｛I→Q，B→Q｝
显然．S4不服从BCNF，还需要继续分解。
③对S4分解。
S4的码为IBO，选择I→Q分解。
得出：
={S1，S3，S5,S6}。
其中S5＝IQ，F5＝｛I→Q｝;
S6＝BIO，F6＝
。
④最后的分解为：
={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的GRANT和REVOKE语句（加上视图机制），完成以下授权定义或存取控制功能。
1）用户王明对两个表有SELECT权力。
2）用户李勇对两个表有INSERT和DELETE权力。
3）用户刘星对职工表有SELECT权利，对工资字段具有更新权力。
4）用户张新具有修改这两个表的结构的权力。
5）用户周平具有对两个表的所有权力（读、插、改、删数据），并具有给其他用户授权的权利。
6）用户杨兰具有从每个部门职工中SELECT最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。
2．假设有下面两个关系模式：
职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；
部门（部门号，名称，
升级会员 