数据库考试设计题Word格式文档下载.docx
《数据库考试设计题Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《数据库考试设计题Word格式文档下载.docx(21页珍藏版)》请在冰点文库上搜索。
(3)实现πXM,XB?
(4)实现πXM,DWH(σXB=’女’?
)的SQL语句是__。
(5)实现R∞S的SQL语句是__。
(6)实现πXM,XB,DWH(σXB=’男’(R∞S))的SQL语句是__。
(1)SELECT*FROMRUNIONSELECT*FROMT
(2)SELECT*FROMRWHEREDWH=’100’(3)SELECTXM,XBFROMR(4)SELECTXM,DWHFROMRWHEREXB=’女’(5)SELECTR.BH,R.XM,R.XB,R.DWH,S.DWMFROMR,SWHERER.DWH=S.DWH(6)SELECTR.XM,R.XB,S.DWHFROMR,SWHERER.DWH=S.DWHANDR.XB=’男’
针对上题中建立的四个表试用SQL语言完成第2章习题5中的查询。
(填空题)
(1)求供应工程J1零件的供应商号码SNO;
SELECTSNOFROMSPJWHEREJNO=‘J1’;
(2)求供应工程J1零件P1的供应商号码SNO;
SELECTSNOFROMSPJWHEREJNO=‘J1’ANDPNO=‘P1’;
(3)求供应工程J1零件为红色的供应商号码SNO;
SELECTSNO/*这是嵌套查询*/FROMSPJWHEREJNO=‘J1’ANDPNOIN/*找出红色的零件的零件号码PNO*/(SELECTPNOFROMP/*从P表中找*/WHERECOLOR=‘红’);
或SELECTSNOFROMSPJ,P/*这是两表连接查询*/WHEREJNO=‘J1’/*这是复合条件连接查询*/ANDSPJ.PNO=P.PNOANDCOLOR=‘红’;
1、设学生课程数据库中有三个关系:
学生关系S(S#,SNAME,AGE,SEX)
学习关系SC(S#,C#,GRADE)
课程关系C(C#,CNAME)
其中S#、C#、SNAME、AGE、SEX、GRADE、CNAME分别表示学号、课程号、姓名、年龄、性别、成绩和课程名。
用SQL语句表达下列操作
(1)检索选修课程名称为“MATHS”的学生的学号与姓名
(2)检索至少学习了课程号为“C1”和“C2”的学生的学号
(3)检索年龄在18到20之间(含18和20)的女生的学号、姓名和年龄
(4)检索平均成绩超过80分的学生学号和平均成绩
(5)检索选修了全部课程的学生姓名
(6)检索选修了三门课以上的学生的姓名
答案:
(1)SELECTSNAME,AGE
FROMS,SC,C
WHERES.S#=SC.S#
ANDC.C#=SC.C#
ANDCNAME=’MATHS’
(2)SELECTS#
FROMSC
WHERECNO=’C1’ANDS#IN(SELECTS#
WHERECNO=’C2’)
(3)SELECTS#,SNAME,AGE
FROMS
WHEREAGEBETWEEN18AND20
(4)SELECTS#,AVG(GRADE)‘平均成绩’
GROUPBYS#
HAVINGAVG(GRADE)>
80
(5)SELECTSNAME
FROMS
WHERENOTEXISTS
(SELECT*
FROMC
WHERENOTEXISTS
WHERES#=S.S#ANDC#=C.C#
)
)
(6)SELECTSNAME
FROMS,SC
GROUPBYSNAME
HAVINGCOUNT(*)>
3
2、设学生-课程数据库中包括三个表:
学生表:
Student(Sno,Sname,Sex,Sage,Sdept)
课程表:
Course(Cno,Cname,Ccredit)
学生选课表:
SC(Sno,Cno,Grade)
其中Sno、Sname、Sex、Sage、Sdept、Cno、Cname、Ccredit、Grade分别表示学号、姓名、性别、年龄、所在系名、课程号、课程名、学分和成绩。
试用SQL语言完成下列项操作:
(1)查询选修课程包括“1042”号学生所学的课程的学生学号
(2)创建一个计科系学生信息视图S_CS_VIEW,包括Sno学号、Sname姓名、Sex性别;
(3)通过上面第2题创建的视图修改数据,把王平的名字改为王慧平
(4)创建一选修数据库课程信息的视图,视图名称为datascore_view,包含学号、姓名、成绩。
(1)SELECTDISTINCTSNO
FROMSCSCX
FROMSCSCY
WHERESCY.SNO='
1042'
AND
NOTEXISTS
FROMSCSCZ
WHERESCZ.SNO=SCX.SNOAND
SCZ.CNO=SCY.CNO));
(2)CREATEVIEWS_CS_VIEW
AS
SELECTSNO,SNAME,SEX
FROMSTUDENT
WHERESdept=’CS’
(3)UPDATES_CS_VIEW
SETSNAME=’王慧平’
WHERESNAME=’王平’
(4)CREATEVIEWdatascore_view
SELECTSNO学号、SNAME姓名、GRADE成绩
FROMSTUDENT,SC,COURSE
WHERESTUDENT.SNO=SC.SNO
ANDCOURSE.CNO=SC.CNO
ANDCNAME=’数据库’
1、已知学生关系模式
S(Sno,Sname,SD,Sdname,Course,Grade)
其中:
Sno学号、Sname姓名、SD系名、Sdname系主任名、Course课程、Grade成绩。
(1)写出关系模式S的基本函数依赖和主码。
(2)原关系模式S为几范式?
为什么?
分解成高一级范式,并说明为什么?
(3)将关系模式分解成3NF,并说明为什么?
答:
关系模式S的基本函数依赖如下:
Sno→Sname,SD→Sdname,Sno→SD,(Sno,Course)→Grade
关系模式S的码为:
(Sno,Course)。
原关系模式S是属于1NF的,码为(Sno,Course),非主属性中的成绩完全依赖于码,而其它非主属性对码的函数依赖为部分函数依赖,所以不属于2NF。
消除非主属性对码的函数依赖为部分函数依赖,将关系模式分解成2NF如下:
S1(Sno,Sname,SD,Sdname)
S2(Sno,Course,Grade)
(3)将关系模式分解成3NF,并说明为什么?
答:
将上述关系模式分解成3NF如下:
关系模式S1中存在Sno→SD,SD→Sdname,即非主属性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)
2.设有如下关系R
(1)它为第几范式?
为什么?
(2)是否存在删除操作异常?
若存在,则说明是在什么情况下发生的?
(3)将它分解为高一级范式,分解后的关系是如何解决分解前可能存在的删除操作异常问题?
(1)它为第几范式?
解:
它是2NF。
因为R的候选关键字为“课程名”。
依赖关系:
课程名→教师名,教师名→课程名,教师名→教师地址,所以课程名→教师地址。
即存在非主属性“教师地址”对候选关键字课程名的传递函数,因此R不是3NF。
但:
因为不存在非主属性对候选关键字的部分函数依赖,所以R是2NF。
存在。
当删除某门课程时会删除不该删除的教师的有关信息。
分解为高一级范式如图所示。
R1如下:
R2如下:
分解后,若删除课程数据时,仅对关系R1操作,教师地址信息在关系R2中仍然保留,不会丢失教师方面的信息。
3、设某商业集团数据库中有一关系模式R如下:
R(商店编号,商品编号,数量,部门编号,负责人)
如果规定:
(1)每个商店的每种商品只在一个部门销售;
(2)每个商店的每个部门只有一个负责人;
(3)每个商店的每种商品只有一个库存数量。
试回答下列问题:
(1)根据上述规定,写出关系模式R的基本函数依赖;
(商店编号,商品编号)→部门编号,(商店编号,部门编号)→负责人,(商店编号,商品编号)→数量
(2)找出关系模式R的候选码;
关系模式R的码为:
(商店编号,商品编号,部门编号)。
(3)试问关系模式R最高已经达到第几范式?
原关系模式R是属于1NF的,码为(商店编号,商品编号,部门编号),非主属性对码的函数依赖全为部分函数依赖,所以不属于2NF。
R1(商店编号,商品编号,部门编号,数量)
R2(商店编号,部门编号,负责人)
(4)如果R不属于3NF,请将R分解成3NF模式集。
答:
将R分解为
分解后的R不存在传递的函数依赖,所以分解后的R已经是第3NF
五、综合题(每小题10分,共20分)
1.设有一个学生课程数据库,包括学生关系Student、课程关系Course、选修关系SC,图1所示:
Student
学号
Sno
姓名
Sname
性别
Ssex
年龄
Sage
所在系
Sdept
95001
李勇
男
20
CS
95002
刘晨
女
19
IS
95003
王敏
18
MA
95004
张立
Course
课程号
Cno
课程名
Cname
先行课
Cpno
学分
Ccredit
1
数据库
5
4
2
数学
3
信息系统
操作系统
6
数据结构
7
数据处理
PASCAL语言
SC
成绩
Grade
92
85
88
90
图1
学生-课程数据库
写出完成下列要求的SQL语句:
1)查询所有年龄在20岁以下的学生姓名与年龄。
2)查询年龄不在20~23(包括20岁和23岁)之间的学生的姓名、系别和年龄。
3)查询每个学生及其选修课程的情况。
2.把下面用关系表示的实体、实体与实体之间的联系,用E-R图表示出来,要求在图中表示联系的类型
(1∶1、1∶n、m∶n)。
实体1:
学生(学号,姓名,性别,年龄)
关键字为:
实体2:
课程(课程号,课程名,学分数)
实体1与实体2的联系:
学习(学号,课程号,成绩)
学号+课程号
注:
一个学生可以选多门课程,一门课程也可以被多个学生选,学生选课后有成绩。
1.1)selectSname,Sage
fromStudent
whereSage<
20;
或
selectSname,Sage
wherenotSage>
=20;
2)selectSname,Sdept,Sage
whereSagenotbetween20and23;
3)selectStudent.*,SC.*
fromStudent,SC
whereStudent.Sno=SC.Sno;
学生选课关系E-R图