中级数据库系统工程师-2021年上半年《数据库系统工程师》案例分析真题

中级数据库系统工程师-2021年上半年《数据库系统工程师》案例分析真题问型即i题五5分5C某竞靠:理L勺部分数据库关系模式如下选手PLAYERPnO Pha江南博哥me,Sex.Region,Tel,各属性分别表示参赛选手的编号、姓名、性别、地区和联系电话:竞赛项目CONTESTCnO,Chame,Type,Date,各属性分别表示竞赛项目的编号、名称、类别和举办日期；选手参赛PCPno.Cno,City,Rank,Point,各属性分别表示选手编号、竞赛项目编号、竞赛所在城市、选手取得的名次和积分有关关系模式的说明如下⑴下划线标出的属性是表的主码⑵选手参赛表的属性Pno和Cno分别参照了选手表和竞赛项目表的主码⑶一个选手参加一项竞赛有一个名次和一一个积分，名次有4个取值“一”“二”“三”无”另外，竞赛所在城市不能为空根据以上描述，回答下列问题，将SQL语句的空缺部分补充完整【问题1]5分请将下面创建选手参赛表PC的SQL语句补充完整，要求定义实体完整性约束、参照完整性约束，以及其他完整性约束CREATE TABLEPCPno CHAR10REFERENCESaPno,Cno CHAR3REFERENCESbCno,City CHAR20cRank CHAR20dPointSMALLIT,e【问题2】6分查询所有未参加正确答案参考解析【问题1】aPLAYERbCONTESTcNOT NULLdCHECK Rankin—，，，二「，三，，无，ePRIMARY KEYPno,Cno【问题2】f:PLAYER g:not inh:pnoi:pc.eno=contest,enoj:andk:order by【问题3】1deletemPCndeleteoCONTEST

2.某数据库系统采用数据转储方式对数据和日志文件进行离线备份，用检查点机制进行恢复假设部分其日志文件如表5-1所示日志记录内容中表日志记求列表5-1口志记录编号日志记录内容LSN1T1,START〉LSN2TlJ223//LSN3T2,START〉LSN4T2X32,37LSN5T3,STARTLSN6vT2J45,5LSN7T7,START〉LSN8T1,COMMITLSN9T3M5315//,LSN1OLSN11CHECKPOINTLSN12T2,COMMITLSN13CRASH【问题1】6分假设各数据项的初始值为「22,J=45,K-9,系统出错恢复后，I,J,K的数值会恢复为多少？【问题2】4分请给出系统恢复时需要重做Redo的事务列表和需要撤销undo的事务列表【问题3]5分假设掉电造成磁盘介质损坏，数据库无法启动，请用100字以内的文字简要说明其恢复过程正确答案参考解析【问题1】I,J,K的数值会恢复为1=3,J=5,K=9【问题2】Redo:T2UndoT3【问题3】由于掉电造成磁盘介质损坏，数据库已经无法启动，恢复过程的步骤如下所示1装入最新的数据库后备副本，使数据库恢复到最近一次转储时的一致性状O2转入相应的日志文件副本，重做已完成的事务

