问答题
问答题
| 【问题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)， 部门号 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}} |
【正确答案】
【答案解析】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 月工资＜=5000
e．Count(*)，Sum(月工资)，Avg(月工资)
f．Group by 部门号
[试题一分析]
SQL语言包括数据定义、数据查询和数据操纵，其中数据定义有表、视图和索引的定义。
数据库完整性包括实体完整性、参照完整性和用户自定义完整性约束。用户自定义完整性写在列级完整性约束条件中。定义实体完整性通常采用“Not Null”，“Unique”，“Constraint主键约束名Primary Key(属性组)”等。其中：
·“Not Null”表示该列的属性不能为空，定义时紧跟数据类型的后面；
·“Unique”表示该列的属性是唯一标识的(即不能取重复值)，定义时可以紧跟数据类型的后面，也可以放在最后面，这时的格式是：Unique(列名，[列名]...)；
·“Constraint主键约束名Primary Key(属性组)”表示该属性组是表的主键，能唯一标识记录。
定义参照完整性通常采用：
Constraint参照约束名Foreign Key参照表(属性组)References被参照表(属性组)定义用户自定义完整性通常采用：
Constraint自定义约束名Check(条件)
例如：某表R1(a1，a2，a3)，其中a1，a2，a3都是int型。现约束要求a1+a2＜100，则用户自定义完整性定义是：
Constraint C1 Check(a1+a2＜100)
其中“Constraint约束名”可以省略。
关于定义视图的一般格式如下：
Create View＜视图名＞[(＜列名＞[，＜列名＞]...)]
Aa＜子查询＞
[with Check Option]
注意：
(1)其中的查询可以是任意复杂的Select语句，但通常不允许含有Order by子句和Distinct短语。
(2)With Check Option表示对视图进行Update，Insert和Delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。
对视图的查询、更新操作，它的执行过程是首先把这个SQL语句与定义这个视图的SQL语句合并起来，转换成一个新的SQL语句，然后才真正的执行。
SQL语言还提供了一些常用的统计函数，如：Count用来统计元组个数，Sum用来计算一列值的总和，Avg用来计算一列值的平均值，Max用来求一列值中的最大值， Min用来求一列值中的最小值，等等。
SQL语言的查询优化在数据库系统中有着非常重要的地位，同时也是考试的一个难点，能反映出考生能否比较熟练地掌握SQL语言，做这种类型的题目有一些技巧。一般来说，如果查询中采用了查询嵌套，特别是自我连接的那种类型，优化的原则是尽量采用不嵌套的的SQL语句来实现相同的功能；当有选择运算时，应尽可能让它先做：在执行连接前注意对关系做适当的预处理，比如在联接的属性上建立索引和对关系排序，然后再执行联接。
[问题1]
从试题描述可以看出，在“部门”关系中，“部门号”是唯一标识记录的，是该关系的主键，可采用(部门号Char(1)Primary Key)来定义。在“职工”关系中，“职工号”是唯一标识记录的，因此它是主键，可以定义为：UNIQUE(职工号)或Constraint PF PER Primary Key(职工号)。
显然，“负责人代码”是“部门”关系的外键，通过“负责人代码”等于“职工号”来关联“职工”关系的，定义为：Constraint FK_DEPT Foreign Key(负责人代码)References职工(职工号)。同时“部门号”是“职工”关系的外键，定义为：Constraint FK_PERDEPT Foreign Key(部门号)References部门(部门号)。
由于表22-1中告诉我们在“职工”关系中的月工资有个约束：500元≤月工资≤ 5000元。它属于用户自定义完整性约束，可以定义为：Check(月工资Between 500 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】 对于表22-2、表22-3所示的“职工”和“部门”关系，请指出下列各行是否可以插入，为什么? 
【正确答案】
【答案解析】(1)不能插入。它违反了实体完整性原则，因为其主键属性值已经存在。
(2)可以插入。尽管部门号、电话和办公室为空，但是它表示该职工暂时还没有分配到某个部门。
(3)不能插入。它违反了参照完整性。因为6在关系“部门”中不存在。
本题主要考查完整性定义的约束性。
先看看第一条记录，它的职工号是1001，在表22-2中已经存在该职工号的记录。因为“职工号”是“职工”关系的主键，它在表中不能重复出现，否则破坏了实体的完整性。因此该条记录不能插入。
在第二条记录中职工号没有重复，同时它可以先不录入部门号(表示是新职工，暂时还没有分配部门)，因为在“职工”关系中“部门号”是外键，在定义中也没有约束它不能为空。因此该记录可以插入。
最后一条记录中，部门号是6，但是在“部门”关系中没有找到“部门号”是6的记录，因此不能做插入操作。否则，就违反了参照完整性规则。
问答题
【正确答案】
【答案解析】(1)和(2)都不能执行，因为使用分组和聚集函数定义的视图是不可更新的。
(3)不一定能执行，具体要看视图的返回值的情况。
(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中不能包括Group聚合函数，因此不能执行。
(2)合并结果为：Delete From 职工Where Count(职工号)＞4 Group by 部门号，因此也不能执行。
(3)这种要看视图的返回值的情况。因此不一定能执行。
(4)可以。
(5)显然该语句能执行。
问答题
【正确答案】
【答案解析】(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 部门号 As depMax
Where 月工资=最高工资 And 职工.部门号=depMax.部门号；
因为该SQL语句用了查询嵌套和聚集函数，所以这种方式的查询效率会受到很大的影响。可以把它改成：
Select Max(月工资)As maxgz，部门号Into Temp From 职工
Group by 部门号
Select 职工号 From 职工，Temp
Where 月工资=maxgz And 职工.部门号=Temp.部门号；
问答题
【正确答案】
【答案解析】Select 姓名，年龄，月工资 From 职工
Where 年龄＞45
UNION
Select 姓名，年龄，月工资 From 职工
Where 月工资＜1000；
因为该SQL语句用了查询嵌套和聚集函数，所以这种方式的查询效率会受到很大的影响。可以把它改成：
Select Max(月工资)As maxgz，部门号Into Temp From 职工
Group by 部门号
Select 职工号 From 职工，Temp
Where 月工资=maxgz And 职工.部门号=Temp.部门号；
在语句
Select 姓名，年龄。月工资 From 职工
Where 年龄＞45 or 月工资＜1000；
中，由于使用了条件“Or”，查询的时候变成了对全表的扫描，不会促使查询优化器使用索引，从而降低了查询效率。改正的方法是去掉“Or”，修改后的SQL语句如下：
Select 姓名，年龄，月工资 From 职工
Where 年龄＞45
UNION
Select 姓名，年龄，月工资 From 职工
Where 月工资＜1000；
|