自考课程：数据库原理串讲讲义.ppt

数据库原理 串讲讲义,陈立军,第一章 数据库概论,本章要点,数据管理技术的发展经历了哪几个阶段？各阶段的特点是什么？ 数据库的三级模式、两级映象的含义，两级数据独立性的含义是什么？ 什么是概念数据模型和结构数据模型？ 了解e-r模型中的主要术语以及e-r图的表示方法？三种联系的含义是什么？ 什么是结构数据的三要素？主要的结构数据模型有哪些？,本章要点,dbms的主要功能有哪些？ dml有哪些类型？各自的执行方式有哪些？ 什么是数据字典？ db，dbms，dbs的含义是什么？ dba的职责有哪些？,why? what? how?,why? 数据管理艺术的必然 what? 数据模型 how? 数据库模式,数据管理技术的发展,考察要点 各阶段的技术及应用背景 计算机应用范围 外存储设备 数据管理软件 各个阶段的差别体现 谁管理数据 数据面向谁 数据与应用的独立性,人工管理阶段,文件系统阶段,数据1,数据2,数据n,存取方式,程序1,程序2,程序n,文件系统阶段,文件系统阶段,数据与程序的独立性差 数据的共享性差 数据冗余度大，数据不一致性 数据查询困难 数据完整性难于保证,数据库系统阶段,数据库观点 数据不是依赖于处理过程的附属品，而是现实世界中独立存在的对象,数据库系统阶段,数据模型,数据描述的三个世界，两个层次 数据模型定义，两层数据模型的特点 er模型 结构数据模型的三要素 结构数据模型示例,数据模型,“computing”,数据模型定义,数据模型 是数据库系统中用于提供信息表示和操作手段的形式构架,数据模型定义,概念数据模型 按用户的观点来对数据和信息建模 用于组织信息世界的概念，表现从现实世界中抽象出来的事物以及它们之间的联系 这类模型强调其语义表达能力，概念简单、清晰，易于用户理解 它是现实世界到信息世界的抽象，是用户与数据库设计人员之间进行交流的语言 如e-r模型,数据模型定义,结构数据模型 从计算机实现的观点来对数据建模 是信息世界中的概念和联系在计算机世界中的表示方法 一般有严格的形式化定义，以便于在计算机上实现 如层次模型、网状模型、关系模型、面向对象模型,-模型,历史 -模型：entity-relationship model 1976年，p.p.s.chen提出-模型，用-图来描述概念模型 观点 世界是由一组称作实体的基本对象和这些对象之间的联系构成的,-模型,基本概念 实体、属性、实体集、键（键）、联系 联系的种类 一对一 e1中的一个实体与e2中至多一个实体相联系，并且 e2中的一个实体与e1中至多一个实体相联系 一对多 多对多,联系的种类,一个实体集内的二元联系 一对一 ei e，至多存在一个ej e，与之相联系（ji） 如“职工”之间的“配偶”联系 一对多 如“职工”内部的“领导”联系 多对多 如“零件”之间的“构成”联系,基本-图要点,基本-图要点,键在e-r图中的表示 实体集属性中作为主键的一部分的属性用下划线来标明。,结构数据模型,结构数据模型的三要素 数据结构 数据操作 数据的完整性约束 结构数据模型示例 层次模型 网状模型 关系模型 面向对象模型 各模型的数据结构、优缺点、如何表达er图中的实体和联系,结构数据模型示例层次模型,结构数据模型示例层次模型,结构数据模型示例网状模型,结构数据模型示例关系模型,用二维表来表示实体及其相互联系,联系,数据库模式,三级模式 两级映象 两级数据独立性,数据库模式,ansi/x3/sparc(75,78)提出sparc报告，其ansi模型将数据库模式分为外部级、概念级、内部级,数据库模式,数据库管理系统,dbms功能 数据定义功能ddl、数据字典 数据操纵功能dml 数据保护功能 数据维护功能 数据库存储管理 dbms的构成 查询处理器、存储管理器,数据库管理系统,ddl语言（data description language） 描述外模式、模式、内模式、完整性、安全性 dml语言（data manipulation language） 对数据库进行检索、插入、修改、删除 dml类型 宿主型：dml不独立使用，嵌入到高级语言（主语言）程序中使用 自含型：独立使用，交互式命令方式 dml语句执行方式 宿主型：预编译和增强编译 自含型：解释执行 过程性dml和非过程性dml,数据库系统,区分几个相关概念 数据库系统的软硬件层次 数据库系统的主要成分 数据库管理系统的层次结构及其功能 数据库系统结构,几个相关概念,数据库 数据的集合 由dbms统一管理，多用户共享 数据库管理系统dbms 系统软件，对数据库进行统一管理和控制 数据库系统 带有数据库的整个计算机系统，包括硬件、软件、数据、人员,数据库系统的主要成分,用户 最终用户 通过应用系统的用户接口（菜单等）使用数据库 应用程序员 基于外模式来编写应用程序 系统分析员 负责应用系统的需求分析和规范定义，确定系统的软硬件配置，参与数据库模式设计 数据库管理员dba 负责数据库的全面管理和控制,数据库系统的主要成分,dba职责 建库方面 确定模式、外模式、存储结构、存取策略 负责数据的整理和装入 用库方面 定义完整性约束条件 规定数据的保密级别、用户权限 监督和控制数据库的运行情况 制定后援和恢复策略，负责故障恢复 改进方面 监督分析系统的性能（空间利用率，处理效率） 数据库重组织，物理上重组织，以提高性能 数据库重构造，设计上较大改动，模式和内模式修改,数据库系统的主要成分,查询处理器 ddl编译器 dml编译器 嵌入式dml编译器 查询运行核心程序 存储管理器 授权和完整性管理器 事务管理器 文件管理器 缓冲区管理器 磁盘存储器中的数据结构 数据文件 数据字典 索引文件 统计数据,第二章 关系模型,提纲,关系模型的基本概念 元组、属性、关系模式、键、候选键、主键、外键 关系的性质 关系模型的完整性 实体完整性 参照完整性 用户定义完整性 关系代数 基本操作：选择、投影、笛卡儿积、并、差 扩展运算：交、连接、自然连接、外连接、除法 关系验算 元组关系验算 域关系验算,关系模型,候选键（candidate key） 关系中的一个属性组，其值能唯一标识一个元组。若从属性组中去掉任何一个属性，它就不具有这一性质了，这样的属性组称作候选键 如dept中的d#，dn都可作为候选键 主键（primary key） 进行数据库设计时，从一个关系的多个候选键中选定一个作为主键 如可选定d#作为dept的主键 外键（foreign key） 关系r中的一个属性组，它不是r的键，但它与另一个关系s的键相对应，则称这个属性组为r的外部键 如s关系中的d#属性,关系模型的完整性,实体完整性 关系的主键中的属性值不能为空值 空值：不知道或无意义 参照完整性 如果关系r2的外键fk与关系r1的主键pk相对应，则r2中的每一个元组的fk值或者等于r1 中某个元组的pk 值，或者为空值 意义：如果关系r2的某个元组t2参照了关系r1的某个元组t1，则t1必须存在,关系模型的完整性,今要向关系p中插入新行，新行的值分别列出如下。哪些行能够插入？ a（037，绿，s10） b（null，黄，t20） c（201，红，t20） d（105，蓝，b01） e（205，蓝，null） f（101，黄，t11）,零件关系p（主键是“零件号”，外键是“供应商号”）,供应商关系s（主键是“供应商号”）,除运算,象集（image set） 关系r(x , z), x, z是属性组，x是x上的取值，定义x在r中的象集为 zx = tz | tr tx= x 从r中选出在x上取值为x的元组，去掉x上的分量，只留z上的分量,x z,张军同学所选修的全部课程,如何得到选修了全部课程的学生？,x=张军,zx,除运算,做法：逐个考虑选课关系中的元组r，求r在姓名上的分量x，再求x在选课关系中的象集课程x，若课程x包含了所有的课程，则x是满足条件的一个元组 除定义 r s 中每个元组t与s中每个元组s组成的新元组一定在r中 t = 1, 2, r-s(r) w = (t s) r v = 1, 2, r-s(w) rs = t - v,除运算,没有选修全部课程的学生,所有学生选修全部课程,课程,物理,姓名,王红,张军,姓名,王红,姓名,张军,选修了全部 课程的学生,姓名,王红,除运算,r,ab (r),s,ab (r) cd (s),ab (r) cd (s)-r,r s=,-,=,关系代数表达式,查询实例 学生关系s（s#，sname，age，sex） 课程关系c（c#，cname，teacher） 学习关系sc（s#，c#，grade）,选择运算,示例 找年龄不小于20的男学生 age20 sex=male（s）,投影,示例 找001号学生所选修的课程号 c#( s#=001 （sc） 求选修了001号课程的学生号 s#(c# = 001(sc),并运算,示例 求选修了001号或002号课程的学生号 方案1： s#(c# = 001 c# = 002(sc) 方案2： s#(c# = 001 (sc)s#(c# = 002(sc),差运算,示例 求选修了001号而没有选002号课程的学生号 错误的写法： s#(c# = 001 c# 002(sc) 正确的写法： s#(c# = 001 (sc) s#(c# = 002(sc),交运算,示例 求同时选修了001号和002号课程的学生号 错误的写法： s#(c# = 001 c# = 002(sc) 正确的写法： s#(c# = 001 (sc)s#(c# = 002(sc),连接运算,示例 求选修了001号课程的学生号和姓名。 s#，sname(c# = 001 (s sc) 求选修了课程名为maths的学生姓名。 sname(cname = maths (s c sc),外连接,列出所有老师的有关信息，包括姓名、工资、所教授的课程 p# ,pn , sal , c# , cn(prof) pc c),问题：有关p03号职工的姓名和工资信息没有显示出来。,外连接,所有老师的信息,除运算,示例 求同时选修了所有课程的学生号 方案1： s#，c#(sc) c# (c) 方案2： s#(sc c#(c) 哪一个正确？,除运算,选修了全部课程并且成绩都相同的学生,选修了全部课程的学生,关系代数查询实例,求没有选修c1号课程的学生 所有学生 选修了c1号课程学生 s(sc) -s#(c# = c1 (sc) s#(c# c1 (sc),哪一个正确？,仅选c1号课程之外的其他学生,关系代数查询实例,求仅选修了c1号课程的学生号 选修c1号课程的学生仅选c1号课程之外的学生 s(sc) -s#(c# c1 (sc) s#(c# = c1 (sc)s#(scc# = c1 (sc),关系代数查询实例,求选修c1课程比s1学生的该门课程成绩高的学生 sc sc (s1, c1, g1, ?, c1, g2), g1 g2,关系代数查询实例,求选修了至少两门课的学生 sc sc (s1, c1, g1, s1, c2, g2), c1 c2,求选修了至少n门课的学生,求只选修了1门课的学生,关系代数查询实例,求最低的成绩,关系代数查询实例,求每门课程的先修课的先修课 c c (c3, c2, c2, c1),关系代数查询实例,求选修课程中包含了所有s01号学生所选修课程的学生号 s#(s#，c#(sc) c#(s# = s01 (c),元组关系演算,r,s,r1= t | s(t) t1 2,r2= t | r(t) s(t) ,元组关系演算,r3= t | ( u)(s(t) r(u) t3 u2,r4= t | (u)(r(t) s(u) t3 u1,r5= t | (u)(v) (r(u) s(v) u1v2 t1=u2 t2=v3 t3=u1,元组关系演算,元组关系演算与关系代数的等价性 投影 a( r ) = t | sr ( sa = ta ) 选择 f(a)(r) = t | r(t) f(ta) 广义笛卡儿积 r(a) s(b) = t | urss( ta = ua tb = sb) 并 rs= t | r(t) s(t) 差 rs= t | r(t) s(t),元组关系演算,设r和s都是二元关系，试把关系代数表达式1，4(2 = 3 (rs)转换为元组表达式 rs = t | (u) (v) (r(u) s(v) t1=u1 t2=u2 t3=v1 t4=v2) 2 = 3 (rs) = t | (u) (v) (r(u) s(v) t1=u1 t2=u2 t3=v1 t4=v2) t2=t3 1，4(2 = 3 (rs) = w | (u) (v) (t) (r(u) s(v) t1=u1 t2=u2 t3=v1 t4=v2) t2=t3 w1=t1 t2=t3 1，4(2 = 3 (rs) = w | (u) (v) (r(u) s(v) u2=v1) w1=u1 w2=v2,元组关系演算,求选修了001号课程的学生号及其成绩 t | (u)(sc(u) u2=001 t1=u1 t2=u3) 求选修了001号课程的学生号及其姓名 t | (u) (v)(s(u) sc(v) v2=001 u1=v1 t1=u1 t2=u2) 求选修了课程名为maths的学生号及其姓名 t | (u) (v) (w)(s(u) sc(v) c(w) v2=w1 w2=maths u1=v1 t1=u1 t2=u2),元组关系演算,求选修了001号或002号课程的学生号 t | (u)(sc(u) (u2=001 u2=001) t1=u1) 求至少选修了001号和002号课程的学生号 t | (u) (v)(sc(u) sc(v) u2 = 001 v2=002 u1=v1 t1=u1) 求没有选修001号课程的学生号和姓名 t | (u) (v)(s(u) sc(v) (u1 = v1 v2 001) t1=u1 t2=u2),元组关系演算,求选修了全部课程的学生姓名 t | (u) (v)(w)(s(u) c(v) sc(w) (u1 = w1) (v1=w2) t1=u2) 求选修了s3所学全部课程的学生号 t | (u) (sc(u) (v)(sc(v) v1=s3 (w)(sc(w) (w1 = u1) (w2=v2) t1=u1),域关系演算,r,s,r1= xyz | r(xyz) x3,r2= xyz | r(xyz) (s(xyz) y=4),w,域关系演算,r2= xyz | (u) (u) (r(zxu) w(yv) uv),域关系演算,求选修了001号课程的学生号及其成绩 t1t2 | (u1) (u2) (u3)(sc(u1u2u3) u2=001 t1=u1 t2=u3) 求选修了001号课程的学生号及其姓名 t1t2 | (u1) (u2) (u3) (u4)(v1)(v2)(v3) (s(u1u2u3u4) sc(v1vv2v3) v2=001 t1=u1 t2=u2) t | (u) (v)(s(u) sc(v) v2=001 u1=v1 t1=u1 t2=u2),关系代数表达式的语法树,s(s#, sname, age, sex) c(c#, cname, teacher) sc(s#, c#, grade) 求选修了刘老师课程的女学生的学号及姓名,s#, sname(teacher=liusex=f(l (sc.c#=c.c# sc.s#=s.s#(s sc c),l=s#,sname,age,sex,c#,cname,teacher,grade,第三章 sql,提纲,sql概述 sql数据定义功能 sql数据查询功能 sql数据修改功能 sql数据控制功能 嵌入式sql,sql概述,历史 1970年，e.f.codd提出关系模型 1974年，由boyce和chamber提出；1975-1979年，在system r上实现，由ibm的san jose研究室研制 标准化 sql-86：“数据库语言sql” sql-89：“具有完整性增强的数据库语言sql”，增加了对完整性约束的支持。 sql-92：“数据库语言sql”，是sql-89的超集，增加了许多新特性，如新的数据类型，更丰富的数据操作，更强的完整性、安全性支持等。 sql-3：正在讨论中的新的标准，将增加对面向对象模型的支持。,sql概述,特点 一体化 集ddl，dml，dcl于一体。 单一的结构-关系，带来了数据操作符的统一。 面向集合的操作方式 一次一集合。 高度非过程化 用户只需提出“做什么”，无须告诉“怎么做”，不必了解存取路径。 两种使用方式，统一的语法结构 sql既是自含式语言（用户使用），又是嵌入式语言（程序员使用）。 语言简洁，易学易用,sql概述,sql数据定义功能,模式的定义 基本表的定义 索引的定义 视图的定义,基本表的定义,基本表的定义（create） 格式 create table 表名（ 列名 数据类型 not null ，列名 数据类型 not null ，primary key（列名 ，列名 ） ，foreign key （列名 ，列名 ） references 表名 （列名 ，列名 ） ，check（条件）,基本表的定义,示例 create table s ( s# char（4）, sname char（8） not null, age smallint, sex char（1）, primary key (s#), check (sex=0 or sex=1) ),基本表的定义,create table c ( c# char（4）, cname char（10） not null, teacher char（8）, primary key (c#), ),基本表的定义,create table sc (s# char（4）, c# char（4）, grade samllint, primary key (s#，c#), foreign key (s#) references s(s#), foreign key (c#) references c(s#), check(grade is null) or grade between 0 and 100),索引的定义,索引的定义 格式 create unique index 索引名 on 表名 (列名asc | desc, 列名asc | desc ) unique：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新。 示例： create index s-index on s（s#）,sql数据查询功能,sql数据查询基本结构 select子句 重复元组的处理 from子句 where子句 更名运算 字符串操作 元组显示顺序 集合操作,sql数据查询功能,分组和聚集函数 空值 嵌套子查询 派生关系 视图 关系的连接,sql数据查询基本结构,基本结构 select a1 , a2 , , an from r1 , r2 , , rm where f a1 , a2 , , an(f(r1 r2 rm),重复元组的处理,语法约束 缺省为保留重复元组，也可用关键字all显式指明。若要去掉重复元组，可用关键字distinct或unique指明。 示例 找出所有选修课程的学生。 select distinct sno from sc,from子句,说明 from子句列出查询的对象表。当目标列取自多个表时，在不混淆的情况下可以不用显式指明来自哪个关系。 示例 找出选修了c2课程的学生号及姓名。 select s.s# , sname from s , sc where s.s# = sc.s# and c# = c2,更名运算,格式 old_name as new_name 为关系和属性重新命名，可出现在select和from子句中。 示例 属性更名 例：给出所有学生的姓名、年龄 select sname as 姓名，age as 年龄 from s,更名运算,关系更名 找出年龄比王伟同学大的同学的姓名及年龄。 select s1.sname，s1.age from s as s1，s as s2 where s1.age s2.age and s2.sname = 王伟,更名运算,找出选修了c1和c2课程的学生号 select x.s# from sc as x，sc as y where x.s# = y.s# and x.c# = c1 and y.c# = c2,字符串操作,命令格式 like：找出满足给定匹配条件的字符串。 格式：列名 not like “字符串” 匹配规则： “%” ：匹配零个或多个字符。 “”：匹配任意单个字符。 列出名称中含有4个字符以上，且倒数第3个字符是d，倒数第2个字符是_的课程的所有信息。 select * from c where cname like “% d ”,元组显示顺序,命令 order by 列名 asc | desc 示例 按年龄升序列出学生信息，相同年龄学生按姓名降序排列。 select * from s order by age asc，sname desc,集合操作,命令 集合并：union 集合交：intersect 集合差： except,集合操作,示例 求选修了001或（且）002号课程的学生号。 （select sno from sc where cno = 001） union (intersect) （select sno from sc where cno = 002）,分组和聚集函数,分组命令 group by 列名 having 条件表达式 group by将表中的元组按指定列上的值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值。having则对分组进行选择，只将聚集函数作用到满足条件的分组上。,分组和聚集函数,聚集函数 平均值：avg 最小值：min 最大值：max 总和：sum 记数：count count（*）：计算集合中元组个数,分组和聚集函数,列出及格的学生的平均成绩。 select s#，avg(grade) from sc group by s# having min(grade) = 60 select sno，avg(score) from sc (where score =60 group by sno,分组和聚集函数,列出每一年龄组中男学生（超过50人）的人数 select age，count(s#) from s where sex = m group by age having count(*) 50,空值,空值测试 is not null 测试指定列的值是否为空值。 示例 找出年龄值为空的学生姓名。 select sname from s where age is null 不可写为where age = null,空值,注意事项 除is not null之外，空值不满足任何查找条件。 如果null参与算术运算，则该算术表达式的值为null。 如果null参与比较运算，则结果可视为false。在sql-92中可看成unknown。 如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null。,集合成员资格,in 子查询 表达式 not in （子查询） 判断表达式的值是否在子查询的结果中。 示例 列出张军和王红同学的所有信息。 select * from s where sname in （“张军”，“王红”）,集合成员资格,示例 选修了001号课程的学生的学号及姓名。 select s#，sname from s where s# in (select s# from sc where c# = 001）,集合成员资格,列出选修了001号和002号课程的学生的学号。 select s# from sc where sc.c# = 001 and s# in （select s# from sc where c# = 002）,集合之间的比较,some/all子查询 表达式 比较运算符 some （子查询） 表达式的值至少与子查询结果中的一个值相比满足比较运算符 。 表达式 比较运算符 all （子查询） 表达式的值与子查询结果中的所有的值相比都满足比较运算符。,集合之间的比较,集合之间的比较,示例 找出平均成绩最高的学生号。 select s# from sc group by s# having avg(grade) = all （select avg(grade) from sc group by s#）,集合基数的测试,测试集合是否为空 not exists （子查询） 判断子查询的结果集合中是否有任何元组存在。 列出选修了01号课程的学生的学号及姓名。 select s#，sname from s where exists （select * from sc where c# = 01 and s# = s.sno）,集合基数的测试,列出选修了001号和002号课程的学生的学号。 select s# from sc sc1 where sc1.c# = 001 and exists （select s# from sc sc2 where sc2. c# = 002 and sc2.s# = sc1.s#） 注：in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询。,列出选修了全部课程的学生姓名。 select sname from s where not exists （select c# from c where not exists （select * from sc where sc.c# = c.c# and sc.s# = s.s# ） ）,任意课程，所求学生选之。 不存在任何一门课程，所求学生没有选之。,集合基数的测试,集合基数的测试,测试集合是否存在重复元组 unique (子查询） 如果子查询结果中没有重复元组，则返回true。 示例 找出所有只教授一门课程的老师姓名。 select pname from prof where unique （select pno from pc where pc.pno = prof.pno),集合基数的测试,找出至少选修了两门课程的学生姓名。 select sname from s where not unique （select sno from sc where sc.sno = s.sno) 思考 unique (a , b , null) , (a , b , null) ?,视图,定义视图 create view view_name(列名，列名 ) as （查询表达式） with check option 视图的属性名缺省为子查询结果中的属性名，也可以显式指明。 with check option指明当对视图进行insert，update时，要检查是否满足视图定义中的条件。 撤消视图 drop view view_name,关系的连接,自然连接：出现在结果关系中的两个连接关系的元组在公共属性上取值相等，且公共属性只出现一次。 内连接：舍弃不匹配的元组。 左外连接：内连接 + 左边关系中失配的元组（缺少的右边关系属性值用null表示）。,关系的连接,求选修了c2课程的学生名 select sname from s, sc where s.s# = sc.s# and sc.c# = c2 select sname from (s nature inner join sc) where c# = c2,关系的连接,列出老师的教工号、姓名、工资、所教课程号 select pno，pname，sal，cno from (prof nature left outer join pc),select pno，pname，sal，cno from prof, pc where prof.pno = pc.pno union select pno，pname，sal，null from prof where pno not in (select pno from pc),插入操作,命令 insert into 表名 （列名，列名 values （值 ，值） 插入一条指定好值的元组 insert into 表名 （列名，列名 （子查询） 插入子查询结果中的若干条元组,插入操作,示例 insert into s values ( s123, “王明”, 21, m) insert into s (s#, sname) values ( s123, “王明”) 思考：age，sex取何值？如何防止插入带有空值的元组？ 将平均成绩大于90的学生加入到excellent中。 insert into excellent ( sno, grade) select sno , avg(score) from sc group by (sno) having avg(score) 90,删除操作,命令 delete from 表名 where 条件表达式 从表中删除符合条件的元组，如果没有where语句，则删除所有元组。 示例 清除所有选课记录 delete from sc,删除操作,删除王明同学所有的选课记录。 delete from sc where s# in (select s# from s where sname = “王明”) 删除低于平均成绩的学生选课记录。 delete from sc where grade (select avg(grade) from sc),更新操作,命令 update 表名 set 列名 = 表达式 | 子查询 列名 = ，表达式 | 子查询 where 条件表达式 指定对哪些列进行更新，以及更新后的值是什么。 示例 学生年龄增加一岁。 update s set age = age + 1,更新操作,将女学生的成绩提高5% update sc set grade = grade * 1.05 where s# in (select s# from s where sex = f),更新操作,当c1课程的成绩小于该课程的平均成绩时，将该成绩提高5% update sc set grade = grade * 1.05 where c# = c1 and grade (select avg(grade) from sc where c# = c1),视图更新,create view sn_age as (select s# , sname, age from s ) insert into sn_age values (s12, “张立”, 23 ) 转换为： insert into s values (s12, “张立”, 23, null),视图更新,create view sn_age as (select sname, age from s ) insert into sn_age values ( “张立” ,23 ) 转换为： insert into s values (null, “张立”, 23, null) 缺主码信息,视图,create view s_g(s#, average ) as ( select s#, avg(grade) from sc group by s# ) update s_g set average = 85 where s# = s1 如何更新sc？,嵌入式sql,为什么使用嵌入式sql？ 有些操作对于交互式sql是不可能的任务。 sql的表达能力相比高级语言有一定的限制，有些数据访问要求单纯使用sql无法完成。一方面，sql在逐渐增强自己的表达能力（参见习题4.10），另一方面，太多的扩展会导致优化能力及执行效率的降低。 非声明性动作 实际的应用系统是非常复杂的，数据库访问只是其中一个部件。有些动作如与用户交互、图形化显示数据等只能用高级语言实现。,嵌入式sql执行过程,sql函数定义库,需要解决的几个问题,区分sql语句与c语言语句 嵌入的sql语句以exec sql开始，以end_exex结束。 exec sql delete from s where s# = s1 end_sql,需要解决的几个问题,嵌入sql语句与主语言之间的数据传递 共享变量 宿主语言变量，既可以用在宿主语言语句中，也可用在sql语句中，用来在两者之间传递数据。 共享变量的声明：声明为通常的c变量，并将其放在下列标识语句之间， exec sql begin declare section exec sql end declare section,需要解决的几个问题,exec sql begin declare section char s_no5; char s_name10; char sqlstate5; exec sql end declare section 注：共享变量出现于sql语句中时，前面加（:）以区别列名。,需要解决的几个问题,sql语句执行信息反馈 良好的应用程序必须提供对错误的处理,应用程序需要知道sql语句是否正确执行了，发生错误时的错误代码，执行时遇到特殊情况时的警告信息。 sqlstate是特殊的共享变量，根据sqlstate中的内容可以获得每一嵌入sql语句执行后的信息,应用程序就可以做相应的处理。 sql语句成功执行，则sqlstate设置为00000，否则标识为错误代码。,需要解决的几个问题,sql与主语言之间操作方式的协调 sql：一次一集合。 主语言：一次一记录。 游标：在查询结果的记录集合中移动的指针。 游标分类： 卷游标：游标的位置可以来回移动，可在活动集中取任意元组。 非滚动游标：只能在活动集中顺序地取下一个元组。 更新游标：数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁。,需要解决的几个问题,定义与使用游标的语句 declare：定义一个游标，使之对应一个select语句。 declare 游标名 scroll cursor for select语句 scroll指明是卷游标 declare只是声明语句，并不执行select语句 open：打开一个游标，执行游标对应的查询，结果集合为该游标的活动集。 open 游标名 游标指向查询结果的第一行之前,需要解决的几个问题,fetch ：在活动集中将游标移到特定的行，并取出该行数据放到相应的宿主变量中。 fetch next | prior | first | last | current | relative n | absolute m 游标名 into 共享变量表 close ：关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句。 close 游标名 free：删除游标，以后便不能再对该游标执行open语句了 free 游标名,关系数据理论,提纲,关系模式的设计问题 函数依赖 范式 函数依赖的推理规则 模式分解,关系模式的设计问题,示例 考虑为管理职工的工资信息而设计一个关系模式,关系模式的设计问题,信息的不可表示问题 插入异常：如果没有职工具有8级工资，则8级工资的工资数额就难以插入 删除异常：如果仅有职工赵明具有4级工资，如果将赵明删除，则有关4级工资的工资数额信息也随之删除了 信息的冗余问题 数据冗余：职工很多，工资级别有限，每一级别的工资数额反复存储多次 更新异常：如果将5级工资的工资数额调为620，则需要找到每个具有5级工资的职工，逐一修改,关系模式的设计问题,望闻问切：不良的数据依赖,函数依赖,函数依赖 设r(u)是属性集u上的关系模式，x , y u， r是r(u) 上的任意一个关系，如果成立 对t , s r，若tx = sx，则ty = sy 那么称“x函数决定y”，或“y函数依赖于x”，记作xy 称x为决定因素 如s# sn， （s#，c#） g,不存在t , s r，tx = sx，但ty sy,函数依赖,找出可能的函数依赖,函数依赖,有关学生的关系模式s(s# , sn , sd , dean , c# , g),函数依赖的等价和覆盖,写一个断言以保证在r(a, b, c)上成立bc,create assertion btoc check (not exists (select b from r group by b having count(distinct c) 1 ) ),平凡函数依赖,思考：一个关系模式有n个属性，那么在它上面成立的所有可能的函数依赖有多少个？非平凡的函数依赖又有多少个？,如果x y，但y x，则称其为非平凡的函数依赖，否则称为平凡的函数依赖 如（s#，sn） sn是平凡的函数依赖,部分函数依赖,（s#，c#） g,（s#，c#） sn,快速热身：找出s中的另一部分函数依赖,传递函数依赖,快速热身：找出职工工资表中的传递函数依赖,s# sd，sd dean,范例,（s#，c#） g s# sn，（s#，c#） sn s# sd，（s#，c#） sd sd dean,关系模式s(s#, sn, sd, dean, c#, g),函数依赖,1nf,定义 关系中每一分量不可再分。即不能以集合、序列等作为属性值,1nf,2nf,2nf,定义 若r1nf，且每个非主属性完全依赖于码，则称r2nf 消除非主属性对码的部分依赖 如s2nf，因为,（s#，c#） sn （s#，c#） sd,2nf,改造 非主属性有两种，一种完全依赖于码，一种部分依赖于码。 将s分解为： sc(s# , c# , g) s_sd(s# , sn , sd , dean) 快速热身 关系模式r（a，b，c，d），码为ab，给出它的一个函数依赖集，使得r属于1nf而不属于2nf,3nf,3nf,定义 关系模式r中，若不存在这样的码x，属性组y及非主属性z(z y)，使得下式成立， xy , yz , yx 则称r3nf 消除非主属性对码的传递依赖 如s_sd 3nf，因为有s#sd，sddean,3nf,改造 将s分解为 student(s# , sn , sd) dept(sd , dean) 快速热身 关系模式r（a，b，c，d），码为ab，给出它的一个函数依赖集，使得r属于2nf而不属于3nf,3nf,bcnf,stc(s# , t# , c#),每位老师只教授一门课,某学生选定一门课，就对应一位老师,stc 3nf ？,候选码,bcnf,bcnf,定义 关系模式r中，对于属性组x，y，若xy且y x时x必含有码，则r bcnf 如spc bcnf，因为t# c#，而t不含有码 改造 将s分解为(s#，t#)，(t#，c#),多值依赖,关系模式teach(c#，t#，b#)，一门课程由多个教员担任，一门课程使用相同的一套参考书。 它的码是（c#，t#，b#），所以属于bcnf,多值依赖,形式化：关系模式r(u)，x、y、zu，z=ux y，对于r(u)的任一关系r，若存在元组t1，t2，使得t1x = t2x，那么就必然存在元组t3，t4，使得： t3 = (t1x, t1y, t2z) t4 = (t2x, t2y, t1z) 则称y多值依赖于x，记作x y 若(c#, t