数据库系统工程师-数据库综合设计

1、数据库系统工程师-数据库综合设计（总分：90.00，做题时间：90分钟）一、B试题一 /B（ 总题数：1,分数：15.00）阅读下列说明，回答问题1至问题5。【说明】某工厂的信息管理数据库的部分关系模式如下所示：职工（职工号，姓名，年龄，月工资，部门号，电话，办公室）部门（部门号，部门名，负责人代码，任职时间）关系模式的主要属性、含义及约束如表22-1所示，“职工”和“部门”的关系示例分别如表22-2和表22-3所示。B表22-1主要属性、含义及约束 /B属性含义和约束条件职工 号唯一标记每个职工的编号，每个职工性于并且仅属于一 个部门部门 号唯一标记每个部门的编号，每个部门有一个负责人，且

2、他也是一个职工月工 资500元w月工资w 500元B表 22-2 “职工”关系/B职工号姓名年龄月工资部门号电话办公室1001郑俊华:26100018001234r主楼2011002王平27110018001234主楼2012001王晓华:381300280012351号楼3022002 :李力2480028001236r 1号楼3033001黎远军42130038001237主楼2024001李源24800480012452号楼1024002 :李兴民136120048001246:2号楼1035001赵欣250NullB表 22-3 “部门”关系/B部门号部门名负责人代码任职时间1人事处1

3、0022004-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) ， 年龄 NUM

4、DER(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) /U from 职工U (f) /U(分数： 3.00 ) 正确答案： （）解析：a. Primary Key b. Constraint FKDEPForeign Key（负责人代码）References 职工或 Foreign Key（负 责人代码 ）Re

5、ferences 职工 c. Constraint FK_PERDEPT Foreign Key（ 部门号 ）References 部门或 Foreign Key（部门号References 部门 d 月工资 Between 500 And 5000 或月工资=500 And 月工资v =5000 e. Count（*） , Sum（月工资），Avg（月工资）f Group by部门号试题一分析SQL语言包括数据定义、 数据查询和数据操纵，其中数据定义有表、视图和索引的定义。 数据库完整性包括实体完整性、参照完整 性和用户自定义完整性约束。用户自定义完整性写在列级完整性约束条件中。定义实体完整

6、性通常采用“Not Null ”，“ Unique”，" Constraint主键约束名 Primary Key（属性组）”等。其中： “ Not Null”表示该列的属性不能为空，定义时紧跟数据类型的后面；“Unique”表示该列的属性是唯一标识的（即不能取重复值 ） ，定义时可以紧跟数据类型的后面，也可以放在最后面，这时的格式是：Unique（ 列名， 列名.）;“Constraint 主键约束名Primary Key（属性组）”表示该属性组是表的主键，能唯一标识记 录。 定义参照完整性通常采用： Constraint 参照约束名 Foreign Key 参照表 （ 属性组 ）R

7、eferences 被参照 表（属性组）定义用户自定义完整性通常采用：Constraint自定义约束名Check（条件）例如：某表R1（a1，a2, a3），其中a1, a2, a3都是int型。现约束要求a1+a2v 100,则用户自定义完整性定义是：ConstraintC1Check（a1+a2 v 100）其中“ Constraint 约束名”可以省略。 关于定义视图的一般格式如下：Create Viewv视图名（v列名,v列名.）Aav子查询with Check Option 注意： 其中的查询可以是任意复杂的 Select 语句，但通常不允许含有 Order by 子句和 Disti

8、nct 短语。 （2）With Check Option 表示对视图进行 Update， Insert 和 Delete 操作时要保证更新、插入或删除的行满足视图定义中的谓词条 件（即子查询中的条件表达式）。对视图的查询、更新操作，它的执行过程是首先把这个SQL语句与定义这个视图的SQL语句合并起来，转换成一个新的SQL语句，然后才真正的执行。SQL语言还提供了一些常用的统计函数，如：Count用来统计元组个数，Sum用来计算一列值的总和，Avg用来计算一列值的平均值， Max用来求一列值中的最大值，Min用来求一列值中的最小值，等等。SQL语言的查询优化在数据库系统中有着非常重要的地位，同时

