中级数据库系统工程师上半下午试题答案及详细解析Word文件下载.doc

1、 问题2图1-1中存在四处错误数据流，请指出各自的起点和终点；若将上述四条错误数据流删除，为保证数据流图的正确性，应补充三条数据流，请给出所补充数据流的起点和终点。（起点和终点请采用数据流图1-1中的符号或名称）错误数据流补充的数据流 试题一分析 本题考查DFD的分析与设计，问题一主要考查DFD中的外部实体和数据存储，由于在题干中已经提到“系统接收由连锁超市提出的供货请求，并将其记录到供货请求记录文件”，因此可以明确出“连锁超市”外部实体和“供货请求记录文件”数据存储：对应到DFD图中为E3和D4。描述中的第二项提出“从商品库存记录文件中进行商品库存信息查询。如果库存满足供货请求，则给配送处发

2、送配送通知；否则，向采购部门发出缺货通知”，因为配送通知需要发送到采购部门，因此采购部门将成为系统的外部实体；同时，商品库存记录文件能够提供库存信息，所以DFD图中E2和D2分别为采购部门和商品配送记录文件。第三项需求“配送处理接到配送通知后，查询供货请求记录文件，更新商品库存记录文件，并向配送部门发送配送单，在配送货品的同时记录配送信息至商品配送记录文件”，所以配送处理需要查询供货请求记录文什，更新商品库存记录文件与商品配送记录文件，因此D3为商品配送记录文件：采购处理需要记录采购清单同时通知财务部门，所以E1应该为财务部门，D1为采购清单记录文件，剩下的E4则为配送部门。 DFD中出现的错

3、误数据流为：E1到E2，E1与E2的数据流不属于系统的范围：D3到E4，多余的数据流；D2到采购处理，数据流方向错误；D4到供货请求处理，数据流方向错误。 需要补充的数据流为：E2到采购处理，因为E2是采购部门，采购部门需要给采购处提供入库商品信息；采购处到D2需要一条数据流，因为采购处理需要更改库存信息；供货请求处理到D4需要一条数据流，因为供货请求处理需要记录供货请求信息。 参考答案 E1：财务部门 E2：采购部门 E3：连锁超市 E4配送部门 D1：采购清单记录文件 D2：商品库存记录文件 D3商品配送记录文件 D4：供货请求记录文件问题2 试题二 阅读下列说明，回答问题1至问题3，将解

4、答填入答题纸的对应栏内。 某网上书店后台数据库的部分关系模式如下： 会员（会员编号，用户名，密码，姓名；地址，邮编，电话，消费额，积分） 图书（图书编号，类型名称，图书名称，作者，出版社，出版日期，ISBN，价格） 订单（订单编号，用户名，销售额，订购日期，出货日期） 订单明细（订单明细编号，订单编号，图书编号，数量） 下面是创建订单关系的SQL语句，订单编号唯一识别一个订单，用户名为订购图书的会员用户名，且不能为空。要求订购日期不能大于出货日期。请将空缺部分补充完整。 CREATE TABLE 订单（ 订单编号 CHAR（6） （a） 用户名VARCHAR（40）NOT NULL （b） ，

5、 销售额FLOAT, 订购日期DATE NOT NULL， 出货日期DATE （c） ）； 请完成下列查询的SQL语句。 （1）查询名称中包含“数据库”的图书的图书名称，作者，出版社和出版日期。 SELECT （d） FROM 图书 WHERE 图书名称 （e） ； （2）查询提供销售（图书表中有）但没有销售过（没在订单明细表中出现）的图书名称和出版社。 SELECT 图书名称，出版社 WHERE NOT EXISTS （ SELECT （f） FROM 订单明细 WHERE （g） ）； （3）查询订购图书数量最多的会员名及其订购的数量。 SELECT 用户名, （h） FROM订单，订单明

6、细 WHERE （i） GROUP BY 用户名 HAVING （j） （SELECT SUM （数量） FROM 订单，订单明细 WHERE 订单.订单编号=订单明细订单编号 GROUP BY 用户名）； （4）为了统计会员的购买行为信息，实施有意义的客户关怀策略，查询会员的平均订购间隔时间，考虑多次购买图书和一次购买图书的情况（其中，DATEDIFF函数表示两个日期之间的天数）。 SELECT 用户名，CASE WHEN （k） THEN DATEDIFF （MAX （订购日期），MIN （订购日期） / （1） ELSE DATEDIFF（CURRENT_TIMESTAMP，MIN（订购

