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

1、三、简答题1.设有关系模式：学生修课管理（学号，姓名，所在系，性别，课程号，课程名，学分，成绩）。设一名学生可以选修多门课程号，一门课程号可以被多名学生选修；一名学生有唯一的所在系，每门课程号有唯一的课程名和学分。回答以下问题：（1）根据上述规定写出关系模式R的基本函数依赖；（2）找出关系模式R的候选码；（3）试问关系模式R最高已经达到第几范式？为什么？（4）将R分解成3NF模式集。答：（1）学号（姓名，所在系，性别）F课程号一"（课程名，学分）F（学号，Wo成绩F（学号，课程号）一"（姓名，所在系，性别，课程号，学分）P（2）候选码：学号，课程号-"（3）存在部

2、分函数依赖，R达到第一范式（4）Student（学号，姓名，所在系，性别）SC（学号，课程号，成绩）Course（课程号，课程名，学分）2.设有关系模式：学生表（学号，姓名，所在系，班号，班主任，系主任）。其语义为：一名学生只在一个系的一个班学习，一个系只有一名系主任，一个班只有一名班主任，一个系可以有多个班。回答以下问题：（1）根据上述规定写出关系模式R的基本函数依赖；（2）找出关系模式R的候选码；（3）试问关系模式R最高已经达到第几范式？为什么？（4）将R分解成3NF模式集。答：（1）学号_（姓名，所在系，班号，班主任，系主任）F班号：（班主任,系主任）F班主任*,系主任F所在系系主任F（

3、2）候选码：学丁f（3）存在传递依赖，不存在部分函数依赖，R达到第二范式（4）Student（学号，姓名，所在系，班号）Class（班号，班主任）Dept（所在系，系主任）3.设有关系模式：授课表（课程号，课程名，学分，授课教师号，教师名，授课时数）。其语义为：一门课程号有确定的课程名和学分，每名教师有确定的教师名，每门课程号可以由多名教师讲授，每名教师也可以讲授多门课程，每名教师对每门课程号有确定的授课时数。回答以下问题：（1）根据上述规定写出关系模式R的基本函数依赖；（2）找出关系模式R的候选码；（3）试问关系模式R最高已经达到第几范式？为什么？（4）将R分解成3NF模式集。答：（1）课程

4、号_（课程名，学分）F授课教加丁_教师名F（授课教师号/鼻号）_授课时数F（授课教师号，课程号）二（课程名，学分，教师名）P（2）候选码：授课教师号，课程号"（3）存在部分函数依赖，R达到第一范式（4）Course（课程号，课程名，学分）Teacher（授课教师号，教师名）CT（课程号，授课教师号，授课时数）4. （20分）设某图书集团有一关系模式R如下：R（书店编号，书籍编号，库存数量，部门编号，负责人）如果规定:（1）每个书店的每种书籍只在该书店的一个部门销售；（2）每个书店的每个部门只有一个负责人；（3）每个书店的每种书籍只有一个库存数量。回答以下问题：（1）根据上述规定写出关

5、系模式R的基本函数依赖；（2）找出关系模式R的候选码；（3）试问关系模式R最高已经达到第几范式？为什么？（4）将R分解成3NF模式集。答：（1）有三个函数依赖：（书店编号，书籍编号）_部门编号（2分）（书店编号，部门编号）"负责人（2分）（书店编号，书籍编号）"库存数量（2分）部门编号负责人-"书籍编号"库存数量（2） R的候选布：（书店编号，书籍编号）（3分）（3） R属于2NE（2分）因为R中存在着非主属性“负责人”对候选码（书店编号，书籍编号）的传递函数依赖，所以R属于2NE（3分）（4） 分解成：R1（书店编号，书籍编号，库存数量，部门编号）（3

