中级数据库系统工程师2005上半年下午试题答案及详细解析.doc

《中级数据库系统工程师2005上半年下午试题答案及详细解析.doc》由会员分享，可在线阅读，更多相关《中级数据库系统工程师2005上半年下午试题答案及详细解析.doc（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、【精品文档】如有侵权，请联系网站删除，仅供学习与交流中级数据库系统工程师2005上半年下午试题答案及详细解析.精品文档.第14章 数据库系统工程师级下午试题分析与解答 试题一分析参见软件设计师。试题二阅读下列说明，回答问题1至问题5，将解答填入对应栏内。说明某工厂的仓库管理数据库的部分关系模式如下所示： 仓库(仓库号，面积，负责人，电话) 原材料(编号，名称，数量，储备量，仓库号) 要求一种原材料只能存放在同一仓库中。“仓库”和“原材料”的关系实例分别如表2-1和表2-2所示。 表2-1“仓库”关系 (表格)仓库号面积负责人电话仓库号面积负责人电话01500李劲松8765412103300郑爽

2、8765412302300陈东明8765412204400刘春来87654125 表2-2“原材料”关系 编号名称数量储备量仓库号1001小麦10050012001玉米5030011002大豆2010022002花生3050023001菜油602003【问题1】 根据上述说明，用SQL定义“原材料”和“仓库”的关系模式如下，请在空缺处填入正确的内容。 CREATE TABLE仓库(仓库号CHAR(4)， 面积 INT， 负责人 CHAR(8)， 电话 CHAR(8)， (a) )； /主键定义 CREATE TABLE 原材料(编号 CHAR(4) (b) ， /主键定义 名称 CHAR(16

3、)， 数量 INT, 储备量 INT， 仓库号 (c) ， (d) )； /外键定义【问题2】 将下面的SQL语句补充完整，完成“查询存放原材料数量最多的仓库号”的功能。 SELECT仓库号 FROM (e) (f) ；【问题3】 将下面的SQL语句补充完整，完成“01号仓库所存储的原材料信息只能由管理员李劲松米维护，而采购员李强能够查询所有原材料的库存信息”的功能。 CREATE VIEW raws_in_wh01 AS SELECT (g) FROM 原材料 WHERE仓库号=01； GRANT (h) ON (i) TO 李劲松； GRANT (j) ON (k) TO 李强；【问题4】

4、 仓库管理数据库的订购计划关系模式为：订购计划(原材料编号，订购数量)。采用下面的触发器程序可以实现“当仓库中的任一原材料的数量小于其储备量时，向订购计划表中插入该原材料的订购记录，其订购数量为储备量的三倍”的功能。请将该程序的空缺部分补充完整。 CREATE TRIGGER ins_order_trigger AFTER (1) ON 原材料 REFERENCING NEW ROWAS nrow FOR EACHROW WHEN nrow数量arow储备量 INSERT INTO 订购计划VALUES ( (m) ， (n) )；【问题5】 如果一种原材料可以在多个仓库中存放，则问题4中的触

5、发器程序存在什么问题，如何修改?试题二分析问题1 本问题考查应试者对SQL数据库定义语言的掌握。通过给出的关系模式及关系实例，完成数据库定义语句中的主键及外键的定义部分。主码的定义可以有3种：在列级约束中用NOT NULL UNIQUE或PRIMARY KEY指定；在表级约束中用PRIMARY KEY主键，的方式定义。前两种方法只适用于单一属性作主码的情况，后一种适用于任何情况。参照完整性的指定使用FOREIGN KEY外键REFERENCES被参照关系(被参照属性)的方式定义，参照关系的域应和被参照数据的域相同。 问题2 本题考查应试者对SQL查询语句的掌握。完成本查询的思路：根据原材料表，

