数据库原理及应用试题库1

三、简答题

1.设有关系模式：学生修课管理（学号，姓名，所在系，性别，课程号，课程名，学分，成绩）。设一名学生可以选修多门课程号，一门课程号可以被多名学生选修；一名学生有唯一的所在系，每门课程号有唯一的课程名和学分。

回答以下问题：（1）根据上述规定写出关系模式R的基本函数依赖；

（2）找出关系模式R的候选码；

（3）试问关系模式R最高已经达到第几范式？为什么？

（4）将R分解成3NF模式集。

答：（1）学号（姓名，所在系，性别）F

课程号（课程名，学分）F

（学号，课程号）成绩F

（学号，课程号）（姓名，所在系，性别，课程号，学分）P

（2）候选码：学号，课程号

（3）存在部分函数依赖，R达到第一范式

（4）Student（学号，姓名，所在系，性别）

SC（学号，课程号，成绩）

Course（课程号，课程名，学分）

2.设有关系模式：学生表（学号，姓名，所在系，班号，班主任，系主任）。其语义为：一名学生只在一个系的一个班学习，一个系只有一名系主任，一个班只有一名班主任，一个系可以有多个班。

回答以下问题：（1）根据上述规定写出关系模式R的基本函数依赖；

（2）找出关系模式R的候选码；

（3）试问关系模式R最高已经达到第几范式？为什么？

（4）将R分解成3NF模式集。

答：（1）学号（姓名，所在系，班号，班主任，系主任）F

班号（班主任，系主任）F

班主任系主任F

所在系系主任F

（2）候选码：学号

（3）存在传递依赖，不存在部分函数依赖，R达到第二范式

（4）Student（学号，姓名，所在系，班号）

Class（班号，班主任）

Dept（所在系，系主任）

3.设有关系模式：授课表（课程号，课程名，学分，授课教师号，教师名，授课时数）。其语义为：一门课程号有确定的课程名和学分，每名教师有确定的教师名，每门课程号可以由多名教师讲授，每名教师也可以讲授多门课程，每名教师对每门课程号有确定的授课时数。

回答以下问题：（1）根据上述规定写出关系模式R的基本函数依赖；

（2）找出关系模式R的候选码；

（3）试问关系模式R最高已经达到第几范式？为什么？

（4）将R分解成3NF模式集。

答：（1）课程号（课程名，学分）F

授课教师号教师名F

（授课教师号，课程号）授课时数F

（授课教师号，课程号）（课程名，学分，教师名）P

（2）候选码：授课教师号，课程号

（3）存在部分函数依赖，R达到第一范式

（4）Course（课程号，课程名，学分）

Teacher（授课教师号，教师名）

CT（课程号，授课教师号，授课时数）

4．（20分）设某图书集团有一关系模式R如下：R（书店编号，书籍编号，库存数量，部门编号，负责人）如果规定：（1）每个书店的每种书籍只在该书店的一个部门销售；

（2）每个书店的每个部门只有一个负责人；

（3）每个书店的每种书籍只有一个库存数量。

回答以下问题：（1）根据上述规定写出关系模式R的基本函数依赖；

（2）找出关系模式R的候选码；

（3）试问关系模式R最高已经达到第几范式？为什么？

（4）将R分解成3NF模式集。

答：（1）有三个函数依赖：

（书店编号，书籍编号）部门编号（2分）

（书店编号，部门编号）负责人（2分）

（书店编号，书籍编号）库存数量（2分）

部门编号负责人

书籍编号库存数量

（2）R的候选码：（书店编号，书籍编号）（3分）

（3）R属于2NF。（2分）

因为R中存在着非主属性“负责人”对候选码（书店编号，书籍编号）的传递函数依赖，所以R属于2NF。（3分）（4）分解成：

R1（书店编号，书籍编号，库存数量，部门编号）（3分）

R2（书店编号，部门编号，负责人）（3分）

四、综合题

1．设有一个SPJ数据库，包括S、P、J、SPJ四个关系模式：

供应商表S（供应商代码SNO，供应商姓名SNAME，供应商状态STATUS，供应商所在城市CITY）；

零件表P（零件代码PNO，零件名PNAME，颜色COLOR，重量WEIGHT）；

