数据库综合练习

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

2、4-1教学管理数据库4 .图4-2是一个销售业务管理的 E-R图，请把它转换成关系模型。5 .设有一家百货商店，已知信息有：l ）每个职工的数据是职工号、姓名、地址和他所在的商品部。2 ）每一商品部的数据有：它的职工，经理和它经销的商品。3 ）每种经销的商品数有：商品名、生产厂家、价格、型号（厂家定的）和内部商品代号（商店规定的）4）关于每个生产厂家的数据有：厂名、地址、向商店提供的商品价格。请设计该百货商店的概念模型，再将概念模型转换为关系模型。注意某些信息 可用属性表示，其他信息可用联系表示。6.下列有关E-R模型向关系模型转换的叙述中。不正确的是 。A. 一个实体类型转换为一个关系模式B

3、. 一个1:1联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体 所对应的关系模式合并C. 一个1:n联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体 所对应的关系模式合并D. 一个m n联系转换为一个关系模式习题一解答1 .答：转换后的关系模型如图 6所示。出版社（出版社名,地址，邮政编码）；作者（姓生性别，年龄，证件号码，单位）出版（出版社名，作者姓名，出书数量，联系方式）图62 .答：该图书馆数据库的E-R图如图7图7其中：读者：读者号，姓名,地址，性别，年龄，单位；图书.书呈书名，作者，出版社。转换后的关系模型为：借阅者（读者号，姓名,地址，性别，年龄，单位）书籍

