软件设计师第7部分数据模式设计类

　　●试题3

　　阅读以下说明和表，回答问题l至问题4，将解答填入答题纸的对应栏内。

　　【说明】

　　某医院信息管理系统的需求分析和部分关系模式设计的结果描述如下：

　　(1)医院有多个部门，每个部门有一名负责人、一间办公室、一部电话、多名医生(护士)，每个医生(护士)最多属于一个部门，负责人也是一名医院医生(护士)。

　　(2)医院医生(护士)的月工资大于等于1000元且小于等于8000元。

　　(3)数据库的部分关系模式设计如下：

　　医护人员(编号，姓名，月工资，部门号，办公室，电话)

　　部门(部门号，部门名，负责人代码，任职时间)

　　(4)“医护人员”和“部门”的关系示例分别见表7.3和表7.4。

　　表7.3 “医护人员”关系
　　

　　【问题1】

　　根据上述说明，请给出

　　(1)“医护人员”关系模式的主键和外键。

　　(2)“部门”关系模式的主键和外键。

　　【问题2】

　　(1)用SQL定义“医护人员”关系模式，请在空缺处填入正确的内容。

　　Create Table“医护人员”(编号CHAR(5)(a)，

　　姓名CHAR(8)，

　　月工资NUMBER(4)，

　　部门号CHAR(1)，

　　办公室CHAR(20)，

　　电话CHAR(8)，

　　(b)(部门号)，

　　CHECK(月工资>=1000 AND月工资<=8000));

　　(2)针对人数大于等于2的部门创建视图D—View(Dept，D—num，D—Totals，D—AvgPay)，其中， Dept为部门号，D—num为部门人数，D_Totals为工资总数，D—AvgPay为平均工资，请在空缺处填入正确的内容。

　　Create View D—View(Dept，D—num，D_TotMs，D—AvgPay)As

　　(Select部门号，(C)

　　from医护人员

　　(d)count(*)>=2 WHERE部门号IS NOT NULL);

　　【问题3】

　　对于表7.3、表7.4所示的“医护人员”和“部门”关系，请指出下列各行是否可以插入“医护人员”关系，为什么?

　　表7.5　　

　　原来的“医护人员”关系模式存在什么问题?在不增加新关系模式的前提下，请给出修改后的“医护人员”和“部门”关系模式。

　　【问题1】解答：

　　(1)“医护人员”关系模式的主键和外键分别是编号和部门号。

　　(2)“部门”关系模式的主键是部门号或部门名，外键是负责人代码。

　　【问题2】解答：

　　编号是主键，所以(a)处填入PRIMARY KEY。部门号是外键，所以(b)处填入FOREIGN KEY(部门号)REFERENCES部门。

　　根据字段要求，(c)处填入Count(*)，Sum(月工资)，Avg(月工资)，(d)处填入GROUP BY部门号HAVING。以上答案中的单词不区分大小写。

　　【问题3】解答：

　　第一行不能插入，原因是他的工资小于1000。

　　第二行也不能插入，因为编号60802已经存在于表中。

　　第三行可以插入，尽管后三个属性为空，它表示该人员尚未分配部门。

　　【问题4】解答：

　　“医护人员”关系模式存在的问题有，①数据冗余，同一部门有多少员工，部门的办公司和电话就要重复记录多少次。②数据一致性，当某部门的办公司变化时，可能会导致某些人的属性修改了，而某些未修改。

　　将关系模式修改为：

　　医护人员(编号，姓名，月工资，部门号)

　　部门(部门号，部门名，负责人代码，任职时间，办公室，电话)