[ 模拟 ] 数据库系统工程师 SQL 操作与应用
论述设计题
阅读下列说明和 E-R 图，根据要求回答下列问题。
[ 说明 ]
某网上订书系统的 E-R 图（已消除了不必要的冗余 ）如图 3-1 所示（图中 没有标出主码 ） 。图中实体的说明如表 3-25 所示，相关属性说明如表 3-26 所示。
一个顾客可以在同一天填写多张购书单，每张购书单上可填写多种图书，每 种图书可以订购多本， bid 相同的图书在同一张购书单上不能出现多次。
注：为简化起见，不考虑信用卡号码泄漏所带来的安全性等问题。
第 1 题：
根据图 3-1 所示的 E-R图中给出的词汇，按照“关系模式名 （属性,属性⋯ ） ”的 格式，将此
E-R图转换为 4 个关系模式，并指出每个关系模式中的主码和外 码，其中模式名根据需要取实体名或联系名。
参考答案：
Customers(cid,cname,adderss,cardnum) ， 主 键 ： cid Orders(ordernum,orderdate,cid) ， 主 键 ： ordemum ， 外 键 ： cid Books(bid,title,author,qty_in_stock,year_publicshed,price) ，主键： bid Orderlist(bid,oNemum,qty,ship_date) ，其中 bid lift和 无锡新东方官网ordemum是主键，也是键 码 注：以上 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))
参考答案：
(1)PRIMARYKEY(cid) (2) UNIQUE(cardnum) ( 注： (1) ekbar和(2) 的次序可互 换)
详细解答：
第 3 题：
[ 问题 3]
以下的 SQL语句是书店用于查询“所有订购了 bid 为′123-456′图书的用户 订购其他图书的情况”的不完整语句，请在空缺处填入正确的内容。
Select bid
From Orderlist A
Where not exists ( Select * from Orders B
dernum = B.ordernum and B.cid (3)
(Select eid from Orderlist C, Orders D
where (4) .bid = ′123-456 ′
and (5) = D.ordernum))
参考答案：
(3)not in (4) C (5) C.ordemum
详细解答：
阅读以下说明和表，根据要求回答下列问题。
[ 说明 ] 某公司信息管理系统的需求分析和部分关系模式设计的结果描述如下。
(1)公司有多个部门，每个部门有一名负责人、一间办公室、一部电话 和多名职员，每个职员最多属于一个部门，负责人也是一名公司职员。
(2)公司职员的月工资大于等于 1000元且小于等于 8000 元。
(3)数据库的部分关系模式设计如下。 职员敬酒词(职员号,职员姓名 , 月工资, 金贤重官网部门号,办公室, 电话) 部门(部门号,部门名,负责人代码 ,任职时间)
(4)“职员”和“部门”的关系示例分别如表 3-27 和表 3-28 所示
第 4 题： 根据上述说明，请给出：
(1)“职员”关系模式的主键和外键。
(2)“部门”关系模式的主键和外键。
参考答案：
(1)主键：职员号 外键：部门号 (2) 主键：部门号，或部门名 外键： 负责人代码
详细解答：
第 5 题：
(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_Totals,D_AVgPay) As
( Select 部门号 , c.
from 职员
d. count(*) > =2 WHERE部 门号 IS NOT NULL);
参考答案：
(a)PRIMARYKEY (b) FOREIGNK EY(部门号 ),REFERENCE部S 门 (c)
Count(*),Sum( 月 工资 ).Avg( 月 工资 ) (d) GROUPb y 部门号 HAVING count(*)
详细解答：
第 6 题： 对于如表 3-27 和表 3-28 所示的“职员”和“部门”关系，请指出表 3-29 中
(1) ～ (3) 各行是否可以插入到“职员”关系中，为什么 ?
参考答案：
(1) the police该行不能插入 “职员”关系，它违反了用户定义完整性中月工资的取值范 围必须大于等于 1000 元，小于等于 8000 元 (2) 该行不能插入“职员”关 系，因为职员号“ 60802”在表 3-27 中已存在，违反了实体完整性中主键必须唯 一区分关系中的每一个属性 (3) 该行可以插入“职员”关系，尽管部门号、 电话和办公室为空，但是它表示该职员没有分配到某个部门
详细解答：
第 7 题：
原来的“职员”关系模式存在什么问题 ?在不增加新关系模式的前提下，请给出 修改后的“职员”和“部门”关系模式。
参考答案：
“职员”关系模式主要的问题如下 数据冗余问题。因为某部门的职员人 数有多少， 其办公室和电话将要重复存入多少 数据修改不一致问题。 因为某 部门的办公室变了可能会导致某些职员的修改了，某些职员的未修改 将关 系模式修改为：职员 (职员号,职员姓名 ,月工资, 部门号) 部门(部门号, 部门
名,负责人代码 ,任职时间,办公室,电话)
详细解答： 第 8 题：
请按［ 说明］ 中的要求画出修改后的数据模型 参考答案：
如图 3-4 所示
详细解答：
第 9 题：
(1) ［ 说明］ 中的几个关系仍无法实现甲公司的要求，为什么 ?
(2)需要在哪个关系中增加什么数据项才能实现这个要求 参考答案：
(1) 由于数据库中没有记录订货时产品的单价， 也没有记录订货的总金额， 因 此一旦产品单价发生变化，那么计算用的单价就是变化后的单价了 refu的名词(2) 在 OrderDetail 中增加一个数据项：订货时的单价 ( 或者在 Order 中增加一个数据 项：总金额 )
详细解答：
第 10 题：
请写出 OrderDetail 中的主键。
参考答案：
OrderNo,ProductNo
详细解答：
第 11 题：
以下 value是什么意思SQL语句用于查询没有订购产品代码为“ 1KIO”的产品的所有客户名，请 填补其中的空缺。
SELECT CustomerName FROM Customer (1)
WHERE (2)
(SELECT * FROM OrderDetail B,Order C
WHERE B. ProductNo = C. ProductNo
AND B. ProductNo = ′1KIO′
AND C. CustomerNo = A. CustomerNo)
