数据库综合练习.docx

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

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

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

4、号，书名，作者，出版社）；借阅（读者号，书号，借出日期，应还日期）。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所示。关系模型为：职工（职工号，姓名，住址，工作商品部）；商品部（商品部号，名称，经理职工号，经理名）；商品（商品代号，价格，型号，出厂

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

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

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

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

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

10、070分之间，学习成绩为一般；平均成绩在60分以下的为学习情况较差。习题二解答1答：1）CREAT TABLE职工（职工号CHAR（l0）NOT NULL UNIQUE，姓名CHAR（8）NOT NULL，年龄SMALLINT，性别CHAR（2）,CONSTRAINT C1 CHECK（性别 IN（男，女）；CREAT TABLE社会团体（编号CHAR（8）NOT NULL UNIQUE，名称CHAR（12）NOT NULL，负责人 CHAR（8），活动地点 VARCHAR（50），CONSTRAINT C2 FOREIGN KEY（负责人）REFERENCES职工（职工号）；CREAT TA

11、BLE参加（职工号CHAR（8），编号CHAR（8），参加日期 DATECONSTRAINT C3 PRIMARY KEY（职工号，编号），CONSTRAIN C3 FOREIGN KEY (职工号）REFERENCES职工（职工号）；2）CREAT VIEW社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）AS SELECT编号，名称，负责人，姓名，性别FROM社会团体，职工WHERE社会团体，负责人=职工职工号；CREAT VIEW参加人情况（职工号，姓名，社团编号，社团名称，参加日期）AS SELECT参加职工号，姓名，社会团体编号，名称，参加日期FROM职工，社会团体，参加

12、WHERE职工职工号=参加职工号AND参加编号=社会团体编号；3）SELECT职工号，姓名FROM 职工，社会团体，参加WHERE职工职工号=参加职工号AND参加编号=社会团体编号AND社会团体名称IN（唱歌队，篮球队）4）SELECT*FROM 职工WHERE NOT EXISTS（SELECT*FROM参加WHERE参加职工号=职工职工号）；5）SELECT*FROM职工WHERE NOT EXISTS（SELECT*FROM参加WHERE NOT EXISTS（SELECT*FROM社会团体WHERE参加职工号=职工职工号AND参加编号=社会团体编号）；6）SELECT职工号FROM职工

13、WHERE NOT EXISTS（SELECT*FROM参加 参加1WHERE参加1职工号=1001AND NOT EXISTS（SELECT*FROM参加 参加 2WHERE 参加2编号=参加1编号AND参加2职工号一职工职工号）；7）SELECT TCOUNT（职工号）FROM 参加GROUP BY编号；2答：B3答：C4答：A5答：C6答：C7答：l）CREATE DATABASE学生选课库ON PRIMARY（NANE=学生选课库，FILENAMEC:msSQLdata学生选课mdf，SIZE=4MB，MAXSIZE6MB，FILEROWHT=2MB）GO2）CREAT TABLE学生

14、（学号 CHAR10）PRIMARY KEY CLUSTERED，姓名CHAR（8），性别 CHAR（2），年龄SMALLINT所在系VARCHAR（50）GOCREAT TABLE课程（课程号CHAR（10）PRIMARY KEY CLUSTERED，课程名VARCHAR（50），先行课VARCHAR（50）GOCREATE TABLE选课（学号 CHAR（10），课程号VARCHAR（50），成绩SMALLINT，CONSTRAINT C1 PRIMARY KEY（学号，课程号）,CONSTRAINT C2 FOREIGN KEY（学号）REFRENCES学生（学号）,CONSTRAINT

15、 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 DEFAULE性别缺省AS男GOEXEC sp_bindrule value_rule，学生性别GOEXEC Sp_binddefault 性别缺省，学生性别GO5）CREATE TRIGGER sc_insert ON选课F

16、OR INSERTAS IF（SELECT COLJNT（*）FROM 学生，inserted，课程WHERE学生学号=inserted学号AND课程课程号=inserted课程号)=0ROLLBACK TRANSACTIONGOCREATE TRIGGER sc_updat6 ON选课FOR UPDATEAS IF（SELECT COUNT（*）FROM 学生，updated，课程WHERE学生学号=updated学号 AND课程课程号=updated课程号)=0ROLLBACK TRANSACTIONGO6）CREATE TRIGGER delete_all ON 学生FOR DELETE