7、日期） END AS AVG GAP FROM 订单 （m） ； 问题3 会员订购图书后，将本次订购的销售额累加到该会员的消费额中，并按照本次订单的销售额计算积分累加到该会员的积分中（每20元增加1个积分，不足20元不计入积分）。下面用触发器实现该需求，请填充空缺部分。 CREATE TRIGGER会员积分TRIGGER AFTER （n） REFERENCING NEW ROW AS NROW BEGIN UPDATE会员 SET消费额=消费额+NROW.销售额, （o） WHERE用户名=NROW用户名 END 试题二分析 本题考核SQL语言的应用。 本题考查关系模型的完整性约束。完整性约

8、束包括三类：实体完整性、参照完整性和用户定义的完整性。实体完整性约束规定关系的主属性不能取空值，关系模型中以主码作为唯一性标示。（a）考核的是实体完整性，订单编号是订单关系的主键，用PRIMARY KEY关键字，也可用NOT NULL UNIQUE 或UNIQUE NOT NULL来保证一个订单标号唯一识别一个订单：参照完整性约束规定若属性（或属性组）A是关系R上的主码，B是关系S上的外码，A与B相对应（来自相同的域），则B取值为空或者来自于R上的某个A的值。（b）考查参照完整性，用户名属性对应于会员关系的用户名属性，定义语法为REFERENCES会员（用户名），由于两个关系中的属性名一致，因

9、此也可以用 REFERENCES会员定义；用户定义的完整性约束是针对具体的数据库应用而定义的，它反映该应用所涉及的数据必须满足用户定义的语义要求。（c）考查用户定义的完整性约束，要求出货日期必须大于等于订购日期，因此答案为CHECK（订购日期=出货日期） 或C0ONSTRAINT chk date CHECK（订购日期=出货日期），此处，chk date可以为其他的约束名。 本题考查查询语句SELECT的语法，查询是SQL的重要内容。 （1）考查基本的SQL查询语法，SELECT后是要查询的属性，查询条件是包含“数据库”的图书，因此用LIKE关键字。 （2）考查NOTEXISTS语法，（f）处

10、填。该查询为相关查询，因此（g）处的条件为图书.图书编号=订单明细.图书编号。 （3）考查较复杂的嵌套查询、分组查询和ALL关键字。子查询得到所有会员的订购数，题目要求查询订购数最多的会员名和订购数，因此（j）的答案为SUM（数量）=ALL。 （4）考查分组查询、聚集函数和CASE语法。平均订购间隔时间对一次购买和多次购买的会员有不同的计算方式，对一次购买的会员，平均订购时间为当前时间订购时间；而对于多次购买的会员，平均订购时间为（最晚的订购时间最早的订购时间）/间隔数，间隔数为订购数-1。 本题考查SQL的触发器机制。触发器是通过事件触发而执行的，事件指的是对某个关系的插入、删除或更新。当触

11、发器被事件激活时，并不是立即执行，而是检测触发器的条件，若条件满足则执行触发器定义的动作。因此，创建一个触发器时，必须指定：触发器名称、在其上定义触发器的关系（表）、触发器将何时激发、触发器执行时应作的动作。本题中触发器定义的事件为会员订购图书，即插入一个订单记录，因此（n）的答案为INSERT ON订单。执行的动作是累加会员的消费额，并按照规则把本次订单的销售 额计算为积分，更新会员的积分，因此（o）的答案为积分=积分+INT（NROW.销售额/20）。 （a） PRIMARY KEY 或 NOT NULL UNIQUE 或 UNIQUE NOT NULL （b） REFERENCES会员（

12、用户名）或REFERENCES会员 （c） CHECK（订购日期=出货日期）或 CONSTRAINT chk date CHECK（订购日期=出货日期） （注：chk date可为其他名称） （1）（d）图书名称，作者，出版社，出版日期 （e）LIKE%数据库% （2）（f）* （g）图书.图书编号=订单明细.图书编号 （3）（h）SUM（数量） （i）订单订单编号=订单明细.订单编号 （j） SUM（数量）=ALL （4）（k）COUNT（*）1 （l）（COUNT（*）-1或COUNT（*）也给分 （m）GROUP BY 用户名 （n）INSERT ON订单 （o）积分=积分+INT（NROW.销售额/20） 试题三 某集团公司拥有多个大型连锁商场，公司需要构建一个数据库系统便于管理其业务运作