9、也是考试的一个难点，能反映出考生能否比较熟练地掌握SQL语言，做这种类型的题目有一些技巧。一般来说，如果查询中采用了查询嵌套，特别是自我连接的那种类型，优化的原 则是尽量采用不嵌套的的 SQL语句来实现相同的功能；当有选择运算时，应尽可能让它先做：在执行连接前注意对关系做适当的预处理，比如在联接的属性上建立索引和对关系排序，然后再执行联接。问题1从试题描述可以看岀，在“部门”关系中，“部门号”是唯一标识记录的，是该关系的主键，可采用（部门号CharPrimary Key） 来定义。在“职工”关系中，“职工号”是唯一标识记录的，因此它是主键，可 以定义为：UNIQUE职工号）或Constrain

10、t PF PER Primary Key（ 职工号）。显然，“负责人代码”是“部 门”关系的外键，通过“负责人代码”等于“职工号”来关联“职工”关系的，定义为：ConstraintFK_DEPT Foreign Key（负责人代码References职工（职工号）。同时“部门号”是“职工”关系的外键， 定义为：Constraint FK_PERDEPT Foreign Key（ 部门号）References 部门（部门号）。 由于表22-1中告诉 我们在“职工”关系中的月工资有个约束：500元w月工资w 5000元。它属于用户自定义完整性约束，可以定义为：Check（月工资Between 50

11、0 And 5000）。通过对题目分析，建立该视图，要采用到集函数和记 录分组语句，采用Count来计算部门的人数，用Sum来计算工资总数，用Avg来计算平均工资。然后用“ Group by部门号”来对不同部门进行分组。创建D_S视图的SQL语句如下：Create View D_S（D ，C. Totals，Averages） AS （Select 部门号，Count （*）， sum （月工资），Avg（月工资）From 职工 Group by 部门号）（2）.【问题2】对于表22-2、表22-3所示的“职工”和“部门”关系，请指出下列各行是否可 以插入，为什么？（分数：3.00 ）正确答案

12、：（）解析：（1）不能插入。它违反了实体完整性原则， 因为其主键属性值已经存在。（2）可以插入。尽管部门号、 电话和办公室为空，但是它表示该职工暂时还没有分配到某个部门。（3）不能插入。它违反了参照完整性。因为6在关系“部门”中不存在。本题主要考查完整性定义的约束性。先看看第一条记录，它的职工号是1001，在表22-2中已经存在该职工号的记录。因为“职工号”是“职工”关系的主键，它在表中不能 重复岀现，否则破坏了实体的完整性。因此该条记录不能插入。在第二条记录中职工号没有重复，同时它可以先不录入部门号（表示是新职工，暂时还没有分配部门 ），因为在“职工”关系中“部门号”是外键， 在定义中也没有

13、约束它不能为空。因此该记录可以插入。最后一条记录中，部门号是 6，但是在“部门”关系中没有找到“部门号”是 6的记录，因此不能做插入操作。否则，就违反了参照完整性规则。（3）.【问题3】在问题1定义的视图D_S上，下面哪个查询或更新是允许执行的，为什么 ？（1）Update D_S set D=3 where D=4；（2）Delete from D_S where C > 4；（3）Select D ，Averages from D_Swhere C > （Select C from D_S where D=:dept） ；（4）Select D ，C from D_S wher

14、e Totals > 10000；（5）Select* from D_S（分数：3.00 ） 正确答案：（）解析：（1）和（2）都不能执行，因为使用分组和聚集函数定义的视图是不可更新的。（3）不一定能执行，具体要看视图的返回值的情况。（4）和（5）可以执行，因为给出的 SQL语句与定义D_S视图的SQL语句合并起来验证有效。 做这种类型的题目时，只要把题目给岀的SQL语句与定义该视图的 SQL语句合并起来验证是否有效即可。在问题1的分析中，我们已经求出了定义该视图的SQL语句如下：Create View D_S（D ， C,Totals ， Averages）AS （Select 部门号

15、，Count （职工号），SUN（月工资），AVG（月工资）From 职工 Group by部门号）（1）合并结果为：Update职工Set部门号=3 Where部门号=4 Group by 部门号。因为 Where中不能包括 Group 聚合函数，因此不能执行。 （2） 合并结果为： Delete From 职工 Where Count（ 职工号 ） 4 Group by 部门号，因此也不能执行。（3）这种要看视图的返回值的情况。因此不一定能执行。（4）可以。 （5） 显然该语句能执行。（4）.【问题 4】查询每个部门中月工资最高的“职工号”的SQL查询语句如下：Select 职工号 fro