17、AS DELETEFROM选课WHERE选课学号=deleted学号GO7）SELECT所在系，COUNT(学号）FROM学生GROUP BY学生所在系COMPUTE COUNT（DISTINCT（所在系）COMPUTE COUNT学号）BY所在系GO8）SELECT*FROM学生INNER JOIN课程GOSELECT*FROM学生LEFT OUTER JOIN课程GOSELECT*FROM学生RIGHT OUTER JOIN课程GO9）SELECT选课学号，学生姓名，学习情况=CASEWHEN AVG(成绩）85 THEN好WHEN AVG(成绩）=75 AND AVG(成绩) 85 TH

18、EN较好WHEN AVG(成绩）60 THEN较差ENDFROM学生选课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。同时证明主属性D部分依赖于关键字AC，传递依赖于关键字AB。 3. 设关系模式R，函数依赖集FABE，BCD，

19、BEC，CDB，CEAF，CFBD，CA，DEF，求F的最小函数依赖集。 4.判断下面的关系模式是不是BCNF，为什么 1）任何一个二元关系。 2）关系模式选课（学号，课程号，成绩），函数依赖集F（学号，课程号）成绩。 3）关系模式R（A，B，C，D，E，F），函数依赖集FABC，BCA，BCDEF，EC。 5. 设关系模式R（A，B，C，D，E，F），函数依赖集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，且具有无损连接性。 7.

20、 在关系模式选课（学号，课程号，成绩）中，“学号课程号”正确吗为什么 8. 设有关系模式R（A，B，C），数据依赖集F=ABC，CA，R属于第几范式为什么 9. 设有关系模式R（A，B，C，D），数据依赖集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，得：=

21、A C AC不冗余判断CA是不冗余。设：G2AC，BA，BC，DA，DC，BDA，得： =CA CA不冗余判断BA是否冗余。设：G3AC，CA，BC，DA，DC，BDA，得：=BCAA BA冗余判断BC是否冗余。设：G4AC，CA， DA，DC，BDA， 得：=BC BC不冗余判断DA是否冗余。设：G5AC，CA， BC，DC，BDA， 得：=DCAA DA不冗余判断DC是否冗余。设：G6AC，CA， BC，BDA， 得：=D C DC不冗余判断BDA是否冗余。设：G7AC，CA， BC，DC， 得：=BDCAA BDA冗余F=AC，CA，BC，DC由于各函数依赖在部都为单属性故：Fm=AC，

22、CA，BC，DC。3）TAC，BC，DC，BD2 答：1）= ABECDF ABCDEF AB为码= ABECDF ABCDEF AC为码= ABECDF ABCDEF AD为码2） BC ABC ADB，BC ADC CD ACC BC，CD ABC3答：将F中的函数依赖都分解为右部为单属性的函数依赖。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 E ABE不冗余判断BCD是否冗余。设：G2= ABE，BEC，CDB

23、 ，CEA，CEF，CFB，CFD，CA， DE，DF得：=BCAEFD D BCD冗余判断BEC是否冗余。设：G3= ABE，CDB,CEA ，CEF，CFB，CFD，CA，DE，DF得：=BE C BEC不冗余判断CDB是否冗余。设：G4= ABE,BEC,CEA ,CEF，CFB，CFD，CA，DE，DF得：=CDAEFB B CDB冗余判断CEA是否冗余。设：G5= ABE，BEC，CEF，CFB，CFD，CA，DE，DF得：=CEFBDA A CEA冗余判断CEF是否冗余。设：G6= ABE，BEC，CFB，CFD，CA，DE，DF得：=CEA F CEF不冗余判断CFB是否冗余。设

24、：G7= ABE，BEC，CEF，CFD，CA，DE，DF得：=CFDEF B CFB不冗余判断CFD是否冗余。设：G8= ABE，BEC，CEF，CFB，CA，DE，DF得：=CFABE D CFD不冗余判断CA是否冗余。设：G9= ABE，BEC，CEF，CFB，CFD，DE，DF得：=C A CA不冗余判断DE是否冗余。设：G10= ABE，BEC，CEF，CFB，CFD，CA，DF得：=DF E DE不冗余判断DF是否冗余。设：G11= ABE，BEC，CEF，CFB，CFD，CA，DE得：=DE E DF不冗余F= ABE，BEC，CEF，CFB，CFD，CA，DE，DF 求得FF=

25、F DFF 不能以FD代替CFD 在决定因素中去掉F。 求得：CF=CA DCp 不能以CD代替CFD不能以CFD不冗余F= ABE，BEC，CEF，CFB，CFD，CA，DE，DF4 答：l）是BCNF。二元关系中或为全码，或为一个单属性码候选码。2）是BCNF。关系模式中只有一个候选码。3）不是BCNF、因为模式中存在候选码为AD、BCD和BE。显然C对AD是部分依赖。5 答：U1U2E U1U2=AB U1U2U1U2=EAB=EA，EB U1U2U1U2F该分解具备无损连接。6 答：l）R的主码为IBO。2）FSD，IS，1QBQ 令PBOISQD 由于R的码为IBO选择SD分解。 得

26、出：=S1，S2 其中 S1=SD，F1=SD； S2BOISQ，F2=IS，IQ，BQ。 显然S2不服从BCNF，需要继续分解： 对S1分解，S2的码为IBO，选择IS分解。 得出：=S1，S2，S3 其中：S3ISF3IS S4BOIQF4=IQ，BQ 显然S4不服从BCNF，还需要继续分解。 对S4分解。S4的码为IBO，选择IQ分解。 得出：=S1，S3，S5,S6。 其中 S5IQ，F5IQ; S6BIO，F6。 最后的分解为：= SD，ISIQBIO 。7 答：正确。因为学号能够多值决定课程号。8 答：BCNF。由于A多值依赖于动 而C不是码故不服从4NF。但在函数依赖式中C依赖于

27、码AB故该模式服从BCNF。9 答： l）候选码为AC，BCAD，BD、可选其中之一为主码。 2）不服从4NF。在多值依赖中 泱定因素中不包含码。 3）不服从BCNF。在函数依赖中决定因素中不包含码。 4）服从3NF。该模式中不存在非主属性。习题四1今有两个关系模式： 职工（职工号，姓名，年龄，职务，工资，部门号）； 部门（部门号，名称，经理名，地址，电话）。 请田SQL 的GRANT和REVOKE语句（加上视图机制），完成以下授权定义或存取控制功能。 1）用户王明对两个表有SELECT权力。 2）用户李勇对两个表有INSERT和DELETE权力。 3）用户刘星对职工表有SELECT权利，对工资字段具有更新权力。 4）用户张新具有修改这两个表的结构的权力。 5）用户周平具有对两个表的所有权力（读、插、改、删数据），并具有给其他用户授权的权利。 6）用户杨兰具有从每个部门职工中SELECT最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。2假设有下面两个关系模式： 职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码； 部门（部门号，名称，