数据库综合练习测试3.docx
《数据库综合练习测试3.docx》由会员分享,可在线阅读,更多相关《数据库综合练习测试3.docx(22页珍藏版)》请在冰豆网上搜索。
数据库综合练习测试3
数据库综合练习三
习题一
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.假设有下面两个关系模式:
职工(职工号,姓名,年龄,职务,工资,部门号),其中职工号