数据库综合练习.docx

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.一个实体类型

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

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

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

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

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

8、P BY5.ALTERSQL 吾言集数据查询、数据操纵、数据定义和数据控制功能于一体，吾句TABLE实现哪类功能 oA. 数据查询 B. 数据操纵C. 数据定义 D. 数据控制6.在关系数据库系统中，为了简化用户的查询操作，而又不增加数据的存储空间， 常用的方法是创建 oA. 另一个表 B. 游标C. 视图 D. 索引7.设要建立学生选课数据库，库中包括学生、课程和选课 3 个表，其表结构为： 学生（学号，姓名，性别，年龄，所在系） ； 课程（课程号，课程名，先行课） ， 选课（学号，课程号，成绩） o用Transact-SQL完成下列操作。1）建立学生选课库。2）建立学生、课程和选课表。3）

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

10、0分以下的 为学习情况较差。习题二解答1答：1） CREAT TABL职工（职工号 CHAR（ 10 ） NOT NULL UNIQUJE姓名 CHAR（8） NOT NULL，年龄 SMALLINT，性别 CHAR（ 2） ,CONSTRAINT C1 CHECK性另0 IN （男，女）；CREAT TABLED会团体（编号 CHAR 8） NOT NULL UNIQUE名称 CHAR 12）NOT NUL，L负责人 CHAR（8），活动地点 VARCHAR 50），CONSTRAINC2FOREIGNKEY（负责人）REFERENC职工（职工号）;CREAT TABL参加（职工号 CHAR

11、（ 8）,编号 CHAR（8），参加日期 DATECONSTRAINT C3 PRIMARY KEYR 工号，编号）,CONSTRAIC3FOREIGNKEY（职工号）REFERENC职工（职工号）;2） CREAT VIEW社团负责人（编号，名称，负责人职工号，负责人姓名，负责 人性另）AS SELECTS号，名称，负责人，姓名，性别FROMi会团体，职工WHER社会团体，负责人二职工.职工号；CREAT VIEW参加人情况（职工号，姓名，社团编号，社团名称，参加日期）AS SELECT参加.职工号，姓名，社会团体编号，名称，参加日期FROM只工，社会团体，参加WHER职工.职工号二参加.职

12、工号 AND参加.编号二社会团体.编 号；3)SELECT职工号，姓名FROM职工，社会团体，参加WHER职工.职工号二参加.职工号AND参加.编号二社会团体.编号AND社会团体.名称IN (唱歌队，篮球队)4)SELECT*FROM职工WHERE NOT EXISTSSELECT*FROM参加WHER参加.职工号二职工.职工号)；5)SELECT*FROh职工WHERE NOT EXISTSSELECT*FROM参加WHERE NOT EXISTS(SELECT*FROMtt会团体WHER参加.职工号二职工.职工号 AND参加编号 =社会团体编号） ）；6)SELECT职工号FROMRXWH

13、ERE NOT EXIST（SSELECT*FROM力口参加1WHER参加 1.职工号二1001 AND NOT EXISTS（SELECT*FROM力口参加2WHER参力口 2 .编号二参加1 .编号AND参加 2.职工号一职工. 职工号）；7） SELECT TCOUNT职工号）FROM参 加GROUP B编号；2.答： B3.答： C4.答： A5.答： C6.答： C7.答：l) CREATE DATABASE生选课库ON PRIMARY NANE学生选课库，FILENAM些C: 学生选课.mdfSIZE=4MB，MAXSIZ& 6MBFILEROWHT=2M) BGO2)CREAT

14、TABLED生(学号 CHAR10 PRIMARY KEY CLUSTERED姓名 CHARY8)，性别 CHARY 2)，年龄 SMALLINT所在系 VARCHAYR50)GOCREAT TABLE?程(课程号 CHAR( 10) PRIMARY KEY CLUSTERED课程名 VARCHAYR50)，先行课 VARCHA（R50）GOCREATE TABL选课（学号 CHAR（ 10）,课程号 VARCHA（R50），成绩 SMALLINT，CONSTRAINT C1 PRIMARY KEY学 号，课程号）,CONSTRAINT C2 FOREIGN KEY学 号）REFRENCE学生

15、（学号），CONSTRAINT FOREIGN KEY?程号）REFERENCES程（课程号）GO3） CREATE INDEX student_ind ON 学生（学号）GOREATEI 巨 class_ind ON 课程（课程号）GOCREATE INDEX select_ind ON 选课（学号，课程号）GO4)CREAT RULE value_rule AS VALUE IN (男，女)GOCREAT DEFAUL性别缺省 AS男GOEXEC sp_bindrule value_rule ，学生性别GOEXEC Sp_binddefault 性别缺省 ， 学生性别GO5)CREATE