16、m 职工 EWhere月工资=（Select Max（月工资）from 职工 as Mwhere M.部门号=E.部门号）；（1）请用 30 字以内文字简要说明该查询语句对查询效率的影响。（2）对该查询语句进行修改，使它既可以完成相同功能，又可以提高查询效率。（分数： 3.00 ） 正确答案： （）解析：（1）对于外层的“职工”关系 E中的每一个元组，都要对内层的整个“职工”关系M进行检索，因此查询效率不高。（2）本题可以有两种解法：解答一： 改正后的SQL语句使用了临时表：Select Max（月工资）as最高工资，部门号 Into TempFrom职工Group by部门号Select职工

17、号 From职工，TempWhere 月工资=最高工资And职工.部门号=Temp.部门号； 解答二：Select职工号From职工，（Select Max（月 工资）As最高工资，部门号 Group by部门号 As depMaxWhere月工资=最高工资 And职工.部门号=depMax. 部门号；因为该SQL语句用了查询嵌套和聚集函数，所以这种方式的查询效率会受到很大的影响。可以把 它改成：Select Max（月工资）As maxgz，部门号Into Temp From 职工Group by 部门号Select 职工号 From职工，Temp Where月工资=maxgz And职工.

18、部门号=Temp.部门号；（5）.【问题 5】 假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引， 如下的 Select 查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写 出既可以完成相同功能又可以提高查询效率的SQL语句。Select 姓名，年龄，月工资 from 职工where 年龄45 or 月工资 v 1000；（分数： 3.00 ） 正确答案： （）解析： Select 姓名，年龄，月工资 From 职工 Where 年龄 45 UNION Select 姓名，年龄，月工资 From 职工Where月工资v 1000；因为该SQL语句用了查询嵌套和聚集函数

19、，所以这种方式的查询效率会受到很大的影响。可以把它改成：Select Max（月工资）As maxgz，部门号Into Temp From 职工Group by 部门号Select职工号From职工，TempWhere月工资=maxgzAnd职工.部门号=Temp.部门号； 在语句Select 姓名，年龄。月工资 From职工Where年龄 45 or月工资v 1000；中，由于使用了条件“ Or”，查询 的时候变成了对全表的扫描，不会促使查询优化器使用索引，从而降低了查询效率。改正的方法是去掉“Or”，修改后的 SQL语句如下：Select 姓名，年龄，月工资 From职工 Where年龄

20、45 UNION Select 姓名，年龄，月工资 From 职工 Where月工资v 1000；二、B 试题二 /B（ 总题数： 1，分数： 15.00）阅读下列说明，回答问题 1 至问题 5。【说明】某仓储超市采用 POS（Point of Sale） 收银机负责前台的销售收款， 为及时掌握销售信息， 并依此指导进货， 拟建立商品进、销、存数据库管理系统。该系统的需求分析已经基本完成，下面将进入概念模型的设计。【需求分析结果】1 销售业务由POSI攵银机来辅助实现。POS机外接条码阅读器，结账时收银员将商品的条码通过阅读输入 器输入POS机中。所售商品数量默认值为 1，可以由收银员修改。P

21、OS机根据输入的商品信息，打印出图 22-1所示的购物清单。2将经销的商品分为直销商品和库存商品两大类。直销商品的保质期较短，如食品类，由供应商直接送达 超市，管理员将过期的商品返还给供应商处理；库存商品由采购员向供应商提交订购单，供应商根据订购 单送货。超市会不定期对库存商品按照折扣率进行打优惠。直销商品和库存商品的送货单样表分别如图22-2、图22-3所示，其中直销商品生产批号的前六位表示生产日期22-4所示。4.业务处理过程：由POS机存储每一笔销售记录,在每个工作日结束前汇总当日各商品的销售量至中心数据库（销售日汇总）：根据当日的销售日汇总更新存货表；每笔进货记入进货表中，并及时更新存

22、货表。 【概念模型设计】根据需求阶段收集的信息，设计的实体联系图和关系模式（不完整）如下：1 实体联系图（如图22-5所示）2关系模式销售详单（销售流水号，商品编码，数量，金额，收银员，时间）销售日汇总（日期，商品编码，数量）存货表（商品编码，数量）进货表（送货号码，商品编码，数量，日期 ）商品（U （b） /U）4.业务处理过程：由POS机存储每一笔销售记录， 在每个工作日结束前汇总当日各商品的销售量至中心数 据库（销售日汇总）：根据当日的销售日汇总更新存货表；每笔进货记入进货表中，并及时更新存货表。【概念模型设计】根据需求阶段收集的信息，设计的实体联系图和关系模式（不完整）如下：1 实体联

