沈阳师范--数据库原理---综合练习题

【沈师710寝室】---数据库综合题【一】学生-课程数据库由以下三个关系模型构成：(各关系模式中黑体并加下划线的属性为主码)Student(Sno,Sname,Sex,Sage,Sdept)分别表示:学号、姓名、性别、年龄、所在系Course(Cno,Cname,Cpno,Credit)分别表示:课程号、课程名、先行课课程号、学分SC(Sno,Cno,Grade)分别表示:学号、课程号、成绩1.用SQL语句实现SC表的建立。要求如下：(1)Sno，Cno均为字符型，长度自定义，不允许取空值，（Sno，Cno）是SC表的主码；(2)Grade为短整型，Grade的取值范围为0~100；(短整型的类型符号为smallint)(3)Sno参照引用Student表的主码；(4)Cno参照引用Course表的主码；2.请用关系代数完成如下的操作：(1)求计算机系年龄小于20的学生姓名；(2)求成绩90的学生姓名。3.请用SQL语句完成如下操作：(1)查询计算机系学生的姓名，年龄信息；(2)对学号为001的学生，找出其成绩80的课程名；(3)从Course中查询出课程号为1、3、5的课程的课程号、课程名及学号；(4)查询有3门以上课程成绩80的学生学号及成绩80的课程数；(5)数据修改：对计算机系所有选修了课程的学生，将其成绩增加10分；(6)允许用户U1对Course表中所有属性进行查看，对Create属性列可以修改，通过授权完成；【二】某企业集团计算机管理中需要如下信息：每个工厂生产多种产品，且每一种产品可以在多个工厂生产，每个工厂按照固定的计划数量生产产品；每个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、厂名、地址；产品的属性有产品编号、产品名、规格；职工的属性有职工号、姓名。(1)根据上述语义画出ER图，在ER图中需注明实体的属性、联系的类型及实体的标识符。(2)将ER模型转换成关系模型，并指出每个关系模型的主键和外键。【三】某医院病房计算机管理中心需要如下信息：科室：科名，科地址，科电话病房：病房号，床位号，所属科室名医生：姓名，职称，所属科室名，年龄，工作证号病人：病历号，姓名，性别，诊断，主管医生，病房号其中，一个科室有多个病房、多个医生；一个病房只能属于一个科室；一个一生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个。完成如下设计：(1)设计该计算机管理系统的ER图；(2)将该ER图转换为关系模型结构；(3)指出转换结果中每个关系模式的候选码。【沈师710寝室】---数据库综合题【四】设有表R，如下图所示：车间名产品编号产品名单价J1PX01M190J2PX02M2100J3PX03M2100J4PX04M1100若有如下事实：一个车间只能生产一种型号的产品，同一型号的产品可以在多个车间同时生产；同一名称的产品可有多种型号，某一型号的产品只能有一个名称；某一型号的产品只有一种单价，不同型号的产品可以有相同单价。试回答下列问题：(1)根据上述规定，写出关系模式R的基本函数依赖；(2)找出关系模式R的候选码；(3)关系模式R最高已经达到第几范式？为什么？(4)如果R属于3NF，请将R分解为3NF模式集。【五】设有表R，如下图所示：学号系名公寓楼号1001数学SS011002数学SS011003数学SS012001外语SS023006软件SS033011软件SS03若有如下事实：一个系有多名学生；一名学生且紧属于一个系；同系的学生都住在同栋楼内；不同系的学生住在不同的公寓楼。试回答下列问题：(1)根据上述规定，写出关系模式R的基本函数依赖；(2)找出关系模式R的候选码；(3)关系模式R最高已经达到第几范式？为什么？(4)如果R不属于3NF，请将R分解为3NF模式集。【六】假设某商业集团数据库中有一关系模式R如下：R(商品编码，商品编号，商品库存数量，部门编号，负责人)如果规定：(1)每个上点的每种商品只在该商店的一个部门销售；(2)每个商店的每个部门只有一个负责人；(3)每个商店的每种商品只有一个库存数量试回答下列问题：(1)根据上述规定，写出关系模式R的基本函数依赖；(2)找出关系模式R的候选码；(3)关系模式R最高已经达到第几范式？为什么？(4)如果R不属于3NF，请将R分解为3NF模式集。【沈师710寝室】---数据库综合题【七】有一个应用，包括三个实体集。实体类型“商店”的属性有：商店编号、店名、店址、店经理。实体类型“会员”的属性有：会员编号、会员名、地址。实体类型“职工”的属性有：职工编号、职工名、性别、工资。每家商店有若干职工，但每个职工只能服务于一家商店。每家商店有若干会员，每个会员可以属于多家商店。在联系中应反映出职工参加某商店工作的开始时间，会员的加入时间。(1)试画出反映商店、职工、会员实体类型及联系类型的ER图；(2)将ER图转换成关系模式，并指出每个表的主键和外键；【八】设有关系EMPLOYEE(E#,ENAME,EDEPT,DNAME,C#,PRIZE)，E#+C#为候选码，设关系中有如下函数依赖：(E#,C#)ENAME,EDEPT,DNAMEE#ENAME,EDEPT,DNAME(E#,C#)PRIZEEDEPTDNAME试求下列问题：(1)关系EMPLOYEE属于第几范式？(2)如果关系EMPLOYEE不属于第三范式，请将关系EMPLOYEE逐步分解为第三范式。要求：写出达到每一级范式的分解过程，并指明消除什么类型的函数依赖。【九】试用关系代数或SQL语句表达下列数据库中3个基本表S、SC、C的查询：S(S#,Sname,Age,Sex,Dept)SC(S#,C#,Grade)C(C#,Cname,Teacher)(1)查询张三同学的所在系(用关系代数完成)(2)查询成绩在90分以上的学生学号和课程名(用关系代数完成)；(3)求张老师所授课程的每门课程的学生平均成绩(用SQL查询语句完成)；(4)检索姓名以“王”开头的所有学生的姓名和年龄(用SQL查询语句完成)；【十】假设有下面的三个关系模式：Student(Sno，Sname，Ssex，Sage，Sdept)Course(Cno，Cname，Cpno，Ccredit)SC(Sno，Cno，Grade)(1)用SQL语言定义这三个关系模式，要求在模式中完成以下完整性约束条件的定义:a.定义每个模式的主码b.定义参照完整性c.定义学生年龄不得超过25岁(2)用关系代数语言或SQL语句完成如下操作：a.往student表中增添一条关于学生信息的数据，值为：Sno是11000，Sname是张三，Ssex是女，Sage为20岁，Sdept为计算机应用系。（用SQL语句完成）b.检索张三同学所在的系。（用关系代数完成）c.检索姓名以“王”开头的所有学生的姓名和年龄。（用SQL语句完成）【十一】设关系模式R(S#,C#,GRADE,TNAME,TADDR),其属性分别表示学生学号、选修课程的编号、成绩、任课教师姓名、任课教师地址等意义。如果规定，每个学生每学一门课只有一个成绩；每门课只有一个教师任教；每个教师只有一个地址(此处不允许教师同名同姓)。(1)试写出关系模式R的候选码，主属性和非主属性。(2)试写出关系模式R基本的函数依赖。(3)试述2NF的定义，并把R分解成2NF模式集，同时说明理由。(4)试述3NF的定义，在第2NF基础上把R分解成3NF模式集，同时说明理由。【沈师710寝室】---数据库综合题『答案』【一】1.createtableSC(Snochar(9)notnull,Cnochar(4)notnull,Gradesmallintcheck(Grade=0andGrade=100),primarykey(Sno,Cno),foreignkey(Sno)referencesStudent(Sno),foreignkey(Cno)referencesCourse(Cno))；2.(1)∏Sname(σSdept=‘CS’∧Sage20(Student))∞(2)∏Sname(∏Sname,Sno(Student)∞∏Sno(σGrade90(SC)))3.(1)selectSname,SagefromStudentwhereSdept='CS'(2)selectCnamefromCourse,SCwhereCourrse.Cno=SC.CnoandSC.Grade80andSC.Sno='001'(3)selectCno,Cname,CreditfromCoursewhereCno='1'orCno='3'orCno='5'或者selectCno,Cname,CreditfromCoursewhereCnoin('1','3','5')(4)selectSno,count(Cno)fromSCwhereGrade80groupbySnohavingcount(Cno)3(5)updateSCsetGrade=grade+10where'CS'=(selectSdeptfromStudentwhereStudent.Sno=SC.Sno)(6)grantselect,update(Credit)ontableCoursetoU1【二】(1)m1nn(2)工厂(工厂编号，厂名，地址)主键：工厂编号产品(产品编号，产品名，规格)主键：产品编号职工(职工号，姓名)主键：职工号生产(工厂编号，产品编号，计划数量)主键：工厂编号，产品编号外键：工厂编号，产品编号聘用(职工号，工厂编号，聘期，工资)主键：职工号外键：工厂编号工厂厂编号厂名地址生产聘用产品职工产品名规格产品编号计划数量职工号工资聘期姓名【沈师710寝室】---数据库综合题『答案』【三】(1)n111nnn1(2)对应的关系模型结构如下：科室(科名，科地址，科电话)病房(病房号，床位号，科室名)医生(工作证号，姓名，职称，科室名，年龄)病人(病历号，姓名，性别，诊治，主管医生，病房号)(3)每个关系模式的候选码如下:科室的候选码：科名病房的候选码：科室名、病房号医生的候选码：工作证号病人的候选码：病历号【四】(1)函数依赖：车间名产品型号产品型号产品名产品型号单价(2)模式R的候选码是车间名(3)模式R中不存在非属性对主属性的部分函数依赖，所以R属于第二范式，又由于R中存在非主属性产品名、单价对主属性的传递函数依赖，所以R不属于第三范式，因此R最高属于第二范式。(4)将R分解为：R1(车间名，产品型号)R2(产品型号，产品名，单价)【五】(1)函数依赖：学号系名系名公寓楼号(2)模式R的候选码是学号(3)模式R中不存在非属性对主属性的部分函数依赖，所以R属于第二范式，又由于R中存在非主属性公寓楼号对主属性的传递函数依赖，所以R不属于第三范式，因此R最高属于第二范式。(4)将R分解为：R1(学号，系名)R2(系名，公寓楼号)【六】(1)函数依赖：(商店编号，商品编号)部门编号(商店编号，部门编号)负责人(商店编号，商品编号)商品库存数量(2)模式R的候选码是(商店编号，商品编号)(3)模式R中不存在非属性对主属性的部分函数依赖，所以R属于第二范式，又由于R中存在非主属性(商店编号，商品编号)对主属性的传递函数依赖，所以R不属于第三范式，因此R最高属于第二范式。(4)将R分解为：R1(商店编号，商品编号，商品库存数量，部门编号)R2(商店编号，部门编号，负责人)病房号工作证号床位号科电话科名科地址姓名职称年龄性别姓名病历号病人入住科室病房医生诊治组成从属【沈师710寝室】---数据库综合题『答案』【七】(1)NM1N(2)商店模式（商店编号，店名，店址，店经理）主键：商店编号职工模式（职工编号，职工名，性别，工资，商店编号，开始参加工作时间）主键：职工编号外键：商店编号会员模式（会员编号，会员名，地址）主键：会员编号SM模式（会员编号，商店编号，会员加入时间）主