6、按照“仓库号”分组，组内的记录对“数量”求和，分组求和得到的最大值对应的仓库号即为所求。参考答案中的子查询完成各仓库存储数量的求和，外部查询输出组内“数量”求和为最大值的仓库号。问题3 本问题考查应试者对SQL视图定义和授权语句的掌握。题干给出了通过视图和授权机制实现数据库安全性的方法：建立了01号仓库的视图，将对视图的更新权限赋给李劲松。题干同时要求对原材料的查询权限赋予李强，可以直接使用授权语句。授权的基本语法：GRANT 权限 ON 对象 TO用户名；问题4 本问题考查应试者对触发器程序编写的掌握。空(1)处要求填入的是触发操作 (INSERT、UPDATE或DELETE)，空(m)，(

7、n)处填入的是欲插入的值。本题的关键是读懂程序。问题5 本题存在问题：触发器程序判定某一原材料“数量”是否小于其存储量时，是按照当前记录的“数量”来判定的，当一种原材料存储在多个仓库时，这样判定是错误的，应根据该原材料在各仓库的存储总量判定。参考答案问题1 (a) PRIMARY KEY 仓库号 (b) PRIMARY KEY 或 NOT NULL UNIQUE 或NOT NULL PRIMARY KEY (c) CHAR (4) (d) FOREIGN KEY 仓库号REFERENCES 仓库(仓库号)问题2 (e)原材料 (f)GROUP BY 仓库号 HAVING SUM (数量)=AN

8、Y(SELECT SUM(数量) FROM 原材料 GROUP BY 仓库号)问题3 (g) *或编号，名称，数量，储备量，仓库号 (h) INSERT，DELETE，UPDATE (j) raws in wh01 (j) SELECT (k) 原材料问题4 (1) UPDATE，INSERT (m) nrow,编号 (n) nrow存储量*3问题5 存在问题：触发器程序判定某一原材料“数量”是否小于其存储量时，是按照当前记录的“数量”来判定的，当一种原材料存储在多个仓库时，这样判定是错误的，应根据该原材料在各仓库的存储总量判定。 应将触发器程序的WHEN 子句条件修改为： WHEN nrow

9、.储备量(SELECT SUM (数量) FROM 原材料 WHERE 编号=(SELECT 编号 FROM nrow) GROUP BY 编号)试题三说明 某市人才交流中心为促进当地人力资源的合理配置，加强当地企业与人才的沟通，拟建立人才信息交流网。需求分析结果 1每个前来登记的个人需填写人才入库登记表(如表31所示)，并出示相关证件，经工作人员审核后录入个人信息。 2每个前来登记的企业需填写企业信息登记表(如表3-2所示)，并出示相关证明及复印件，经工作人员核实后录入企业信息。 3个人和企业的基本信息只需在第一次登记时填写，个人编号和企业编号由系统自动生成。个人和企业的基本信息由电脑长期存

10、储，以后个人只需提供个人编号和求职意向信息，企业只需提供企业编号和岗位需求信息。 4个人的求职意向信息和企业的岗位需求信息在两个工作日内由工作人员录入数据库并发布。 表3-1 人才入库登记表个人编号：_ 登记日期：_年_月_日 表3-2 企业信息登记表企业编号：_ 登记日期：_年_月_日 概念模型设计 根据需求阶段收集的信息，设计人才、岗位和企业的实体联系图(不完整)如图3-1所示。图3-1 人才、岗位和企业的实体联系图逻辑结构设计 1将概念模型设计的实体联系图转换为以下关系模式：人才(个人编号，姓名，性别，出生日期，身份证号，毕业院校，专业，学历，证书名称，证书编号，联系电话，电子邮件，个人

11、简历及特长) 企业(企业编号，企业名称，联系人，联系电话，地址，企业网址，电子邮件，企业简介) 求职意向( (b) ) 岗位需求( (c) ) 2由于一个人可能持有多个证书，对“人才”关系模式进行优化，得到如下两个新的关系模式： 人才( (d) ) 证书( (e) )根据上述的设计过程，回答以下问题：问题1 在(a)处填入所需的实体、联系及其属性，完成概念模型设计。问题2 在(b)、(c)、(d)、(e)处填入对应关系的属性，完成逻辑结构设计。问题3 对最终的各关系模式，以下划线指出其主键和外键。问题4张工设计的实体联系图如图3-2所示，请用200字问题5 如果允许企业通过互联网修改本企业的基