23、系图（如图22-5所示）2关系模式销售详单（销售流水号，商品编码，数量，金额，收银员，时间）销售日汇总（日期，商品编码，数量）存货表（商品编码，数量）进货表（送货号码，商品编码，数量，日期 ）商品（U （b） /U）（分数：15.00 ）（1）.【问题1】对直销商品和库存商品进行概括，给出超类和子类，填入图22-5中（a）处所示的虚线框内，并补充联系。【问题1】对直销商品和库存商品进行概括，给出超类和子类，填入图22-5中（a）处所示的虚线框内，并补充联系。（分数：3.00）正确答案：（）解析：见图22-6。试题2分析这是道关于数据库设计方面的试题，主要考查考生在数据库设计中的概 念结构设计与

24、逻辑结构设计方面的知识。所谓概念结构设计，就是将需求分析得到的用户需求抽象为信息结构，它是整个数据库设计的关键。在这个过程中有个很重要的环节，就是设计E-R图。在概念设计阶段中，数据抽象是对实际的人，物，事和概念进行人为处理，抽取所关心的共同特性。有三种抽象形式，分 别是分类，聚集和概括。其中概括是定义类型之间的一种子集联系，其重要性质是继承性。也就是说子类 继承了超类上定义的所有抽象。例如学生是实体型，本科生，研究生也是实体型，本科生和研究生是学生的子集。则学生称为超类，本科生和研究生称为学生的子类。本科生和研究生继承了学生类型的属性。当 然，子类可以增加自己的某些特殊属性。概念结构设计是独

25、立于任何一种数据模型的信息结构。而逻辑结构设计的任务是把概念结构设计阶段设计奸的基本E-R图转换为与选用DBM产品所支持的数据模型相符合的逻辑结构。问题1解答此类题目的关键是要能理解题目中处理的信息，以及它们之间的关系。在题 目中告诉了我们，将经销的商品分为直销商品和库存商品两大类。因此在E-R图中商品是超类，而直销商品和库存商品是子类。而且在 E-R图中销售详单、销售日汇总、存货表和进货表中的商品编号属性都必须能在商品表中找到。即它们与商品的关系分别是N:l、N:l、1:1和N:l。因此，E-R图如图22-6所示。（2）.【问题2】根据你的实体联系图，完成（b）处的商品关系模式，并增加子类型

26、的实体关系模（分数：3.00）正确答案：（）解析：商品（商品编码，商品名称，供应商，价格）直销商品（商品编码，生产批号，消费期限）库存商品（商 品编码，折扣率）根据问题1的分析，同时从图22-1，图22-2和图22-3可以看出，商品包括了商品编码， 商品名称和价格属性。商品（商品编码，商品名称，供应商，价格 ）因为直销商品还加上生产批号，消费期限，因此，直销商品的关系模式是：直销商品（商品编码，生产批号，消费期限 ）题目中该系统对库存商品会不定期按照折扣率进行打折优惠。因此可以看岀，库存商品还有价格折扣率这个字段，即可知道库 存商品的关系模式是：库存商品（商品编码，价格折扣率）（3）.【问题3

27、】对所有关系模式，以下划线指出各关系模式的主键。（分数：3.00 ）正确答案：（）解析：销售详单（U销售流水号，商品编号/U，数量，金额，收银员，时间）销售日汇总（U日 期，商品编码/U，数量）存货表（U商品编码/U，数量）进货表（U送货号码，商品编码/U: 数量，日期）商品（U商品编码/U，商品名称，供应商，价格）直销商品（U商品编码，生产批 号/U，消费期限）库存商品（U商品编号/U，折扣率）这是一道关于确定关系模式中的主键的 问题。此种类型的题目比较简单，只要对题意有大致的了解，就可以很容易地解答。通过图22-1可以知道，销售详单中通过销售流水号和商品编号就可以确定其他属性值了。销售日汇