4、（一书名，作者，出版社）；借阅（读者号，书号，借出日期,应还日期）。3答：转换后的关系模型如下：Student （SNQ SN SR SA ;Teacher(TNO, TN, TD, TG ;Course (CN。CN PCNQ;ST （SN。TN。;SC（SNQ CN。G）;TC （TN。CN。4 .答：转换后的关系模型如下工厂（匚纭厂长，地址）；产品（编出型号，单价）；用户（心地址，电话）；工厂-产品-用户（厂名，编号，姓名）,5 .答：概念模型如图8所示。关系模型为：职工（职工号、姓名、住址，工作商品部）商品部（商品部号、名称、经理职工号，经理名）商品（商品代号，价格,型号，出厂价格）生

5、产厂家（厂名，地址）销售（商品代号，商品部号）生产（匚区商品代号）。图8百货商店E-R图模型图中：职工：职工号,姓名，住址；商品部：商品部号，名称;商品：商品代号，价格,型号，出厂价格;生产厂家：厂名，地址。6 .答：D习题二1.设职工社团数据库有三个基本表：职工（职工号，姓名，年龄，性别）；社会团体（编号，名称，负责人，活动地点）参加（职工号，编号，参加日期）。其中:1 ）职工表的主码为职工号2 ）社会团体表的主码为编号；外码为负责人，被参照表为职工表，对应属性为 职工号。3 ）参加表的职工号和编号为主码；职工号为外码，其被参照表为职工表，对应 属性为职工号；编号为外码，其被参照表为社会团体

6、表，对应属性为编号。试用SQL语句表达下列操作：l ）定义职工表、社会团体表和参加表，并说明其主码和参照关系。4 ）建立下列两个视图。社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；参加人情况（职工号，姓名，社团编号，社团名称，参加日期）5 ）查找参加唱歌队或篮球队的职工号和姓名。6 ）查找没有参加任何社会团体的职工情况。7 ）查找参加了全部社会团体的职工情况。8 ）查找参加了职工号为“ 1001”的职工所参加的全部社会团体的职工号。9 ）求每个社会团体的参加人数。10 ）求参加人数最多的社会团体的名称和参加人数。11 ）求参加人数超过100人的社会团体的名称和负责人。10）把

7、对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李 平，并允许他再将此权力授予其他用户。2. SQL语言集数据查询、数据操作、数据定义和数据控制功能于一体，语句INSERTDELETE UPDAT凌现下歹U哪类功能。A.数据查询B.数据操纵C.数据定义D.数据控制3.下面列出的关于视图（View）的条目中，不正确的是 oA.视图是外模式B.视图是虚表C. 使用视图可以加快查询语句的执行速度D. 使用视图可以简化查询语句的编写4.在SQL语言的SELECTS句中，能实现投影操作的是。A. SELECTB. FROMC. WHERED. GROUP BYALTER5. SQL语言集数

8、据查询、数据操纵、数据定义和数据控制功能于一体，语句TABLE实现哪类功能A.数据查询B.数据操纵C.数据定义D.数据控制6 .在关系数据库系统中，为了简化用户的查询操作，而又不增加数据的存储空间，常用的方法是创建 0A.另一个表B.游标C. 视图D.索引7 .设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为:学生（学号，姓名，性别，年龄，所在系）；课程（课程号，课程名，先行课），选课（学号，课程号，成绩）。用Transact-SQL完成下列操作。l ）建立学生选课库。2 ）建立学生、课程和选课表。3 ）建立各表以主码为索引项的索引。4 ）建立学生性别只能为“男”或“女”的规

9、则，性别为“男”的缺省，并将它 们绑定在学生表上。5 ）建立在对选课表输入或更改数据时，必须服从参照完整性约束的INSERT和UPDAT触发器。6 ）建立在删除学生记录时，同时也要把相应的选课记录删除的触发器。7 ）查询各系及学生数，最后来出共有多少系和多少学生。8 ）将学生表和选课表进行内连接、左外连接和右外连接。9 ）列出学生学号、姓名及学习情况。学习情况用好、较好、一般或较差表示。当平均成绩大于85分时，学习情况为好；当平均成绩在7085分之间，学习情况为较好；当平均成绩在 6070分之间，学习成绩为一般；平均成绩在60分以下的为学习情况较差。习题二解答1） 答：2） CREAT TAB

10、LED工（职工号 CHAR（10 ） NOT NULL UNIQUE姓名 CHAR（8） NOT NULL年龄 SMALLINT性另U CHAR(2)CONSTRAINT C1 CHECK捌 IN （男，女）；CREAT TABLED会团体（编号 CHAR（8） NOT NULL UNIQUE名称 CHAR（12） NOT NULL负责人CHAR （8）,活动地点VARCHAR50）,CONSTRAINC2FOREIGNKEY（负责人）REFERENCES工（职工号）;CREAT TABLED （职工号 CHAR（8）,编号 CHAR（8）,参加日期DATECONSTRAINT C3 PRIM

11、ARY KEY；HX<,编号），CONSTRAIC3FOREIGNKEY（职工号）REFERENCES工（职工号）;3） CREAT VIEW社团负责人（编号，名称，负责人职工号，负责人姓名，负责 人性别）AS SELECTS号，名称，负责人，姓名，性别FROMi会团体，职工WHERE会团体，负责人=职工.职工号；CREAT VIEW1加人情况（职工号，姓名，社团编号，社团名称，参加日期）AS SELEC惨力口.职工号，姓名，社会团体编号，名称，参加日期FROM只工，社会团体，参加WHERBRX.耳R工号=参加.职工号 AND参力口.编号二社会团体.编 号；4） ) SELEC屈工号，姓

12、名FROM职工，社会团体，参加WHERE工.职工号=参加.职工号AND#力口.编号二社会团体.编号ANDtt会团体.名称IN ('唱歌队，篮球队)5） SELECT*FROMHRXWHERE NOT EXIST SSELECT*FRO修力口WHEREAT 职工号=职工.职工号)；6） SELECT*FROMR 工WHERE NOT EXIST SSELECT*FRO修力口WHERE NOT EXISTS(SELECT*FROMt会团体WHERE加.职工号=职工.职工号 AND参加.编号二社会团体.编号）；7） SELEC屈工号FROMR工WHERE NOT EXIST SSELECT*

13、FRO修加参加1WHERE力口 1.职工号=1001' AND NOT EXISTS（SELECT*FRO修加参力口 2WHER参加2.编号二参加1 .编号AND参加2.职工号一职工.职工号）;8） SELECT TCOUN碎工号）FRO唯力口GROUP B编号；2 .答：B3 .答：C4 .答：A5 .答：C6 .答：C7 .答：l ) CREATE DATABASE生选课库ON PRIMARY NANE学生选课库，FILENAME= ' C:学生选课.mdfSIZE=4MBMAXSIZ丘 6MBFILEROWHT=2M BGO2) CREAT TABLED (学号 CHAR

14、1Q PRIMARY KEY CLUSTERED姓名 CHAR(8),性另U CHAR ( 2),年龄 SMALLINT所在系 VARCHAR50)GOCREAT TABLE?® (课程号 CHAR(10) PRIMARY KEY CLUSTERED课程名 VARCHAR50),先行课 VARCHAR50）GOCREATE TABLE课（学号 CHAR （10）,课程号 VARCHAR50）,成绩 SMALLINTCONSTRAINT C1 PRIMARY KE（号，课程号）,CONSTRAINT C2 FOREIGN KE（Y号）REFRENCES生（学号）,CONSTRAINT

