中级数据库系统工程师上半年下午试题答案及详细解析.docx

1、中级数据库系统工程师上半年下午试题答案及详细解析第21章 数据库系统工程师下试题分析与解答试题一(15分) 阅读下列说明以及数据流图，回答问题1至问题3，将解答填入答题纸的对应栏内。说明 某学校建立了一个网上作业提交与管理系统，基本功能描述如下： (1)账号和密码。任课老师用账号和密码登录系统后，提交所有选课学生的名单。系统自动为每个选课学生创建登录系统的账号和密码。 (2)作业提交。学生使用账号和密码登录系统后，可以向系统申请所选课程的作业。系统首先检查学生的当前状态，如果该学生还没有做过作业，则从数据库服务器申请一份作业。若申请成功，则显示需要完成的作业。学生需在线完成作业，单击“提交”按

2、钮上交作业。 (3)在线批阅。系统自动在线批改作业，显示作业成绩，并将该成绩记录在作业成绩统计文件中。问题1(3分) 如果将数据库服务器(记为DB)作为一个外部实体，那么在绘制该系统的数据流图时，还应有哪些外部实体和数据存储?问题2(7分) 根据说明结合问题1的解答，指出在该系统的顶层数据梳图中应有哪些数据流。请采用说明中的词汇给出这些数据流的起点、终点以及数据流名称，下表给出了数据流的部分信息，请填充空缺处。 序号起点终点数据流名称1(1)网上作业提交与管理系统作业申请2(2)网上作业提交与管理系统提交的作业3网上作业提交与管理系统(3)需完成的作业4网上作业提交与管理系统(4)(5)5网上

3、作业提交与管理系统(6)作业申请6网上作业提交与管理系统(7)(8)7(9)网上作业提交与管理系统选课学生名单8(10)网上作业提交与管理系统(11)9(12)网上作业提交与管理系统账号和密码10(13)网上作业提交与管理系统账号和密码问题3(5分) 根据数据流图的设计原则，阅读下图所示的数据流图，找出其中的错误之处。试题一分析 本题考查的是DFD(数据流图)的绘制，详细内容参见软件设计师下试题一分析。参考答案问题1 外部实体：(选课)学生、(任课)老师 数据存储：作业成绩统计文件问题2 (1) (选课)学生 (2) (选课)学生 (3) (选课)学生 (4) (选课)学生 (5) 作业成绩

4、(6) DB (7) 作业成绩统计文件 (8) 作业成绩 (9) (任课)老师 (10)DB (11)作业 (12)(选课)学生 (13)(任课)老师 注：(4)、(6)的答案可互换；(12)、(13)的答案可互换问题3 错误1：外部实体A和B之间不能存在数据流。 错误2：外部实体A和数据存储H之间不能存在数据流。 错误3：加工2的输入/输出数据流名字相同。 错误4：加工4只有输入没有输出。 错误5：加工5只有输出，没有输入。 注：以上5条错误信息可以打乱次序。试题二(15分) 阅读下列说明，回答问题1至问题5，将解答填入答题纸的对应栏内。说明 某企业网上销售管理系统的数据库部分关系模式如下所

5、示： 客户(客户号，姓名，性别，地址，邮编) 产品(产品号，名称，库存，单价) 订单(订单号，时间，金额，客户号) 订单明细(订单号，产品号，数量) 关系模式的主要属性及约束如下表所示。 关系名约束客户客户号唯一标识一位客户，客户性别取值为“男”或者“女”产品产品号唯一标识一个产品订单订单号唯一标识一份订单。一份订单必须且仅对应一位客户，份订单可由一到多条订单明细组成。一位客户可以有多份订单。订单明细一条订单明细对应一份订单中的个产品 客户、产品、订单和订单明细关系及部分数据分别如下列各表所示。 客户关系 客户号姓名性别地址邮编01王晓杰女南京路2号20000502林俊杰男北京路18号2000

