数据库原理与设计-陶宏才.ppt

1、数据库原理及设计Principle and Design of Database,主讲:朱 焱 博 士 SIST,SWJTU2009.10,数据库原理及设计 Dr.Yan Zhu,2,学习目的和要求从概念化数据库设计 进入逻辑数据库设计关系数据模型的基本概念和术语关系数据模型上的完整性约束关系代数关系运算,第三章 关系数据模型,数据库原理及设计 Dr.Yan Zhu,3,DBMS无关,DBMS有关,数据库设计过程,概念模式,内模式,逻辑设计模式优化,数据库,数据库需求,使用高级数据模型ERM,使用特定的DBMS的数据模型,物理DB设计,数据库原理及设计 Dr.Yan Zhu,4,关系数据模型,

2、数据模型的三要素,是本章学习的路标静态数据结构;动态数据操作;完整性约束这三要素体现在如下问题中:如何表达数据本身？如何表达数据间的联系？有哪些动态数据操作？能表达哪些完整性约束？完整性约束如何设定？完整性约束的效果？如何进行关系模型的DB设计？,数据库原理及设计 Dr.Yan Zhu,5,ER模式向关系数据模式逻辑映射,数据库原理及设计 Dr.Yan Zhu,6,ER 关系-表描述实体,关系模式（relational schema)的关键：基本表（base table)，也简称关系表。表由“列”（Column）和“行”（Row）组成。行又叫元组（tuple），列又叫字段（field）。数据对

3、象（实体集）用关系（表）来定义。ER中实体集的属性表中的列（字段、属性）；ER中实体集中的每个实体实例表中的行（元组、记录）。,列,行,学生表,数据库原理及设计 Dr.Yan Zhu,7,ER关系-表描述联系,ER模式中联系是单独描述的。而在关系模式中，联系也可以用表来描述。例如，选课联系选课表。表中有来自学生和课程两个实体的属性，而成绩是选课关联自身的描述属性。列的取值范围称为域（Domain）。例如，成绩的范围一般是：0-100。,学生选课表,数据库原理及设计 Dr.Yan Zhu,8,关系模式、关系、主键、外键,关系模式（Schema）由关系名、和各个列表示。数学上表示为：R（A1，A2

4、，An)。关系实例：由表中的各行构成。常简称关系；行序不重要，而列序重要。每个元组的字段必须对应关系模式中的字段。（物理存储要求的）关系实例表示：，ai为属性Ai的值。ER模型中的候选键关系中的候选键ER模型中的主键关系中的主键(Primary Key,PK)关系中的新概念-外键，描述表与表之间的关联。,数据库原理及设计 Dr.Yan Zhu,9,表与表的联系,主键,外键,学生表,班级表,主键/候选键,外键(Foreign Key,FK):A表中的某个属性（组）是B表中的候选键或主键，则称该属性（组）为A表的外键。要使B表中的某个属性成为A表的外键，必须确定该属性为B表的主键或候选键。如何指定

5、？完整性约束的任务。,数据库原理及设计 Dr.Yan Zhu,10,关系模型术语,数据库原理及设计 Dr.Yan Zhu,11,关系的性质,关系是一个集合。集合中的元素是元组，每个元组的属性数目应该相同。关系是一种规范化了的二维表格，不是一般的二维表。它的性质是：关系中每一个属性值都是不可分解的；关系中不允许出现重复元组；关系是元组的集合，因此无行序；关系的列是有序的。,数据库原理及设计 Dr.Yan Zhu,12,逻辑映射之数据结构映射,关系表,表的主键/候选键,表的列,表和列,表中的各行,？,数据库原理及设计 Dr.Yan Zhu,13,SQL语言,SQL发音为Sequel，现在趋向于发音

6、为S-Q-L。,最早在IBM System-R RDBMS 上使用的查询语言；,第一个标准由ANSI于1986年制订,称为SQL-86；,1992年推出了SQL-92，是大多数RDBMS支持的版本；,1999年提出SQL:1999(SQL3)，是SQL-92的扩展。,SQL在数据库界之外也受到重视。在软件工程、AI、万维网数据管理、等领域显示了潜力。,SQL成为国际标准后，由于各种类型的计算机和DBS都采用SQL作为其存取语言和标准接口，从而使数据库世界有可能链接为一个整体。前景非常好。,数据库原理及设计 Dr.Yan Zhu,14,几个重要的SQL语言命令,SQL DDL针对表结构的操作Cr

