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郑俊华26100018001234主楼2011002王平27110018001234主楼2012001王晓华381300280012351号楼3022002李力24800280012361号楼3033001黎远军42130038001237主楼2024001李源24800480012452号楼1024002李兴民361200480012462号楼1035001赵欣250NullB表22-3“部门”关系/B 部门号部门名负责人代码任职时间1人事处10022004-
3、8-32机关20012003-8-33销售科4生产科40022003-6-15车间（分数：15.00）(1).【问题1】根据上述说明，由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
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）_正确答案：()解析：a. Primary Key b. Constraint FK_DEPT Foreign Key(负责人代码) References 职工或Foreign Key(负责人代码)References 职工 c. Constraint FK_PERDEPT
5、Foreign Key(部门号)References 部门或Foreign Key(部门号)References 部门 d月工资Between 500 And 5000或月工资=500 And 月工资=5000 eCount(*)，Sum(月工资)，Avg(月工资) fGroup by 部门号 试题一分析 SQL语言包括数据定义、数据查询和数据操纵，其中数据定义有表、视图和索引的定义。 数据库完整性包括实体完整性、参照完整性和用户自定义完整性约束。用户自定义完整性写在列级完整性约束条件中。定义实体完整性通常采用“Not Null”，“Unique”，“Constraint主键约束名Primar
6、y Key(属性组)”等。其中： “Not Null”表示该列的属性不能为空，定义时紧跟数据类型的后面； “Unique”表示该列的属性是唯一标识的(即不能取重复值)，定义时可以紧跟数据类型的后面，也可以放在最后面，这时的格式是：Unique(列名，列名.)； “Constraint主键约束名Primary Key(属性组)”表示该属性组是表的主键，能唯一标识记录。 定义参照完整性通常采用： Constraint参照约束名Foreign Key参照表(属性组)References被参照表(属性组)定义用户自定义完整性通常采用： Constraint自定义约束名Check(条件) 例如：某表R1
7、(a1，a2，a3)，其中a1，a2，a3都是int型。现约束要求a1+a2100，则用户自定义完整性定义是： Constraint C1 Check(a1+a2100) 其中“Constraint约束名”可以省略。 关于定义视图的一般格式如下： Create View视图名(列名，列名.) Aa子查询 with Check Option 注意： (1)其中的查询可以是任意复杂的Select语句，但通常不允许含有Order by子句和Distinct短语。 (2)With Check Option表示对视图进行Update，Insert和Delete操作时要保证更新、插入或删除的行满足视图定义
8、中的谓词条件(即子查询中的条件表达式)。 对视图的查询、更新操作，它的执行过程是首先把这个SQL语句与定义这个视图的SQL语句合并起来，转换成一个新的SQL语句，然后才真正的执行。 SQL语言还提供了一些常用的统计函数，如：Count用来统计元组个数，Sum用来计算一列值的总和，Avg用来计算一列值的平均值，Max用来求一列值中的最大值， Min用来求一列值中的最小值，等等。 SQL语言的查询优化在数据库系统中有着非常重要的地位，同时也是考试的一个难点，能反映出考生能否比较熟练地掌握SQL语言，做这种类型的题目有一些技巧。一般来说，如果查询中采用了查询嵌套，特别是自我连接的那种类型，优化的原则
9、是尽量采用不嵌套的的SQL语句来实现相同的功能；当有选择运算时，应尽可能让它先做：在执行连接前注意对关系做适当的预处理，比如在联接的属性上建立索引和对关系排序，然后再执行联接。 问题1 从试题描述可以看出，在“部门”关系中，“部门号”是唯一标识记录的，是该关系的主键，可采用(部门号Char(1)Primary Key)来定义。在“职工”关系中，“职工号”是唯一标识记录的，因此它是主键，可以定义为：UNIQUE(职工号)或Constraint PF PER Primary Key(职工号)。 显然，“负责人代码”是“部门”关系的外键，通过“负责人代码”等于“职工号”来关联“职工”关系的，定义为：
10、Constraint FK_DEPT Foreign Key(负责人代码)References职工(职工号)。同时“部门号”是“职工”关系的外键，定义为：Constraint FK_PERDEPT Foreign Key(部门号)References部门(部门号)。 由于表22-1中告诉我们在“职工”关系中的月工资有个约束：500元月工资 5000元。它属于用户自定义完整性约束，可以定义为：Check(月工资Between 500 And 5000)。 通过对题目分析，建立该视图，要采用到集函数和记录分组语句，采用Count来计算部门的人数，用Sum来计算工资总数，用Avg来计算平均工资。然后
11、用“Group by部门号”来对不同部门进行分组。创建D_S视图的SQL语句如下： 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)不能插
12、入。它违反了参照完整性。因为6在关系“部门”中不存在。 本题主要考查完整性定义的约束性。 先看看第一条记录，它的职工号是1001，在表22-2中已经存在该职工号的记录。因为“职工号”是“职工”关系的主键，它在表中不能重复出现，否则破坏了实体的完整性。因此该条记录不能插入。 在第二条记录中职工号没有重复，同时它可以先不录入部门号(表示是新职工，暂时还没有分配部门)，因为在“职工”关系中“部门号”是外键，在定义中也没有约束它不能为空。因此该记录可以插入。 最后一条记录中，部门号是6，但是在“部门”关系中没有找到“部门号”是6的记录，因此不能做插入操作。否则，就违反了参照完整性规则。(3).【问题3
13、】在问题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）_正确答案：()解析：(1)和(2)都不能执行，因为使用分组和聚集函数定义的视图是不可更新的。 (3)不一定能执行，具体要看视图的返回
14、值的情况。 (4)和(5)可以执行，因为给出的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中不能包
15、括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)对该查询语句进行修改，使它既可以完成相同功能
16、，又可以提高查询效率。（分数：3.00）_正确答案：()解析：(1)对于外层的“职工”关系E中的每一个元组，都要对内层的整个“职工”关系M进行检索，因此查询效率不高。 (2)本题可以有两种解法： 解答一： 改正后的SQL语句使用了临时表： Select Max(月工资)as 最高工资，部门号Into Temp From 职工 Group by 部门号 Select 职工号 From 职工，Temp Where 月工资=最高工资 And 职工.部门号=Temp.部门号； 解答二： Select 职工号 From 职工，(Select Max(月工资) As 最高工资，部门号 Group by 部
17、门号 As depMax Where 月工资=最高工资 And 职工.部门号=depMax.部门号； 因为该SQL语句用了查询嵌套和聚集函数，所以这种方式的查询效率会受到很大的影响。可以把它改成： Select Max(月工资)As maxgz，部门号Into Temp From 职工 Group by 部门号 Select 职工号 From 职工，Temp Where 月工资=maxgz And 职工.部门号=Temp.部门号；(5).【问题5】假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引，如下的 Select查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出
18、既可以完成相同功能又可以提高查询效率的SQL语句。Select 姓名，年龄，月工资 from 职工where 年龄45 or 月工资1000；（分数：3.00）_正确答案：()解析：Select 姓名，年龄，月工资 From 职工 Where 年龄45 UNION Select 姓名，年龄，月工资 From 职工 Where 月工资1000； 因为该SQL语句用了查询嵌套和聚集函数，所以这种方式的查询效率会受到很大的影响。可以把它改成： Select Max(月工资)As maxgz，部门号Into Temp From 职工 Group by 部门号 Select 职工号 From 职工，Te
19、mp Where 月工资=maxgz And 职工.部门号=Temp.部门号； 在语句 Select 姓名，年龄。月工资 From 职工 Where 年龄45 or 月工资1000； 中，由于使用了条件“Or”，查询的时候变成了对全表的扫描，不会促使查询优化器使用索引，从而降低了查询效率。改正的方法是去掉“Or”，修改后的SQL语句如下： Select 姓名，年龄，月工资 From 职工 Where 年龄45 UNION Select 姓名，年龄，月工资 From 职工 Where 月工资1000；二、B试题二/B(总题数：1，分数：15.00)阅读下列说明，回答问题1至问题5。【说明】某仓储
20、超市采用POS(Point of Sale)收银机负责前台的销售收款，为及时掌握销售信息，并依此指导进货，拟建立商品进、销、存数据库管理系统。该系统的需求分析已经基本完成，下面将进入概念模型的设计。【需求分析结果】1销售业务由POS收银机来辅助实现。POS机外接条码阅读器，结账时收银员将商品的条码通过阅读输入器输入POS机中。所售商品数量默认值为1，可以由收银员修改。POS机根据输入的商品信息，打印出图22-1所示的购物清单。2将经销的商品分为直销商品和库存商品两大类。直销商品的保质期较短，如食品类，由供应商直接送达超市，管理员将过期的商品返还给供应商处理；库存商品由采购员向供应商提交订购单，
21、供应商根据订购单送货。超市会不定期对库存商品按照折扣率进行打优惠。直销商品和库存商品的送货单样表分别如图22-2、图22-3所示，其中直销商品生产批号的前六位表示生产日期。3超市的硬件拓扑结构如图22-4所示。4业务处理过程：由POS机存储每一笔销售记录，在每个工作日结束前汇总当日各商品的销售量至中心数据库(销售日汇总)：根据当日的销售日汇总更新存货表；每笔进货记入进货表中，并及时更新存货表。【概念模型设计】根据需求阶段收集的信息，设计的实体联系图和关系模式(不完整)如下：1实体联系图(如图22-5所示)2关系模式销售详单(销售流水号，商品编码，数量，金额，收银员，时间)销售日汇总(日期，商品
22、编码，数量)存货表(商品编码，数量)进货表(送货号码，商品编码，数量，日期)商品(U (b) /U)4业务处理过程：由POS机存储每一笔销售记录，在每个工作日结束前汇总当日各商品的销售量至中心数据库(销售日汇总)：根据当日的销售日汇总更新存货表；每笔进货记入进货表中，并及时更新存货表。【概念模型设计】根据需求阶段收集的信息，设计的实体联系图和关系模式(不完整)如下：1实体联系图(如图22-5所示)2关系模式销售详单(销售流水号，商品编码，数量，金额，收银员，时间)销售日汇总(日期，商品编码，数量)存货表(商品编码，数量)进货表(送货号码，商品编码，数量，日期)商品(U (b) /U)（分数：1
23、5.00）(1).【问题1】对直销商品和库存商品进行概括，给出超类和子类，填入图22-5中(a)处所示的虚线框内，并补充联系。【问题1】对直销商品和库存商品进行概括，给出超类和子类，填入图22-5中(a)处所示的虚线框内，并补充联系。（分数：3.00）_正确答案：()解析：见图22-6。 试题2分析 这是道关于数据库设计方面的试题，主要考查考生在数据库设计中的概念结构设计与逻辑结构设计方面的知识。 所谓概念结构设计，就是将需求分析得到的用户需求抽象为信息结构，它是整个数据库设计的关键。在这个过程中有个很重要的环节，就是设计E-R图。 在概念设计阶段中，数据抽象是对实际的人，物，事和概念进行人为
24、处理，抽取所关心的共同特性。有三种抽象形式，分别是分类，聚集和概括。其中概括是定义类型之间的一种子集联系，其重要性质是继承性。也就是说子类继承了超类上定义的所有抽象。 例如学生是实体型，本科生，研究生也是实体型，本科生和研究生是学生的子集。则学生称为超类，本科生和研究生称为学生的子类。本科生和研究生继承了学生类型的属性。当然，子类可以增加自己的某些特殊属性。 概念结构设计是独立于任何一种数据模型的信息结构。而逻辑结构设计的任务是把概念结构设计阶段设计奸的基本E-R图转换为与选用DBMS产品所支持的数据模型相符合的逻辑结构。 问题1 解答此类题目的关键是要能理解题目中处理的信息，以及它们之间的关
25、系。在题目中告诉了我们，将经销的商品分为直销商品和库存商品两大类。因此在E-R图中商品是超类，而直销商品和库存商品是子类。而且在E-R图中销售详单、销售日汇总、存货表和进货表中的商品编号属性都必须能在商品表中找到。即它们与商品的关系分别是 N:I、N:I、1:1和N:I。因此，E-R图如图22-6所示。 (2).【问题2】根据你的实体联系图，完成(b)处的商品关系模式，并增加子类型的实体关系模式。（分数：3.00）_正确答案：()解析：商品(商品编码，商品名称，供应商，价格) 直销商品(商品编码，生产批号，消费期限) 库存商品(商品编码，折扣率) 根据问题1的分析，同时从图22-1，图22-2
26、和图22-3可以看出，商品包括了商品编码，商品名称和价格属性。 商品(商品编码，商品名称，供应商，价格) 因为直销商品还加上生产批号，消费期限，因此，直销商品的关系模式是： 直销商品(商品编码，生产批号，消费期限) 题目中该系统对库存商品会不定期按照折扣率进行打折优惠。因此可以看出，库存商品还有价格折扣率这个字段，即可知道库存商品的关系模式是： 库存商品(商品编码，价格折扣率)(3).【问题3】对所有关系模式，以下划线指出各关系模式的主键。（分数：3.00）_正确答案：()解析：销售详单(U销售流水号，商品编号/U，数量，金额，收银员，时间) 销售日汇总(U日期，商品编码/U，数量) 存货表(
27、U商品编码/U，数量) 进货表(U送货号码，商品编码/U，数量，日期) 商品(U商品编码/U，商品名称，供应商，价格) 直销商品(U商品编码，生产批号/U，消费期限) 库存商品(U商品编号/U，折扣率) 这是一道关于确定关系模式中的主键的问题。此种类型的题目比较简单，只要对题意有大致的了解，就可以很容易地解答。通过图22-1可以知道，销售详单中通过销售流水号和商品编号就可以确定其他属性值了。销售日汇总表是通过从每个工作日结束前汇总当日各商品的销售量情况得来的，很显然，在销售日汇总关系中只有(日期，商品编号)才能唯一决定记录，因此(日期，商品编号)是主键。存货表记录每种商品的库存数量，它的主键是商品编号。从图22-2和图22-3可以知道，有了送货号码和商品编号就可以确定某条记录的其他信息，如：数量，日期等。(4).【问题4】如果将商品信息只存储在中心数据库中，与在各POS机上存储其备份相比，从前台销售效率和更新商品库两方面论述各自的优缺点(不超过300字)。（分数：3.00）_正确答案：()解析：第一种方式：采用商品信息集中存储在中心数据库中的方式，则在销售前台的每笔计费中，都必须从中心数据库提取商品名称和单价，增加网络的负载，在业务繁忙时直接影响到前台的销售效