16、TRIGGER sc_insert ON 选课FOR INSERTAS IF(SELECT COLJN(T*)FROM学生，inserted，课程WHER学生.学号二inserted .学号 AND课程.课程号二inserted .课程号)=0ROLLBACK TRANSACTIONGOCREATE TRIGGER sc_updat6 ON选课FOR UPDATEAS IF（SELECT C OUN（T* ）FROM学生，updated，课程WHER学生.学号二updated .学号AND课程.课程号二updated .课程号）=0ROLLBACK TRANSACTIONGO6）CREATE

17、TRIGGER delete_all ON 学生FOR DELETEAS DELETEFROM选课WHER选课.学号二deleted .学号GO7)SELECT所在系，COUNT学号)乡刑韦煮合煮 L0313S(609aJI0Pd3丄no丄H9心 韦訥ONT0313S09SlNiorda丄no 丄dan 韦訥obt丄 0313S 093NI0rd3NNI 韦訥ONT0313S （8 09玄丑均人日（帥noom丄ndi/ioo（玄丑均）丄onllsicniinoo m丄ndi/ioo玄丑均-adnodo学习情况 =CASEWHEN AVG成绩）=85 THEN 好WHEN AVG（绩） =75

18、AND AVG成绩） 85 THEN 较好WHEN AVG（绩）v 60 THEN 较差ENDFROM学生选课WHER学生.学号二选课.学号GROUP B选课.学号GO习题三1.设关系模式 RA B, C, D,函数依赖集 F= AtC, A, BAC, DACBD A。1）求出 R 的候选码。2）求出 F 的最小函数依赖集。3）将R分解为3NF使其既具有无损连接性又具有函数依赖保持性2.设关系模式 R,函数依赖集 F=ASE, ASF, AXB,Bt C, C D o1）证明AB AC AD均是候选关键宇。2）证明主属性C部分依赖于关键字 AB,传递依赖于AQ同时证明主属性 D部 分依赖于关

19、键字AC,传递依赖于关键字ABo3.设关系模式 R,函数依赖集 F= ABE, BCTD, BEC,CD B, CL AF, CFt BD, Ct A, DT EF，求 F 的最小函数依赖集。4.判断下面的关系模式是不是 BCNF为什么1）任何一个二元关系。2）关系模式选课（学号，课程号，成绩），函数依赖集F= （学号，课程号）T成绩 o3）关系模式 R（A, B , C, D, E , F）,函数依赖集 F=AtBC BCTA, BCTEF,ETCo5.设关系模式 R（ A B C D E F） 函数依赖集 F=ATB CTF ETA CETA 将R分解为P= ABE CDEFo判断p是否是

20、无损连接。6.设关系模式 RB O I S QD 函数依赖集 F=STD I TS ISTQ BTQol ）找出 R 的主码2 )把R分解为BCNF且具有无损连接性7.在关系模式选课(学号,课程号,成绩)中,“学号tt课程号”正确吗为什么8.设有关系模式R(A, B, C),数据依赖集F=ABtC, Ctt A, R 属于第几范式为什么9.设有关系模式R(A, B, C, D),数据依赖集F=AtB, BtA, ACtD, BCtDADC, BDC, CD, Bn CD。1)求 R 的主码。2) R是否为第4范式为什么3)R是否是BCNF为什么4)R是否是3NF为什么习题三解答1答：1)R 的

21、候选码为 BD。2)将F中的函数依赖都分解为右部为单属性的函数依赖。F= 2 C, S A, B t a Bt C, D A, DC, BDA去掉 F 中冗余的函数依赖判断A-C是否冗余设：G1=CA，BA，B C， DA，DC，BDA，得：( A )G1 =A/ C ( a)g1 AC 不冗余判断 CA 是不冗余。设：G2=A C， B A，B C， DA，DC，BDA，得：(C )G2 =CA (C )gi /. C A不冗余判断 B-A 是否冗余。设：G3= A C, C A, B C, D A, D C, BD A,得：(B)g3=bcaA(B)g3 /. B A 冗余判断B C是否冗

22、余。设：G4 A C, C A, DA D C, BDA, 得：(B)G4=BC ( B ) g 4 二 B C不冗余判断 DA 是否冗余设：G5= A C, CA, B f C, “ C, BDH A, 得：(D)g5二DCA A ( D )g5 Df A不冗余判断Df C是否冗余C ( D ) g 6 Df C不冗余判断BDf A是否冗余。 A ( BD )g 7 BD A冗余F=AfC, CfA, BfC, DfC由于各函数依赖在部都为单属性故：Fm= Af C, Cf A, Bf C, Df C。3)T= AC, BC, DC, BD2 答：. AB 为码1)v (AB)f 二 ABE

