数据库系统原理试卷(A)评分标准2015(2012年级)

一、解答：1.2、对应的关系模式结构如下：科室（科名，科地址，科电话）病房（病房号，床位号，科室名）诊治（病例号，工作证号）入住（病例号，病房号）科室组成（科室，病房号）医生（工作证号，姓名，职称，科室名，年龄）病人（病历号，姓名，性别，主管医生，病房号）每个关系的后选码如下：科室的后选码是科名。病房的后选码是科室名+病房号。医生的后选码是工作证。病人的后选码是病历号。评分标准：ER图：每个实体0.5分（2分），实体属性各0.5分（6分），关联关系各0.5分（2分）关系模式：主码0.5分（2.5分），外码0.5分（2分），关系名0.5分（0.5分）厦门大学《数据库系统原理》评分标准信息科学技术学院计算机系2012年级计算机专业主考教师：张东站试卷类型：（A）二、（50%）某物流公司数据库包含以下5个关系学生S（Sno,Sname,Sage,Sex,Sdept），分别为学号、姓名、年龄、性别和所在系。社团A（Ano,Aname,Aquality,Lno,Managerno,Telephone,Astatus），分别为社团编号、社团名称、社团性质、纳新地点编号，负责人学号，社团联系方式，纳新状态。纳新地点L（Lno,Lname），分别为纳新地点编号、地点名。学生入社情况SA（Sno,Ano,Dtime），分别为学号、社团编号和入社时间。纳新D（Lno,Ano），分别为纳新地点编号和社团编号。其中S由Sno唯一标识，A由Ano唯一标识，L由Lno唯一标识，SA由Sno和Ano唯一标识，D由Lno和Ano唯一标识。其中Dtime格式为yyyy-MM-dd(统一格式可直接比较大小)1、(9%)用关系代数、元组关系演示和SQL查询参加了全部社团的学生姓名和所在系。后面的操作仅用SQL关系代数:ΠS.Sname,S.Sdept(σA.Aname=篮球协会’(S∞SA∞A))投影1分，选择1分，自然连接1分关系演示：RangeSx;RangeAy;RangeSAz;GETW(x.Sname,x.Sdept):xyz（x.Sno=z.Sno^z.Ano=y.Ano^y.Aname='摄影协会'）语法2分，条件1分SQL:SelectS.Sname,S.SdeptfromS,SA,AwhereS.Sno=SA.SnoandSA.Ano=A.AnoandA.Aname=’摄影协会’SQL语法1分，条件2分2、(6%)查询2014-9-1至2014-9-5(不包括2014-9-1和2014-9-5这两天)纳新人数至少为150人的社团的名称、性质和联系方式。Select*fromAwhereAnoin(selectAnofromSAwhereDtime'2014-9-1'andDtime'2014-9-5'groupbyAnohavingcount(*)=100)Select语法1分，groupby语法2分，where条件1分，having条件2分3、(3%)学校需要让已入社的“计算机系”所有学生都改为加入“电脑协会”（编号：007），请进行修改操作。UpdateSAsetAno=’007’whereSnoin(selectSnofronSwhereSdept=’计算机系’)语法2分，条件1分，若更新顺序颠倒0分4、(8%)校团委有”书记”、”副书记”和”科员”三名管理人员（假设数据库中已经存在这三个用户）,用SQL语句设计一个安全策略，保证”科员”能够查看人数小于200的社团所有信息、副书记能够查看人数为200-300的社团的所有信息、书记能够查看人数大于300的社团所有信息。createviewv1asselect*fromCwhereCsalary200grantselectonv1to科员createviewv2asselect*fromCwhereCsalary=200andCsalary300grantselectonv2to副书记createviewv3asselect*fromCwhereCsalary300grantselectonv3to书记视图创建1分，正确选择1分，授权1分，三个授权，满分8分5、(9%)输出2014-9-1至2014-9-5期间(不包括2014-9-1和2014-9-5这两天)，社团性质包含“体育”关键字的纳新信息，输出格式化为”学生姓名/所在系/所在社团”，多个社团之间以逗号隔开。declare@Snovarchar(50)declare@Snamevarchar(50)eclare@Sdeptvarchar(50)declare@Anovarchar(50)declare@outputnvarchar(MAX)declarecur1cursorforselectdistinctSA.SnofromSA,AwhereSA.Ano=A.AnoandA.Aqualitylike'%体育%'andD.Dtime'2014-9-1'andD.Dtime'2014-9-5'beginopencur1fetchnextfromcur1into@Snowhile(@@fetch_status=0)begindeclarecur2cursorforselectS.Sname,S.Sdept,A.AnamefromS,SA,AwhereS.Sno=SA.SnoandSA.Ano=A.AnoandA.Aqualitylike'%体育%'andD.Dtime'2014-9-1'andD.Dtime'2014-9-5'andS.Sno=@Snobeginopencur2fetchnextfromcur2into@Sname,@Sdept,@Anameset@output=@Sname+'/'+@Sdept+'/'+@Anamefetchnextfromcur2into@Sname,@Sdept,@Anamewhile(@@fetch_status=0)beginset@output=@output+','+@Anamefetchnextfromcur2into@Sname,@Sdept,@Anameendprint@outputclosecur2deallocatecur2endfetchnextfromcur1into@Snoendclosecur1deallocatecur2End定义变量2分，两层游标分别3分，结果1分9、(9%)编写一个存储过程，输入为纳新地点的名称，输出为在该地点有纳新记录，且纳新状态为“正在纳新”的纳新信息，输出格式为“学生姓名/所在系/社团名称/社员级别”，这里的“社员状态”设置方法为：若入社时间(Dtime)距今超过10天，则设置为“正式社员”；否则设置为“体验社员”。提示：获取系统时间函数为getdate()，获取间隔天数的函数为DATEDIFF(dd,[startdate],[enddate])。例如DATEDIFF(dd,'2015-05-21','2015-05-23')输出为2createprocshowDetail@Lnamevarchar(50)asdeclare@Snamevarchar(50)declare@Sdeptvarchar(100)declare@Anamevarchar(50)declare@Dtimedatetimedeclare@Statusvarchar(10)declare@Outputvarchar(MAX)declarecurcursorforselectS.Sname,S.Sdept,A.Aname,D.DtimefromD,S,L,AS,AwhereA.Astatus='正在纳新'andL.Lname=@LnameandS.Sno=SA.SnoandSA.Ano=A.AnoandA.Lno=L.LnoandD.Lno=A.Lnobeginopencurfetchnextfromcurinto@Sname,@Sdept,@Aname,@Dtimewhile(@@fetch_status=0)beginif(datediff(dd,@Dtime,getdate())=10)beginset@Status='体验社员'endelsebeginset@Status='正式社员'endset@Output=@Sname+'/'+@Sdept+'/'+@Aname+'/'+@Statusprint@Outputfetchnextfromcurinto@Sname,@Sdept,@Aname,@DtimeendclosecurdeallocatecurEnd存储过程创建2分，定义变量2分，游标2分，中间处理2分，结果1分7、(6%)用SQL写一个完整性规则，当插入社团信息时，将社团状态置为“未纳新”createtriggerupdateStatusonAforinsertasbeginupdateAsetAstatus='未纳新'whereA.Anoin(selectAnofrominserted)End触发器创建3分，更新过程3分三、数据库设计模型分析（25%）设某商业集团数据库中有一关系模式R如下：R(商店编号，商品编号，商店名称，商店地址，库存数量，商品名称，商品单价，部门编号，负责人)如果规定：（1）每个商店的每种商品只在一个部门销售；（2）每个商店的每个部门只有一个负责人；（3）每个商店的每种商品只有一个库存数量。试回答下列问题：（1）根据上述规定，写出关系模式R的基本函数依赖；（2）找出关系模式R的候选码；（3）判断R最高属于第几范式，给出判断依据；（4）将R分解成3NF，分解保持函数依赖和无损链接，给出求解过程。（5）将R分解成BCNF，分解保持函数依赖和无损链接，给出求解过程。3.参考答案：1.有三个函数依赖：4分（商店编号，商品编号）-部门编号（商店编号，部门编号）-负责人（商店编号，商品编号）-库存数量（商店编号）-商店地址（商店编号）-商店名称（商品编号）-商品名称（商品编号）-商品单价基于题目逻辑，写出前三个即给分2.R的候选码是（商店编号，商品编号）4分3.因为R中存在部分函数依赖（3分），所以R属于1NF（2分）4.将R分解成：6分R1（商店编号，商品编号，库存数量，部门编号）R2（商店编号，部门编号，负责人）R3（商店编号，商店名称，商店地址）R4（商品编号，商品名称，商品单价）R5（商店编号，商品编号）5.将R分解成：6分R1（商店编号，商店名称）R2（商店编号，商店地址）R3（商品编号，商品名称）R4（商品编号，商品单价）R5（商店编号，商品编号，库存数量，部门编号）R6（商店编号，部门编号，负责人）分解结果可能不唯一第四题评分标准：反证法：（1’）假设R2NF，1’（1’）则存在（X码，Y非主属性），1’（1’）即子集，不包含码。2’（2’）RBCNF任给V-W则V必然包含码1’R2NF。