数据库综合练.docx

《数据库综合练.docx》由会员分享，可在线阅读，更多相关《数据库综合练.docx（22页珍藏版）》请在三一办公上搜索。

1、数据库综合练数据库综合练习三 习题一 1.现有一个局部应用，包括两个实体：“出版社”和“作者”，这两个实体是多对多的联系，请设计适当的属性，画出E-R图，再将其转换为关系模型。 2.请设计一个图书馆数据库，此数据库中对每个借阅者保存的记录包括：读者号，姓名，地址，性别，年龄，单位。对每本书保存有：书号，书名，作者，出版社。对每本被借出的书保存有读者号、借出日期和应还日期。要求：给出该图书馆数据库的E-R图，再将其转换为关系模型。 3.图4-1是某个教务管理数据库的E-R图，请把它们转换为关系模型。 图4-1教学管理数据库 4.图4-2是一个销售业务管理的E-R图，请把它转换成关系模型。 5.设

2、有一家百货商店，已知信息有： l）每个职工的数据是职工号、姓名、地址和他所在的商品部。 2）每一商品部的数据有：它的职工，经理和它经销的商品。 3）每种经销的商品数有：商品名、生产厂家、价格、型号和内部商品代号。 4）关于每个生产厂家的数据有：厂名、地址、向商店提供的商品价格。 请设计该百货商店的概念模型，再将概念模型转换为关系模型。注意某些信息可用属性表示，其他信息可用联系表示。 6.下列有关E-R模型向关系模型转换的叙述中。不正确的是_。 A. 一个实体类型转换为一个关系模式 B. 一个1:1 联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体所对应的关系模式合并 C. 一个1:

