数据库设计与优化

试对下题中数据需求进行需求分析和方案设计，并采用关系规范化理论验证设计方案的合理性。

1．现有公务员报考管理系统，其中有若干个报考职位，其属性有职位代码、职位名称；有若干报考人员，其属性有准考证号、身份证号、姓名、年龄、性别、学历、单位名称、单位负责人、政治面貌，每个单位只有一个负责人；每个报考人员有若干工作经历，包括时间、地点、职务、证明人；每个报考人员参加考试后均有成绩，包括准考证号、笔试成绩、面试成绩。一个人可以报考多个职位；每个职位可以有多人报名参加考试；一个人报考一个职位就对应一个面试成绩和笔试成绩。

请根据上述需求，完成如下问题：

问题1：确定实体及实体型属性。（5分）

报考职位（职位代码，名称）

  报考人员（准考证号，身份证号，姓名，年龄，性别，学历，单位名称，单位负责人，政治面貌）

  工作经历（身份证号，姓名，时间，地点，职务，证明人）

成绩（准考证号，笔试成绩，面试成绩）

问题2：写出数据依赖，构造关系模式并写出候选码。（5分）

（准考证号，职位代码）→笔试成绩，面试成绩

（身份证号，时间）→地点，职务，证明人

身份证号→姓名

准考证号→身份证号，姓名，年龄，性别，学历，单位，单位负责人，政治面貌

身份证号→准考证号，姓名，年龄，性别，学历，单位，单位负责人，政治面貌

单位名称→单位负责人

职位代码→名称

关系模式

报考职位（职位代码，名称）候选码：职位代码

报考人员（准考证号，身份证号，姓名，年龄，性别，学历，单位名称，单位负责人，政治面貌）候选码：准考证号，身份证号

工作经历（身份证号，姓名，时间，地点，职务，证明人）候选码：（身份证号，时间）

报名成绩（准考证号，职位代码，笔试成绩，面试成绩）候选码：（准考证号，职位代码）

问题3：对关系模型进行优化，构造出符合3NF的关系模型，用下划线标识出主码。（5分）

符合3NF关系数据模型

报考职位（职位代码，名称）

报考人员（准考证号，身份证号，姓名，年龄，性别，学历，单位名称，政治面貌）

单位（单位，单位负责人）

工作经历（身份证号，姓名，时间，地点，职务，证明人）

报名成绩（准考证号，职位代码，笔试成绩，面试成绩）

2．现有一个记录各个球队队员每场比赛进球数的关系模式：

R（队员编号，比赛场次，进球数，球队名，队长名）

如果规定，每个队员只能属于一个球队，每个球队只有一个队长。

请根据上述需求，完成如下问题：

问题1：写出关系模式R的基本函数依赖和主码。（5分）

关系模式R的基本函数依赖F如下

F={队员编号→球队名，球队名→队长名，

    （队员编号，比赛场次）→进球数}

其主键为（队员编号，比赛场次）。

问题2：说明R不是2NF模式的理由，并把R分解成2NF。（5分）

R不是2NF模式的原因是队员编号→球队名，所以（队员编号，比赛场次）→球队名是一个部分函数依赖关系，将R分解成2NF如下：

R1={队员编号，球队名，队长名}

R2={球队名，比赛场次，进球数}

问题3：进而将R分解成3NF，并说明理由。（5分）

由于在R1中，主键为队员编号，所以队员编号→队长名是一个传递函数依赖，将R分解成：

 R11={队员编号，球队名}，R12={球队名，队长名}

 则将R分解为R11，R12，R2后均为3NF的关系模式。

三、数据库设计与优化（每小题15分，共30分）

试对下题中数据需求进行需求分析和方案设计，并采用关系规范化理论验证设计方案的合理性。

1. 设有如下所示的关系R

职工号	职工名	年龄	性别	部门号	部门名
E1	张玉萍	26	女	D3	财务部
E2	于志强	25	男	D2	开发部
E3	王勇乐	38	男	D1	人事部
E4	高欣欣	25	女	D2	开发部

请根据上述需求，完成如下问题：

问题1：关系R属于第几范式？请说明理由。（2分）

关系R是2NF

问题2：关系R是否存在删除异常？若存在，请说明在什么情况下发生？（5分）

当插入一个新部门，而该部门还没有员工时，不能加入，从而发生插入异常；

当某部门只有一名员工，删除该员工时会把该部门的信息也删除，从而发生删除异常。

原因在于非主属性“部门名称”对码“职工号”存在传递函数依赖。

问题3：将关系R分解为高一级范式。（4分）

职工（职工号，职工名，年龄，性别，部门号）

部门（部门号，部门名称）

问题4：分解后的关系如何解决分解前可能存在的删除操作异常问题？（4分）

当增加一个新部门，而该部门还没有员工时，仍然可以向部门关系中加入该部门信息，从而解决了插入异常问题。

当某部门只有一名员工，删除该员工时，直接删除职工关系中的该行信息即可。 不再会把该部门的信息也删除，从而解决了删除异常的问题。

2．现有一个记录读者借阅图书的关系模式：

R（图书编号，书名，作者名，出版社，读者编号，读者姓名，借阅日期，归还日期）。其中规定：图书编号唯一，读者编号唯一，读者一次最多能够借阅5本书，读者在归还某一本书后还可以其他时间再次借阅。

请根据上述需求，完成如下问题：

问题1：写出关系模式R的基本函数依赖和主码。（5分）

关系模式R的基本函数依赖F如下

F={图书编号→书名，图书编号→作者名，图书编号→出版社，

读者编号→读者姓名，（图书编号，读者编号）→借阅日期，

（图书编号，读者编号）→归还日期，}

其主键为（图书编号，读者编号）。

问题2：说明R不属于2NF的原因。（4分）

非主属性书名、作者名、出版社部分函数依赖主码图书编号（2分），非主属性读者姓名部分函数依赖主码读者编号（2分），所以R不属于2NF。

问题3：进而将R分解成2NF。（6分）

R1（图书编号，书名，作者名，出版社）

R2 （读者编号，读者姓名）

R3（图书编号，读者编号，借阅日期，归还日期）