三级《数据库技术》05-SQL、关系规范化.ppt

1、内容简介：结构化查询语言SQLSQL数据库的体系结构SQL语言的组成和SQL语句的类型*SQL数据定义SQL的数据查询SQL的修改语句SQL中的视图SQL数据控制嵌入式SQL动态SQL关系规范化码的概念范式理论关系模式的分解数据库设计,第5部分SQL语言，关系规范化与数据库设计,SQL的特点SQL语言集数据查询(dataquery)、数据操纵(datamanipulation)、数据定义(datadefinition)和数据控制(datacontrol)功能于一体，充分体现了关系数据语言的特点和优点。其主要特点包括：综合统一高度非过程化面向集合的操作方式以同一种语法结构提供两种使用方式语言简洁

2、，易学易用,结构化查询语言SQL,SQL数据库的体系结构,以SQL为数据库语言的数据库支持三级模式结构：1、全体基本表构成了数据库的模式。基本表是本身独立的表，是实际存放在数据库中的表。SQL中的一个关系对应一个基本表，基本表是按数据全局逻辑模式建立的。2、视图和部分基本表构成了数据库的外模式。视图是从一个或几个基本表或其它视图导出的表，它的数据不独立存放在中，只存放视图的定义。3、数据库的存储文件和它们的索引文件构成了关系数据库的内模式。4、存储文件的逻辑结构组成了关系数据库的内模式。5、表或者是一个基本表，或者是一个视图。6、在SQL中：一个关系对应一个基本表；一个基本表可以存放在多个存储

3、文件中，一个存储文件也可以存放一个或多个基本表；一个基本表可以带若干索引，索引也可以存放在存储文件中。7、SQL用户可以是应用程序，也可以是终端用户。SQL的宿主语言有C，FORTARAN，COBOL，Pascal和Java等语言。SQL也能作为独立的用户接口，供交互环境下的终端使用。,SQL语言的组成和SQL语句的类型*,SQL语言的组成数据定义语言DDL数据操作语言DML数据控制语言DCL嵌入式和动态SQL规则SQL调用和会话规则SQL语句的类型SQL定义语句：创建、删除、修改数据库模式及其对象的语句。SQL数据语句：查询、插入、删除、更新操作SQL事务和控制语句SQL连接、会话和诊断语句

4、SQL数据类型预定义数据类型构造数据类型用户定义数据类型,SQL数据定义,关系数据库的基本对象是表、视图和索引。因此，SQL的数据定义功能包括定义表、定义视图和定义索引。,SQL的模式CREATEschema模式名AUTHORIZATION用户名SQL的域CREATEDOMAIN域名AS数据类型DEFAULTCHECK,SQL的数据查询,数据库查询是数据库操作的核心。SQL语言提供了SELECT语句进行数据库的查询，该语句的一般格式是：SELECTALL|DISTINCT目标列表达式，目标列表达式FROM基本表(或视图)，基本表(或视图)WHERE条件表达式GROUPBY列名1HAVING内部

5、函数表达式ORDERBY列名2ASC|DESC；例：（31）查询单价在600元以上的主机板和硬盘的正确命令是A）SELECT*FROM产品WHERE单价600AND(名称=主机板AND名称=硬盘)B）SELECT*FROM产品WHERE单价600AND(名称=主机板OR名称=硬盘)C）SELECT*FROM产品FOR单价600AND(名称=主机板AND名称=硬盘)D）SELECT*FROM产品FOR单价600AND(名称=主机板OR名称=硬盘),WHERE子句常用的查询条件,注意：统配符的使用：%，_,SQL提供的集函数,SQL提供的集函数主要有：COUNT(DISTINCT|ALL*)统计元

