数据库算法

数据库算法 示例:course(cno, cname, cpno, ccredit) cno列是主码，cname列不允许有重复值 create table course ( cno char(1) constraint PK_course_cno primary key, cname varchar(20) constraint UQ_course_cname unique, cpno char(1), ccredit smallint ); , create table course ( cno char(1), cname varchar(20), cpno char(1), ccredit smallint, constraint PK_course_cno primary key(cno), constraint UQ_course_cname unique(cname) ); , 示例:course(cno, cname, cpno, ccredit) , cno列是主码，cname列不允许有重复值，ccredit大于零 , create table course ( cno char(1) constraint PK_course_cno primary key, cname varchar(20) constraint UQ_course_cname unique, cpno char(1), ccredit smallint constraint CK_course_ccredit check(ccredit>0) ); , create table course ( cno char(1) constraint PK_course_cno primary key, cname varchar(20) constraint UQ_course_cno unique, cpno char(1), ccredit smallint, constraint CK_course_ccredit check(ccredit>0) ); , 示例:course(cno, cname, cpno, ccredit)cno列是主码，cname列不允许有重复值， ccredit大于0小于等于4 , create table course ( cno char(1) constraint PK_course_cno primary key, cname varchar(20) constraint UQ_course_cname unique, cpno char(1), ccredit smallint, constraint CK_course_ccredit check(ccredit>0 and ccredit<=4) ); 示例: student( sno, sname, ssex, sage, sdept )sno列为主码，sname列不能取空值， sage默认值为20 , create table student ( sno char( 5 ), sname varchar( 20 ) not null, ssex char( 2 ), sage smallint constraint DF_student_sage default(20), sdept varchar(15), constraint PK_student_sno primary key( sno ) ); , 示例: sc(sno, cno, grade) , (sno, cno)列为主码， sno列为外码，cno列为外码 , create table sc ( sno char(5) constraint FK_sc_sno foreign key references student(sno), cno char(1) constraint FK_sc_cno foreign key references course(cno), grade int, constraint PK_sc_snocno primary key(sno, cno) ); , 示例: sc(sno, cno, grade) , (sno, cno)列为主码， sno列为外码，cno列为外码 , create table sc ( sno char(5), cno char(1), grade int, constraint FK_sc_sno foreign key(sno) references student(sno), constraint FK_sc_cno foreign key(cno) references course(cno), constraint PK_sc_snocno primary key(sno, cno) , ); 示例:course(cno, cname, cpno, ccredit) , cno列是主码，cpno是外码 , create table course ( cno char(1), cname varchar(20), cpno char(1), ccredit smallint, constraint PK_course_cno primary key(cno), constraint FK_course_cpno foreign key(cpno) references course(cno) ); , create table t1 ( c1 int constraint UQ_t1_c1 unique ); , create table t1 ( c1 int, constraint UQ_t1_c1 unique(c1) ); , create table t2 ( c2 int constraint DF_t2_c2 default(10) ); 对于default约束在列级上进行定义。 , 添加属性saddr到student , alter table student add saddr varchar(30) null; , 添加属性scardid到student，scardid取值不重复 , alter table student add scardid char(18); , alter table student add constraint UQ_student_scardid unique(scardid); , 可以在增加列的时候同时增加该列上的约束。 , alter table student add scardid char(18) constraint UQ_student_scardid unique , 将teach表中的tno属性设为主码 , alter table teach add constraint PK_teach_tno primary key(tno); , alter table teach add primary key(tno); , 在sc表中增加完整性约束定义，使grade在0~100之间。 , alter table sc add constraint CK_sc_grade check( grade>=0 AND grade<=100 ); , alter table sc add constraint CK_sc_grade check( grade BETWEEN 0 AND 100 ); , 在course表中为ccredit增加默认约束，约束值为2。 , alter table course add constraint DF_course_ccredit default(2) for ccredit; , 用于修改某些列定义，其语法格式为: , alter table <表名> alter column <列定义>; , <列定义>格式为: <列名> <数据类型> [ null | not null ] , ALTER方式只能修改列的定义，不能修改约束，约束只能增减和删除。 , 把student表中的sdept列由原来的20位字符加宽到50位字符 , alter table student alter column sdept varchar(50); , 用于删除某些列以及某些约束，其语法格式为: , alter table <表名> drop column <列名> , alter table <表名> drop constraint <约束名>; , 删除student表中的sdept列 , alter table student drop column sdept; , 将student表中的sname属性加上唯一性约束 , alter table student add constraint UQ_sname unique(sname); , 删除student表中的sname属性上的唯一性约束 , alter table student drop constraint UQ_sname; , 删除course表中的cpno属性上的外码约束 , alter table course drop constraint FK_course_cpno , 格式 , drop table 表名; , 示例 , drop table student; , 为student表按学号升序建唯一索引。 , create unique index ID_sno on student(sno ASC); , 为course表按课程号降序建唯一索引。 , create unique index ID_cno on course(cno DESC); , 为sc表按学号升序和课程号降序建唯一索引 , create unique index ID_sno_cno on sc(sno ASC, cno DESC); , create table t1( c1 int, c2 int ); insert into t1 values( 12, 10 ); insert into t1 values( 12, 101 ); , create unique index ID_c1 on t1(c1 asc); , 索引创建失败 , 索引创建失败 , 删除索引的语句格式 , drop index <表名>.<索引名> , 删除student表的ID_sno索引。 , drop index student.ID_sno; , 查询每个学生和每门课的信息，对于每个学生选修了哪些课，对于每门课有哪些学 生选修 , (student sc) course , SELECT student.*, course.*, sc.grade FROM student left outer join sc on student.sno= sc.sno right outer join course on sc.cno = course.cno , 查询每个学生和每门课的信息，对于每个学生选修了哪些课，对于每门课有哪些学 生选修 连接查询,外连接, , S03null孙立nullnullC03化学 , 查询每个学生和每门课的信息，对于每个学生选修了哪些课，对于每门课有哪些学 生选修 , (student sc) course , SELECT student.*, course.*, sc.grade FROM student left outer join sc on student.sno= sc.sno full outer join course on sc.cno = course.cno , 查询每个学生和每门课的信息，对于每个学生选修了哪些课，对于每门课有哪些学 S0生选修 nullnull孙立3, (student sc) course , SELECT student.*, course.*, sc.grade FROM nu student full outer join sc on student.sno= sc.sno nullC03化学 full outer join course on sc.cno = course.cno ll , 查询选修了02号课程的学生学号，姓名 , select student.sno, sname from student inner join sc on student.sno=sc.sno where sc.cno = '02'; , SELECT sno, sname FROM student WHERE sno IN ( SELECT sno FROM sc WHERE cno= '02' ); , 查询选修了02号课程的学生学号，姓名 , select student.sno, sname from student inner join sc on student.sno=sc.sno where sc.cno = '02'; , SELECT sno, sname FROM student WHERE sno IN ( SELECT sno FROM sc WHERE cno= '02' ); , 查询与95002号学生在同一个系学习的学生。 , SELECT * FROM student WHERE sdept in ( SELECT sdept FROM student WHERE sno='95002' ) , 查询先行课为数据处理的课程信息。 , SELECT * FROM course where cpno in (SELECT cno FROM course where cname ='数据处理') 嵌套查询,带有IN谓词, ,查询先行课为数据处理的课程信息。 Π(σ( ) )C1C2C1.*c2.cname='数据处理'C1.cpno = C2.cno , SELECT C1.* FROM course as C1 inner join course as C2 on C1.cpno = C2.cno where C2.cname = '数据处理' , 查询选修了课程名为“信息系统”的学生学号和姓名 SELECT Sno, Sname ? 最后在Student关系中 FROM Student 取出Sno和Sname WHERE Sno IN ( SELECT Sno ? 然后在SC关系中找出选 FROM SC 修了这些课号的学生学号 WHERE Cno IN ( SELECT Cno ? 首先在Course关系中找出“信 FROM Course 息系统”的课程号。 WHERE Cname= '信息系统' ) ) , 查询选修了课程名为“信息系统”的学生学号和姓名 , ?sno,sname(,cname=„信息系统? (SC?Course?student)) , select Student.Sno, Student.Sname from SC inner join Course on Student.sno=SC.sno inner join Student on SC.cno = student.cno where Course.Cname = '信息系统'; , 查询与95002号学生在同一个系学习的学生。 , SELECT * FROM student WHERE sdept in ( SELECT sdept FROM student WHERE sno='95002' ) , 当能确切知道内层查询返回单值时，可用比较运算符(>，<，=，>=，<=，< >或!= ) 来确定父查询的条件。 , 查询与“刘晨”在同一个系学习的学生。 , SELECT sname FROM student WHERE sdept = ( SELECT sdept FROM student WHERE sname='刘晨' ) , 当有多个叫刘晨的学生时，上面SQL语句出错。 , 带有比较运算符的子查询要求返回单值 , 查询与95002号学生在同一个系学习的学生。 , SELECT sname FROM student WHERE ( SELECT sdept FROM student WHERE sno='95002' ) = sdept , 找出95001号学生在哪些课上的成绩超过或等于他所选修课程的平均成绩(给出课 程号)。 , select Cno from SC where Sno = '95001' and Grade >= ( select avg(Grade) from SC where Sno = '95001' ) 嵌套查询,带有比较运算符, ,找出每个学生超过或等于他选修课程平均成绩的 课程号。 SELECT AVG(Grade) FROM SC as Y WHERE Y.Sno= X.Sno YX , 查询其他系中比信息系中所有学生年龄小的学生姓名和年龄 , SELECT Sname, Sage FROM Student WHERE sage < ALL ( SELECT Sage FROM Student WHERE Sdept = 'IS' ) , 查询其他系中比信息系中所有学生年龄小的学生姓名和年龄 , SELECT Sname, Sage FROM Student WHERE sage < ( SELECT MIN(Sage) FROM Student WHERE Sdept= 'IS ' ) , 查询其他系中比信息系中某一学生年龄小的学生姓名和年龄 , SELECT Sname, Sage FROM Student WHERE sage < ANY ( SELECT Sage FROM Student WHERE Sdept = 'IS' ) AND Sdept <> 'IS' ; --父查询块中的条件 , 查询其他系中比信息系中某一学生年龄小的学生姓名和年龄 , SELECT Sname, Sage FROM Student WHERE sage < ( SELECT MAX(Sage) FROM Student WHERE Sdept= 'IS ' ) AND Sdept <> 'IS' , EXISTS谓词 , 用于判断一个查询语句查询的结果是否为空。 , SELECT * FROM SC WHERE Cno = '01' if exists( SELECT * FROM SC WHERE Cno = '01') print '存在' else print '不存在' 嵌套查询 ,查询所有选修了2号课程的学生学号、姓名。 ,selectfrom innerjoinon where , 查询所有选修了2号课程的学生学号、姓名。 , SELECT student.sno, sname FROM student WHERE sno in (SELECT sno FROM SC WHERE cno= '02'); 嵌套查询 ,查询所有选修了2号课程的学生学号、姓名。 (SELECT * FROM SC WHERE student.snosc.sno= student.snoAND cno= '02');95003950019500495002 嵌套查询 ,查询所有没选修2号课程的学生学号、姓名。 (SELECT * FROM sc WHERE sc.sno= student.snoAND cno= '02'); 嵌套查询 ,查询所有同时选修了2号和3号课程的学生学号， 姓名 嵌套查询 ,Select from where exists( select * from sc where sc.sno= student.snoand sc.cno= '02') and exists( select * from sc where sc.sno= student.snoand sc.cno= '03') , 查询与“刘晨”在同一个系学习的学生姓名。 , SELECT sname FROM student WHERE sdept in (SELECT sdept FROM student WHERE sname='刘晨') CS(SELECT * FROM Student as S2 WHERE S2.Sdept = S1.Sdept AND 刘晨')S2.Sname = ' 嵌套查询 ,查询95001没有选修的课 , 查询选修了全部课程的学生信息。 , 给定一个学生，假设学号为95001，怎么样保证他选修了全部的课 , 查找95001没有选修的课，如果查询不到，说明95001选修了全部的课 , SELECT * FROM course WHERE NOT EXISTS (SELECT * FROM sc WHERE sno= '95001' AND sc.cno = course.cno) SELECT * FROM student WHERE NOT EXISTS( SELECT * FROM course WHERE NOT EXISTS (SELECT * FROM sc WHERE sc.sno = '95001' AND sc.cno= course.cno) ) , 上面查询95001没有选修的课，如果为空，表明95001选修了所有课。 , 查询选修了全部课程的学生信息。 , 查询课程总数 select count(cno) from course , 查询选课门数等于课程总数的学生学号 select sno from sc group by sno having count(cno) =( select count(cno) from course ) , 查询学生信息 select * from student where sno in ( select sno from sc group by sno having count(cno) = ( select count(cno) from course) ) , 查询选修了学生95001选修的全部课程的学生信息。 , 给定一个学生，假设学号为95002，怎么样保证他选修了95001选修的全部 课程 , 查找一门课，这门课95001选修了，95002没有选修。如果查询不到，说明 95002选修了选修了95001选修的全部课程。 , 怎样查找一门课，这门课95001选修了，95002没有选修。 , SELECT * FROM course where exists( select * from sc where sc.cno = course.cno and sc.sno='95001') and not exists( select * from sc where sc.cno = course.cno and sc.sno='95002') select * from student where not exists( SELECT * FROM course where exists( select * from sc where sc.cno = course.cno and sc.sno= '95001' ) and not exists( select * from sc where sc.cno = course.cno and sc.sno= student.sno ) ) , 查询03号课的选课人数 , select count(*) from sc where cno = '03'; , , 查询各个课程号及相应的选课人数。 , SELECT cno, count(sno) as num FROM SC group by cno; 单表查询——group by子句 , group by ，gradecno count(sno) , 示例:列出每门课程的最高、最低分。 , select cno, max(grade), min(grade) from sc group by cno; , 示例:列出每门课程的最高、最低分。 , select cno, max(grade), min(grade) from sc group by cno; , 查询每个学生的选课门数。 , select Sno, count(*) from sc group by sno; , 查询选修了2门以上课程的学生学号及选课门数。 , select Sno, count(*) from SC group by Sno HAVING count(*) > 2; , 查询平均成绩大于85分的各门课程的课号及平均成绩。 , select Cno, avg(grade) from SC group by Cno HAVING avg(grade) > 85; , 查询有2门以上课程是90分以上的学生的学号及(90分以上的)课程数 , SELECT Sno FROM SC WHERE Grade>90 GROUP BY Sno HAVING COUNT(cno)>2; , 示例:求选修了01号课程的的学生学号和选修了02号课程的学生学号的并集。 , select SNO from SC where CNO ='01' union select SNO from SC where CNO ='02'; , 集合并提示 , 参加UNION操作的各结果表的列数必须相同，对应列的数据类型也必须相 同。 , select SNO from SC where CNO ='01' union select SNO, grade from SC where CNO ='02'; , 查询CS系或年龄小于19的学生，按学号排序 , SELECT * FROM Student WHERE Sdept= 'CS' ORDER BY Sno UNION SELECT * FROM Student WHERE Sage < 19 ORDER BY Sno; , 查询CS系或年龄小于19的学生, 按学号排序 , SELECT * FROM Student WHERE Sdept = 'CS' UNION SELECT * FROM Student WHERE Sage < 19 ORDER BY Sno; , 查询计算机科学系的学生与年龄不大于19岁的学生的交集。 , 本例实际上就是查询计算机科学系中年龄不大于19岁的学生 , SELECT * FROM Student WHERE Sdept = 'CS' AND Sage<=19; , 查询计算机科学系的学生与年龄不大于19岁的学生的差集 , 本例实际上就是查询计算机科学系中年龄大于19岁的学生 , SELECT * FROM Student WHERE Sdept= 'CS' AND Sage>19; 连接查询 ,查询95001号学生所选修的课程名。 ,?(,(SC ?Course) )CnameSno=‘95001’ , 查询Student表和SC表的广义笛卡尔积 , Student×SC , SELECT * FROM Student cross join SC; , select * from student, sc; , 查询学生表、选修表和课程表的广义笛卡儿积 , SELECT * FROM student cross join sc cross join course; , SELECT * FROM Student, sc, course; , SELECT * FROM Student cross join sc, course; 连接查询 ,查询选课学生的基本信息及其选修课程的情况,课程只给出课程号和成绩 , 查询选课学生的基本信息及其选修课程的情况,课程只给出课程号和成绩 , student student.sno=sc.sno 连接号 sc , 等价于σstudent.sno=sc.sno (student×sc) , select * from student cross join sc where student.sno = sc.sno; , select student.*, sc.cno, sc.grade from student cross join sc where student.sno = sc.sno; , select student.*, cno, grade from student cross join sc where student.sno = sc.sno; , 查询选课学生的学号和姓名 , select sno, sname from student cross join sc where student.sno = sc.sno; , select student.sno, sname from student cross join sc where student.sno = sc.sno; , select distinct student.sno, sname from student cross join sc where student.sno = sc.sno; , 查询选课学生的基本信息及其选修课程的情况,课程只给出课程号和成绩 , select student.*, cno, grade from student cross join sc where student.sno = sc.sno; , select student.*, cno, grade from student inner join sc on student.sno = sc.sno; , <表1> inner join <表2> on <条件> , 用在from子句后面， 连接查询 ,查询95001号学生选修的课程号和课程名。 ,select frominner joinon where ,select frominner joinon where , 意义:表1与表2之间在on指定的条件上进行θ连接。 , 查询所有选修了2号课程的学生学号、姓名。 , 本查询涉及Student和SC关系 , select student.sno, sname from , student inner join sc on student.sno = sc.sno , where cno= '02'; , 查询2号课成绩在85分以上的所有学生的学号、姓名 , select student.sno, student.sname , from student inner join sc on student.sno=sc.sno , where sc.grade >85 and sc.cno = '02'; , 查询选修了,数学,课的学生学号及姓名。 , select student.sno, sname from sc inner join course on sc.cno = course.cno inner join student on sc.sno = student.sno where cname = '数学'; 连接查询,自身连接, ,查询每一门课的间接先修课(先修课的先修 课)，没有直接先行课的不输出 ( )?C1C2c1.cno,c2.cpnoC1.cpno = C2.cno C1.C2. cnocpno 12 3 , 查询每一门课的间接先修课(即先修课的先修课，没有直接先行课的不输出) , SELECT C1.cno, C2.cpno FROM C1 inner join C2 on C1.cpno = C2.cno , SELECT C1.cno, C2.cpno FROM course as C1 inner join course as C2 on C1.cpno = C2.cno , 查询每个学生及其选修课程的情况(包括没有选课的学生，课程只给出课程号和成 绩)。 , SELECT student.*, sc.cno, sc.grade FROM student inner join sc on student.sno = sc.sno; , student sc , SELECT student.*, sc.cno, sc.grade FROM student left outer join sc on student.sno = sc.sno; , 查询每个学生和每门课的信息，对于每个学生选修了哪些课，对于每门课有哪些学 生选修 , (student sc) course , SELECT student.*, course.*, sc.grade FROM student left outer join sc on student.sno= sc.sno right outer join course on sc.cno = course.cno , 将(95007,王大明,男,19岁,CS)插入到Student表中。 , insert into student(sno,sname,ssex,sage,sdept) values ('95007', '王大明', '男', 19, 'CS' ); , 求每一个学生的平均成绩，并把结果存入数据库。 , 第一步:建表 CREATE TABLE savg ( sno CHAR(5), avggrade INT, constraint PK_savg_sno primary key(sno), constraint FK_savg_sno foreign key(sno) references student(sno) ); , 求每一个学生的平均成绩，并把结果存入数据库。 , 第二步:插入元组 insert into savg(sno, avggrade) select sno, avg(grade) from sc group by sno , 带子查询的修改语句 , 示例:计算机系的学生成绩上浮5%。 UPDATE sc SET sc.grade = sc.grade*1.05 WHERE sc.sno IN ( SELECT sno FROM student WHERE sdept ='CS' ) , 建立信息系学生的视图，包括学号、姓名、年龄 , create view IS_Student (Sno1, Sname1, Sage1) AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept= 'IS' , 建立信息系选修了01号课程的学生的视图，包括学号、姓名。(基于多表的视图) , CREATE VIEW IS_S1 AS SELECT Student.Sno, Sname, FROM SC inner join Student on student.Sno = SC.Sno WHERE Sdept= 'IS' AND SC.Cno= '01' , 建立信息系选修了01号课程且成绩在90分以上的学生的视图，包含学号，姓名， 成绩。 , CREATE VIEW IS_S2 AS SELECT Sno, Sname, Grade FROM IS_S1 WHERE Grade>=90; , 在信息系学生的视图中找出姓名为张三的学生的学号。 , SELECT Sno FROM IS_Student WHERE Sname = '张三'; , 通过视图IS_S1学号95002的学生姓名改为刘三。 , UPDATE IS_S1 SET Sname = '刘三' WHERE Sno= '95002'; , 通过视图IS_S1学号95001的学生姓名改为李四。 , UPDATE IS_S1 SET Sname= '李四' WHERE Sno= '95001'; , 通过视图IS_S1学号95002的学生性别改为男。 , UPDATE IS_S1 SET Ssex = '男' WHERE Sno= '95002'; , 通过视图IS_S2将学号95002的学生院系改为CS。 , UPDATE IS_S2 SET Sdept = 'CS' WHERE Sno= '95002'; , 通过视图IS_S2_WC将学号95004的学生院系改为CS。 , UPDATE IS_S2_WC SET Sdept = 'CS' WHERE Sno= '95004'; , 向信息系学生视图IS_S1中插入一个新的学生记录:95029，赵新，20岁 , insert into IS_S1 VALUES( '95029', '赵新', 20 ); 是否等价于下面 , insert into student(Sno,Sname,Sage,Sdept) VALUES('95029', '赵新', 20, 'IS' ); , CREATE VIEW IS_S1_WC AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept= 'IS' with check option , 向信息系学生视图IS_S1_WC中插入一个新的学生记录:95030，赵四，20岁 , INSERT INTO IS_S1_WC VALUES( '95030', '赵四', 20 ); , CREATE VIEW IS_S1 AS SELECT Sno, Sname, Sage FROM Student WHERE Sdept= 'IS' , 通过视图IS_S1删除学号95002的学生 , delete from IS_S1 WHERE Sno= '95002'; , 通过视图IS_S1删除学号95004的学生 , delete from IS_S1 WHERE Sno= '95004'; , 视图对重构数据库提供了一定程度的逻辑独立性 , 建立一个视图Student: CREATE VIEW Student(Sno, Sname,Ssex, Sage, Sdept) AS SELECT SX.Sno, SX.Sname, SY.Ssex, SX.Sage, SY.Sdept FROM SX inner join SY on SX.Sno=SY.Sno; 第二章 专门的关系运算——选择,查询年龄小于20岁的学生,查询信息系(IS)年龄小于20岁的学生 ? 专门的关系运算——投影,投影运算的含义 ,查询学生的姓名和所在系 专门的关系运算——投影,查询95001号学生所选修的课程号 1. 求选修了1号或者2号课程的学生学号。 ?Sno(,Cno =‘1’?Cno =‘2’(SC)) 或者?Sno(,Cno =‘1’ (SC))??Sno(,Cno = ‘2’(SC)) 2. 求选修了1号而没有选2号课程的学生学号。 ?Sno(,Cno =‘1’ (SC)),?Sno(,Cno=‘2’(SC)) 3. 求同时选修了1号和2号课程的学生学号。 ?Sno(,Cno=‘1’ (SC))??Sno(,Cno=‘2’(SC)) ?Sno(,Cno =‘1’ ?Cno =‘2’(SC)) 第六章 , 1NF的定义 , 如果一个关系模式R的所有属性都是不可分的基本数据项，则R?1NF。 , 2NF的定义 , 若关系模式R?1NF，并且每一个非主属性都完全函数依赖于R的码，则R ?2NF。 2NF 不属于2NF Sno?Dn Cno?Cn , 从1NF到2NF , 消除非主属性对码的部分函数依赖。 , SLC(Sno,Sn,Dno,Dn,Dloc,Cno,Cn,Grade) , 分解为如下三个关系模式 , SD(Sno, Sn, Dno, Dn, Dloc) , Sno为码 , C(Cno, Cn) , Cno为码 , SC(Sno, Cno, Grade) , (Sno, Cno)为码 , 3NF的定义 , 关系模式R(U, F)中若不存在这样的码X、属性组Y及非主属性Z(Z Y), 使得X?Y, Y?X, Y?Z成立，则称R(U,F)?3NF。 , 3NF的定义 , 如果关系模式R中不存在非主属性部分函数依赖于码, 也不存在非主属性 传递函数依赖于码, 则称模式R?3NF。 , SLC(Sno,Sn,Dno,Dn,Dloc,Cno,Cn,Grade) , 分解为如下三个关系模式 , SD( Sno, Sn, Dno, Dn, Dloc ) , C(Cno, Cn) , SC(Sno, Cno, Grade) , 都属于2NF , 其中对于关系模式SD， , Sno?Dno，Dno?Dn，Dn传递函数依赖于Sno。所以SD不属于 3NF , SD( Sno, Sn, Dno, Dn, Dloc ) , C(Cno, Cn) , SC(Sno, Cno, Grade) , 对上面SD分解(投影分解) , S(Sno, Sn, Dno) , D(Dno, Dn, Dloc) , C(Cno, Cn) , 1NF的定义 , 如果一个关系模式R的所有属性都是不可分的基本数据项，则R?1NF。 , 2NF的定义 , 若关系模式R?1NF，并且每一个非主属性都完全函数依赖于R的码，则R ?2NF。 , 从1NF到2NF , 消除非主属性对码的部分函数依赖。 , SC(Sno, Cno, Grade) 3NF的定义 , 关系模式R(U, F)中若不存在这样的码X、属性组Y及非主属性Z，使得X ?Y，Y?X， Y?Z，Z Y成立，则称R(U,F)?3NF。 , 等价定义:如果关系模式R中不存在非主属性部分函数依赖于码, 也不存在 非主属性传递函数依赖于码, 则称关系模式R?3NF。 , 从2NF到3NF , 消除非主属性对码的传递函数依赖。 , BCNF的定义 , 设关系模式R(U, F)?1NF，如果对于R的每个函数依赖X?Y，若Y X， 则X必含有码，那么R(U, F) , BCNF。 , 设关系模式R(U, F)?1NF，如果R中的每一个非平凡的函数依赖的决定因 素都包含码，则R(U, F) , BCNF , 对于关系模式 , STJ(Sno,Jno,Tno) , 候选码:(Sno,Tno)， (Sno,Jno) , STJ中的数据依赖 , Tno?Jno (Sno,Tno)?Jno (Sno,Jno)?Tno , 对于Tno?Jno，但Tno中不含有码，STJ不属于BCNF。 , 从3NF到BCNF , 1、消除主属性对不包含该主属性的码的部分函数依赖， , 2、消除主属性对码的传递函数依赖 , F(函数依赖集)的闭包 , 定义:在关系模式R中为F所逻辑蕴含的函数依赖的全体叫作F的 闭包，记为F+。 , 关系模式R，其中U={A, B}, F={A?B} 。求F+。 , F逻辑蕴涵那些函数依赖 , 考虑决定因素为A的函数依赖 , A?A，A?B， A?AB , 考虑决定因素为B的函数依赖 , B?B , 考虑决定因素为AB的函数依赖 , AB?AB，AB?A， AB?B , F+={ A?A，A?B，A?AB，B?B， , 关系模式R，其中U={A, B, C}, F={A?B,B?C}, 求F+。 , F+={A?A, A?B, A?C, A?AB, A?AC, A?BC, A?ABC, B?B, B?C, B?BC, C?C, AB?A, AB?B, AB?C, AB?AB, AB?BC, AB?AC, AB?ABC, AC?A, AC?B, AC?C, AC?AB, AC?AC, AC?AB, AC?ABC, BC?B, BC?C, BC?BC, ABC?A,ABC?B, ABC?C, ABC?AB, ABC?BC, ABC?AC, , 属性集X关于函数依赖集F的闭包(X，F) , F为U上一组函数依赖，X,U，X，F={A|X,A能由F根据Armstrong公理 推出}，称为属性集X关于函数依赖集F的闭包。A是U中的单个属性。 , 关系模式在R中，其中U={A, B, C}, F={A?B,B?C} 。设X=(AB), 求X，F , 对于A: X?A由F推出，A是X，F的一个元素 , 对于B: X?B由F推出，B是X，F的一个元素 , 对于C: X?C由F推出， C是X，F的一个元素 , X，F = {A，B，C} = ABC , F+与X，F , F+是函数依赖的集合，该集合里的每个函数依赖都被F逻辑蕴涵 , 即X，F为属性的集合，对于该集合里的每一个属性A，X,A能由F根据 Armstrong公理推出。 关系模式在R中，其中U={A, B, C}, F={A?B,B?C} 。求(AB)，F。 , 算法6.1:求属性集X(X,U)关于U上的函数依赖集F的闭包X，F。 , 输入:X，F 输出: X，F F={AB?C, B?D, C?E}。求(AB)，F 。 , 步骤: , (1) 令X(0)=X，i=0 , (2) 求Z: Z={A,(，V), (，W), (V,W,F , V,X(i) , A ,W} , (3) X(i+1)=Z?X(i) , (4)判断 X(i+1) =X(i)是否成立。 , (5)如果(4)成立或X(i)=U,则X，F=X(i), 算法终止。 , (6)若(4)假，则让i=i+1, 返回(2) 。 , 已知关系模式R, 其中U={A,B,C,D,E};F={AB?C, B?D, C?E, EC?B, AC ?B}。 求(AB)，F 。 , (1) X(0)= AB;计算X(1): 逐一的扫描F集合中各个函数依赖，找左部为A，B或AB的函数依赖。得到两个: AB?C，B?D。 于是X(1) = X(0)?C?D=ABCD。 , (2)因为X(0)?X(1)，X(0)?U，所以计算X(2): 再找出左部为ABCD子集的那些函数依赖， 得到AB?C，B?D， C?E，AC?B， 于是X(2) = X(1) ?C?D?E?B=ABCDE。 , 已知关系模式R，其中 U={A，B，C，D，E}; F={ AB?C, B?D, C?E, EC?B, AC?B }。 求(AC)，F 。 , 已知关系模式R，其中 U={A, B, C, D}; F={A?B, BC?D}; 求A ，F ，C ，F ，(AC) ，F , A，F = AB , C，F = C , (AC)，F = ABCD , 已知关系模式R(U, F)，U={A,B,C,D,E}, F={AB?C, B?D, C?E, EC?B, AC?B} 验证:(AB) 是候选码。 提示:就是验证U完全函数依赖AB (1) U函数依赖AB (2) U不函数依赖AB的任一真子集 如果(AB)+F=U可以验证(1) 如果(A)+F?U，(B)+F?U可以验证(2) , 函数依赖集等价 , 定义6.14:如果G+=F+，就说函数依赖集F覆盖G(F是G的覆盖，或G 是F的覆盖)，或F与G等价 , 定义6.15:如果函数依赖集F满足下列条件，则称F为一个极小函数依赖集。亦称 为最小依赖集或最小覆盖。 , 对于关系模式S(U)，其中: U={ SNO，SDEPT，MN，CNO，G } , F1={SNO?SDEPT, SDEPT?MN, SNO?MN, (SNO, CNO)?G } , F1-{SNO?MN}与F1等价。F1不是最小依赖集。 , 对于关系模式S(U)，其中:U = { A, B, C } , F1 =, A,C, AC,B , , F1-{AC,B}?{A,B}与F1等价。F1不是最小依赖集。 , 每一个函数依赖集F均等价于一个极小函数依赖集Fm。此Fm称为F的最小依赖 集。 , F={AB?C, B?D, C?E, EC?B, AC?B} 求:F的最小依赖集。 (2)去掉多余的依赖: , 考察AB?C， 令G=,B,D, C,E, EC,B, AC,B,, 求(AB)+G=? 因为C,(AB) +G ，则AB,C保留。 , 考察B?D， 令G=,AB,C, C,E, EC,B, AC,B,， 求(B)+G=? 因为D,(B) +G ，则B,D 保留。 , 考察C?E， 令G=,AB,C, B,D, EC,B, AC,B,， 求(C)+G=? 因为E,(C) +G，则C,E保留 (2)去掉多余的依赖: , 考察CE?B， 令G = ,AB?C, B?D, C?E, AC?B,， 求(EC)+G=, 因为B, (EC)+G ，所以EC,B保留。 , 考察AC?B， 令G=,AB?C, B?D, C?E, EC,B,， 求(AC)+G=, 因为B,(AC)+G, 故去掉AC,B , 得F, =, AB,C, B,D, C,E, EC,B, , (3) 去掉左部多余的属性: , 考察AB,C，取X=AB, B1=A, B2=B, , (X-B1)+F,=(B)+F,=? 因为C,BD，所以不替代 , (X-B2)+F,=(A)+F,=? 因为C,A，所以不替代 , 考察EC,B ，取X=EC, B1=E, B2=C, , (X-B1)+F,=(C)+F,=? 因为B,CEBD 所以即在F,中用(X-B1),B 代替EC ,B , 得F,=,AB,C, B,D, C,E, C,B,为F的一个最小依赖集。 , F=, A,B, B,A, B,C, A,C, C,A , , (1)右部分解:不需要分解 , (2)去掉多余的依赖 , 考察A,B， 令G=, B,A, B,C, A,C, C,A,, 则(A)+G=AC ? B,AC ? A,B保留。 , 考察B,A， 令G=, A,B, B,C, A,C, C,A,, 则(B)+G=BCA ? A,BCA, ? B,A去掉 , 考察B,C， 令G=, A,B, A,C, C,A ,, 则(B)+G=B 而C,B, ？B,C保留。 , 考察A,C， 令G=, A,B, B,C, C,A ,, 则A)+G=ABC ?C,ABC, ? A,C去掉 , 考察C,A ， 令G=, A,B, B,C ,, 则(C)+G=C 而A,C, ？C,A保留。 , 得F'=, A,B, B,C, C,A , , (3)去掉左部多余的属性:不需要 , 最小依赖集为F' = , A,B, B,C, C,A , , 考察C,A，令G=,A,B,B,A,B,C,A,C,, 则(C)+G=C, ?A,C ? C,A保留。 , 考察B,C，令G=,A,B,B,A,A,C,C,A,, 则(B)+G=BAC, ?C,BAC, ? B,C去掉 , 考察B,A，令G=, A,B, A,C, C,A ,, 则(B)+G=B, 而A,B, ？B,A保留。 , 考察A,C，令G=, A,B, B,A, C,A ,, , 则(A)+G=AB, ?C,AB, ? A,C保留 , 考察A,B，令 G=,B,A, A,C, C,A,, 则X+G=(A)+G=AC, 而B,AC, ？A,B保留。 , (3)去掉左部多余的属性:不需要 , F=, A,B, B,A, B,C, A,C, C,A , F'=, A,B, B,C, C,A , F''=, A,B, B,A, A,C, C,A , 都为F的最小依赖集 , 设关系模式R(U, F), U={ A, B, C, D, E, F } F=, A,BC, BC,A, AD,EF, BCD,EF , 求F的最小依赖集。 ,A,B,BC,A,A,C,BCD,EBCD,F, , 已知关系模式R(U, F)，U={A,B,C,D,E}, F={AB?C, B?D, C?E, EC?B, AC?B} 请找出关系R所有的候选码。 Fm,,AB,C, B,D, C,E, C,B, , 判断下面关系模式的所有候选码，并判断其达到哪一级范式。 R(U，F)，U={ W，X，Y，Z}， F={ X?Z，WX?Y } 解:因为 (WX)+F = U 所以R的只有一个候选码WX。 非主属性Z对WX为部分函数依赖，关系模式为第1范式 判断下列关系模式各达到哪一级范式。 2. R2(U2，F2)，U2={A1, A2, A3, A4}， F2={A2A3?A1，A1?A4，A4?A3} 3. R3(U3，F3)，U3={B1，B2，B3，B4， B5}， F3={B1?B2B3，B4?B5} , 给出关系模式R(A, B, C), F={ (A,B),C, C,A}。求证:(B, C)是侯选码。 , 即证(B, C) F, (A, B, C) , 证明: ?C,A (已知条件) ?(C, B) ,(A, B) (A2:增广律，两边并B) ?(C, B) ,(A, B,C) (A2:增广律，两边并C) ?B?A ?B?(A, B,C) ?C? B ?C?(A, B,C) ? (B, C) F, (A, B, C) , 已知关系模式R(U, F)，U={A,B,C,D,E}, F={ B?C, B?D, C?EB, E?B, AC?B} 求(DE) ，F 证必