1、1、设教学数据库有三个关系：学生表 S(S# , SNAME , AGE , SEX )，其属性分别表示学号、姓名、年龄、性别课程表 C(C# , CNAME , TEACHER )，其属性分别表示课程号，课程名，任课教师成绩表 SC(S# , C# , SCORE ) ，其属性分别表示学号、课程号、分数自己确定类型与长度，定义三个表。同时考虑完整性约束条件（主码、外码、分数介于 0与 100之间、性别非空且只取男与女） 。create table S(S# char(2) ,SNAME varchar(8) not null ,AGE int ,SEX char(2) not null ch
2、eck (SEX = 男 or SEX = 女 ) ,primary key (S#)create table C(C# char(2) ,CNAME varchar(20) not null ,TEACHER varchar(10) ,primary key (C#)create table SC(S# char(2) ,C# char(2) ,SCORE int ,foreign key (S#) references S(S#) ,foreign key (C#) references C(C#) ,primary key (S#,C#) ,check (SCORE between 0
3、and 100)2、针对教学数据库三个关系 S、C、SC，试用 SQL语句完成以下查询：（1）查询 LIU老师所授的课程号与课程名。select C# , CNAME from C where TEACHER = LIU（2）检索年龄大于 23岁的男同学的学号与姓名。select S# , SNAME from S where SEX = 男 and AGE 23（3）检索学号为 S3学生所学课程的课程名与任课教师。子查询select CNAME , TEACHER from C where C# in (select C# from SC where S# = S3)连接查询select C
4、NAME , TEACHER from C , SC where C.C# = SC.C# and S# = S3内连接查询select CNAME , TEACHER from C inner join SC on C.C# = SC.C# where S# = S3（4）检索王飞同学不学的课程号。select C# from C where C# not in (select C# from sc where s# in (select s# from s where sname = 王飞)select C# from C where C# not in (select C# from s
5、c , s where sc.s# = s.s# and sname = 王飞)select C# from C where C# not in (select C# from SC inner join S on SC.S# = S.S# where sname = 王飞)（5）统计开设课程的教师人数。select count(distinct TEACHER) from C（6）求每个学生选修课程（已有成绩）的门数与平均成绩。select S# , count(*) , avg(SCORE) from SC where SCORE is not null group by S#3、针对教学
6、数据库三个关系 S、C、SC，试用 SQL语句完成以下操作：（1）向关系 C插入一门课程：C8，VC+，BAO。insert into C(C# , CNAME , TEACHER) values(C8 , VC+ , BAO)（2）删除尚无成绩的选课元组。delete from SC where SCORE is null（3）删除选修柳城老师课程的女同学选课元组。delete from SC where S# in (select S# from SC where C# in (select C# from C where Teacher = 柳城 ) ) and S# in (selec
7、t S# from S where SEX = 女)（4）将低于总平均成绩的男同学成绩提高 5。update SC set SCORE = SCORE * 1.05 where SCORE ( select avg(SCORE) from SC ) and S# in ( select S# from S where SEX = 男 )（5）定义男学生视图 S_MAN，包括属性(S# , SNAME , AGE)；并以此视图查询所有男同学的学号与年龄。create view S_MAN as select S# , SNAME , AGE from S where SEX = 男select
8、S# , AGE from S_MAN4、设有关系模式 R(A,B,C,D)，R 上成立的 FD集 F=ABCD,AD（1）试说明 R不是 2NF的理由。（2）试把 R分解成 2NF的模式集。解答：（1）由函数依赖集 F可知：R的码为 AB，非主属性为 C、D。由 AD 推出 ABD 即非主属性 D部分依赖于码 AB。故 R 2NF。（2）由 AD 将 R分解成 R1(A,D)，其函数依赖集 F1=AD；R2=R - D 即 R2(A,B,C)，其函数依赖集 F2=ABC。可以证明 R12NF，R22NF。5、设有关系模式 R(A,B,C)，R 上成立的 FD集 F=CB,BA（1）试说明 R
9、不是 3NF的理由。（2）试把 R分解成 3NF的模式集。解答：P（1）由函数依赖集 F可知：CB，BA 即有 CA。所以 R的码为 C，非主属性为 A、B。加之 R1NF，R 的非主属性 A、B 都完全依赖于码 C，故 R2NF。由 CB，BA 有 CA 即非主属性 A通过属性 B传递依赖于码 C。故 R 3NF。（2）由 BA 将 R分解成 R1(B,A),其函数依赖集 F1=BA;R2=R - A 即 R2(C,B),其函数依赖集 F2=CB。可以证明 R13NF，R23NF。6、设有一个记录各个球队队员每场比赛进球数的关系模式R（队员编号，比赛场次，进球数，球队名，队长名）如果规定每个
10、队员统一编号，且只能属于一个球队；每个球队只有一个队长。（1）试写出关系模式 R的基本函数依赖 FD和码（2）说明 R不是 2NF的理由，把 R分解成 2NF的模式集。（3）进而把 R分解成 3NF的模式集，并说明理由。解答：（1）基本 FD有：（队员编号，比赛场次）进球数，队员编号球队名，球队名队长名。R的码为（队员编号，比赛场次） ；非主属性为球队名、队长名进球数。（2）由队员编号球队名推出（队员编号，比赛场次） 球队名即非主属性球队名部分依赖于码（队员编号，比赛场次） ，故 R 2NF。由队员编号球队名，球队名队长名推出队员编号队长名，进而推出（队员编号，比赛场次） 队长名即非主属性队长
11、名部分依赖于码（队员编号，比赛场次） 。由 FD队员编号球队名，队员编号队长名分解出：R1（队员编号，球队名，队长名） ，其 FD集 队员编号球队名，球队名队长名。R1的非主属性都完全依赖于码队员编号，故 R12NF。而 R2 = R 球队名，队长名，故R2（队员编号，比赛场次，进球数） ，其 FD集 （队员编号，比赛场次）进球数 。R2的非主属性都完全依赖于码（队员编号，比赛场次） ，故 R22NF。（3）R1（队员编号，球队名，队长名） ，其 FD集 队员编号球队名，球队名队长名且 R12NF。由队员编号球队名，球队名队长名推出队员编号队长名，即非主属性队长名通过队长名依赖于码队员编号，R
12、1 3NF。由球队名队长名将 R1分解成 R11(球队名，队长名)，其函数依赖集 F11=球队名队长名；R12=R1-队长名 即 R12(队员编号，球队名),其函数依赖集 F12 = 队员编号球队名。可以证明 R113NF，R123NF，R23NF。7.一个图书借阅数据库系统要求提供下述服务：可随时查询书库中现有书籍的名称、品种、数量与存放位置，所有各类书籍均可由书号唯一标识；可随时查询书籍借还情况。包括借书人单位、姓名、借书证号、借书日期和还书日期。约定：任何人可借多种书，任何一种书可为多个人所借，借书证号具有唯一性；当PP需要时，可通过数据库中保存的出版社电报编号、电话、邮编及地址等信息向
13、有关书籍的出版社增购有关书籍。约定：一个出版社可出版多种书籍，同一本书仅为一个出版社出版，出版社名具有唯一性。(1) 根据上述语义画出 ER图，要求在图中画出属性并注明联系的类型。(2) 将 ER模型转换成关系模型，并指出每个关系模式的主键和外键。书籍（书号，名称，品种，数量，存放位置，出版社名） 出版社名为外键出版社（出版社名，电报编号，电话，邮编，地址） 无外键借书人（借书证号，姓名，单位）无外键借阅（书号，借书证号，借书日期，还书日期） 书号和借书证号都是外键其中下划线表示主键（或把出版联系单独形成一个关系模式，即 5个关系模式）NM1N书籍 出版社借书人借阅名称品种 数量存放位置书号姓名 借书证号单位还书日期借书日期出版邮编地址出版社名 电话电报编号