15、FOREIGN KEt程号）REFERENCES程（课程号）GO3) CREATE INDEX student_ind ON 学生（学号）GOREATE* 巨 class_ind ON 课程（课程号）GOCREATE INDEX select_ind ON 选课（学号，课程号）GO4) CREAT RULE value_rule AS VALUE IN ('男'，'女')GOCREAT DEFAULT另瞅省 ASM'GOEXEC sp_bindrule 'value_rule'学生.性别GOEXEC Sp_binddefault '

16、;性别缺省，学生.性另GO5) CREATE TRIGGER sc_insert ON 选课FOR INSERTAS IF ( SELECT COLJNT*)FRO游生，inserted ,课程WHERE生.学号=inserted .学号 AN醴程.课程号=inserted .课程号)=0ROLLBACK TRANSACTIONGOCREATE TRIGGER sc_updat6 O随课FOR UPDATEAS IF ( SELECT COUINT*)FRO游生，updated ,课程WHERE生.学号二updated .学号AND课程.课程号=updated .课程号)=0ROLLBACK

17、TRANSACTIONGO6) CREATE TRIGGER delete_all ON 学生FOR DELETEAS DELETEFRO随课WHER选课.学号=deleted .学号GO7) SELECTS在系，COUN悻号)FRO阵生GROUP B浮生.所在系COMPUTE COUNDISTINCT （所在系）COMPUTE COUNTS BY 所在系GO8) SELECT*FRO阵生INNER JOIN课程GOSELECT*FRO蚱生 LEFT OUTER JOINfK程GOSELECT*FRO蚱生 RIGHT OUTER JOINWGO9) SELECTS课.学号，学生.姓名,学习情况

18、=CASEWHEN AV破绩）=85 THEN 好'WHEN AV破绩）=75 AND AVG假绩） 85 THEN 较好WHEN AVG（绩）v 60 THEN 较差ENDFRO蚱生选课WHERE生.学号=选课.学号GROUP B选课.学号GO习题三1. 设关系模式 RA B, C, D函数依赖集 F= AfC, C- A, B” AC £ ACBD A o1 ）求出R的候选码。2 ）求出F的最小函数依赖集。3 ）将R分解为3NF,使其既具有无损连接性又具有函数依赖保持性2. 设关系模式 R<A B, C, D, E, F>,函数依赖集 F= ABE, ACHF

