【计算机类职业资格】软件设计师-数据库设计及答案解析.doc

1、软件设计师-数据库设计及答案解析(总分：75.00，做题时间：90 分钟)一、B试题一/B(总题数：3，分数：15.00)1.【问题 1】 根据以上说明设计的实体联系图如图 2-1所示，请指出读者与图书、书目与读者、书目与图书之间的联系类型。 （分数：4.00）_2.【问题 2】 该图书管理系统的主要关系模式如下，请补充“借还记录”和“预约登记”关系中的空缺。管理员(工号，姓名) 读者(读者 ID，姓名，电话，E-mail) 书目(ISBN 号，书名，作者，出版商，出版年月，册数，经办人) 图书(图书 ID，ISBN 号，存放位置，状态，经办人) 借还记录(U (a) /U，借出时间，应还时间

2、，归还时间) 预约登记(U (b) /U，预约时间，预约期限，图书 ID) 注：时间格式为“年.月.日 时:分:秒”。（分数：4.00）_3.【问题 3】 请指出问题 2中给出的读者、书目关系模式的主键，以及图书、借还记录和预约登记关系模式的主键和外键。（分数：7.00）_二、B试题二/B(总题数：3，分数：15.00)4.【问题 1】 根据题意，补充关系模式中(a)处的空缺，即货物关系模式的属性。（分数：3.00）_5.【问题 2】 根据题意，补充图 2-5中缺失的联系和联系的类型，使其成为完善的实体联系图。其中，联系名分别取名为联系 1、联系 2、联系 3（分数：6.00）_6.【问题 3

