搜索
第10章数据库系统工程师级下午试题分析试题1分析参见软件设计师下午试题一分析。试题2阅读下列说明，回答问题1至问题5。说明某工厂的信息管理数据库的部分关系模式如下所示：职工(职工号，姓名，年龄，月工资，部门号，电话，办公室)部门(部门号，部门名，负责人代码，任职时间)关系模式的主要属性、含义及约束如表2—1所示，“职工”和“部门”的关系示例分别如表2-2和表2-3所示。表2-1主要属性、含义及约束属性含义和约束条件职工号惟一标记每个职工的编号，每个职工属于并且仅属于一个部门部门号惟一标识每个部门的编号，每个部门有一个负责人，且他也是一个职工月工资500元≤月工资45000元表2-2“职工”关系职工号姓名年龄月工资部门号电话办公室1001郑俊华26100018001234主楼2011002王平27110018001234主楼2012001王晓华381300280012351号楼3022002李力24800280012361号楼3033001黎远军42130038001237主楼2024001李源24800480012452号楼1024002李兴民361200480012462号楼1035001赵欣250Null┄┄表2-3“部门”关系部门号部门名负责人代码任职时间1人事处10022004-8-32机关20012004-8-33销售科4生产科40022003-6-15车间[问题1]根据上述说明，由SQL定义的“职工”和“部门”的关系模式，以及统计各部门的人数C、工资总数Totals、平均工资Averages的D_S视图如下所示，请在空缺处填入正确的内容。CreateTable部门(部门号CHAR(1)(a)，部门名CHAR(16)，负责人代码CHAR(4)，任职时间DATE，(b)(职工号))；CreateTable职工(职工号CHAR(4)，姓名CHAR(8)，年龄NUMBER(3)，月工资NUMBER(4)，部门号CHAR(1)，电话CHAR(8)，办公室CHAR(8)，(a)(职工号)，(c)(部门号)，CHECK((d)))；CreateViewD_S(D,C,Totals,Averages)As(Select部门号，(e)from职工(f))；[问题2]对于表2-2、表2-3所示的“职工”和“部门”关系，请指出下列各行是否可以插入，为什么?[问题3]在问题1定义的视图D_S上，下面哪个查询或更新是允许执行的，为什么?(1)UpdateD_SsetD-3whereD=4；(2)DeletefromD_SwhereC＞4；(3)SelectD，AveragesfromD_SwhereC＞(SelectCfromD_SwhereD=:dept)；(4)SelectD,CFromD_SwhereTotals＞10000；(5)Select*fromD_S；[问题4]查询每个部门中月工资最高的“职工号”的SQL查询语句如下：Select职工号from职工Ewhere月工资=(SelectMax(月工资)from职工asMwhereM．部门号=E．部门号)(1)请用30字以内文字简要说明该查询语句对查询效率的影响。(2)对该查询语句进行修改，使它既可以完成相同功能，又可以提高查询效率。[问题5]假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引，如下的Select查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既可以完成相同功能又可以提高查询效率的SQL语句。Select姓名，年龄，月工资from职工where年龄＞45or月工资＜1000；试题2[分析][问题1][分析]根据题意，“职工”和“部门”的关系模式如下：用SQL定义关系模式的一个非常重要的问题是完整性控制。完整性控制应具有三方面的功能：定义功能、检测功能、处理功能(一旦发现违背了完整性约束条件，采取相关的动作来保证数据的完整性)。数据库中最重要的约束是声明一个或一组属性形成关系的键。键的约束在SQL的CREATETABLE命令中声明。在关系系统中，最重要的完整性约束条件是：实体完整性和参照完整性。1．实体完整性定义在关系中只能有一个主键。声明主键有两种方法：①将PRIMARYKEY保留字加在属性类型之后。②在属性列表中引入一个新元素，该元素包含保留字PRIMARYKEY和用圆括号括起的形成该键的属性或属性组列表。2．参照完整性参照完整性定义格式如下：FOREIGNKEY(属性名)REFERENCES表名(属性名)[ONDELETE[CASCADE|SETNULL]参照完整性是通过使用如下保留字：FOREIGNKEY定义那些列为外码；REFERENCES指明外键对应于哪个表的主键；ONDELETECASCADE指明删除被参照关系的元组时，同时删除参照关系中的元组；SETNULL表示置为空值方式。本试题中，部门关系的主键为部门号，职工关系的主键为职工号。其中，部门关系的主键为部门号可采用如下两种方式定义：部门号CHAR(1)PRIMARYKEY或者是PRIMARYKEY(部门号)又因为负责人也是一个职工，所以负责人代码应该是一个外码，应进行参照完整性定义。根据分析部门的SQL定义如下：CreateTable部门(部门号CHAR(1)PRIMARYKEY，部门名CHAR(16)，负责人代码CHAR(4)，任职时间DATE，FOREIGNKEY(负责人代码)REFERENCES职工(职工号))；在职工关系中，部门号是一个外码，应进行参照完整性定义。又因为在试题表2-1中的条件“500元≤月工资≤5000元”，所以在职工关系中应加上用户定义完整性。根据分析职工的SQL定义如下：CreateTable职工(职工号CHAR(4)，姓名CHAR(8)，年龄NUMBER(3)，月工资NUMBER(4)，部门号CHAR(1)，电话CHAR(8)，办公室CHAR(8)，PRIMARYDEY(职工号)，FOREIGNKEY(部门号)REFERENCES部门(部门号)，CHECK(月工资BETWEEN500AND5000))；建立D_S视图需要COUNT函数来统计各部门的人数C，SUM来计算工资总数Totals，用AVG来计算平均工资Averages，用分组语句GROUPBY来对不同部门进行分组。因此创建D_S视图的SQL语句是：CreateViewD_S(D，C，Totals，Averages)AS(SELECT部门号，COUNT(*)，SUM(月工资)，AVG(月工资)FROM职工GROUPBY部门号)[问题2][分析]本题主要考查完整性定义的约束性。以下表是待插入的记录组。(1)由于在职工表的定义中职工号主码是惟一标识每个元组(记录)的，而(1)中的职工号是“1001”，在试题的职工关系中已经存在该职工号的记录，为了保证实体的完整性，该条记录不能插入。(2)该元组可以插入“职工”关系，尽管部门号、电话和办公室为空，但是它表示该职工暂时没有分配到某个部门。虽然职工表中部门号是外键，但在定义中也没有约束它不能为空。(3)该元组不能插入“职工'’关系，部门号是外键，而在部门关系中找不到部门号是6的元组，违反了参照完整性，所以不能做插入操作。[问题3][分析]此问考查的是视图更新必须遵循的原则。因此，需要将SQL语句与定义该视图的SQL语句结合起来考虑。由于SQL视图更新必须遵循以下规则：▲从多个基本表通过连接操作导出的视图不允许更新。▲对使用了分组、集函数操作的视图则不允许进行更新操作。▲如果视图是从单个基本表通过投影、选取操作导出的则允许进行更新操作，且语法同基本表。(1)由于D_S视图中包含分组操作，也即将D_S视图合并到UpdateD_SsetD=3whereD=4，结果为：Update职工set部门号=3where部门号=4GROUPBY部门号，在where中包括GROUP分组操作，因此不能执行。(2)同理，将D_S视图合并到DeletefromD_SwhereC＞4中，结果为：Deletefrom职工whereCOUNT(职工号)＞4GROUPBY部门号，因此不能执行。(3)对于SelectD，AveragesfromD_SwhereC＞(SelectCfromD_SwhereD=：dept)，要根据视图的返回值的情况。因此不一定能执行。(4)对于语句SelectD，CFromD_SwhereTotals＞10000可以执行。(5)对于语句Select*fromD_S显然是能执行的。[问题4][分析]此问考查的是查询效率的问题。在涉及相关查询的某些情形中，构造临时关系可以提高查询效率。(1)对于外层的职工关系E中的每一个元组，都要对内层的整个职工关系M进行检索，因此查询效率不高。(2)此问有两种解法。解答一改正后的SQL语句使用了临时表：SelectMax(月工资)as最高工资，部门号intoTempfrom职工Groupby部门号Select职工号from职工，Tempwhere月工资=最高工资and职工．部门号=Temp．部门号解答二Select职工号from职工，(SelectMax(月工资)as最高工资，部门号Groupby部门号)asdepMaxwhere月工资；最高工资and职工．部门号；depMax．部门号[问题5][分析]问题5中的Select查询语句中使用了条件or，系统在查询的时候将对全表进行扫描，不会促使查询优化器使用索引，从而降低了查询效率。改正的方法是去掉or，修改后的SQL语句如下：Select姓名，年龄，月工资from职工where年龄＞45；unionSelect姓名，年龄，月上资from职工where年龄月工资＜1000；参考答案[问题1]解答(a)PRIMARYKEY(b)FOREIGNKEY(负责人代码)REFERENCES职工(c)FOREIGNKEY(部门号)REFERENCES部门(d)月工资＞=500AND＜月工资=5000，或月工资BETWEEN500AND5000(e)count(*)，Sum(月工资)，Avg(月工资)(f)GrOupby部门号[问题2]解答(1)该行不能插入“职工”关系，它违反了实体完整性中主码必须惟一区分关系中的每一个属性。(2)该行可以插入“职工”关系，尽管部门号、电话和办公室为空，但是它表示该雇员没有分配到某个部门。(3)该行不能插入“职32'’关系，它违反了参照完整性。因为6在关系“部门”中不存在。[问题3]解答此问考查的是对视图定义的掌握。(1)和(2)都不能更新，因为使用分组合聚集函数定义的视图是不可更新的。(3)不一定，视子查询的返回值而定，(4)和(5)允许查询。[问题4]解答此问考查的是查询效率的问题。在涉及相关查询的某些情形中，构造临时关系可以提高查询效率。(1)对于外层的职工关系E中的每一个元组，都要对内层的整个职工关系M进行检索，因此查询效率不高。(2)解答一改正后的SQL语句使用了临时表：SelectMax(月工资)as最高工资，部门号intoTempfrom职工Groupby部门号Select职工号from职工，Tempwhere月工资=最高工资and职工，部门号=Temp．部门号解答二Select职工号from职工，(SelectMax(月工资)as最高工资，部门号Groupby部门号)asdepMaxwhere月工资=最高工资and职工．部门号=depMax．部门号[问题5]解答此问主要考查在查询中注意where子句中使用索引的问题。Select姓名，年龄，月工资from职工where年龄＞45；unionSelect姓名，年龄，月工资from职工where年龄月工资＜1000；试题3阅读下列说明，回答问题1至问题5。说明某仓储超市采用POS(PointofSale)收银机负责前台的销售收款，为及时掌握销售信息，并依此指导进货，拟建立商品进、销、存数据库管理系统。该系统的需求分析已经基本完成，下面将进入概念模型的设计。需求分析结果1．销售业