数据库系统工程师数据库综合设计Word文档下载推荐.docx
《数据库系统工程师数据库综合设计Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《数据库系统工程师数据库综合设计Word文档下载推荐.docx（28页珍藏版）》请在冰点文库上搜索。
26
1000
1
8001234
主楼201
1002
王平
27
1100
2001
王晓华
38
1300
2
8001235
1号楼302
2002
李力
24
800
8001236
1号楼303
3001
黎远军
42
3
8001237
主楼202
4001
李源
4
8001245
2号楼102
4002
李兴民
36
1200
8001246
2号楼103
5001
赵欣
25
Null
…
{{B}}表22-3“部门”关系{{/B}}
部门名
负责人代码
任职时间
人事处
2004-8-3
机关
2003-8-3
销售科
生产科
2003-6-1
5
车间
（分数：
（1）.
【问题1】
根据上述说明，由SQL定义的“职工”和“部门”的关系模式，以及统计各部门的人数C、工资总数Totals、平均工资Averages的D_S视图如下所示，请在空缺处填入正确的内容。
CreateTable部门（部门号Char
（1）{{U}}（a）{{/U}}
部门名Char（16）,
负责人代码Char（4）,
任职时间DATE,
{{U}}（b）{{/U}}（职工号））；
CreateTable职工（职工号Char（4），
姓名Char（8），
年龄NUMDER（3），
月工资NUMDER（4），
部门号Char
（1），
电话Char（8），
办公室Char（8），
{{U}}（a）{{/U}}（职工号），
{{U}}（c）{{/U}}（部门号），
CHECK（{{U}}（d）{{/U}}））；
CreateViewD_S（D，C，Totals，Averages）As
（Select部门号，{{U}}（e）{{/U}}
from职工
{{U}}（f）{{/U}}
3.00）
__________________________________________________________________________________________
正确答案：
（）
解析：
a.PrimaryKeyb.ConstraintFK_DEPTForeignKey（负责人代码）References职工或ForeignKey（负责人代码）References职工c.ConstraintFK_PERDEPTForeignKey（部门号）References部门或ForeignKey（部门号）References部门d．月工资Between500And5000或月工资＞=500And月工资＜=5000e．Count（*），Sum（月工资），Avg（月工资）f．Groupby部门号[试题一分析]SQL语言包括数据定义、数据查询和数据操纵，其中数据定义有表、视图和索引的定义。
数据库完整性包括实体完整性、参照完整性和用户自定义完整性约束。
用户自定义完整性写在列级完整性约束条件中。
定义实体完整性通常采用“NotNull”，“Unique”，“Constraint主键约束名PrimaryKey（属性组）”等。
其中：
·
“NotNull”表示该列的属性不能为空，定义时紧跟数据类型的后面；
“Unique”表示该列的属性是唯一标识的（即不能取重复值），定义时可以紧跟数据类型的后面，也可以放在最后面，这时的格式是：
Unique（列名，[列名]...）；
“Constraint主键约束名PrimaryKey（属性组）”表示该属性组是表的主键，能唯一标识记录。
定义参照完整性通常采用：
Constraint参照约束名ForeignKey参照表（属性组）References被参照表（属性组）定义用户自定义完整性通常采用：
Constraint自定义约束名Check（条件）例如：
某表R1（a1，a2，a3），其中a1，a2，a3都是int型。
现约束要求a1+a2＜100，则用户自定义完整性定义是：
ConstraintC1Check（a1+a2＜100）其中“Constraint约束名”可以省略。
关于定义视图的一般格式如下：
CreateView＜视图名＞[（＜列名＞[，＜列名＞]...）]Aa＜子查询＞[withCheckOption]注意：
（1）其中的查询可以是任意复杂的Select语句，但通常不允许含有Orderby子句和Distinct短语。
（2）WithCheckOption表示对视图进行Update，Insert和Delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。
对视图的查询、更新操作，它的执行过程是首先把这个SQL语句与定义这个视图的SQL语句合并起来，转换成一个新的SQL语句，然后才真正的执行。
SQL语言还提供了一些常用的统计函数，如：
Count用来统计元组个数，Sum用来计算一列值的总和，Avg用来计算一列值的平均值，Max用来求一列值中的最大值，Min用来求一列值中的最小值，等等。
SQL语言的查询优化在数据库系统中有着非常重要的地位，同时也是考试的一个难点，能反映出考生能否比较熟练地掌握SQL语言，做这种类型的题目有一些技巧。
一般来说，如果查询中采用了查询嵌套，特别是自我连接的那种类型，优化的原则是尽量采用不嵌套的的SQL语句来实现相同的功能；
当有选择运算时，应尽可能让它先做：
在执行连接前注意对关系做适当的预处理，比如在联接的属性上建立索引和对关系排序，然后再执行联接。
[问题1]从试题描述可以看出，在“部门”关系中，“部门号”是唯一标识记录的，是该关系的主键，可采用（部门号Char
（1）PrimaryKey）来定义。
在“职工”关系中，“职工号”是唯一标识记录的，因此它是主键，可以定义为：
UNIQUE（职工号）或ConstraintPFPERPrimaryKey（职工号）。
显然，“负责人代码”是“部门”关系的外键，通过“负责人代码”等于“职工号”来关联“职工”关系的，定义为：
ConstraintFK_DEPTForeignKey（负责人代码）References职工（职工号）。
同时“部门号”是“职工”关系的外键，定义为：
ConstraintFK_PERDEPTForeignKey（部门号）References部门（部门号）。
由于表22-1中告诉我们在“职工”关系中的月工资有个约束：
500元≤月工资≤5000元。
它属于用户自定义完整性约束，可以定义为：
Check（月工资Between500And5000）。
通过对题目分析，建立该视图，要采用到集函数和记录分组语句，采用Count来计算部门的人数，用Sum来计算工资总数，用Avg来计算平均工资。
然后用“Groupby部门号”来对不同部门进行分组。
创建D_S视图的SQL语句如下：
CreateViewD_S（D，C．Totals，Averages）AS（Select部门号，Count（*），sum（月工资），Avg（月工资）From职工Groupby部门号）
（2）.
【问题2】
对于表22-2、表22-3所示的“职工”和“部门”关系，请指出下列各行是否可以插入，为什么?
（1）不能插入。
它违反了实体完整性原则，因为其主键属性值已经存在。
（2）可以插入。
尽管部门号、电话和办公室为空，但是它表示该职工暂时还没有分配到某个部门。
（3）不能插入。
它违反了参照完整性。
因为6在关系“部门”中不存在。
本题主要考查完整性定义的约束性。
先看看第一条记录，它的职工号是1001，在表22-2中已经存在该职工号的记录。
因为“职工号”是“职工”关系的主键，它在表中不能重复出现，否则破坏了实体的完整性。
因此该条记录不能插入。
在第二条记录中职工号没有重复，同时它可以先不录入部门号（表示是新职工，暂时还没有分配部门），因为在“职工”关系中“部门号”是外键，在定义中也没有约束它不能为空。
因此该记录可以插入。
最后一条记录中，部门号是6，但是在“部门”关系中没有找到“部门号”是6的记录，因此不能做插入操作。
否则，就违反了参照完整性规则。
（3）.
【问题3】
在问题1定义的视图D_S上，下面哪个查询或更新是允许执行的，为什么?
（1）UpdateD_SsetD=3whereD=4；
（2）DeletefromD_SwhereC＞4；
（3）SelectD，AveragesfromD_S
whereC＞（SelectCfromD_SwhereD=:
dept）；
（4）SelectD，CfromD_S
whereTotals＞10000；
（5）Select*fromD_S
（1）和
（2）都不能执行，因为使用分组和聚集函数定义的视图是不可更新的。
（3）不一定能执行，具体要看视图的返回值的情况。
（4）和（5）可以执行，因为给出的SQL语句与定义D_S视图的SQL语句合并起来验证有效。
做这种类型的题目时，只要把题目给出的SQL语句与定义该视图的SQL语句合并起来验证是否有效即可。
在问题1的分析中，我们已经求出了定义该视图的SQL语句如下：
CreateViewD_S（D，C，Totals，Averages）AS（Select部门号，Count（职工号），SUN（月工资），AVG（月工资）From职工Groupby部门号）
（1）合并结果为：
Update职工Set部门号=3Where部门号=4Groupby部门号。
因为Where中不能包括Group聚合函数，因此不能执行。
（2）合并结果为：
DeleteFrom职工WhereCount（职工号）＞4Groupby部门号，因此也不能执行。
（3）这种要看视图的返回值的情况。
因此不一定能执行。
（4）可以。
（5）显然该语句能执行。
（4）.
【问题4】
查询每个部门中月工资最高的“职工号”的SQL查询语句如下：
Select职工号from职工E
Where月工资=（SelectMax（月工资）
from职工asM
whereM.部门号=E.部门号）；
（1）请用30字以内文字简要说明该查询语句对查询效率的影响。
（2）对该查询语句进行修改，使它既可以完成相同功能，又可以提高查询效率。
（1）对于外层的“职工”关系E中的每一个元组，都要对内层的整个“职工”关系M进行检索，因此查询效率不高。
（2）本题可以有两种解法：
解答一：
改正后的SQL语句使用了临时表：
SelectMax（月工资）as最高工资，部门号IntoTempFrom职工Groupby部门号Select职工号From职工，TempWhere月工资=最高工资And职工.部门号=Temp.部门号；
解答二：
Select职工号From职工，（SelectMax（月工资）As最高工资，部门号Groupby部门号AsdepMaxWhere月工资=最高工资And职工.部门号=depMax.部门号；
因为该SQL语句用了查询嵌套和聚集函数，所以这种方式的查询效率会受到很大的影响。
可以把它改成：
SelectMax（月工资）Asmaxgz，部门号IntoTempFrom职工Groupby部门号Select职工号From职工，TempWhere月工资=maxgzAnd职工.部门号=Temp.部门号；
（5）.
【问题5】
假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引，如下的Select查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既可以完成相同功能又可以提高查询效率的SQL语句。
Select姓名，年龄，月工资from职工
where年龄＞45or月工资＜1000；
Select姓名，年龄，月工资From职工Where年龄＞45UNIONSelect姓名，年龄，月工资From职工Where月工资＜1000；
在语句Select姓名，年龄。
月工资From职工Where年龄＞45or月工资＜1000；
中，由于使用了条件“Or”，查询的时候变成了对全表的扫描，不会促使查询优化器使用索引，从而降低了查询效率。
改正的方法是去掉“Or”，修改后的SQL语句如下：
Select姓名，年龄，月工资From职工Where年龄＞45UNIONSelect姓名，年龄，月工资From职工Where月工资＜1000；
二、{{B}}试题二{{/B}}（总题数：
某仓储超市采用POS（PointofSale）收银机负责前台的销售收款，为及时掌握销售信息，并依此指导进货，拟建立商品进、销、存数据库管理系统。
该系统的需求分析已经基本完成，下面将进入概念模型的设计。
【需求分析结果】
1．销售业务由POS收银机来辅助实现。
POS机外接条码阅读器，结账时收银员将商品的条码通过阅读输入器输入POS机中。
所售商品数量默认值为1，可以由收银员修改。
POS机根据输入的商品信息，打印出图22-1所示的购物清单。
2．将经销的商品分为直销商品和库存商品两大类。
直销商品的保质期较短，如食品类，由供应商直接送达超市，管理员将过期的商品返还给供应商处理；
库存商品由采购员向供应商提交订购单，供应商根据订购单送货。
超市会不定期对库存商品按照折扣率进行打优惠。
直销商品和库存商品的送货单样表分别如图22-2、图22-3所示，其中直销商品生产批号的前六位表示生产日期。
3．超市的硬件拓扑结构如图22-4所示。
4．业务处理过程：
由POS机存储每一笔销售记录，在每个工作日结束前汇总当日各商品的销售量至中心数据库（销售日汇总）：
根据当日的销售日汇总更新存货表；
每笔进货记入进货表中，并及时更新存货表。
【概念模型设计】
根据需求阶段收集的信息，设计的实体联系图和关系模式（不完整）如下：
1．实体联系图（如图22-5所示）
2．关系模式
销售详单（销售流水号，商品编码，数量，金额，收银员，时间）
销售日汇总（日期，商品编码，数量）
存货表（商品编码，数量）
进货表（送货号码，商品编码，数量，日期）
商品（{{U}}（b）{{/U}}）
对直销商品和库存商品进行概括，给出超类和子类，填入图22-5中（a）处所示的虚线框内，并补充联系。
见图22-6。
[试题2分析]这是道关于数据库设计方面的试题，主要考查考生在数据库设计中的概念结构设计与逻辑结构设计方面的知识。
所谓概念结构设计，就是将需求分析得到的用户需求抽象为信息结构，它是整个数据库设计的关键。
在这个过程中有个很重要的环节，就是设计E-R图。
在概念设计阶段中，数据抽象是对实际的人，物，事和概念进行人为处理，抽取所关心的共同特性。
有三种抽象形式，分别是分类，聚集和概括。
其中概括是定义类型之间的一种子集联系，其重要性质是继承性。
也就是说子类继承了超类上定义的所有抽象。
例如学生是实体型，本科生，研究生也是实体型，本科生和研究生是学生的子集。
则学生称为超类，本科生和研究生称为学生的子类。
本科生和研究生继承了学生类型的属性。
当然，子类可以增加自己的某些特殊属性。
概念结构设计是独立于任何一种数据模型的信息结构。
而逻辑结构设计的任务是把概念结构设计阶段设计奸的基本E-R图转换为与选用DBMS产品所支持的数据模型相符合的逻辑结构。
[问题1]解答此类题目的关键是要能理解题目中处理的信息，以及它们之间的关系。
在题目中告诉了我们，将经销的商品分为直销商品和库存商品两大类。
因此在E-R图中商品是超类，而直销商品和库存商品是子类。
而且在E-R图中销售详单、销售日汇总、存货表和进货表中的商品编号属性都必须能在商品表中找到。
即它们与商品的关系分别是N:
I、N:
I、1:
1和N:
I。
因此，E-R图如图22-6所示。
根据你的实体联系图，完成（b）处的商品关系模式，并增加子类型的实体关系模式。
商品（商品编码，商品名称，供应商，价格）直销商品（商品编码，生产批号，消费期限）库存商品（商品编码，折扣率）根据问题1的分析，同时从图22-1，图22-2和图22-3可以看出，商品包括了商品编码，商品名称和价格属性。
商品（商品编码，商品名称，供应商，价格）因为直销商品还加上生产批号，消费期限，因此，直销商品的关系模式是：
直销商品（商品编码，生产批号，消费期限）题目中该系统对库存商品会不定期按照折扣率进行打折优惠。
因此可以看出，库存商品还有价格折扣率这个字段，即可知道库存商品的关系模式是：
库存商品（商品编码，价格折扣率）
对所有关系模式，以下划线指出各关系模式的主键。
销售详单（{{U}}销售流水号，商品编号{{/U}}，数量，金额，收银员，时间）销售日汇总（{{U}}日期，商品编码{{/U}}，数量）存货表（{{U}}商品编码{{/U}}，数量）进货表（{{U}}送货号码，商品编码{{/U}}，数量，日期）商品（{{U}}商品编码{{/U}}，商品名称，供应商，价格）直销商品（{{U}}商品编码，生产批号{{/U}}，消费期限）库存商品（{{U}}商品编号{{/U}}，折扣率）这是一道关于确定关系模式中的主键的问题。
此种类型的题目比较简单，只要对题意有大致的了解，就可以很容易地解答。
通过图22-1可以知道，销售详单中通过销售流水号和商品编号就可以确定其他属性值了。
销售日汇总表是通过从每个工作日结束前汇总当日各商品的销售量情况得来的，很显然，在销售日汇总关系中只有（日期，商品编号）才能唯一决定记录，因此（日期，商品编号）是主键。
存货表记录每种商品的库存数量，它的主键是商品编号。
从图22-2和图22-3可以知道，有了送货号码和商品编号就可以确定某条记录的其他信息，如：
数量，日期等。
如果将商品信息只存储在中心数据库中，与在各POS机上存储其备份相比，从前台销售效率和更新商品库两方面论述各自的优缺点（不超过300字）。
第一种方式：
采用商品信息集中存储在中心数据库中的方式，则在销售前台的每笔计费中，都必须从中心数据库提取商品名称和单价，增加网络的负载，在业务繁忙时直接影响到前台的销售效
升级会员 