3、n 联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体所对应的关系模式合并 D. 一个m:n 联系转换为一个关系模式 习题一 解答 1 答： 转换后的关系模型如图6所示。 出版社(出版社名，地址，邮政编码）； 作者； 出版。 图6 2答： 该图书馆数据库的ER图如图7 图7 其中： 读者：读者号，姓名，地址，性别，年龄，单位； 图书书号，书名，作者，出版社。 转换后的关系模型为： 借阅者； 书籍； 借阅。 3 答： 转换后的关系模型如下： Student； Teacher(TNO，TN，TD，TG）； Course； ST； SC； TC。 4答： 转换后的关系模型如下 工厂； 产品

4、； 用户； 工厂-产品-用户。 5答： 概念模型如图8所示。 关系模型为： 职工； 商品部； 商品； 生产厂家； 销售； 生产。 图8 百货商店E-R图模型 图中： 职工：职工号，姓名，住址； 商品部：商品部号，名称； 商品：商品代号，价格，型号，出厂价格； 生产厂家：厂名，地址。 6答：D 习题二 1. 设职工社团数据库有三个基本表: 职工； 社会团体； 参加。 其中： 1）职工表的主码为职工号。 2）社会团体表的主码为编号；外码为负责人，被参照表为职工表，对应属性为职工号。 3）参加表的职工号和编号为主码；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体

5、表，对应属性为编号。 试用SQL语句表达下列操作： l）定义职工表、社会团体表和参加表，并说明其主码和参照关系。 2）建立下列两个视图。 社团负责人； 参加人情况 3）查找参加唱歌队或篮球队的职工号和姓名。 4）查找没有参加任何社会团体的职工情况。 5）查找参加了全部社会团体的职工情况。 6）查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。 7）求每个社会团体的参加人数。 8）求参加人数最多的社会团体的名称和参加人数。 9）求参加人数超过100人的社会团体的名称和负责人。 10）把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用

6、户。 2. SQL 语言集数据查询、数据操作、数据定义和数据控制功能于一体，语句INSERT、DELETE、UPDATA实现下列哪类功能_。 A. 数据查询 B. 数据操纵 C. 数据定义 D. 数据控制 3. 下面列出的关于视图的条目中，不正确的是_。 A. 视图是外模式 B. 视图是虚表 C. 使用视图可以加快查询语句的执行速度 D. 使用视图可以简化查询语句的编写 4. 在SQL语言的SELECT语句中，能实现投影操作的是_。 A. SELECT B. FROM C. WHERE D. GROUP BY 5. SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，语句ALTER

7、TABLE实现哪类功能_。 A. 数据查询 B. 数据操纵 C. 数据定义 D. 数据控制 6. 在关系数据库系统中，为了简化用户的查询操作，而又不增加数据的存储空间，常用的方法是创建_。 A. 另一个表 B. 游标 C. 视图 D. 索引 7. 设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为： 学生； 课程， 选课。 用 Transact-SQL完成下列操作。 l）建立学生选课库。 2）建立学生、课程和选课表。 3）建立各表以主码为索引项的索引。 4）建立学生性别只能为“男”或“女”的规则，性别为“男”的缺省，并将它们绑定在学生表上。 5）建立在对选课表输入或更改数据时，

8、必须服从参照完整性约束的INSERT和UPDATE触发器。 6）建立在删除学生记录时，同时也要把相应的选课记录删除的触发器。 7）查询各系及学生数，最后来出共有多少系和多少学生。 8）将学生表和选课表进行内连接、左外连接和右外连接。 9）列出学生学号、姓名及学习情况。学习情况用好、较好、一般或较差表示。当平均成绩大于85分时，学习情况为好；当平均成绩在7085分之间，学习情况为较好；当平均成绩在6070分之间，学习成绩为一般；平均成绩在60分以下的为学习情况较差。 习题二解答 1答： 1）CREAT TABLE职工NOT NULL UNIQUE， 姓名CHARNOT NULL， 年龄SMALL

9、INT， 性别CHAR, CONSTRAINT C1 CHECK）； CREAT TABLE社会团体NOT NULL UNIQUE， 名称CHARNOT NULL， 负责人 CHAR， 活动地点 VARCHAR， CONSTRAINT C2 FOREIGN KEYREFERENCES职工）； CREAT TABLE参加， 编号CHAR， 参加日期 DATE CONSTRAINT C3 PRIMARY KEY， CONSTRAIN C3 FOREIGN KEY (职工号）REFERENCES职工）； 2）CREAT VIEW社团负责人 AS SELECT编号，名称，负责人，姓名，性别 FROM社

10、会团体，职工 WHERE社会团体，负责人=职工职工号； CREAT VIEW参加人情况 AS SELECT参加职工号，姓名，社会团体编号，名称，参加日期 FROM职工，社会团体，参加 WHERE职工职工号=参加职工号AND参加编号=社会团体编号； 3）SELECT职工号，姓名 FROM 职工，社会团体，参加 WHERE职工职工号=参加职工号AND参加编号=社会团体编号 AND社会团体名称IN 4）SELECT* FROM 职工 WHERE NOT EXISTS； 5）SELECT* FROM职工 WHERE NOT EXISTS）； 6）SELECT职工号 FROM职工 WHERE NOT E

11、XISTS）； 7）SELECT TCOUNT FROM 参加 GROUP BY编号； 2答：B 3答：C 4答：A 5答：C 6答：C 7答： l）CREATE DATABASE学生选课库 ON PRIMARY GO 2）CREAT TABLE学生PRIMARY KEY CLUSTERED， 姓名CHAR， 性别 CHAR， 年龄SMALLINT 所在系VARCHAR） GO CREAT TABLE课程PRIMARY KEY CLUSTERED， 课程名VARCHAR， 先行课VARCHAR） GO CREATE TABLE选课， 课程号VARCHAR， 成绩SMALLINT， CONSTR

12、AINT C1 PRIMARY KEY, CONSTRAINT C2 FOREIGN KEYREFRENCES学生, CONSTRAINT FOREIGN KEYREFERENCES课程） GO 3）CREATE INDEX student_ind ON学生 GO REATEI巨class_ind ON课程 GO CREATE INDEX select_ind ON选课 GO 4）CREAT RULE value_rule AS VALUE IN GO CREAT DEFAULE性别缺省AS男 GO EXEC sp_bindrule value_rule，学生性别 GO EXEC Sp_bin

13、ddefault 性别缺省，学生性别 GO 5）CREATE TRIGGER sc_insert ON选课 FOR INSERT AS IF FROM 学生，inserted，课程 WHERE学生学号=inserted学号AND课程课程号=inserted课程号)=0 ROLLBACK TRANSACTION GO CREATE TRIGGER sc_updat6 ON选课 FOR UPDATE AS IF FROM 学生，updated，课程 WHERE学生学号=updated学号 AND课程课程号=updated课程号)=0 ROLLBACK TRANSACTION GO 6）CREATE

14、 TRIGGER delete_all ON 学生 FOR DELETE AS DELETE FROM选课 WHERE选课学号=deleted学号 GO 7）SELECT所在系，COUNT(学号） FROM学生 GROUP BY学生所在系 COMPUTE COUNT） COMPUTE COUNT学号）BY所在系 GO 8）SELECT* FROM学生INNER JOIN课程 GO SELECT* FROM学生LEFT OUTER JOIN课程 GO SELECT* FROM学生RIGHT OUTER JOIN课程 GO 9）SELECT选课学号，学生姓名， 学习情况=CASE WHEN AVG

15、(成绩）85 THEN好 WHEN AVG(成绩）=75 AND AVG(成绩) 85 THEN较好 WHEN AVG(成绩）60 THEN较差 END FROM学生选课 WHERE学生学号=选课学号 GROUP BY选课学号 GO 习题三 1. 设关系模式R，函数依赖集FAC，CA， BAC，DAC，BDA。 1）求出R的候选码。 2）求出F的最小函数依赖集。 3）将R分解为3NF，使其既具有无损连接性又具有函数依赖保持性。 2.设关系模式R，函数依赖集FABE，ACF，ADB，BC，CD。 1）证明AB、AC、AD均是候选关键宇。 2）证明主属性C部分依赖于关键字AB，传递依赖于AD。同时

16、证明主属性D部分依赖于关键字AC，传递依赖于关键字AB。 3. 设关系模式R，函数依赖集FABE，BCD，BEC，CDB，CEAF，CFBD，CA，DEF，求F的最小函数依赖集。 4.判断下面的关系模式是不是BCNF，为什么？ 1）任何一个二元关系。 2）关系模式选课，函数依赖集F成绩。 3）关系模式R，函数依赖集FABC，BCA，BCDEF，EC。 5. 设关系模式R，函数依赖集F=AB，CF，EA，CEA，将R分解为PABE，CDEF。判断p是否是无损连接。 6. 设关系模式RB，O，I，S，QD，函数依赖集F=SD，IS，ISQ，BQ。 l）找出R的主码。 2）把R分解为BCNF，且具有

17、无损连接性。 7. 在关系模式选课中，“学号课程号”正确吗？为什么？ 8. 设有关系模式R，数据依赖集F=ABC，CA，R属于第几范式？为什么？ 9. 设有关系模式R，数据依赖集F=AB，BA，ACD，BCD， ADC，BDC，ACD，BCD。 1）求R的主码。 2）R是否为第4范式？为什么？ 3）R是否是BCNF？为什么？ 4）R是否是3NF？为什么？ 习题三解答 1答： 1）R的候选码为BD。 2）将F中的函数依赖都分解为右部为单属性的函数依赖。 FAC，CA， BA，BC，DA，DC，BDA 去掉F中冗余的函数依赖。 判断AC是否冗余。 设：G1CA，BA，BC，DA，DC，BDA，得：

18、(A)=A C(A) AC不冗余 G1+G1+判断CA是不冗余。 设：G2AC，BA，BC，DA，DC，BDA，得： (C)=C A(C) CA不冗余 G2+G1+判断BA是否冗余。 设：G3AC，CA，BC，DA，DC，BDA，得：(B)=BCA A(B) BA冗余 判断BC是否冗余。 设：G4AC，CA， DA，DC，BDA， 得：(B)=B G3+G3+G4C(B)G4 BC不冗余 判断DA是否冗余。 设：G5AC，CA， BC，DC，BDA， 得：(D)G5=DCA A(D)G5 DA不冗余 判断DC是否冗余。 设：G6AC，CA， BC，BDA， 得：(D)C(D)+G6+=D DC

19、不冗余 判断BDA是否冗余。 G6设：G7AC，CA， BC，DC， 得：(BD) BDA冗余 F=AC，CA，BC，DC 由于各函数依赖在部都为单属性故： Fm=AC，CA，BC，DC。 3）TAC，BC，DC，BD 2 答： G7+G7=BDCA A(BD)+1）(AB)+F= ABECDF ABCDEF(AB)+F AB为码 (AC)F= ABECDF ABCDEF(AC)F AC为码 (AD)F= ABECDF ABCDEF(AD)F AD为码 +C 2） BC AB部分C ADB，BC ADC CD AC部分传递C BC，CD AB3答： 将F中的函数依赖都分解为右部为单属性的函数依

20、赖。 FABE，BCD，BEC，CDB，CEA，CEF，CFB，CFD，CA， DE，DF 去掉F中冗余的函数依赖。 判断ABE是否冗余。 设：G1 BCD，BEC，CDB，CEA，CEF，CFB，CFD，CA， DE，DF 得：(AB)+G1传递=AB E(A) +G1 ABE不冗余 判断BCD是否冗余。 设：G2= ABE，BEC，CDB ，CEA，CEF，CFB，CFD，CA， DE，DF 得：(BC)-G2=BCAEFD + D(BC)G2 BCD冗余 判断BEC是否冗余。 设：G3= ABE，CDB,CEA ，CEF，CFB，CFD，CA，DE，DF 得：(BE)+G3=BE +G3

21、 BEC不冗余 判断CDB是否冗余。 设：G4= ABE,BEC,CEA ,CEF，CFB，CFD，CA，DE，DF 得：(CD)+G4 C(BE)=CDAEFB +G4 B(CD) CDB冗余 判断CEA是否冗余。 设：G5= ABE，BEC，CEF，CFB，CFD，CA，DE，DF 得：(CE)G5=CEFBDA A(CE)+ CEA冗余 判断CEF是否冗余。 设：G6= ABE，BEC，CFB，CFD，CA，DE，DF G5得：(CE)=CEA G6+ CEF不冗余 判断CFB是否冗余。 设：G7= ABE，BEC，CEF，CFD，CA，DE，DF G6 F(CE)+得：(CF)=CFD

22、EF G7+ CFB不冗余 判断CFD是否冗余。 设：G8= ABE，BEC，CEF，CFB，CA，DE，DF G7 B(CF)+得：(CF)+G8=CFABE + D(CF)G8 CFD不冗余 判断CA是否冗余。 设：G9= ABE，BEC，CEF，CFB，CFD，DE，DF 得：(C)G9=C A(C)G9 CA不冗余 判断DE是否冗余。 设：G10= ABE，BEC，CEF，CFB，CFD，CA，DF +得：(D)+G10=DF +G10 DE不冗余 判断DF是否冗余。 设：G11= ABE，BEC，CEF，CFB，CFD，CA，DE 得：(D)+G11 E(D)=DE +G11 DF不

23、冗余 F= ABE，BEC，CEF，CFB，CFD，CA，DE，DF 求得FF=F QDFF 不能以FD代替CFD 在决定因素中去掉F。 求得：CF=CA QDCp 不能以CD代替CFD 不能以CFD不冗余 F= ABE，BEC，CEF，CFB，CFD，CA，DE，DF 4 答： l）是BCNF。二元关系中或为全码，或为一个单属性码候选码。 2）是BCNF。关系模式中只有一个候选码。 3）不是BCNF、因为模式中存在候选码为AD、BCD和BE。显然C对AD是部分依赖。 5 答： E(D)QU1IU2E U1U2=AB U1IU2U1U2=EAB=EA，EB U1IU2U1U2F 该分解具备无损

24、连接。 6 答： l）R的主码为IBO。 2）FSD，IS，1QBQ 令PBOISQD 由于R的码为IBO选择SD分解。 得出：r=S1，S2 其中 S1=SD，F1=SD； S2BOISQ，F2=IS，IQ，BQ。 显然S2不服从BCNF，需要继续分解： 对S1分解，S2的码为IBO，选择IS分解。 得出：r=S1，S2，S3 其中：S3ISF3IS S4BOIQF4=IQ，BQ 显然S4不服从BCNF，还需要继续分解。 对S4分解。S4的码为IBO，选择IQ分解。 得出：r=S1，S3，S5,S6。 其中 S5IQ，F5IQ; S6BIO，F6。 最后的分解为：r= SD，ISIQBIO

25、。 7 答： 正确。因为学号能够多值决定课程号。 8 答： BCNF。由于A多值依赖于动 而C不是码故不服从4NF。但在函数依赖式中C依赖于码AB故该模式服从BCNF。 9 答： l）候选码为AC，BCAD，BD、可选其中之一为主码。 2）不服从4NF。在多值依赖中 泱定因素中不包含码。 3）不服从BCNF。在函数依赖中决定因素中不包含码。 4）服从3NF。该模式中不存在非主属性。 习题四 1今有两个关系模式： 职工； 部门。 请田SQL 的GRANT和REVOKE语句，完成以下授权定义或存取控制功能。 1）用户王明对两个表有SELECT权力。 2）用户李勇对两个表有INSERT和DELETE

26、权力。 3）用户刘星对职工表有SELECT权利，对工资字段具有更新权力。 4）用户张新具有修改这两个表的结构的权力。 5）用户周平具有对两个表的所有权力，并具有给其他用户授权的权利。 6）用户杨兰具有从每个部门职工中SELECT最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。 2假设有下面两个关系模式： 职工，其中职工号为主码； 部门，其中部门号为主码 用SQL语言定义这两个关系模式要求在模式中完成以下完整性约束条件的定义： 1）定义每个模式的主码。 2）定义参照完整性。 3）定义职工年龄不得超过60岁。 习题四解答 1 答： l）GRANT SELECT ON职工，部门 TO王明

27、 WITH GRANT OPTION； 2）GRANT INSERT, DELETE ON职工, 部门 TO李勇 WITH GRANT OPTION; 3） GRANT SELECT，UPDATE(工资)ON职工 TO刘星 WITH GRANT OPTION; 4） GRANT ALTE TABLE TO张新 WITH GRANT OPTION 5）GRANT ALL PRIVILIGES ON职工, 部门 TO周平 WITH GRANT OPTION 6）CREM VIEW部门工资统计 AS SELECTMAX，MIN，AVG FROM职工 GROUP BY部门号 GRANT SELECT ON部门工资统计 TO杨兰 2 答： CREATE TABLE职工PRIMARY KEY， 姓名CHARNOT NULL， 年龄SMALLINT 职务CHAR， 工资DECIMAL， 部门号CHAR。 CONSTRAINT CI CHECK CONSTRAIN C2 FOREIGN KEY REFEENCES部门）； CREAT TABLE部门PRIMARY KEY 名称CHAR 经理名 CHAR 电话 CHAR(8) CONSTRAINT C3 FOREIGN KEY REFERECES职工）；