数据库期末由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“数据库系统期末”。
一．ＥＲ图作成及向关系模式转化
1.学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中有的教授和副教授每人各带若干研究生；每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修。请用 E 一 R 图画出此学校的概念模型。
2.某学生宿舍管理系统，涉及的部分信息如下：(1)学生：学号，姓名，性别，专业，班级。(2)寝室：寝室号，房间电话。
(3)管理员：员工号，姓名，联系电话。
其中：每个寝室可同时住宿多名学生，每名学生只分配一个寝室；每个寝室指定其中一名学生担当寝室长；每个管理员同时管理多个寝室，但每个寝室只有一名管理员。(1)建立一个反映上述局部应用的ER模型，要求标注联系类型。
(2)根据转换规则，将ER模型转换为关系模型。要求标注每个关系模型的主键和外键（如果存在）。（1）
（2）
学生（学号，姓名，性别，专业，班级）主键：学号
住宿（寝室号，学号）主键：学号
寝室号是外键；学号也是外键
学生管理寝室（寝室号，寝室长学号）主键：寝室号或者寝室长学号
寝室号是外键；寝室长学号也是外键
寝室（寝室号，房间电话）主键：寝室号
管理（寝室号，员工号）主键：寝室号
寝室号是外键；员工号是外键
管理员（员工号，姓名，联系电话）主键：员工号
合并后的答案：
学生（学号，姓名，性别，专业，班级，寝室号）主键：学号 寝室号是外键
寝室（寝室号，房间电话，寝室长学号，员工号）主键：寝室号或者寝室长学号 员工号是外键；寝室长学号是外键
管理员（员工号，姓名，联系电话）主键：员工号
3设某工厂数据库中有四个实体集。
一是“仓库”实体集，属性有仓库号、仓库面积等； 二是“零件”实体集，属性有零件号、零件名、规格、单价等； 三是“供应商”实体集，属性有供应商号、供应商名、地址等； 四是“保管员”实体集，属性有职工号、姓名等。
设仓库与零件之间有“存放”联系，每个仓库可存放多种零件，每种零件可存放于若干仓库中，每个仓库存放每种零件要记录库存量；供应商与零件之间有“供应”联系，一个供应商可供应多种零件，每种零件也可由多个供应商提供，每个供应商每提供一种零件要记录供应量；仓库与保管员之间有“工作”联系，一个仓库可以有多名保管员，但一名保管员只能在一个仓库工作。
(1)试为该工厂的数据库设计一个ER模型，要求标注联系类型。
(2)根据转换规则，将ER模型转换成关系模型，要求标明每个关系模式的主键和外键。
（2）
仓库（仓库号，仓库面积）主键：仓库号
存放（仓库号，零件号，库存量）主键：仓库号，零件号
仓库号是外键，零件好也是外键。
零件（零件号，零件名，规格，单价）主键：零件号
供应（零件号，供应商号，供应量）主键：零件号，供应商号
零件号是外键，供应商号是外键
供应商（供应商号，供应商名，地址）主键：供应商号
工作（职工号，仓库号）主键：职工号
职工号是外键，仓库号是外键
保管员（职工号、姓名）主键：职工号
合并后的答案：
仓库（仓库号，仓库面积）主键：仓库号
存放（仓库号，零件号，库存量）主键：仓库号，零件号
仓库号是外键，零件好也是外键。
零件（零件号，零件名，规格，单价）主键：零件号
供应（零件号，供应商号，供应量）主键：零件号，供应商号
零件号是外键，供应商号是外键
供应商（供应商号，供应商名，地址）主键：供应商号
保管员（职工号，姓名，仓库号）主键：职工号 仓库号是外键
二． 函数依赖识别以及规范到3NF 1.设某人才市场数据库中有一个记录应聘人员信息的关系模式： R（人员编号，姓名，性别，职位编号，职位名称，考试成绩）
如果规定：每人可应聘多个职位，每个职位可由多人应聘且必须参加相关考试，考试成绩由人员编号和职位编号确定。
姓名可以重复。职位名称不可重复。
（1）根据上述规定，写出模式R的基本FD和关键码。（2）R最高属于第几范式。（3）将R规范到3NF。答案：
（1）根据上述规定，写出模式R的基本FD和关键码。人员编号→姓名 人员编号→性别 职位编号→职位名称 职位名称→职位编号
（人员编号，职位编号）→考试成绩 候选码：（人员编号，职位编号）（2）R最高属于第几范式。
R最高属于第一范式。因为非主属性姓名，性别部分依赖于码，不属于第二范式。（3）将R规范到3NF。
人员（人员编号，姓名，性别）职位（职位编号，职位名称）
应聘（人员编号，职位编号，考试成绩）2.设有一个反映教师参加科研项目的关系模式：
R（教师号，教师名称，项目名称，科研工作量，项目类别，项目金额，负责人）
如果规定：每个项目可有多人参加，每名教师每参加一个项目有一个科研工作量；每个项目只属于一种类别，只有一名负责人。教师名称可以重复。（1）根据上述规定，写出模式R的基本FD和关键码。（2）说明R不是2NF的理由。（3）将R规范到3NF。答案：（1）根据上述规定，写出模式R的基本FD和关键码。教师号→教师名称 项目名称→项目类别 项目名称→项目金额 项目名称→负责人
（教师号，项目名称）→科研工作量 主键：（教师号，项目名称）（2）说明R不是2NF的理由。
非主属性教师名称，项目类别，项目金额，负责人部分依赖码。（3）将R规范到3NF。教师（教师号，教师名称）
项目（项目名称，项目类别，项目金额，负责人）项目开发（教师号，项目名称，科研工作量）3.设有一个记录学生毕业设计情况的关系模式：
R（学号，学生名，班级，教师号，教师名，职称，毕业设计题目，成绩）
如果规定：每名学生只有一位毕业设计指导教师，每位教师可指导多名学生；学生的毕业设计题目可能重复。
（1）根据上述规定，写出模式R的基本FD和关键码。（2）R最高属于几范式。（3）将R规范到3NF。
三． SQL语句
1.零件、供应商、采购 设有以下关系模式
PART(PNO, PNAME, PMODEL)零件号、零件名称、零件型号
BUY(PNO, SNO, BDATE, BPRICE, BNUM)零件号、供应商号、采购日期、采购价格、采购数量
SUPPLIER(SNO, SNAME, SADDRESS)供应商号，供应商名称，供应商地址
要求写出符合下列题目要求的SQL语句
（1）查询供应商名称为‟创新零件‟的所有零件购买记录（零件号、零件名称、零件型号、供应商号、供应商名称、采购日期、采购价格、采购数量）。
查询供应商名称为‟创新零件‟的所有零件购买记录（零件号、零件名称、零件型号、供应商号、供应商名称、采购日期、采购价格、采购数量）。
SELECT PART.PNO, PART.PNAME, PART.PMODEL, SUPPLIER.SNO, SUPPLIER.SNAME, BUY.BDATE, BUY.BPRICE, BUY.BNUM FROM PART, SUPPLIER, BUY WHERE PART.PNO = BUY.PNO AND
BUY.SNO = SUPPLIER.SNO AND
SUPPLIER.SNAME = '创新零件';（2）查询零件‟O性密封圈‟并且型号为‟5*5‟的零件购买记录。
SELECT PART.PNO, PART.PNAME, PART.PMODEL, SUPPLIER.SNO, SUPPLIER.SNAME, BUY.BDATE, BUY.BPRICE, BUY.BNUM FROM PART, SUPPLIER, BUY WHERE PART.PNO = BUY.PNO AND
BUY.SNO = SUPPLIER.SNO AND
PART.PNAME = 'O型密封圈' AND
PART.PMODEL = '5*5';（3）查询2011年5月份的零件购买记录
SELECT PART.PNO, PART.PNAME, PART.PMODEL, SUPPLIER.SNO, SUPPLIER.SNAME, BUY.BDATE, BUY.BPRICE, BUY.BNUM FROM PART, SUPPLIER, BUY WHERE PART.PNO = BUY.PNO AND
BUY.SNO = SUPPLIER.SNO AND
BUY.BDATE BETWEEN TO_DATE(‘2011-05-01', 'YYYY-MM-DD')AND TO_DATE('2011-05-31', 'YYYY-MM-DD');（4）查询2011年度采购价格1000元以上的零件购买记录
SELECT PART.PNO, PART.PNAME, PART.PMODEL, SUPPLIER.SNO, SUPPLIER.SNAME, BUY.BDATE, BUY.BPRICE, BUY.BNUM FROM PART, SUPPLIER, BUY
WHERE PART.PNO = BUY.PNO AND
BUY.SNO = SUPPLIER.SNO AND
BUY.BPRICE >= 1000 AND
BUY.BDATE BETWEEN TO_DATE('2011-1-1', 'YYYY-MM-DD')AND TO_DATE('2011-12-31', 'YYYY-MM-DD');（5）查询2011年度各零件的购买总数量和总金额，按零件号排列
SELECT PART.PNO, PART.PNAME, PART.PMODEL, SUM(BUY.BNUM)AS CNT, SUM(BUY.BPRICE * BUY.BNUM)AS AMOUNT FROM PART, BUY WHERE PART.PNO = BUY.PNO AND
BUY.BDATE BETWEEN TO_DATE('2011-1-1', 'YYYY-MM-DD')AND TO_DATE('2011-12-31', 'YYYY-MM-DD')GROUP BY PART.PNO, PART.PNAME, PART.PMODEL ORDER BY PART.PNO;（6）查询各供应商2011年度的零件供应总数量和总金额，按总金额逆序排列
SELECT SUPPLIER.SNO, SUPPLIER.SNAME, SUM(BUY.BNUM)AS CNT, SUM(BUY.BPRICE * BUY.BNUM)AS AMOUNT FROM SUPPLIER, BUY WHERE BUY.SNO = SUPPLIER.SNO AND
BUY.BDATE BETWEEN TO_DATE('2011-1-1', 'YYYY-MM-DD')AND TO_DATE('2011-12-31', 'YYYY-MM-DD')
GROUP BY SUPPLIER.SNO, SUPPLIER.SNAME ORDER BY AMOUNT DESC;（7）查询供应商‟创新零件‟2011年度的零件供应总数量和总金额
SELECT SUPPLIER.SNO, SUPPLIER.SNAME, SUM(BUY.BNUM)AS CNT, SUM(BUY.BPRICE * BUY.BNUM)AS AMOUNT FROM SUPPLIER, BUY WHERE BUY.SNO = SUPPLIER.SNO AND
SUPPLIER.SNAME = '创新零件' AND
BUY.BDATE BETWEEN TO_DATE('2011-1-1', 'YYYY-MM-DD')AND TO_DATE('2011-12-31', 'YYYY-MM-DD')
GROUP BY SUPPLIER.SNO, SUPPLIER.SNAME；（8）查询符合下列条件的零件购买记录 零件号‟PART01‟ 非‟创新零件‟供应商
零件采购价格低于‟创新零件‟且零件号为‟PART01‟的最低价格
SELECT PART.PNO, PART.PNAME, PART.PMODEL, SUPPLIER.SNO, SUPPLIER.SNAME, BUY.BDATE, BUY.BPRICE, BUY.BNUM FROM PART, SUPPLIER, BUY WHERE PART.PNO = BUY.PNO AND
BUY.SNO = SUPPLIER.SNO AND
PART.PNO = 'PART01' AND
SUPPLIER.SNAME '创新零件' AND
BUY.BPRICE
VALUES('PART06', '轮胎', '225/55 R17');（10）在零件表中删除零件规格为„225/55 R17‟的记录 DELETE FROM PART
WHERE PART.PMODEL = '225/55 R17';
（11）更新供应商‟创新零件‟的地址为‟杭州市‟ UPDATE SUPPLIER SET SUPPLIER.SADDRESS ='杭州市' WHERE SUPPLIER.SNAME = '创新零件';（12）生成一个视图ANNUALBUY显示2011年度所有零件购买记录，按照供应商号排序，一个供应商内按照零件号排序 CREATE VIEW ANNUALBUY AS SELECT SUPPLIER.SNO, SUPPLIER.SNAME, PART.PNO, PART.PNAME, PART.PMODEL, BUY.BDATE, BUY.BPRICE, BUY.BNUM FROM PART, SUPPLIER, BUY WHERE PART.PNO = BUY.PNO AND
BUY.SNO = SUPPLIER.SNO AND
BUY.BDATE BETWEEN TO_DATE('2011-1-1', 'YYYY-MM-DD')AND TO_DATE('2011-12-31', 'YYYY-MM-DD')
ORDER BY SUPPLIER.SNO, PART.PNO;
（13）赋予用户张小明对零件表有SELECT权利，对购买表的采购数量字段有更新权限。GRANT SELECT ON PART, UPDATE(BNUM)ON BUY TO 张小明;（14）收回用户张小明对零件表有SELECT权利。REVOKE SELECT ON PART FROM 张小明;2.试用SQL语言查询项目金额在20000元~30000元（包括20000元和30000元）之间并且项目名称以‟银行‟开头的项目名称，项目类别，项目金额。SELECT PNAME,PCATOGERY,AMOUNT FROM PROJECT WHERE AMOUNT BETWEEN 20000 AND 30000 AND PNAME LIKE ‘银行%’;
3.试用SQL语言查询参加项目名称为“网上书城”的教师姓名。
SELECT TNAME FROM TEACHER, DELIVERY,PROJECT WHERE TEACHER.TNO=DELIVERY.TNO AND DELIVERY.PNO=PROJECT.PNO AND PROJECT.PNAME=‘网上书城’;