12、本信息，应对数据库的设计做何种修改?请用200字以内的文字叙述实现方案。试题三分析【问题1】 本题考查应试者对概念模型设计和分析能力的掌握。按照题干的说明，应该建立人才、岗位和企业三个实体的联系，人才与岗位之间是求职意向联系，企业与岗位之间是岗位需求联系。按照所给定的登记表中的属性，组织两个联系的属性。实体、联系及属性的名称根据题目上出现的名称来填写。【问题2】 逻辑结构设计是建立在概念结构设计的基础上的，按照E-R图向关系模式的转换方法，将实体和联系分别转换为关系模式。本题中已给出了实体对应的关系模式，其中岗位实体没有属性，应归并到联系中。本题还要求对关系模式进行模式分解，以达到实体分离的优

13、化目的(也是规范化)。【问题3】 各关系模式的主键和外键可以根据关系模式的语义，并结合E-R向关系模式的转换方法来确定。【问题4】 此处的“需求”是“岗位”、“企业”和“人才”三个实体之间的联系，而事实上只有人才被聘用之后三者之间才产生联系。本系统解决的是人才的求职和企业的岗位需求，人才与企业之间没有直接的联系。【问题5】 建立企业的登录信息表，包含用户名和密码，记录企业的用户名和密码，将对本企业的基本信息的修改权限赋予企业的用户名，企业工作人员通过输入用户名和密码，经过服务器将其与登录信息表中记录的该企业的用户名和密码进行验证后，合法用户才有权限修改企业的信息。参考答案问题1 (a) 问题2

14、 (b)个人编号，岗位，最低薪水，登记日期 (c)企业编号，岗位，专业，学历，薪水，备注，登记日期 (d)个人编号，姓名，性别，出生日期，身份证号，毕业院校，专业，学历，证书名称，联系电话，电子邮件，个人简历及特长 (e)证书名称，证书编号问题3 企业(企业编号，企业名称，联系人，联系电话，地址，企业网址，电子邮件，企业简介) 求职意向(，最低薪水，登记日期) 岗位需求(，专业，学历，薪水，备注，登记日期) 人才(个人编号，姓名，性别，出生日期，身份证号，毕业院校，专业，学历， ，联系电话，电子邮件，个人简历及特长) 证书(证书名称，证书编号)问题4 此处的“需求”是“岗位”、“企业”和“人才

15、”三个实体之间的联系，而事实上只有人才被聘用之后三者之间才产生联系。本系统解决的是人才的求职和企业的岗位需求，人才与企业之间没有直接的联系。问题5 建立企业的登录信息表，包含用户名和密码，记录企业的用户名和密码，将对本企业的基本信息的修改权限赋予企业的用户名，企业工作人员通过输入用户名和密码，经过服务器将其与登录信息表中记录的该企业的用户名和密码进行验证后，合法用户才有权限修改企业的信息。试题四说明 E 软件开发公司，决定开发设计本公司的项目工作管理系统，由王先生承担数据库的设计工作。公司项目管理的需求分析如下。 1组织机构：E公司有多个部门，每个部门有多个职员、多个办公室，每个办公室有一部电

16、话。当部门变更时更换新的部门代码。职员辞职后，若再次被聘用仍使用辞职前的代码。被聘用职员担任某职务，职务用职务代码来标识。职务分为工程师、高级工程师、经理助理、经理等。职员的工资根据等级区分，共分为S、A、B、C、D五个等级。个职务对应某个等级，一个等级对应多个职务。职员月工资等于职员月工作时间(小时)乘以小时工资。职员的人事变动以及职位变更(升级、降级)在月初进行。 2项目管理：项目用项目代码标识，使用过的项目代码不能重复使用。一个部门可承担多个项目，但一个项目仅由一个部门承担。一个项目有一名项目主管和多名职员：一名职员可参加多个项目。项目代码由系统自动生成，一旦项目建立，项目名、部门代码以