3、】 写出每种关系模式的主键，将其填写在对应栏内。（分数：6.00）_三、B试题三/B(总题数：4，分数：15.00)7.【问题 1】 根据上述说明，请给出： (1)“职员”关系模式的主键和外键。 (2)“部门”关系模式的主键和外键。（分数：4.00）_8.【问题 2】 (1)用 SQL定义“职员”关系模式，请在空缺处填入正确的内容。 Create Table 职员(职员号 CHAR(5)U (a) /U， 职员姓名 CHAR(8)， 月工资 NUMBER(4)， 部门号 CHAR(1)， 办公室 CHAR(20)， 电话 CHAR(8)， U (b) /U (部门号)， CHECK(月工资=1

4、000 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 部门号，U (c) /U from 职员 U (d) /U count(*)=2 WHERE 部门号 IS NOT NULL)：（分数：4.00）_9.【问题 3】对于如表 2-3、表 2-4

5、所示的“职员”和“部门”关系，请指出下列各行是否可以插入“职员”关系，为什么? 1 60811 芦 峰 800 1 A座 201 68831222 60802 李晓啸 3500 2 B座 202 68831233 60812 高亚南 2600（分数：3.00）_10.【问题 4】 原来的“职员”关系模式存在什么问题?在不增加新关系模式的前提下，请给出修改后的“职员”和“部门”关系模式。（分数：4.00）_四、B试题四/B(总题数：3，分数：15.00)11.【问题 1】 根据 E-R图中给出的词汇，按照“有关模式名(属性，属性，.)”的格式，将此 E-R图转换为 4个关系模式，并指出每个关系模

6、式中的主键和外键，其中模式名根据需要取实体名或联系名。（分数：5.00）_12.【问题 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)， eardnum CHAR(

7、16) NOT NULL， U(1) /U， U(2) /U)（分数：5.00）_13.【问题 3】 如下的 SQL语句是书店用于查询“所有订购了 bid为123-456图书的用户订购其他图书的情况”的不完整语句，请在空缺处填入正确的内容。 Select bid From orderlist A Where not exists (Select * from Orders B where A.ordemum=B.ordemum and B.cid U(3) /U (Select cid from orderlist C,orders D where U(4) /U.bid=123-456 an

8、d U(5) /U=D.ordemum)（分数：5.00）_五、B试题五/B(总题数：4，分数：15.00)14.【问题 1】 请按说明中的要求画出修改后的数据模型。（分数：3.00）_15.【问题 2】 (1)说明中的几个关系仍无法实现甲公司的要求，为什么? (2)需要在哪个关系中增加什么数据项才能实现这个要求?（分数：4.00）_16.【问题 3】 写出 OrderDetail中的关键项。（分数：4.00）_17.【问题 4】 以下 SQL语句用于查询没有订购产品代码为“1K10”的产品的所有客户名。请填补其中的空缺。 SELECT CustomerName FROM Customer U

9、(1) /U WHERE U(2) /U (SELECT * FROM OrderDetml B，Order C WHERE B.ProductNo=C.ProductNo AND B.ProductNo=1KIO AND C.CustomerNo=A.CustomerNo)（分数：4.00）_软件设计师-数据库设计答案解析(总分：75.00，做题时间：90 分钟)一、B试题一/B(总题数：3，分数：15.00)1.【问题 1】 根据以上说明设计的实体联系图如图 2-1所示，请指出读者与图书、书目与读者、书目与图书之间的联系类型。 （分数：4.00）_正确答案：()解析：(1) n (2) m

10、 (3) 1 (4) n 或 m (5) m (6) n 其中，(1)和(2)的答案可互换，(5)和(6)答案可互换。2.【问题 2】 该图书管理系统的主要关系模式如下，请补充“借还记录”和“预约登记”关系中的空缺。管理员(工号，姓名) 读者(读者 ID，姓名，电话，E-mail) 书目(ISBN 号，书名，作者，出版商，出版年月，册数，经办人) 图书(图书 ID，ISBN 号，存放位置，状态，经办人) 借还记录(U (a) /U，借出时间，应还时间，归还时间) 预约登记(U (b) /U，预约时间，预约期限，图书 ID) 注：时间格式为“年.月.日 时:分:秒”。（分数：4.00）_正确答案

11、：()解析：(a)读者 ID，图书 ID。 (b)读者 ID，ISBN 号。3.【问题 3】 请指出问题 2中给出的读者、书目关系模式的主键，以及图书、借还记录和预约登记关系模式的主键和外键。（分数：7.00）_正确答案：()解析：分析问题 1是考查 E-R图中的联系类型的识别，两个不同的实体集间的联系类型有：一对一、一对多和多对多。一个读者最多可以借阅 10本书，显然一本书可以被多个读者借阅(当然不可能是同肘)，因此“读者”与“图书”之间是多对多联系，即空(1)应为 n，空(2)应为 m。资料室中，每部书有唯一的一条图书在版编目数据(CIP，以下简称书目)，同一书目的图书可拥有多册(本)，关

12、系模式 主键 外键读者 读者 ID书目 ISDN号图书 图书 ID ISBN号借还记录 读者 ID、图书 ID、借出时间 读者 ID、图书 ID预约登记 读者 ID、ISBN 号、预约时间 读者 ID、ISBN 号、ID 图书因此“书目”与“图书”之间是一对多联系，即空(3)应为 1。空(4)应为 n。一个读者可以预约多部书目，一部书目显然可被多名读者预约，因此“读者”与“书目”之间是多对多联系，即空(5)应为 n，空(6)应为 m。问题 2是关系模式的属性识别。根据说明，“读者借书时需由图书管理员登记读者 ID、所借图书 ID、借阅时间和应还时间，读者还书时图书管理员在对应的借书信息中记录归

13、还时间”，因此“借还记录”关系中应有属性：读者 ID、图书 ID、借出时间、应还时间、归还时间。故空(a)应填“读者 ID，图书 ID”。根据说明，“预约登记时需要记录读者 ID、书的 ISBN号、预约时间和预约期限”，因此“预约登记”关系中应有属性：读者 ID、ISBN 号、预约时间、预约期限。故空(b)应填“读者 ID、ISBN 号”。关系的主键是指能唯一标识记录的属性或属性组，外键是指非该关系的主键但是其他关系的主键。根据题中说明，“系统为不同读者生成不同的读者 ID”，即“读者”关系的属性“读者 ID”是唯一的，故“读者”关系的主键为“读者 ID”，无外键。“不同书目的 ISBN号不相

14、同”，因此“书目”关系的主键为“ISBN号”，无外键。“每一本书在系统中被赋予唯一的图书 ID”，因此“图书”关系的主键为“图书ID”，外键为“ISBN 号”。“借还记录”和“预约登记”的主键比较难，“借还记录”的主键显然应该包含属性“读者 ID”和“图书 ID”，考虑到一个读者可能在不同的时间借阅同一本图书，因此“借还记录”的主键应为“(读者 ID，图书 ID，借出时间)”，外键有：读者 ID，图书 ID。类似地，“预约登记”的主键显然应该包含属性“读者 ID”和“ISBN 号”，考虑到一个读者可能在不同的时间预约同一部图书，因此“预约登记”的主键应为“(读者 ID，ISBN 号，预约时间)

15、”，外键有：读者 ID、ISBN 号和图书 ID。二、B试题二/B(总题数：3，分数：15.00)4.【问题 1】 根据题意，补充关系模式中(a)处的空缺，即货物关系模式的属性。（分数：3.00）_正确答案：()解析：货物编码，货物名称，单价，仓库号，库存量5.【问题 2】 根据题意，补充图 2-5中缺失的联系和联系的类型，使其成为完善的实体联系图。其中，联系名分别取名为联系 1、联系 2、联系 3（分数：6.00）_正确答案：()解析：见图 2-6。 6.【问题 3】 写出每种关系模式的主键，将其填写在对应栏内。（分数：6.00）_正确答案：()解析：出入库单的主键：流水号。 出入库记录的主

16、键：(流水号，货物编码)。 日汇总表的主键：(日期，货物编码)。 仓库的主键：仓库号。 管理员的主键：管理员号。 货物的主键：货物编码。 分析 问题1是考查关系的属性识别，根据如图 2-3所示的出库单，货物应由属性：货物编码、货物名称、单价，再结合“初始化”工作：“前端机根据仓库号从货物表中读取本仓库中每种货物的货物编码、库存量、货物名称和单价”，可得货物关系还应包含属性：仓库号、库存量。故关系模式中空(a)应填“货物编码，货物名称，单价，仓库号，库存量”。 问题 2是考查 E-R图中实体间联系类型的识别，联系的类型有：一对一、一对多和多对多。注意，只需考虑属性存在交叉的实体间的联系类型。 根

17、据说明“每个仓库可以存放多种货物，但同一种货物不能存放在不同的仓库中”可得“仓库”与“货物”是一对多联系。根据说明“每个仓库有多个管理员，但每个管理员只管理一个仓库”可得“仓库”与“管理员”是一对多联系。 一份出入库单由一位管理员签字，一个管理员自然可以签多份出入库单，因此“管理员”与“出入库单”是一对多联系。 根据说明“每份出库/入库单由流水号唯一标识”，“出入库记录”关系中包含属性“流水号”，可知一条出入库记录对应一个特定的出入库单，再结合如图 2-3所示的出入库单示例，一个出入库单包含多条出入库记录，因此“出入库单”与“出入库记录”是一对多联系。 类似地，一条出入库记录对应特定的货物，一

18、种货物显然可能存在多条出入库记录，因此“货物”与“出入库记录”是一对多联系。 日汇总表是汇总每个工作日的各种货物的出入库记录，因此一条出入库记录对应于特定的一个日汇总表中的一条记录，一个日汇总表白然是多条出入库记录的汇总，故“日汇总表”与“出入库记录”是一对多联系。 问题 3是考查关系模式中各关系的主键的识别。“每份出库从库单由流水号唯一标识”，因此“出入库单”关系的主键为“流水号”。“出库/入库单中同一种货物最多只出现一次”，出入库单由流水号标识，货物由货物编号标识，因此“出入库记录”关系的主键为“(流水号，货物编号)”。“日汇总表”关系的主键应该包含属性“日期”和“货物编号”，“仓库”关系

19、的主键自然是属性“仓库号”，“管理员”关系的主键应为属性“管理号”，“货物”关系的主键应为属性“货物编号”。三、B试题三/B(总题数：4，分数：15.00)7.【问题 1】 根据上述说明，请给出： (1)“职员”关系模式的主键和外键。 (2)“部门”关系模式的主键和外键。（分数：4.00）_正确答案：()解析：(1) 主键：职员号，外键：部门号。 (2) 主键：部门号或部门名；外键：负责人代码。8.【问题 2】 (1)用 SQL定义“职员”关系模式，请在空缺处填入正确的内容。 Create Table 职员(职员号 CHAR(5)U (a) /U， 职员姓名 CHAR(8)， 月工资 NUMB

20、ER(4)， 部门号 CHAR(1)， 办公室 CHAR(20)， 电话 CHAR(8)， U (b) /U (部门号)， 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 部门号，U (c) /U from 职员 U

21、(d) /U count(*)=2 WHERE 部门号 IS NOT NULL)：（分数：4.00）_正确答案：()解析：(a)PRIMARY KEY (b)FOREIGN KEY (c)COUNT(*)，SUM(月工资)，AVG(月工资) (d)GROUP BY 部门号 HAVING9.【问题 3】对于如表 2-3、表 2-4所示的“职员”和“部门”关系，请指出下列各行是否可以插入“职员”关系，为什么? 1 60811 芦 峰 800 1 A座 201 68831222 60802 李晓啸 3500 2 B座 202 68831233 60812 高亚南 2600（分数：3.00）_正确答案

22、：()解析：(1) 该行不能插入“职员”关系，它违反了用户定义完整性中月工资的取值范围必须“大于等于1000元且小于等于 8000元”的条件。 (2)该行不能插入“职员”关系，因为职员号“60802”在表 2-3中已存在，违反了实体完整性中主键必须唯一区分关系中的每一个属性的条件。 (3)该行可以插入“职员”关系，尽管部门号、电话和办公室为空，但是它表示该职员没有分配到某个部门。10.【问题 4】 原来的“职员”关系模式存在什么问题?在不增加新关系模式的前提下，请给出修改后的“职员”和“部门”关系模式。（分数：4.00）_正确答案：()解析：“职员”关系模式主要的问题是： (1)数据冗余问题。

23、因为某部门的职员人数有多少人，其办公室和电话将要重复存入多少个。 (2)数据修改不一致问题。因为某部门的办公室变了可能会导致某些职员的属性修改了，某些职员的属性未修改。 将关系模式修改为：职员(职员号，职员姓名，月工资，部门号) 部门(部门号，部门名，负责人代码，任职时间，办公室，电话) 分析 根据表 2-3，“职员”关系的主键可为属性“职员号”，根据表 2-4，“部门”关系的主键可为属性“部门号”。在“部门”关系中，由于“负责人也是公司的一名职员”，因此“负责人代码”亦即职员号，是“职员”关系的主键，是外键；在“职员”关系中，“部门号”是“部门”关系的主键，是外键。 问题 2是考查用 SQL

24、定义关系，在定义时需要特别注意完整性约束，包括：实体完整性、参照完整性。在“职员”关系中，“职员号”是主键，须声明实体完整性约束，“部门号”是外键，须声明参照完整性。故空(a)应填 PRIMARY KEY，表示主键，空(b)应填 FOREIGN KEY，表示外键。 问题 2的(2)是创建部门视图，条件是“部门人数大于 2”，这样就要求按部门分组统计职员人数。根据视图的概念，易得空(c)应填“COUNT(*)，SUM(月工资)，AVG(月工资)”，由于需要分组，故空(d)处应由“GROUP BY 部门号”，这样其后只能搭配 HAVING语句，故空(d)应填“GROUP BY 部门号 HAVING

25、”。 问题 3就是插入数据时，完整性约束的检查，必须满足实体完整性和参照完整性，以及用户自定义完整性。在此，实体完整性为“职员号唯一且非空”，参照完整性为“部门号只能取部门表中的某个部门号或为空”，用户自定义完整性为“月工资大于等于 1000且小于等于 8000”。根据这 3个完整性易得：记录 1不能插入，其工资是 800，违反了用户自定义完整性；记录 2不能插入，其职员号 60802在表 2-3中已存在，违反实体完整性(主键唯一)：记录 3可以插入，部门号等属性为空，表示尚未分配到任何部门。 考虑到“每个部门有一名负责人、一间办公室、一部电话、多名职员”，因此“职员”表中的属性“办公室”和“

26、电话”存在数据冗余，而且有可能导致数据修改不一致。因此，应该将“职员”关系中的属性“办公室”和“电话”放到“部门”关系中，这样修改后的关系模式如下： 职员(职员号，职工姓名，月工资，部门号) 部门(部门号，部门名，负责人代码，任职时间，办公室，电话)四、B试题四/B(总题数：3，分数：15.00)11.【问题 1】 根据 E-R图中给出的词汇，按照“有关模式名(属性，属性，.)”的格式，将此 E-R图转换为 4个关系模式，并指出每个关系模式中的主键和外键，其中模式名根据需要取实体名或联系名。（分数：5.00）_正确答案：()解析：Customers(cid，cname，address，card

27、num)，主键：cid。 Orders(Ordernum，Orderdate，cid)，主键：Ordemum；外键：cid。 Books(bid，title，author,qty_in_stock，year_published，price)，主键：bid。 Orderlist(bid，ordemum，qty，ship_date)，其中(bid，ordernum)是主键，外键是bid、ordernum。 注：以上 4个关系模式和每个模式中的属性可按任意次序书写。12.【问题 2】 创建 Customers表时，cid 使用 INTEGER数据类型，cname 使用 CHAR(80)数据类型，ad

28、dress使用 CHAR(200)数据类型，cardnum 使用 CHAR(16)数据类型并且要求此列值唯一。请在下列用于创建表 Customers的 SQL语句空缺处填入正确的内容。 CREATE TABLE Customers(cid INTEGER NOT NULL， cname CHAR(80) NOT NULL， address CHAR(200)， eardnum CHAR(16) NOT NULL， U(1) /U， U(2) /U)（分数：5.00）_正确答案：()解析：(1) PRIMARY KEY(cid) (2) UNIQUE(cardnum) 注：(1)和(2)的次序可

29、以颠倒。13.【问题 3】 如下的 SQL语句是书店用于查询“所有订购了 bid为123-456图书的用户订购其他图书的情况”的不完整语句，请在空缺处填入正确的内容。 Select bid From orderlist A Where not exists (Select * from Orders B where A.ordemum=B.ordemum and B.cid U(3) /U (Select cid from orderlist C,orders D where U(4) /U.bid=123-456 and U(5) /U=D.ordemum)（分数：5.00）_正确答案：()

30、解析：(3) in (4) C (5) Cordernum 分析 问题 1考查 E-R图与关系模式之间的转换。按照 E-R向关系模式转换的原则，Books、Orders 及 Customers为 3个关系模式，题中要求转换为 4个模式，E-R 图中有两个联系，显然不能都转换为关系。考虑到一个客户可以填写多张购书单，而一张购书单仅输入一个客户，所以联系 PlaceOrder 不必单独作为一个关系模式，可将其合并到 Customers中；而购书单和书(指种)之间是多对多联系，因此联系 OrderList需要单独作为一个关系。因此，4 个关系模式及其主键、外键如下： Customers(cid，cn

31、ame，address，camum)，主键为 cid。 Orders(ordemum，orderdate，cid)，主键为 ordernum，外键为 cid。 Books(bid，title，author，qty_in_stock，year_punlished，price)，主键为 bid。 OrderList(bid，ordernum，qty,ship_date)，主键为(bid，ordernum)，外键为bid、ordemum。 问题 2是用 SQL创建关系，需要注意完整性约束。Customers 关系中，cid 是主键，因此空(1)应填 PRIMARY KEY(cid)。cardnum

32、列要求唯一，因此空(2)应填 UNIQUE(cardnum)。 问题 3是填充SQL查询语句。根据题意分析，最内层 SQL语句是查找订购了 bid为“123-456”的客户 cid，而ordernum只出现在 Orderlist和 Orders中，因此易得空(5)应填 C.ordernum；bid 是 Orderlist关系的属性，故空(4)应填 C。该 SQL语句是为了查询这些用户还订购了哪些其他书，所以空(3)应填 In。五、B试题五/B(总题数：4，分数：15.00)14.【问题 1】 请按说明中的要求画出修改后的数据模型。（分数：3.00）_正确答案：()解析：15.【问题 2】 (1

33、)说明中的几个关系仍无法实现甲公司的要求，为什么? (2)需要在哪个关系中增加什么数据项才能实现这个要求?（分数：4.00）_正确答案：()解析：(1)因为数据库中没有记录订货时产品的单价，也没有记录订货的总金额，所以一旦产品单价发生变化，那么计算用的单价就是变化后的单价了。 (2)在 OrderDetail中增加一个数据项：订货时的单价(或者在 Order中增加一个数据项：总金额)。16.【问题 3】 写出 OrderDetail中的关键项。（分数：4.00）_正确答案：()解析：OrderNo，ProductNo17.【问题 4】 以下 SQL语句用于查询没有订购产品代码为“1K10”的产

34、品的所有客户名。请填补其中的空缺。 SELECT CustomerName FROM Customer U(1) /U WHERE U(2) /U (SELECT * FROM OrderDetml B，Order C WHERE B.ProductNo=C.ProductNo AND B.ProductNo=1KIO AND C.CustomerNo=A.CustomerNo)（分数：4.00）_正确答案：()解析：(1) A 或 ASA (2) NOT EXIST 分析 问题 1是修改数据模型。一个客户可以下多张订单，一张订单只属于一个客户，所以客户与订单之间是一对多关系；一张订单有多条订单项目，一条订单项目只属于一张订单，因此订单与订单项目之间是一对多关系；一条订单项目只包含一种产品，一种产品可以出现在多条订单项目中，故订单项目与产品之间是多对一关系。因此可得修改后的数据模型如下：