28、总表是通过从每个工作日结束 前汇总当日各商品的销售量情况得来的，很显然，在销售日汇总关系中只有（日期，商品编号）才能唯一决定记录，因此（日期，商品编号）是主键。存货表记录每种商品的库存数量，它的主键是商品编号。从图22-2和图22-3可以知道，有了送货号码和商品编号就可以确定某条记录的其他信息，如：数量，日期等。.【问题4】如果将商品信息只存储在中心数据库中，与在各POS上存储其备份相比，从前台销售效率和更新商品库两方面论述各自的优缺点（不超过300字）。（分数：3.00）正确答案：（）解析：第一种方式：采用商品信息集中存储在中心数据库中的方式，则在销售前台的每笔计费中，都必须 从中心数据库提

29、取商品名称和单价，增加网络的负载，在业务繁忙时直接影响到前台的销售效率；同时， 如果发生网络故障，则该 POS机不能工作。 采用这种方式，如引入新的商品和修改商品价格，会及时体现 在前台的销售业务中。因此，更新商品库时，只要更新中心数据库就够了，比较快捷。第二种方式：采用商品信息存储在中心数据库中的方式，各POS机存储商品表的备份，POS机直接从本地读取商品信息，减少于网络的负载，可以提高交易的效率；同时即使有短时间的网络故障，也不影响该POS机的正常使用，只有当存在商品信息变更时才需要与中心数据库同步。采用这种方式时，必须在每次商品信息变更时同步各POS机的数据。因此，前台销售访问本机数据，

30、访问速度快，销售效率高。但是在更新商品库时，需要 同时更新中心数据库和各 POS机备份，速度比较低。 本题涉及数据存储分布的问题，可以通过各种性能分 析来决定采用的存储分布缸略。如果将商品信息只存储在中心数据库中，则POS攵银机每处理一个业务都要从中心数据库中存取数据，这样可以保持数据的一致性，在中心计算机可以集中有效地管理，更新商品 库时比较方便。但是，前台的 POS攵银机是通过网络来实现数据的访问的，这会影响数据的访问速度，在 一定程度上给前台处理效率带来影响。若采用在各个POS机上存储数据库的备份，这样 POS机访问的是本机的数据，速度比较快，前台的销售效率高。但是，在更新商品库的同时没

31、有保证整体数据的一致性和有 效性。比如：该超市某天进了一些新的商品，放在柜台上，同时在当天有客户购买这种商品，准备在前台 付款，但是前台的POS机通过条码阅读器不能检测到该商品。原因很简单，因为POS机访问的是本机上的数据，这个数据是在录入新商品之前从中心计算机的数据库备份下来的。这样就岀现了超市有新来的商品 但当天又不能出售的现象。另外一种情况是，如果某POS机出售了一个商品，则必须更新所有POS机的备份，否则数据也会不一致。（5）.【问题5】如果考虑引入积分卡，根据累积消费金额计算积分点，再根据和分点在顾客购物 时进行现金返还，并修改顾客的累积消费金额和积分点。 请给出新增加的积分卡 关系

32、模式，并对销售详单关系模式进行修正，指出修正后关系模式和新增关系模 式的候选键和外键。【问题5】如果考虑引入积分卡，根据累积消费金额计算积分点，再根据和分点在顾客购物 时进行现金返还，并修改顾客的累积消费金额和积分点。 请给出新增加的积分卡 关系模式，并对销售详单关系模式进行修正，指出修正后关系模式和新增关系模 式的候选键和外键。（分数：3.00 ）销售详单（U销售流水号，商品编码/U，数正确答案：（）解析：对销售详单做如下的修改，增加积分卡号属性:）2 增加积分卡关系积分卡（U积分卡号/U，累积消费金额,量，金额，收银员，时间，积分点）在大部分大型超市中，都有积分卡活动这个促销机制，它通过该

33、卡的唯一编号来区别其他同超市 的积分卡，相信读者对这个不会陌生吧。也就是说，它的价值跟消费者在这个超市消费的程度有关，消费越多，它的积分点就越高，即其价值就越高。因此它的关系模式是积分卡(积分卡编号，累积消费金额，积分点)，其中积分卡编号是主键。 在销售详单中把积分卡与当次消费的销售流水号绑定，这样才可以保证该 积分卡在本次消费后增加积分或根据积分的多少进行现金返还。因此它的关系模式是销售详单(销售流水号,商品编码，数量，金额，收银员，时间，积分卡编号)，其中销售流水号是主键，积分卡编号是外键。三、B试题三/B( 总题数：3,分数：60.00)15.00)(1) .【问题1】根据上述说明，由S