17、及起始年月日不能再变更。 3项目的工作管理流程为：项目工作计划输入(初始计划)工作业绩输入业绩生成(每月一次)计划修正(每月一次)。 项目工作计划输入：项目主管使用如图4-1所示的工作计划输入界面，输入项目代码、职员代码、职员参加某个项目的月工作时间(计划)。图中白色部分为可输入项。图4-1 工作计划输入界面(初始计划) 工作业绩输入：输入职员每天参加各个项目的工作时间。图4-2为工作业绩输入界面，图中白色部分为可输入项。其中，出勤时间由考勤系统管理，指定项目代码的顺序可以不同，并且一天可以输入多个项目代码，但同一个项目代码不能重复输入。 业绩生成：月底汇总职员的当月工作业绩，生成月工作业绩表

18、。 计划修正：项目主管根据项目进度修改以后的工作计划。图4-2为工作业绩输入界面王先生根据公司的项目需求将数据库关系模式设计如下： 部门(部门代码，部门名，起始年月，终止年月，办公室，办公电话) 职务(职务代码，职务名) 等级(等级代码，等级名，年月，小时工资) 职员(职员代码，职员名，部门代码，职务代码，任职时间) 项目(项目代码，项目名，部门代码，起始年月口，结束年月日，项目主管) 工作计划(项目代码，职员代码，年月，工作时间) 【问题1】 请使用“关系模式标记规则”，给出部门、等级、项目、工作计划关系模式的主键和外键，以及基本函数依赖集F1、F2、F3和F4。【问题2】 请将下面关系模式

19、中的(a)和(b)处填入属性名称，要求使用说明中已有的属性名称。 (1)王先生设计的关系模式不能管理职务和等级之间的关系，可以通过修改“职务”关系模式实现，修改后的关系模式为： 职务( (a) ) (2)为了管理公司职员参加各项目每天的工作业绩，需设计工作业绩关系模式为： 工作业绩( (b) )【问题3】 (1)部门关系模式存在什么问题?请用100字以内的文字阐述原因。为了解决这个问题可将关系模式分解，分解后的关系模式的关系名依次取部门_A、部门_B、 (2)假定月工作业绩关系模式为：月工作业绩(职员代码，年月，工作时间)，请给出“杳询职员代码、职员名、年月、月工资”的SQL语句。试题四分析

20、试题四是关于数据库设计方面的题目。关系数据库设计的目标是生成一组合适的、性能良好的关系模式，以减少系统中信息存储的冗余度，但又可方便地获取信息。问题1 为了正确给出关系模式的主键和外键，需要正确地理解主键和外键的基本概念。首先看一下什么是候选码。 设K为R(U，F)中的属性的组合，若，且对于K的任何一个真子集K，都有K不能决定U，则K为R的候选码(候选关键字)，若有多个候选码，则选一个作为主码(主键)。 1)部门关系 根据题意在部门关系中，由于E公司每个部门多个办公室，部门代码多值决定办公室，如果仅用部门代码无法唯一区分部门关系中的每一个元组(记录)。但是，用(部门代码，办公室)作为主键，可以

21、唯一区分部门关系中的每一个元组，因此，正确答案如下： 部门(部门代码，部门名，起始年月，终止年月，办公室，办公电话) F1=部门代码(部门名，起始年月，终止年月)，部门代码办公室，办公室办公电话 2)等级关系 根据题意在等级关系中，由于一个职务对应某个等级， 一个等级对应多个职务。职员月工资等于职员月工作时间(小时)乘以小时工资。职员的人事变动以及职位变更(升级、降级)在月初进行。如果用等级代码无法唯一区分等级关系中的每一个元组，这是因为对于同一个等级在不同的时期小时工资不一定一样。例如，等级1在2004年1月小时工资为5元，到2004年10月小时工资为6元。可见用(等级代码，年月)作为主键，

