ch22关系代数和关系约束.ppt

第二章 数据模型 2.1 数据模型概述 2.2 关系模型基础 2.3 在SQL中定义关系模式 2.4 代数查询语言 2.5 关系上的约束 An Introduction to Database Systems 2.3 在SQL中定义关系模式 v流行的描述和操作关系数据库的语言SQL（读作 “sequel”）,最新的标准为SQL-99。 v提供两方面的内容： 数据定义语言DDL - 定义数据库模式 数据操作语言DML - 查询和更新数据库 本节介绍SQL的数据定义部分，第6章介绍数据操 作，第 7章介绍SQL的数据约束问题。 An Introduction to Database Systems 2.3.1 SQL中的关系 vSQL中的三类关系 表（Table） 存储在数据库中的关系，用户可以对其中的元 组进行查询和更新。 视图（View） 通过计算定义的关系，并不在数据库中存储 ，只在需要的时候被完整或部分地构造。 临时表 执行数据查询和更新时由SQL处理程序临时 构造，处理结束后被删除。 An Introduction to Database Systems 2.3.2 SQL的基本数据类型 v可变长度或固定长度字符串 v固定或可变长度的位串 v布尔类型 v整型 v浮点类型 v日期和时间类型 An Introduction to Database Systems 固定长度和可变长度字符串类型 vCHAR(n) 最大为n个字符的固定长度字符串。当字符数量小于定 义的长度时，后面补上某个短字符(例如，空格)。 例如，将字符串foo赋给CHAR(5)类型的字段时，存储 的实际值是foo 。 vVARCHAR(n) 表示最多可有n个字符的字符串。当字符数量小于n时， 使用一个结束符或字符长度值来标识字符串的结束，未满 的空间不会被填充。 An Introduction to Database Systems 固定或可变长度的位串 vBIT(n) 固定长度为n的位串。 vBIT VARYING(n) 最大长度为n的位串。 其区别类似于上述的固定或可变长字符串，区别在于取 值为比特位取值。 An Introduction to Database Systems 布尔类型和整型数据 vBOOLEAN 表示具有逻辑类型的值。该类属性的可能取值为TRUE、 FALSE和UNKNOWN。 vINT(等价于INTEGER) 表示具有准确数字的整数数据类型。一般分为：shortint 、int、bigint等，位数不同使其取值范围不同。 An Introduction to Database Systems 近似数字类型 vFLOAT(和REAL) 用于表示浮点数字数据的近似数字数据类型。浮点数据 为近似值；并非数据类型范围内的所有数据都能精确地表 示。 float ( n )：n 为用于存储科学记数法 float 数尾数的位 数，同时指示其精度和存储大小。 vdecimal (和numeric) 带定点精度和小数位数的浮点类型。 Decimal (n, d)：n位有效数字的十进制数，小数点是在 从右往左第d位的位置。 An Introduction to Database Systems 日期和时间类型 vDATE日期类型 日期值由关键字DATE后面接一个用单引号括起来的特 定形式的字符串来定义。 例如，DATE 1948-05-14，注意数字0的填充。 vTIME时间类型 时间值由关键字TIME和一个特定形式的字符串组成。 例如，TIME 15:00:02.5 An Introduction to Database Systems 2.3.3 简单的表定义 v简单表的定义形式 CREATE TABLE 关系名 （属性名1，属性名2， ，属性名n）； P17 例2.2 关系MOVIES的SQL定义 P17 例2.3 关系MovieStar的SQL定义 An Introduction to Database Systems 2.3.4 修改关系模式 v删除关系模式 DROP TABLE 关系名； v修改关系模式 添加属性：添加的字段值为空值NULL ALTER TABLE 关系名 ADD 属性名 属性类型 例如，ALTER TABLE MovieStar ADD phone CHAR(16); 删除属性 ALTER TABLE 关系名 DROP 属性名 例如，ALTER TABLE MovieStar DROP birthdate; An Introduction to Database Systems 2.3.5 默认值 v在任何声明属性及其数据类型的地方，使用 DEFAULT关键字为属性设置默认值。 DEFAULT 常量或 NULL 例如， gender CHAR(1) DEFAULT ? birthdate DATE DEFAULT DATE 0000-00-00 ALTER TABLE MovieStar ADD phone CHAR(16) DEFALUT unlisted; An Introduction to Database Systems 2.3.6 键的声明 v在CREAT TABLE语句中声明键的两种方法 当属性被列入关系模式时，声明其为键； 在模式声明的项目表中增加表项，声明一个或者一 组属性是键。 注意：如果键由多个属性组成，则只能用第二种方 法。 v两种指明键的关键字： PRIMARY KEY UNIQUE v指明键的效果是使关系的任一两个元组不能在键的所有属 性上具有完全相同的值。 An Introduction to Database Systems v PRIMARY KEY和UNIQUE的区别在于，声明为UNIQUE 的属性集可以取NULL值，而PRIMARY KEY不能。 可以用关键字UNIQUE替代 An Introduction to Database Systems 第二章 数据模型 2.1 数据模型概述 2.2 关系模型基础 2.3 在SQL中定义关系模式 2.4 代数查询语言 2.5 关系上的约束 An Introduction to Database Systems v关系操作的存取路径对用户隐蔽，用户只要指出“ 做什么”，不必详细说明“怎么做”。 v常用的关系操作 查询 选择、投影、连接、除、并、交、差 数据更新 插入、删除、修改 2.4.1 关系数据操作概述 An Introduction to Database Systems v关系数据语言的种类 关系代数语言 关系演算语言 关系数据库标准SQL An Introduction to Database Systems v关系代数的传统定义 一个元组集合（即关系），能用来进行典型的基于关系 的查询 集合上的五个操作：并、差、笛卡尔积、选择、 投影 在这些基本操作上定义的附加操作，例如各种连 接 关系代数的操作规则对于集合和包是不一样的 简单的说，包是以空间代价换取时间效率 所以对一般小例子来说，包的综合效率更高 但对实际应用中的数据库来说，用集合更加合理 2.4节的介绍针对集合，第5章介绍针对包 2.4.2 三种关系数据语言 An Introduction to Database Systems v关系演算语言：用谓词来表达查询要求。 元组关系演算语言 谓词变元的基本对象是元组变量 典型代表：ALPHA, QUEL 域关系演算语言 谓词变元的基本对象是域变量 典型代表：QBE An Introduction to Database Systems v 结构化查询语言SQL 具有关系代数和关系演算双重特点 SQL语言的特点： 是一种高度非过程化的语言 存取路径的选择由DBMS的优化机制来完成 能够嵌入高级语言中使用 与关系代数和关系演算语言在表达能力上完 全等价 An Introduction to Database Systems v 关系代数语言 通过以下运算符对关系进行运算来表达查询要求 l并、交、差等集合运算符； l除去某些行或列的操作。例如，选择、投影 l组合两个关系元组的操作。例如，笛卡尔积，连接 等 l、=、） ：逻辑运算符（ 或或） ：表示任选项 ：表示上述格式可以重复下去 An Introduction to Database Systems 关系Movies如下图所示： length100(Movies) = 在关系Movies中所有Fox公式出品的至少有100分钟长的电影，请写出 相应的关系代数查询表达式。 length100 and studioName=Fox (Movies) = An Introduction to Database Systems 2.4.7 笛卡尔积 v 关系R和S的笛卡尔积(或称叉积或积)记为RS，结果是一个 有序对的集合，其中第一个元素是关系R中的任何一个元组 ，第二个元素是关系S中的任何一个元组。 v RS的关系模式是R和S关系模式的并，当R和S具有相同的 属性A时，在结果关系模式中分别用R.A和S.A表示来自R和 S的属性。 例如，R为n目关系，k1个元组；S为m目关系，k2个 元组 则 RS = tr ts |tr R tsS 列：（n+m）列的元组的集合 元组的前n列是关系R的一个元组 后m列是关系S的一个元组 行：k1k2个元组 An Introduction to Database Systems ABC a1 b1 c1 a1 b2 c2 a2 b2 c1 ABC a1b1c1 a1b1c1 a1b1c1 a1b2c2 a1b2c2 a1b2c2 a2b2c1 a2b2c1 a2b2c1 ABC a1 b2 c2 a1 b3 c2 a2 b2 c1 R S R S= ABC a1b2c2 a1b3c2 a2b2c1 a1b2c2 a1b3c2 a2b2c1 a1b2c2 a1b3c2 a2b2c1 An Introduction to Database Systems R S= An Introduction to Database Systems 2.4.8 连接 v连接操作 参与连接操作的一般是两个关系，且连接时相应的 元组在某些方面具有一致性 连接分为三类 全连接即笛卡尔积 自然连接 连接 An Introduction to Database Systems v 自然连接 关系R和S的自然连接表示为R S。此操作仅把在R和S 模式中有某共同属性，且此属性有相同值的元组配对。 结果关系中的元组称为连接元组，连接元组具有R和S连 接的所有成分。连接元组示意见图2-15 例2.13，例2.14 R S= 注意：自然连接还需要取消重复列 An Introduction to Database Systems v 自然连接 悬浮元组（dangling tuple） 在一个连接当中，如果一个元组不能和另外关 系中的任何一个元组配对的话，就称其为悬浮元组 例2.13中S的(9, 10, 11) 共同属性为属性集合时的自然连接，必须是在共同属 性集上取值一致的元组才能组合形成连接元组。 U V= An Introduction to Database Systems v连接 定义 按照给定约束条件将两个关系中的元组进行配对连 接的操作 过程 先计算两个关系的笛卡尔积 在上述结果中寻找满足约束条件的元组 重名属性通过附加前缀的办法进行处理 U ADV= An Introduction to Database Systems v连接 自然连接和连接的区别 前者要将公共属性合并，后者不合并 原因：条件连接时，相同属性的属性值不一定约束成等值关系 ，而要合并的话，基本前提肯定是要等值 U AD AND U.BV.BV= An Introduction to Database Systems ABC a1b15 a1b26 a2b38 a2b412 BE b13 b27 b310 b32 b52 CE R S= R S AR.BCS.BE a1b15b27 a1b15b310 a1b26b27 a1b26b310 a2b38b310 等值连接 R S = R.B=S.B AR.BCS.BE a1b15b13 a1b26b27 a2b38b310 a2b38b32 An Introduction to Database Systems 自然连接 R S= ABCE a1b153 a1b267 a2b3810 a2b382 P30习题2.4.5 An Introduction to Database Systems 象集Zx 给定一个关系R（X，Z），X和Z为属性组。当 tX=x时，x在R中的象集（Images Set）为： Zx=tZ|t R，tX=x 它表示R中属性组X上值为x的诸元组在Z上分 量的集合。 象集和除运算 An Introduction to Database Systems 给定关系R (X，Y) 和S (Y，Z)，其中X，Y，Z为属性组。 R中的Y与S中的Y可以有不同的属性名，但必须出自相同 的域集。 R与S的除运算得到一个新的关系P(X)，P是R中满足 下列条件的元组在X属性列上的投影：元组在X上分量值 x的象集Yx包含S在Y上投影的集合。 RS = tr X | tr RY (S) Yx Yx：x在R中的象集，x = trX 除运算 An Introduction to Database Systems ABC a1b1c2 a2b3c7 a3b4c6 a1b2c3 a4b6c6 a2b2c3 a1b2c1 BCD b1c2d1 b2c1d1 b2c3d2 RS A a1 R S An Introduction to Database Systems 在关系R中，A可以取四个值a1，a2，a3，a4 a1的象集为 (b1，c2)，(b2，c3)，(b2，c1) a2的象集为 (b3，c7)，(b2，c3) a3的象集为 (b4，c6) a4的象集为 (b6，c6) S在(B，C)上的投影为 (b1，c2)，(b2，c1)，(b2，c3) 只有a1的象集包含了S在(B，C)属性组上的投影 所以 RS =a1 An Introduction to Database Systems 2.4.10 组合操作构成查询 v 使用组合操作生成查询 关系代数中关系操作表达式的形式不局限于两个关系的 单次操作，可以是复杂组合 P26 例2.17，查询由Fox制作的至少100分钟的电影名称 (title)和制作年份(year); 表达式树 其中叶子节点是参与操作的关系，根节点代表查询结果， 中间层次节点对应查询过程分解后的中间结果 An Introduction to Database Systems 该查询对应的代数表达式 该例引出的另一个问题 同一个查询可能对应多种不同形式的等价表达 式，但在SQL的实现中要根据效率优先原则进行取舍实 现查询优化 An Introduction to Database Systems 2.4.11 命名和重命名 v 重命名 操作符：s(A1,A2,An)(R) 标准的重命名表达式同时重命名属性和关系，如果只 重命 名关系，则表达式中不需要带属性列表。 例2.18。重命名的一类用法，即对属性重命名而关系名 不变，用于连接等操作的预处理 Rs(X,C,D)(S)或RS(A,B,X,C,D)(RS) 两种做法：重命名属性后再连接和连接后再重命 名属性 区别在于后者必须指定一个关系名（重命名表达式的 固定格式要求） An Introduction to Database Systems Rs(X,C,D)(S) Rs(A,B,X,C,D)(RS) 1.重命名属性后再连接 2.连接后再重命名属性 An Introduction to Database Systems 2.4.12 操作之间的联系 v 操作表达式的等价表示 交运算可用差运算表示 从R中去掉那些在R而不在S中的元组 条件连接运算可用积操作和选择操作表示 从笛卡尔积中选择满足条件的元组 自然连接可以在积操作的基础上按照等值条件进行选择 操作，最后再对相同属性进行投影去重 令条件C为： 令L为所有R中的属性和在S但不在R中的属性列表 An Introduction to Database Systems 一个关系代数操作集合的最小化完备集 即其他操作都可以由这些操作组合代替，而且这些操作 之间不能互相代替实现 并、差、选择、投影、积运算、重命名 U V 等价于 U AD AND U.BV.BV 等价于 An Introduction to Database Systems v 关系代数表达式中的线性符号 复杂的关系代数表达式之前可用树状结构表示（回顾pp.26例 2.17） 也可以用临时关系（变量）表示树的中间层节点，配合一系列 赋值语句来表示 一般根节点关系为Answer(attrlist)，并出现在最后一个赋 值语句左端 一般一个赋值语句表示一个单步操作，但如果节点层次 较为复杂，则某些中间层次节点对应的赋值语句右端可以是一个 组合形式的复杂表达式 例2.20 2.4.13 代数表达式的线性符号 An Introduction to Database Systems 5.2 关系代数操作：连接实例 v 设关系student（学生）、course（课程）和sc（成绩）如下： 学号 sno 姓名 sname 性别 sex 年龄 sage 系 sdept 95001 95002 95003 95004 李勇 刘晨 王敏 张立 男 女 女 男 20 19 18 19 CS IS MA IS 课程号 cno 课程名 cname 先行课 cpno 学分 credit 1 2 3 4 5 6 7 数据库 数学 信息系统 操作系统 数据结构 数据处理 C语言 5 1 6 7 6 4 2 4 3 4 2 4 学号 sno 课程号 Cno 成绩 grade 95001 95001 95001 95002 95002 1 2 3 2 3 92 85 88 90 80 student sc course An Introduction to Database Systems 5.2 关系代数操作举例 v 1、查询计算机系所有同学的姓名 v 2、查询选修了2号课程的学生的姓名 v 3、查询至少选修了一门其直接先行课为5号课程的学生 姓名 组合操作方法：sname(sdept=“CS”(student) 或线性符号：R1=sdept=“CS”(student) Result= sname(R1） 组合操作方法：sname(cno=“2”(student sc) 或：sname( (student cno=“2”(sc) (比较两者效率) 线性符号:R1=cno=“2”(sc)；R2= student R1；Result= sname(R2） 组合操作方法：sname(cpno=“5”(course) sc sno,sname(student) An Introduction to Database Systems 5.2 关系代数操作举例 v 4、查询所有选修数学课，成绩大于80分的女生的姓名 v 5、查询被两个以上同学选修的课程的课程名 组合操作:sname(sex=“女” and cname=“数学”(student sc course) 或： sname( (sex=“女”(student) sc cname=“数学” (course) 组合操作法： cname(cno(sc1(sno1,cno1,grade1)(sc) sc) (cno,cname(course) C=o1=o and sc1.sno1sc.sno w解释原因：-求解过程 C An Introduction to Database Systems v 上面第5题解题过程 cnocnameCpnocredit 1 2 3 4 5 6 7 数据库 数学 信息系统 操作系统 数据结构 数据处理 C语言 5 1 6 7 6 4 2 4 3 4 2 4 snoCnograde 95001 95001 95001 95002 95002 1 2 3 2 3 92 85 88 90 80 sno1Cno1grade1snoCnograde 95001 95001 95001 95001 95001 95002 95002 95002 95002 1 2 2 3 3 2 2 3 3 92 85 85 88 88 90 90 80 80 95001 95001 95002 95001 95002 95001 95002 95001 95002 1 2 2 3 3 2 2 3 3 92 85 90 88 80 85 90 88 80 sc1 sc：(C=o1=o) C v 对上述的连接再加条件，使得红色及蓝 色元组各合并成一条元组，且能去掉黑色 元组 C=o1=o and sc1.sno1sc.sno v 从连接结果投影出cno为： 即被两个以上同学选修的课程的课程 名 Cno 2 3 v 为求得课程名还得跟course连接 A