软件设计师第7部分数据模式设计类
●试题1
阅读下列说明和E—R图，回答问题l至问题3，将解答填入答题纸的对应栏内。
【说明】
某网上玩具订购系统的E—R图(已消除了不必要的冗余)如图7．1所示(图中没有标出主码)。图中实体的说明见表7．1。相关属性说明见表7．2。
表7．1
| 实体 |
说明 |
|
Toys |
玩具店内的玩具 |
|
Customers |
与玩具店有业务的顾客 |
|
Orders |
顾客向玩具店下的订购单 |
表7．2
| 主要属性 |
说明 |
|
qty-in-stock |
玩具库存量 |
|
year_produced |
生产日期 |
|
ordernum |
订购单编号 |
|
cid |
顾客编号 |
|
tid |
惟一标识每种玩具的编码 |
|
cardnum |
顾客信用卡号码 |
|
orderdate |
填订购单日期 |
|
qty |
订购某种玩具的数量 |
|
ship_date |
发货日期 |
注：为简化起见，不考虑信用卡号码泄漏所带来的安全性等问题。
【问题1】
根据E—R图中给出的词汇，按照“关系模式名(属性，属性…．)”的格式，将此E—R图转换为4个关系模式，并指出每个关系模式中的主码和外码，其中模式名根据需要取实体名或联系名。
【问题2】
创建Customers表时，cid使用INTEGER数据类型，cname使用CHAR(80)数据类型，address使用CHAR(200)数据类型，cardnum使用CHAR(16)数据类型并且要求此列值惟一。请在下列用于创建表Customers的SQL语句空缺处填入正确的内容。
CREATE TABLE Customers(cid INTEGER NOT NULL，
cname CHAR(80)NOT NULL，
address CHAR(200)，
cardnum CHAR(16)NOT NULL，
(1)，
(2)
【问题3】
如下的SQL语句是玩具店用于查询“所有订购了tid为‘123—456’玩具的用户订购其他玩具
的情况”的不完整语句，请在空缺处填人正确的内容。
Select tid
From Orderlist A
Where not exists(Select*from Orders B
where A．ordemum=B．ordernum and B．cid(3)
(Select cid from Orderlist C，Orders D
where(4)．．tid=’123-456’
and(5)=D．ordemum)
【问题1】解答：
Toys、Customers、orders三个实体转换为3个关系模式，由于订购单和顾客之间是一对一关系，所以PlaceOrder不需要单独成为一个关系模式，而订购单和玩具之间是多对多关系，所以0rderList需要单独成为一个关系模式。于是得到：
(1)Customer(cid，ename，address，cardnum)主键为cid
(2)Orders(ordemm，orderdate，cid)主键为ordemum，外键为cid
(3)Toys(bid，name，producer，qty—in—stock，year_produced，price)主键为bid
(4)OrderList(bid，ordernum，qty，ship_date)主键为(bid，ordemum)，外键为bid、oMemum
【问题2】解答：
关系模式Customers的主键为cid，而cardnum列值唯一，因此应分别填入：
Primary Key cid和UNIQUE cardnum。
【问题3】解答：
最内层的SQL语句查找订购了123—456的客户cid，ordemum只出现在OrderList和order中，所以第(5)处填写C．ordemum，第(4)处填写C，题目要求查找这些客户还订购了那些玩具，所以(3)处填in。完整的SQL语句如下：
Select tid
From Orderlist A
Where not exists(Select * from Orders B
where A．ordemum=B．ordemum and B．cid in
(Select cid from Orderlist C，Orders D
where C．tid=’123—456’
and C．ordernum=D．ordemum)
●试题2
阅读下列说明和图，回答问题1至问题3，将解答填人答题纸的对应栏内。
【说明】
某连锁超市决定开发一个超市销售系统，由张工承担系统的设计工作。该系统的网络连接如图7．2所示。
该连锁超市有多个分店，图7．2所示的中心数据库存储了各个分店中每种商品的信息。每个分店配备一台前端机，进出商品均由前端机辅助实现。管理员每天上班时，通过前端机从中心数据库的库存表中读取本分店各种商品的库存数，每个分店的当日业务数据也都暂存在前端机，当天业务结束后，再将前端机中存储的数据传输到主机进行存储与汇总。
每个分店可以存放多种商品。每个分店有多个管理员，但每个管理员只管理一个分店。商品销售／A．店时，由分店管理员将商品的条码通过阅读器输入前端机中，商品数量的默认值为1，可以由管理员修改。前端机根据输入的商品信息，打印“销售／A．店”清单。销售／／k店单中同一种商品最多只出现一次，每份销售／入店单由流水号唯一标识。图7．3是一个销售单的实例。
该系统处理业务的过程如下：
1．初始化：前端机根据分店号从商品表中读取本分店中每种商品的商品编码、点存量、商品名称和单价。
2．登记销售／人店信息：由前端机存储每一笔“销售／入店”记录。
3．汇总：在每个工作日结束前汇总当日各种商品的“销售／人店”量至日汇总表； 4．更新库存表：根据当日的汇总信息更新商品的库存。
张工经过分析，设计出如图7．4所示的关系模式。
出入店单(流水号，出入店标志，管理员号，时间)
出入店记录(商品编码，数据，流水号)
日汇总表(日期，商品编码，数量，出入店标志)
分店(分店号，分店名，分店电话)管理员(管理号，姓名，分店号)商品((a))
注：时间格式为：年．月．日时：分：日期格式为：年．月．日。
实体联系图的表示方法如图7．5所示，其中方框表示实体，菱形表示联系，联系的类型在实体与联系的边上标出。图7．6为与该系统对应的实体联系图。
【问题1】
根据题意，补充图7．4中(a)处的空缺，即商品关系模式的属性。
【问题2】
根据题意，补充图7．6中缺失的联系和联系的类型，使其成为完善的实体联系图。其中，联系名分别取名为联系l，联系2，联系3，…。
【问题3】
写出每种关系模式的丰键．将其填写在答题纸的对应栏内。
【问题1】解答：
根据题意，补充商品关系模式如下：
商品(商品编号，名称，单价，数量，分店号)
【问题2】解答：
出入店单与出入店记录-e_N是一对多联系，日汇总表与出入店记录之间是一对多联系；连锁分店与日汇总表之间是一对一联系；分店与管理员之间是一对多联系；出入店记录与管理员之间是多对多联系；商品与出入店记录之间是一对多联系。图略。
【问题3】解答：
各关系模式的主键如下：
出入店单(流水号)
出入店记录(商品编码，流水号)
日汇总表(日期)
分店(分店号) 管理员(管理号)
商品(商品编码)
●试题3
阅读以下说明和表，回答问题l至问题4，将解答填入答题纸的对应栏内。
【说明】
某医院信息管理系统的需求分析和部分关系模式设计的结果描述如下：
(1)医院有多个部门，每个部门有一名负责人、一间办公室、一部电话、多名医生(护士)，每个医生(护士)最多属于一个部门，负责人也是一名医院医生(护士)。
(2)医院医生(护士)的月工资大于等于1000元且小于等于8000元。
(3)数据库的部分关系模式设计如下：
医护人员(编号，姓名，月工资，部门号，办公室，电话)
部门(部门号，部门名，负责人代码，任职时间)
(4)“医护人员”和“部门”的关系示例分别见表7．3和表7．4。
表7．3 “医护人员”关系
| 编号 |
姓名 |
月工资 |
部门号 |
办公室 |
电活 |
|
60801 |
汪俊华 |
1000 |
3 |
A座201 |
68831226 |
|
60802 |
杨晓军 |
3200 |
1 |
A座202 |
68831227 |
|
60803 |
王晓华 |
1200 |
2 |
B座202 |
68831256 |
|
60804 |
吕靖原 |
3200 |
4 |
A座301 |
6883 1258 |
|
60805 |
牟雪松 |
2800 |
3 |
B座302 |
68831266 |
|
60806 |
周黎 |
4300 |
2 |
A座202 |
6883 1227 |
|
60807 |
程文驰 |
0 |
Null |
| 部门号 |
部门名 |
负责人代码 |
任职时间 |
|
1 |
财务部 |
60802 |
2001—8—52 |
|
2 |
住院部 |
60803 |
2002—6—33 |
|
3 |
医药部 |
60805 |
2002—6—34 |
|
4 |
门诊部1 |
60810 |
2003—8—15 |
|
5 |
门诊部2 |
60821 |
2004—6—3 |
| 部门号 |
部门名 |
负责人代码 |
任职时间 |
|
1 |
财务部 |
60802 |
2001—8—52 |
|
2 |
住院部 |
60803 |
2002—6—33 |
|
3 |
医药部 |
60805 |
2002—6—34 |
|
4 |
门诊部1 |
60810 |
2003—8—15 |
|
5 |
门诊部2 |
60821 |
2004—6—3 |
| 部门号 |
部门名 |
负责人代码 |
任职时间 |
|
1 |
财务部 |
60802 |
2001—8—52 |
|
2 |
住院部 |
60803 |
2002—6—33 |
|
3 |
医药部 |
60805 |
2002—6—34 |
|
4 |
门诊部1 |
60810 |
2003—8—15 |
|
5 |
门诊部2 |
60821 |
2004—6—3 |
| 部门号 |
部门名 |
负责人代码 |
任职时间 |
|
1 |
财务部 |
60802 |
2001—8—52 |
|
2 |
住院部 |
60803 |
2002—6—33 |
|
3 |
医药部 |
60805 |
2002—6—34 |
|
4 |
门诊部1 |
60810 |
2003—8—15 |
|
5 |
门诊部2 |
60821 |
2004—6—3 |
根据上述说明，请给出
(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
| 60811 |
芦峰 |
800 |
1 |
A座201 |
68831226 |
|
60802 |
李晓啸 |
3500 |
2 |
B座202 |
68831236 |
|
60812 |
高亚南 |
2600 |
原来的“医护人员”关系模式存在什么问题?在不增加新关系模式的前提下，请给出修改后的“医护人员”和“部门”关系模式。
【问题1】解答：
(1)“医护人员”关系模式的主键和外键分别是编号和部门号。
(2)“部门”关系模式的主键是部门号或部门名，外键是负责人代码。
【问题2】解答：
编号是主键，所以(a)处填入PRIMARY KEY。部门号是外键，所以(b)处填入FOREIGN KEY(部门号)REFERENCES部门。
根据字段要求，(c)处填入Count(*)，Sum(月工资)，Avg(月工资)，(d)处填入GROUP BY部门号HAVING。以上答案中的单词不区分大小写。
【问题3】解答：
第一行不能插入，原因是他的工资小于1000。
第二行也不能插入，因为编号60802已经存在于表中。
第三行可以插入，尽管后三个属性为空，它表示该人员尚未分配部门。
【问题4】解答：
“医护人员”关系模式存在的问题有，①数据冗余，同一部门有多少员工，部门的办公司和电话就要重复记录多少次。②数据一致性，当某部门的办公司变化时，可能会导致某些人的属性修改了，而某些未修改。
将关系模式修改为：
医护人员(编号，姓名，月工资，部门号)
部门(部门号，部门名，负责人代码，任职时间，办公室，电话)
●试题4
阅读下列说明，回答问题1至问题4，将解答填人答题纸的对应栏内。
【说明】
某公司的采购业务目前是手工处理的，随着业务量的增长，准备采用关系数据库对进货信息进行管理。采购业务的手工处理主要涉及三种表：订单、客户表和原材料表(见表6～8)。
为了用计算机管理采购信息，该公司提出应达到以下要求：原材料的单价发生变化时，应及时修改原材料表中的单价数据。客户销售计价采用订货时的单价。订货后，即使单价发生变化，计算用的单价也不变。
在设计数据库时，经销部的王先生建立了如图7．7所示的数据模型。其中，方框表示实体，单向箭头表示1对多的联系，双向箭头表示多对多的联系。
由于上述模型对建立关系数据库是不合适的，因此王先生又修改了数据模型，并设计了如下几个关系(带下划线的数据项是关键项，最后一个关系中没有指出关键项)：
Customer(CustomerN0，CustomerName，Address，Phone)
Material(MaterialN0，MaterialName，UnitPrice)
Order(OrderN0，CustomerN0，Date)
OrderDetail(OrderN0，MaterialN0，Quantity)
【问题1】
请按【说明】中的要求画出修改后的数据模型。
【问题2】
(1)【说明】中的几个关系仍无法实现该公司的要求，为什么?
(2)需要在哪个关系中增加什么数据项才能实现这个要求?
【问题3】
写出0rderDetail中的关键项。
【问题4】
以下SQL语句用于查询不销售原材料代码为“01 10”的所有客户名。请填补空缺。
SELECT CustomerName FROM Customer(1)
WHERE(2)
(SELECT*FROM OrderDetail B,Order C
WHERE B．MaterialNo=C．MaterialNo
AND B．MaterialNo=‘0110’
AND C．CustomerNo=A．CustomerNo)
【问题1】解答：
客户与订单是一对多关系；订单和订单明细是一对多关系：原材料和订单明细是一对多关系。
【问题2】解答： 圈
(1)没有在OrderDetail表中记录产品的单价，也没有在Order表中记录订单总价，所以当单价发生变化时，计算用的单价就是变化后的单价了。
(2)在OrderDetail表中增加一个单价数据项或者在Order表中增加一个总金额数据项。
【问题3】解答：
表OrderDetail中的关键项为：OrderN0。MaterialNo
【问题4】解答：
(1)A或AS A
(2)NOT EXIST
■试题5
阅读下列说明和E～R图，回答问题1～3，把解答写在答卷的对应栏内。
【说明】
现有下列关于教务管理系统的E～R图。图中矩形表示实体，圆表示属性，双圆表示关键字属性，菱形表示实体问的联系。试用SQL语言定义教师(TEACHER)模式。回答时字段的数据类型以及题中未指明的名字由考生自己定义。为了答题的方便，图中的实体和属性同时给出了中英文两种名字，回答问题时只须写出英文名即可。
【问题1】
写出与上述E—R图对应的关系模式，并用下划线标明相应的关键字。
【问题2】
问题1中的关系模式属于第几范式?如果属于第三范式，则说明理由；如果不属于第三范式，则将它化为第三范式(回答时只须写出修改的部分)。
【问题3】
试用SQL语言定义教师(TEACHER)模式。回答时字段的数据类型以及题中未指明的名字由考生自己定义。
【问题1】解答：
下列模式中有下划线的项为关键字。 四个实体建立模式如下。
DEFF(DNAME，DHEAD，PHONE)
TEACHER(TN0，TNAME，SEX，AGE，TITLE)
STUDENT(SN0，SNAME，SEX，CLASS，MONITOR)
COURSE(CN0，CNAME，HOUR，ROOM)
两个一对多联系建立模式如下：
D—T(DNAME，TNO)
D—S(DNAME，SN0)
两个多对多模式建立关系如下：
TEACH(TN0。CN0。EVAL)
STUDY(SN0，CN0，GRADE)
【问题2】解答：
问题1中的模式不属于第三范式，可将STUDENT分解为如下模式以化成第三范式： STUDENT(SN0，SNAME．SEX，CLASS)
SCLASS(CLASS，MONITOR)
【问题3】解答：
用SQL语言定义教师(TEACHER)模式也就是用SQL语言在数据库中建立教师表：
CREATE TABLE TEACHER(TNO(CHAR(8)，NONULL)，
TNAME(CHAR(20))，SEX(CHAR(1))，AGE(1NTEGER)，TITLE(CHAR(20)))
●试题6
阅读下列说明和E—R图，回答问题，把解答写在答卷的对应栏内。
【说明】
下列E—R图是某学校关于学生成绩管理系统的。图中矩形表示实体，圆表示属性，双圆表示关键字属性，菱形表示实体问的联系。假定已通过下列SQL语言建立了基本表：?
CREATE TABLE sTUDENT?
(SNO CHAR(6)NOT NULL，
SNAME CHAR(20)．
DEPT CHAR(20)，
AGE SMALLINr)：
CREATE TABLE COURSE
(CNO CHAR(6)NOT NULL，
CNAME CHAR(20)．
HOUR SMALLINT)：
CREATE TABLE S—C
(SNO CHAR(6)
CNO CHAR(6)．
GRADE SMALIINT)：
为了答题的方便，图中的实体和属性同时给出了中英两种名字，回答问题时只须写出英文名即可。
【问题】
填充下列SQL程序2．1—2．4中的①一⑥，使它们分别完成以下查询功能：
程序2．1:索选读所有课程的学生姓名。
程序2．2：给出全体学生人数。
程序2．3：按学号给出每个学生的平均成绩。
程序2．4：按学号给出每个学生选读课程的门数。
【程序2．1】
SELECT STUDENT．SNAME
FROM STUDENT
WHERE[(1)]
(SELECT*
FROM COURSE
WHERE[(2)]
(SELECT*
FROM S—C
WHERE[(3)]))
【程序2．2】
SELECT[(4)]
FROM STUDENT
【程序2．3】
SELECT[(5)]
FROM S-C
GROUP BY SNO
【程序2．4】
SELECT[(6)]
FROM S—C
GROUPBY SNO
试题6答案：
(1)NOT EXIS
解析：选出不属于下面视图的学生表记录，即得到选读所有课程的学生姓名。
答案：(2)NOT EXISTS
解析：此处得到没有选全部课程的学生记录。
答案：(3)STUDENT．SNO=S—C．SNO AND COURSE．CNO=S—C．CNO
解析：将三个表进行连接的条件。
答案：(4)COUNT(*)
解析：使用COUNT关键字得出全体学生总数。
答案：(5)SN0，AVG(GRADE)
解析：使用关键字AVG和GROUPBY组合得到每个学号即学生的平均成绩。
答案：(6)SN0，COUNT(CNO)
解析：使用关键字COUNT和GROUPBY组合得到每个学号即学生的选课总数。
【问题1】
根据E—R图中给出的词汇，按照“关系模式名(属性，属性…．)”的格式，将此E—R图转换为4个关系模式，并指出每个关系模式中的主码和外码，其中模式名根据需要取实体名或联系名。
【问题2】
创建Customers表时，cid使用INTEGER数据类型，cname使用CHAR(80)数据类型，address使用CHAR(200)数据类型，cardnum使用CHAR(16)数据类型并且要求此列值惟一。请在下列用于创建表Customers的SQL语句空缺处填入正确的内容。
CREATE TABLE Customers(cid INTEGER NOT NULL，
cname CHAR(80)NOT NULL，
address CHAR(200)，
cardnum CHAR(16)NOT NULL，
(1)，
(2)
【问题3】
如下的SQL语句是玩具店用于查询“所有订购了tid为‘123—456’玩具的用户订购其他玩具
的情况”的不完整语句，请在空缺处填人正确的内容。
Select tid
From Orderlist A
Where not exists(Select*from Orders B
where A．ordemum=B．ordernum and B．cid(3)
(Select cid from Orderlist C，Orders D
where(4)．．tid=’123-456’
and(5)=D．ordemum)
【问题1】解答：
Toys、Customers、orders三个实体转换为3个关系模式，由于订购单和顾客之间是一对一关系，所以PlaceOrder不需要单独成为一个关系模式，而订购单和玩具之间是多对多关系，所以0rderList需要单独成为一个关系模式。于是得到：
(1)Customer(cid，ename，address，cardnum)主键为cid
(2)Orders(ordemm，orderdate，cid)主键为ordemum，外键为cid
(3)Toys(bid，name，producer，qty—in—stock，year_produced，price)主键为bid
(4)OrderList(bid，ordernum，qty，ship_date)主键为(bid，ordemum)，外键为bid、oMemum
【问题2】解答：
关系模式Customers的主键为cid，而cardnum列值唯一，因此应分别填入：
Primary Key cid和UNIQUE cardnum。
【问题3】解答：
最内层的SQL语句查找订购了123—456的客户cid，ordemum只出现在OrderList和order中，所以第(5)处填写C．ordemum，第(4)处填写C，题目要求查找这些客户还订购了那些玩具，所以(3)处填in。完整的SQL语句如下：
Select tid
From Orderlist A
Where not exists(Select * from Orders B
where A．ordemum=B．ordemum and B．cid in
(Select cid from Orderlist C，Orders D
where C．tid=’123—456’
and C．ordernum=D．ordemum)
/clip_image004.jpg)
●试题2
阅读下列说明和图，回答问题1至问题3，将解答填人答题纸的对应栏内。
【说明】
某连锁超市决定开发一个超市销售系统，由张工承担系统的设计工作。该系统的网络连接如图7．2所示。
该连锁超市有多个分店，图7．2所示的中心数据库存储了各个分店中每种商品的信息。每个分店配备一台前端机，进出商品均由前端机辅助实现。管理员每天上班时，通过前端机从中心数据库的库存表中读取本分店各种商品的库存数，每个分店的当日业务数据也都暂存在前端机，当天业务结束后，再将前端机中存储的数据传输到主机进行存储与汇总。
每个分店可以存放多种商品。每个分店有多个管理员，但每个管理员只管理一个分店。商品销售／A．店时，由分店管理员将商品的条码通过阅读器输入前端机中，商品数量的默认值为1，可以由管理员修改。前端机根据输入的商品信息，打印“销售／A．店”清单。销售／／k店单中同一种商品最多只出现一次，每份销售／入店单由流水号唯一标识。图7．3是一个销售单的实例。
/clip_image006.jpg)
该系统处理业务的过程如下：
1．初始化：前端机根据分店号从商品表中读取本分店中每种商品的商品编码、点存量、商品名称和单价。
2．登记销售／人店信息：由前端机存储每一笔“销售／入店”记录。
3．汇总：在每个工作日结束前汇总当日各种商品的“销售／人店”量至日汇总表； 4．更新库存表：根据当日的汇总信息更新商品的库存。
张工经过分析，设计出如图7．4所示的关系模式。
出入店单(流水号，出入店标志，管理员号，时间)
出入店记录(商品编码，数据，流水号)
日汇总表(日期，商品编码，数量，出入店标志)
分店(分店号，分店名，分店电话)管理员(管理号，姓名，分店号)商品((a))
注：时间格式为：年．月．日时：分：日期格式为：年．月．日。
/clip_image008.jpg)
实体联系图的表示方法如图7．5所示，其中方框表示实体，菱形表示联系，联系的类型在实体与联系的边上标出。图7．6为与该系统对应的实体联系图。
【问题1】
根据题意，补充图7．4中(a)处的空缺，即商品关系模式的属性。
【问题2】
根据题意，补充图7．6中缺失的联系和联系的类型，使其成为完善的实体联系图。其中，联系名分别取名为联系l，联系2，联系3，…。
【问题3】
写出每种关系模式的丰键．将其填写在答题纸的对应栏内。
/clip_image010.jpg)
【问题1】解答：
根据题意，补充商品关系模式如下：
商品(商品编号，名称，单价，数量，分店号)
【问题2】解答：
出入店单与出入店记录-e_N是一对多联系，日汇总表与出入店记录之间是一对多联系；连锁分店与日汇总表之间是一对一联系；分店与管理员之间是一对多联系；出入店记录与管理员之间是多对多联系；商品与出入店记录之间是一对多联系。图略。
【问题3】解答：
各关系模式的主键如下：
出入店单(流水号)
出入店记录(商品编码，流水号)
日汇总表(日期)
分店(分店号) 管理员(管理号)
商品(商品编码)
●试题3
阅读以下说明和表，回答问题l至问题4，将解答填入答题纸的对应栏内。
【说明】
某医院信息管理系统的需求分析和部分关系模式设计的结果描述如下：
(1)医院有多个部门，每个部门有一名负责人、一间办公室、一部电话、多名医生(护士)，每个医生(护士)最多属于一个部门，负责人也是一名医院医生(护士)。
(2)医院医生(护士)的月工资大于等于1000元且小于等于8000元。
(3)数据库的部分关系模式设计如下：
医护人员(编号，姓名，月工资，部门号，办公室，电话)
部门(部门号，部门名，负责人代码，任职时间)
(4)“医护人员”和“部门”的关系示例分别见表7．3和表7．4。
表7．3 “医护人员”关系
| 编号 |
姓名 |
月工资 |
部门号 |
办公室 |
电活 |
|
60801 |
汪俊华 |
1000 |
3 |
A座201 |
68831226 |
|
60802 |
杨晓军 |
3200 |
1 |
A座202 |
68831227 |
|
60803 |
王晓华 |
1200 |
2 |
B座202 |
68831256 |
|
60804 |
吕靖原 |
3200 |
4 |
A座301 |
6883 1258 |
|
60805 |
牟雪松 |
2800 |
3 |
B座302 |
68831266 |
|
60806 |
周黎 |
4300 |
2 |
A座202 |
6883 1227 |
|
60807 |
程文驰 |
0 |
Null |
| 部门号 |
部门名 |
负责人代码 |
任职时间 |
|
1 |
财务部 |
60802 |
2001—8—52 |
|
2 |
住院部 |
60803 |
2002—6—33 |
|
3 |
医药部 |
60805 |
2002—6—34 |
|
4 |
门诊部1 |
60810 |
2003—8—15 |
|
5 |
门诊部2 |
60821 |
2004—6—3 |
| 部门号 |
部门名 |
负责人代码 |
任职时间 |
|
1 |
财务部 |
60802 |
2001—8—52 |
|
2 |
住院部 |
60803 |
2002—6—33 |
|
3 |
医药部 |
60805 |
2002—6—34 |
|
4 |
门诊部1 |
60810 |
2003—8—15 |
|
5 |
门诊部2 |
60821 |
2004—6—3 |
| 部门号 |
部门名 |
负责人代码 |
任职时间 |
|
1 |
财务部 |
60802 |
2001—8—52 |
|
2 |
住院部 |
60803 |
2002—6—33 |
|
3 |
医药部 |
60805 |
2002—6—34 |
|
4 |
门诊部1 |
60810 |
2003—8—15 |
|
5 |
门诊部2 |
60821 |
2004—6—3 |
| 部门号 |
部门名 |
负责人代码 |
任职时间 |
|
1 |
财务部 |
60802 |
2001—8—52 |
|
2 |
住院部 |
60803 |
2002—6—33 |
|
3 |
医药部 |
60805 |
2002—6—34 |
|
4 |
门诊部1 |
60810 |
2003—8—15 |
|
5 |
门诊部2 |
60821 |
2004—6—3 |
根据上述说明，请给出
(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
| 60811 |
芦峰 |
800 |
1 |
A座201 |
68831226 |
|
60802 |
李晓啸 |
3500 |
2 |
B座202 |
68831236 |
|
60812 |
高亚南 |
2600 |
原来的“医护人员”关系模式存在什么问题?在不增加新关系模式的前提下，请给出修改后的“医护人员”和“部门”关系模式。
【问题1】解答：
(1)“医护人员”关系模式的主键和外键分别是编号和部门号。
(2)“部门”关系模式的主键是部门号或部门名，外键是负责人代码。
【问题2】解答：
编号是主键，所以(a)处填入PRIMARY KEY。部门号是外键，所以(b)处填入FOREIGN KEY(部门号)REFERENCES部门。
根据字段要求，(c)处填入Count(*)，Sum(月工资)，Avg(月工资)，(d)处填入GROUP BY部门号HAVING。以上答案中的单词不区分大小写。
【问题3】解答：
第一行不能插入，原因是他的工资小于1000。
第二行也不能插入，因为编号60802已经存在于表中。
第三行可以插入，尽管后三个属性为空，它表示该人员尚未分配部门。
【问题4】解答：
“医护人员”关系模式存在的问题有，①数据冗余，同一部门有多少员工，部门的办公司和电话就要重复记录多少次。②数据一致性，当某部门的办公司变化时，可能会导致某些人的属性修改了，而某些未修改。
将关系模式修改为：
医护人员(编号，姓名，月工资，部门号)
部门(部门号，部门名，负责人代码，任职时间，办公室，电话)
●试题4
阅读下列说明，回答问题1至问题4，将解答填人答题纸的对应栏内。
【说明】
某公司的采购业务目前是手工处理的，随着业务量的增长，准备采用关系数据库对进货信息进行管理。采购业务的手工处理主要涉及三种表：订单、客户表和原材料表(见表6～8)。
/clip_image012.jpg)
/clip_image014.jpg)
/clip_image016.jpg)
为了用计算机管理采购信息，该公司提出应达到以下要求：原材料的单价发生变化时，应及时修改原材料表中的单价数据。客户销售计价采用订货时的单价。订货后，即使单价发生变化，计算用的单价也不变。
在设计数据库时，经销部的王先生建立了如图7．7所示的数据模型。其中，方框表示实体，单向箭头表示1对多的联系，双向箭头表示多对多的联系。
/clip_image018.jpg)
由于上述模型对建立关系数据库是不合适的，因此王先生又修改了数据模型，并设计了如下几个关系(带下划线的数据项是关键项，最后一个关系中没有指出关键项)：
Customer(CustomerN0，CustomerName，Address，Phone)
Material(MaterialN0，MaterialName，UnitPrice)
Order(OrderN0，CustomerN0，Date)
OrderDetail(OrderN0，MaterialN0，Quantity)
【问题1】
请按【说明】中的要求画出修改后的数据模型。
【问题2】
(1)【说明】中的几个关系仍无法实现该公司的要求，为什么?
(2)需要在哪个关系中增加什么数据项才能实现这个要求?
【问题3】
写出0rderDetail中的关键项。
【问题4】
以下SQL语句用于查询不销售原材料代码为“01 10”的所有客户名。请填补空缺。
SELECT CustomerName FROM Customer(1)
WHERE(2)
(SELECT*FROM OrderDetail B,Order C
WHERE B．MaterialNo=C．MaterialNo
AND B．MaterialNo=‘0110’
AND C．CustomerNo=A．CustomerNo)
【问题1】解答：
客户与订单是一对多关系；订单和订单明细是一对多关系：原材料和订单明细是一对多关系。
/clip_image020.jpg)
【问题2】解答： 圈
(1)没有在OrderDetail表中记录产品的单价，也没有在Order表中记录订单总价，所以当单价发生变化时，计算用的单价就是变化后的单价了。
(2)在OrderDetail表中增加一个单价数据项或者在Order表中增加一个总金额数据项。
【问题3】解答：
表OrderDetail中的关键项为：OrderN0。MaterialNo
【问题4】解答：
(1)A或AS A
(2)NOT EXIST
■试题5
阅读下列说明和E～R图，回答问题1～3，把解答写在答卷的对应栏内。
【说明】
现有下列关于教务管理系统的E～R图。图中矩形表示实体，圆表示属性，双圆表示关键字属性，菱形表示实体问的联系。试用SQL语言定义教师(TEACHER)模式。回答时字段的数据类型以及题中未指明的名字由考生自己定义。为了答题的方便，图中的实体和属性同时给出了中英文两种名字，回答问题时只须写出英文名即可。
【问题1】
写出与上述E—R图对应的关系模式，并用下划线标明相应的关键字。
【问题2】
问题1中的关系模式属于第几范式?如果属于第三范式，则说明理由；如果不属于第三范式，则将它化为第三范式(回答时只须写出修改的部分)。
【问题3】
试用SQL语言定义教师(TEACHER)模式。回答时字段的数据类型以及题中未指明的名字由考生自己定义。
【问题1】解答：
下列模式中有下划线的项为关键字。 四个实体建立模式如下。
/clip_image022.jpg)
DEFF(DNAME，DHEAD，PHONE)
TEACHER(TN0，TNAME，SEX，AGE，TITLE)
STUDENT(SN0，SNAME，SEX，CLASS，MONITOR)
COURSE(CN0，CNAME，HOUR，ROOM)
两个一对多联系建立模式如下：
D—T(DNAME，TNO)
D—S(DNAME，SN0)
两个多对多模式建立关系如下：
TEACH(TN0。CN0。EVAL)
STUDY(SN0，CN0，GRADE)
【问题2】解答：
问题1中的模式不属于第三范式，可将STUDENT分解为如下模式以化成第三范式： STUDENT(SN0，SNAME．SEX，CLASS)
SCLASS(CLASS，MONITOR)
【问题3】解答：
用SQL语言定义教师(TEACHER)模式也就是用SQL语言在数据库中建立教师表：
CREATE TABLE TEACHER(TNO(CHAR(8)，NONULL)，
TNAME(CHAR(20))，SEX(CHAR(1))，AGE(1NTEGER)，TITLE(CHAR(20)))
●试题6
阅读下列说明和E—R图，回答问题，把解答写在答卷的对应栏内。
【说明】
下列E—R图是某学校关于学生成绩管理系统的。图中矩形表示实体，圆表示属性，双圆表示关键字属性，菱形表示实体问的联系。假定已通过下列SQL语言建立了基本表：?
CREATE TABLE sTUDENT?
(SNO CHAR(6)NOT NULL，
SNAME CHAR(20)．
DEPT CHAR(20)，
AGE SMALLINr)：
CREATE TABLE COURSE
(CNO CHAR(6)NOT NULL，
CNAME CHAR(20)．
HOUR SMALLINT)：
CREATE TABLE S—C
(SNO CHAR(6)
CNO CHAR(6)．
GRADE SMALIINT)：
为了答题的方便，图中的实体和属性同时给出了中英两种名字，回答问题时只须写出英文名即可。
/clip_image024.jpg)
【问题】
填充下列SQL程序2．1—2．4中的①一⑥，使它们分别完成以下查询功能：
程序2．1:索选读所有课程的学生姓名。
程序2．2：给出全体学生人数。
程序2．3：按学号给出每个学生的平均成绩。
程序2．4：按学号给出每个学生选读课程的门数。
【程序2．1】
SELECT STUDENT．SNAME
FROM STUDENT
WHERE[(1)]
(SELECT*
FROM COURSE
WHERE[(2)]
(SELECT*
FROM S—C
WHERE[(3)]))
【程序2．2】
SELECT[(4)]
FROM STUDENT
【程序2．3】
SELECT[(5)]
FROM S-C
GROUP BY SNO
【程序2．4】
SELECT[(6)]
FROM S—C
GROUPBY SNO
试题6答案：
(1)NOT EXIS
解析：选出不属于下面视图的学生表记录，即得到选读所有课程的学生姓名。
答案：(2)NOT EXISTS
解析：此处得到没有选全部课程的学生记录。
答案：(3)STUDENT．SNO=S—C．SNO AND COURSE．CNO=S—C．CNO
解析：将三个表进行连接的条件。
答案：(4)COUNT(*)
解析：使用COUNT关键字得出全体学生总数。
答案：(5)SN0，AVG(GRADE)
解析：使用关键字AVG和GROUPBY组合得到每个学号即学生的平均成绩。
答案：(6)SN0，COUNT(CNO)
解析：使用关键字COUNT和GROUPBY组合得到每个学号即学生的选课总数。