1、数据库系统工程师-数据库综合设计及答案解析(总分：90.00，做题时间：90 分钟)一、B试题一/B(总题数：1，分数：15.00)阅读下列说明，回答问题 1 至问题 5。【说明】某工厂的信息管理数据库的部分关系模式如下所示：职工(职工号，姓名，年龄，月工资，部门号，电话，办公室)部门(部门号，部门名，负责人代码，任职时间)关系模式的主要属性、含义及约束如表 22-1 所示，“职工”和“部门”的关系示例分别如表 22-2 和表22-3 所示。B表 22-1 主要属性、含义及约束/B 属性 含义和约束条件职工号唯一标记每个职工的编号，每个职工性于并且仅属于一个部门部门号唯一标记每个部门的编号，每
2、个部门有一个负责人，且他也是一个职工月工资 500 元月工资500 元B表 22-2“职工”关系/B职工号 姓名 年龄 月工资 部门号 电话 办公室1001 郑俊华 26 1000 1 8001234 主楼 2011002 王平 27 1100 1 8001234 主楼 2012001 王晓华 38 1300 2 8001235 1 号楼 3022002 李力 24 800 2 8001236 1 号楼 3033001 黎远军 42 1300 3 8001237 主楼 2024001 李源 24 800 4 8001245 2 号楼 1024002 李兴民 36 1200 4 8001246
3、2 号楼 1035001 赵欣 25 0 Null B表 22-3“部门”关系/B 部门号 部门名 负责人代码 任职时间1 人事处 1002 2004-8-32 机关 2001 2003-8-33 销售科4 生产科 4002 2003-6-15 车间（分数：15.00）(1).【问题 1】根据上述说明，由 SQL 定义的“职工”和“部门”的关系模式，以及统计各部门的人数 C、工资总数 Totals、平均工资 Averages 的 D_S 视图如下所示，请在空缺处填入正确的内容。Create Table 部门 (部门号 Char(1)U (a) /U部门名 Char(16),负责人代码 Char
4、(4),任职时间 DATE,U(b) /U (职工号)；Create Table 职工 (职工号 Char(4)，姓名 Char(8)，年龄 NUMDER(3)，月工资 NUMDER(4)，部门号 Char(1)，电话 Char(8)，办公室 Char(8)，U(a) /U(职工号)，U(c) /U(部门号)，CHECK(U (d) /U)；Create View D_S(D，C，Totals，Averages) As(Select 部门号，U (e) /Ufrom 职工U (f) /U（分数：3.00）_(2).【问题 2】对于表 22-2、表 22-3 所示的“职工”和“部门”关系，请指出
5、下列各行是否可以插入，为什么?（分数：3.00）_(3).【问题 3】在问题 1 定义的视图 D_S 上，下面哪个查询或更新是允许执行的，为什么?(1)Update D_S set D=3 where D=4；(2)Delete from D_S where C4；(3)Select D，Averages from D_Swhere C(Select C from D_S where D=:dept)；(4)Select D，C from D_Swhere Totals10000；(5)Select* from D_S（分数：3.00）_(4).【问题 4】查询每个部门中月工资最高的“职工号”
6、的 SQL 查询语句如下：Select 职工号 from 职工 EWhere 月工资=(Select Max(月工资)from 职工 as Mwhere M. 部门号=E. 部门号)；(1)请用 30 字以内文字简要说明该查询语句对查询效率的影响。(2)对该查询语句进行修改，使它既可以完成相同功能，又可以提高查询效率。（分数：3.00）_(5).【问题 5】假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引，如下的 Select 查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既可以完成相同功能又可以提高查询效率的 SQL 语句。Select 姓名，年龄，月工资 f
7、rom 职工where 年龄45 or 月工资1000；（分数：3.00）_二、B试题二/B(总题数：1，分数：15.00)阅读下列说明，回答问题 1 至问题 5。【说明】某仓储超市采用 POS(Point of Sale)收银机负责前台的销售收款，为及时掌握销售信息，并依此指导进货，拟建立商品进、销、存数据库管理系统。该系统的需求分析已经基本完成，下面将进入概念模型的设计。【需求分析结果】1销售业务由 POS 收银机来辅助实现。POS 机外接条码阅读器，结账时收银员将商品的条码通过阅读输入器输入 POS 机中。所售商品数量默认值为 1，可以由收银员修改。POS 机根据输入的商品信息，打印出图
8、 22-1 所示的购物清单。2将经销的商品分为直销商品和库存商品两大类。直销商品的保质期较短，如食品类，由供应商直接送达超市，管理员将过期的商品返还给供应商处理；库存商品由采购员向供应商提交订购单，供应商根据订购单送货。超市会不定期对库存商品按照折扣率进行打优惠。直销商品和库存商品的送货单样表分别如图 22-2、图 22-3 所示，其中直销商品生产批号的前六位表示生产日期。3超市的硬件拓扑结构如图 22-4 所示。4业务处理过程：由 POS 机存储每一笔销售记录，在每个工作日结束前汇总当日各商品的销售量至中心数据库(销售日汇总)：根据当日的销售日汇总更新存货表；每笔进货记入进货表中，并及时更新
9、存货表。【概念模型设计】根据需求阶段收集的信息，设计的实体联系图和关系模式(不完整)如下：1实体联系图(如图 22-5 所示)2关系模式销售详单(销售流水号，商品编码，数量，金额，收银员，时间)销售日汇总(日期，商品编码，数量)存货表(商品编码，数量)进货表(送货号码，商品编码，数量，日期)商品(U (b) /U)（分数：15.00）(1).【问题 1】对直销商品和库存商品进行概括，给出超类和子类，填入图 22-5 中(a)处所示的虚线框内，并补充联系。（分数：3.00）_(2).【问题 2】根据你的实体联系图，完成(b)处的商品关系模式，并增加子类型的实体关系模式。（分数：3.00）_(3)
10、.【问题 3】对所有关系模式，以下划线指出各关系模式的主键。（分数：3.00）_(4).【问题 4】如果将商品信息只存储在中心数据库中，与在各 POS 机上存储其备份相比，从前台销售效率和更新商品库两方面论述各自的优缺点(不超过 300 字)。（分数：3.00）_(5).【问题 5】如果考虑引入积分卡，根据累积消费金额计算积分点，再根据和分点在顾客购物时进行现金返还，并修改顾客的累积消费金额和积分点。请给出新增加的积分卡关系模式，并对销售详单关系模式进行修正，指出修正后关系模式和新增关系模式的候选键和外键。（分数：3.00）_三、B试题三/B(总题数：3，分数：60.00)(1).【问题 1】
11、根据上述说明，由 SQL 定义的“职工”和“部门”的关系模式，以及统计各部门的人数 C、工资总数 Totals、平均工资 Averages 的 D_S 视图如下所示，请在空缺处填入正确的内容。Create Table 部门 (部门号 Char(1)U (a) /U部门名 Char(16),负责人代码 Char(4),任职时间 DATE,U(b) /U (职工号)；Create Table 职工 (职工号 Char(4)，姓名 Char(8)，年龄 NUMDER(3)，月工资 NUMDER(4)，部门号 Char(1)，电话 Char(8)，办公室 Char(8)，U(a) /U(职工号)，U(
12、c) /U(部门号)，CHECK(U (d) /U)；Create View D_S(D，C，Totals，Averages) As(Select 部门号，U (e) /Ufrom 职工U (f) /U（分数：3.00）_(2).【问题 2】对于表 22-2、表 22-3 所示的“职工”和“部门”关系，请指出下列各行是否可以插入，为什么?（分数：3.00）_(3).【问题 3】在问题 1 定义的视图 D_S 上，下面哪个查询或更新是允许执行的，为什么?(1)Update D_S set D=3 where D=4；(2)Delete from D_S where C4；(3)Select D，
13、Averages from D_Swhere C(Select C from D_S where D=:dept)；(4)Select D，C from D_Swhere Totals10000；(5)Select* from D_S（分数：3.00）_(4).【问题 4】查询每个部门中月工资最高的“职工号”的 SQL 查询语句如下：Select 职工号 from 职工 EWhere 月工资=(Select Max(月工资)from 职工 as Mwhere M. 部门号=E. 部门号)；(1)请用 30 字以内文字简要说明该查询语句对查询效率的影响。(2)对该查询语句进行修改，使它既可以完成
14、相同功能，又可以提高查询效率。（分数：3.00）_(5).【问题 5】假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引，如下的 Select 查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既可以完成相同功能又可以提高查询效率的 SQL 语句。Select 姓名，年龄，月工资 from 职工where 年龄45 or 月工资1000；（分数：3.00）_B试题二/B阅读下列说明，回答问题 1 至问题 5。【说明】某仓储超市采用 POS(Point of Sale)收银机负责前台的销售收款，为及时掌握销售信息，并依此指导进货，拟建立商品进、销、存数据库管理系统。该系
15、统的需求分析已经基本完成，下面将进入概念模型的设计。【需求分析结果】1销售业务由 POS 收银机来辅助实现。POS 机外接条码阅读器，结账时收银员将商品的条码通过阅读输入器输入 POS 机中。所售商品数量默认值为 1，可以由收银员修改。POS 机根据输入的商品信息，打印出图 22-1 所示的购物清单。2将经销的商品分为直销商品和库存商品两大类。直销商品的保质期较短，如食品类，由供应商直接送达超市，管理员将过期的商品返还给供应商处理；库存商品由采购员向供应商提交订购单，供应商根据订购单送货。超市会不定期对库存商品按照折扣率进行打优惠。直销商品和库存商品的送货单样表分别如图 22-2、图 22-3
16、 所示，其中直销商品生产批号的前六位表示生产日期。3超市的硬件拓扑结构如图 22-4 所示。4业务处理过程：由 POS 机存储每一笔销售记录，在每个工作日结束前汇总当日各商品的销售量至中心数据库(销售日汇总)：根据当日的销售日汇总更新存货表；每笔进货记入进货表中，并及时更新存货表。【概念模型设计】根据需求阶段收集的信息，设计的实体联系图和关系模式(不完整)如下：1实体联系图(如图 22-5 所示)2关系模式销售详单(销售流水号，商品编码，数量，金额，收银员，时间)销售日汇总(日期，商品编码，数量)存货表(商品编码，数量)进货表(送货号码，商品编码，数量，日期)商品(U (b) /U)（分数：1
17、5.00）(1).【问题 1】对直销商品和库存商品进行概括，给出超类和子类，填入图 22-5 中(a)处所示的虚线框内，并补充联系。（分数：3.00）_(2).【问题 2】根据你的实体联系图，完成(b)处的商品关系模式，并增加子类型的实体关系模式。（分数：3.00）_(3).【问题 3】对所有关系模式，以下划线指出各关系模式的主键。（分数：3.00）_(4).【问题 4】如果将商品信息只存储在中心数据库中，与在各 POS 机上存储其备份相比，从前台销售效率和更新商品库两方面论述各自的优缺点(不超过 300 字)。（分数：3.00）_(5).【问题 5】如果考虑引入积分卡，根据累积消费金额计算积
18、分点，再根据和分点在顾客购物时进行现金返还，并修改顾客的累积消费金额和积分点。请给出新增加的积分卡关系模式，并对销售详单关系模式进行修正，指出修正后关系模式和新增关系模式的候选键和外键。（分数：3.00）_(1).【问题 1】根据上述说明，由 SQL 定义的“职工”和“部门”的关系模式，以及统计各部门的人数 C、工资总数 Totals、平均工资 Averages 的 D_S 视图如下所示，请在空缺处填入正确的内容。Create Table 部门 (部门号 Char(1)U (a) /U部门名 Char(16),负责人代码 Char(4),任职时间 DATE,U(b) /U (职工号)；Crea
19、te Table 职工 (职工号 Char(4)，姓名 Char(8)，年龄 NUMDER(3)，月工资 NUMDER(4)，部门号 Char(1)，电话 Char(8)，办公室 Char(8)，U(a) /U(职工号)，U(c) /U(部门号)，CHECK(U (d) /U)；Create View D_S(D，C，Totals，Averages) As(Select 部门号，U (e) /Ufrom 职工U (f) /U（分数：3.00）_(2).【问题 2】对于表 22-2、表 22-3 所示的“职工”和“部门”关系，请指出下列各行是否可以插入，为什么?（分数：3.00）_(3).【问题
20、 3】在问题 1 定义的视图 D_S 上，下面哪个查询或更新是允许执行的，为什么?(1)Update D_S set D=3 where D=4；(2)Delete from D_S where C4；(3)Select D，Averages from D_Swhere C(Select C from D_S where D=:dept)；(4)Select D，C from D_Swhere Totals10000；(5)Select* from D_S（分数：3.00）_(4).【问题 4】查询每个部门中月工资最高的“职工号”的 SQL 查询语句如下：Select 职工号 from 职工
21、EWhere 月工资=(Select Max(月工资)from 职工 as Mwhere M. 部门号=E. 部门号)；(1)请用 30 字以内文字简要说明该查询语句对查询效率的影响。(2)对该查询语句进行修改，使它既可以完成相同功能，又可以提高查询效率。（分数：3.00）_(5).【问题 5】假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引，如下的 Select 查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既可以完成相同功能又可以提高查询效率的 SQL 语句。Select 姓名，年龄，月工资 from 职工where 年龄45 or 月工资1000；（分数：
22、3.00）_(6).【问题 1】对直销商品和库存商品进行概括，给出超类和子类，填入图 22-5 中(a)处所示的虚线框内，并补充联系。（分数：3.00）_(7).【问题 2】根据你的实体联系图，完成(b)处的商品关系模式，并增加子类型的实体关系模式。（分数：3.00）_(8).【问题 3】对所有关系模式，以下划线指出各关系模式的主键。（分数：3.00）_(9).【问题 4】如果将商品信息只存储在中心数据库中，与在各 POS 机上存储其备份相比，从前台销售效率和更新商品库两方面论述各自的优缺点(不超过 300 字)。（分数：3.00）_(10).【问题 5】如果考虑引入积分卡，根据累积消费金额计
23、算积分点，再根据和分点在顾客购物时进行现金返还，并修改顾客的累积消费金额和积分点。请给出新增加的积分卡关系模式，并对销售详单关系模式进行修正，指出修正后关系模式和新增关系模式的候选键和外键。（分数：3.00）_数据库系统工程师-数据库综合设计答案解析(总分：90.00，做题时间：90 分钟)一、B试题一/B(总题数：1，分数：15.00)阅读下列说明，回答问题 1 至问题 5。【说明】某工厂的信息管理数据库的部分关系模式如下所示：职工(职工号，姓名，年龄，月工资，部门号，电话，办公室)部门(部门号，部门名，负责人代码，任职时间)关系模式的主要属性、含义及约束如表 22-1 所示，“职工”和“部
24、门”的关系示例分别如表 22-2 和表22-3 所示。B表 22-1 主要属性、含义及约束/B 属性 含义和约束条件职工号唯一标记每个职工的编号，每个职工性于并且仅属于一个部门部门号唯一标记每个部门的编号，每个部门有一个负责人，且他也是一个职工月工资 500 元月工资500 元B表 22-2“职工”关系/B职工号 姓名 年龄 月工资 部门号 电话 办公室1001 郑俊华 26 1000 1 8001234 主楼 2011002 王平 27 1100 1 8001234 主楼 2012001 王晓华 38 1300 2 8001235 1 号楼 3022002 李力 24 800 2 80012
25、36 1 号楼 3033001 黎远军 42 1300 3 8001237 主楼 2024001 李源 24 800 4 8001245 2 号楼 1024002 李兴民 36 1200 4 8001246 2 号楼 1035001 赵欣 25 0 Null B表 22-3“部门”关系/B 部门号 部门名 负责人代码 任职时间1 人事处 1002 2004-8-32 机关 2001 2003-8-33 销售科4 生产科 4002 2003-6-15 车间（分数：15.00）(1).【问题 1】根据上述说明，由 SQL 定义的“职工”和“部门”的关系模式，以及统计各部门的人数 C、工资总数 To
26、tals、平均工资 Averages 的 D_S 视图如下所示，请在空缺处填入正确的内容。Create Table 部门 (部门号 Char(1)U (a) /U部门名 Char(16),负责人代码 Char(4),任职时间 DATE,U(b) /U (职工号)；Create Table 职工 (职工号 Char(4)，姓名 Char(8)，年龄 NUMDER(3)，月工资 NUMDER(4)，部门号 Char(1)，电话 Char(8)，办公室 Char(8)，U(a) /U(职工号)，U(c) /U(部门号)，CHECK(U (d) /U)；Create View D_S(D，C，Tota
27、ls，Averages) As(Select 部门号，U (e) /Ufrom 职工U (f) /U（分数：3.00）_正确答案：()解析：a. Primary Key b. Constraint FK_DEPT Foreign Key(负责人代码) References 职工或 Foreign Key(负责人代码)References 职工 c. Constraint FK_PERDEPT Foreign Key(部门号)References 部门或Foreign Key(部门号)References 部门 d月工资 Between 500 And 5000 或月工资=500 And 月工资
28、=5000 eCount(*)，Sum(月工资)，Avg(月工资) fGroup by 部门号 试题一分析 SQL 语言包括数据定义、数据查询和数据操纵，其中数据定义有表、视图和索引的定义。 数据库完整性包括实体完整性、参照完整性和用户自定义完整性约束。用户自定义完整性写在列级完整性约束条件中。定义实体完整性通常采用“Not Null”，“Unique”，“Constraint 主键约束名 Primary Key(属性组)”等。其中： “Not Null”表示该列的属性不能为空，定义时紧跟数据类型的后面； “Unique”表示该列的属性是唯一标识的(即不能取重复值)，定义时可以紧跟数据类型的后
29、面，也可以放在最后面，这时的格式是：Unique(列名，列名.)； “Constraint 主键约束名 Primary Key(属性组)”表示该属性组是表的主键，能唯一标识记录。 定义参照完整性通常采用： Constraint 参照约束名 Foreign Key 参照表(属性组)References 被参照表(属性组)定义用户自定义完整性通常采用： Constraint 自定义约束名 Check(条件) 例如：某表 R1(a1，a2，a3)，其中 a1，a2，a3 都是 int 型。现约束要求 a1+a2100，则用户自定义完整性定义是： Constraint C1 Check(a1+a210
30、0) 其中“Constraint 约束名”可以省略。 关于定义视图的一般格式如下： Create View视图名(列名，列名.) Aa子查询 with Check Option 注意： (1)其中的查询可以是任意复杂的 Select 语句，但通常不允许含有 Order by 子句和Distinct 短语。 (2)With Check Option 表示对视图进行 Update，Insert 和 Delete 操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。 对视图的查询、更新操作，它的执行过程是首先把这个 SQL 语句与定义这个视图的 SQL 语句合并起来，
31、转换成一个新的 SQL 语句，然后才真正的执行。 SQL 语言还提供了一些常用的统计函数，如：Count 用来统计元组个数，Sum 用来计算一列值的总和，Avg 用来计算一列值的平均值，Max 用来求一列值中的最大值， Min 用来求一列值中的最小值，等等。 SQL 语言的查询优化在数据库系统中有着非常重要的地位，同时也是考试的一个难点，能反映出考生能否比较熟练地掌握 SQL 语言，做这种类型的题目有一些技巧。一般来说，如果查询中采用了查询嵌套，特别是自我连接的那种类型，优化的原则是尽量采用不嵌套的的 SQL 语句来实现相同的功能；当有选择运算时，应尽可能让它先做：在执行连接前注意对关系做适当
32、的预处理，比如在联接的属性上建立索引和对关系排序，然后再执行联接。 问题 1 从试题描述可以看出，在“部门”关系中，“部门号”是唯一标识记录的，是该关系的主键，可采用(部门号 Char(1)Primary Key)来定义。在“职工”关系中，“职工号”是唯一标识记录的，因此它是主键，可以定义为：UNIQUE(职工号)或 Constraint PF PER Primary Key(职工号)。 显然，“负责人代码”是“部门”关系的外键，通过“负责人代码”等于“职工号”来关联“职工”关系的，定义为：Constraint FK_DEPT Foreign Key(负责人代码)References 职工(职
33、工号)。同时“部门号”是“职工”关系的外键，定义为：Constraint FK_PERDEPT Foreign Key(部门号)References 部门(部门号)。 由于表 22-1 中告诉我们在“职工”关系中的月工资有个约束：500 元月工资 5000 元。它属于用户自定义完整性约束，可以定义为：Check(月工资 Between 500 And 5000)。通过对题目分析，建立该视图，要采用到集函数和记录分组语句，采用 Count 来计算部门的人数，用Sum 来计算工资总数，用 Avg 来计算平均工资。然后用“Group by 部门号”来对不同部门进行分组。创建D_S 视图的 SQL 语
34、句如下： Create View D_S(D，CTotals，Averages) AS (Select 部门号，Count (*)，sum (月工资)，Avg(月工资)From 职工 Group by 部门号)(2).【问题 2】对于表 22-2、表 22-3 所示的“职工”和“部门”关系，请指出下列各行是否可以插入，为什么?（分数：3.00）_正确答案：()解析：(1)不能插入。它违反了实体完整性原则，因为其主键属性值已经存在。 (2)可以插入。尽管部门号、电话和办公室为空，但是它表示该职工暂时还没有分配到某个部门。 (3)不能插入。它违反了参照完整性。因为 6 在关系“部门”中不存在。 本
35、题主要考查完整性定义的约束性。 先看看第一条记录，它的职工号是 1001，在表 22-2 中已经存在该职工号的记录。因为“职工号”是“职工”关系的主键，它在表中不能重复出现，否则破坏了实体的完整性。因此该条记录不能插入。 在第二条记录中职工号没有重复，同时它可以先不录入部门号(表示是新职工，暂时还没有分配部门)，因为在“职工”关系中“部门号”是外键，在定义中也没有约束它不能为空。因此该记录可以插入。 最后一条记录中，部门号是 6，但是在“部门”关系中没有找到“部门号”是 6 的记录，因此不能做插入操作。否则，就违反了参照完整性规则。(3).【问题 3】在问题 1 定义的视图 D_S 上，下面哪
36、个查询或更新是允许执行的，为什么?(1)Update D_S set D=3 where D=4；(2)Delete from D_S where C4；(3)Select D，Averages from D_Swhere C(Select C from D_S where D=:dept)；(4)Select D，C from D_Swhere Totals10000；(5)Select* from D_S（分数：3.00）_正确答案：()解析：(1)和(2)都不能执行，因为使用分组和聚集函数定义的视图是不可更新的。 (3)不一定能执行，具体要看视图的返回值的情况。 (4)和(5)可以执行，
37、因为给出的 SQL 语句与定义 D_S 视图的 SQL 语句合并起来验证有效。 做这种类型的题目时，只要把题目给出的 SQL 语句与定义该视图的 SQL 语句合并起来验证是否有效即可。在问题 1 的分析中，我们已经求出了定义该视图的 SQL 语句如下： Create View D_S(D，C，Totals，Averages)AS (Select 部门号，Count (职工号)， SUN (月工资)，AVG (月工资) From 职工 Group by 部门号) (1)合并结果为：Update 职工 Set 部门号=3 Where 部门号 =4 Group by 部门号。因为 Where 中不能
38、包括 Group 聚合函数，因此不能执行。 (2)合并结果为：Delete From 职工Where Count(职工号)4 Group by 部门号，因此也不能执行。 (3)这种要看视图的返回值的情况。因此不一定能执行。 (4)可以。 (5)显然该语句能执行。(4).【问题 4】查询每个部门中月工资最高的“职工号”的 SQL 查询语句如下：Select 职工号 from 职工 EWhere 月工资=(Select Max(月工资)from 职工 as Mwhere M. 部门号=E. 部门号)；(1)请用 30 字以内文字简要说明该查询语句对查询效率的影响。(2)对该查询语句进行修改，使它既可以完成相同功能，又可以提高查询效率。（分数：3.00）_正确答案：()解析：(1)对于外层的“职工”关系 E 中的每一个元组，都要对内层的整个“职工”关系 M 进行检索，因此查询效率不高。 (2)本题可以有两种解法： 解答一： 改正后的 SQL 语句使用了临时表： Select Max(月工资)as 最高工资，部门号 Int