7、eate（创建）Drop（删除）Alter（修改）SQL DML针对表中数据的操作Insert（插入）Delete（删除）Update（修改）Select（查询）,数据库原理及设计 Dr.Yan Zhu,15,关系模型中的数据操作,动态的数据操作：增加、删除、修改和查询，简称：增删改查询。增加：INSERT R VALUES（林海，男）修改：UPDATE R SET name=张湖,数据库原理及设计 Dr.Yan Zhu,16,逻辑映射之数据操作,关系表,表的主键/候选键,表的列,表和列,表中的各行,使用SQL语句对表进行操作,？,数据库原理及设计 Dr.Yan Zhu,17,关系模型上完整性

8、约束,完整性约束（Integrity Constraints,ICs）可以帮助阻止非法数据的输入。它要求存入DB的数据应满足一些条件。在定义一个关系模式的同时，或之后定义完整性约束。当表中数据发生变化（如Insert、Delete、Update）时，DBMS即检查更新的数据是否满足完整性约束指定的条件。几种完整性约束：域约束、主键约束、唯一约束、外键约束等。,数据库原理及设计 Dr.Yan Zhu,18,几种完整性约束（1）,域限制（Domain Constraint）每个属性A的值必须是来自域 dom（A）的原子值。例如，表中某列的数据类型为“整数型”，那么该列的各记录值就不能是“字符串”。

9、不必显示指定。主键约束（Primary Key Constraint）概念：针对主键而言，保证主键的完整性。要求：主键值必须唯一，且不能为空值。例如：作者不能作主键。需要显示指定。（如何指定，以后介绍）,数据库原理及设计 Dr.Yan Zhu,19,几种完整性约束（2）,唯一约束（Unique Constraint）主键可以用主键约束来保证其值的完整性。因为候选键也能唯一标识关系实例元组，因此，保证候选键的完整性，需要唯一约束。要求值唯一，允许有一个且仅有一个空值。以上是“表本身”的完整性约束。外键限制（Foreign Key Constraint）也被称为“参照完整性约束”。目的：用来维护表

