三、简答题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岁以下的学生姓名及年龄。