23、CDF ABCDEF (AB)f (AC)f= ABECDFABCDEF(AC ) F AC为码 (AD)f= ABECDFABCDEF(AD )F AD为码2)v BfC部分 ABCBf C传递C/ ADB,AD部分/ Cf D ACC Bf C,Cf D传递CAB 传递3答：将 F 中的函数依赖都分解为右部为单属性的函数依赖。F= AL E, BSD, BiC, CDB, A, F, dB, CiD, SA,D f E, D F去掉 F 中冗余的函数依赖。判断A4E是否冗余。设：G1= BCf D, BE C, CD B, CA, CE F, CFf B, CFf D, Cf A,fE，D

24、fF得： (AB )G1 =AB E (A)gi A4 E 不冗余判断 BCD 是否冗余。设：G2= ABt E, BiC, CXB , A, d F, Ci B, Ci D, A,D te, DTF得： (BC )G2 =BCAEFDd (Bc )g2 BCTD冗余判断BiC是否冗余。设： G3= ABTE, CDTB,CETA , CETF, CFTB, CFTD, CTA, DT E, DTF得： (BE )G3=BE/ C (BE)G3 . betc不冗余判断CD- B是否冗余。设： G4= ABTE,BETC,CETA ,CETF, CFTB, CFTD, CTA, DTE, DTF

25、得： (CD )G4 =CDAEFBZ3( do) 9 d3QdO=-( d0):割OT日 3TH =Z9 :祺MSa-do 蛹赚V3O= 90( 39):割。爭卫昱者T TO蛹赚vaad3O=( 3g):割da Un Wo Geo 日to tto ot日(3av =o :祺MSv-3O设：G8二AB E, Bi C, d F, Ci B, A, X E, F得： (CF )G8 =CFABE D (CF 人8 cf D不冗余判断 CA 是否冗余。设：G9= ABE, BEC, CEF, CFB, CFD, DE, DF得：(C ) G 9 =CT A (C )g9 C A不冗余判断 DE 是

26、否冗余。设：G10= ABE, BEC, CEF, CFB, CFD, CA, DF得：( D ) G 10 =DF E (D)gio d E不冗余判断 DF 是否冗余。设： G11= ABE,BEC, CEF, CFB, CFD, CA, DE F= ABtE, BLC, dF, CiB, CiD, SA, E, DF求得 FF=FD Ff 不能以Ftd代替CFtD在决定因素中去掉 F。求得： CF=CAD Cp 不能以CtD代替CFt D不能以CFtd不冗余F= ABtE, BEtC, CEtF, CFtB, CFtD, CtA, DtE, DtF4答：1） 是bcnf二元关系中或为全码，

27、或为一个单属性码候选码。2） 是BCNF关系模式中只有一个候选码。3） 不是BCNF因为模式中存在候选码为 AD BCD和B吕显然C对AD是部分依 赖。5答：1U2=ABU1 U2= EU i L2tUi-U2=EtAB二Ef A, iBU i UtUi-U2 F该分解具备无损连接。6答：l) R的主码为IBO。2) F= St D, I f S, 1f Q BtQ令 P= boisqd1由于R的码为IBO.选择StD分解。得出： =S1, S2其中 S1=SD, F1=StD；S 2=BOISQ, F2=I tS, I tQ, BtQ。显然S2不服从BCNF需要继续分解：2对Si分解，S2的

28、码为IBO,选择I ts分解其中：IS . F3 = I TSS 4= BOIQ F4= I TQ, Q显然.S不服从BCNF还需要继续分解。3对S4分解。S4的码为IBO,选择I t Q分解。得出： =S1, S3, S5,S6。其中 S5=IQ, F5= ITQ ;S 6= BIO, F6= 。4最后的分解为： = SD, ISIQBIO 。7答：正确。因为学号能够多值决定课程号。8答：BCNF由于A多值依赖于动 而C不是码.故不服从 4NF。但在函数依赖式中 C依赖于码AB.故该模式服从 BCNF9答：l）候选码为 AC, BC. AD, BD可选其中之一为主码。2）不服从4NF。在多值依赖中 泱定因素中不包含码。3）不服从BCNF在函数依赖中决定因素中不包含码。4）服从3NF。该模式中不存在非主属性。习题四1今有两个关系模式：职工（职工号，姓名，年龄，职务，工资，部门号） ； 部门（部门号，名称，经理名，地址，电话） 。请田SQL的GRAN和REVOKE句（加上视图机制），完成以下授权定义或存取