数据库设计
一、数据库的设计：
1、概念
2、基本任务,根据一个单位的信息需求、处理
需求和数据库的支撑环境，设计
出数据模式及典型的应用程序
3、特点：
?数据库设计是软、硬件和干件三者的结合。
?数据库设计应和应用系统的设计结合。
4、设计的方法：
5、设计的步骤：共六步
信息需求 处理需求安全
、
完
整
性
需
求
运行和维护
数据库的实施
物理设计
需求分析
概念设计
逻辑设计
1) DFD,DD
2) E-R
3) 建数据模式，
4) 化为规范化数据模式
5) 建表、视图和定义索引
及相关约束
6) 完整性的实现（定义主
键及触发器
7) 建立一些存储过程
二、需求分析
（一）调查方法：
?查询文档
?询问（谈话）
?跟班作业（观察业务运转）
?问卷调查
?专人介绍
?开调查会
（二） DFD及 DD
1,DFD,表达数据和处理过程的关系
1) 图元：
?源目的
?处理
?存储
?数据流
实例,1、银行 ATM系统
2、考试处理系统
3、教材科教材管理系统
2,DD（ 数据字典）,各类数据描述的集合。
数据项
数据结构
数据流
数据存储
数据处理过程
数据项描述
数据项 含义说明 别名 类型 长度 取值范围 取值含义
教材编
号
唯一标识每
种教材
字符型 12 0-9,A-Z 教材分类号 D3
出版社号 D4
版次 D1
教材号 D4
数据结构描述
数据结构 组成 含义说明
订 /购书单位 单位编号、单位名、购书类型 订或购书的单位或个人
数据存储
数据
存储
说明 流入数
据流
流出数
据流
组成 数据量 存储
方式
用书计
划
每个订书
单位制订
的用书计
划
录入 输出到汇
总平衡
计划号、用
书单位号、
计划日期、
教材号、数
量
根据各单
位的订书
情况而订
随机存
储
数据流 说明 来源 去向 组成 平均流量 高峰期流量
到书信
息
教材入库流 管理
员录
入
写入到
书明细
文件中
到书明
细表结
构
数据流描述
数据处理描述
处理过程名 输入 输出 处理
产生领书单 购书信息 领书单 根据购书信息检查书数量是否够，够
的话打出领书单，并写入领书明细，
否则给予提示
三、概念设计
（一）方法：
?自顶向下
?自底向上
?逐步扩张
?混合策略
（二）自底向上的设计步骤
1,数据抽象、设计局部 E-R图
2,集成局部 E-R图
（三）数据抽象、设计局部 E-R图
?数据抽象：分类、聚集、概括
?局部 E-R图的设计
（四 ） 局部 E-R图的集成
1、方法：
?多个局部 E-R图一次集成
?逐步集成，采用累加的方法一个集成两个 E-R图
2、局部 E-R图间的冲突：
属性冲突、命名冲突、结构冲突
3、解决结构冲突的调整手段：
?根据综合应用的需要，把属性转为实体或把实体
转为属性
?对同一个实体的属性取各个局部 E-R图相同实体
属性的并集
?实体联系要根据应用语义进行综合调整
三、逻辑设计
（一）初始关系模式的导出
1、实体集的转换规则：
一个实体型转为一个关系模式，实体的关
键字即为关系的关键字。
2、联系的转换规则：
1） 1,1联系：
2） 1,m联系：
选择任一实体的主键作为另一实
体的外键插入另一实体中。
a,把 1端关键键作为外键加入 m端
b,把联系单独作为一个关系，此关系的关键
字是多方实体的关键字
3） m,n联系,把联系单独转为一个关系。把 m,n端
的主关键字作为外键加入到该关系中，
并把他们作为该关系的复合主键。
（二）关系数据库的规范化理论
1、初始关系模式存在的问题：
sno sn sage ssex dept mn cn grade
03001 张华 20 女 计算机 韩冰 数据库 89
03001 张华 20 女 计算机 韩冰 数据结构 88
03002 李平 21 男 管理 李江 管理学 80
?数据冗余
?插入异常
?删除异常
?更新异常
2、解决方法：分解
3、函数依赖,反映属性间的对应约束关系
S(sno,sn,sage,ssex,dept_no)
Dept(dept_no,dept,mn)
Sc(sno,cn,grade)
1)函数依赖：
?
U={A1,A2,…,An}
R(A1,A2,…,An) X U,Y U, t1,t2∈ r
若 t1[X]=t2[X],则 t1[Y]=t2[Y],X→ Y
? ?
给定一个属性值 (组 )，可查到另一个属性值
X称为决定因素
2) 非平凡的函数依赖：
X→ Y,但 Y X
3) 平凡的函数依赖：
4) 完全函数依赖：
5) 部分函数依赖：
例 1：设有选课关系模式
S_C(SNO,CNO,GRADE,CREDIT),其中,SNO表
示学号,CNO表示课程号,GRADE表示成绩，
CREDIT表示学分。请找出此关系模式中的“完
全函数依赖”和“部分函数依赖”。
6) 传递函数依赖：
例 2：设有关系模式 S (SNO,SNAME,DNO，
DNAME,DADDRESS),其中,SNO表示学号，
SNAME 表示学生姓名,DNO表示系号,DNAME
表示系名称,DADDRESS 表示系所在地址。
4、函数依赖表示的方法
1) 依赖函数
2) 函数依赖图
3) 简单表示
5、码 1) 候选码、主码：
2) 主属性、非主属性：
3) 全码：
4) 外码：
例：找出下列关系模式的函数依赖 F,候选码、主码、
主属性及非主属性。
1) SJP（ S,J,P） S,学生 J,课程 P,名次
2) T（ C,T,B） C,课程 T,教师 B,参考书
3) WPE（ WNO,PNO,ENO,QNT） WNO,仓库
号 PNO,配件号 ENO,职工号 QNT,数量
候选码可决定整个元组。
（三）范式：关系满足的要求。 R∈ XNF
1、基本概念：规范化
低 高模式分解
2,1NF,关系
1) 元组无重复
2) 属性不可分
例：职工号、姓名、电话（家里电话、办公电话）
3,2NF
1) R∈ 1NF
2) 非主属性完全依赖于码
S_C(SNO,CNO,GRADE,CREDIT)
4,3NF
1) R∈ 2NF
2) 非主属性对任一候选码无传递依赖
5,BCNF
1) R∈ 3NF
2) 决定因素必为码
1) 定义：
2) 问题：
6、多值依赖,X Y
7,4NF,不存在多值依赖
S(SNO,SN,DNO,DNAME,LOCATION)
WPE（ WNO,PNO,ENO,QNT）
T（ C,T,B）
SID MAJOR ACTIVITY
100 MUSIC SWIMMING
100 ACCOUNTING SWIMMING
100 MUSIC TENNIS
150 MATH JOGGING
Ｓ（ SID,MAJOR,ACTIVITY）
SID,学生 MAJOR,专业课程 ACTIVITY,活动
范式小结
1NF
2NF
3NF
BCNF
4NF
非主属性完全依赖候选码
非主属性对候选码无传递依赖
决定因素必为候选码
无多值依赖
习题
写出基本函数依赖和候选码，并把相应关系模
式规范为 3NF
1,学号 姓名 系 系主任 成绩 课程
2,学号、姓名、性别、专业、年级、成绩（课
程号、课程名、学时、学分，教师、工资号、
成绩）
数据依赖公理系统
一、函数逻辑蕴含：
即由一组已知的函数依赖，去判断另一些函
数依赖是否成立或能否从前者推出后者。
二,Armstrong公理系统：
1,自反：
2,增广：
3,传递：
X→Y XZ→YZ
X→Y
Y→Z X→Z
X→Y X→XY
三、推导规则：
1,合并规则：
2,伪传递规则：
3,分解规则：
X→Y
X→Z X→YZ
X→Y
WY→Z XW→Z
X→Y
Z Y X→Z?
四,F的闭包
1,定义,F中所蕴含的函数依赖全体。
2,XF＋, X关于函数依赖集的闭包。（ X为决定因素）
例 1,R（ A,B,C） F＝｛ A → B, B → C ｝ 求 XF＋
3,XF＋ 求解算法：
输入,X,F
输出,XF＋
处理：
1) 置 Z为空,Z＊ ＝ X
2) 判断 Z与 Z＊ 是否相等
3) 若等则 XF＋ ＝ Z＊, 算法终止
4) 若不等，则 Z＝ Z＊
5) B：以 Z中属性作为决定因素的 F的闭包
6) Z＊ ＝ Z∪ B,转到 2）
例 2 P185
五、等价定义：
六,Fm
例 3：有三个函数依赖，分别指出它们是否为最小覆盖
F1＝｛ AB→CD,B→EC,C→G ｝
F2＝｛ A→D,B→A,A→C,B→D,D→C ｝
F3＝｛ A→D,AC→B,D→C,C→A ｝
例 4,F＝｛ A→B,B→A,B→C,A→C,C→A ｝ 求 Fm
结论：同一函数依赖集的最小函数依赖集不唯一。
例 5,F＝｛ C→A,A→B,B→C,C→B,A→C,BC→A ｝ 求 Fm
Fm与各属性的处置顺序相关。
模式分解
一、基本概念：具有无损连接，保持函数依赖
二、判定分解的无损连接
（一）记号的定义,ρ＝｛ R1<U1,F1>… RK<UK,FK>｝
是 R<U,F>的一个分解
（二）判定步骤：,R<U,F>,ρ,U＝｛ A1 … Ai｝
1,求 Fm
2,初始化表格
A1 … Ai
R1<U1,F1>
…
RK<UK,FK>
3,初始化：若 Ai Uk,在 Ai与 Rk交叉处填 ak,否
则，填 bki
?
4,修改：逐一对函数依赖考察。
对 X → Y 考察，表中是否存在至少两行在 X上的
值相等。
若等，则看这些行在 Y上的值，若有一行在 Y上值
为 ai,那么在其他行中的 Y上的值都改为 ai。
否则改为行数最小的 bk
5,若表格在更改前后不变，则考察结束，否则重
新按新表考察。
无损连接的判定：若表格中出现一行全为 a,则为
无损连接。
例 1：
R<U,F>,U＝｛ A,B,C,D,E｝
F＝｛ AB → C, C → D,D → E ｝
ρ＝｛ R1(A,B,C),R2(C,D),R3(D,E)｝
例 2：
R<U,F>,U＝｛ A,B,C,D,E｝
F＝｛ AB → C, C → D,D → E ｝
ρ＝｛ R1(A,B),R2(C,D),R3(B,D,E)｝
三、判定保持函数依赖：等价
四、分解：用 Fm分解
例：关系模式 R＜ U,F＞,U＝｛ ABCDEFGHIJ｝
F＝｛ A → B, A → C, A → DE, D → E, DE → B,
AF → GHI, I → J, F → I ｝
1,求 Fm
2,求 F＋
3,R＜ U,F＞ 的候选码
4,分解 R＜ U,F＞,结果为 ρ1
5,验证 ρ1具有无损连接
6,判断分解后的是否为 BCNF
物理设计
一,物理设计步骤
二、物理设计的内容与方法
（一）关系模式存取方法的选择：
1,确定数据库的物理结构。
2,评价物理结构。
1,索引,B＋ 树结构
思考：表中的索引是否越多越好？
1) 说明：
? 一个表可有多个索引，但最多最好 2-3个
?数据在物理上随机存放在数据页上，每个键值项通
过指针指向包含该键值的数据行。
?叶层中不含数据页
2) 不宜建索引的条件：
?不出现或很少出现在查询条件中的属性
2.聚簇：
?一个表中中只允许一个聚簇。
?属性值很少的属性
?属性值分布不均
?常更新的属性
?过长的属性
?太小的表
3）组合索引：常同时存取多列，且每列都含重复值。
1）说明：
?结构上与索引相同。
?物理上按顺序放在数据页上，且重复值排在一起
?叶层含有数据页
2) 哪些需要建立聚簇？
? 主、外键
? 返回某范围的属性
? 聚集函数后的参数
? 查询时返回大量结果的列
4) 聚簇与索引的比较：
例,record(id,date,amount,place),其中记录
620000行。进行以下查询：
3) 注意：当有大量行插入时，不要在插入表中的
自动增加列上建立聚簇索引。
?Select date sum(amount) from record group by date
?Select count(*) from record where date=‘19990901’ and
place in(‘BJ’,’SH’)
3,HASH：
地址 01 02 … 22 …
年份 1949 1950 … 1970 …
人数 … 15000 …
用 hash表的存取方法进行存取。
关键字,H(KEY)=key-1948
4.SQL Server中的索引：
1）类型：聚集索引、非聚集索引
2）索引的建立：
?在企业管理器内建立
?通过查询分析器建立
?用 SQL语句建立
注意,
?建立一个主键约束或唯一约束时会自动建立索引
?若在建立主键约束前还示有聚集索引，则自动把此索引
设为聚集索引。
?在视图上建立非聚集索引前，视图上必已存在一个聚集
索引 。
Create [unique] [clustered |Nonclustered] Index Index_name
on table_name|view_name(column_name [,…n])
3)索引的删除：
?在企业管理器中删除
?在查询分析器中删除
?用 SQL语句
DROP Index
table_name.index_name|View_name.index_name[,…n]
（二 ） 确定数据库的存储结构
1,数据存放位置：
2,确定系统配置：
同时使用数据库的用户数、同时打开数据库的
个数、使用的缓冲区的大小和个数、时间片的大小、
数据库的大小、装填因子等等。
（三）评价物理结构：
补充,SQL SERVER 中几个常用系统表 (master数据库中 )
1,Syslogins:
2,Sysdatabase:
3,Sysusers:
4,Sysobjects:
5,Syscolumns:
6,Sysindex:
每个连接到 SQL SEVER的登录帐号存储
一条记录。
每个数据库存储一条记录
每个用户存储一条记录
数据库中每个对象存储一条记录
表中每一列存储一条记录
每个索引存储一条记录
Sysobjects:
Name:对象名
Id,对象 id
Xtype,对象类型
Uid,对象所有者 id
应用实例,
在库中 db1建表 table1,若表存在则删除后重建，
否则直接建立。
数据库实施和维护
一、创建数据库 。
运用数据库管理系统提供的命令创建数据的结构，将
数据库结构定义映像到内存中。
注意,
?表中字段名字尽量采用有意义的名字。
?仔细选用字段的类型。
?为外键建立索引，以提高效率。
?选主键时，不要选用手工输入或用户可编辑的字段，主
键最好无含义，能用自动增量类型的尽量使用。
?表中可多设一个放删除标记的字段。
二、数据库数据的载入和应用程序编码
三、数据库的试运行
四、数据库的运行和维护
（一）数据库的转储和恢复
例：银行转账, 从账号 a转 100元到账号 b:
1、事务,数据库应用程序的基本逻辑单元。
注意,事务与程序的关系
SQL,BEGIN TRANSACTION
COMMIT/ROLLBACK TRANSACTION
2,事务的 ACID性质：
原子性
一致性
隔离性
持续性
3,SQL SERVER中执行事务：
假设：银行数据库中存储用户账户信息表为：
account（ acid,acname,surplus,date）,用 SQL语
句实现上述事务。
4、在存储过程中使用事务
（二）并发控制
例：甲乙同时购买同一航班的机票，假设机
票余额为 A＝ 16
1、事务并发操作造成的问题：
?更新的丢失
?读“脏”数据（即过时的数据）
?不可重复读
（三）封锁（锁机制）
1、概念
2、锁的类型,X锁：S锁,资源由一个事务独占只允许一个事务可写
T1 T2 X S -
X N N Y
S N Y Y
- Y Y Y
注意,只有相容的锁才能在同一对象上同时实现。
例：用 X锁解决“更新丢失”的问题
对某对象加锁,X(S)LOCK 对象名
对某对象加锁,UNLOCK 对象名
（三）封锁协议：
1、概念：
2、三级锁协议：
?一级封锁协议
?二级封锁协议
?三级封锁协议
（四）死锁
1、产生：
TA TB
t0 Xlock A
t1 Xlock B
t2 Read(A)
t3 Read(B)
t4 Xlock B
t5 Wait Xlock A
t6 wait Wait
t7 wait wait
2、解决方法：
?预防：一次封锁和顺序封锁
?允许死锁，诊断有死锁时，杀一个事务来解除。
超时法
事务等待图法
（五）并发调度的可串行性
1、可串行化调度定义：
2、与串行调度的区别：
?可串行化调度是交叉执行各事务操作，但在效
果上相当于事务的某一串行执行（即两者结果
相同）
?串行调度完全串行执行各事务，没有并发的意义。
例：银行转账,TA从账号 C1转 100到,TB从 C1转 10%
到 C2
TA TB
Read(C1);
C1 = C1 -100;
Write(C1);
Read(C2);
C2 = C2 +100;
Write(C2 );
Read(C1);
var = C1 *0.1;
Write(C1);
Read(C2);
C2 = C2 +var;
Write(C2 );
(六 ) 两段锁协议：保证并发调度可串行化的协议
1、内容：
事务 第一段：扩展阶段第二段：收缩阶段 只能申请锁，不能释放锁只能释放锁，而不能申请
该协议是并发调度可串行化的充分不必要条件
2、与一次封锁法的关系
（七）封锁的粒度：封锁对象的大小
数据库的恢复
一、故障的分类
1、事务故障
2、系统故障
3、介质故障
4、计算机病毒
二、数据库的恢复：
（一）建立“冗余数据”技术
1、数据转储
2、日志
数据和数据库结构都损坏
数据损坏
通过冗余
（二）数据转储,数据库恢复的基本技术。
1、概念：建立数据库后备副本。此过程称为“数
据库的备份”
2、分类：
?静态、动态
例：每天夜里 12:00备份，设周二下午 3:00DB出故障。
（三）登记日志文件
1、内容：记录事务对 DB的更新操作。
?事务标识
?操作类型
?操作对象
?对象修改前的值
?对象改后的值
包括 INSERT,UPDATE,DELETE
?海量转储、增量转储
例：客户 C001公司地址由 NEW YORK迁到 LONDON
2、作用
3、如何写
?严格按照并发事务执行的次序写。
?先写日志后写库。
（三）恢复策略
1、利用日志进行恢复两种方法：
?REDO 正向扫描
?UNDO 反向扫描
2、事务故障的恢复
3、系统故障的恢复
1) 步骤：
?从日志文件头开始正向扫描日志文件到故障
发生点，已提交的事务放入 REDO队列中，未
完成的放入 UNDO队列
?对于 REDO中的事务 REDO
?对于 UNDO中的事务 UNDO
4、介质故障恢复
步骤：
?由 DBA利用后备副本重建 DB
?装入相应日志文件，通过系统运行事务恢复。
2) 具有检查点恢复技术,在日志中增加检查点记录
重建 DB 利用日志恢复 继续运行
DB转储 执行事务
登录日志 故障点T1 T2
（四） SQL SERVER DB恢复的实现
1,DB备份
?备份类型：
完全
差异
事务日志
文件和文件组
?说明,SQL SERVER 中“故障还原模型”影响
备份类型的选择。
?完全
?简单
?大容量日志记录 只能选择完全和差异备份
2,DB备份方法：
?用企业管理器
单击要备份的 DB→ 所有任务 → 备份 DB
?用 SQL,Backup database
Backup database database_name To
｛ logical_backup_device_name|{Disk|Tape}
=‘physical_backup_device_name’}
[WITH DIFFERENTIAL]
说明：
?logical_backup_device_name：
?physical_backup_device_name：
?WITH DIFFERENTIAL：
用户事先建立，用来存储备
份内容的逻辑设备名
包含绝对路径的物理文件名
实现差异备份
?备份日志,BACKUP LOG
3,SQL SERVER恢复
1) 企业管理器
1) SQL,RESTORE DATABASE
例,CAPDB在上午 11,00受破坏，而上次完全
备份在前一天，系统每隔一小时做一次事务日
志备份，存储完全备份设备名为 Backupdevice，
存储事务日志备份名为 Backuplog
步骤,
?从完全备份恢复，建立基线
?从事务日志恢复
数据库完整性
一、完整性的概念：
数据正确性和相容性。
二、完整性约束条件：
（一）作用对象：关系、元组、列
（二）分类：静态、动态
?静态列级约束
?静态元组约束
?静态关系约束
?动态列级约束
?动态元组约束
?动态关系约束
三、完整性控制
（一）完整性控制机制的功能：
?定义
?检查
?采取的动作
（二）实现（ SQL SERVER）
1、实体完整性,主键唯一,PRIMARY KEY
例：在 SCDB库中，建表 S,其中 SNO为主键。
2、参照完整性,建立外键,FOREIGN KEY
说明：
?定义外键时必须用 REFERENCES引用该键所引用
的主表主键名
?创建外键后，若用户修改主表中的主键值，
SQL SERVER可选两种方法做出反应：
1) NO ACTION,默认
若 FK所在表中存在与 PK中相等的记录，则产
生错误，回滚主表更新。
1) CASCADE,级联
若 FK所在表中存在与 PK中相等的记录，则修
改相应记录的值。
3、用户定义完整性
1) 建表时定义
?列值非空
?列值唯一
?检查
?规则
NOT NULL
UNIQUE
CHECK
RULE
例：建学生表 S,姓名非空，学号为主键，年龄
小于 35，性别只取“男”和“女”
2) RLUE对象的建立
a) 创建：
CREATE RULE rule_name As condition_expression
condition_expression,由算术运算符、关系运
算符、谓词等组成条件表达式，且还可包含由
＠开头的局部变量。
b) 绑定：
Sp_bindrule ‘rule_name’,’object_name’
c) 与 CHECK的比较
CHECK是列取值限定的首选方法，但 RULE
提供对不同表相同属性域的列进行相同限定
的快速方法
4,用触发器实现复杂的完整性约束
1) 触发器：是特殊的存储过程，由系统自动执行
2) 组成：事件、条件、动作
说明：
?触发器无法手动触发。
?不成功的事务会自动回滚
?一个触发器只能应用于一个单独的表或视图
3) 语法：
CREATE trigger trigger_name
On table_name or view_name
For trigger_type
As sql_statement
例 1：当向表中插入一条记录时,id号自动加 1
例 2：对教师表 T定义一规则：教授工资不低于
1000元，若低于 1000元，自动改为 1000
例 3：在图书管理系统中，有以下四个表：
borrow(bo_id,bo_bookno,bo_booknum)
return(re_id,re_bookno,re_booknum)
inbook(in_id,bookno,booknum)
bookstore(bookno,booksum,bookcur)
一、数据库的设计：
1、概念
2、基本任务,根据一个单位的信息需求、处理
需求和数据库的支撑环境，设计
出数据模式及典型的应用程序
3、特点：
?数据库设计是软、硬件和干件三者的结合。
?数据库设计应和应用系统的设计结合。
4、设计的方法：
5、设计的步骤：共六步
信息需求 处理需求安全
、
完
整
性
需
求
运行和维护
数据库的实施
物理设计
需求分析
概念设计
逻辑设计
1) DFD,DD
2) E-R
3) 建数据模式，
4) 化为规范化数据模式
5) 建表、视图和定义索引
及相关约束
6) 完整性的实现（定义主
键及触发器
7) 建立一些存储过程
二、需求分析
（一）调查方法：
?查询文档
?询问（谈话）
?跟班作业（观察业务运转）
?问卷调查
?专人介绍
?开调查会
（二） DFD及 DD
1,DFD,表达数据和处理过程的关系
1) 图元：
?源目的
?处理
?存储
?数据流
实例,1、银行 ATM系统
2、考试处理系统
3、教材科教材管理系统
2,DD（ 数据字典）,各类数据描述的集合。
数据项
数据结构
数据流
数据存储
数据处理过程
数据项描述
数据项 含义说明 别名 类型 长度 取值范围 取值含义
教材编
号
唯一标识每
种教材
字符型 12 0-9,A-Z 教材分类号 D3
出版社号 D4
版次 D1
教材号 D4
数据结构描述
数据结构 组成 含义说明
订 /购书单位 单位编号、单位名、购书类型 订或购书的单位或个人
数据存储
数据
存储
说明 流入数
据流
流出数
据流
组成 数据量 存储
方式
用书计
划
每个订书
单位制订
的用书计
划
录入 输出到汇
总平衡
计划号、用
书单位号、
计划日期、
教材号、数
量
根据各单
位的订书
情况而订
随机存
储
数据流 说明 来源 去向 组成 平均流量 高峰期流量
到书信
息
教材入库流 管理
员录
入
写入到
书明细
文件中
到书明
细表结
构
数据流描述
数据处理描述
处理过程名 输入 输出 处理
产生领书单 购书信息 领书单 根据购书信息检查书数量是否够，够
的话打出领书单，并写入领书明细，
否则给予提示
三、概念设计
（一）方法：
?自顶向下
?自底向上
?逐步扩张
?混合策略
（二）自底向上的设计步骤
1,数据抽象、设计局部 E-R图
2,集成局部 E-R图
（三）数据抽象、设计局部 E-R图
?数据抽象：分类、聚集、概括
?局部 E-R图的设计
（四 ） 局部 E-R图的集成
1、方法：
?多个局部 E-R图一次集成
?逐步集成，采用累加的方法一个集成两个 E-R图
2、局部 E-R图间的冲突：
属性冲突、命名冲突、结构冲突
3、解决结构冲突的调整手段：
?根据综合应用的需要，把属性转为实体或把实体
转为属性
?对同一个实体的属性取各个局部 E-R图相同实体
属性的并集
?实体联系要根据应用语义进行综合调整
三、逻辑设计
（一）初始关系模式的导出
1、实体集的转换规则：
一个实体型转为一个关系模式，实体的关
键字即为关系的关键字。
2、联系的转换规则：
1） 1,1联系：
2） 1,m联系：
选择任一实体的主键作为另一实
体的外键插入另一实体中。
a,把 1端关键键作为外键加入 m端
b,把联系单独作为一个关系，此关系的关键
字是多方实体的关键字
3） m,n联系,把联系单独转为一个关系。把 m,n端
的主关键字作为外键加入到该关系中，
并把他们作为该关系的复合主键。
（二）关系数据库的规范化理论
1、初始关系模式存在的问题：
sno sn sage ssex dept mn cn grade
03001 张华 20 女 计算机 韩冰 数据库 89
03001 张华 20 女 计算机 韩冰 数据结构 88
03002 李平 21 男 管理 李江 管理学 80
?数据冗余
?插入异常
?删除异常
?更新异常
2、解决方法：分解
3、函数依赖,反映属性间的对应约束关系
S(sno,sn,sage,ssex,dept_no)
Dept(dept_no,dept,mn)
Sc(sno,cn,grade)
1)函数依赖：
?
U={A1,A2,…,An}
R(A1,A2,…,An) X U,Y U, t1,t2∈ r
若 t1[X]=t2[X],则 t1[Y]=t2[Y],X→ Y
? ?
给定一个属性值 (组 )，可查到另一个属性值
X称为决定因素
2) 非平凡的函数依赖：
X→ Y,但 Y X
3) 平凡的函数依赖：
4) 完全函数依赖：
5) 部分函数依赖：
例 1：设有选课关系模式
S_C(SNO,CNO,GRADE,CREDIT),其中,SNO表
示学号,CNO表示课程号,GRADE表示成绩，
CREDIT表示学分。请找出此关系模式中的“完
全函数依赖”和“部分函数依赖”。
6) 传递函数依赖：
例 2：设有关系模式 S (SNO,SNAME,DNO，
DNAME,DADDRESS),其中,SNO表示学号，
SNAME 表示学生姓名,DNO表示系号,DNAME
表示系名称,DADDRESS 表示系所在地址。
4、函数依赖表示的方法
1) 依赖函数
2) 函数依赖图
3) 简单表示
5、码 1) 候选码、主码：
2) 主属性、非主属性：
3) 全码：
4) 外码：
例：找出下列关系模式的函数依赖 F,候选码、主码、
主属性及非主属性。
1) SJP（ S,J,P） S,学生 J,课程 P,名次
2) T（ C,T,B） C,课程 T,教师 B,参考书
3) WPE（ WNO,PNO,ENO,QNT） WNO,仓库
号 PNO,配件号 ENO,职工号 QNT,数量
候选码可决定整个元组。
（三）范式：关系满足的要求。 R∈ XNF
1、基本概念：规范化
低 高模式分解
2,1NF,关系
1) 元组无重复
2) 属性不可分
例：职工号、姓名、电话（家里电话、办公电话）
3,2NF
1) R∈ 1NF
2) 非主属性完全依赖于码
S_C(SNO,CNO,GRADE,CREDIT)
4,3NF
1) R∈ 2NF
2) 非主属性对任一候选码无传递依赖
5,BCNF
1) R∈ 3NF
2) 决定因素必为码
1) 定义：
2) 问题：
6、多值依赖,X Y
7,4NF,不存在多值依赖
S(SNO,SN,DNO,DNAME,LOCATION)
WPE（ WNO,PNO,ENO,QNT）
T（ C,T,B）
SID MAJOR ACTIVITY
100 MUSIC SWIMMING
100 ACCOUNTING SWIMMING
100 MUSIC TENNIS
150 MATH JOGGING
Ｓ（ SID,MAJOR,ACTIVITY）
SID,学生 MAJOR,专业课程 ACTIVITY,活动
范式小结
1NF
2NF
3NF
BCNF
4NF
非主属性完全依赖候选码
非主属性对候选码无传递依赖
决定因素必为候选码
无多值依赖
习题
写出基本函数依赖和候选码，并把相应关系模
式规范为 3NF
1,学号 姓名 系 系主任 成绩 课程
2,学号、姓名、性别、专业、年级、成绩（课
程号、课程名、学时、学分，教师、工资号、
成绩）
数据依赖公理系统
一、函数逻辑蕴含：
即由一组已知的函数依赖，去判断另一些函
数依赖是否成立或能否从前者推出后者。
二,Armstrong公理系统：
1,自反：
2,增广：
3,传递：
X→Y XZ→YZ
X→Y
Y→Z X→Z
X→Y X→XY
三、推导规则：
1,合并规则：
2,伪传递规则：
3,分解规则：
X→Y
X→Z X→YZ
X→Y
WY→Z XW→Z
X→Y
Z Y X→Z?
四,F的闭包
1,定义,F中所蕴含的函数依赖全体。
2,XF＋, X关于函数依赖集的闭包。（ X为决定因素）
例 1,R（ A,B,C） F＝｛ A → B, B → C ｝ 求 XF＋
3,XF＋ 求解算法：
输入,X,F
输出,XF＋
处理：
1) 置 Z为空,Z＊ ＝ X
2) 判断 Z与 Z＊ 是否相等
3) 若等则 XF＋ ＝ Z＊, 算法终止
4) 若不等，则 Z＝ Z＊
5) B：以 Z中属性作为决定因素的 F的闭包
6) Z＊ ＝ Z∪ B,转到 2）
例 2 P185
五、等价定义：
六,Fm
例 3：有三个函数依赖，分别指出它们是否为最小覆盖
F1＝｛ AB→CD,B→EC,C→G ｝
F2＝｛ A→D,B→A,A→C,B→D,D→C ｝
F3＝｛ A→D,AC→B,D→C,C→A ｝
例 4,F＝｛ A→B,B→A,B→C,A→C,C→A ｝ 求 Fm
结论：同一函数依赖集的最小函数依赖集不唯一。
例 5,F＝｛ C→A,A→B,B→C,C→B,A→C,BC→A ｝ 求 Fm
Fm与各属性的处置顺序相关。
模式分解
一、基本概念：具有无损连接，保持函数依赖
二、判定分解的无损连接
（一）记号的定义,ρ＝｛ R1<U1,F1>… RK<UK,FK>｝
是 R<U,F>的一个分解
（二）判定步骤：,R<U,F>,ρ,U＝｛ A1 … Ai｝
1,求 Fm
2,初始化表格
A1 … Ai
R1<U1,F1>
…
RK<UK,FK>
3,初始化：若 Ai Uk,在 Ai与 Rk交叉处填 ak,否
则，填 bki
?
4,修改：逐一对函数依赖考察。
对 X → Y 考察，表中是否存在至少两行在 X上的
值相等。
若等，则看这些行在 Y上的值，若有一行在 Y上值
为 ai,那么在其他行中的 Y上的值都改为 ai。
否则改为行数最小的 bk
5,若表格在更改前后不变，则考察结束，否则重
新按新表考察。
无损连接的判定：若表格中出现一行全为 a,则为
无损连接。
例 1：
R<U,F>,U＝｛ A,B,C,D,E｝
F＝｛ AB → C, C → D,D → E ｝
ρ＝｛ R1(A,B,C),R2(C,D),R3(D,E)｝
例 2：
R<U,F>,U＝｛ A,B,C,D,E｝
F＝｛ AB → C, C → D,D → E ｝
ρ＝｛ R1(A,B),R2(C,D),R3(B,D,E)｝
三、判定保持函数依赖：等价
四、分解：用 Fm分解
例：关系模式 R＜ U,F＞,U＝｛ ABCDEFGHIJ｝
F＝｛ A → B, A → C, A → DE, D → E, DE → B,
AF → GHI, I → J, F → I ｝
1,求 Fm
2,求 F＋
3,R＜ U,F＞ 的候选码
4,分解 R＜ U,F＞,结果为 ρ1
5,验证 ρ1具有无损连接
6,判断分解后的是否为 BCNF
物理设计
一,物理设计步骤
二、物理设计的内容与方法
（一）关系模式存取方法的选择：
1,确定数据库的物理结构。
2,评价物理结构。
1,索引,B＋ 树结构
思考：表中的索引是否越多越好？
1) 说明：
? 一个表可有多个索引，但最多最好 2-3个
?数据在物理上随机存放在数据页上，每个键值项通
过指针指向包含该键值的数据行。
?叶层中不含数据页
2) 不宜建索引的条件：
?不出现或很少出现在查询条件中的属性
2.聚簇：
?一个表中中只允许一个聚簇。
?属性值很少的属性
?属性值分布不均
?常更新的属性
?过长的属性
?太小的表
3）组合索引：常同时存取多列，且每列都含重复值。
1）说明：
?结构上与索引相同。
?物理上按顺序放在数据页上，且重复值排在一起
?叶层含有数据页
2) 哪些需要建立聚簇？
? 主、外键
? 返回某范围的属性
? 聚集函数后的参数
? 查询时返回大量结果的列
4) 聚簇与索引的比较：
例,record(id,date,amount,place),其中记录
620000行。进行以下查询：
3) 注意：当有大量行插入时，不要在插入表中的
自动增加列上建立聚簇索引。
?Select date sum(amount) from record group by date
?Select count(*) from record where date=‘19990901’ and
place in(‘BJ’,’SH’)
3,HASH：
地址 01 02 … 22 …
年份 1949 1950 … 1970 …
人数 … 15000 …
用 hash表的存取方法进行存取。
关键字,H(KEY)=key-1948
4.SQL Server中的索引：
1）类型：聚集索引、非聚集索引
2）索引的建立：
?在企业管理器内建立
?通过查询分析器建立
?用 SQL语句建立
注意,
?建立一个主键约束或唯一约束时会自动建立索引
?若在建立主键约束前还示有聚集索引，则自动把此索引
设为聚集索引。
?在视图上建立非聚集索引前，视图上必已存在一个聚集
索引 。
Create [unique] [clustered |Nonclustered] Index Index_name
on table_name|view_name(column_name [,…n])
3)索引的删除：
?在企业管理器中删除
?在查询分析器中删除
?用 SQL语句
DROP Index
table_name.index_name|View_name.index_name[,…n]
（二 ） 确定数据库的存储结构
1,数据存放位置：
2,确定系统配置：
同时使用数据库的用户数、同时打开数据库的
个数、使用的缓冲区的大小和个数、时间片的大小、
数据库的大小、装填因子等等。
（三）评价物理结构：
补充,SQL SERVER 中几个常用系统表 (master数据库中 )
1,Syslogins:
2,Sysdatabase:
3,Sysusers:
4,Sysobjects:
5,Syscolumns:
6,Sysindex:
每个连接到 SQL SEVER的登录帐号存储
一条记录。
每个数据库存储一条记录
每个用户存储一条记录
数据库中每个对象存储一条记录
表中每一列存储一条记录
每个索引存储一条记录
Sysobjects:
Name:对象名
Id,对象 id
Xtype,对象类型
Uid,对象所有者 id
应用实例,
在库中 db1建表 table1,若表存在则删除后重建，
否则直接建立。
数据库实施和维护
一、创建数据库 。
运用数据库管理系统提供的命令创建数据的结构，将
数据库结构定义映像到内存中。
注意,
?表中字段名字尽量采用有意义的名字。
?仔细选用字段的类型。
?为外键建立索引，以提高效率。
?选主键时，不要选用手工输入或用户可编辑的字段，主
键最好无含义，能用自动增量类型的尽量使用。
?表中可多设一个放删除标记的字段。
二、数据库数据的载入和应用程序编码
三、数据库的试运行
四、数据库的运行和维护
（一）数据库的转储和恢复
例：银行转账, 从账号 a转 100元到账号 b:
1、事务,数据库应用程序的基本逻辑单元。
注意,事务与程序的关系
SQL,BEGIN TRANSACTION
COMMIT/ROLLBACK TRANSACTION
2,事务的 ACID性质：
原子性
一致性
隔离性
持续性
3,SQL SERVER中执行事务：
假设：银行数据库中存储用户账户信息表为：
account（ acid,acname,surplus,date）,用 SQL语
句实现上述事务。
4、在存储过程中使用事务
（二）并发控制
例：甲乙同时购买同一航班的机票，假设机
票余额为 A＝ 16
1、事务并发操作造成的问题：
?更新的丢失
?读“脏”数据（即过时的数据）
?不可重复读
（三）封锁（锁机制）
1、概念
2、锁的类型,X锁：S锁,资源由一个事务独占只允许一个事务可写
T1 T2 X S -
X N N Y
S N Y Y
- Y Y Y
注意,只有相容的锁才能在同一对象上同时实现。
例：用 X锁解决“更新丢失”的问题
对某对象加锁,X(S)LOCK 对象名
对某对象加锁,UNLOCK 对象名
（三）封锁协议：
1、概念：
2、三级锁协议：
?一级封锁协议
?二级封锁协议
?三级封锁协议
（四）死锁
1、产生：
TA TB
t0 Xlock A
t1 Xlock B
t2 Read(A)
t3 Read(B)
t4 Xlock B
t5 Wait Xlock A
t6 wait Wait
t7 wait wait
2、解决方法：
?预防：一次封锁和顺序封锁
?允许死锁，诊断有死锁时，杀一个事务来解除。
超时法
事务等待图法
（五）并发调度的可串行性
1、可串行化调度定义：
2、与串行调度的区别：
?可串行化调度是交叉执行各事务操作，但在效
果上相当于事务的某一串行执行（即两者结果
相同）
?串行调度完全串行执行各事务，没有并发的意义。
例：银行转账,TA从账号 C1转 100到,TB从 C1转 10%
到 C2
TA TB
Read(C1);
C1 = C1 -100;
Write(C1);
Read(C2);
C2 = C2 +100;
Write(C2 );
Read(C1);
var = C1 *0.1;
Write(C1);
Read(C2);
C2 = C2 +var;
Write(C2 );
(六 ) 两段锁协议：保证并发调度可串行化的协议
1、内容：
事务 第一段：扩展阶段第二段：收缩阶段 只能申请锁，不能释放锁只能释放锁，而不能申请
该协议是并发调度可串行化的充分不必要条件
2、与一次封锁法的关系
（七）封锁的粒度：封锁对象的大小
数据库的恢复
一、故障的分类
1、事务故障
2、系统故障
3、介质故障
4、计算机病毒
二、数据库的恢复：
（一）建立“冗余数据”技术
1、数据转储
2、日志
数据和数据库结构都损坏
数据损坏
通过冗余
（二）数据转储,数据库恢复的基本技术。
1、概念：建立数据库后备副本。此过程称为“数
据库的备份”
2、分类：
?静态、动态
例：每天夜里 12:00备份，设周二下午 3:00DB出故障。
（三）登记日志文件
1、内容：记录事务对 DB的更新操作。
?事务标识
?操作类型
?操作对象
?对象修改前的值
?对象改后的值
包括 INSERT,UPDATE,DELETE
?海量转储、增量转储
例：客户 C001公司地址由 NEW YORK迁到 LONDON
2、作用
3、如何写
?严格按照并发事务执行的次序写。
?先写日志后写库。
（三）恢复策略
1、利用日志进行恢复两种方法：
?REDO 正向扫描
?UNDO 反向扫描
2、事务故障的恢复
3、系统故障的恢复
1) 步骤：
?从日志文件头开始正向扫描日志文件到故障
发生点，已提交的事务放入 REDO队列中，未
完成的放入 UNDO队列
?对于 REDO中的事务 REDO
?对于 UNDO中的事务 UNDO
4、介质故障恢复
步骤：
?由 DBA利用后备副本重建 DB
?装入相应日志文件，通过系统运行事务恢复。
2) 具有检查点恢复技术,在日志中增加检查点记录
重建 DB 利用日志恢复 继续运行
DB转储 执行事务
登录日志 故障点T1 T2
（四） SQL SERVER DB恢复的实现
1,DB备份
?备份类型：
完全
差异
事务日志
文件和文件组
?说明,SQL SERVER 中“故障还原模型”影响
备份类型的选择。
?完全
?简单
?大容量日志记录 只能选择完全和差异备份
2,DB备份方法：
?用企业管理器
单击要备份的 DB→ 所有任务 → 备份 DB
?用 SQL,Backup database
Backup database database_name To
｛ logical_backup_device_name|{Disk|Tape}
=‘physical_backup_device_name’}
[WITH DIFFERENTIAL]
说明：
?logical_backup_device_name：
?physical_backup_device_name：
?WITH DIFFERENTIAL：
用户事先建立，用来存储备
份内容的逻辑设备名
包含绝对路径的物理文件名
实现差异备份
?备份日志,BACKUP LOG
3,SQL SERVER恢复
1) 企业管理器
1) SQL,RESTORE DATABASE
例,CAPDB在上午 11,00受破坏，而上次完全
备份在前一天，系统每隔一小时做一次事务日
志备份，存储完全备份设备名为 Backupdevice，
存储事务日志备份名为 Backuplog
步骤,
?从完全备份恢复，建立基线
?从事务日志恢复
数据库完整性
一、完整性的概念：
数据正确性和相容性。
二、完整性约束条件：
（一）作用对象：关系、元组、列
（二）分类：静态、动态
?静态列级约束
?静态元组约束
?静态关系约束
?动态列级约束
?动态元组约束
?动态关系约束
三、完整性控制
（一）完整性控制机制的功能：
?定义
?检查
?采取的动作
（二）实现（ SQL SERVER）
1、实体完整性,主键唯一,PRIMARY KEY
例：在 SCDB库中，建表 S,其中 SNO为主键。
2、参照完整性,建立外键,FOREIGN KEY
说明：
?定义外键时必须用 REFERENCES引用该键所引用
的主表主键名
?创建外键后，若用户修改主表中的主键值，
SQL SERVER可选两种方法做出反应：
1) NO ACTION,默认
若 FK所在表中存在与 PK中相等的记录，则产
生错误，回滚主表更新。
1) CASCADE,级联
若 FK所在表中存在与 PK中相等的记录，则修
改相应记录的值。
3、用户定义完整性
1) 建表时定义
?列值非空
?列值唯一
?检查
?规则
NOT NULL
UNIQUE
CHECK
RULE
例：建学生表 S,姓名非空，学号为主键，年龄
小于 35，性别只取“男”和“女”
2) RLUE对象的建立
a) 创建：
CREATE RULE rule_name As condition_expression
condition_expression,由算术运算符、关系运
算符、谓词等组成条件表达式，且还可包含由
＠开头的局部变量。
b) 绑定：
Sp_bindrule ‘rule_name’,’object_name’
c) 与 CHECK的比较
CHECK是列取值限定的首选方法，但 RULE
提供对不同表相同属性域的列进行相同限定
的快速方法
4,用触发器实现复杂的完整性约束
1) 触发器：是特殊的存储过程，由系统自动执行
2) 组成：事件、条件、动作
说明：
?触发器无法手动触发。
?不成功的事务会自动回滚
?一个触发器只能应用于一个单独的表或视图
3) 语法：
CREATE trigger trigger_name
On table_name or view_name
For trigger_type
As sql_statement
例 1：当向表中插入一条记录时,id号自动加 1
例 2：对教师表 T定义一规则：教授工资不低于
1000元，若低于 1000元，自动改为 1000
例 3：在图书管理系统中，有以下四个表：
borrow(bo_id,bo_bookno,bo_booknum)
return(re_id,re_bookno,re_booknum)
inbook(in_id,bookno,booknum)
bookstore(bookno,booksum,bookcur)