工程项目表J（项目代码JNO，项目名JNAME，项目所在城市CITY）；

供应情况表SPJ（供应商代码SNO，零件代码PNO，项目代码JNO，供应数量QTY）；

用SQL语言完成如下查询：（1）找出所有供应商的姓名和所在城市；

（2）求供应工程J1零件P1的供应商号码SNO；

（3）求供应工程J1零件为红色的供应商号码SNO；

（4）找出所有零件的名称、颜色、重量；

（5）找出上海厂商供应的所有零件号码；

（6）找出工程项目J2使用的各种零件的名称及其数量；

（7）找出所有供应商的姓名和所在城市；

（8）找出所有零件的名称、颜色、重量；

（9）找出使用供应商S1所供应零件的工程号码；

（10）找出工程项目J2使用的各种零件的名称及其数量。

（1）select SNAME,CITY from S （2分）

（2）select SNO from SPJ where JNO=’J1’ and PNO=’P1’（3分）

（3）select SNO from SPJ where SPJ.PNO=P.PNO and JNO=’J1’ and COLOR=’red’（3分）

（4）select PNAME,COLOR,WEIGHT from P; （3分）

（5）select S.SNO from S,P,SPJ where S.SNO=SPJ.SNO and SPJ.PNO=P.PNO and CITY=’上海’;（3分）

（6）select PNAME,WEIGHT from P,SPJ where P.PNO=SPJ.PNO and JNO=’J2’; （3分）

（7）Select sname, city1 from S ; （2分）

（8）Select Pname, color, weight from P;（2分）

（9）Select Jno from SPJ where sno=’s1’; （3分）

（10）Select P.pname , SPJ.qty from SPJ, P where SPJ.pno = P. pno and SPJ.Jno=’J2’; （3分）

2．设某商业集团数据库中有三个实体集：

商店：商店编号、商店名、地址

商品：商品编号、商品名、规格、单价

职工：职工编号、姓名、性别、业绩

每个商店可销售多种商品，每种商品也可放在多个商店销售，每个商店销售一种商品时有月销售量；每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。

（1）试画出E-R图，要求在图上注明属性及联系的类型；

（2）将E-R图转换成关系模型，并注明主码；

（3）根据实际情况，使用SQL创建表，包括各种约束；

（4）用SQL语句查找大于平均业绩的职工姓名；

（5）用SQL语句创建一个业绩大于100的所有男职工信息的视图。

（1）（5分）

（2）这个E-R图可转换为4个关系模式：（8分）

商店（商店编号，商店名，地址）（2分）

职工（职工编号，姓名，性别，业绩，商店编号，聘期，月薪） (2分)

商品（商品编号，商品名，规格，单价）（2分）

销售（商店编号，商品编号，月销售量）（2分）

（3）create shop(Sid char(3) primary key, Sname char(10), Sadd char(50)); （2分）create employee(Eid char(3) primary key, Ename char(5), Esex char(1), Each real, Sid char(3), Ere date, Esa int, foreign key (Sid) references (shop)); （2分）

create commodity(Cid char(3) primary key, Cname char(10), Csp char(10), Cpr real); （2分）create vendition(Sid char(3), Cid char(3), Vse int, primary key (Sid,Cid),

foreign key (Sid) references (shop), foreign key (Cid) references (commodity)); （2分）

（4）select Ename from employee x where Each>=(select avg(Each) from employee y where y.Each=x.Each); （2分）

（5）create view Eman(Eid, Ename, Esex, Each, Sid, Ere, Esa)

As select Eid, Ename, Esex, Each, Sid, Ere, Es from employee where Each>100 and Esex=’男’;（2分）

3．（10分）设有学生表S（SNO，SN），其中SNO为学号，SN为姓名；

学生选课表SC（SNO，CNO，CN，G），其中CNO为课程号，CN为课程名，G为成绩，用SQL语言完成以下各题：（1）建立一个视图V-SSC（SNO，SN，CNO，CN，G），并按CNO升序排序；（5分）

（2）从视图V-SSC上查询平均成绩在90分以上的SN，CN和G。（5分）

（1）CREATE VIEW V-SSC(SNO,SN,CNO,CN,G)

AS SELECT S.SNO, S.SN, https://www.360docs.net/doc/cb16293759.html,O, https://www.360docs.net/doc/cb16293759.html,, SC.G

