[模拟] 数据库系统工程师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和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)和(2)的次序可互换)
详细解答：
第3题：
[问题3]
  以下的SQL语句是书店用于查询“所有订购了bid为´123-456´图书的用户订购其他图书的情况”的不完整语句，请在空缺处填入正确的内容。
  Select bid
  From Orderlist  A
  Where not  exists  (  Select  *  from Orders B
  where  A.ordernum = B.ordernum and  B.cid  (3) 
  (Select eid from Orderlist C,  Orders D
  where  (4)  .bid = ´123-456´
  and  (5)  = D.ordernum))
参考答案：
foreign key references用法(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) FOREIGN KEY(部门号),REFERENCES部门   (c) Count(*),Sum(月工资).Avg(月工资)   (d) GROUP by 部门号 HAVING count(*)
详细解答：
第6题：
对于如表3-27和表3-28所示的“职员”和“部门”关系，请指出表3-29中(1)～(3)各行是否可以插入到“职员”关系中，为什么?
参考答案：
(1)该行不能插入“职员”关系，它违反了用户定义完整性中月工资的取值范围必须大于等于1
000元，小于等于8000元   (2) 该行不能插入“职员”关系，因为职员号“60802”在表3-27中已存在，违反了实体完整性中主键必须唯一区分关系中的每一个属性   (3) 该行可以插入“职员”关系，尽管部门号、电话和办公室为空，但是它表示该职员没有分配到某个部门
详细解答：
第7题：
原来的“职员”关系模式存在什么问题?在不增加新关系模式的前提下，请给出修改后的“职员”和“部门”关系模式。
参考答案：
“职员”关系模式主要的问题如下   数据冗余问题。因为某部门的职员人数有多少，其办公室和电话将要重复存入多少   数据修改不一致问题。因为某部门的办公室变了可能会导致某些职员的修改了，某些职员的未修改   将关系模式修改为：职员(职员号,职员姓名,月工资,部门号)   部门(部门号,部门名,负责人代码,任职时间,办公室,电话)
详细解答：
第8题：
请按[说明]中的要求画出修改后的数据模型。
参考答案：
如图3-4所示
详细解答：
第9题：
(1) [说明]中的几个关系仍无法实现甲公司的要求，为什么?
  (2) 需要在哪个关系中增加什么数据项才能实现这个要求?
参考答案：
(1)由于数据库中没有记录订货时产品的单价，也没有记录订货的总金额，因此一旦产品单
价发生变化，那么计算用的单价就是变化后的单价了   (2) 在OrderDetail中增加一个数据项：订货时的单价(或者在Order中增加一个数据项：总金额)
详细解答：
第10题：
请写出OrderDetail中的主键。
参考答案：
OrderNo,ProductNo
详细解答：
第11题：
以下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)
参考答案：
(1)A或ASA   (2) NOT EXIST
详细解答：
阅读以下技术说明，根据要求回答下列问题。