22、可以唯一区分等级关系中的每一个元组。因此正确答案如下： 部门(部门代码，部门名，起始年月，终止年月，办公室，办公电话) 等级(等级代码，等级名，年月，小时工资) F2=等级代码等级名，(等级代码，年月)小时工资 3)项目关系 项目代码可以决定项目关系中的全属性，所以主键是项目代码。在项目关系中，部门代码是部门关系的主键，所以部门代码应是外键。项目主管应该来自职员，所以项目主管应是外键。因此正确答案如下： 项目(项目代码，项目名，起始年月日，结束年月日，) F3=项目代码(项目名，部门代码，起始年月日，结束年月日，项目主管) 4)工作计划 在工作计划关系中，由于一个项目有多个职员参加，因此仅用项

23、目代码无法唯一确定关系中的每一个元组。又由于工作计划是按月给职员安排的，所以，工作计划关系的主键是(项目代码，职员代码，年月)。因此正确答案如下： 工作计划(项目代码，职员代码，年月，工作时间) F4=(项目代码，职员代码，年月)工作时间问题2 (1)王先生设计的关系模式不能管理职务和等级之间的关系，为此可以在“职务”关系模式中增加属性等级代码实现，修改后的关系模式为： 职务(职务代码，职务名，等级代码) (2)为了管理公司职员参加各项目每天的工作业绩，需设计工作业绩关系模式，该模式中有一个属性“年月日”是表示。假设工作业绩的具体实例如下：目代码职员工码年月日工作日期项目代码职员代码年月日工作

24、日期1234567123452005-04-047.02345678123452005-04-042.01234567123452005-04-057.02345678123452005-04-051.01234567123452005-04-067.02345678123452005-04-062.01234567123452005-04-078.01234567123452005-04-085.5 可以根据该实例实现工作业绩输入界面。因此工作业绩关系模式如下： 工作业绩(项目代码，职员代码，年月日，工作时间)问题3 (1)部门关系模式存在的主要问题是数据冗余，因为部门关系模式属于2范式(或

25、 2NF)。例如，假设某个部门有10个办公室，部门代码、部门名、起始年月、终止年月就要重复10次。为了解决这个问题可将模式分解，分解后的关系模式为： 部门_ A(部门代码，部门名，起始年月，终止年月) 部门_ B(部门代码，办公室，办公电话) (2)假定月工作业绩关系模式为：月工作业绩(职员代码，年月，工作时间)，那么“查询职员代码、职员名、年月、月工资”的SQL语句如下； SELECT 职员代码，职员名，年月，工作时间*小时工资AS月工资 FROM 职员，职务，等级，月工作业绩 WHERE 职员.职务代码=职务.职务代码 AND 职务.等级代码；等级.等级代码AND 等级.年月=月工作业绩.

26、年月AND 职员.职员代码；月工作业绩.职员代码；参考答案问题1 部门(部门代码，部门名，起始年月，终止年月，办公室，办公电话) F1=部门代码(部门名，起始年月，终止年月)，部门代码办公室，办公室办公电话 注：部门代码(部门名，起始年月，终止年月)可写成(下同)： 部门代码部门名，部门代码起始年月，部门代码终止年月 等级(等级代码，等级名，年月，小时工资) F2=等级代码等级名，(等级代码，年月)小时工资 项目(项目代码，项目名，起始年月日，结束年月日，) F3=项目代码(项目名，部门代码，起始年月日，结束年月日，项目主管) 工作计划(项目代码，职员代码，年月，工作时间) F4=(项目代码，

27、职员代码，年月)工作时间问题2 (1)职务(职务代码，职务名，等级代码) (2)工作业绩(项目代码，职员代码，年月日，工作时间)问题3 (1)部门关系模式属于2范式(或2NF)，该关系模式存在冗余问题，因为某部门有多少个办公室，部门代码、部门名、起始年月、终止年月就要重复多少次。 为了解决这个问题可将模式分解，分解后的关系模式为： 部门_A (部门代码，部门名，起始年月，终止年月) 部门_B (部门代码，办公室，办公电话) (2)SELECT 职员代码，职员名，年月，工作时间*小时工资AS 月工资 FROM 职员，职务，等级，月工作业绩 WHERE 职员.职务代码=职务.职务代码 AND 职务.等级代码=等级.等级代码 AND 等级.年月=月工作业绩.年月 AND 职员.职员代码=月工作业绩.职员代码；