10、与表之间的数据一致性，即其中一张表的改动，可能要求另一张表要作出某些改动，以保持数据一致。为使DBMS能做这样的检查，则应指定这种涉及两个表的ICs，此即外键限制。,数据库原理及设计 Dr.Yan Zhu,20,主表与从表,主表：指外键在另一张表中作主/候选键的表。（例中的班级表）从表：指含有外键的表。（例中的学生表）,班级表（主表）,学生表（从表）,数据库原理及设计 Dr.Yan Zhu,21,外键约束的例子(主表从表）,学生表（从表）,班级表（主表）,数据库原理及设计 Dr.Yan Zhu,22,分析外键约束（主表从表）,对主表进行三种操作，观察对表间完整性的影响：对主表中的主键进行操作插

11、入：要求插入值满足主键限制即可，不影响其它表。修改：可能会影响与该主键相关的从表的外键值。当相应的外键值存在时，有两个策略可用：一是改变对应从表的所有外键值，使之与主键一致；其二是不允许修改主表中的主键值。删除：可能会影响与该主键相关的从表的外键值。若相应的外键值存在时，策略有二：一是不允许删除主表的主键值；二是级联删除从表中相应外键值所在的行。,数据库原理及设计 Dr.Yan Zhu,23,外键约束的例子(从表主表）,主键,学生表（从表）,班级表（主表）,数据库原理及设计 Dr.Yan Zhu,24,分析外键约束（从表主表）,对从表中的外键操作插入：要求插入的外键值应“参照”（Referen

12、ce）主表中的主键值。修改：要求修改的外键值“参照”主表中的主键值。删除：不需要参照主表中的主键值。,数据库原理及设计 Dr.Yan Zhu,25,外键约束小结,维护表间数据完整性从两个方向上完成，即：主表从表：“主表”中的主键值在修改和删除时，“从表”中与该主键值相同的外键值可“级联”（CASCADE）修改和删除，或“禁止”（NO ACTION）“主表”主键值的修改和删除。从表主表：表示“从表”中的外键值在插入和修改时，其值应“参照”（Reference）“主表”中的主键值。,数据库原理及设计 Dr.Yan Zhu,26,表间数据完整性维护的实现,利用外键约束定义对从表定义外键限制（或称参照

13、完整性）完成主表和从表间两个方向的数据完整性；利用触发器主表的触发器维护主表到从表方向的数据完整性，而从表的触发器维护从表到主表方向的参照完整性。SQL-92标准只支持外键约束方式。如果使用触发器，必须检查所用的RDBMS是否支持。,数据库原理及设计 Dr.Yan Zhu,27,其他的约束 检查限制,域限制、主键限制、唯一限制和外键限制是关系数据模型中最基本的限制，大多数商用系统都支持他们。其他还有更一般的限制，如：检查限制(Check Constraint，亦称“表限制”)检查某一列值是否在某一取值范围之内；表中某几列是否满足指定的条件。与单个表有关。例如：中学生的年龄定义为两位整数，范围很

14、大，用户可以写如下规则，将年龄限制在10-20岁之间CHECK(AGE BETWEEN 10 AND 20),数据库原理及设计 Dr.Yan Zhu,28,其他的约束性 断言限制,断言限制（Assertion）检查表中个别列、整个表或表与表之间是否满足指定的条件。与多个表有关，是关系之间的约束。例如：希望限定计算机图书的作者一定是女性。这时，仅靠键约束、唯一约束和外键限制不能实现，因为这个要求涉及到两张表（关系）：Book，Author。可以写为：Create Assertion NoManComputerAuthorCheck（Book.计算机类.作者 Author.性别为男性=空集)与主键

15、约束和唯一限制的定义不同，定义表约束和断言约束时要显式定义。,数据库原理及设计 Dr.Yan Zhu,29,完整性约束的实施,关系创建并指定了ICs后，当关系“更新”时（指插入、删除和修改）应实施检查。对域限制、主键限制和唯一限制的实施由于影响直接，故只要插入/删除/修改命令违背了限制，即被拒绝。而其它ICs（如一般性限制）的违背检查通常是在每个SQL语句之后。对参照完整性（外键）限制的实施外键限制的影响较复杂，要从两个方向分别进行，但不是同时进行。,数据库原理及设计 Dr.Yan Zhu,30,完整性约束小结,SQL中把完整性约束分成三大类：域约束基本表约束：主键约束；候选键约束（唯一约束）

16、；外键约束;检查约束表间约束:断言,数据库原理及设计 Dr.Yan Zhu,31,逻辑映射之约束,关系表,表的主键/候选键,表的列,表和列,表中的各行,使用SQL语句对表进行操作,域约束、主键约束、唯一约束、外键约束、检查约束、断言,数据库原理及设计 Dr.Yan Zhu,32,SQL Server对数据完整性约束的支持,不同的数据库管理系统厂商开发不同类型的SQL，被称为数据库方言。例如：Microsoft SQL Server 产品中的SQL方言叫Transact-SQL。T-SQL，即事务SQL，是对标准SQL的扩展。T-SQL由SQL语句、函数和存储过程三部分组成。T-SQL除了具有标

17、准的子语言：DDL，DML，DCL之外，还具有过程控制能力和事务控制能力。T-SQL有两类变量：局部变量：如 name,price全局变量(系统定义，用户只能引用，不能修改、定义）：如 error,rowcount,数据库原理及设计 Dr.Yan Zhu,33,SQL Server系统数据类型（一）,pp179(pp139)表4-2Tinyint,1 byte,数值范围是028-1精确小数是numeric(p,s)、decimal(p,s).近似小数是float和real。Datetime表示日期和时间。例如：0329/2006 10:05:00:00:00 PM,数据库原理及设计 Dr.Ya

18、n Zhu,34,SQL Server系统数据类型（二）,Char(n)和varchar(n)Char(10)可表示最多10个字符长的字符串,物理存储空间是10个字符,不能改变.如果输入字符少于10,实际存储仍然占满10个字符长,不足部分用前空格补足.varchar(10)则不同，10为最大字符串长度。实际输入是几个字符,存储时只占满几个字符,是可变的。1234567890，1，650都是合法数据。但它们在上述两种数据类型中所用空间不同。Nchar,Nvarchar,ntext是Unicode 数据类型,数据库原理及设计 Dr.Yan Zhu,35,SQL Server对完整性限制的支持,SQ

19、L Server中的数据完整性可粗分为两大类，如下表。,数据库原理及设计 Dr.Yan Zhu,36,默认值（1）,默认值（default）：用于为列或用户自定义数据类型指定缺省值，每一列或自定义类型只能有一个缺省值。当用户没有给指定有默认值的列输入数据时，RDBMS自动用该默认值代替。两种方式：表定义时设定CREATE TABLE publishers(pub_id char(4)NOT NULL,pub_name varchar(40)NULL,city varchar(20)DEFAULT Pasadena,state char(2)DEFAULT CA),数据库原理及设计 Dr.Yan

20、 Zhu,37,默认值验证,CREATE table 是创建表命令。在SQL Server中，对字符型数据须用单引号括起来。利用两条SQL语句来验证默认的作用与效果。INSERT publishers(pub_id)VALUES(0001)SELECT*FROM publishers,publishers,数据库原理及设计 Dr.Yan Zhu,38,默认值（2）,2.用单独的命令来创建Default当多个表中的列，它们的缺省值相同时，这种方式很有用。步骤：1）CREATE DEFAULT 缺省名 AS 缺省值 让某个默认名的值为默认值。2）sp_bindefault 默认名,表名.列名 将该

21、默认名绑定到表中某个列上。例子：设定一个“全局”缺省值，可多次赋值（绑定）CREATE DEFAULT dft_state AS CAsp_bindefault dft_state,publishers.state要求列名的类型与缺省值相同。绑定了缺省值后,并不会对绑定缺省值之前表中已存在的值产生影响,而只对绑定之后的值产生影响。,数据库原理及设计 Dr.Yan Zhu,39,取消缺省,取消某列的缺省时，按下列步骤：sp_unbindefault 表名.列名-取消绑定DROP DEFAULT 缺省名-删除缺省注意：应首先将默认值从绑定的列上都摘除之后，再删除缺省，否则删除不会成功。,数据库原理

22、及设计 Dr.Yan Zhu,40,域约束规则（1）,概念：针对表中的某一列，指明某列的取值范围。在更新该列值时,RDBMS首先要检查是否在该规则规定的范围内。设定步骤：1）CREATE RULE 规则名 AS 规则2）sp_bindrule 规则名，表名.列名 例如：CREATE RULE state_rule AS state IN(CA,CO,WA)sp_bindrule state_rule,publishers.state规则可用IN()，BETWEEN，AND，关系式、=、=和 LIKE 操作符描述。创建规则时，应注意AS后有一个以开头的临时变量。,数据库原理及设计 Dr.Yan

23、Zhu,41,域约束规则（2）,解除规则绑定：sp_unbindrule 表名.列名 删除规则：DROP RULE 规则名例子：CREATE TABLE publishers(pub_id char(4)NOT NULL,pub_name varchar(40)NULL,cityvarchar(20)DEFAULT Pasadena,statechar(2)CREATE RULE state_rule AS state IN(CA,CO,WA)sp_bindrule state_rule,publishers.state,数据库原理及设计 Dr.Yan Zhu,42,规则作用的验证,INSER

24、T publishers VALUES(0002,MIT Press,San Francisco,IL)由于插入的state列的值“IL”不在该列所绑定规则规定的范围之内，因此，SQL Server会返回错误信息，拒绝本信息的插入。如果将IL改为CA，则该语句可成功执行。,数据库原理及设计 Dr.Yan Zhu,43,扩展用法,先创建好用户定义类型，然后将创建好的缺省和规则绑定到用户定义类型上 绑定：sp_bindrule 规则名，用户定义类型 sp_bindefault 缺省名，用户定义类型摘除：sp_unbindefault 用户定义类型 sp_unbinderule 用户定义类型查看创建

25、规则和缺省的过程：sp_helptext 规则名或缺省名规则检查属性的取值范围。默认值提供属性在没有设定值时的缺省。默认值从属于规则，当默认与规则都有时，必须同时满足。一般，绑定一个新规则或默认时，应先摘除旧规则或旧默认。如果没有摘除，则自动用新规则或新默认替换旧的。,数据库原理及设计 Dr.Yan Zhu,44,SQL Server 检查约束（1）,概念：类似于规则，要求用户插入到列或表中的数据满足限制条件。分为列级检查(针对表中一列）和表级检查（针对同一表中多列）。列级检查限制用IN、BETWEEN、AND或LIKE表达，例如：CREATE TABLE publishers(pub_id

26、char(4)NOT NULL,CONSTRAINT pub_id_constraint CHECK(pub_id IN(234,3344,564)OR pub_id LIKE 430-90-9),city varchar(20)NULL,state char(2)NULL),数据库原理及设计 Dr.Yan Zhu,45,检查约束（2）,表级检查限制，例如：CREATE TABLE discounts(discounttype varchar(40)NOT NULL,store_id char(4)NULL,lowqtysmallint NULL,highqtysmallint NULL,di

27、scount float NOT NULL,CONSTRAINT low_high_check CHECK(lowqty highqty)列级检查限制可用表级方式写，表级限制也能用列级方式写，但要求在第一个列定义后。缺省值须满足检查限制要求；,数据库原理及设计 Dr.Yan Zhu,46,检查约束与规则的比较（1）,检查约束和规则都可以对一个表中的列的值进行约束。例如：创建一个雇员关系。要求部门编号的值必须在10-100之内，雇员的职务只能为下列之一：“Sales”、“Mgr”、“Clerk”检查约束：CREATE TABLE emp(id smallint NOT NULL,name var

28、char(9),deptNo smallint，CONSTRAINT dept_constraint CHECK(deptNo BETWEEN 10 AND 100），job char(5)，CONSTRAINT job_constraint CHECK(job IN（Sales,Mgr,Clerk）,数据库原理及设计 Dr.Yan Zhu,47,检查约束与规则比较（2）,规则：CREATE TABLE emp(id smallint NOT NULL,name varchar(9),dept smallint，job char(5)）CREATE RULE dept_rule AS dept

29、 BETWEEN 10 AND 100Sp_bindrule dept_rule,emp.deptCREATE RULE job_rule AS job IN（Sales,Mgr,Clerk)Sp_bindrule job_rule,emp.job完成同一个功能可以使用“检查约束和”规则“两种方法。注意两者不同的语法形式。,数据库原理及设计 Dr.Yan Zhu,48,主键约束（1）,主键不能出现空值，且所有的值都是唯一的。在定义了主键限制后，系统自动为该表生成一个聚簇（Clustered）索引。定义方式分为列级检查和表级检查。列级针对表中一列。列级主键约束：CREATE TABLE publ

30、ishers(pub_idchar(4)PRIMARY KEY,pub_namechar(30),cityvarchar(20)NULL,statechar(2)NULL),数据库原理及设计 Dr.Yan Zhu,49,主键约束（2）,表级主键约束：CREATE TABLE sales(stor_idchar(4)NOT NULL,datedatetimeNOT NULL,ord_numvarchar(20)NOT NULL,CONSTRAINT pk_sales_constr PRIMARY KEY(NONCLUSTERED)(stor_id,ord_num)pk_sales_constr为

31、该主键约束的名称，NONCLUSTERED表示非聚集索引。因为系统会自动生成聚集索引，但用户可以利用命令来更改。该例子中主键是复合键，是stor_id和 ord_num的组合。,数据库原理及设计 Dr.Yan Zhu,50,唯一约束（1）,主要是针对候选键的限制。在定义了惟一限制后，系统自动为该表生成一个非聚簇索引。用户在定义时也可改成聚簇索引。与主键约束的区别：所有值唯一，最多只能有一个空值。默认索引为非聚簇（NonClustered）索引。分为列级检查和表级检查。列级唯一限制：CREATE TABLE publishers(pub_idchar(4)UNIQUE,pub_namechar(

32、30)UNIQUE是定义唯一约束的命令关键字。,数据库原理及设计 Dr.Yan Zhu,51,唯一约束（）,表级唯一限制：CREATE TABLE sales(stor_id char(4)NOT NULL,ord_num varchar(20)NOT NULL,date datetimeNOT NULL,CONSTRAINT uq_sales_constr UNIQUE CLUSTERED(stor_id,ord_num)其中，uq_sales_constr是该唯一约束的名称，(stor_id,ord_num)构成一个候选键。上述命令的最后一句除了申明唯一约束，还定义了聚簇索引。,数据库原理

33、及设计 Dr.Yan Zhu,52,外键约束（）,维护表间完整性实际上是从两个方向上完成的。外键约束定义的条件：在含外键的表上定义；即是在从表上定义。定义外键限制的列必须是另一个表中的主键或候选键外键限制分类：列级和表级。列级针对表中一列，表级则针对同一表中多列。,数据库原理及设计 Dr.Yan Zhu,53,外键约束（）,列级外键约束定义：（例3-14）CREATE TABLE titles(title_id tid NOT NULL,title varchar(4)NULL,pub_id char(4)NULL CONSTRAINT pub_id_const REFERENCES publishers(pub_id)ON DELETE CASCADE ON UPDATE NO ACTION,notes varchar(23)NULL)定义了三个子约束：1。约束“从表主表”方向的参照完整