6、组个数COUNT(DISTINCT|ALL列名)统计一列中值的个数SUM(DISTINCT|ALL列名)计算一列值的总和(此列必须是数值型)AVG(DISTINCT|ALL列名)计算一列值的平均值(此列必须是数值型)MAX(DISTINCT|ALL列名)求一列值中的最大值MIN(DISTINCT|ALL列名)求一列值中的最小值,嵌套查询,嵌套查询亦称为子查询，嵌套查询是指一个SELECT-FROM-WHERE查询块可以嵌入在另一个查询块之中。SQL中允许多层嵌套。例1求选修了课程名为“数据库”的学生姓名。SELECTsnameFROMstudentWHEREsnoIN(SELECTsnoFRO

7、MscWHEREcnoIN(SELECTcnoFROMcourseWHEREcname=数据库)；每个子查询在上一级查询处理之前求解，即嵌套查询是由里向外处理的，这样外层查询可以利用内层查询的结果。查询涉及多个关系时用嵌套查询逐次求解层次分明，容易理解也容易书写，具有结构化程序设计的优点。,嵌套查询,（1）由谓词IN引导的子查询。在嵌套查询中，最常用的是谓词IN。例2查询与“陈列”在同一系学习的学生号、姓名和系。SELECTsno，sname，deptFROMstudents1WHEREs1.deptIN(SELECTdeptFROMstudents2WHEREs2.sname=陈列)；（2）

8、谓词是比较运算符的子查询。若能确切知道内层查询返回的是单值，则可以用比较运算符。如上例可改为：SELECTsno，sname，deptFROMstudentWHEREdept=(SELECTdeptFROMstudents2WHERE=陈列)；,嵌套查询,（3）由NOTEXISITS谓词引导的子查询。EXISTS代表存在量词，若内层查询结果非空，则外层查询的WHERE后面的条件为真，否则为假。一般地，要使EXISTS为真，当且仅当其后的SELECT语句查询结果非空。由NOTEXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实

9、际意义。例3查询所有选修了1号课程的学生姓名。本查询涉及student和sc关系。可以在student中依次取每个元组的sno值，用此值去检查sc关系。若sc中存在这样的元组，其sno值等于此student.sno值，并且其cno=1，则取此student.sname送入结果关系。将此想法写成SQL语句是：SELECTsnameFROMStudentWHEREEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno=1)；与EXISTS谓词相对应的是NOTEXISTS谓词。使用存在量词NOTEXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值，

10、否则返回假值。例4查询没有选修1号课程的学生姓名。SELECTsnameFROMstudentWHERENOTEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno=1)；,多个SELECT语句的结果可进行集合操作。并UNION：集合操作中用得最多的是并操作UNION。例：查询选修了课程1或者选修了课程2的学生号。SELECTsnoFROMscWHEREcno=1UNIONSELECTsnoFROMscWHEREcno=2；交：INTERSECT例：查询选修了课程1并选修了课程2的学生号。SELECTsnoFROMscWHEREcno=1INTERSECT

11、SELECTsnoFROMscWHEREcno=2；差：EXCEPT例：查询选修了课程1但没有选修课程2的学生号。SELECTsnoFROMscWHEREcno=1EXCEPTSELECTsnoFROMscWHEREcno=2；,集合查询,SQL中的连接和外连接*,SQL中的连接表JOINOUTERJOINNATURALJOINCROSSJOINSQL中的外连接LEFTJOINRIGHTJOINFULLJOIN,查询结果,存放在数组中：INTOARRAY存放在临时文件INTOCURSOR存放在永久表：INTODBF/TABLE存放在文本文件：TOFILE文件名输出到打印机TOPRINTER,S

12、QL的修改语句,SQL的修改语句包括更新、删除和插入三类语句。更新UPDATE表名SET列名=表达式，列名=表达式WHERE谓词；删除DELETEFROM表名WHERE谓词；插入(1)插入一个元组INSERTINTO表名(字段名，字段名)VALUES(常量，常量)；(2)第2种格式INSERTINTO表名(字段名，字段名)FROMARRAY/FROMMEMVAR注意：操作涉及的记录范围！如果没有where子句，就是全部记录。,SQL中的视图,1创建视图SQL语言用CREATEVIEW命令创建视图，其一般格式为：CREATEVIEW视图名(列名，列名)AS子查询WITHCHECKOPTION；2

