数据库原理与设计_陶宏才

数据库原理及设计PrincipleandDesignofDatabase主讲:朱焱博士yzhu@swjtu.edu.cnSIST,SWJTU2009.10数据库原理及设计Dr.YanZhu*数据库原理及设计Dr.YanZhu*学习目的和要求从概念化数据库设计进入逻辑数据库设计关系数据模型的基本概念和术语关系数据模型上的完整性约束关系代数关系运算第三章关系数据模型数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*DBMS无关DBMS有关数据库设计过程……概念模式内模式逻辑设计模式优化数据库数据库需求使用高级数据模型ERM使用特定的DBMS的数据模型物理DB设计数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*关系数据模型 数据模型的三要素,是本章学习的路标 静态数据结构;动态数据操作;完整性约束 这三要素体现在如下问题中: 如何 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 达数据本身？ 如何表达数据间的联系？ 有哪些动态数据操作？ 能表达哪些完整性约束？ 完整性约束如何设定？ 完整性约束的效果？ 如何进行关系模型的DB设计？数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*ER模式向关系数据模式逻辑映射 概念模式与逻辑模式的对应 ERSchema RDBSchema 数据结构 实体集+属性联系集+属性主键（候选键） ？？？ 数据操作 / ？ 约束 一般性约束（联系类型约束）键约束参与约束 ？数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*ER关系-表描述实体关系模式（relationalschema)的关键：基本表（basetable)，也简称关系表。表由“列”（Column）和“行”（Row）组成。行又叫元组（tuple），列又叫字段（field）。数据对象（实体集）用关系（表）来定义。ER中实体集的属性表中的列（字段、属性）；ER中实体集中的每个实体实例表中的行（元组、记录）。列行学生表 姓名 学号 D.O.B … PeterBach 2000101 5-10-1982 LisaBaumann 2000102 12-7-1982数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*ER关系-表描述联系 ER模式中联系是单独描述的。而在关系模式中，联系也可以用表来描述。例如，选课联系选课表。 表中有来自学生和课程两个实体的属性，而成绩是选课关联自身的描述属性。 列的取值范围称为域（Domain）。例如，成绩的范围一般是：0-100。学生选课表 姓名 学号 课程号 课程名 成绩 PeterBach 2000101 A1033 数据库 85 LisaBaumann 2000102 A1030 数据仓储化 91数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*关系模式、关系、主键、外键 关系模式（Schema）由关系名、和各个列表示。数学上表示为：R（A1，A2，…，An)。 关系实例：由表中的各行构成。常简称关系；行序不重要，而列序重要。每个元组的字段必须对应关系模式中的字段。（物理存储要求的） 关系实例表示：{<a1,a2,…,an>}，ai为属性Ai的值。 ER模型中的候选键关系中的候选键 ER模型中的主键关系中的主键(PrimaryKey,PK) 关系中的新概念-外键，描述表与表之间的关联。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*表与表的联系主键外键学生表班级表主键/候选键外键(ForeignKey,FK):A表中的某个属性（组）是B表中的候选键或主键，则称该属性（组）为A表的外键。要使B表中的某个属性成为A表的外键，必须确定该属性为B表的主键或候选键。如何指定？完整性约束的任务。 姓名 学号 D.O.B 班号 PeterBach 2000-101 5-10-1986 计算机200001 班号 班名 人数 … 计算机200001 计1班 50 …数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*关系模型术语 关系模式术语 一般术语 关系模式 记录类型 属性 字段 属性值 字段值 元组1 记录1数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*关系的性质 关系是一个集合。集合中的元素是元组，每个元组的属性数目应该相同。 关系是一种 规范 编程规范下载gsp规范下载钢格栅规范下载警徽规范下载建设厅规范下载 化了的二维表格，不是一般的二维表。它的性质是：关系中每一个属性值都是不可分解的；关系中不允许出现重复元组；关系是元组的集合，因此无行序；关系的列是有序的。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*逻辑映射之数据结构映射关系表表的主键/候选键表的列表和列表中的各行？ 概念模式与逻辑模式的对应 ER模型 关系数据模型 数据结构 实体集实体集属性联系集与属性实体的实例主键/候选键 ??？？？ 数据操作 / ？ 约束 联系类型约束键约束完全参与约束 ？数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*SQL语言 SQL发音为Sequel，现在趋向于发音为S-Q-L。 最早在IBMSystem-RRDBMS上使用的查询语言； 第一个标准由ANSI于1986年制订,称为SQL-86； 1992年推出了SQL-92，是大多数RDBMS支持的版本； 1999年提出SQL:1999(SQL3)，是SQL-92的扩展。 SQL在数据库界之外也受到重视。在软件 工程 路基工程安全技术交底工程项目施工成本控制工程量增项单年度零星工程技术标正投影法基本原理 、AI、万维网数据管理、等领域显示了潜力。 SQL成为国际标准后，由于各种类型的计算机和DBS都采用SQL作为其存取语言和标准接口，从而使数据库世界有可能链接为一个整体。前景非常好。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*几个重要的SQL语言命令 SQLDDL——针对表结构的操作 Create（创建） Drop（删除） Alter（修改） SQLDML——针对表中数据的操作 Insert（插入） Delete（删除） Update（修改） Select（查询）数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*关系模型中的数据操作 动态的数据操作：增加、删除、修改和查询，简称：增删改查询。 增加：INSERTRVALUES（‘林海’，‘男’） 修改：UPDATERSETname=‘张湖’数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*逻辑映射之数据操作关系表表的主键/候选键表的列表和列表中的各行使用SQL语句对表进行操作？ 概念模型与逻辑模型的对应 ER模型 关系数据模型 数据结构 实体集实体集属性联系集与属性实体的实例主键/候选键 ??？？？ 数据操作 / ？ 约束 联系类型约束键约束完全参与约束 ？数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*关系模型上完整性约束 完整性约束（IntegrityConstraints,ICs）可以帮助阻止非法数据的输入。 它要求存入DB的数据应满足一些条件。 在定义一个关系模式的同时，或之后定义完整性约束。 当表中数据发生变化（如Insert、Delete、Update）时，DBMS即检查更新的数据是否满足完整性约束指定的条件。 几种完整性约束：域约束、主键约束、唯一约束、外键约束等。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*几种完整性约束（1） 域限制（DomainConstraint） 每个属性A的值必须是来自域dom（A）的原子值。 例如，表中某列的数据类型为“整数型”，那么该列的各记录值就不能是“字符串”。 不必显示指定。 主键约束（PrimaryKeyConstraint） 概念：针对主键而言，保证主键的完整性。 要求：主键值必须唯一，且不能为空值。 例如：作者不能作主键。需要显示指定。（如何指定，以后介绍）数据库原理及设计Dr.YanZhu*无法处理超大量数据（如GB级，TB级，PB级）证券、银行、保险等领域拥有超大量级数据。内寸不够，不能一次读入大量数据。32位计算机直接访问的地址为4GB；在32位机上Linux、WindowsNT、Windows2000等操作系统不允许硬盘上单个文件超过232=4GB大小。大数据量下的查询速度存在问题数据库原理及设计Dr.YanZhu*几种完整性约束（2）唯一约束（UniqueConstraint）主键可以用主键约束来保证其值的完整性。因为候选键也能唯一标识关系实例元组，因此，保证候选键的完整性，需要唯一约束。要求值唯一，允许有一个且仅有一个空值。以上是“表本身”的完整性约束。 外键限制（ForeignKeyConstraint）也被称为“参照完整性约束”。 目的：用来维护表与表之间的数据一致性，即其中一张表的改动，可能要求另一张表要作出某些改动，以保持数据一致。为使DBMS能做这样的检查，则应指定这种涉及两个表的ICs，此即外键限制。数据库原理及设计Dr.YanZhu*唯一约束：只能有一个空值，多个空值=值不唯一。数据库原理及设计Dr.YanZhu*主表与从表主表：指外键在另一张表中作主/候选键的表。（例中的班级表）从表：指含有外键的表。（例中的学生表）班级表（主表）学生表（从表） 姓名 学号 D.O.B 班号 PeterBach 2000-101 5-10-1986 计算机200001 班号 班名 人数 … 计算机200001 计1班 50 … 计算机200010 计10班 45 …数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*外键约束的例子(主表从表）学生表（从表）班级表（主表） 姓名 学号 D.O.B 班号 PeterBach 2000-101 5-10-1986 计算机200001 班号 班名 人数 … 计算机200001 计1班 50 … 计算机200010 计10班 45 …数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*分析外键约束（主表从表）对主表进行三种操作，观察对表间完整性的影响：对主表中的主键进行操作插入：要求插入值满足主键限制即可，不影响其它表。修改：可能会影响与该主键相关的从表的外键值。当相应的外键值存在时，有两个策略可用：一是改变对应从表的所有外键值，使之与主键一致；其二是不允许修改主表中的主键值。删除：可能会影响与该主键相关的从表的外键值。若相应的外键值存在时，策略有二：一是不允许删除主表的主键值；二是级联删除从表中相应外键值所在的行。数据库原理及设计Dr.YanZhu*实际上，有四个策略可用：p97,但SQLServer只实现了两个。数据库原理及设计Dr.YanZhu*外键约束的例子(从表主表）主键学生表（从表）班级表（主表） 姓名 学号 D.O.B 班号 PeterBach 2000-101 5-10-1986 计算机200001 班号 班名 人数 … 计算机200001 计1班 50 …数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*分析外键约束（从表主表） 对从表中的外键操作 插入：要求插入的外键值应“参照”（Reference）主表中的主键值。 修改：要求修改的外键值“参照”主表中的主键值。 删除：不需要参照主表中的主键值。数据库原理及设计Dr.YanZhu*主表中的人数属性要修改。数据库原理及设计Dr.YanZhu*外键约束小结维护表间数据完整性从两个方向上完成，即： 主表从表：“主表”中的主键值在修改和删除时，“从表”中与该主键值相同的外键值可“级联”（CASCADE）修改和删除，或“禁止”（NOACTION）“主表”主键值的修改和删除。 从表主表：表示“从表”中的外键值在插入和修改时，其值应“参照”（Reference）“主表”中的主键值。数据库原理及设计Dr.YanZhu*父亲在银行办了主卡，给你办了从卡。卡号+密码是主键。1。父亲改密码，你的记忆中也要改密码（级联）。2。你不让你父亲改密码（禁止）而你在银行用副卡取钱时，要知道你父亲的帐户的最新密码是多少（参照）。数据库原理及设计Dr.YanZhu*表间数据完整性维护的实现 利用外键约束定义 对从表定义外键限制（或称参照完整性）完成主表和从表间两个方向的数据完整性；利用触发器 主表的触发器维护主表到从表方向的数据完整性，而从表的触发器维护从表到主表方向的参照完整性。 SQL-92标准只支持外键约束方式。如果使用触发器，必须检查所用的RDBMS是否支持。数据库原理及设计Dr.YanZhu*为什么在从表上定义？保护弱者的角度，从表是弱者，在其上定义，RDBMS不会忽略它。数据库原理及设计Dr.YanZhu*其他的约束–检查限制 域限制、主键限制、唯一限制和外键限制是关系数据模型中最基本的限制，大多数商用系统都支持他们。其他还有更一般的限制，如： 检查限制(CheckConstraint，亦称“表限制”)检查某一列值是否在某一取值范围之内；表中某几列是否满足指定的条件。 与单个表有关。 例如：中学生的年龄定义为两位整数，范围很大，用户可以写如下 规则 编码规则下载淘宝规则下载天猫规则下载麻将竞赛规则pdf麻将竞赛规则pdf ，将年龄限制在10-20岁之间 CHECK(AGEBETWEEN10AND20)数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*其他的约束性–断言限制 断言限制（Assertion）检查表中个别列、整个表或表与表之间是否满足指定的条件。与多个表有关，是关系之间的约束。 例如：希望限定计算机图书的作者一定是女性。这时，仅靠键约束、唯一约束和外键限制不能实现，因为这个要求涉及到两张表（关系）：Book，Author。可以写为：CreateAssertionNoManComputerAuthorCheck（Book.计算机类.作者∩Author.性别为男性=空集) 与主键约束和唯一限制的定义不同，定义表约束和断言约束时要显式定义。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*完整性约束的实施 关系创建并指定了ICs后，当关系“更新”时（指插入、删除和修改）应实施检查。 对域限制、主键限制和唯一限制的实施 由于影响直接，故只要插入/删除/修改命令违背了限制，即被拒绝。而其它ICs（如一般性限制）的违背检查通常是在每个SQL语句之后。 对参照完整性（外键）限制的实施 外键限制的影响较复杂，要从两个方向分别进行，但不是同时进行。数据库原理及设计Dr.YanZhu*数据库原理及设计Dr.YanZhu*完整性约束小结 SQL中把完整性约束分成三大类： 域约束 基本表约束：主键约束；候选键约束（唯一约束）；外键约束;检查约束 表间约束:断言数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*逻辑映射之约束关系表表的主键/候选键表的列表和列表中的各行使用SQL语句对表进行操作域约束、主键约束、唯一约束、外键约束、检查约束、断言 概念模式与逻辑模式的对应 ER模型 关系数据模型 数据结构 实体集实体集属性联系集与属性实体的实例主键/候选键 ??？？？ 数据操作 / ？ 约束 联系类型约束键约束完全参与约束 ？数据库原理及设计Dr.YanZhu*数据独立性(DataIndependence)：指应用程序独立于数据的表示（逻辑）与存储（物理），通过将数据的定义与存储从程序中独立出来实现。高效数据访问(EfficientDataAccess)：DBMS利用许多复杂的技术来高效存储和检索数据，这对存于外部存储设备上的数据相当重要。数据完整性与安全性(DataIntegrityandSecurity)：DBMS通过数据的完整性约束：为每个数据项指定数据类型；规定数值项的值保持唯一性。或限制(IntegrityConstraints)、访问控制来完成。数据库原理及设计Dr.YanZhu*SQLServer对数据完整性约束的支持不同的数据库管理系统厂商开发不同类型的SQL，被称为数据库方言。例如：MicrosoftSQLServer产品中的SQL方言叫Transact-SQL。 T-SQL，即事务SQL，是对标准SQL的扩展。 T-SQL由SQL语句、函数和存储过程三部分组成。T-SQL除了具有标准的子语言：DDL，DML，DCL之外，还具有过程控制能力和事务控制能力。 T-SQL有两类变量： 局部变量：如@name,@price 全局变量(系统定义，用户只能引用，不能修改、定义）：如@@error,@@rowcount数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*SQLServer系统数据类型（一） pp179(pp139)表4-2 Tinyint,1byte,数值范围是0~28-1 精确小数是numeric(p,s)、decimal(p,s). 近似小数是float和real。 Datetime表示日期和时间。例如：03／29/200610:05:00:00:00PM数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*SQLServer系统数据类型（二） Char(n)和varchar(n) Char(10)可表示最多10个字符长的字符串,物理存储空间是10个字符,不能改变.如果输入字符少于10,实际存储仍然占满10个字符长,不足部分用前空格补足. varchar(10)则不同，10为最大字符串长度。实际输入是几个字符,存储时只占满几个字符,是可变的。 ‘1234567890’，‘1’，‘650’都是合法数据。但它们在上述两种数据类型中所用空间不同。 Nchar,Nvarchar,ntext是Unicode数据类型数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*SQLServer对完整性限制的支持 SQLServer中的数据完整性可粗分为两大类，如下表。 完整性分类 现有实现方式 备注 表本身的完整性 Default（缺省） 指定列的缺省值 Rule（规则） 指定列的取值范围 CheckConstraint（检查限制） SQL语句中有列级（即只涉及一列）和表级（涉及表中多列）两种定义方法。 PrimaryKey（主键限制） Unique（唯一限制） 表间的完整性 ForeignKey（外键限制） 参照完整性限制的体现，亦分列级与表级定义 Trigger（触发器） 可利用触发器来维护表间数据完整性数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*默认值（1） 默认值（default）：用于为列或用户自定义数据类型指定缺省值，每一列或自定义类型只能有一个缺省值。 当用户没有给指定有默认值的列输入数据时，RDBMS自动用该默认值代替。 两种方式：表定义时设定 CREATETABLEpublishers (pub_id char(4) NOTNULL, pub_namevarchar(40) NULL, city varchar(20) DEFAULT‘Pasadena’, state char(2) DEFAULT‘CA’)数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*默认值验证 CREATEtable是创建表命令。 在SQLServer中，对字符型数据须用单引号括起来。 利用两条SQL语句来验证默认的作用与效果。 INSERTpublishers(pub_id)VALUES(‘0001’) SELECT*FROMpublisherspublishers 属性 Pub_id Pub_name city state 元组1 0001 NULL Pasadena CA数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*默认值（2）2.用单独的命令来创建Default 当多个表中的列，它们的缺省值相同时，这种方式很有用。步骤：1）CREATEDEFAULT缺省名AS缺省值让某个默认名的值为默认值。2）sp_bindefault‘默认名’,‘表名.列名’将该默认名绑定到表中某个列上。 例子：设定一个“全局”缺省值，可多次赋值（绑定）CREATEDEFAULTdft_stateAS‘CA’sp_bindefault‘dft_state’,‘publishers.state’＃要求列名的类型与缺省值相同。绑定了缺省值后,并不会对绑定缺省值之前表中已存在的值产生影响,而只对绑定之后的值产生影响。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*取消缺省取消某列的缺省时，按下列步骤：sp_unbindefault‘表名.列名’----取消绑定DROPDEFAULT缺省名----删除缺省注意：应首先将默认值从绑定的列上都摘除之后，再删除缺省，否则删除不会成功。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*域约束——规则（1） 概念：针对表中的某一列，指明某列的取值范围。在更新该列值时,RDBMS首先要检查是否在该规则规定的范围内。 设定步骤：1）CREATERULE规则名AS规则2）sp_bindrule规则名，’表名.列名’ 例如： CREATERULEstate_ruleAS@stateIN(‘CA’,’CO’,’WA’) sp_bindrulestate_rule,‘publishers.state’ 规则可用IN(…)，BETWEEN…，AND…，关系式＜、＞、<=、>=、<>、=和LIKE操作符描述。 创建规则时，应注意AS后有一个以@开头的临时变量。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*域约束——规则（2）解除规则绑定：sp_unbindrule‘表名.列名’删除规则：DROPRULE规则名例子：CREATETABLEpublishers(pub_idchar(4) NOTNULL,pub_namevarchar(40) NULL,city varchar(20) DEFAULT‘Pasadena’,state char(2) )CREATERULEstate_ruleAS@stateIN(‘CA’,’CO’,’WA’)sp_bindrulestate_rule,‘publishers.state’数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*规则作用的验证INSERTpublishersVALUES(‘0002’,’MITPress’,’SanFrancisco’,’IL’) 由于插入的state列的值“IL”不在该列所绑定规则规定的范围之内，因此，SQLServer会返回错误信息，拒绝本信息的插入。如果将‘IL’改为‘CA’，则该语句可成功执行。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*扩展用法先创建好用户定义类型，然后将创建好的缺省和规则绑定到用户定义类型上绑定：sp_bindrule规则名，用户定义类型 sp_bindefault缺省名，用户定义类型摘除：sp_unbindefault用户定义类型sp_unbinderule用户定义类型查看创建规则和缺省的过程：sp_helptext规则名或缺省名规则检查属性的取值范围。默认值提供属性在没有设定值时的缺省。默认值从属于规则，当默认与规则都有时，必须同时满足。一般，绑定一个新规则或默认时，应先摘除旧规则或旧默认。如果没有摘除，则自动用新规则或新默认替换旧的。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*SQLServer检查约束（1）概念：类似于规则，要求用户插入到列或表中的数据满足限制条件。分为列级检查(针对表中一列）和表级检查（针对同一表中多列）。列级检查限制用IN、BETWEEN、AND或LIKE表达，例如：CREATETABLEpublishers(pub_id char(4) NOTNULL,CONSTRAINTpub_id_constraintCHECK(pub_idIN(‘234’,‘3344’,‘564’)ORpub_idLIKE‘43[0-9][0-9]’),city varchar(20) NULL,state char(2) NULL)数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*检查约束（2）表级检查限制，例如：CREATETABLEdiscounts (discounttypevarchar(40) NOTNULL,store_id char(4) NULL, lowqty smallint NULL, highqty smallint NULL, discount float NOTNULL, CONSTRAINTlow_high_check CHECK(lowqty＜＝highqty))  列级检查限制可用表级方式写，表级限制也能用列级方式写，但要求在第一个列定义后。 缺省值须满足检查限制要求；数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*检查约束与规则的比较（1）检查约束和规则都可以对一个表中的列的值进行约束。例如：创建一个雇员关系。要求部门编号的值必须在10-100之内，雇员的职务只能为下列之一：“Sales”、“Mgr”、“Clerk”检查约束：CREATETABLEemp(idsmallintNOTNULL,namevarchar(9),deptNosmallint，CONSTRAINTdept_constraintCHECK(deptNoBETWEEN10AND100），jobchar(5)，CONSTRAINTjob_constraintCHECK(jobIN（‘Sales’,’’Mgr’,’Clerk’）））数据库原理及设计Dr.YanZhu*列级检查约束数据库原理及设计Dr.YanZhu*检查约束与规则比较（2）规则：CREATETABLEemp(idsmallintNOTNULL,namevarchar(9),deptsmallint，jobchar(5)）CREATERULEdept_ruleAS@deptBETWEEN10AND100Sp_bindruledept_rule,‘emp.dept’CREATERULEjob_ruleAS@jobIN（‘Sales’,’’Mgr’,’Clerk’)Sp_bindrulejob_rule,‘emp.job’完成同一个功能可以使用“检查约束和”规则“两种方法。注意两者不同的语法形式。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*主键约束（1）主键不能出现空值，且所有的值都是唯一的。在定义了主键限制后，系统自动为该表生成一个聚簇（Clustered）索引。定义方式分为列级检查和表级检查。列级针对表中一列。列级主键约束：CREATETABLEpublishers(pub_id char(4) PRIMARYKEY, pub_name char(30), city varchar(20) NULL, state char(2) NULL)数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*主键约束（2）表级主键约束：CREATETABLEsales(stor_id char(4) NOTNULL,date datetime NOTNULL,ord_num varchar(20) NOTNULL,CONSTRAINTpk_sales_constrPRIMARYKEY(NONCLUSTERED)(stor_id,　ord_num))pk_sales_constr为该主键约束的名称，NONCLUSTERED表示非聚集索引。因为系统会自动生成聚集索引，但用户可以利用命令来更改。该例子中主键是复合键，是stor_id和ord_num的组合。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*唯一约束（1）主要是针对候选键的限制。在定义了惟一限制后，系统自动为该表生成一个非聚簇索引。用户在定义时也可改成聚簇索引。与主键约束的区别：所有值唯一，最多只能有一个空值。默认索引为非聚簇（NonClustered）索引。分为列级检查和表级检查。列级唯一限制：CREATETABLEpublishers(pub_id char(4) UNIQUE, pub_name char(30))UNIQUE是定义唯一约束的命令关键字。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*唯一约束（２）表级唯一限制：CREATETABLEsales(stor_id char(4) NOTNULL,ord_numvarchar(20) NOTNULL,date datetime NOTNULL,CONSTRAINTuq_sales_constrUNIQUECLUSTERED(stor_id,　ord_num))其中，uq_sales_constr是该唯一约束的名称，(stor_id,ord_num)构成一个候选键。上述命令的最后一句除了申明唯一约束，还定义了聚簇索引。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*外键约束（１）维护表间完整性实际上是从两个方向上完成的。 外键约束定义的条件： 在含外键的表上定义；即是在从表上定义。 定义外键限制的列必须是另一个表中的主键或候选键 外键限制分类：列级和表级。列级针对表中一列，表级则针对同一表中多列。数据库原理及设计Dr.YanZhuSHAPE\*MERGEFORMATCASCADEUPDATEDELETE从表NOACTION主表可能影响SHAPE\*MERGEFORMATINSERTUPDATE主表REFERENCE从表数据库原理及设计Dr.YanZhu*外键约束（２）列级外键约束定义：（例3-14）CREATETABLEtitles(title_id tid NOTNULL,titlevarchar(4) NULL,pub_id char(4) NULLCONSTRAINTpub_id_constREFERENCES publishers(pub_id)ONDELETECASCADEONUPDATENOACTION,notes varchar(23) NULL) 定义了三个子约束： 1。约束“从表→主表”方向的参照完整性； 2。约束“主表(删除主键）→从表”的数据完整性。 3。约束“主表(修改主键）→从表”的数据完整性。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*外键约束（３）表级外键限制：参看p107例３－１５。外键约束定义中的行动，表示当对主表的主键值做某种操作（删除或修改时），可采取的处理策略有二种：CASCADE（针对从表的策略）NoAction（针对主表的策略）SQL-92定义有四种策略，分别是：Cascade,NoAction,SetNull和SetDefault。但SQLSERVER和SYBASE只支持以上两种。如果不定义行动，则缺省的处理为NOACTION。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*　触发器（1） 利用主表和从表的触发器，也可以保持主/从表间的数据完整一致性。publishers表的主键是pub_id，它也出现在titles表中，即titles表中的pub_id是外键，也就是说，publishers为主表，而titles则为从表。要维护两表间的完整性，可对titles用外键限制或参照限制来维护它们间的完整性，或对publishers和titles用触发器来维护它们间的完整性。 触发器的创建：CREATETRIGGER触发器名ON表名FOR{INSERT|UPDATE|DELETE}[，{INSERT|UPDATE|DELETE}]…AS SQL语句组RETURN数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*　触发器（2） 触发器不带参数，不被用户和程序调用，只能由用户对DB中的表进行操作时触发（即：由操作激发）。一个表中最多有三个触发器，即：插入、修改和删除，分别对应各自的操作，例如：插入操作触发“插入”触发器。主表和从表应分别建立各自的触发器，主表的触发器维护主表到从表方向的数据完整性，而从表的触发器维护从表到主表方向的参照完整性。这里，主表和从表不需要定义外键，但必须有共同的列。沿用“外键约束”中的“主表和从表”，“主键和外键”的说法。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*　触发器（3）系统为触发器提供两张表，即：inserted和deleted。当操作为插入时，则新数据也会写入inserted表中；当操作为删除时，删除数据会保存在deleted表中；而当操作为修改时，会同时用到这两张表，先删后写。 由删除操作激发的主表触发器示例：CREATETRIGGERpub_delONpublishersFORDELETEAS IF@@rowcount=0 RETURN DELETEtitles FROMtitlest,deletedd WHEREt.pub_id=d.pub_id RETURN系统变量，表示publishers表中有几行记录被删除了。级联删除titles表中对应的行,类似于外键约束中的CASCADE行动.数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*　触发器（4） 查看触发器的创建过程: sp_helptext触发器名查看表依赖的触发器或触发器涉及的表:sp_depends表名或触发器名删除触发器:DROPTRIGGER触发器名注意不能对视图和临时表创建触发器;如果建立触发器的表被删除,那么其上的触发器将被自动删除。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*视图（1）视图是一个从其他表中导出的表，其他表可以是DB中的基表也可以是预先定义的视图。在数据库中并不存储视图（on-the-fly），因此又称它为“虚表”。用视图定义新的查询或视图，也可以通过视图来更新基表。但在更新视图时，应记住视图与基表的区别及对视图更新的限制。可以通过视图维护基表的安全性，例如：设定让用户只操作视图，而不能操作基表，来提高表中其他数据的安全性。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*视图（2）视图创建命令：CREATEVIEW视图名[属性列表] ASSELECT-FROM-WHERE语句例如：产生一张California州的作家表。CREATEVIEWca_authorsAS SELECTau_id,au_lname,au_fname,phone,state,contract FROMauthors WHEREstate=‘CA’查看视图：sp_help视图名查看视图的创建过程：sp_helptext视图名删除视图：DROPVIEW视图名数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*视图更新（1）视图更新（update，insert，delete）是很复杂的操作，容易造成问题，因为视图上的修改都必须反映到基表中。如果视图属性中包含基表的主键（或其他候选键）仅在单个基本表上定义没有使用任何聚集函数的视图该视图是可更新的。因为，每个视图记录都可以在基表中有对应的记录。从多个表组合导出的视图是不能更新的。利用聚集函数定义的视图是不可更新的。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*视图更新（2）视图的数据插入：对视图插入一行也会在基表中插入一行。对基表中没有出现在视图中的列，以NULL代替。由于主键不能用NULL值，如果视图中没有主键，那么对视图的插入将被拒绝。如果插入的数据在基表中，而没有在视图中，SQL-92是允许的。例如，p115（89）,例3-25。如果要避免这样的插入，可使用WithCheckOption。书上的例3-26。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*ER模型映射到关系模型 实体映射为表： 直接映射。实体名即关系名。实体型中的每个属性对应表中的属性，实体型中的每个主键和候选键即是表的主键和候选键。 ER中复合属性的简单成员属性直接成为表的属性。如：姓名（现用名，曾用名)现用名，曾用名 如表一，表二。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*表一：教师表表二：课程 教师证号 姓名 性别 19880321 T.Neuer 女 20040201 A.Schwack 男 课程号 课程名称 3201243 数据结构 3241220 数据挖掘技术数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*1：1联系映射为单独的表主持大学表主持大学表教师（教师证号，姓名，性别）大学（大学名称，大学地址） 教师证号 大学名称 任期 19880321 SWJTU 4 19770201 XY-Uni. 4 教师证号 大学名称 任期 19880321 SWJTU 4 19770201 XY-Uni. 4数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*1：N联系映射为单独的表教师（教师证号，姓名，性别）大学（大学名称，大学地址）工作 教师证号 大学名称 工作起始日期 19880321 SWJTU 4 19770201 XY-Univ. 4数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*M：N联系映射为单独的表教师（教师证号，姓名，性别）课程（课程号，课程名称）讲授 教师证号 课程号 19880321 SWJTU 19770201 XY-Univ.数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*联系的转换–减少表数量方式（1）实体之间的联系有1：1，1：n,m:n三种，采取的策略是不一样的。（1）1：1的转换 两个实体分别转换成两个关系模式 可在任意一个关系的属性中加入另一个关系的键和联系类型的属性。 如表三。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*表三：大学关系表 大学名称 大学校长工作证号 大学地址 任期 西南交通大学 19760201 四川、成都 2005-2007数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*联系的转换–减少表数量方式（2）（2）1：n的联系类型转换 两个实体转换成两个关系模式 将联系合并到n端实体中。即在n端实体属性中加入1端的关系的键和联系类型的属性。 如表四。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*表四：教师工作表 教师证号 姓名 性别 大学名称 工作起始日期 19800201 A.Schwack 男 TUDarm 1980.3.22数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*联系的转换–减少表数量方式（3）（3）M：N联系类型转换 两个实体转换成两个关系。 m:n联系不能由一个实体的主键唯一识别，必须由所关联实体的主键共同识别。 将联系转换为一个独立的关系。 联系的属性、两个实体的主键都成为这个独立关系的属性。 如表五。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*表五：讲授关系 教师证号 课程号 20040201 3201243 20040201 3241220 19880321 3241220数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu* ER–联系类型约束 关系模型–表 1：1键约束 参与实体 关系表 联系联系 独立的关系表一端实体的主键和联系属性移到另一端实体的关系表中 1：n键约束 参与实体 关系表 联系联系 独立的关系表“1”端实体的主键和联系属性移动到“n”端实体的关系表中 m:n无键约束 参与实体 关系表 联系 独立的关系表，由参与实体的主键、联系的属性共同构成。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*ER模型的转换实例 八个实体类型 七个联系类型数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*实体和联系的属性 旅游线路（路线号，起点，终点） 旅游班次（班次号，出发日期，回程日期，报价） 旅游团（团号，团名，人数） 游客（游客编号，姓名，年龄，性别） 导游（导游编号，姓名，年龄，性别，职称） 交通工具（交通工具编号） 宾馆（宾馆编号，宾馆名，星级，标准房价） 保险单（保险单编号，保险费，投保日期） 开设（旅游班次季节时段） 食宿（团体订单折扣）数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*ER模型的转换实例分析13451179 旅游线路（路线号，起点，终点） 旅游班次（班次号，出发日期，回程日期，报价） 开设（旅游班次季节时段） 游客（游客编号，姓名，年龄，性别）旅游团（团号，团名，人数）导游（导游编号，姓名，年龄，性别，职称）交通工具（交通工具编号）宾馆（宾馆编号，宾馆名，星级，标准房价）保险单（保险单编号，保险费，投保日期）286食宿（团体订单折扣）10数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*转换后的关系模式旅游线路（路线号，起点，终点）旅游班次（班次号，出发日期，回程日期，报价，路线号,旅游班次季节时段）交通工具（交通工具编号）旅游团（团号，团名，人数,旅游班次号）游客（游客编号，姓名，年龄，性别,团号）导游（导游编号，姓名，年龄，性别，职称）宾馆（宾馆编号，宾馆名，星级，标准房价）安全保险（保险单编号，保险费，投保日期,团号） 食宿（旅游班次号，宾馆编号，团体订单折扣） 载运（旅游班次号，交通工具编号） 陪同（团号，导游编号）数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*请为一家IT培训的公司建立一个概念数据模型以满足该公司的数据需求。公司有:教师（教师编号、姓名、性别、年龄、职称)；学员（学员编号、姓名、年龄、单位）；培训课程（课程编号、名称、学时）；教师应分属各教研组（组号、组名）；学员分属各培训班（班号、班名、人数）；实体间的联系及它们的属性有：教师属于教研组，一个组可有多名教师，但一个教师只属一个教研组；学员属于培训班，一个班可有多名学员，但一个学员只属一个培训班；学员学习课程，为多对多联系，含成绩属性；教师为培训班讲授课程，为m:n:k联系，含讲授年度属性。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*请画出ER图，要求标出实体的主键、联系的约束类型和键约束数据库原理及设计Dr.YanZhu属于nk讲授课程编号年度n组名学习课程n年龄学员编号教师编号nm1名称教研组组号年龄性别姓名培训班教师属于m1姓名学时学员班号人数班名成绩单位职称数据库原理及设计Dr.YanZhu*将此ER图转换为关系模型，要求标出各关系的主键，如果存在的话还应指明外键。 教研组（组号，组名）。 培训班（班号，班名，人数）。 教师（教师编号，姓名，性别，年龄，职称，组号），组号为外键。 学员（学员编号，姓名，年龄，单位，班号），班号为外键。 课程（课程编号，名称，学时）。 讲授（教师编号，班号，课程编号，年度），教师编号、班号和课程编号均各为外键。 学习（课程编号，学员编号，成绩），课程编号和学员编号均各为外键。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*关系数据库模型关系（表），字段（列），元组（行），域，主键，外键关系模式的性质完整性约束域约束、主键约束、唯一约束、外键约束SQLServer中的数据完整性约束视图ER模型向关系数据库模型的转换关系代数五个基本操作（选择、投影、集合并、几何差，集合积）自然联结关系代数式的构成SPJ操作，关系代数的优化本章已学内容与后续内容一览数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*关系代数及关系运算关系代数：RelationalAlgebra关系运算：RelationalCalculus关系代数和关系运算是数据库的理论基础书中称它们为查询语言，只关注查询。区别：关系代数中的查询着重“过程”，故由“操作符”集合组成，是“过程化”的查询；而关系运算中的查询侧重“结果”，故主要描述结果应满足的“条件”，是“非过程化”的查询。查询的输入输出均是“关系”，即输入为关系，结果输出仍为关系。因此，可以组合查询。数据库原理及设计Dr.YanZhu数据库原理及设计Dr.YanZhu*关系代数基本的代数表达式：由基本的代数操作符（Operator）对操作数（Operand）进行一元或二元的操作。基本的操作符：SELECTION（选择)PROJECTION(投影)UNION(并)INTERSECTION(交)DIFFERENCE(差)CROSS-PRODUCT(积，笛卡尔积)操作数则是关系实例。关系查询时，既可用列（属性）