19、, ADHB, Bf C, 5 D。1 ）证明AB AC AD均是候选关键宇。2 ）证明主属性C部分依赖于关键字 AB,传递依赖于AD同时证明主属性 D部 分依赖于关键字AC,传递依赖于关键字ABo3 .设关系模式 R<A B, C, D, E, F> 函数依赖集 F= ABfE, BJD, BDC,CAB, C+AF, CFBD CA, D-EF,求F的最小函数依赖集。4 .判断下面的关系模式是不是 BCNF为什么1 ）任何一个二元关系。2 ）关系模式选课（学号，课程号，成绩），函数依赖集F= （学号，课程号） f成绩。3 ）关系模式 R （A, B, C, D, E, F）,函

20、数依赖集 F=A"BC BJA, BCI>EF,C o5. 设关系模式 R（A, B, C, D, E, F）,函数依赖集 F=AfB, C-F, A, C+A,将R分解为P= ABE CDEFo判断p是否是无损连接。6. 设关系模式 RB,O,I , S,Q.D,函数依赖集 F=SfD,I “S,IS"QB”Q。l ）找出R的主码2 )把R分解为BCNF且具有无损连接性7. 在关系模式选课(学号，课程号，成绩)中， “学号”课程号”正确吗为什么8. 设有关系模式 R (A, B, C),数据依赖集F=ABfC, C"”A, R属于第几范式为什么9. 设有关

21、系模式 R (A, B, C, D),数据依赖集 F=AfB, B"A, ACD, BCD,A£C, B£C, 2f CD Bf f CD。1 )求R的主码。2 ) R是否为第4范式为什么3 ) R是否是BCN叨什么4 ) R是否是3NF为什么习题三解答1 .答：1) R的候选码为BQ2)将F中的函数依赖都分解为右部为单属性的函数依赖。F= QC, rA, BfA, Bf C, >A, >C, B£A去掉F中冗余的函数依赖判断A-C是否冗余。设：G1= 5A, BfA, EHC, >A, >C, BCHA,得：（a）gi=AV C

22、 （ a）gi，Af C不冗余判断C-A是不冗余。设：G2= QC, BfA, BC, >A, >C, BCHA,得：（C）G2=CA （C）gi，CfA不冗余判断B-A是否冗余。设：G3= QC, CfA, BC, >A, >C, BCHA,得：（b）g3=bCAA （ B J. BA 冗余判断B-C是否冗余。设：G4 QC, CfA, DfA, rC, BCHA, 得：（b）g，=bV C（ B ）g 4.Bf C不冗余判断>A是否冗余设：G5= QC, CfA, BfC, rC, BDHA, 得：(D)g5=DCA. A( D )g5r A不冗余判断C是否冗

23、余。设：G6= Q C, r A,C ( D ) G 6判断BA A是否冗余。设：G7= Q C, r A,. A( BD )g7BC, BDA,得:C不冗余BC, rC,得: BD A冗余(BD ) G 7 =BDCAF= Af C, 5A, Bf C, r C由于各函数依赖在部都为单属性.故:Fm=A C, C” A, BfC, D- C。3) T= AC, BC, DC, BD2 .答：AB为码1); (ab)f= ABECDF ABCDEF (AB)f( AC )F = ABECDF ABCDEE (AC )f .AC为码(AD )F = ABECDF ABCDEE (AD )f .A

24、D为码部分2) v BCAB C传拂V ADB, B"C .'.AD C部分- CD.AC CV BC, SD /.AB 传递 C3 .答：将F中的函数依赖都分解为右部为单属性的函数依赖。F= ABE, B8D, BEC, CAB, CEA, CDF, CFB, CFD, CA,D E, rF去掉F中冗余的函数依赖。判断A4E是否冗余。设：G1= BCf D, BXC, CAB, CD A, CDF, CAB, CDD, SA,E, >F得：（AB）G1=AB E （A）G1，AEH E 不冗余判断BC- D是否冗余。设：G2= ABf E, BEC, CDHB , C