13、删除视图视图创建好后，若导出此视图的基本表被删除了，该视图将失效，但一般不会被自动删除。删除视图通常需要显式地使用DROPVIEW语句进行。该语句的格式为：DROPVIEW视图名；3修改视图包括插入(INSERT)、删除(DELETE)和更新(UPDATE)三类操作。由于视图是虚表，因此对视图的更新，最终要转换为对基本表的更新。为防止用户通过视图对数据进行插入、删除和更新时，无意或故意操作不属于视图范围内的基本表数据，可在定义视图时加上WITHCHECKOPTION子句，这样在视图上进行修改数据时，DBMS会进一步检查视图定义中的条件，若不满足条件，则拒绝执行该操作。,SQL数据控制,SQL的

14、数据控制语句包括授权(Grant)、收权(Revoke)和拒绝访问(Deny)三种，其权限的设置对象可以是数据库用户或用户组。授予权限GRANT权限，权限.ON对象类型对象名，对象名TO用户，用户WITHGRANTOPTION；,收回权限REVOKE权限组|ALLPRIVILEGESON对象类型对象名,对象名.FROM用户名组|PUBLIC；,嵌入式SQL,嵌入式SQL预编译的方法，即由DBMS的预处理程序对源程序进行扫描，识别出SQL语句，把他们转换成主语言调用语句程序中使用的任何表(基本表或视图)一般要用EXECSQLDECLARE语句加以说明区分SQL和主语言语句。在所有的SQL语句前面

15、加上前缀EXECSQL使数据库的工作单元与程序工作单元之间能够通信。在SQL语句中使用这些主变量时，需在主变量名前加冒号：做标志。使用游标解决SQL一次一集合的操作与主语言一次一记录操作的矛盾。,嵌入式SQL,游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。游标定义语句EXECSQLDECLARE游标名CURSORFOR子查询FORUPDATEOF字段名1，.n；游标打开语句EXECSQLOPEN游标名；游标推进语句EXECSQLFETCH游标名INTO主变量组；游标关闭语句EXECSQLCLOSE游标名；,动态SQL,含义：在SQL客户模块或嵌入式宿主程序的执行过程中动态生成

16、SQL语句。动态SQL的语句类型可变的SQL的语句条件可变的SQL语句数据库对象、条件都可变的SQL语句动态SQL语句的执行方式立即执行方式ExecuteimmediateSQL语句变量先准备后执行,关系规范化,“不好”的关系模式数据冗余更新异常插入异常删除异常函数依赖部分函数依赖在关系模式R中，如果XY，并且对于X的任何一个真子集X，都有XY，则称Y对X完全函数依赖，记作XfY。若XY，但Y不完全函数依赖于X，则称Y对X部分函数依赖，记作XpY。传递函数依赖X、Y和Z是R的属性集合的任意子集，如果XY、YZ且YX，Z-Y，Y-X，则称Z传递依赖于X，记作XtY,码的概念,候选码主码全码主属性

17、,范式理论,第一范式（1NF）元组的每个分量必须是不可分的数据项，即表中不能有表第二范式（2NF）即当1NF消除了非主属性对码的部分函数依赖，就称为2NF。第三范式（3NF）3NF是2NF消除了非主属性对码的传递函数依赖。Boyce-Codd范式(BCNF)3NF消除了主属性对码的部分函数依赖和传递函数依赖第四范式(4NF)限制关系模式的属性之间不允许有非平凡且非非函数依赖的多值依赖,范式理论,例1：设有关系模式R(U，F)，其中U=B，S，P，Q，I，D，F=SD，IB，(I，S)Q，BP(1)(I，S)是关系模式R的一个候选码吗?(2)关系模式R属于第几范式?不属于第几范式?【分析】本题考

