全国计算机技术与软件专业技术资格 ( 水平 ) 考试 2005 年上半年数据库系统工程师下午试卷 ( 考试时间 14:00~16:30 共 150 分钟 ) 请按下述要求正确填写答题纸 1. 在答题纸的指定位置填写你所在的省 自治区 直辖市 计划单列市的名称 2. 在答题纸的指定位置填写准考证号 出生年月日和姓名 3. 答题纸上除填写上述内容外只能写解答 4. 本试卷共 4 道题, 全部是必答题 其中, 试题一 15 分, 试题二至试题四每题 20 分, 满分 75 分 5. 解答时字迹务必清楚, 字迹不清时, 将不评分 6. 仿照下面例题, 将解答写在答题纸的对应栏内 例题 2005 年上半年全国计算机技术与软件专业技术资格 ( 水平 ) 考试日期是 (1) 月 (2) 日 因为正确的解答是 5 月 29 日, 故在答题纸的对应栏内写上 5 和 29 ( 参看下表 ) 例题 解答栏 (1) 5 (2) 29 2005 年上半年数据库系统工程师下午试卷第 1 页 ( 共 11 页 )
试题中通用的表示规则 关于概念数据模型 关系模式 关系数据库的表结构标记, 试题中若无特殊声明, 将使用如下的通用标记规则 1. 概念数据模型的标记规则分别如图 1 图 2 和图 3 所示 其中 : (1) 实体类型用长方形表示 长方形内为称 (2) 实体类型间的联系用菱形表示 在联系与实体间的连线上注明联系的类型 1 1 联系名 1 m 联系名 n 联系名 m 图 1 实体类型和联系的标记规则 (3) 超类型和子类型之间的联系, 从超类型到子类型之间画连线, 连线上加注小圆圈, 子类型加双线竖框 超类型名 子类型名 1 子类型名 2 图 2 超类型和子类型的标记规则 (4) 实体和联系的属性用椭圆表示, 并用直线将属性与其所属的实体或联系连接起来 联系名 属性名 1 属性名 2 属性名 1 属性名 2 图 3 实体类型及联系与其属性的标记规则 2005 年上半年数据库系统工程师下午试卷第 2 页 ( 共 11 页 )
2. 关系模式的标记规则如下 : 关系名 ( 属性名 1, 属性名 2,, 属性名 n) 其中 : (1) 若该属性仅为主键属性时, 则该属性名下画实下划线 ; (2) 若该属性仅是外键属性时, 则该属性名下画虚下划线 ; (3) 若该属性既是主键属性, 又是外键属性时, 则在该属性名下面画实下划线和虚下划线 ; (4) 若该属性既不是主键属性, 又不是外键属性时, 则在该属性名下不做标记 3. 关系数据库的表结构标记规则如下 : 表名列名 1 列名 2 列名 3 列名 n 其中 : (1) 若该列名仅为主键的列名时, 则该列名下画实下划线 ; (2) 若该列名仅是外键的列名时, 则该列名下画虚下划线 ; (3) 若该列名既是主键的列名, 又是外键的列名时, 则在该列名下面画实下划线和虚下划线 ; (4) 若该列名既不是主键的列名, 又不是外键的列名时, 则在该列名下不做标记 2005 年上半年数据库系统工程师下午试卷第 3 页 ( 共 11 页 )
试题一 (15 分 ) 阅读以下说明和数据流图, 回答问题 1 至问题 3, 将解答填入答题纸的对应栏内 说明 学生住宿服务系统帮助学生在就学的城市内找到所需的住房, 系统对出租的房屋信息 房主信息 需要租房的学生信息以及学生和房主的会面信息进行管理和维护 房主信息包括姓名 地址 电话号码以及系统分配的唯一身份标识 (ID) 和密码 ; 房屋信息包括房屋地址 类型 ( 单间 / 套间 ) 适合住宿的人数 房租 房主的 ID 以及现在是否可以出租 ( 例如由于装修原因, 需等到装修后才可出租或者房屋已被租出 ) 每当房屋信息发生变化时, 房主须通知系统, 系统将更新房屋文件以便学生能够获得准确的可租用房屋信息 房主向系统中加入可租用的房屋信息时, 需交纳一定的费用, 由系统自动给出费用信息 房主可随时更新房屋的各种属性 学生可通过系统查询现有的可租用的房屋, 但必须先在系统中注册 学生信息包括姓名 现住址 电话号码 出生日期 性别以及系统分配的唯一身份标识 (ID) 和密码 若学生希望租用某房屋, 则需要发出租房请求, 请求中包含房屋的详细信息, 系统将安排学生与房主会面的时间和地点, 并将会面信息通知学生和房主 会面信息包括会面时间 地点以及会面双方的基本信息, 系统将记录会面信息 学生住宿服务系统的顶层图如数据流图 1-1 所示 ; 学生住宿服务系统的第 0 层 DFD 图如数据流图 1-2 所示, 其中, 加工 3 的细化图如数据流图 1-3 所示 问题 1 (6 分 ) (1) 数据流图 1-1 缺少了一条数据流 ( 在图 1-2 中也未给出该数据流 ), 请给出此数据流的起点和终点, 并采用说明中的词汇给出此数据流名 (2) 数据流图 1-2 中缺少了与 查询房屋 加工相关的数据流, 请指出此数据流的起点和终点 问题 2 (4 分 ) 安排会面 加工除需要写入会面文件外, 还需要访问哪些文件? 问题 3 (5 分 ) 请补齐下列数据字典条目 : 登录信息 = 学生 ID + 密码注册信息 = 2005 年上半年数据库系统工程师下午试卷第 4 页 ( 共 11 页 )
[ 数据流图 1-1] 学生信息 房主 添加房屋信息 更新房屋信息 学生住宿服务系统 查看房屋请求可租用的房屋租房请求 学生 房主的会面通知 学生住宿服务顶层图 学生的会面通知 [ 数据流图 1-2] 添加的新房屋 1 添加房屋 新房主新房屋 房主文件 学生文件注册信息 房屋文件 3 身份验证 学生信息 更新房屋信息 2 修改房屋状态 房屋状态学生 ID 学生 ID 4 查看房屋请求 房主的会面通知 5 安排会面 查询房屋 可租用的房屋 会面信息 租房请求 会面文件 学生的会面通知 学生住宿服务第 0 层 DFD 图 [ 数据流图 1-3] 注册信息 学生 ID 学生 ID 3.1 注册 新学生标志 3.2 登录 登录信息 注册信息 注册信息 加工 3 的细化图 2005 年上半年数据库系统工程师下午试卷第 5 页 ( 共 11 页 )
试题二 (20 分 ) 阅读下列说明, 回答问题 1 至问题 5, 将解答填入答题纸的对应栏内 说明 某工厂的仓库管理数据库的部分关系模式如下所示 : 仓库 ( 仓库号, 面积, 负责人, 电话 ) 原材料 ( 编号, 名称, 数量, 储备量, 仓库号 ) 要求一种原材料只能存放在同一仓库中 仓库 和 原材料 的关系实例分别如表 2-1 和表 2-2 所示 表 2-1 仓库 关系 仓库号 面积 负责人 电话 01 500 李劲松 87654121 02 300 陈东明 87654122 03 300 郑爽 87654123 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 定义 原材料 和 仓库 的关系模式如下, 请在空缺处填入正确的内容 (4 分 ) CREATE TABLE 仓库 ( 仓库号 CHAR(4), 面积 INT, 负责人 CHAR(8), 电话 CHAR(8), (a) ) ; // 主键定义 CREATE TABLE 原材料 ( 编号 CHAR(4) (b), // 主键定义 名称 CHAR(16), 数量 INT, 储备量 INT, 仓库号 (c), (d) ) ; // 外键定义 2005 年上半年数据库系统工程师下午试卷第 6 页 ( 共 11 页 )
问题 2 将下面的 SQL 语句补充完整, 完成 查询存放原材料数量最多的仓库号 的功能 (3 分 ) SELECT 仓库号 FROM (e) (f) ; 问题 3 将下面的 SQL 语句补充完整, 完成 01 号仓库所存储的原材料信息只能由管 理员李劲松来维护, 而采购员李强能够查询所有原材料的库存信息 的功能 (5 分 ) CREATE VIEW raws_in_wh01 AS SELECT (g) FROM 原材料 WHERE 仓库号 = 01 ; GRANT (h) ON (i) TO 李劲松 ; GRANT (j) ON (k) TO 李强 ; 问题 4 仓库管理数据库的订购计划关系模式为: 订购计划 ( 原材料编号, 订购数量 ) 采用下面的触发器程序可以实现 当仓库中的任一原材料的数量小于其储备量时, 向订 购计划表中插入该原材料的订购记录, 其订购数量为储备量的三倍 的功能 请将该程 序的空缺部分补充完整 (3 分 ) CREATE TRIGGER ins_order_trigger AFTER (l) ON 原材料 REFERENCING NEW ROW AS nrow FOR EACH ROW WHEN nrow. 数量 < nrow. 储备量 INSERT INTO 订购计划 VALUES ( (m), (n) ); 问题 5 如果一种原材料可以在多个仓库中存放, 则问题 4 中的触发器程序存在什么问题, 如何修改?(5 分 ) 2005 年上半年数据库系统工程师下午试卷第 7 页 ( 共 11 页 )
试题三 (20 分 ) 阅读下列说明, 回答问题 1 至问题 5, 将解答填入答题纸的对应栏内 说明 某市人才交流中心为促进当地人力资源的合理配置, 加强当地企业与人才的沟通, 拟建立人才信息交流网 需求分析结果 1. 每个前来登记的个人需填写 人才入库登记表 ( 如表 3-1 所示 ), 并出示相关证件, 经工作人员审核后录入个人信息 2. 每个前来登记的企业需填写 企业信息登记表 ( 如表 3-2 所示 ), 并出示相关证明及复印件, 经工作人员核实后录入企业信息 3. 个人和企业的基本信息只需在第一次登记时填写, 个人编号和企业编号由系统自动生成 个人和企业的基本信息由电脑长期存储, 以后个人只需提供个人编号和求职意向信息, 企业只需提供企业编号和岗位需求信息 4. 个人的求职意向信息和企业的岗位需求信息在两个工作日内由工作人员录入数据库并发布 表 3-1 人才入库登记表 个人编号 : 登记日期 : 年月日姓名性别出生日期 求职 身份证号 毕业院校专业学历 证书 1. 名称 2. 联系电话 及薪 1. 意 水 2. 向 3. 个人简历及特长 : 职位名称 编号 电子邮件 最低薪水 照片 表 3-2 企业信息登记表 企业编号 : 登记日期 : 年月日企业名称地址企业网址 联系人联系电话电子邮件 岗位需求 职位专业学历薪水备注 企业简介 : 2005 年上半年数据库系统工程师下午试卷第 8 页 ( 共 11 页 )
概念模型设计 根据需求阶段收集的信息, 设计人才 岗位和企业的实体联系图 ( 不完整 ) 如图 3-1 所示 : 人才 企业 (a) 图 3-1 人才 岗位和企业的实体联系图 逻辑结构设计 1. 将概念模型设计的实体联系图转换为以下关系模式 : 人才 ( 个人编号, 姓名, 性别, 出生日期, 身份证号, 毕业院校, 专业, 学历, 证书名称, 证书编号, 联系电话, 电子邮件, 个人简历及特长 ) 企业 ( 企业编号, 企业名称, 联系人, 联系电话, 地址, 企业网址, 电子邮件, 企业简介 ) 求职意向 ( (b) ) 岗位需求 ( (c) ) 2. 由于一个人可能持有多个证书, 对 人才 关系模式进行优化, 得到如下两个新的关系模式 : 人才 ( (d) ) 证书 ( (e) ) 根据上述的设计过程, 回答以下问题 : 问题 1 在 (a) 处填入所需的实体 联系及其属性, 完成概念模型设计 (4 分 ) 问题 2 在 (b) (c) (d) (e) 处填入对应关系的属性, 完成逻辑结构设计 (4 分 ) 问题 3 对最终的各关系模式, 以下划线指出其主键和外键 (5 分 ) 问题 4 张工设计的实体联系图如图 3-2 所示, 请用 200 字以内的文字分析这样设计存在的问题 (4 分 ) 岗位 需求 企业 人才 图 3-2 张工设计的实体联系图 问题 5 如果允许企业通过互联网修改本企业的基本信息, 应对数据库的设计做何种修改? 请用 200 字以内的文字叙述实现方案 (3 分 ) 2005 年上半年数据库系统工程师下午试卷第 9 页 ( 共 11 页 )
试题四 (20 分 ) 阅读下面关于项目工作管理系统的数据库设计说明, 回答问题 1 至问题 3, 将解答填入答题纸的对应栏内 说明 E 软件开发公司, 决定开发设计本公司的项目工作管理系统, 由王先生承担数据库的设计工作 公司项目管理的需求分析如下 : 1. 组织机构 :E 公司有多个部门, 每个部门有多个职员 多个办公室, 每个办公室有一部电话 当部门变更时更换新的部门代码 职员辞职后, 若再次被聘用仍使用辞职前的代码 被聘用职员担任某职务, 职务用职务代码来标识 职务分为 : 工程师 高级工程师 经理助理 经理等 职员的工资根据等级区分, 共分为 S A B C D 五个等级 一个职务对应某个等级, 一个等级对应多个职务 职员月工资等于职员月工作时间 ( 小时 ) 乘以小时工资 职员的人事变动以及职位变更 ( 升级 降级 ) 在月初进行 2. 项目管理 : 项目用项目代码标识, 使用过的项目代码不能重复使用 一个部门可承担多个项目, 但一个项目仅由一个部门承担 一个项目有一名项目主管和多个职员 ; 一个职员可参加多个项目 项目代码由系统自动生成, 一旦项目建立, 项目名 部门代码以及起始年月日不能再变更 3. 项目的工作管理流程为 : 项目工作计划输入 ( 初始计划 ) 工作业绩输入 业绩生成 ( 每月一次 ) 计划修正 ( 每月一次 ) 1) 项目工作计划输入 : 项目主管使用如图 4-1 所示的工作计划输入界面, 输入项目代码 职员代码 职员参加某个项目的月工作时间 ( 计划 ) 图中白色部分为可输入项 项目代码 :1234567 项目名 : ABCDEFGH 部门代码 :9999 部门名 : 开发部起始年月日 :2005-04-01 结束年月日 :2005-9-31 登录时间 :2005-03-20 10 11 12 1 2 3 合职员代码职员名 4 月 5 月 6 月 7 月 8 月 9 月月月月月月月计 12345 张力军 150 150 150 150 150 150 900 23456 张小华 180 180 180 180 100 100 920 34567 卫平 100 100 180 180 180 180 920 图 4-1 工作计划输入界面 ( 初始计划 ) 2) 工作业绩输入 : 输入职员每天参加各个项目的工作时间 图 4-2 为工作业绩输入界面, 图中白色部分为可输入项 其中, 出勤时间由考勤系统管理, 指定项目代码的顺序可以不同, 并且一天可以输入多个项目代码, 但同一个项目代码不能重复输入 3) 业绩生成 : 月底汇总职员的当月工作业绩, 生成月工作业绩表 4) 计划修正 : 项目主管根据项目进度修改以后的工作计划 2005 年上半年数据库系统工程师下午试卷第 10 页 ( 共 11 页 )
职员代码 12345 职员名 : 张力军输入年月 2005 年 4 月 年月日 星 期 出勤 每个项目的工作时间 时间项目代码工作时间项目代码工作时间项目代码 2005-04-04 一 9.0 1234567 7.0 2345678 2.0 2005-04-05 二 8.0 1234567 7.0 2345678 1.0 2005-04-06 三 9.0 1234567 7.0 2345678 2.0 2005-04-07 四 10.0 1234567 8.0 2345678 1.0 3456789 2005-04-08 五 8.0 1234567 5.5 3456789 1.0 4567890 2005-04-09 六 0.0 2005-04-10 日 0.0 图 4-2 工作业绩输入界面 王先生根据公司的项目需求将数据库关系模式设计如下 : 部门 ( 部门代码, 部门名, 起始年月, 终止年月, 办公室, 办公电话 ) 职务 ( 职务代码, 职务名 ) 等级 ( 等级代码, 等级名, 年月, 小时工资 ) 职员 ( 职员代码, 职员名, 部门代码, 职务代码, 任职时间 ) 项目 ( 项目代码, 项目名, 部门代码, 起始年月日, 结束年月日, 项目主管 ) 工作计划 ( 项目代码, 职员代码, 年月, 工作时间 ) 问题 1 请使用 关系模式标记规则, 给出部门 等级 项目 工作计划关系模式的主键和外键, 以及基本函数依赖集 F1 F2 F3 和 F4 (9 分 ) 问题 2 请将下面关系模式中的(a) 和 (b) 处填入属性名称, 要求使用说明中已有的属性名称 (3 分 ) (1) 王先生设计的关系模式不能管理职务和等级之间的关系, 可以通过修改 职务 关系模式实现, 修改后的关系模式为 : 职务 ( (a) ) (2) 为了管理公司职员参加各项目每天的工作业绩, 需设计工作业绩关系模式为 : 工作业绩 ( (b) ) 问题 3 (1) 部门关系模式存在什么问题? 请用 100 字以内的文字阐述原因 为了解决这个问题可将关系模式分解, 分解后的关系模式的关系名依次取部门 _A 部门_B (4 分 ) (2) 假定月工作业绩关系模式为 : 月工作业绩 ( 职员代码, 年月, 工作时间 ), 请给出 查询职员代码 职员名 年月 月工资 的 SQL 语句 (4 分 ) 2005 年上半年数据库系统工程师下午试卷第 11 页 ( 共 11 页 )