FROM S, SC

WHERE S.SNO=SC.SNO;

ORDER BY CNO; （5分）

（2）SELEC SN, CN, G

FROM V-SSC

GROUP BY SNO

HAVING AVG(G)>90; （5分）

4. （共10分）设学校数据库中有两个实体集：

学生表：学号、姓名、班级

课程表：课程号、课程名称、教师

某学校有若干学生，每个学生可以选修多门课程，学校有若干课程供学生选修，每门课程可以供多个学生选修，要建立该学校学生选修课程的数据库，请设计：

（1）试画出E-R 图，要求在图上注明属性及联系的类型；

（2）将E-R 图转换成关系模型，并注明主码；

（2）这个E-R 图可转换为4个关系模式：（12分）

商店（商店编号，商店名，地址） （3分）

职工（职工编号，姓名，性别，业绩，商店编号，聘期，月薪） (3分)

商品（商品编号，商品名，规格，单价） （3分）

销售（商店编号，商品编号，月销售量） （3分）

5．（10分）有“学生选课系统”数据库，学生选课的关系模式为：

学生（学号，姓名，性别，年龄，所在系）

课程（课程号，课程名，先行课）

选课（学号，课程号，成绩）

根据所给系统，用关系代数运算完成下面查询。

（1）查询年龄小于20岁的学生。

（2）查询学生的姓名和所在系，即求“学生”关系中学生姓名和所在系两个属性上的投影。

（3）查询选修了2号课程的学生学号。

（4）查询选修了全部课程的学生的学号和姓名。

6、（10分）设有学生表S （SNO ，SN ，SA ），其中SNO 为学号，SN 为姓名，SA 为年龄；

学生选课表SC （SNO ，CNO ，CN ，G ），其中CNO 为课程号，CN 为课程名，G 为成绩，用SQL 语言完成以下各题：（1）查询所有年龄在20岁以下的学生姓名及年龄。（5分）

（2）查询选修了2号课程且成绩在90分以上的所有学生的学号及姓名。（5分）

（1）select sn,sa _______(2分)

from s ———（1分）

where sa<20;（2分）

（2）select s.sno,smame from s,sc_______(2分)

Where s.sno=sc.sno and https://www.360docs.net/doc/cb16293759.html,o=’2’ and sc.g>90;————（3分）

7.设有一个SPJ 数据库，包括S 、P 、J 、SPJ 四个关系模式：

S （SNO,SNAME,STATUS,CITY ）

P （PNO,PNAME,COLOR,WEIGHT ）

J （JNO,JNAME,CITY ）

SPJ （SNO,PNO,JNO,QTY ）

供应商表S 由供应商代码（SNO ）、供应商姓名（SNAME ）、供应商状态（STATUS ）、供应商所在城市（CITY ）组成；

（2）这个E-R 图可转换为3个关系模式：（6分） 课程表（学号，姓名，班级）

选修（学号，课程号）

课程表（课程号，课程名称，教师）

零件表P由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成；

工程向目标J由工程项目代码（JNO）、工程项目名（JNAME）、工程项目所在城市（CITY）组成；

供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某种供应商供应某种零件给某工程项目的数量为QTY。

今有若干数据如下：

(省略四图)

试用SQL完成如下查询：

建立题目所述的四个表，并输入数据；

求供应工程J1零件的供应商号码SNO；

求供应工程J1零件P1的供应商号码SNO；

求供应工程J1零件为红色的供应商号码SNO；

求没有使用天津供应商生产的红色零件的工程号JNO；（暂时不做）

求至少用了供应商S1所供应的全部零件的工程号JNO；（暂时不做）

找出所有供应商的姓名和所在城市；

找出所有零件的名称、颜色、重量；

找出所有使用供应商S1所供应零件的工程号码；

找出工程项目J2使用的各种零件的名称及其数量；

找出上海厂商供应的所有零件号码；

找出使用上海产的零件的工程名称；

找出没有使用天津产的零件的工程号码；

把全部红色零件的颜色改为蓝色；

由S5供给J4的零件P6改为由S3供应，请做出必要的修改；

从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录；

请将（S2，J6，P4，200）插入供应情况关系；

答案：

Create table ；

