数据库系统工程师经典试题解析Word文档格式.docx
- 文档编号:22241089
- 上传时间:2023-02-03
- 格式:DOCX
- 页数:28
- 大小:333.97KB
数据库系统工程师经典试题解析Word文档格式.docx
《数据库系统工程师经典试题解析Word文档格式.docx》由会员分享,可在线阅读,更多相关《数据库系统工程师经典试题解析Word文档格式.docx(28页珍藏版)》请在冰豆网上搜索。
题目要求根据描述确定图中的外部实体。
分析题目中的描述,并结合已经在顶层数据流图中给出的数据流进行分析。
题目中有信息描述:
数据管理员可通过中间件进行用户管理、操作管理和权限管理;
前端应用提交操作请求;
连接管理连接相应的后台数据库并提交操作。
由此可知该中间件系统有数据管理员、前端应用和后端数据库三个外部实体。
对应图1-1中数据流和实体的对应关系,可知E1为前端应用,E2为数据管理员,E3为后端数据库。
本问题考查0层DFD中数据存储的确定。
说明中描述:
用户信息(用户名、密码)存储在用户表中;
标准操作和后端数据库信息存放在操作表中;
权限管理维护信息存放在权限表中。
因此数据存储为用户表、操作表以及权限表。
再根据图1-2中D1的输入数据流从用户管理来,D2的输入数据流从操作管理来,D3的输入数据流从权限管理来,所以D1为用户表,D2为操作表,D3为权限表。
本问题考查0层DFD中缺失的加工和数据流。
比较图1-1和图1-2,可知顶层DFD中的操作结果和处理后的操作结果没有在0层DFD中体现。
再根据描述:
后端数据库执行操作并将结果传给中间件,中间件对收到的操作结果进行处理后,将其返回给前端应用。
可知,需要有操作结果处理,因此P为操作结果处理,其输入流从后端数据库E3来的操作结果,输出结果为处理后的操作结果,并返回给前端应用E1。
考查完P及其输入输出流之后,对图1-2的内部数据流进行考查,以找出缺失的另外2条数据流。
从图中可以看出D2和D3只有输入流没有输出流,这是常见DFD设计时的错误,所以首先考查D2和D3的输出流。
描述中:
权限验证是验证用户是否有权执行请求的操作,若用户有权执行该操作,进行连接管理;
连接管理连接相应的后台数据库并提交操作;
权限表存储用户可执行的操作信息。
因此,权限验证有从权限表D3来的输入数据流。
而要连接后端数据库,需要数据库信息,从权限验证的输出流中包含有数据库信息可知,权限验证需要获取到数据库信息,所以还需从操作表D2来的输入流。
本问题考查在绘制数据流图中加工绘制时的注意事项。
绘制加工时可能出现的错误有:
加工的输入、输出时可能出现只有输入而无输出、只有输出而无输入、输入的数据流无法通过加工产生输出流以及输入的数据流与输出的数据流名称相同等错误。
参考答案
E1:
前端应用E2:
数据管理员E3:
后端数据库
D1:
用户表D2:
操作表D3:
权限表
P的名称:
操作结果处理
操作结果
E3
处理后的操作结果
E1
缺少的数据流:
D2
权限验证
D3
在绘制数据流图的加工时,可能出现的输入、输出错误:
只有输入而无输出或者黑洞
只有输出而无输入或者奇迹
输入的数据流无法通过加工产生输出流或者灰洞
输入的数据流与输出的数据流名称相同
试题二
阅读下列说明,回答问题1至问题3,将解答填入答题纸的对应栏内。
天津市某银行信息系统的数据库部分关系模式如下所示:
客户(客户号,姓名,性别,地址,邮编,电话)
账户(账户号,客户号,开户支行号,余额)
支行(支行号,支行名称,城市,资产总额)
交易(交易号,账户号,业务金额,交易日期)
其中,业务金额为正值表示客户向账户存款;
为负值表示取款。
以下是创建账户关系的SQL语句,账户号唯一识别一个账户,客户号为客户关系的唯一标识,且不能为空。
账户余额不能小于1.00元。
请将空缺部分补充完整。
CREATETABLE账户(
账户号CHAR(19)(a),
客户号CHAR(10)(b);
开户支行号CHAR(6)NOTNULL,
余额NUMBER(8,2)(c));
(1)现银行决策者希望查看在天津市各支行开户且2009年9月使用了银行存取服务的所有客户的详细信息,请补充完整相应的查询语句。
(交易日期形式为'
2000-01-01'
)
SELECTDISTINCT客户.*
FROM客户,账户,支行,交易
WHERE客户.客户号=账户.客户号AND
账户.开户支行号=支行.支行号AND
(d)AND
交易.账户号=账户.账户号AND
(e);
上述查询优化后的语句如下,请补充完整。
FROM客户,账户,(f)AS新支行,(g)AS新交易
WHERE客户.客户号=账户.客户号AND
账户.开户支行号=新支行.支行号AND
新交易.账户号=账户.账户号;
(2)假定一名客户可以申请多个账户,给出在该银行当前所有账户余额之和超过百万的客户信息并按客户号降序排列。
SELECT*
FROM客户
WHERE(h)
(SELECT客户号FROM账户GROUPBY客户号(i))
ORDERBY(j);
(1)为账户关系增加一个属性“账户标记”,缺省值为0,取值类型为整数;
并将当前账户关系中所有记录的“账户标记”属性值修改为0。
请补充相关SQL语句。
ALTERTABLE账户(k)DEFAULT0;
UPDATE账户(l);
(2)对于每笔金额超过10万元的交易,其对应账户标记属性值加1,给出触发器实现的方案。
CREATETRIGGER交易_触发器(m)ON交易
REFERENCINGNEWROWAS新交易
FOREACHROW
WHEN(n)
BEGINATOMIC
UPDATE账户SET账户标记=账户标记+1
WHERE(o);
COMMITWORK;
END
试题二分析
本题考查SQL语句的基本语法与结构知识。
此类题目要求考生掌握SQL语句的基本语法和结构,认真阅读题目给出的关系模式,针对题目的要求具体分析并解答。
本试题已经给出了4个关系模式,需要分析每个实体的属性特征及实体之间的联系,补充完整SQL语句。
由于问题1中“账户号唯一识别一个账户”可知账户号为账户关系的主键,即不能为空且唯一标识一条账户信息,因此需要用PRIMARYKEY对该属性进行主键约束;
又由于“客户号为客户关系的唯一标识,且不能为空”可知客户号为客户关系的主键,在账户关系中应作外键,用FOREIGNKEY对该属性进行外键约束;
由“账户余额不能小于1.00元”可知需要限制账户余额属性值的范围,通过CHECK约束来实现。
从上分析可见,完整的SQL语句如下:
账户号CHAR(19)PRIMARYKEY,
客户号CHAR(10)FOREIGNKEY(客户号)REFERENcEs客户(客户号),
开户支行号CHAR(6)NOTNULL,
余额NUMBER(8,2)CHECK(余额>1.00));
PRIMARYKEY可替换为NOTNULLUNIQUE或NOTNULLPRIMARYKEY。
SQL查询通过SELECT语句实现。
(1)根据问题2要求应在表连接条件的基础上,需要添加两个条件:
①支行关系的城市属性值为“天津市”,即支行城市='
天津市'
;
②在2009年9月存在交易记录,由于交易日期形式为'
,所以需要通过模糊匹配来实现,用LIKE关键词和通配符表示,即交易.交易日期LIKE'
2009-09-%'
。
WHERE子句中条件的先后顺序会对执行效率产生影响。
假如解析器是按照先后顺序依次解析并列条件,优化的原则是:
表之间的连接必须出现在其他WHERE条件之后,那些可以过滤掉最多条记录的条件尽可能出现在WHERE子句中其他条件的前面。
要实现上述优化过程,可以重新组织WHERE条件的顺序或者通过嵌套查询以缩小连接记录数目的规模来实现。
根据问题2要求,考生需要添加两个子查询以缩小参与连接的记录的数目,即筛选出天津市的所有支行(SELECT+FROM支行WHERE城市='
),而且找到2009年9月发生的交易记录(SELECT+FROM交易WHERE交易日期LIKE'
),然后再做连接查询。
(2)根据问题2要求,可通过予查询实现“所有账户余额之和超过百万的客户信息”的查询;
对SUM函数计算的结果应通过HAVING条件语句进行约束;
降序通过DESC关键字来实现。
FROM客户
WHERE客户号IN
(SELECT客户号FROM账户GROUPBY客户号HAVINGSUM(余额)>1000000.00)
ORDERBY客户号DESC;
(1)关系模式的修改通过ALTER语句来实现,使用ADD添加属性;
使用SET修改属性值。
ALTERTABLE账户ADD账户标记INTDEFAULT0;
UPDATE账户SET账户标记=0;
(2)创建触发器可通过CREATETRIGGER语句实现,问题3要求考生掌握该语句的基本语法结构。
按照问题3要求,在交易关系中插入一条记录时触发器应自动执行,故需要创建基于INSERT类型的触发器,其触发条件是新插入交易记录的金额属性值>100000.00;
最后添加表连接条件。
完整的触发器实现方案如下:
CREATETRIGGER交易触发器AFTERINSERTON交易
REFERENCINGNEWROWAS新交易
WHEN新交易.金额>100000.00
WHERE账户.账户号=新交易.账户号;
(a)PRIMARYKEY/NOTNULLUNIQUE/NOTNULLPRIMARYKEY
(b)FOREIGNKEY(客户号)REFERENCES客户(客户号)
(C)CHECK(余额>1.00)
(1)
(d)支行.城市='
(e)交易.交易日期LIKE'
或等价表示
(d)(e)次序无关
(f)(SELECT*FROM支行WHERE城市='
(g)(SELECT*FROM交易WHERE交易日期LIKE'
2009-09%'
)或等价表示
(2)
(h)客户号IN
(i)HAVINGSUM(余额)>1000000.00或等价表示
(i)客户号DESC
(k)ADD账户标记INT
(1)SET账户标记=0;
(m)AFTERINSERT
(n)新交易.金额>100000.00或等价表示
(o)账户.账户号=新交易.账户号
试题三
某学校拟开发一套实验管理系统,对各课程的实验安排进行管理。
[需求分析]
每个实验室可进行的实验类型不同。
由于实验室和实验员资源有限,需根据学生人数分批次安排实验室和实验员。
一门含实验的课程可以开设给多个班级,每个班级每学期可以开设多门含实验的课程。
每个实验室都有其可开设的实验类型。
一门课程的一种实验可以根据人数、实验室的可容纳人数和实验室类型,分批次开设在多个实验室的不同时间段。
一个实验室的一次实验可以分配多个实验员负责辅导实验,实验员给出学生的每次实验成绩。
1.课程信息包括:
课程编号、课程名称、实验学时、授课学期和开课的班级等信息;
实验信息记录该课程的实验进度信息,包括:
实验名、实验类型、学时、安排周次等信息,如表3-1所示。
表3-1 课程及实验信息
课程编号
15054037
课程名称
数字电视原理
实验学时
12
班级
电0501,信0501,计0501
授课院系
机械与电气工程
授课学期
第三学期
序号
实验名
实验类型
难度
学时
安排周次
1505403701
音视频AD-DA实验
验证性
1
2
3
1505403702
音频编码实验
5
1505403703
视频编码实验
演示性
0.5
9
2.以课程为单位制定实验安排计划信息,包括:
实验地点,实验时间、实验员等信息。
实验计划如表3-2所示。
表3-2 实验安排计划
安排学期
2009年秋
总人数
220
实验编号
实验员
实验时间
地点
批次号
人数
盛×
,陈×
第3周周四晚上
实验三楼310
60
吴×
,刘×
第3周周五晚上
实验三楼311
4
40
第5周周一下午
实验四楼410
70
3.由实验员给出每个学生每次实验的成绩,包括:
实验名,学号,姓名,班级,实验成绩等信息。
实验成绩如表3-3所示。
表3-3 实验成绩 实验员:
盛×
课程名
学号
姓名
实验成绩
030501001
陈民
信0501
87
030501002
刘志
78
040501001
张勤
计0501
86
4.学生的实验课程总成绩根据每次实验的成绩以及每次实验的难度来计算。
[概念模型设计]
根据需求阶段收集的信息,设计的实体联系图(不完整)如图3-1所示。
[逻辑结构设计]
根据概念模型设计阶段完成的实体联系图,得出如下关系模式(不完整):
课程(课程编号,课程名称,授课院系,实验学时)
班级(班级号,专业,所属系)
开课情况(
(1),授课学期)
实验(
(2),实验类型,难度,学时,安排周次)
实验计划((3),实验时间,人数)
实验员((4),级别)
实验室(实验室编号,地点,开放时间,可容纳人数,实验类型)
学生((5),姓名,年龄,性别)
实验成绩((6),实验成绩,评分实验员)
补充图3-1中的联系和联系的类型。
根据图3-1,将逻辑结构设计阶段生成的关系模式中的空
(1)~(6)补充完整。
对所有关系模式,用下划线标出各关系模式的主键。
如果需要记录课程的授课教师,新增加“授课教师”实体。
请对图3-1进行修改,画出修改后的实体问联系和联系的类型。
试题三分析
本题考查数据库概念结构设计及向逻辑结构转换的掌握。
此类题目要求考生认真阅读题目,根据题目的需求描述,给出实体问的联系。
根据题意由“一门含实验的课程可以开设给多个班级,每个班级每学期可以开设多门含实验的课程”可知课程和班级之间的开设关系为m:
n联系。
由“一个实验室的一次实验可以分配多个实验员负责辅导实验”可知实验、实验室与实验员之间的安排关系为k:
n:
m联系。
由“实验员给出学生的每次实验成绩”可知实验、学生与实验员之间的成绩关系为k:
班级和学生之间的包含关系为1:
根据题意,可知课程编号是课程的主键,班级号是班级的主键。
从表3-1可见,开课情况是体现课程与班级间的m:
n联系,因此开课情况关系模式应该包含课程编号和班级号,并共同作为主键。
一门课程包含多次实验,实验与课程之间是m:
1关系,因此,由表3-1可知,实验关系模式应包含实验编号和课程编号,并且以实验编号为主键,以课程编号为外键。
在制定试验计划时,每个班的每次实验可能按实验室被分成多个批次,每个批次的实验会有若干名实验员来辅导学生实验并打分。
实验员关系模式应该记录实验员编号和实验员姓名,并以实验员编号为主键。
实验室编号是实验室的主键。
从表3-2可知,实验计划关系模式应记录实验编号、批次号和授课学期,并且共同作为主键。
从表3-3可知,实验成绩关系模式记录每个学生的每次实验成绩,应包含学号和实验编号,并共同作为主键。
由于授课教师负责给若干个班级开设若干门课程,因此,课程、班级和授课教师之间的开设关系是k:
答案参见图3-1。
(1)课程编号,班级号
(2)实验编号,课程编号
(3)实验编号,批次号,安排学期,,实验室编号,实验员编号
(4)实验员编号,实验员姓名
(5)学号,班级号
(6)实验编号,学号
其他关系模式主键:
实验室(实验室编号,地点,开放时间,可容纳人数,实验课类型)
试题四
某旅行社拟开发一套旅游管理系统,以便管理旅游相关的信息。
1.旅行社可发布旅游线路的信息,包含:
线路的价格、天数、住宿情况,以及具体的行程安排等。
不同的线路参观的景点及住宿情况不相同,如表4-1所示。
表4-1 旅游线路信息
线路编号
SO-501
价格
2000
天数
日程号
景点
城市
住宿
D1
接站集合,天安门、紫禁城、颐和园
北京
建国饭店
上午参观北京胡同,下午飞往西安
北京,西安
花园饭店
上午参观兵马俑,下午参观大雁塔
西安
D4
上午参观钟鼓楼,下午返回
2.游客与旅行社沟通,选择适合自己的线路,并由旅行社为其生成订单,以记录游客联系人的姓名、身份证号、联系方式、人数、所选线路、导游安排和票务信息。
旅行社为游客在行程中的每个城市安排一个负责导游,负责游客在该城市的具体旅行安排。
同一城市的负责导游相同,不同城市的负责导游有可能不同。
3.旅行社的每位员工只属于一种固定的员工类别,系统可记录员工的多部手机号。
旅行社按月统计导游每月的带团人数和游客投诉次数,以计算导游的当月月薪。
根据上述需求,初步设计了旅游信息数据库,其关系模式如图4-1所示。
关系模式中主要属性的含义及约束如表4-2所示。
对关系“线路信息”,请回答以下问题:
(1)列举出所有不属于任何候选键的属性(非键属性)。
(2)关系“线路信息”是否为BCNF范式,用60字以内文字简要叙述理由。
表4-2 主要属性含义及约束
属性
含义及约束条件
唯一标识某条旅游的线路信息
旅游行程中的某一大,如:
D1代表第1天,Dn代表第n天
不同线路游客在不同城市的住宿情况说明
旅游行程中某一天游客所在的城市名称
旅游行程中某一天游客游览的景点名称
某个订单的总游客数
订单价格
某个订单的总价
车票班次
旅行过程中的车票班次,包括:
火车车次、航班班次等
车票类型
车票类型分为:
飞机、火车
票数
针对某订单某班次的车票数量
总价格
针对某订单某班次的车票的总价格
计薪月
某员工的被投诉次数和月薪所对应的年份和月份,如:
2006年5月
手机号
允许一个员工有多个手机号
被投诉次数
某员工某计薪月的被投诉次数
带团人数
某员工某计薪月的带团人数总和
月薪
某员工某计薪月的薪水金额
员工类别
员工类别分为:
导游或其他
对关系“订单信息”,请回答以下问题:
(1)“订单信息”是否为2NF范式,用100字以内文字简要说明会产生什么问题。
(2)把“订单信息”分解为第三范式,分解后的关系名依次为:
订单信息1,订单信息2,…。
(3)列出分解后的各关系模式的主键。
对关系“员工信息”,请回答以下问题:
(1)关系“员工信息”是不是第四范式,用100字以内文字叙述理由。
(2)若“员工信息”不是第四范式,将其分解为第四范式,分解后的关系名依次为:
员工信息1,员工信息2,…。
试题四分析
本题考查数据库理论的规范化。
本问题考查非主属性和BCNF范式。
根据题意和“线路信息”关系模式可知,不属于任何候选键的属性(非键属性)为:
价格,天数。
根据BCNF范式的要求:
每一个函数依赖的决定因素都包含码。
而“线路信息”关系模式的函数依赖为:
线路编号→价格,天数
因此,“线路信息”关系模式可以达到BCNF范式。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 工程师 经典 试题 解析