34、QL定义的“职工”和“部门”的关系模式， 以及统计各部门 的人数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(c) /U(部门号)，CHECK(U (d) /U

35、)；Create View D_S(D， C, Totals ，Averages) As(Select 部门号，U (e) /Ufrom职工U (f) /U(分数：3.00) 正确答案：()解析：a. Primary Key b. Constraint FK_DEP'Foreign Key(负责人代码)References 职工或 Foreign Key(负 责人代码)References 职工 c. Constraint FK_PERDEPT Foreign Key( 部门号)References 部门或 Foreign Key(部门号)References 部门 d 月工资 Bet

36、ween 500 And 5000 或月工资=500 And 月工资v =5000 e. Count(*)，Sum(月工资)，Avg(月工资)f Group by部门号试题一分析SQL语言包括数据定义、 数据查询和数据操纵，其中数据定义有表、视图和索引的定义。数据库完整性包括实体完整性、参照完整性和用户自定义完整性约束。用户自定义完整性写在列级完整性约束条件中。定义实体完整性通常采用“Not Null ”，“ Unique”，" Constraint主键约束名 Primary Key(属性组)"等。其中：“ Not Null”表示该列的属性不能为空，定义时紧跟数据类型的后面

37、；“Unique”表示该列的属性是唯一标识的(即不能取重复值)，定义时可以紧跟数据类型的后面，也可以放在最后面，这时的格式是：Unique(列名，列名.);“Constraint 主键约束名Primary Key(属性组)”表示该属性组是表的主键，能唯一标识记 录。定义参照完整性通常采用：Constraint 参照约束名 Foreign Key参照表(属性组)References 被参照表（属性组）定义用户自定义完整性通常采用：Constraint自定义约束名Check（条件）例如：某表R1（a1 ,a2, a3），其中a1, a2, a3都是int型。现约束要求a1+a2v 100,则用户自

38、定义完整性定义是：ConstraintC1Check（a1+a2 100）其中“ Constraint 约束名”可以省略。 关于定义视图的一般格式如下：Create Viewv视图名（ 列名,列名.）Aa 子查询with Check Option 注意： 其中的查询可 以是任意复杂的 Select语句，但通常不允许含有Order by子句和Distinct 短语。 With Check Option表示对视图进行Update , Insert和Delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条 件（即子查询中的条件表达式）。对视图的查询、更新操作，它的执行过程是首先把这个SQL

39、语句与定义这个视图的SQL语句合并起来，转换成一个新的SQL语句，然后才真正的执行。SQL语言还提供了一些常用的统计函数，如：Count用来统计元组个数，Sum用来计算一列值的总和，Avg用来计算一列值的平均值， Max用来求一列值中的最大值，Min用来求一列值中的最小值，等等。SQL语言的查询优化在数据库系统中有着非常重要的地位，同时也是考试的一个难点，能反映岀考生能否比较熟练地掌握SQL语言，做这种类型的题目有一些技巧。一般来说，如果查询中采用了查询嵌套，特别是自我连接的那种类型，优化的原 则是尽量采用不嵌套的的SQL语句来实现相同的功能；当有选择运算时，应尽可能让它先做：在执行连接前注意

40、对关系做适当的预处理，比如在联接的属性上建立索引和对关系排序，然后再执行联接。问题1从试题描述可以看岀，在“部门”关系中，“部门号”是唯一标识记录的，是该关系的主键，可采用（部门号CharPrimary Key）来定义。在“职工”关系中，“职工号”是唯一标识记录的，因此它是主键，可以定义为：UNIQUE职工号）或Constraint PF PER Primary Key（ 职工号）。显然，“负责人代码”是“部 门”关系的外键，通过“负责人代码”等于“职工号”来关联“职工”关系的，定义为：ConstraintFK_DEPT Foreign Key（负责人代码References职工（职工号）。同

41、时“部门号”是“职工”关系的外键， 定义为：Constraint FK_PERDEPT Foreign Key（ 部门号）References 部门（部门号）。 由于表22-1中告诉 我们在“职工”关系中的月工资有个约束：500元w月工资w 5000元。它属于用户自定义完整性约束，可以定义为：Check（月工资Between 500 And 5000）。通过对题目分析，建立该视图，要采用到集函数和记 录分组语句，采用Count来计算部门的人数，用Sum来计算工资总数，用Avg来计算平均工资。然后用“ Group by部门号”来对不同部门进行分组。创建D_S视图的SQL语句如下：Create V

42、iew D_S（D ，C. Totals，Averages） AS （Select 部门号，Count （*）， sum （月工资），Avg（月工资）From 职工 Group by 部门号）（2）.【问题2】对于表22-2、表22-3所示的“职工”和“部门”关系，请指出下列各行是否可 以插入，为什么？（分数：3.00 ）正确答案：（）解析：（1）不能插入。它违反了实体完整性原则， 因为其主键属性值已经存在。（2）可以插入。尽管部门号、 电话和办公室为空，但是它表示该职工暂时还没有分配到某个部门。（3）不能插入。它违反了参照完整性。因为6在关系“部门”中不存在。本题主要考查完整性定义的约束性。

43、先看看第一条记录，它的职工号是1001，在表22-2中已经存在该职工号的记录。因为“职工号”是“职工”关系的主键，它在表中不能 重复岀现，否则破坏了实体的完整性。因此该条记录不能插入。在第二条记录中职工号没有重复，同时它可以先不录入部门号（表示是新职工，暂时还没有分配部门 ），因为在“职工”关系中“部门号”是外键， 在定义中也没有约束它不能为空。因此该记录可以插入。最后一条记录中，部门号是 6，但是在“部门”关系中没有找到“部门号”是 6的记录，因此不能做插入操作。否则，就违反了参照完整性规则。（3）.【问题3】在问题1定义的视图D_S上，下面哪个查询或更新是允许执行的，为什么(1) Upda

44、te D_S set D=3 where D=4；(2) Delete from D_S where C > 4；(3) Select D ，Averages from D_Swhere C > (Select C from D_S where D=:dept)(4) Select D ，C from D_Swhere Totals > 10000;(5) Select* from D_S(分数： 3.00 ) 正确答案： ()解析： (1) 和(2) 都不能执行，因为使用分组和聚集函数定义的视图是不可更新的。(3) 不一定能执行，具体要看视图的返回值的情况。(4)和(5)可

45、以执行，因为给出的 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 中不能包括Gro

46、up聚合函数，因此不能执行。(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) 对该查询语句进行修改，使它既可以完成相

47、同功能，又可以提高查询效率。(分数： 3.00 ) 正确答案： ()解析：(1)对于外层的“职工”关系 E中的每一个元组，都要对内层的整个“职工”关系M进行检索，因此查询效率不高。(2)本题可以有两种解法：解答一：改正后的SQL语句使用了临时表：Select Max(月工资 )as 最高工资，部门号 Into TempFrom 职工 Group by 部门号 Select 职工号 From 职工， TempWhere 月工资=最高工资And职工.部门号=Temp.部门号； 解答二：Select职工号From职工，(Select Max(月 工资)As最高工资，部门号 Group by部门号 A

48、s depMaxWhere月工资=最高工资 And职工.部门号=depMax. 部门号；因为该SQL语句用了查询嵌套和聚集函数，所以这种方式的查询效率会受到很大的影响。可以把 它改成：Select Max(月工资)As maxgz，部门号Into Temp From 职工Group by 部门号Select 职工号 From职工，Temp Where月工资=maxgz And职工.部门号=Temp.部门号；(5) .【问题 5】 假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引， 如下的 Select 查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既可以完成相同

49、功能又可以提高查询效率的SQL语句。Select 姓名，年龄，月工资 from 职工where 年龄45 or 月工资 v 1000;(分数： 3.00 ) 正确答案： ()解析： Select 姓名，年龄，月工资 From 职工 Where 年龄>45 UNION Select 姓名，年龄，月工资 From职工Where月工资v 1000;因为该SQL语句用了查询嵌套和聚集函数，所以这种方式的查询效率会受到很大的影响。可以把它改成：Select Max(月工资)As maxgz，部门号Into Temp From 职工Group by 部 门号Select职工号From职工，TempW

50、here月工资=maxgzAnd职工.部门号=Temp.部门号； 在语句Select 姓名，年龄。月工资 From职工Where年龄45 or月工资v 1000;中，由于使用了条件“ Or”，查询 的时候变成了对全表的扫描，不会促使查询优化器使用索引，从而降低了查询效率。改正的方法是去掉“Or”，修改后的 SQL语句如下：Select 姓名，年龄，月工资 From职工 Where年龄45 UNION Select 姓名，年龄，月工资 From 职工 Where月工资v 1000;B试题二/B阅读下列说明，回答问题 1至问题5。【说明】某仓储超市采用POS（Point of Sale）收银机负责

51、前台的销售收款，为及时掌握销售信息，并依此指导进货，拟建立商品进、销、存数据库管理系统。该系统的需求分析已经基本完成，下面将进入概念模型的设计。【需求分析结果】1 销售业务由POS收银机来辅助实现。POS机外接条码阅读器，结账时收银员将商品的条码通过阅读输入 器输入POS机中。所售商品数量默认值为 1，可以由收银员修改。POS机根据输入的商品信息，打印出图 22-1所示的购物清单。2将经销的商品分为直销商品和库存商品两大类。直销商品的保质期较短，如食品类，由供应商直接送达 超市，管理员将过期的商品返还给供应商处理；库存商品由采购员向供应商提交订购单，供应商根据订购 单送货。超市会不定期对库存商

52、品按照折扣率进行打优惠。直销商品和库存商品的送货单样表分别如图22-2、图22-3所示，其中直销商品生产批号的前六位表示生产日期。3.超市的硬件拓扑结构如图22-4所示。4.业务处理过程：由POS机存储每一笔销售记录，在每个工作日结束前汇总当日各商品的销售量至中心数据库（销售日汇总）：根据当日的销售日汇总更新存货表；每笔进货记入进货表中，并及时更新存货表。【概念模型设计】根据需求阶段收集的信息，设计的实体联系图和关系模式（不完整）如下：1 实体联系图（如图22-5所示）2关系模式销售详单（销售流水号，商品编码，数量，金额，收银员，时间）销售日汇总（日期，商品编码，数量）存货表（商品编码，数量）

53、进货表（送货号码，商品编码，数量，日期）商品（U （b） /U）4.业务处理过程：由POS机存储每一笔销售记录， 在每个工作日结束前汇总当日各商品的销售量至中心数 据库（销售日汇总）：根据当日的销售日汇总更新存货表；每笔进货记入进货表中，并及时更新存货表。【概念模型设计】根据需求阶段收集的信息，设计的实体联系图和关系模式（不完整）如下：1 实体联系图（如图22-5所示）2关系模式销售详单（销售流水号，商品编码，数量，金额，收银员，时间）销售日汇总（日期，商品编码，数量）存货表（商品编码，数量）进货表（送货号码，商品编码，数量，日期 ） 商品（U （b） /U）（分数：15.00 ）（1）.【问

54、题1】对直销商品和库存商品进行概括， 给出超类和子类，填入图22-5中（a）处所示的 虚线框内，并补充联系。【问题1】对直销商品和库存商品进行概括， 给出超类和子类，填入图22-5中（a）处所示的 虚线框内，并补充联系。（分数：3.00） 正确答案：（）解析：见图22-6。试题2分析这是道关于数据库设计方面的试题，主要考查考生在数据库设计中的概 念结构设计与逻辑结构设计方面的知识。所谓概念结构设计，就是将需求分析得到的用户需求抽象为信息结构，它是整个数据库设计的关键。在这个过程中有个很重要的环节，就是设计E-R图。在概念设计阶段中，数据抽象是对实际的人，物，事和概念进行人为处理，抽取所关心的共

55、同特性。有三种抽象形式，分 别是分类，聚集和概括。其中概括是定义类型之间的一种子集联系，其重要性质是继承性。也就是说子类 继承了超类上定义的所有抽象。例如学生是实体型，本科生，研究生也是实体型，本科生和研究生是学生的子集。则学生称为超类，本科生和研究生称为学生的子类。本科生和研究生继承了学生类型的属性。当 然，子类可以增加自己的某些特殊属性。概念结构设计是独立于任何一种数据模型的信息结构。而逻辑结构设计的任务是把概念结构设计阶段设计奸的基本E-R图转换为与选用DBM产品所支持的数据模型相符合的逻辑结构。问题1解答此类题目的关键是要能理解题目中处理的信息，以及它们之间的关系。在题目中告诉了我们，将经销的商品分为直销商品和库存商品两大类。因此在E-R图中商品是超类，而直销商品和库存商品是子类。而且在E-R图中销售详单、销售日汇总、存货表和进货表中的商品编号属性都必须能在商品表中找到。即它们与商品的关系分别是N:l、N:l