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

1、厦门大学数据库系统原理评分标准信息科学技术院计算机系2012年计算机专业考试教师：张东健站试卷类型：(a)一、回答：1.2、相应的关系模式结构如下：部门(部门名称、部门地址、部门电话)病房(病房号、床号、部门名称)诊断和治疗(案例编号，工作许可证编号)入住(案件编号，病房编号)部门配置(部门、病房号)医生(工作许可证号码、姓名、职称、部门名称、年龄)患者(医疗记录号、名字、性别、负责医生、病房号)每个关系的后期代码如下：部门的选择后代码是部门名称。病房的后选择代码是部门名病房。医生的选择后代码是工作许可证。患者的选择后代码是医疗记录号。评分标准：ER图：每个对象0.5点(2点)，每个对象属性0

2、.5点(6点)，每个关联关系0.5点(2点)关系样式：主要代码0.5点(2.5点)、外部代码0.5点(2点)、关系名称0.5点(0.5点)2，(50%)物流公司数据库包含以下五种关系学生S(Sno、Sname、Sage、Sex、Sdept)分别是学号、名字、年龄、性别和所属。社区a (ano、aname、a quality、lno、manager no、telephone、a status)，分别是社区编号、社区名称、社区性质、内部分行编号、联系人学号、新地点L(Lno，Lname)分别是新地点编号、地点名称。学生入职情况历史(SnO，ano，dtime)、学号、社区编号、入职时间。nashi

3、n d(lno，ano)，分别是nashin分支机构编号和社区编号。其中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=篮球协会)投影1点、选择1点、自然连接1点关系演示：范围S x；范围A y；范围SA z；Getw (x.sname，x . sdept): $ x $ y $ z(x . Sn

4、O=z . snoz . ano=y . ano y . aname=照片协会)语法2分，条件1分SQL:Select S.Sname，S.SdeptFrom s，sa，a wheres . SnO=sa . SnO and sa . ano=a . ano and a . aname=照片协会SQL语法1点，条件2点2，(6%)查询2014-9-1至2014-9-5 (2014-9-1和2014-9-5天除外)新人数至少为150人的社团的名字、性格和联系方式。Select*from A where Ano in(select ano from sa where dtime 2014-9-1

5、and dtime 2014-9-5Groupby Ano having count(*)=100)Select语法1点、group by语法2点、where条件1点、having条件2点3，(3%)要让从学校进入的计算机系的所有学生加入计算机协会(编号：007)，因此要采取纠正措施。update sa set ano= 007 where SnO in(select SnO fron s where sdept=计算机系统)语法2分，条件1分，更新顺序反转0分4、(8%)学校委员会将“秘书”、“副秘书”和“相关人员”3人(假设数据库中已经有这3名用户)用SQL语句设计安全策略，使“承包商”可

6、以查看200名以下社区的所有信息，副秘书则为200-Createview v1AsSelect*fromCWhere Csalary200Grantselecton v1to语料库Createview v2AsSelect*fromCWhere Csalary=200 and Csalary300Grantselecton v2to副部长Createview v3AsSelect*fromCWhere Csalary300Grantselecton v3to秘书视图创建1点、1点、1点、3点、8点满分5，(9%)输出2014-9-1至2014-9-5期间(2014-9-1和2014-9-5天除

7、外)，社区性质包含有关“体育”关键字的传出信息，输出Declare Sno varchar(50)Declare Sname varchar(50)Eclare Sdept varchar(50)Declare Ano varchar(50)Declare output nvarchar(MAX)Declare cur1 cursorforSelect distinct SA。Sno from SA，AWhere sa。ano=a. ano and a. a quality like%体育% and . d time 2014-9-1 and . d time 2014-9-5BeginOpe

8、n cur1Fetch next from cur1 into SnoWhile(fetch_status=0)BeginDeclare cur2 cursor forSelect s.sname、s.sdept、a.aname from s、sa、awhere s . SnO=sa . SnO and sa . ano=a . ano and a . a quality like %体育%andd . dtime 2014-9-1 and . dtime 2014-9-5 and s . SnO= SnOBeginOpen cur2Fetch next from cur 2 into sna

9、me， sdept， anameset output= sname/ sdept/ anameFetch next from cur 2 into sname， sdept， anameWhile(fetch_status=0)BeginSet output=output，AnameFetch next from cur 2 into sname， sdept， anameEndPrint outputClose cur2Deallocate cur2EndFetch next from cur1 into SnoEndClose cur1Deallocate cur2End定义变量两点，两级

10、光标分别定义三点和结果一点9，(9%)如果入社时间(Dtime)超过10天，请创建以新/新地点名称输入的存储过程，以输出具有新状态(如设置为全职的新/学生姓名/部门/社区名称/员工级别)的新/新记录。否则，将被设定为体验会员。提示：获取系统时间函数为getdate()，获取间隔天数的函数为datediff (DD，startdate，enddate)。例如，datediff (DD，2015-05-21，2015-05-23)将输出为2Createproc showDetailLname varchar(50)AsDeclare Sname varchar(50)Declare Sdept v

11、archar(100)Declare Aname varchar(50)Declare Dtime datetimeDeclare Status varchar(10)Declare Output varchar(MAX)Declare cur cur cur cur cur curForSelect s.sname、s.sdept、a.aname、d.dtime from d、s、l、as、aWhere A.Astatus=新建and L.Lname=Lnameand s . SnO=sa . SnO and sa . ano=a . ano and a . lno=l . lno and

12、. lno=a . lnoBeginOpen curFetch next from cur into sname， sdept， aname， dtimeWhile(fetch_status=0)BeginIf (datediff (DD， dtime，getdate ()=10)BeginSet Status=体验员工EndElseBeginSet Status=全职Endset output= sname/ sdept/ aname/ statusPrint OutputFetch next from cur into sname， sdept， aname， dtimeEndClose

13、curDeallocate curEnd存储过程生成2点，定义变量2点，光标2点，中间处理2点，结果1点7，(6%)插入社区信息时，编写将社区状态设置为“无新”的SQL完整性规则Createtrigger updateStatus温亚ForinsertAsBeginUpdate A set Astatus=无新选择从插入(Where a. ano in)End触发器生成3点，更新过程3点三、数据库设计模型分析(25%)业务组数据库包含以下关系模型：r(商店编号、商品编号、商店名称、商店地址、库存数量、商品名称、商品单价、部门编号、负责人)设置时：(1)各商店的各商品只在一个部门销售。(2)每个

14、商店的每个部门都只有一个负责人。(3)每个商店的每个商品只有一个库存数量。回答以下问题：(1)根据上述规定，编写关系模式r的基本函数依赖性。(2)查找关系模式r的候选代码。(3)判断r属于最高的第几个范式，给出了判断标准。(4)将r分解为3NF，分解保留函数依赖关系和无损链接，提供解决过程。(5)将r分解为BCNF，分解保留函数依赖关系和无损链接，提供解决过程。3.请参阅答案：1.有三种函数从属关系：4点(商店编号，商品编号)-部门编号(商店编号，部门编号)-主管(商店编号、商品编号)-库存数量(商店号码)-商店地址(商店编号)-商店名称(商品编号)-商品名称(商品编号)-商品单价根据主题逻辑，写前三个就给分2.r的候选代码是(商店编号，商品编号)4分3.r属于1NF(两点)，因为r具有某些函数从属关系(三点)4.将r分割为：6点R1(商店编号、商品编号、库存数量、部门编号)R2(商店编号、部门编号、主管)R3(商店编号、商店名称、商店地址)R4(商品编号、商品名称