6、分）R2（书店编号，部门编号，负责人）（3分）四、综合题1.设有一个SPJ数据库，包括S、P、J、SPJ四个关系模式：供应商表S（供应商代码SN。供应商姓名SNAME供应商状态STATUS供应商所在城市CITY）;零件表P（零件代码PN。零件名PNAMIE颜色COLOR重量WEIGHT;工程项目表J（项目代码JNQ项目名JNAME项目所在城市CITY）;供应情况表SPJ（供应商代码SNO零件代码PNO项目代码JNQ供应数量QTY；用SQL语言完成如下查询：（1）找出所有供应商的姓名和所在城市；（2）求供应工程J1零件P1的供应商号码SNO（3）求供应工程J1零件为红色的供应商号码SNO（4）找

7、出所有零件的名称、颜色、重量；（5）找出上海厂商供应的所有零件号码；（6）找出工程项目J2使用的各种零件的名称及其数量；（7）找出所有供应商的姓名和所在城市；（8）找出所有零件的名称、颜色、重量；（9）找出使用供应商S1所供应零件的工程号码；（10）找出工程项目J2使用的各种零件的名称及其数量。（1） selectSNAME,CITYfromS（2分）（2） selectSNOfromSPJwhereJNO='J1'andPNO=P1'（3分）（3） selectSNOfromSPJwhereSPJ.PNO=P.PNOandJNO='J1'andCOLO

8、R=red'（3分）（4） selectPNAME,COLOR,WEIGHTfromP;（3分）（5） selectS.SNOfromS,P,SPJwhereS.SNO=SPJ.SNOandSPJ.PNO=P.PNOandCITY='上海'（3分）（6） selectPNAME,WEIGHTfromP,SPJwhereP.PNO=SPJ.PNOandJNO=J2'（3分）（7） Selectsname,city1fromS;（2分）（8） SelectPname,color,weightfromP;（2分）（9） SelectJnofromSPJwheresno

9、='s1;（3分）（10） SelectP.pname,SPJ.qtyfromSPJ,PwhereSPJ.pno=P.pnoandSPJ.Jno='J2'（3分）2.设某商业集团数据库中有三个实体集：商店：商店编号、商店名、地址商品：商品编号、商品名、规格、单价职工：职工编号、姓名、性别、业绩每个商店可销售多种商品，每种商品也可放在多个商店销售，每个商店销售一种商品时有月销售量；每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。（1）试画出E-R图，要求在图上注明属性及联系的类型；（2）将E-R图转换成关系模型，并注明主码；（3）根据实际情况，使

