资源描述
精选优质文档-倾情为你奉上厦门大学数据库系统原理评分标准信息科学技术学院计算机系2012年级计算机专业主考教师：张东站 试卷类型：（A）一、解答：1.2、对应的关系模式结构如下： 科室（科名，科地址，科电话） 病房（病房号，床位号，科室名）诊治（病例号，工作证号）入住（病例号，病房号）科室组成（科室，病房号）医生（工作证号，姓名，职称，科室名，年龄） 病人（病历号，姓名，性别，主管医生，病房号） 每个关系的后选码如下： 科室的后选码是科名。 病房的后选码是科室名+病房号。 医生的后选码是工作证。 病人的后选码是病历号。评分标准：ER图：每个实体0.5分（2分），实体属性各0.5分（6分），关联关系各0.5分（2分）关系模式：主码0.5分（2.5分），外码0.5分（2分），关系名0.5分（0.5分）2、 （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 =篮球协会(SSAA)投影1分，选择1分，自然连接1分关系演示：Range S x;Range A y;Range SA z;GET W (x.Sname, x.Sdept) : $ x$ y $ z（x.Sno =z.Sno z.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'and Dtime <'2014-9-5'groupby 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%)校团委有”书记”、”副书记”和”科员”三名管理人员（假设数据库中已经存在这三个用户）, 用SQL语句设计一个安全策略，保证”科员”能够查看人数小于200的社团所有信息、副书记能够查看人数为200-300的社团的所有信息、书记能够查看人数大于300的社团所有信息。createview v1asselect*fromCwhere Csalary<200grantselecton v1to科员createview v2asselect*fromCwhere Csalary>=200 and Csalary<300grantselecton v2to副书记createview v3asselect*fromCwhere Csalary>300grantselecton v3to书记视图创建1分，正确选择1分，授权1分，三个授权，满分8分5、(9%)输出2014-9-1至2014-9-5期间(不包括2014-9-1和2014-9-5这两天)，社团性质包含“体育”关键字的纳新信息，输出格式化为”学生姓名/所在系/所在社团”， 多个社团之间以逗号隔开。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.Aquality like'%体育%'and D.Dtime>'2014-9-1'and D.Dtime<'2014-9-5'beginopen cur1fetch next from cur1 into Snowhile(fetch_status=0)begindeclare cur2 cursor forselect S.Sname, S.Sdept,A.Aname from S,SA,A where S.Sno = SA.Sno and SA.Ano=A.Ano and A.Aquality like'%体育%'and D.Dtime>'2014-9-1'and D.Dtime<'2014-9-5'and S.Sno=Snobeginopen cur2fetch next from cur2 into Sname, Sdept,Anameset output=Sname +'/'+ Sdept +'/'+ Anamefetch next from cur2 into Sname, Sdept,Anamewhile(fetch_status=0)beginset output = output +','+ Anamefetch next from cur2 into Sname,Sdept, Anameendprint outputclose cur2deallocate cur2 endfetch next from cur1 into Snoendclose cur1deallocate cur2End定义变量2分，两层游标分别3分，结果1分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 varchar(100)declare Aname varchar(50)declare Dtime datetimedeclare Status varchar(10)declare Output varchar(MAX)declare cur cursorforselect S. Sname,S. Sdept,A. Aname ,D.Dtime from D,S,L,AS,A where A.Astatus='正在纳新'and L.Lname=Lname and S.Sno = SA.Sno and SA.Ano = A.Ano and A.Lno = L.Lno and D.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 curdeallocate curEnd存储过程创建2分，定义变量2分，游标2分，中间处理2分，结果1分7、(6%)用SQL写一个完整性规则，当插入社团信息时，将社团状态置为“未纳新”createtrigger updateStatuson Aforinsertasbeginupdate A set Astatus='未纳新'where A.Ano in(select Ano from inserted)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必然包含码 1R2NF。专心-专注-专业
展开阅读全文