数据库原理及应用试题库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 = and JNO=’J1’ and COLOR=’red’（3分）（4）select PNAME,COLOR,WEIGHT from P; （3分）（5）select from S,P,SPJ where = and = and CITY=’上海’;（3分）（6）select PNAME,WEIGHT from P,SPJ where = 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 , from SPJ, P where = P. pno and =’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 =; （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 , , , ,FROM S, SCWHERE =;ORDER BY CNO; （5分）（2）SELEC SN, CN, GFROM V-SSCGROUP BY SNOHAVING 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岁以下的学生姓名及年龄。