25、EA, CD F, CF B, CD D, 5A,Df E, >F得：（BC K=BCAEFDDC （BC）G2.-.BCD冗余判断BEC是否冗余。设：G3= ABf E, CDB,CEf A , CEF, CDB, CDD, CA, £E,F得：（BE）g3=BEV C （BE）g3/.BEC不冗余判断CD- B是否冗余。设：G4=AB7E,BEf C,C;A ,CEF, CDB, CDD, 8A, >E, 殴F得：（CD ）G4=CDAEFBBG (CD)。,判断CEE-A是否冗余。设：G5=AB7E, BbC, CbF, CFB, CDD, CA, >E,F得

26、：(CE )g5=CEFBDAAG (CE)G5，C+ A冗余判断CDF是否冗余。设：G6=ABfE, BEC, CFB, CFD, CA, >E, A F得：(CE)G6=CEA F (CE)G6.cd F 不冗余判断CF- B是否冗余。设：G7=ABfE, BEC, CEF, CFD, CA, >E, AF得：(CF)G7=CFDEF B (CF)G7，C曰B不冗余判断CD D是否冗余设：G8=AB7E, BbC, CbF, CFB,。A, >E, AF得：(CF )G8=CFABEd (CF )g8CD D不冗余判断C-A是否冗余。设：G9= ABf E, BE C,C

27、EF, CFB, CDD, >E, >F得：（C）g9=C A (C )G9Cf A不冗余判断D- E是否冗余。设：G10= AB7E,BE C, C+ F, CD B, CD D, 8A, > F得：(D )G10 =DFE (D )gi0判断D- F是否冗余。设：G11= AB7E,BE C, C+ F, CD B, CD D, 8A, > 日得：(D ) g 11 =DE，F= AB7E, BDC, CEF, CDB, CDD, CA, AE, >F求得.Ff=FD Ff不能以F"D代替CDD在决定因素中去掉Fo求得：G=CAD Cp不能以r D代

28、替CF D不能以CDD不冗余F= AB7E, BDC, CDF, CDB, CFD, 5A, >E, AF4答：l）是BCNF二元关系中或为全码，或为一个单属性码候选码。2）是BCNF关系模式中只有一个候选码。3）不是BCNF因为模式中存在候选码为 AD BCM BE显然C对AD是部分依 赖。5答:i-U2=ABU U=EU 1 Uf U U=Ef AB=Ef A,卜 BU i U2Ui-Ub F该分解具备无损连接。6答：1) R的主码为旧O。2) F= Sf D, IS, 1fQ Bf Q令 P= BOISQD由于R的码为旧O.选择S”D分解。得出： =Si, S2其中 Si=SD,

29、Fi=SD;S2=BOISQ F2= IfS, I-Q, B Q0显然S2不服从BCNF需要继续分解：对Si分解，S的码为 旧O,选择I"S分解 得出： =Si, S2, S3其中：$=IS. F3= ISS 4=BOIQ F4= I f Q, Bf Q显然.0不服从BCNF还需要继续分解。对S4分解。S4的码为 旧O,选择I" Q分解。得出：=Sl, S3, S5,S6o其中 S5=IQ, F5= I"Q；S 6 = BIO, F6 = o最后的分解为：= SD, IS. IQ. BIO o7答：正确。因为学号能够多值决定课程号。8答：BCNF由于A多值依赖于动

30、 而C不是码.故不服从 4NF。但在函数依赖式中 C 依赖于码AB.故该模式服从 BCNF9答：l ）候选码为 AC BC. AD, BD可选其中之一为主码。2 ）不服从4NF。在多值依赖中 泱定因素中不包含码。3 ）不服从BCNF在函数依赖中决定因素中不包含码。4 ）服从3NF。该模式中不存在非主属性。习题四1 .今有两个关系模式：职工（职工号，姓名，年龄，职务，工资，部门号） ；部门（部门号，名称，经理名，地址，电话） 。请田SQL的GRAN下口 REVOK踣句（加上视图机制）,完成以下授权定义或存取 控制功能。1 ）用户王明对两个表有 SELECTOR2 ）用户李勇对两个表有 INSER