6、10 产品关系 产品号名称库存单价01产品A20298.0002产品B50168.00 订单关系 订单号时间金额客户号10012006.02.031268.000110022006.02.03298.0002 订单明细关系 订单号产品号数量100101210010241002011问题1(3分) 以下是创建部分关系表的SQL语句，请将空缺部分补充完整。 CREATE TABLE客户( 客户号 CHAR(5) (a) ， 姓名 CHAR(30)， 性别 CHAR(2) (b) ， 地址 CHAR(30)， 邮编 CHAR(6)； CREATE TABLE 订单( 订单号CHAR(4)， 时间 C

7、HAR(10)， 金额 NUMBER(6,2)， 客户号CHAR(5)NOTNULL， PRIMARY KEY(订单号)， (c) ；问题2(4分) 请根据如下查询语句，回答问题(d)，(e)和(f)。 SELECT客户号 FROM订单，订单明细 WHERE订单明细.订单号=订单.订单号AND 产品号=02 AND 数量10； (d) 上述查询语句的功能是什么?请简要回答。(30个字以内) (e) 将上述查询语句转换成对应的关系代数表达式。 (f) 上述SQL查询语句是否可以进一步优化?如可以，给出优化后的SQL查询语句。问题3(3分) 请按题意将下述SQL查询语句的空缺部分补充完整。 按客户

8、购买总额的降序，输出每个客户的客户名和购买总额。 SELECT 客户.客户名， (g) FROM 客户，订单 WHERE 客户.客户号=订单.客户号 (h) (i) ；问题4(3分) 用SQL语句完成下述要求。 (1) 定义一个描述订单的客户号和对应订单明细中产品号关系的视图：客户产品(客户号，产品号)。 (2)借助(1)所定义的视图，查询至少购买了01号客户购买的所有产品的客户号。 SELECT客户号 FROM客户产品 客户产品1 WHERE (j) (SELECT* FROM客户产品 客户产品2 WHERRE (k) (SELECT* FROM客户产品 客户产品3 WHERRE (1) )

9、；问题5(2分) 当一个订单和对应的订单明细数据入库时，应该减少产品关系中相应的产品库存，为此应该利用数据库管理系统的什么机制实现此功能?请用100字以内的文字简要说明。试题二分析问题1 本题考查的是SQL语言中的创建基本表命令。 SQL中使用CREATE TABLE命令来定义基本表，其一般格式为： CREATE TABLE表名(列名数据类型列级完整性约束条件 ,列名数据类型列级完整性约束条件. ,表级完整性约束条件)； 本题着重考查完整性约束的定义。 实体完整性定义。声明主键有两种方法：将PRIMARYKEY保留字加在属性类型之后；在属性列表中引入一个新元素，该元素包含保留字PRIMARY

10、KEY和用圆括号括起的构成主键的属性或者属性组列表。 参照完整性定义。FOREIGNKEY(属性名)REFERFENCES表名(属性名)。参照完整性通过保留字FOREIGNKEY定义哪些列为外码，REFERFENCES指明外码所对应的被参照表的主码。 用户定义完整性定义。用CHECK后增加一条检查子句来完成属性值上的约束。 根据题目描述，客户号唯一标识一位客户，客户性别取值为“男”或者“女”，一份订单必须且仅对应一位客户。所以，客户号是客户表的主键，客户性别的取值只能为“男”或者“女”，订单中的客户号必须外键依赖于客户。 因此，客户号应表示为主键： (a)NOT NULL UNIQUE或NOT

11、 NULL PRIMARY KEY或PRIMARY KEY； 客户性别的取值只能为“男”或者“女”： (b)CHECK (VALUE IN(男，女) 订单中的客户号必须外键依赖于客户： (c)FOREIGN KEY (客户号) REFERENCES客户(客户号)问题2 本题考核的知识点包括SQL的查询命令与关系代数表达式的互相转换以及SQL的查询优化。 SELECT语句的功能是：查询一次订购(或购买)产品号为02的数量大于10的客户号。 根据关系代数表达式的定义，将上述查询语句转换成对应的关系代数表达式为： 该SELECT语句，在进行多表查询时，采用的是相关子查询。由于相关子查询查询时要做连接

12、JOIN，性能不是最优。可以考虑采用不相关子查询，来提高性能。优化的 SQL语句为： SELECT客户号 FROM订单 WHERE订单号IN (SELECT订单号 FROM订单明细 WHERE产品号=02 AND数量10)；问题3 本题考核的是SQL语言的查洵命令。 要输出客户的客户名和购买总额，SELECT语句的输出部分，还必须包含统计客户的购买总额(SUM(金额)AS总额)。统计一个客户的购买总额，需要对订单信息按客户号进行分组，才能求出每个客户的购买信息(GROUP BY客户.客户号)。再根据每个客户的购买总额进行降序排序(ORDER BY总额DESC)。问题4 本题考核的知识点是SQL

13、中的视图创建和SQL查询命令。 SQL语言用CREATE VIEW命令建立视图，其格式为： CREATE VIEW 视图名(列名,列名.) AS 子查询 WITHCHECKOPTION； 根据题目描述，一个订单涉及多个产品，订单中的每个产品对应一项订单明细。一个订单对应一个客户，一个客户可以有多个订单。要建立客户号和产品号的关系，必须通过订单和订单明细。一个客户号对应多个订单，一个订单对应多项订单明细，一项订单明细对应一个产品。所以，视图如下： CREATE VIEW客户产品AS( SELECT 客户号，产品号 FROM 订单，订单明细 WHERE 订单明细.订单号=订单.订单号); 要查找至

14、少购买了01号客户购买的所有产品的客户号，可借助上述的客户产品视图。通过该视图，可以知道每个客户购买过的产品号。根据01号客户购买的所有产品号，看看是否存在这样的客户号，其对应的产品号集合包含这些产品号(01号客户购买的所有产品号)。查找可以进一步转换成，查询客户X，使得不存在这样的产品Y，01号客户购买了Y，而客户X没有购买。问题5 本题考查触发器的概念。 触发器是一种特殊类型的存储过程，它不由用户直接调用，被定义为在对表或视图发出UPDATE、INSERT或DELETE语句时自动执行。触发器可以查询其他表，而且可以包含复杂的SQL语句。 本题中由于订单明细表上产品数量与产品表中的库存有联系

15、。比如，当某个订单生成时，可以通过设计触发起来减少相应的产品的库存。参考答案问题1 (a)NOT NULL UNIQUE或NOT NULL PRIMARY KEY 或 PRIMARY KEY (b)CHECK (VALUE IN(男，女) (c)FOREIGN KEY (客户号) REFERENCES 客户(客户号)问题2 (d)查询一次订购(或购买)产品号为02的数量大于10的客户号 (e) (f)可以优化。优化的SQL语句为： SELECT客户号 FROM 订单 WHERE 订单号 IN (SELECT 订单号 FROM 订单明细 WHERE产品号=02 AND数量10)；问题3 (g)S

16、UM (金额) AS 总额 (h)GROUP BY客户.客户号 (i)ORDER BY总额DESC问题4 (1) CREATE VIEW客户产品AS( SELECT 客户号，产品号 FROM 订单，订单明细 WHERE 订单明细.订单号=订单.订单号)； (2) (j)NOT EXISTS (k)客户号=01 AND NOT EXISTS (1)客户产品1.客户号=客户产品3.客户号AND客户产品2.产品号 =客户产品3.产品号问题5 采用数据库管理系统的触发器机制。对产品关系定义一个触发器，在订单明细中的记录插入或更新之后，该触发器被激活，根据订单明细中订购的产品及数量，减少产品关系中对应产

17、品的库存量。试题三(15分) 阅读下列说明，回答问题1至问题4，将解答填入答题纸的对应栏内。说明 某单位资料室需要建立一个图书管理系统，初步的需求分析结果如下： (1)资料室有图书管理员若干名，他们负责已购入图书的编目和借还工作，每名图书管理员的信息包括工号和姓名； (2)读者可在阅览室读书，也可通过图书流通室借还图书，读者信息包括读者D、姓名、电话和E-mail，系统为不同读者生成不同的读者ID： (3)每部书在系统中对应唯一的一条图书在版编目数据(CIP，以下简称书目)，书目的基本信息包括ISBN号、书名、作者、出版商、出版年月，以及本资料室拥有该书的册数(以下简称册数)，不同书目的ISB

18、N号不相同： (4)资料室对于同一书目的图书可拥有多册(本)，图书信息包括图书ID、ISBN号、存放位置、当前状态，每一本书在系统中被赋予唯一的图书ID； (5)一名读者最多只能借阅十本图书，且每本图书最多只能借两个月，读者借书时需由图书管理员登记读者ID、所借图书ID、借阅时间和应还时间，读者还书时图书管理员在对应的借书信息中记录归还时间。 某书目的信息以及与该书目对应的图书信息如下表所示。 书目信息 书名作者出版商ISBN号出版年月册数经办人数据结构严蔚敏吴伟民清华大学出版社ISBN73020236891997.4401 图书信息 图书IDISBN号存放位置状态经办人C832.1ISBN7

19、-302-02368-9图书流通室已借出01C832.1ISBN7-302-02368-9图书阅览室不外借01C832.1ISBN7-302-02368-9图书流通室未借出01 系统的主要业务处理如F。 (1)入库管理：图书购进入库时，管理员查询本资料室的书目信息，若该书的书目尚未建立，则由管理员编写该书的书目信息并录入系统，然后编写并录入图书信息；否则，修改该书目的册数，然后编写并录入图书信息，对于进入流通室的书，其初始状态为“未借出”，而送入阅览室的书的状态始终为“不外借”。 (2)借书管理：读者借书日寸，若有，则由管理员为该读者办理借书手续，并记录该读者的借书信息，同时将借出图书的状态修

20、改为“已借出”。 (3)还书管理：读者还书时，则记录相应借还信息中的“归还时间”，对于超期归还者，系统自动计算罚金(具体的计算过程此处省略)。同时修改该图书的状态为“未借出”。 (4)通知处理：对于已到期且未归还的图书，系统通过E-mail自动通知读者。问题1(2分) 根据以上说明设计的实体联系图如下图所示，请指出读者与图书、书目与图书之间的联系类型。问题2 该图书管理系统的主要关系模式如下，请补充“借还记录”关系中的空缺。 管理员(工号，姓名) 读者(读者ID，姓名，电话，E-mail) 书目(1SBN号，书名，作者，出版商，出版年月，册数，经办人) 图书(图书ID，ISBN号，存放位置，状

21、态，经办人) 借还记录( (a) ，借出时间，应还时间，归还时间) 注：时间格式为“年.月.日时:分:秒”。问题3(4分) 请指出问题2中给出的读者、书目关系模式的主键，以及图书和借还记录关系模式的主键和外键。问题4(7分) 若系统增加新的预约需求，其业务处理描述如下： 若图书流通室没有读者要借的书，则可为该读者建立预约登记，需要记录读者ID、书的ISBN号、预约时间和预约期限(最长为10天)。一旦其他读者归还这种书，系统将自动查询预约登记表，若存在有读者预约该书的记录，则将该图书的状态修改为“已预约”，并将该图书ID写入相应的预约记录中(系统在清除超出预约期限的记录时解除该图书的“已预约”状

22、态)，同时通过E-mail通知该预约读者办理借阅手续。对于超出预约期限的预约记录，系统将自动清除。 为满足上述需要，应对上图所示的实体联系图如何修改或补充，请给出修改后的实体联系图，并对关系模式做相应的修改或补充，指出新增关系模式的主键和外键。试题三分析问题1 本题考查有关实体之间的联系。 两个实体型之间的联系可以分为三类： 一对一联系(1:1) 如果对于实体集A中的每一个实体，实体集B中至多有一个(也可以没有)实体与之联系。反之亦然，则称实体集A与实体集B具有一对一联系，记为1:1。 一对多联系(1:n) 如果对于实体集A中的每一个实体，实体集B中有n个实体(n=0)与之联系。反之，对于实体

23、集B中的每一个实体，实体集A中最多只有一个实体与之联系，则称实体集A与实体集B有一对多联系，记为1:n。 多对多联系(m:n) 如果对于实体集A中的每一个实体，实体集B中有n个实体(n=0)与之联系。反之，对于实体集B中的每一个实体，实体集A中也有m个实体(m=0)与之联系，则称实体集A与实体集B具有多对多联系，记为m:n。 根据题目描述，一名读者最多只能借阅10本图书，而每本书可被多名读者先后借阅过，所以，读者和图书之间的关系是多对多(n:m)；每本书在系统中对应唯一的一条书目数据，且对于同一书目的图书可拥有多册(本)。所以书目和图书之间是一对多(1:n)的关系。问题2 本题考查在ER图向关

24、系模型的转换中，如何将实体和实体间的联系转换为关系模式，以及如何确定这些关系模式的属性。 这种ER图向关系模型的转换一般遵循如下原则： 一个实体型转换为一个关系模式。实体的属性就是关系的属性，实体的码就是关系的码。 一个1:1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。 一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。 一个m:n联系转换为一个关系模式。与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。 3个或3个以上实体间的一个多元联系可以转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身

25、的属性均转换为关系的属性，而关系的码为各实体码的组合。 具有相同码则关系模式可合并。 根据题目中的ER图，借还是图书和读者之间的联系(m:n)。借还记录是联系“借还”转换成的一个关系模式，用来记录借阅入和所借阅图书的借阅信息。所以应该记录与该联系相连的各实体(读者和图书)的码(读者ID和图书ID)，以及联系本身的属性(借出时间、应还时间和归还时间)，均转换为关系的属性。问题3 本题考查关系模式的主码的确定。 读者实体的码是能唯一标识读者的读者ID，关系读者是由读者实体转换而来的，读者实体的码(读者ID)就是关系读者的码(读者ID)。书目实体的码是能唯一标识书目的ISBN号，书目关系的码就是IS

26、BN号。同理，图书关系的码是图书ID。借还记录是由联系借还对应的关系，它的码应为相连实体的码(读者ID和图书ID)，再加上联系本身的码(借出时间)。所以，借还记录的码应为：读者ID，图书ID，借出时间。 根据ER图，图书和书目之间有1:n的联系，为了记录该联系，在n端，也就是图书关系中，记录对应的书目的主码。因此，图书关系具有外键ISBN号，来与书目关系相关联。 借还记录是由联系借还对应的关系，它记录与图书和读者的联系。因此，借还记录具有外键读者ID和图书ID，分别与读者和图书相关联。 根据实体联系图，可定义如下的主键和外键： 读者(读者ID，姓名，电话，E-mail) 书目(ISBN号，书名

27、，作者，出版商，出版年月，册数，经办人) 图书(图书ID，ISBN号，存放位置，状态，经办人) 借还记录(读者ID，图书ID，借出时间，应还时间，归还时间)问题4 为使读者可以对已借完的流通图书进行预约。由于一个书目所对应的流通图书可能有多本，读者不需要去预定具体某一本图书，只需对该书目进行预约。因此，需要在书目和读者之间增加预约联系。只要该书目对应的任意一本具体的流通图书先归还，就可以对将该图书的状态设置为“已预约”。一个读者可以预约多个书目，而一个书目也可以被多个读者预约。所以，读者和书目之间的关系是多对多(n:m)，如下图所示。 增加新的关系模式： 预约登记(读者ID，ISBN号，预约时

28、间，预约期限，图书ID) 主键：(读者ID，ISBN号，预约时间) 外键：读者ID，ISBN号，图书ID参考答案问题1 (1)n或m (2)m或n (3)1 (4)n或m问题2 (a)读者ID，图书ID问题3 关系模式主键外键读者读者ID书目ISBN号图书图书IDISBN号借还记录读者ID，图书ID，借出时间读者ID，图书ID问题4 补充联系“预约”，修补后的实体联系图如下： 增加新的关系模式： 预约登记(读者ID，ISBN号，预约时间，预约期限，图书ID) 主键：(读者ID，ISBN号，预约时间) 外键：读者ID，ISBN号，图书ID试题四(15分) 阅读下列说明，回答问题1至问题4，将解答

29、填入答题纸的对应栏内。说明 某保险公司需要管理用户投保的相关信息，拟建立针对投保数据、险种数据、缴费数据的管理系统。系统需求分析情况如下所述。 1)投保单是缔结保险合同的重要依据，需填写投保人、被保险人、受益人资料等信息。投保单格式如下所示： 2)该公司需要管理险种信息以供查询。险种信息包括：险种名称、承保年龄、保险利益、缴费方式、保险费、保险特点等信息。示例如下： 险种名称重大疾病保险承保年龄30日以上、65周岁以下保险利益重大疾病保险金由于患病无法工作而失去正常收入来源，将获得一笔资金以支付巨额医疗费用缴费方式保险费的交付方式分为趸交、年交和月交3种。分期交付保险费的交费期间分为5年，10年、20年和30年4种，由投保人在投保时选择保险费10万保险特点提供29种疾病的特别保障 3)业务处理过程。用户可通过网络查询险种，并选择投保的险种。用户直接填写投保书，经过业务员审核通过后，请投保人签字，并由业务员确认投