18、查的是函数依赖及其范式的知识点。(1)因为IB，BP，根据传递率，由IP。又因为IP，IB，SD，(I，S)Q，根据合并率有：(I，S)(B，P，D，Q)根据增广率有：(I，S)(I，S，B，P，D，Q)因为(I，S)能够决定关系模式中的所有属性，所以(I，S)是关系模式R的一个候选码。(2)因为(I，S)是候选码，所以主属性是I和S，非主属性是B、P、Q、D，因为F中存在SD，IB，即存在非主属性对候选码的依赖，所以R不属于2NF，但属于1NF。,范式理论,例2：有关系模式A(C，T，H，R，S)，其中各属性的含义为C：课程，T：教员，H：上课时间，R：教室，S：学生。现有下述函数依赖集：F

19、=CT，(H，R)C，(H，T)R，(H，S)R(1)找出关系模式A的码。(2)关系模式A属于第几范式，不属于第几范式?【分析】本题考查的是函数依赖及其范式的知识点。(1)因为有(H，S)R，(H，R)T，则根据伪传递规则有：(H，H，S)T，即(H，S)T因为(H，S)R，(H，R)C，同样有：(H，S)C因为(H，S)T，(H，S)R，(H，S)C，所以有：(H，S)(T，C，R)，码为(H，S)(2)由于码为(H，S)，主属性为H和S，非主属性为：T，C，R。由于(H，S)C，CT，所以T传递依赖于主码(H，S)，存在非主属性对主码的传递依赖，但不存在非主属性对主码的部分依赖，所以关系模

20、式A为2NF，但不是3NF。,关系模式的分解,“一事一地”设计原则让一个关系模式只表示一个实体或一种联系同一关系模式有多个分解方案模式分解的等价标准分解具有无损连接性分解保持函数依赖。关于模式分解的几个事实(1)分解具有无损连接性和分解保持函数依赖是两个互相独立的标准。具有无损连接性的分解不一定保持函数依赖；保持函数依赖的分解不一定具有无损连接性。(2)若要求分解具有无损连接性，那么模式分解一定可以达到BCNF。(3)若要求分解保持函数依赖，那么模式分解可以达到3NF，但不一定能达到BCNF。(4)若要求分解既具有无损连接性，又保持函数依赖，则模式分解可以达到3NF，但不一定能达到BCNF。,

21、数据库设计,数据库系统设计的内容结构特性的设计：设计数据模型行为特性的设计：应用程序的设计数据库系统设计的步骤需求分析概念结构设计：现实世界中实体之间的关系逻辑结构设计：按某种数据模型的特性物理结构设计：数据存储结构和存储方法数据库的实施数据库运行与维护,数据库设计-概念结构设计,实体：显示世界中任何可以相互区别的事物，可以是一个个具体的东西，也可以是一个抽象的概念。如：教师、学生、教室都是实体，哲学也是一个实体。在我们的例子中，图书、读者、订单、供应商都是实体。属性：实体所具有的性质。如：学生的学号、姓名、性别、班级等都是学生实体的属性。实体和属性的型和值：实体和属性都有“型”和“值”之分。

22、“型”是概念的内涵，“值”是概念的特例。例如：学生实体可以用学号、姓名、班级、出生日期等属性描述，是实体的“型”；0102312、张三、01信息、09/21/83则表示了学生张三的情况，是实体的值。,数据库设计-概念结构设计,实体之间联系方式一对一的联系：若两个不同型实体集中，一方的一个实体唯一与另一方的一个实体相对应，称1:1联系。一对多的联系：若两个不同型实体集中，一方的一个实体可与另一方的多个实体相对应，但另一方一个实体只与本方一个实体相对应，称1:m联系。多对多的联系：若两个不同型实体集中，任何一方的一个实体都与另一方的多个实体相对应，称m:n联系。例：（8）一间宿舍可住多个学生，则实体宿舍和学生之间的联系是A）一对一B）一对多C）多对一D）多对多,数据库设计-概念结构设计,E-R图如何描述信息世界中实体和实体之间的关系？E-R图，也称实体联系方法，用形式化的方法表示了实体以及实体之间的联系。E-R图中的图形元素要求：根据问题画出E-R图。概念结构设计的主要步骤：(1)设计局部ER模型(2)设计全局ER模型(3)全局ER模型的优化,n,m,学号,姓名,性别,出生年月,课程编号,课程名称,学时,成绩,学生,学习,课