3.某社区蔬菜团购网站，为规范商品收发流程，便于查询客户订单情况，需要开发个信息系统请根据下述需求描述完成该系统的数据库设计［需求描述］⑴记录蔬菜供应商的信息，包括供应商编号、地址和一个电话⑵记录社区团购点的信息，包括团购点编号、地址和一个电话⑶记录客户信息，包括客户姓名和一个电话客户可以在不同的社区团购点下订单，不直接与蔬菜供应商发生联系⑷记录客户订单信息，包括订单编号、团购点编号、客户电话、订单内容和日期［概念模型设计］根据需求阶段收集的信息，设计的实体联系图不完整如图1-1所示图实体联系图［逻辑结构设计］根据概念模型设计阶段完成的实体联系图，得出如下关系模式（不完整）蔬菜供货商（供货商编号，地址，电话）社区团购点（团购点编号，地址，电话）供货（供货商编号，（a））客户（姓名，客户电话）订单（订单编号，团购点编号，订单内容，日期，（b））【问题1】（6分）根据问题描述，补充图1T的实体联系图【问题2】（4分）补充逻辑结构设计结果中的（a）、（b）两处空缺及完整性约束关系【问题3】（5分）若社区蔬菜团购网站还兼有代收快递的业务，请增加新的“快递”实体，并给出客户实体和快递实体之间的“收取联系，对图1进行补充“快递”关系模式包括快递编号、客户电话和日期正确答案参考解析【问题1】依据题干中的关系设计（a）处应该为主键的定义，（b）和（c）处应该为外键的定义（a）PRIMARY KEY（b）REFERENCES VEM（VEMno）（c）REFERENCES GOODS（Gno）【问题2】本题是考查创建视图的语法CREATE VIEW视图名（列名［,••n］）・AS SELECT查询子句（d）处应该制定分组的依据，由于SELECT语句后跟随了VEMno,Gno,SDate这些列，所以（d）处应该列表这些列（e）处应该为创建视图的名称:SALES_Detail（VEMno,Location,Gno,Brand,Price,Amount,SDate）（f）和（g）处应该制定查询语句中涉及到的3个关系之间的连接条件（f）VEM.VEMno=SALES_Total.VEMno；（g）GOODS.Gno=SALES_Total.Gno；f和g可以互换【问题3】本题是考查创建触发器的语句CREATE TRIGGER触发器名［｛BEFORE|AFTER｝］｛事件｝ON〈对象名〉REFERENCING{OLD ASold|{NEW ASnew}[FOR EACHROW][WHEN条件]{触发器执行体}h处应该为关键字TRIGGERi处应该是插入数据时触发，所以应该为INSERT00S VEMno,Gno,SDate,STime共有4个属性且为全码表，INSERT语句必须插入所有列的值，因此j处应该为SALES.Gno,SALES.Sdate【问题4】题干中要求返回数量，因此k COUNT*AS数量1处是分组的依据，分组的依据为商品编号，由于SELECT后跟随了列GOODS.Gno,Brand,所以1处应为GOODS.Gno,Brandm处是在分组的基础上制定筛选的条件；而子查询返回的是一系列的单个属性的值的集合，应该使用二人比来取得最大值，所以m处应填写COUNT*=ALL【问题5】依据子查询SELECT DISTINCTGNO FROMo；应该是从销售表中查询出有销售记录的商品编号，所以处应为SALESn处为筛选条件，只有没有出现在销售表中的商品编号，也就意味着没有销售过，因此n处应为NOT IN

4.为防控新冠疫情，一些公共设施需要定期消毒，管理部门为高效完成工作并记承必要的工作信息，设计了相应的数据库，其中有一个表用来记承公共汽车的消毒情况，表的结构如下消毒记录日期，车牌号，行驶路线，消毒人员工号，消毒人员姓名其中车牌号和消毒人员工号唯一，同辆车保持固定的行驶路线假设同一人员每天可以负责多辆车的消毒工作【问题1】6分给出消毒记录表中成立且左侧只有一个属性的所有函数依赖关系题中设计的消毒记录表是否满足2NF请用100字以内的文字说明原因【问题2】6分如果要将消毒记录表规范化为满足3NF,请用100字以内的文字简要说明解决方案，并给出各个新表的主码和外码【问题3】3分如果每辆车每日有多次消毒，需要记录每次消毒的消毒时间，在问题2设计结果的基础上，如何在不破坏3NF且不增加冗余的前提下做到？请简单说明方案正确答案参考解析【问题1】左侧只有一个属性的函数依赖关系有2个，分别为车牌号行驶路线消毒人员工号-〉消毒人员姓名不满足2NF,根据题干可以得到消毒记录表的候选键为（日期，车牌号），由于存在车牌号-〉行驶路线，属于部分依赖根据2NF的要求，不存在非主数据对主属性的部分依赖，所以不满足2NF【问题2】由于消毒记录表不满足2NF,需要将消毒记录表进行拆分成多个表，拆分的表如下消毒记录1（车牌号，行驶路线）消毒记录2（消毒人员工号，消毒人员姓名）消毒记录3（车牌号，日期，消毒人员工号）由于拆分后的三个表都不存在非主属性对主属性的部分和传递函数依赖，所以都满足3NFo⑸）某企业网上书城系统的部分关系模式如下书籍信息表books（book no,book name,press no,ISBN,price,sale type,all nums）,其中属性含义分别为:书籍编码、书籍名称、出版商编码、ISBN、销售价格、销售分类、当前库存数量书籍销售订单表orders（order no,book no,book nums,book price,orderdate,amount）,其中属性分别为:订单编码、书籍编码、书籍数量、书籍价格、订单日期和总金额书籍再购额度表booklimit（book no,sale_type,limitamount）,其中属性含义分别为书籍编码、销售分类、再购额度；书籍最低库存表bookminlevel（book no,leve）,其中属性含义分别为:书籍编码」书籍最低库存数量；书籍采购表bookorders（book no,order._amount）,其中属性含义分别为书籍编码和采购数量一有关关系模式的说明如下⑴下划线标出的属性是表的主码⑵根据书籍销售情况来确定书籍的销售分类销售数量小于1万的为普通类型，其值为0;I万及以上的为热销类型，其值为I⑶系统具备书籍自动补货功能，涉及到的关系模式有:书籍再购额度表、书籍最低库存表、书籍采购表其业务逻辑是当某书籍库存小于其最低库存数量时，根据书籍的销售分类以及书籍再购额度表中的再购额度，生成书籍采购表中的采购订单，完成自动补货操作【问题1］（5分）系统定期扫描书籍销售订单表，根据书籍总的销售情况来确定书籍的销售类别下面是系统中设置某书籍销售类别的存储过程，结束时需显式提交返回请补全空缺处的代码CREATE PROCEDUREUpdateBookSaleTypeIN bnovarchar20DECLAREall_nums number6;BEGINSELECT abook_nums INTOall_nums FROMordersWHERE book_no=b;IF all_numsc THENUPDATEbooks SET sale_type=0WHERE book_no=bno;ELSEUPDATE booksSETsale_type=d WHEREbook_no=bno;END IF;e;END;【问题2】6分下面是系统中自动补货功能对应的触发器，请补全空缺处的代码CREATE TRIGGERBookOrdersTrigger fupdateof gon bookshWHENiSELECT levelFROM bookminlevelWHEREbookminlevel.book_no=OLD.book_noAND j=SELECT levelFROM bookminlevelWHEREbookminlevel.book_no=OLD.book_moBEGININSERT INTOkSELECT book_no,limit_amountFROM booklimitas TMPWHERETMP.book_no=OLD.book_noAND TMP.sale_type=OLD.sale_type;END;【问题3】4分假设用户1和用户2同时购买同一书籍，对应事务的部分调度序列如表4-1所示事务中未进行并发控制，其中T0时刻该书籍的库存数量all nums=500o表事务运行部分调度本意表4-1时间用户2事务用户事务1TOT1readall_numsall_nums=all_nums-2T2T3v/riteall_numsT4readallnumsallnums=all_nums-3T5writeall_numsT6ROLLBACKT7T8请说明T4时刻，用户2事务读取到的all nums数值是多少请说明T8时刻，allnums数据是否出现不一致性问题如出现，请说明属于哪一种数据不一致性正确答案.参考解析;【问题1】a:sum b:bnoc:10000d:1e:commit【问题2】faftergall_numsh:for eachrow i:NEW.all_nums j:OLD.all_nums k:bookorders【问题3】说明T4时刻，用户2事务读取到的all_nums数值是498新版章节练习，考前压卷，完整优质题窿+考生笔记分享，实时更新，软件，在T8时刻，all_nums数据会出现不一致性的问题，由于用户2事务读到了用户1修改过的all_nums,然后在T7时刻用户1事务回滚了之前对all_nums的修改，把an_nums恢复到了500最终用户2事务读到的数据是498,读到的是脏数据所以是属于读脏数据的不一致。