10、用SQL创建表，包括各种约束；（4）用SQL®句查找大于平均业绩的职工姓名；（5）用SQL®旬创建一个业绩大于100的所有男职工信息的视图。（1）（5分）(2)这个E-R图可转换为4个关系模式：(8分)商店(商店编号，商店名，地址)(2分)职工(职工编号，姓名，性别，业绩，商店编号，聘期，月薪)(2分)商品(商品编号，商品名，规格，单价)(2分)销售(商店编号，商品编号，月销售量)(2分)(3) createshop(Sidchar(3)primarykey,Snamechar(10),Saddchar(50);(2分)createemployee(Eidchar(3)pr

11、imarykey,Enamechar(5),Esexchar(1),Eachreal,Sidchar(3),Eredate,Esaint,foreignkey(Sid)references(shop);(2分)createcommodity(Cidchar(3)primarykey,Cnamechar(10),Cspchar(10),Cprreal);(2分)createvendition(Sidchar(3),Cidchar(3),Vseint,primarykey(Sid,Cid),foreignkey(Sid)references(shop),foreignkey(Cid)referen

12、ces(commodity);(2分)(4) selectEnamefromemployeexwhereEach>=(selectavg(Each)fromemployeeywherey.Each=x.Each);(5) createviewEman(Eid,Ename,Esex,Each,Sid,Ere,Esa)AsselectEid,Ename,Esex,Each,Sid,Ere,EsfromemployeewhereEach>100andEsex='男'3.（10分）设有学生表S（SNQSN,其中SNM学号，SN为姓名；学生选课表SC（SN。CN。CNG）,其

13、中CNM课程号，CN为课程名，G为成绩，用SQL®言完成以下各题：（1）建立一个视图V-SSC（SN。SN,CN。CNG）,并按CN。序排序；（5分）（2）从视图V-SSC上查询平土§成绩在90分以上的SNCN和G（5分）（1） CREATEVIEWV-SSC（SNO,SN,CNO,CN,G）ASSELECTS.SNO,S.SN,SC.CNO,SC.CN,SC.GFROMS,SCWHERES.SNO=SC.SNO;ORDERBYCNO;（5分）（2） SELECSN,CN,GFROMV-SSCGROUPBYSNOHAVINGAVG（G）90;（5分）4 .（共10分）设学校

14、数据库中有两个实体集：学生表：学号、姓名、班级课程表：课程号、课程名称、教师某学校有若干学生，每个学生可以选修多门课程，学校有若干课程供学生选修，每门课程可以供多个学生选修，要建立该学校学生选修课程的数据库，请设计：（1）试画出E-R图，要求在图上注明属性及联系的类型；（2）将E-R图转换成关系模型，并注明主码；（2）这个E-R图可转换为4个关系模式：（12分）商店（商店编号，商店名，地址）（3分）职工（职工编号，姓名，性别，业绩，商店编号，聘期，月薪）（3分）商品（商品编号，商品名，规格，单价）（3分）销售（商店编号，商品编号，月销售量）（3分）（1）（4分）镶程名称）（课程号）（教师（2）

15、这个E-R图可转换为3个关系模式：（6分）课程表（学号，姓名，班级）选修（学号，课程号）课程表（课程号，课程名称，教师）5 .（10分）有“学生选课系统”数据库，学生选课的关系模式为：学生（学号，姓名，性别，年龄，所在系）课程（课程号，课程名，先行课）选课（学号，课程号，成绩）根据所给系统，用关系代数运算完成下面查询。（1）查询年龄小于20岁的学生。（2）查询学生的姓名和所在系，即求“学生”关系中学生姓名和所在系两个属性上的投影。（3）查询选修了2号课程的学生学号。（4）查询选修了全部课程的学生的学号和姓名。6、（10分）设有学生表S（SNQSNSA）,其中SNO学号，SN为姓名，SA为年龄；

16、学生选课表SC（SN。CNOCNG）,其中CNM课程号，CN为课程名，G为成绩，用SQL®言完成以下各题：（1）查询所有年龄在20岁以下的学生姓名及年龄。（5分）（2）查询选修了2号课程且成绩在90分以上的所有学生的学号及姓名。（5分）（1） selectsn,sa（2分）froms（1分）wheresa<20;（2分）（2） selects.sno,smamefroms,sc（2分）Wheres.sno=o='2'andsc.g>90;（3分）7 .设有一个SPJ数据库，包括S、P、J、SPJ四个关系模式：S（SNO,SNAME,STATUS,C|TYP

17、（PNO,PNAME,COLOR,WEIGHTJ（JNO,JNAME,CITYSPJ（SNO,PNO,JNO,QTY供应商表S由供应商代码（SN。、供应商姓名（SNAME供应商状态（STATUS供应商所在城市（CITY）组成；零件表P由零件代码（PNO、零件名（PNAME颜色（COLOR重量（WEIGHT组成；工程向目标J由工程项目代码（JN。、工程项目名（JNAME、工程项目所在城市（CITY）组成；供应情况表SPJ由供应商代码（SNO、零件代码（PNO、工程项目代码（JN。、供应数量（QTY组成，表示某种供应商供应某种零件给某工程项目的数量为QTY今有若干数据如下：（省略四图）试用SQLI

18、成如下查询：建立题目所述的四个表，并输入数据；求供应工程J1零件的供应商号码SNO求供应工程J1零件P1的供应商号码SNO求供应工程J1零件为红色的供应商号码SNO求没有使用天津供应商生产的红色零件的工程号JNQ（暂时不做）求至少用了供应商S1所供应的全部零件的工程号JNQ（暂时不做）找出所有供应商的姓名和所在城市；找出所有零件的名称、颜色、重量；找出所有使用供应商S1所供应零件的工程号码；找出工程项目J2使用的各种零件的名称及其数量;找出上海厂商供应的所有零件号码;找出使用上海产的零件的工程名称;找出没有使用天津产的零件的工程号码；把全部红色零件的颜色改为蓝色；由S5供给J4的零件P6改为由

19、S3供应，请做出必要的修改;从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录;请将（S2,J6,P4,200）插入供应情况关系；答案：Createtable；j1'；j1'andpno='p1,;Selectsnofroms,spjwheres.sno=spj.snoandjno=Selectsnofroms,spjwheres.sno=spj.snoandjno='j1'andcolor='红，；Selectsnofroms,spj,pwheres.sno=spj.snoandspj.pno=p.pnoandjno=Selects

20、name,cityfroms;Selectpname,color,weightfromp;Selectjnofromj,spjwherej.jno=spj.jnoandsno='s1'Selectpname,qtyfromp,spjwherep.pno=spj.pnoandjno='j2'Selectpnofromp,spj,swherep.pno=spj.pnoandspj.sno=s.snoandcity=Selectjnamefromjnowherecity='上海'；Selectjnofromj,spj,swherej.jno=spj.j

21、noandspj.sno=s.snoands.city<>Updatepsetcolor='蓝'wherecolor='红，；8 .设有一个学生选课数据库，包括Student,SCCourse三个关系模式：Student(Sno,SnameSsex,Sage,Sdept)SC(SnqCno,Grade)Course(Cno,CnameCcredit,Semester)试用SQLI成如下查询：绩降序排列结果；查询哪些课程号没有学生选修，要求列出课程号和课程名；查询计算机系哪些学生没有选课，列出学生姓名；查询选修C001课程号的学生姓名和所在系；查询通信工程系成

22、绩在80分以上的学生学号和姓名；查询计算机系考试成绩最高的学生姓名。查询SC表中的全部数据；查询计算机系学生的姓名和年龄；查询成绩在70-80分的学生的学号、课程号和成绩；查询计算机系年龄在18-20岁的男学生的姓名和年龄；查询C001课程号的最高分；查询计算机系学生的最大年龄和最小年龄；统计每个系的学生人数；统计每门课程号的选课人数和考试最高分；统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果；查询总成绩超过200分的学生，要求列出其学号和总成绩；查询选修C002课程的学生姓名和所在系；查询成绩80分以上的学生姓名、课程号和成绩，并按成查询年龄最大的男学生的姓名和年龄；查询C00

23、1课程号的考试成绩高于该课程号平均成绩的学生学号和成绩；创建内容为学生学号、姓名、所在系、课程号、课程名、课程学分的视图；创建内容为学生的学号、姓名、选修课程名和考试成绩的视图；创建内容为统计每个学生的选课门数的视图，要求列出学生学号和选课门数；创建内容为每个学生的选课总学分的视图，要求列出学生学号和总学分（说明：考试成绩超过60才能获得此课程的学分）删除选课成绩小于50分的学生的选课记录；将所有选修C001课程的学生的成绩加10分；将计算机所有选修“数据库原理及应用”课程的学生成绩加10分。答案：Select*fromscSelectsname,sagefromstudentwheresdept='计算机系；Selectsno,course,gradefromscwheregradebetween70and80;Selectsname,sagefromstudentwheresdept='计算机系'andssex='男'andsagebetween18and20；Selectmax(grade)最高分fromscwherecno='C001'Selectmax(sage)最大年龄,min(sage)最小年龄fromstudentwheresdetp='计算