大数据库 考试大题

word假设教学管理规定：一个学生可选修多门课，一门课有假如干学生选修；一个教师可讲授多门课，一门课只有一个教师讲授；一个学生选修一门课，仅有一个成绩。学生的属性有学号、学生某某；教师的属性有教师编号，教师某某；课程的属性有课程号、课程名。要求：根据上述语义画出ER图，要求在图中画出实体的属性并注明联系的类型；解答：三、应用题：设有如下所示的关系S(S#,SNAME,AGE,SEX)、C(C#,AME,TEACHER)和SC(S#,C#,GRADE)，试用关系代数表达式表示如下查询语句：(1)检索“程军教师所授课程的课程号(C#)和课程名(AME)。 (2)检索年龄大于21的男学生学号(S#)和某某(SNAME)。 (3)检索至少选修“程军教师所授全部课程的学生某某(SNAME)。 (4)检索李强同学不学课程的课程号(C#)。 (5)检索至少选修两门课程的学生学号(S#)。 (6)检索全部学生都选修的课程的课程号(C#)和课程名(AME)。 (7)检索选修课程包含“程军教师所授课程之一的学生学号(S#)。 (8)检索选修课程号为k1和k5的学生学号(S#)。 (9)检索选修全部课程的学生某某(SNAME)。 (10)检索选修课程包含学号为2的学生所修课程的学生学号(S#)。 (11)检索选修课程名为“C语言的学生学号(S#)和某某(SNAME)。 解：此题各个查询语句对应的关系代数表达式表示如下：(1). C#,AME(TEACHER=程军(C) (2). S#,SNAME(AGE21SEX=男(C) (3). SNAMEswv S#,C#scC#(TEACHER=程军(C) (4). C#(C)- C#(SNAME=李强(S)wv SC) (5). S#(1=425 (SC SC)(6). C#,AME(Cwv (S#,C#(sc)S#(S) (7). S#(SCwvC#(TEACHER=程军(C) (8). S#,C#scC#(C#=k1 C#=k5(C) (9). SNAMEswv S#,C#scC#(C) (10). S#,C#scC#(S#=2(SC)(11). S#,SNAMEswv S#(SCwvAME=C语言(C)3、设有如下关系表R： R(No，NAME，SEX，AGE，CLASS) 主关键字是NO其中NO为学号，NAME为某某，SEX为性别，AGE为年龄，CLASS为班号。写出实现如下功能的SQL语句。插入一个记录(25，“李明，“男，21，“95031)； 。插入“95031班学号为30、某某为“X和的学生记录； 。将学号为10的学生某某改为“王华； 。将所有“95101班号改为“95091； 。删除学号为20的学生记录； 。删除姓“王的学生记录； 。答案：INSERT INTO R VALUES(25，“李明，“男，21，“95031)INSERT INTO R(NO，NAME，CLASS) VALUES(30，“X和，“95031)UPDATE R SET NAME=“王华WHERE NO10UPDATE R SET CLASS“95091WHERE CLASS“95101DELETE FROM R WHERE NO=20DELETE FROMR WHERE NAME LIKE“王1、设学生课程数据库中有三个关系：学生关系SS#，SNAME，AGE，SEX学习关系SCS#，C#，GRADE课程关系CC#，AME其中S#、C#、SNAME、AGE、SEX、GRADE、AME分别表示学号、课程号、某某、年龄、性别、成绩和课程名。用SQL语句表达如下操作1检索选修课程名称为“MATHS的学生的学号与某某 2检索至少学习了课程号为“C1和“C2的学生的学号3检索年龄在18到20之间含18和20的女生的学号、某某和年龄4检索平均成绩超过80分的学生学号和平均成绩5检索选修了全部课程的学生某某6检索选修了三门课以上的学生的某某9 / 9答案：1SELECT SNAME,AGE FROM S,SC,C WHERE S.S#=SC.S#AND C.C#=SC.C# AND AME= MATHS (2) SELECT S# FROM SC WHERE O=C1 AND S# IN( SELECT S# FROM SC WHERE O=C2) (3)SELECT S#,SNAME,AGE FROM S WHERE AGE BETWEEN 18 AND 20 (4) SELECT S# ,AVG(GRADE) 平均成绩FROM SC GROUP BY S# HAVING AVG(GRADE)80(5)SELECT SNAMEFROM SWHERE NOT EXISTS (SELECT *FROM CWHERE NOT EXISTS(SELECT *FROM SCWHERE S# AND C#) (6) SELECT SNAME FROM S,SC WHERE S.S#=SC.S# GROUP BY SNAME HAVING COUNT(*)3 2、设学生-课程数据库中包括三个表： 学生表：Student Sno，Sname，Sex，Sage，Sdept课程表：Courseo，ame，Ccredit学生选课表：SCSno，o，Grade其中Sno、Sname、Sex、Sage、Sdept、 o、ame、Ccredit 、Grade分别表示学号、某某、性别、年龄、所在系名、课程号、课程名、学分和成绩。试用SQL语言完成如下项操作：1查询选修课程包括“1042号学生所学的课程的学生学号2创建一个计科系学生信息视图S_CS_VIEW，包括Sno学号、Sname某某、Sex性别;3通过上面第2题创建的视图修改数据，把王平的名字改为王慧平4创建一选修数据库课程信息的视图，视图名称为datascore_view，包含学号、某某、成绩。答案：(1) SELECT DISTINCT SNO FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCYNO = 1042 AND NOT EXISTS (SELECT * FROM SC SCZNONO ANDNONO)； (2) CREATE VIEW S_CS_VIEW AS SELECT SNO,SNAME,SEX FROM STUDENT WHERE Sdept=CS (3)UPDATE S_CS_VIEW SET SNAME= 王慧平 WHERE SNAME= 王平(4) CREATE VIEW datascore_view AS SELECT SNO 学号、SNAME 某某、GRADE 成绩 FROM STUDENT,SC,COURSE AND AME= 数据库三、设计题在学生课程管理数据库中创建一触发器，当向学生选课表插入记录时，检查该记录的学号在学生表中是否存在，检查该记录的课程号在课程表中是否存在，与选课成绩是否在0到100X围，假如有一项为否，如此不允许插入。答案：create trigger stu_ins_trion scfor insertasbegin declare s# char(6),c# char(5),grade int select s#=sno,c#=o,grade=scorefrom insertedif (s# not in(select sno from student) or (c# not in (select o from course) )or (grade not between 0 and 100)rollback transaction elseprint 成功插入end四、综合练习1、学生关系模式S(Sno，Sname，SD，Sdname，Course，Grade)其中：Sno学号、Sname某某、SD系名、Sdname系主任名、Course课程、Grade成绩。(1)写出关系模式S的根本函数依赖和主码。(2)原关系模式S为几X式？为什么？分解成高一级X式，并说明为什么? (3)将关系模式分解成3NF，并说明为什么? (1)写出关系模式S的根本函数依赖和主码。答: 关系模式S的根本函数依赖如下： SnoSname，SDSdname，SnoSD，(Sno，Course) Grade关系模式S的码为：Sno，Course。(2)原关系模式S为几X式？为什么？分解成高一级X式，并说明为什么?答: 原关系模式S是属于1NF的，码为(Sno，Course)，非主属性中的成绩完全依赖于码，而其它非主属性对码的函数依赖为局部函数依赖，所以不属于2NF。消除非主属性对码的函数依赖为局部函数依赖，将关系模式分解成2NF如下：S1(Sno，Sname，SD，Sdname)S2(Sno，Course，Grade) (3)将关系模式分解成3NF，并说明为什么?答: 将上述关系模式分解成3NF如下：关系模式S1中存在SnoSD，SDSdname，即非主属性Sdname传递依赖于Sno，所以S1不是3NF。进一步分解如下： S11(Sno，Sname,SD) S12(SD，Sdname)分解后的关系模式S11、S12满足3NF。对关系模式S2不存在非主属性对码的传递依赖，故属于3NF。所以，原模式S(Sno，Sname，SD，Sdname，Course，Grade)按如下分解满足3NF。S11(Sno，Sname，SD) S12(SD，Sdname)S2(Sno，Course，Grade) 2、设有如下关系R (1)它为第几X式? 为什么? (2)是否存在删除操作异常?假如存在，如此说明是在什么情况下发生的? (3)将它分解为高一级X式，分解后的关系是如何解决分解前可能存在的删除操作异常问题? (1)解:它是2NF。因为R的候选关键字为“课程名。依赖关系: 课程名教师名，教师名课程名，教师名教师地址，所以课程名教师地址。即存在非主属性“教师地址对候选关键字课程名的传递函数，因此R不是3NF。但：因为不存在非主属性对候选关键字的局部函数依赖，所以R是2NF。(2)解: 存在。当删除某门课程时会删除不该删除的教师的有关信息。(3)解: 分解为高一级X式如下列图。R1如下: R2如下: 分解后，假如删除课程数据时，仅对关系R1操作，教师地址信息在关系R2中仍然保存，不会丢失教师方面的信息。 3、设某商业集团数据库中有一关系模式R如下：R 商店编号，商品编号，数量，部门编号，负责人如果规定：1 每个商店的每种商品只在一个部门销售；2 每个商店的每个部门只有一个负责人；3 每个商店的每种商品只有一个库存数量。试回答如下问题：1 根据上述规定，写出关系模式R的根本函数依赖；答: 关系模式S的根本函数依赖如下： (商店编号，商品编号) 部门编号，商店编号，部门编号负责人，(商店编号，商品编号) 数量2 找出关系模式R的候选码；答:关系模式R的码为：商店编号，商品编号, 部门编号。3 试问关系模式R最高已经达到第几X式？为什么？答: 原关系模式R是属于1NF的，码为商店编号，商品编号, 部门编号，非主属性对码的函数依赖全为局部函数依赖，所以不属于2NF。消除非主属性对码的函数依赖为局部函数依赖，将关系模式分解成2NF如下：R1(商店编号，商品编号, 部门编号, 数量)R2(商店编号，部门编号, 负责人) 4 如果R不属于3NF，请将R分解成3NF模式集。答：将R分解为R1(商店编号，商品编号, 部门编号, 数量)R2(商店编号，部门编号, 负责人) 分解后的R不存在传递的函数依赖，所以分解后的R已经是第3NF三、应用题设有如下实体：学生：学号、单位、某某、性别、年龄、选修课程名 课程：编号、课程名、开课单位、任课教师号 教师：教师号、某某、性别、职称、讲授课程编号 单位：单位名称、 、教师号、教师名上述实体中存在如下联系：1一个学生可选修多门课程，一门课程可为多个学生选修；2一个教师可讲授多门课程，一门课程可为多个教师讲授；3一个单位可有多个教师，一个教师只能属于一个单位。试完成如下工作：1分别设计学生选课和教师任课两个局部信息的结构E-R图。2将上述设计完成的E-R图合并成一个全局E-R图。3将该全局E-R图转换为等价的关系模型表示的数据库逻辑结构。解：1学生选课、教师任课局部E-R图如下所示。学生选课局部E-R图教师授课局部E-R图2合并后的全局E-R图如下所示。全局E-R图为防止图形复杂，下面给出各实体属性：单位：单位名、 学生：学号、某某、性别、年龄教师：教师号、某某、性别、职称课程：编号、课程号3该全局E-R图转换为等价的关系模型表示的数据库逻辑结构如下：单位单位名， 教师教师号，某某，性别，职称，单位名课程课程编号，课程名，单位名学生学号，某某，性别，年龄，单位名讲授教师号，课程编号选修学号，课程编号四、简答题数据库设计一般分为哪几个阶段，每个阶段的主要任务是什么？解答：1数据库设计分为6个阶段：需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库运行和维护。2各阶段任务如下：需求分析：准确了解与分析用户需求包括数据与处理。概念结构设计：通过对用户需求进展综合、归纳与抽象，形成一个独立于具体 DBMS 的概念模型。逻辑结构设计：将概念结构转换为某个 DBMS 所支持的数据模型，并对其进展优化。数据库物理设计：为逻辑数据模型选取一个最适合应用环境的物理结构包括存储结构和存取方法。数据库实施：设计人员运用 DBMS 提供的数据语言、工具与宿主语言，根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进展试运行。 数据库运行和维护：在数据库系统运行过程中对其进展评价、调整与修改。设计题1、今有两个关系模式：职工职工号，某某，年龄，职务，工资，部门号Person(pno, pname, page, ptitle, psal, dno)部门 部门号，名称，经理名，地址， 号Depart(dno, dname, dmanager, daddress, dtel)请用SQL的GRANT和REVOKE语句(加上视图机制)完成以下授权定义或存取控制功能：(1)用户yong对两个表有INSERT 和DELETE权力。GRANT INSERT,DELETE ON Person,DepartTO yong(2)用户liu对职工表有SELECT权力，对工资字段具有更新权力。GRANT SELECT,UPDATE(psal) ON PersonTO liu(3)用户zhang具有修改这两个表的结构的权力。GRANT ALTER TABLE ON Person,DepartTO zhang;(4)用户yang具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权力，他不能查看每个人的工资，并具有给其他用户授权的权力。CREATE VIEW psalASSELECT Depart.dname,MAX(psal),MIN(psal),AVG(psal)FROM Person,DepartWHERE Person.dno=Depart.dnoGROUP BY Person.dnoGRANT SELECT ON psalTO yang with grant option ;(5)撤销各用户yang所授予的权力REVOKE ALL PRIVILIGES ON Person,Depart FROM YANG; 2、假设有下面两个关系模式：职工职工号，某某，年龄，职务，工资，部门号，其中职工号为主码；部门部门号，名称，经理名， ，其中部门号为主码。用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：定义每个模式的主码；定义参照完整性；定义职工年龄不得超过60岁。答CREATE TABLE DEPT(Deptno NUMBER(2),Deptname VARCHAR(10),Manager VARCHAR(10),PhoneNumber Char(12)CONSTRAINT PK_SC RIMARY KEY(Deptno);CREATE TABLE EMP(Empno NUMBER(4),Ename VARCHAR(10),Age NUMBER(2),CONSTRAINT C1 CHECK ( Aage1000； 4写一个断言，要求区域名为“EAST的商店里销售商品的单价不能低于100元。解：CREATE ASSERTION ASSE8 CHECK100=ALLSELECT PRICE FROM SHOP A，SALE B，GOODS C WHERE A.S#=B.S# AND B.G#=C.G# AND AREA=EAST；或 CREATE ASSERTION ASSE8 CHECKNOT EXISTSSELECT * FROM SHOP A，SALE B，GOODS C WHERE A.S#=B.S# AND B.G#=C.G# AND AREA=EAST AND PRICE100； 5试写出如下操作的SQL语句： 统计区域名为“EAST的所有商店销售的每一种商品的总数量和总价值。 要求显示G#，GNAME，SUM_QUANTITY，SUM_VALUE，其属性为商品编号、商品名称、销售数量、销售价值。解：SELECT C.G#，GNAME，SUMQUANTITYAS SUM_QUANTITY，PRICE*SUMQUANTITYAS SUM_VALUEFROM SHOP A，SALE B，GOODS CWHERE A.S#=B.S# AND B.G#=C.G# AND AREA=EASTGROUP BY C.G#，GNAME；注：SELECT子句中的属性C.G#，GNAME应在分组子句中出现4、今有一个层次数据库实例，试用子女一兄弟法和层次序列法画出它的存储结构示意图。