中级数据库系统工程师上半年下午试题答案及详细解析.docx
《中级数据库系统工程师上半年下午试题答案及详细解析.docx》由会员分享，可在线阅读，更多相关《中级数据库系统工程师上半年下午试题答案及详细解析.docx（15页珍藏版）》请在冰豆网上搜索。
中级数据库系统工程师上半年下午试题答案及详细解析
第14章数据库系统工程师级下午试题分析与解答
试题一分析参见软件设计师。
试题二
阅读下列说明，回答问题1至问题5，将解答填入对应栏内。
[说明]
某工厂的仓库管理数据库的部分关系模式如下所示：
仓库（仓库号，面积，负责人，电话）
原材料（编号，名称，数量，储备量，仓库号）
要求一种原材料只能存放在同一仓库中。
“仓库”和“原材料”的关系实例分别如表2-1和表2-2所示。
表2-1“仓库”关系（表格）
仓库号
面积
负责人
电话
仓库号
面积
负责人
电话
01
500
李劲松
87654121
03
300
郑爽
87654123
02
300
陈东明
87654122
04
400
刘春来
87654125
表2-2“原材料”关系
编号
名称
数量
储备量
仓库号
1001
小麦
100
50
01
2001
玉米
50
30
01
1002
大豆
20
10
02
2002
花生
30
50
02
3001
菜油
60
20
03
【问题1】
根据上述说明，用SQL定义“原材料”和“仓库”的关系模式如下，请在空缺处填入正确的内容。
CREATETABLE仓库（仓库号CHAR（4），
面积INT，
负责人CHAR（8），
电话CHAR（8），
（a））；//主键定义
CREATETABLE原材料（编号CHAR（4）（b），//主键定义
名称CHAR（16），
数量INT,
储备量INT，
仓库号（c），
（d））；//外键定义
【问题2】
将下面的SQL语句补充完整，完成“查询存放原材料数量最多的仓库号”的功能。
SELECT仓库号
FROM（e）
（f）；
【问题3】
将下面的SQL语句补充完整，完成“01号仓库所存储的原材料信息只能由管理员李劲松米维护，而采购员李强能够查询所有原材料的库存信息”的功能。
CREATEVIEWraws_in_wh01AS
SELECT（g）
FROM原材料
WHERE仓库号="01"；
GRANT（h）ON（i）TO李劲松；
GRANT（j）ON（k）TO李强；
【问题4】
仓库管理数据库的订购计划关系模式为：
订购计划（原材料编号，订购数量）。
采用下面的触发器程序可以实现“当仓库中的任一原材料的数量小于其储备量时，向订购计划表中插入该原材料的订购记录，其订购数量为储备量的三倍”的功能。
请将该程序的空缺部分补充完整。
CREATETRIGGERins_order_triggerAFTER
（1）ON原材料
REFERENCINGNEWROWASnrow
FOREACHROW
WHENnrow．数量＜arow．储备量
INSERTINTO订购计划VALUES
（（m），（n））；
【问题5】
如果一种原材料可以在多个仓库中存放，则问题4中的触发器程序存在什么问题，如何修改?
试题二分析
[问题1]
本问题考查应试者对SQL数据库定义语言的掌握。
通过给出的关系模式及关系实例，完成数据库定义语句中的主键及外键的定义部分。
主码的定义可以有3种：
在列级约束中用NOTNULLUNIQUE或PRIMARYKEY指定；在表级约束中用PRIMARYKEY＜主键，的方式定义。
前两种方法只适用于单一属性作主码的情况，后一种适用于任何情况。
参照完整性的指定使用FOREIGNKEY＜外键＞REFERENCES＜被参照关系＞（《被参照属性＞）的方式定义，参照关系的域应和被参照数据的域相同。
[问题2]
本题考查应试者对SQL查询语句的掌握。
完成本查询的思路：
根据原材料表，按照“仓库号”分组，组内的记录对“数量”求和，分组求和得到的最大值对应的仓库号即为所求。
参考答案中的子查询完成各仓库存储数量的求和，外部查询输出组内“数量”求和为最大值的仓库号。
[问题3]
本问题考查应试者对SQL视图定义和授权语句的掌握。
题干给出了通过视图和授权机制实现数据库安全性的方法：
建立了01号仓库的视图，将对视图的更新权限赋给李劲松。
题干同时要求对原材料的查询权限赋予李强，可以直接使用授权语句。
授权的基本语法：
GRANT＜权限＞ON＜对象＞TO＜用户名＞；
[问题4]
本问题考查应试者对触发器程序编写的掌握。
空
（1）处要求填入的是触发操作（INSERT、UPDATE或DELETE），空（m），（n）处填入的是欲插入的值。
本题的关键是读懂程序。
[问题5]
本题存在问题：
触发器程序判定某一原材料“数量”是否小于其存储量时，是按照当前记录的“数量”来判定的，当一种原材料存储在多个仓库时，这样判定是错误的，应根据该原材料在各仓库的存储总量判定。
参考答案
[问题1]
（a）PRIMARYKEY仓库号
（b）PRIMARYKEY或NOTNULLUNIQUE或NOTNULLPRIMARYKEY
（c）CHAR（4）
（d）FOREIGNKEY仓库号REFERENCES仓库（仓库号）
[问题2]
（e）原材料
（f）GROUPBY仓库号
HAVINGSUM（数量）＞=ANY（SELECTSUM（数量）
FROM原材料
GROUPBY仓库号）
[问题3]
（g）*或编号，名称，数量，储备量，仓库号
（h）INSERT，DELETE，UPDATE
（j）rawsinwh01
（j）SELECT
（k）原材料
[问题4]
（1）UPDATE，INSERT
（m）nrow,编号
（n）nrow．存储量*3
[问题5]
存在问题：
触发器程序判定某一原材料“数量”是否小于其存储量时，是按照当前记录的“数量”来判定的，当一种原材料存储在多个仓库时，这样判定是错误的，应根据该原材料在各仓库的存储总量判定。
应将触发器程序的WHEN子句条件修改为：
WHENnrow.储备量＞（SELECTSUM（数量）
FROM原材料
WHERE编号=（SELECT编号
FROMnrow）
GROUPBY编号）
试题三
[说明]
某市人才交流中心为促进当地人力资源的合理配置，加强当地企业与人才的沟通，拟建立人才信息交流网。
[需求分析结果]
1．每个前来登记的个人需填写《人才入库登记表》（如表3—1所示），并出示相关证件，经工作人员审核后录入个人信息。
2．每个前来登记的企业需填写《企业信息登记表》（如表3-2所示），并出示相关证明及复印件，经工作人员核实后录入企业信息。
3．个人和企业的基本信息只需在第一次登记时填写，个人编号和企业编号由系统自动生成。
个人和企业的基本信息由电脑长期存储，以后个人只需提供个人编号和求职意向信息，企业只需提供企业编号和岗位需求信息。
4．个人的求职意向信息和企业的岗位需求信息在两个工作日内由工作人员录入数据库并发布。
表3-1人才入库登记表
个人编号：
______登记日期：
______年______月______日
表3-2企业信息登记表
企业编号：
______登记日期：
______年______月______日
[概念模型设计]
根据需求阶段收集的信息，设计人才、岗位和企业的实体联系图（不完整）如图3-1所示。
图3-1人才、岗位和企业的实体联系图
[逻辑结构设计]
1．将概念模型设计的实体联系图转换为以下关系模式：
人才（个人编号，姓名，性别，出生日期，身份证号，毕业院校，专业，学历，证书名称，证书编号，联系电话，电子邮件，个人简历及特长）
企业（企业编号，企业名称，联系人，联系电话，地址，企业网址，电子邮件，企业简介）
求职意向（（b））
岗位需求（（c））
2．由于一个人可能持有多个证书，对“人才”关系模式进行优化，得到如下两个新的关系模式：
人才（（d））
证书（（e））
根据上述的设计过程，回答以下问题：
[问题1]
在（a）处填入所需的实体、联系及其属性，完成概念模型设计。
[问题2]
在（b）、（c）、（d）、（e）处填入对应关系的属性，完成逻辑结构设计。
[问题3]
对最终的各关系模式，以下划线指出其主键和外键。
[问题4]
张工设计的实体联系图如图3-2所示，请用200字
[问题5]
如果允许企业通过互联网修改本企业的基本信息，应对数据库的设计做何种修改?
请用200字以内的文字叙述实现方案。
试题三分析
【问题1】
本题考查应试者对概念模型设计和分析能力的掌握。
按照题干的说明，应该建立人才、岗位和企业三个实体的联系，人才与岗位之间是求职意向联系，企业与岗位之间是岗位需求联系。
按照所给定的登记表中的属性，组织两个联系的属性。
实体、联系及属性的名称根据题目上出现的名称来填写。
【问题2】
逻辑结构设计是建立在概念结构设计的基础上的，按照E-R图向关系模式的转换方法，将实体和联系分别转换为关系模式。
本题中已给出了实体对应的关系模式，其中岗位实体没有属性，应归并到联系中。
本题还要求对关系模式进行模式分解，以达到实体分离的优化目的（也是规范化）。
【问题3】
各关系模式的主键和外键可以根据关系模式的语义，并结合E-R向关系模式的转换方法来确定。
【问题4】
此处的“需求”是“岗位”、“企业”和“人才”三个实体之间的联系，而事实上只有人才被聘用之后三者之间才产生联系。
本系统解决的是人才的求职和企业的岗位需求，人才与企业之间没有直接的联系。
【问题5】
建立企业的登录信息表，包含用户名和密码，记录企业的用户名和密码，将对本企业的基本信息的修改权限赋予企业的用户名，企业工作人员通过输入用户名和密码，经过服务器将其与登录信息表中记录的该企业的用户名和密码进行验证后，合法用户才有权限修改企业的信息。
参考答案
[问题1]
（a）
[问题2]
（b）个人编号，岗位，最低薪水，登记日期
（c）企业编号，岗位，专业，学历，薪水，备注，登记日期
（d）个人编号，姓名，性别，出生日期，身份证号，毕业院校，专业，学历，证书名称，联系电话，电子邮件，个人简历及特长
（e）证书名称，证书编号
[问题3]
企业（企业编号，企业名称，联系人，联系电话，地址，企业网址，电子邮件，企业简介）
求职意向（
，最低薪水，登记日期）
岗位需求（
，专业，学历，薪水，备注，登记日期）
人才（个人编号，姓名，性别，出生日期，身份证号，毕业院校，专业，学历，
，联系电话，电子邮件，个人简历及特长）
证书（证书名称，证书编号）
[问题4]
此处的“需求”是“岗位”、“企业”和“人才”三个实体之间的联系，而事实上只有人才被聘用之后三者之间才产生联系。
本系统解决的是人才的求职和企业的岗位需求，人才与企业之间没有直接的联系。
[问题5]
建立企业的登录信息表，包含用户名和密码，记录企业的用户名和密码，将对本企业的基本信息的修改权限赋予企业的用户名，企业工作人员通过输入用户名和密码，经过服务器将其与登录信息表中记录的该企业的用户名和密码进行验证后，合法用户才有权限修改企业的信息。
试题四
[说明]
E软件开发公司，决定开发设计本公司的项目工作管理系统，由王先生承担数据库的设计工作。
公司项目管理的需求分析如下。
1．组织机构：
E公司有多个部门，每个部门有多个职员、多个办公室，每个办公室有一部电话。
当部门变更时更换新的部门代码。
职员辞职后，若再次被聘用仍使用辞职前的代码。
被聘用职员担任某职务，职务用职务代码来标识。
职务分为工程师、高级工程师、经理助理、经理等。
职员的工资根据等级区分，共分为S、A、B、C、D五个等级。
—个职务对应某个等级，一个等级对应多个职务。
职员月工资等于职员月工作时间（小时）乘以小时工资。
职员的人事变动以及职位变更（升级、降级）在月初进行。
2．项目管理：
项目用项目代码标识，使用过的项目代码不能重复使用。
一个部门可承担多个项目，但一个项目仅由一个部门承担。
一个项目有一名项目主管和多名职员：
一名职员可参加多个项目。
项目代码由系统自动生成，一旦项目建立，项目名、部门代码以及起始年月日不能再变更。
3．项目的工作管理流程为：
项目工作计划输入（初始计划）→工作业绩输入→业绩生成（每月一次）→计划修正（每月一次）。
·项目工作计划输入：
项目主管使用如图4-1所示的工作计划输入界面，输入项目代码、职员代码、职员参加某个项目的月工作时间（计划）。
图中白色部分为可输入项。
图4-1工作计划输入界面（初始计划）
·工作业绩输入：
输入职员每天参加各个项目的工作时间。
图4-2为工作业绩输入界面，图中白色部分为可输入项。
其中，出勤时间由考勤系统管理，指定项目代码的顺序可以不同，并且一天可以输入多个项目代码，但同一个项目代码不能重复输入。
·业绩生成：
月底汇总职员的当月工作业绩，生成月工作业绩表。
·计划修正：
项目主管根据项目进度修改以后的工作计划。
图4-2为工作业绩输入界面
王先生根据公司的项目需求将数据库关系模式设计如下：
部门（部门代码，部门名，起始年月，终止年月，办公室，办公电话）
职务（职务代码，职务名）
等级（等级代码，等级名，年月，小时工资）
职员（职员代码，职员名，部门代码，职务代码，任职时间）
项目（项目代码，项目名，部门代码，起始年月口，结束年月日，项目主管）
工作计划（项目代码，职员代码，年月，工作时间）
【问题1】
请使用“关系模式标记规则”，给出部门、等级、项目、工作计划关系模式的主键和外
键，以及基本函数依赖集F1、F2、F3和F4。
【问题2】
请将下面关系模式中的（a）和（b）处填入属性名称，要求使用说明中已有的属性名称。
（1）王先生设计的关系模式不能管理职务和等级之间的关系，可以通过修改“职务”
关系模式实现，修改后的关系模式为：
职务（（a））
（2）为了管理公司职员参加各项目每天的工作业绩，需设计工作业绩关系模式为：
工作业绩（（b））
【问题3】
（1）部门关系模式存在什么问题?
请用100字以内的文字阐述原因。
为了解决这个问题可将关系模式分解，分解后的关系模式的关系名依次取部门_A、部门_B、……
（2）假定月工作业绩关系模式为：
月工作业绩（职员代码，年月，工作时间），请给出“杳询职员代码、职员名、年月、月工资”的SQL语句。
试题四分析
试题四是关于数据库设计方面的题目。
关系数据库设计的目标是生成一组合适的、性能良好的关系模式，以减少系统中信息存储的冗余度，但又可方便地获取信息。
[问题1]
为了正确给出关系模式的主键和外键，需要正确地理解主键和外键的基本概念。
首先看一下什么是候选码。
设K为R（U，F）中的属性的组合，若
，且对于K的任何一个真子集K'，都有K'不能决定U，则K为R的候选码（候选关键字），若有多个候选码，则选一个作为主
码（主键）。
1）部门关系
根据题意在部门关系中，由于E公司每个部门多个办公室，部门代码多值决定办公室，如果仅用部门代码无法唯一区分部门关系中的每一个元组（记录）。
但是，用（部门代码，办公室）作为主键，可以唯一区分部门关系中的每一个元组，因此，正确答案如下：
部门（部门代码，部门名，起始年月，终止年月，办公室，办公电话）
F1=｛部门代码→（部门名，起始年月，终止年月），部门代码→→办公室，办公室→办公电话｝
2）等级关系
根据题意在等级关系中，由于一个职务对应某个等级，一个等级对应多个职务。
职员月工资等于职员月工作时间（小时）乘以小时工资。
职员的人事变动以及职位变更（升级、降级）在月初进行。
如果用等级代码无法唯一区分等级关系中的每一个元组，这是因为对于同一个等级在不同的时期小时工资不一定一样。
例如，等级1在2004年1月小时工资为5元，到2004年10月小时工资为6元。
可见用（等级代码，年月）作为主键，可以唯一区分等级关系中的每一个元组。
因此正确答案如下：
部门（部门代码，部门名，起始年月，终止年月，办公室，办公电话）
等级（等级代码，等级名，年月，小时工资）
F2=｛等级代码→等级名，（等级代码，年月）→小时工资｝
3）项目关系
项目代码可以决定项目关系中的全属性，所以主键是项目代码。
在项目关系中，部门代码是部门关系的主键，所以部门代码应是外键。
项目主管应该来自职员，所以项目主管应是外键。
因此正确答案如下：
项目（项目代码，项目名，
，起始年月日，结束年月日，
）
F3=｛项目代码→（项目名，部门代码，起始年月日，结束年月日，项目主管）｝
4）工作计划
在工作计划关系中，由于一个项目有多个职员参加，因此仅用项目代码无法唯一确定关系中的每一个元组。
又由于工作计划是按月给职员安排的，所以，工作计划关系的主键是（项目代码，职员代码，年月）。
因此正确答案如下：
工作计划（项目代码，职员代码，年月，工作时间）
F4=｛（项目代码，职员代码，年月）→工作时间｝
[问题2]
（1）王先生设计的关系模式不能管理职务和等级之间的关系，为此可以在“职务”关系模式中增加属性等级代码实现，修改后的关系模式为：
职务（职务代码，职务名，等级代码）
（2）为了管理公司职员参加各项目每天的工作业绩，需设计工作业绩关系模式，该模式中有一个属性“年月日”是表示。
假设工作业绩的具体实例如下：
目代码
职员工码
年月日
工作日期
项目代码
职员代码
年月日
工作日期
1234567
12345
2005-04-04
7.0
2345678
12345
2005-04-04
2.0
1234567
12345
2005-04-05
7.0
2345678
12345
2005-04-05
1.0
1234567
12345
2005-04-06
7.0
2345678
12345
2005-04-06
2.0
1234567
12345
2005-04-07
8.0
…
…
…
…
1234567
12345
2005-04-08
5.5
可以根据该实例实现工作业绩输入界面。
因此工作业绩关系模式如下：
工作业绩（项目代码，职员代码，年月日，工作时间）
[问题3]
（1）部门关系模式存在的主要问题是数据冗余，因为部门关系模式属于2范式（或2NF）。
例如，假设某个部门有10个办公室，部门代码、部门名、起始年月、终止年月就要重复10次。
为了解决这个问题可将模式分解，分解后的关系模式为：
部门_A（部门代码，部门名，起始年月，终止年月）
部门_B（部门代码，办公室，办公电话）
（2）假定月工作业绩关系模式为：
月工作业绩（职员代码，年月，工作时间），那么“查询职员代码、职员名、年月、月工资”的SQL语句如下；
SELECT职员代码，职员名，年月，工作时间*小时工资AS月工资
FROM职员，职务，等级，月工作业绩
WHERE职员.职务代码=职务.职务代码AND
职务.等级代码；等级.等级代码AND
等级.年月=月工作业绩.年月AND
职员.职员代码；月工作业绩.职员代码；
参考答案
[问题1]
部门（部门代码，部门名，起始年月，终止年月，办公室，办公电话）
F1=｛部门代码→（部门名，起始年月，终止年月），部门代码→→办公室，办公室→办公电话｝
注：
部门代码→（部门名，起始年月，终止年月）可写成（下同）：
部门代码→部门名，部门代码→起始年月，部门代码→终止年月
等级（等级代码，等级名，年月，小时工资）
F2=｛等级代码→等级名，（等级代码，年月）→小时工资｝
项目（项目代码，项目名，
，起始年月日，结束年月日，
）
F3=｛项目代码→（项目名，部门代码，起始年月日，结束年月日，项目主管）｝
工作计划（项目代码，职员代码，年月，工作时间）
F4=｛（项目代码，职员代码，年月）→工作时间｝
[问题2]
（1）职务（职务代码，职务名，等级代码）
（2）工作业绩（项目代码，职员代码，年月日，工作时间）
[问题3]
（1）部门关系模式属于2范式（或2NF），该关系模式存在冗余问题，因为某部门有多少个办公室，部门代码、部门名、起始年月、终止年月就要重复多少次。
为了解决这个问题可将模式分解，分解后的关系模式为：
部门_A（部门代码，部门名，起始年月，终止年月）
部门_B（部门代码，办公室，办公电话）
（2）SELECT职员代码，职员名，年月，工作时间*小时工资AS月工资
FROM职员，职务，等级，月工作业绩
WHERE职员.职务代码=职务.职务代码AND
职务.等级代码=等级.等级代码AND
等级.年月=月工作业绩.年月AND
职员.职员代码=月工作业绩.职员代码；
升级会员 