数据库期末考试复习题及答案 -综合题.doc

《数据库期末考试复习题及答案 -综合题.doc》由会员分享，可在线阅读，更多相关《数据库期末考试复习题及答案 -综合题.doc（37页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、试题一四、设计题( 第1题4分，第2题6分，第3题3分，第4题4分， 第5题8分，共25分)1 设教学数据库中有三个基本表：学生表S（SNO，SNAME，AGE，SEX），其属性分别表示学号、学生姓名、年龄、性别。课程表C（CNO，CNAME，TEACHER），其属性分别表示课程号、课程名、上课教师名。选修表SC（SNO，CNO，GRADE），其属性分别表示学号、课程号、成绩。有如下SQL查询语句：SELECT CNOFROM C WHERE CNO NOT IN(SELECT CNO FROM S,SCWHERE S.SNO=SC.SNO AND SNAME=张三);请完成下列问题：（1）用

2、汉语句子阐述上述SQL语句的含义；（2）用等价的关系代数表达式表示上述SQL查询语句。解：（1）查询张三同学没有选修的课程的课程号。（2分）（2）CNO(C)- CNO (sSNAME=张三 (S)SC) 或CNO(C)- CNO (sSNAME=张三 (SSC) （2分）2. 设有如图3所示的三个关系。其中各个属性的含义如下：A（商店代号）、ANAME（商店名）、WQTY（店员人数）、CITY（所在城市）、B（商品号）、BNAME（商品名称）、PRICE（价格）、QTY（商品数量）。A BA#ANAMEWQTYCITY101韶山商店15长沙204前门百货商店89北京256东风商场501北京3

3、45铁道商店76长沙620第一百货公司413上海BBNAMEPRICE1毛笔212羽毛球7843收音机13254书包242ABABQTY10111051012421013251014104204361256124125629134511413452183454746204125图3试用SQL语言写出下列查询：（1）找出店员人数不超过100人或者在长沙市的所有商店的代号和商店名。（2）找出至少供应了代号为256的商店所供应的全部商品的其它商店的商店名和所在城市。解：（1）SELECT A#, ANAME FROM A WHERE WQTY=100 OR CITY=长沙; （2分）（2）SELEC

4、T ANAME,CITY FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE EXISTS(SELECT * FROM AB AB1 WHERE A#=256 AND B#=B.B#)AND NOT EXISTS(SELECT * FROM AB AB2 WHERE A#!=256 AND A#=A.A# AND B#=B.B#); （4分）3. 设有职工基本表：EMP（ENO，ENAME，AGE，SEX，SALARY），其属性分别表示职工号、姓名、年龄、性别、工资。为每个工资低于1000元的女职工加薪200元，试写出这个操作的SQL语句。UPDATE

5、 EMP SET SALARY=SALARY+200 WHERE SALARY50时，R中与相关联的属性B之值。（3分） SELECT B FROM R, S WHERE R.A=S.A AND C50（2）当属性C=40时，将R中与之相关联的属性B值修改为b4。（5分） UPDATE R SET B=b4 WHERE A IN (SELECT A FROM S WHERE C=40）4.设有关系R和函数依赖F： R（W，X，Y，Z），F = XZ，WXY 。试求下列问题：（1）关系R属于第几范式？（3分） R是1NF。侯选码为WX，则Y，Z为非主属性，又由于XZ，因此F 中存在非主属性对侯选

6、码的部分函数依赖。（2）如果关系R不属于BCNF，请将关系R逐步分解为BCNF。（5分）要求：写出达到每一级范式的分解过程，并指明消除什么类型的函数依赖。 将关系分解为：R1（W，X，Y），F1 = WXY R2（X，Z），F2 = XZ 消除了非主属性对码的部分函数依赖。F1和F2中的函数依赖都是非平凡的，并且决定因素是候选码，所以上述关系模式是BCNF。得 分阅卷人复查人五、综合题（15分）某医院病房管理系统中，包括四个实体型，分别为：科室：科名，科地址，科电话病房：病房号，病房地址医生：工作证号，姓名，职称，年龄病人：病历号，姓名，性别 且存在如下语义约束： 一个科室有多个病房、多个医生

7、，一个病房只能属于一个科室，一个医生只属于一个科室；一个医生可负责多个病人的诊治，一个病人的主管医生只有一个；一个病房可入住多个病人，一个病人只能入住在一个病房。注意：不同科室可能有相同的病房号。 完成如下设计：（1）画出该医院病房管理系统的ER图；（5nn11nn11病房组成科室入住病人拥有诊治医生病历号姓名性别工作证号姓名科名科地址科电话病房号病房地址职称年龄 某医院病房管理系统的基本E-R图（2）将该E-R图转换为关系模型；（5分）（要求：1:1和1:n的联系进行合并）转化后的关系模式如下：科室（科名，科地址，科电话） 病房（病房号，病房地址，科名） 医生（工作证号，姓名，职称，年龄，科

8、名） 病人（病历号，姓名，性别，主管医生，病房号，科名）（3）指出转换结果中每个关系模式的主码和外码。（5分 每个关系模式的主码、外码如下： 科室：主码是科名； 病房：主码是科名十病房号，外码是科名； 医生：主码是工作证号，外码是科名； 病人：主码是病历号，外码是科名十病房号。得 分四、设计题(第1题20分，第2题10分，共30分)1. 设有一个工程供应数据库系统，包括如下四个关系模式：l S(Sno，Sname， Status，City)；l P(Pno，Pname，Color，Weight)；l J(Jno，Jname，City)；l SPJ(Sno，Pno，Jno，Qty)；供应商表S由

9、供应商号、供应商名、状态、城市组成；零件表P 由零件号、零件名、颜色、重量组成；工程项目表J由项目号、项目名、城市组成；供应情况表SPJ由供应商号、零件号、项目号、供应数量组成；（1）用关系代数查询没有使用天津供应商生产的红色零件的工程号；（3分） Jno(J)- Jno (City=天津 (S)SPJColor=红 (P)（2）用关系代数查询至少使用了供应商S1所供应的全部零件的工程号JNO；（3分） Pno,Jno(SPJ)Pno (Sno=S1 (SPJ)（3）用ALPHA语言查询供应工程J1零件为红色的供应商号SNO；（2分）RANGE P PXGET W (SPJ.Sno): SPJ

10、.Jno=J1PX(SPJ.Pno=PX.PnoPX.Color=红)（4）用T-SQL语句建立“供应商”S表(主码必须定义)；（2分） CREATE TABLE S(Sno CHAR(6) PRIMARY KEY, Sname CHAR(10), Status INT, City CHAR(20);（5）用SQL查询工程J1使用的各种零件的名称和使用数量；（3分）SELECT Pname,TotalQty FROM (SELECT Pno,SUM(Qty) TotalQty FROM SPJ WHERE Jno=J1 GROUP BY Pno) X,P WHERE P.Pno=X.Pno;（

11、6）用SQL查询没有使用天津供应商生产的零件的工程号；（3分）SELECT Jno 或：SELECT Jno FROM J FROM J WHERE Jno NOT IN WHERE NOT EXISTS (SELECT Jno （SELECT * FROM SPJ FROM SPJ,S WHERE Sno IN WHERE SPJ.Sno=S.Sno (SELECT Sno AND SPJ.Jno=J.Jno FROM S AND City=天津);WHERE City=天津);或：SELECT Jno FROM J WHERE NOT EXISTS (SELECT * FROM SPJ W

12、HERE SPJ.Jno=J.Jno AND EXISTS (SELECT * FROM S WHERE S.Sno=SPJ.Sno AND City=天津);（7）用SQL语句将全部红色零件改为蓝色；（2分）UPDATE P SET Color=蓝 WHERE Color=红;（8）用SQL语句将（S2，P4，J6，400）插入供应情况关系。（2分）INSERT INTO SPJ VALUES（S2,P4,J6,400）；2设有关系STUDENT(S#,SNAME,SDEPT,MNAME,CNAME,GRADE)，（S#,CNAME）为候选码，设关系中有如下函数依赖： （S#,CNAME）S

13、NAME,SDEPT,MNAMES#SNAME,SDEPT,MNAME（S#,CNAME）GRADESDEPTMNAME试求下列问题：（1）关系STUDENT属于第几范式？（3分） 关系STUDENT是1NF，因为F中存在非主属性SNAME,SDEPT,MNAME对侯选码（S#,CNAME）的部分函数依赖。 （2）如果关系STUDENT不属于BCNF，请将关系STUDENT逐步分解为巧 BCNF。（7分）要求：写出达到每一级范式的分解过程，并指明消除什么类型的函数依赖。首先消除部分函数依赖（S#,CNAME）SNAME,SDEPT,MNAME将关系分解为：R1(S#,SNAME,SDEPT,M

14、NAME)，F1 = S#SNAME,SDEPT,MNAMER2(S#,CNAME,GRADE)，F2=（S#,CNAME）GRADE在关系R1中存在非主属性对候选码的传递函数依赖S#SDEPT，所以将R1进一步分解：R11(S#,SNAME,SDEPT) ，F11 = S#SNAME,SDEPTR12(SDEPT,MNAME) ，F12 = SDEPTMNAME在R2,R11,R12关系模式中函数依赖都是非平凡的，并且决定因素均是候选码，所以上述三个关系模式均是BCNF。得 分五、综合题（15分）某医院病房管理系统中，包括四个实体型，分别为：科室：科名，科地址，科电话病房：病房号，病房地址医

15、生：工作证号，姓名，职称，年龄病人：病历号，姓名，性别 且存在如下语义约束： 一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室；一个医生可负责多个病人的诊治，一个病人的主管医生只有一个；一个病房可入住多个病人，一个病人只能入住在一个病房。 注意：不同科室可能有相同的病房号。 完成如下设计：（1）画出该医院病房管理系统的ER图；（5分）（2）将该E-R图转换为关系模型；（5分）（要求：1:1和1:n的联系进行合并）（3） 指出转换结果中每个关系模式的主码和外码。（5分）（1）本题的E-R图如下图所示。nn11nn11病房组成科室入住病人拥有诊治医生病历号姓名性别工作

16、证号姓名科名科地址科电话病房号病房地址职称年龄某医院病房管理系统的基本E-R图（2）转化后的关系模式如下：科室（科名，科地址，科电话） 病房（病房号，病房地址，科名） 医生（工作证号，姓名，职称，年龄，科名） 病人（病历号，姓名，性别，主管医生，病房号，科名） （3）每个关系模式的主码、外码如下： 科室：主码是科名； 病房：主码是科名十病房号，外码是科名； 医生：主码是工作证号，外码是科名； 病人：主码是病历号，外码是科名十病房号。试题五四、设计题（本大题共4小题，每小题5分，共20分）设有关系EMP（ENO，ENAME，SALARY，DNO），其中各属性的含义依次为职工号、姓名、工资和所在部

17、门号，以及关系DEPT（DNO，DNAME，MANAGER），其中各属性含义依次为部门号、部门名称、部门经理的职工号。1试用SQL语句完成以下查询：列出各部门中工资不低于600元的职工的平均工资。 SELECT DNO, AVG (SALARY) FROM EMP（1分）WHERE SALARY=600GROUP BY DNO2 写出“查询001号职工所在部门名称”的关系代数表达式。 DNAME( ENO=001(EMP)DEPT)（DNAME1分，ENO=001 1分， (EMP)DEPT3分）或DNAME( ENO=001(EMPDEPT) （DNAME1分，ENO=001 1分，(EMP

18、DEPT)3分）3 请用SQL语句将“销售部”的那些工资数额低于600的职工的工资上调10%。 UPDATE EMP SET SALARY=SALARY*1.1 WHERE ENO IN ( SELECT ENO FROM EMP, DEPT WHERE EMP.DNO=DEPT.DNO AND DNAME=销售部 AND SALARY600）4有如下关系代数表达式ENO(EMP(sMANAGER=001(DEPT)请将其转化成相应的SQL语句。 SELECT EMP.ENOFROM EMP, DEPT（2分） WHERE EMP.DNO=DEPT.DNO AND DEPT.MANAGER=0

19、01（3分）得 分五、综合题（本大题共2小题,每小题10分,共20分）1设某商业集团数据库中有一关系模式R如下：R (商店编号，商品编号，数量，部门编号，负责人)如果规定：（1）每个商店的每种商品只在一个部门销售；（2）每个商店的每个部门只有一个负责人；（3）每个商店的每种商品只有一个库存数量。试回答下列问题：（1） 根据上述规定，写出关系模式R的基本函数依赖；（2分） 有三个函数依赖： (商店编号，商品编号) 部门编号 (商店编号，部门编号) 负责人 (商店编号，商品编号) 数量。（2） 找出关系模式R的候选码；（2分） R的候选码是 (商店编号，商品编号)（3） 试问关系模式R最高已经达到

20、第几范式？为什么？（2分） R最高达到2NF（1分），因为R中存在着非主属性“负责人”对候选码 (商店编号、商品编号)的传递函数依赖，所以R属于2NF，但R不属于3NF。（4）如果R不属于3NF，请将R分解成3NF模式集。（4分） 将R分解成：R1 (商店编号，商品编号，数量，部门编号) （2分） R2 (商店编号，部门编号，负责人) （2分）2. 设有商店和顾客两个实体，“商店”有属性商店编号、商店名、地址、电话，“顾客”有属性顾客编号、姓名、地址、年龄、性别。假设一个商店有多个顾客购物，一个顾客可以到多个商店购物，顾客每次去商店购物有一个消费金额和日期，而且规定每个顾客在每个商店里每天最多

21、消费一次。试画出E-R图，注明属性和联系类型，并将E-R模型转换成关系模式，要求关系模式主码加下划线表示。(E-R模型4分，关系模型6分)2. nm顾客购物商店商店编号商店名日期年龄性别顾客编号姓名消费金额地址地址电话（E-R模型4分，两个实体型的属性漏写一个扣0.5分，购物联系的两个属性漏写一个扣1分，联系类型错扣1分）顾客（顾客编号，姓名，地址，年龄，性别）商店（商店编号，商店名，地址，电话）购物（顾客编号，商店名称，日期，消费金额）（关系模型6分，3个关系模式各2分，主码错一个扣1分）试题六四、设计题（本大题共5小题，每小题4分，共20分）现有关系数据库如下： 学生（学号，姓名，性别，专

22、业） 课程（课程号，课程名，学分） 学习（学号，课程号，分数）分别用关系代数表达式和SQL语句实现下列15小题（注意：每小题都要分别写出关系代数表达式和SQL语句！每小题关系代数表达式2分，SQL语句2分）：1检索所有选修了课程号为“C112”的课程的学生的学号和分数； 2检索“英语”专业学生所学课程的信息，包括学号、姓名、课程名和分数；3检索“数据库原理”课程成绩高于90分的所有学生的学号、姓名、专业和分数；4检索没学课程号为“C135”课程的学生信息，包括学号，姓名和专业；5检索至少学过课程号为“C135”和“C219”的课程的学生的信息，包括学号、姓名和专业。得 分五、综合题（本大题共2

23、小题，每小题10分，共20分）1现有如下关系模式：借阅（图书编号，书名，作者名，出版社，读者编号，读者姓名，借阅日期，归还日期），基本函数依赖集F=图书编号（书名，作者名，出版社），读者编号读者姓名，（图书编号，读者编号，借阅日期）归还日期（1）读者编号是候选码吗？（2分）（2）写出该关系模式的主码。（2分）（3）该关系模式中是否存在非主属性对码的部分函数依赖？如果存在，请写出一个。（2分）（4）该关系模式满足第几范式？并说明理由。（4分）3. 某工厂生产多种产品，每种产品由不同的零件组装而成，有的零件可用在不同的产品上。产品有产品号和产品名两个属性，零件有零件号和零件名两个属性。根据语义设计

24、E-R模型，并将E-R模型转换成关系模式，要求关系模式主码加下划线表示。(E-R模型4分，关系模型6分)四、设计题（共5小题，每小题关系代数式2分，SQL语句2分，共20分）。1SQL语句：SELECT 学号,分数 FROM 学习 WHERE 课程号=C112（SELECT 学号,分数 FROM 学习1分，WHERE 课程号=C1121分）关系代数：学号，分数(课程号=C112(学习) （学号，分数1分，课程号=C112(学习)1分。2. SQL语句：SELECT 学生.学号,姓名,课程名,分数FROM 学生,学习,课程 （1分）WHERE 学习.学号=学生.学号 AND 学习.课程号=课程.

25、课程号 AND 专业=英语 （1分） 关系代数：学号，姓名，课程名，分数(学号，姓名(专业=英语(学生)学习课程号，课程名(课程) （学号，姓名，课程名，分数1分，学号，姓名(专业=英语(学生)学习课程号，课程名(课程)1分）3. SQL语句：SELECT 学生.学号,姓名,专业,分数 FROM 学生,学习,课程 （1分）WHERE 学生.学号=学习.学号 AND 学习.课程号=课程.课程号 AND分数90 AND 课程名=数据库原理 （1分）关系代数：学号，姓名，专业,分数(学号，姓名，专业(学生)(分数90(学习)课程号，课程名( 课程名=数据库原理(课程) （学号，姓名，专业,分数1分，学号，姓名，专业(学生)(分数90(学习)课程号，课程名(课程名=数据库原理(课程) 1分）4SQL语句：SELECT 学号,姓名,专业 FROM 学生 WHERE 学号 NOT IN （1分）（SELECT 学号 FROM 学习 WHERE 课程号=C135） （1分）关系代数：(学号(学生)-学号(课程号=C135 (学习)(学号，姓名，专业(学生)（学号(学生