Select sno from s,spj where s.sno=spj.sno and jno=’j1’；

Select sno from s,spj where s.sno=spj.sno and jno=’j1’ and pno=’p1’；

Select sno from s,spj,p where s.sno=spj.sno and spj.pno=p.pno and jno=’j1’ and color=’红’；

Select sname,city from s；

Select pname,color,weight from p；

Select jno from j,spj where j.jno=spj.jno and sno=’s1’；

Select pname,qty from p,spj where p.pno=spj.pno and jno=’j2’；

Select pno from p,spj,s where p.pno=spj.pno and spj.sno=s.sno and city=’上海’；

Select jname from jno where city=’上海’；

Select jno from j,spj,s where j.jno=spj.jno and spj.sno=s.sno and s.city <>’天津’；

Update p set color=’蓝’ where color=’红’；

8. 设有一个学生选课数据库，包括Student，SC，Course 三个关系模式：

Student（Sno，Sname，Ssex，Sage，Sdept）

SC(Sno，Cno，Grade)

Course（Cno，Cname，Ccredit，Semester）

试用SQL完成如下查询：

查询SC表中的全部数据；

查询计算机系学生的姓名和年龄；

查询成绩在70-80分的学生的学号、课程号和成绩；

查询计算机系年龄在18-20岁的男学生的姓名和年龄；查询C001课程号的最高分；

查询计算机系学生的最大年龄和最小年龄；

统计每个系的学生人数；

统计每门课程号的选课人数和考试最高分；

统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果；

查询总成绩超过200分的学生，要求列出其学号和总成绩；

查询选修C002课程的学生姓名和所在系；

查询成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果；

查询哪些课程号没有学生选修，要求列出课程号和课程名；

查询计算机系哪些学生没有选课，列出学生姓名；

查询选修C001课程号的学生姓名和所在系；

查询通信工程系成绩在80分以上的学生学号和姓名；

查询计算机系考试成绩最高的学生姓名。

查询年龄最大的男学生的姓名和年龄；

查询C001课程号的考试成绩高于该课程号平均成绩的学生学号和成绩；

创建内容为学生学号、姓名、所在系、课程号、课程名、课程学分的视图；

创建内容为学生的学号、姓名、选修课程名和考试成绩的视图；

创建内容为统计每个学生的选课门数的视图，要求列出学生学号和选课门数；

创建内容为每个学生的选课总学分的视图，要求列出学生学号和总学分（说明：考试成绩超过60才能获得此课程的学分）

删除选课成绩小于50分的学生的选课记录；

将所有选修C001课程的学生的成绩加10分；

将计算机所有选修“数据库原理及应用”课程的学生成绩加10分。

答案：

Select * from sc；

Select sname,sage from student where sdept=’计算机系’；

Select sno,course,grade from sc where grade between 70 and 80；

Select sname,sage from student where sdept=’计算机系’ and ssex=’男’ and sage between 18 and 20；Select max(grade) 最高分 from sc where cno=’C001’；Select max(sage) 最大年龄,min(sage) 最小年龄 from student where sdetp=’计算机系’；

Select sdept,count(*) 学生人数 from student group by sdept；

Select cno,count(*) 选课人数, max(grade) 最高分from sc group by cno；

Select sno,count(*) 选课门数, sum(grade) 总成绩from sc group by sno order by count(*)；

Select sno,sum(grade) 总成绩 from sc group by sno having sum(grade)>200；

Select sname,sdept from student,sc where student.sno=sc.sno and cno=’C002’；

Select sname,cno,grade from student,sc where student.sno=sc.sno and grade>80 order by grade desc；Select

Select

Select

Select sno,sname from student,sc where student.sno=sc.sno and sdept=’通信工程系’ and grade>80；

Select

Select

Select sno,grade from sc where grade>(select avg(grade) from sc where cno=’c001’) and cno=’c001’；

Create view view1 as select sno,sname,sdept,cno,cname,ccredit from student,sc,course where student.sno=sc.sno and https://www.360docs.net/doc/cb16293759.html,o=https://www.360docs.net/doc/cb16293759.html,o；Create view view2 as select sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and https://www.360docs.net/doc/cb16293759.html,o=https://www.360docs.net/doc/cb16293759.html,o；