数据库开发

设计与应用开发内容

一、关系数据理论
二、数据库设计
三、数据库编程


关系数据理论

问题的提出

一 、关系模式

关系模式是一个五元组。
R < U,D ,dom , F >

(1)关系名R,它是符号化的元组语义;

(2)一组属性U;

(3)属性组U中属性所来自的域D;

(4)属性到域的映射dom;

(5)属性组U上的一组数据依赖F。即属性值间的相互关连

由于(3),(4)对模式设计关系不大,因此在本章中把关系模式看作是一个三元组: R

当且仅当U上的一个关系 r 满足 F 时,r 称为关系模式R的一个关系。

第一范式(1NF):关系模式中每一个分量必须是不可分的数据项。满足了这个条件的就属于第一范式(1NF)。

二、 数据依赖

数据依赖是数据库模式设计的关键,它是一个关系内部属性与属性之间的一种约束关系,这种约束关系是通过属性间的值是否相等体现出来的数据间的相互关系。
它是现实世界属性间相互联系的抽象,是数据内在的性质,是语义的体现。
数据依赖有很多类型,其中最重要的是:
函数依赖(Functional Dependency,简记为FD)
多值依赖(Multivalued Dependency,简记为MVD)

其中,函数依赖起着核心的作用,是模式分解和模式设计的基础,范式是模式分解的标准。

比如描述一个学生的关系,可以有学号(SNO),姓名(SNAME),系名(SDEPT)等几个属性。由于一个学号只对应一个学生,一个学生只在一个系学习。因而当“学号”值确定之后,姓名和该生所在系的值也就被唯一地确定了。就象自变量x确定之后,相应的函数值f(x)也就唯一地确定了一样,称SNO函数决定SNAME和SDEPT,或者说SNAME,SDEPT函数依赖于SNO,记为:SNO→SNAME,SNO→SDEPT。

例 现在要建立一个数据库来描述学生的一些情况,面临的对象有:
单一的关系模式
Student(U,F)

U={SNO,SDEPT,MN,CNAME,G}

SNO 学号,SDEPT 系,MN 系负责人,CNAME 课程名,G 成绩

由现实世界的已知事实得知:

①一个系有若干学生,但一个学生只属于一个系;

②一个系只有一名(正职)负责人;

③一个学生可以选修多门课程,每门课程有若干学生选修;

④每个学生学习每一门课程有一个成绩;

于是得到属性组U上的一组函数依赖:

F={SNO→SDEPT,SDEPT→MN,(SNO,CNAME)→G}

这组函数依赖如图

如果只考虑函数依赖这一种数据依赖,就得到了一个描述学校的数据库模式S,它由一个单一的关系模式构成。这个模式有下述三个“毛病”:

(1)插入异常 :如果一个系刚成立尚无学生,或者虽然有了学生但尚未安排课程。那么就无法把这个系及其负责人的信息存入数据库。

(2)删除异常: 如果某个系的学生全部毕业了,在删除该系学生选修课程的同时,把这个系及其负责人的信息也丢掉了。

(3)冗余太大。比如,每一个系负责人的姓名要与该系每一个学生的每一门功课的成绩出现的次数一样多。这样,一方面浪费存储,另一方面系统要付出很大的代价来维护数据库的完整性。比如某系负责人更换后,就必须逐一修改有关的每一个元组。

由于上述三个“毛病”,它是一个“不好”的数据库模式。一个“好”的模式应当不会发生插入异常和删除异常、冗余应尽可能少。

这个单一的模式改造一下,分成三个关系模式:

S(SNO,SDEPT,SNO→SDEPT);

SG(SNO,CNAME,G,(SNO,CNAME)→G);

DEPT(SDEPT,MN,SDEPT→MN);

这三个模式就不会发生插入异常、删除异常的毛病,数据的冗余也得到了控制。


规范化

规范化理论正是用来改造关系模式的,通过分解关系模式来消除其中不合适的数据依赖,以解决插入异常、删除异常、更新异常和数据冗余问题。

函数依赖

一、定义
设R(U)是一个属性集U上的关系模式,X和Y是U的子集。
若对于R(U)的任意一个可能的关系 r,r 中不可能存在两个元组在X上的属性值相等, 而在Y上的属性值不等, 则称 “X函数确定Y” 或 “Y函数依赖于X”,记作X→Y。

  • X称为这个函数依赖的决定属性集(Determinant)。
  • Y = f(x)

说明:

  • 函数依赖不是指关系模式R的某个或某些关系实例满足的约束条件,而是指R的所有关系实例均要满足的约束条件。
  • 函数依赖是语义范畴的概念,只能根据数据的语义来确定函数依赖。例如:“姓名→年龄”这个函数依赖只有在不允许有同名人的条件下成立。
  • 数据库设计者可以对现实世界作强制的规定。例如规定不允许同名人出现,函数依赖“姓名→年龄”成立。所插入的元组必须满足规定的函数依赖,若发现有同名人存在, 则拒绝装入该元组。

例如
Student ( Sno, Sname, Ssex, Sage, Sdept ) 中
假设不允许学生重名,则有:
Sno → Ssex, Sno → Sage , Sno → Sdept,
Sno ← → Sname, Sname → Ssex, Sname → Sage
Sname → Sdept
但Ssex !→ Sage
若X→Y,并且Y→X, 则记为X← →Y。
若Y不函数依赖于X, 则记为X !→Y。

二、术语和记号:

  • X→Y,但Y⊈ X则称X→Y是 非平凡的函数依赖。若不特别声明,总是讨论非平凡的函数依赖。

  • X→Y,但Y⊆X则称X→Y是 平凡的函数依赖

  • 若X→Y,则X叫做决定因素(Determinant)。

  • 若X→Y,Y→X,则记作X←→Y。

  • 若Y函数不依赖于X,则记作X !→Y。

例:在关系SC(Sno, Cno, Grade)中,

非平凡函数依赖: (Sno, Cno) → Grade
平凡函数依赖:  (Sno, Cno) → Sno
         (Sno, Cno) → Cno

三、完全依赖和部分依赖

 
         p
而(Sno ,Cno )→Sdept是部分函数依赖,因为Sno→Sdept成立,而Sno是(Sno ,Cno )真子集

四、传递函数依赖


设K为关系模式R中的属性或属性组合。

  • 若K → U,则K称为R的一个候选码(Candidate Key)。
  • 若关系模式R有多个候选码,则选定其中的一个作为主码(Primary key)。
  • 包含在任何一个候选码中的属性,称为主属性(Prime attribute);
  • 不包含在任何码中的属性,称为非主属性(Nonprime attribute)或非码属性(Non-key attribute)。

例 :关系模式S(Sno,Sdept,Sage)中单个属性Sno是码,用下横线表示出来。SC(Sno,Cno,Grade)中属性组合(Sno,Cno)是码

极端的情况是:整个属性组U是码,即所有属性当作一个码。若关系中只有一个候选码,且这个候选码中包含全部属性,则该候选码为全码,称为全码(All-key)。

例:关系模式R(P,W,A),属性P表示演奏者,W表示作品,A表示听众。假设一个演奏者可以演奏多个作品,某一作品可被多个演奏者演奏。听众也可以欣赏不同的演奏者的不同作品,这个关系的码为(P,W,A),即All-Key。

关系模式 R 中属性或属性组X 并非 R的码,但 X 是另一个关系模式的码,则称 X 是R 的外部码(Foreign key),也称外码。
主码和外码一起提供了表示关系间联系的手段。 例如上面的关系模式S于SC的联系就是通过Sno来体现的。

例如 在SC(Sno,Cno,Grade)中,Sno不是码,但Sno是关系模式S(Sno,Sdept,Sage)的码,则Sno是关系模式SC的外部码。


范式

关系数据库中的关系必须满足一定的要求。满足不同程度要求的为不同范式。
范式的种类包括:

  • 满足最低要求的,称为第一范式(1NF)
  • 在第一范式中,满足进一步要求的,称为第二范式(2NF)
  • 以此类推:第三范式(3NF)
  • BC范式(BCNF)
  • 第四范式(4NF)
  • 第五范式(5NF)
    某一关系模式R为第n范式,可简记为R∈nNF

5NF⊂4NF⊂BCNF⊂3NF⊂2NF⊂1NF

各模式之间的联系如下图

一个低一级范式的关系模式,通过模式分解可以转换为若干个高一级范式的关系模式的集合,这种过程就叫规范化

一、 第一范式(1NF)

  • 关系中的每个分量必须是不可分的数据项。
    满足了这个条件的关系模式R就属于第一范式(1NF),记作 R∈1NF。

  • 第一范式是对关系模式的最起码的要求。
    不满足第一范式的数据库模式不能称为关系数据库。

满足第一范式的关系模式并不一定是一个好的关系模式。

例如 关系模式 SLC(Sno, Sdept, Sloc, Cno, Grade)
Sloc为学生住处,假设每个系的学生住在同一个地方。

SLC的码为(Sno, Cno)
非主属性Sdept和Sloc部分函数依赖于码(Sno, Cno)
SLC ∈1NF ,但是SLC并不是一个好的关系模式。

①插入异常
假设Sno=’95102’,Sdept=’IS’,Sloc=’N’ 的学生还未选课,因课程号是主属性,因此该学生的信息无法插入SLC。
②删除异常
假定某个学生本来只选修了3号课程这一门课。现在因身体不适,他连3号课程也不选修了。因课程号是主属性,此操作将导致该学生信息的整个元组都要删除。
③数据冗余度大
如果一个学生选修了10门课程,那么他的Sdept和Sloc值就要重复存储10次。
④修改复杂
例如学生转系,在修改此学生元组的Sdept值的同时,还可能需要修改住处(Sloc)。如果这个学生选修了n门课,则必须无遗漏地修改n个元组中全部Sdept、Sloc信息。

造成这些问题的原因是:Sdept、 Sloc部分函数依赖于码。

解决方法:将SLC分解为两个关系模式,以消除这些部分函数依赖

关系模式分解应当具有无损连接性和依赖保持性

SC(Sno, Cno, Grade)
SL(Sno, Sdept, Sloc)


二、第二范式(2NF)

定义 :若关系模式R∈1NF,并且每一个非主属性都完全函数依赖于R的码,则R∈2NF。


SLC(Sno, Sdept, Sloc, Cno, Grade) ∈1NF
SLC(Sno, Sdept, Sloc, Cno, Grade) ∉ 2NF
SC(Sno, Cno, Grade) ∈ 2NF
SL(Sno, Sdept, Sloc) ∈ 2NF

采用投影分解法将一个1NF的关系分解为多个2NF的关系,可以在一定程度上减轻原1NF关系中存在的插入异常、删除异常、数据冗余度大、修改复杂等问题。
但是,将一个1NF关系分解为多个2NF的关系,并不能完全消除关系模式中的各种异常情况和数据冗余。

三、第三范式(3NF)

如果R∈3NF,则R∈2NF。

采用投影分解法将一个2NF的关系分解为多个3NF的关系,可以在一定程度上解决原2NF关系中存在的插入异常、删除异常、数据冗余度大、修改复杂等问题。
但是,将一个2NF关系分解为多个3NF的关系后,并不能完全消除关系模式中的各种异常情况和数据冗余。

四、BCNF

BCNF是修正的第三范式,有时也称为扩充的第三范式。

设关系模式R∈1NF,如果对于R的每个函数依赖X→Y,若Y不属于X,则X必含有候选码,那么R∈BCNF。

也就是说,关系模式R中,若每一个决定因素都包含码

若R∈BCNF,则:

  • 每一个决定属性集(因素)都包含(候选)码
  • R中的所有属性(主属性和非主属性)都完全函数依赖于码
  • R∈3NF(由定义可知,排除了任何属性对码的传递依赖和部分依赖)

但是,若R∈3NF,R不一定∈BCNF

例 在关系模式C(Cno,Cname,Pcno)中,只有一个码Cno。

  • 由于没有任何属性对Cno部分依赖或传递依赖,所以C∈3NF;
  • 同时C中Cno是唯一的决定因素,所以C∈BCNF。

例 关系模式 S(Sno, Sname, Sdept, Sage)中,假定Sname具有唯一性,那么S就有两个码(Sno和Sname),这两个码都由单个属性组成,彼此不相交。

  • 其他属性不存在对码的传递依赖与部分依赖,所以S∈3NF;
  • 同时S中除Sno和Sname以外没有其他决定因素,所以S∈BCNF。

例 关系模式 SJP(S, J, P)中,S表示学生,J表示课程,P表示名次。每个学生选修每门课程的成绩有一定的名次,每门课程中的每一名次只有一个学生(即没有并列名次)。由语法可以得到下面的函数依赖:
(S, J) → P,(J, P) → S

所以(S, J)和(J, P)都可以作为候选码,这两个码各由两个属性组成,而且它们是相交的。

  • 这个关系模式中显然没有属性对码的传递依赖或部分依赖。所以SJP∈3NF;
  • 而且除了(S, J)和(J, P)以外没有其他的决定因素,所以SJP∈BCNF。

例 在关系模式STJ(S,T,J)中,S表示学生,T表示教师,J表示课程。每一教师只教一门课。每门课由若干教师教,某一学生选定某门课,就确定了一个固定的教师。某个学生选修某个教师的课就确定了所选课的名称。则有如下的函数依赖:
(S,J)→ T,(S,T)→ J,T → J
这里(S,J)和(S,T)都可以作为候选码 ,S、T、J都是主属性
没有任何非主属性对码的传递依赖或部分依赖,所以STJ∈3NF;
由于T→J,T是决定因素,而T不是候选码,所以STJ∉BCNF。

非BCNF的关系模式也可以通过分解成为BCNF。
可分解为ST(S,T)
TJ(T,J) 它们都是BCNF。

3NF和BCNF是在函数依赖的条件下对模式分解所能达到的分离程度的测度。一个模式中的关系模式如果都属于BCNF,那么在函数依赖范畴内,它已实现了彻底的分离,已消除了插入和删除的异常。3NF的“不彻底”性表现在可能存在主属性对码的部分依赖和传递依赖。


五、多值依赖

属于BCNF的关系模式是否就很完美了呢?下面来看一个例子。

  1. 例如 学校中某一门课程由多个教员讲授,他们使用相同的一套参考书。每个教员可以讲授多门课程,每种参考书可以供多门课程使用。可以用一个非规范化的关系来表示教员T,课程C和参考书B之间的关系(如表6.1所示)。

关系模型TEACHING(C,T,B)的码是(C,T,B),即All-Key。因而TEACHINGÎBCNF。

对数据的增删改很不方便,数据的冗余也十分明显。仔细考察这类关系模式,发现它具有一种称之为多值依赖(MVD)的数据依赖。

定义 :设R(U)是属性集U上的一个关系模式。X,Y,Z是U的子集,并且Z=U-X-Y。关系模式R(U)中多值依赖X→→Y成立,当且仅当对R(U)的任一关系r,给定的一对(x,z)值,有一组Y的值,这组值仅仅决定于x值而与z值无关。

若X→→Y,而Z=f即Z为空,则称X→→Y为平凡的多值依赖

多值依赖具有以下性质:

(1)多值依赖具有对称性。即若X→→Y,则X→→Z,其中Z=U-X-Y。

(2)多值依赖的传递性。即若X→→Y,Y→→Z,则X→→Z-Y。

(3)函数依赖可以看作是多值依赖的特殊情况。即若X→Y,则X→→Y。

(4)若X→→Y,X→→Z,则X→→YZ。

(5)若X→→Y,X→→Z,则X→→Y∩Z。

(6)若X→→Y,Y→→Z,则X→→Y-Z,X→→Z-Y。

多值依赖与函数依赖相比,具有下面两个基本的区别:

(1)多值依赖的有效性与属性集的范围有关。

若X→→Y在U上成立则在W(XY⊆W⊆U)上一定成立;反之则不然,即X→→Y在W(W⊂U)上成立,在U上并不一定成立。这是因为多值依赖的定义中不仅涉及属性组X和Y,而且涉及U中其余属性Z。

一般地,在R(U)上若有X→→Y在W(W⊂U)上成立,则称X→→Y为R(U)的嵌入型多值依赖。

但是在关系模式R(U)中函数依赖X→Y的有效性仅决定于X,Y这两个属性集的值。只要在R(U)的任何一个关系r中,元组在X和Y上的值满足函数依赖的定义,则函数依赖X→Y在任何属性集W(XY⊆W⊆U)上成立。

(2)若函数依赖X→Y在R(U)上成立,则对于任何Y’⊂Y均有X→Y’成立。而多值依赖X→→Y若在R(U)上成立,却不能断言对于任何Y⊂Y有X→→Y’成立。


六、4NF

定义 关系模式R∈1NF,如果对于R的每个非平凡多值依赖X→→Y(Y⊈X),X都含有码,则称R∈4NF。

4NF就是限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。因为根据定义,对于每一个非平凡的多值依赖X→→Y,X都含有候选码,于是就有X→Y,所以4NF所允许的非平凡的多值依赖实际上是函数依赖。

如果一个关系模式是4NF,则必为BCNF。

关系模式WSC的码是(W,S,C),即All-Key。WSC∈BCNF 但WSC∉ 4NF。

一个关系模式如果已达到了BCNF但不是4NF,这样的关系模式仍然具有不好的性质。数据冗余及插入、修改、删除异常等问题。因此还应该继续规范化使关系模式WSC达到4NF。

可以用投影分解的方法消去非平凡且非函数依赖的多值依赖。

例如可以把WSC分解为WS(W,S),WC(W,C)。在WS中虽然有W→→S,但这是平凡的多值依赖。WS中已不存在非平凡的非函数依赖的多值依赖。所以WS∈4NF。

函数依赖和多值依赖是两种最重要的数据依赖。如果只考虑函数依赖,则属于BCNF的关系模式规范化程度已经是最高的了。如果考虑多值依赖,则和多值依赖之外,还有其他数据依赖。例如有一种连接依赖。函数依赖是多值依赖的一种特殊情况,而多值依赖实际上又是连接依赖的一种特殊情况。但连接依赖不像函数依赖和多值依赖可由语义直接导出,而是在关系的连接运算时才反映出来。存在连接依赖的关系模式仍可能遇到数据冗余及插入、修改、删除异常等问题。如果消除了属于4NF的关系模式中存在的连接依赖,则可以进一步达到5NF的关系模式。

规范化小结

规范化的基本思想为:

  • 逐步消除不合适的数据依赖,使各关系模式达到某种程度的“分离”,即采用“一事一地”的模式设计原则:让一个关系描述一个概念、一个实体或者实体间的一种联系。若多于一个概念就把它“分离”出去。
  • 因此,所谓规范化实质上是概念的单一化。

  • 此外,并不能说规范化程度越高的关系模式就越好。

  • 在设计数据库模式结构时,必须对现实世界的实际情况和用户应用需求作进一步分析,确定一个合适的、能够反映现实世界的模式。上面的规范化步骤可以在其中任何一步终止。
  • 在实际应用中,最有价值的是3NF和BCNF,在进行关系模式的设计时,通常分解到3NF就足够了。

数据依赖的公理系统

数据依赖的公理系统是模式分解算法的理论基础,下面首先讨论函数依赖的一个有效而完备的公理系统——Armstrong公理系统。
其用途是:

  • 求给定关系模式的码
  • 从一组函数依赖求得蕴含的函数依赖 。

定义 对于满足一组函数依赖 F 的关系模式R ,其任何一个关系 r,若函数依赖 X→Y 都成立(即 r 中任意两个元组 t 和 s,若 t[X] = s[X],则 t[Y] = s[Y]), 则称 F 逻辑蕴含 X →Y。

Armstrong公理系统 设U为属性集总体,F是U上的一组函数依赖,于是有关系模式R。对R来说有以下的推理规则:

A1自反律(Reflexivity):若Y⊆X⊆U,则X→Y为F所蕴含。

A2增广律(Augmentation):若X→Y为F所蕴含,且Z⊆U,则X∪Z→Y∪Z为F所蕴含。

A3传递律(Transitivity):若X→Y及Y→Z为F所蕴含,则X→Z为F所蕴含。

注意:由自反律所得到的函数依赖均是平凡的函数依赖,自反律的使用并不依赖于F。

证明Armstrong推理规则的正确性:

自反律:
若 Y ⊆ X ⊆ U,则 X → Y 为F所蕴含。
证明:
设 Y ⊆ X ⊆ U,对R 的任一关系 r 中的任意两个元组 t和s,有:若 t[X] = s[X],由于Y ⊆X,必定有 t[Y] =s[Y],所以 X→Y成立。
自反律得证。

增广律:
若 X→Y 为F所蕴含,且 Z ⊆ U,则 X∪Z → Y∪Z 为F所蕴含。
证明:
设 X→Y 为F所蕴含,且Z ⊆ U。设R 的任一关系 r 中任意的两个元组 t和s
有:若 t[XZ] = s[XZ],则有 t[X] = s[X] 和 t[Z] = s[Z]。
因为X→Y,则有 t[Y] = s[Y],所以 t[YZ] = s[YZ],所以XZ→YZ为F所蕴含 。
增广律得证。

传递律:
若 X→Y 及 Y→Z 为F所蕴含,则 X→Z 为F所蕴含。
证明:
设 X→Y 及 Y→Z 为F所蕴含。则对R 的任一关系 r 中的任意两个元组 t和s
有:若 t[X] = s[X],由于 X→Y,必定有 t[Y] = s[Y];
再由 Y→Z,可知有 t[Z] = s[Z],所以 X→Z 为F所蕴含 。
传递律得证。

根据A1,A2,A3这三条推理规则可以得到下面三条推理规则:
合并规则:由 X→Y,X→Z,有 X→YZ。(A2, A3)
伪传递规则:由 X→Y,WY→Z,有 XW→Z。(A2, A3)
分解规则:由 X→Y 及 Z⊆Y,有 X→Z。(A1, A3)
根据合并规则和分解规则,可得:
X→A1 A2 … Ak 成立的充分必要条件是: X→Ai 成立(i=1,2,…,k)。

小结:一组用于推导函数依赖的规则:
若Y ⊆ X, 则X→Y
若X→Y,则XZ →YZ
若X→Y, Y →Z,则X→Z
若X→Y, X →Z,则X →YZ
若X→Y, WY →Z,则XW →Z
若X→Y, Z ⊆Y,则X →Z
若X →YZ,则X→Y, X →Z

在关系模式R中为F所逻辑蕴含的函数依赖的全体叫做F的闭包,记为F+

人们把自反律,传递律和增广律称为Armstrong公理系统。Armstrong公理系统是有效的、完备的。Armstrong公理的有效性指的是:由F出发根据Armstrong公理推导出来的每一个函数依赖一定在F+中;完备性指的是F+中的每一个函数依赖,必定可以由F出发根据Armstrong公理推导出来。

要证明完备性,就首先要解决如何判定一个函数依赖是否属于由F根据Armstrong公理推导出来的函数依赖的集合。当然,如果能求出这个集合,问题就解决了。但不幸的是,这是一个NP完全问题。比如从F={X→A1,…,X→An}出发,至少可以推导出2n个不同的函数依赖为此引入了下面的概念:
设F为属性集U上的一组函数依赖,X⊆U,X+F={A|X→A 能由 F 根据 Armstrong 公理导出},X+F 称为属性集X关于函数依赖集F的闭包。

设F为属性集U上的一组函数依赖,X,Y⊆U,X→Y能由F根据Armstrong公理导出的充分必要条件是Y⊆XF+

是,判定X→Y是否能由F根据Armstrong公理导出的问题,就转化为求出XF+,判定Y是否为XF+的子集的问题。这个问题由下面的算法解决了。

算法 : 求属性集X(X⊆U)关于U上的函数依赖集F的闭包XF+

输入:X,F

输出:XF+

步骤:

(1)令X(0)=X,i=0

(2)求B,这里B={A |(∃V)(∃W)(V→W ∈ F ∧ V ⊆ X (i) ∧ A ∈W);

(3)X(i+1)=B∪X(i)

(4)判断X(i+1) =X(i)吗?

(5)若相等或X(i+1)=U,则X(i+1)就是XF+,算法终止。

(6)若否,则i=i+1,返回第(2)步。

例 书籍关系模式R

其中U={A,B,C,D,E};F={AB→C,B→D,C→E,EC→B,AC→B}。

求(AB)F+

解 由算法,设X(0)=AB;

  • 计算X(1);逐一扫描F集合中各个函数依赖,找左部为A,B或AB的函数依赖。得到两个:AB→C,B→D。于是X(1)=AB∪CD=ABCD。
  • 因为X(0) ≠ X(1),所以再找出左部为ABCD子集的那些函数依赖,又得到C→E ,AC→B,于是X(2) = X(1)∪ABCDE
  • 因为X(2)已等于全部属性集合,所以(AB)F+=ABCDE。
  • 对于算法, 令ai=| X(i)|,{a(i)}形成一个步长大于1的严格递增的序列,序列的上界是|U|,因此该算法最多|U|-|X|次循环就会终止。

例:

证明完备性的逆否命题,即若函数依赖X→Y不能由F从Armstrong公理导出,那么它必然不为F所蕴含,它的证明分三步。


模式的分解

把低一级的关系模式分解为若干个高一级的关系模式的方法并不是唯一的,只有能够保证分解后的关系模式与原关系模式等价,分解方法才有意义

对于一个模式的分解是多种多样的,但是分解后产生的模式应当与原模式等价。
从不同的角度去看,“等价”有三种不同的定义:

  • 分解具有“无损连接性”
  • 分解要“保持函数依赖”
  • 分解既要“保持函数依赖”,又要具有“无损连接性”

这3个定义是实行分解的3条不同的准则

例如: SL(Sno, Sdept, Sloc)
F = { Sno → Sdept, Sdept → Sloc, Sno → Sloc }
SL∈2NF

Sno Sdept Sloc
95001 CS A
95002 IS B
95003 MA C
95004 IS B
95005 PH B

该模式存在插入异常、删除异常、冗余度大和修改复杂等问题。

  1. 将SL分解为下面三个关系模式:
    SN(Sno)
    SD(Sdept)
    SO(Sloc)
    分解后的数据库丢失了许多信息。例如无法查询95001学生所在系或所在宿舍。如果分解后的关系可以通过自然连接恢复为原来的关系,那么这种分解就没有丢失信息。

  2. 将SL分解为下面两个关系模式:
    NL(Sno, Sloc)
    DL(Sdept, Sloc)
    NL ⋈ DL比原来的SL关系多了3个元组,无法知道95002、95004、95005究竟是哪个系的学生 。元组增加了,信息丢失了 。

  3. 将SL分解为下面两个关系模式:
    ND(Sno, Sdept)
    NL(Sno, Sloc)
    与SL关系一样,因此没有丢失信息,但仍存在插入、删除和更新异常

  4. 将SL分解为下面两个关系模式:
    ND(Sno, Sdept)
    DL(Sdept, Sloc)
    与SL关系一样,因此没有丢失信息,并且也没有插入、删除和更新异常

所以第四种是比较合适的模式分解。

具有无损连接性的模式分解

对于关系模式R的一个分解 ρ = { R1,R2, …,Rn}
若R与R1, R2, …, Rn自然连接的结果相等,则称关系模式R的这个分解ρ具有无损连接性(Lossless join)。
具有无损连接性的分解保证不丢失信息。
但是,无损连接性不一定能解决插入异常、删除异常、修改复杂、数据冗余等问题。

定理
设 ρ={ R1(U1), R2(U2 )} 是 R(U) 的一个分解
则ρ为无损分解的充分必要条件为:
(U1∩U2) →(U1-U2) 或 (U1∩U2) →(U2-U1)

保持函数依赖的模式分解

设关系模式 R被分解为若干个关系模式: R1,R2, …,Rn (其中U=U1∪U2∪…∪Un,且不存在Ui ⊆ Uj,Fi为F在Ui上的投影)
若F所有逻辑蕴含的函数依赖一定也由分解得到的某个关系模式中的函数依赖Fi所逻辑蕴含,则称关系模式R的这个分解是保持函数依赖的(Preserve dependency)。
判断一个分解是否保持依赖
检查每一个原来的依赖关系是否在子模式依赖关系的并的闭包内,涉及如何求一个属性集X的闭包XF+

模式分解小结

  • 如果一个分解具有无损连接性,则它能够保证不丢失信息。
  • 如果一个分解保持了函数依赖,则它可以减轻或解决各种异常情况。

分解具有无损连接性和分解保持函数依赖是两个互相独立的标准。

  • 具有无损连接性的分解不一定能够保持函数依赖。
  • 同样,保持函数依赖的分解也不一定具有无损连接性。

上面例子中(分解 SL(Sno, Sdept, Sloc)):

  • 第一种分解方法: SN(Sno),SD(Sdept),SO(Sloc)
    既不具有无损连接性,也未保持函数依赖,它不是原关系模式的一个等价分解。
  • 第二种分解方法:NL(Sno, Sloc),DL(Sdept, Sloc)
    既未保持了函数依赖,也不具有无损连接性。
  • 第三种分解方法:ND(Sno, Sdept),NL(Sno, Sloc)
    具有无损连接性,但未持函数依赖。
  • 第四种分解方法: ND(Sno, Sdept),DL(Sdept, Sloc)
    既具有无损连接性,又保持了函数依赖。

小结

  • 在规范化过程中,逐渐消除存储异常,使数据冗余尽量小,便于插入、删除和更新。规范化的基本原则就是遵从概念单一化“一事一地”的原则,即一个关系只描述一个实体或者实体间的联系。
  • 规范化的投影分解方法不是唯一的,对于3NF的规范化,分解既要具有无损连接性,又要具有函数依赖保持性。
  • 规范化理论为数据库设计提供了理论的指南和工具。
  • 并不是规范化程度越高,模式就越好,必须结合应用环境和现实世界的具体情况合理地选择数据库模式。

数据库设计

数据库设计概述

数据库设计是指对于一个给定的应用环境,构造最优的数据库模式,建立数据库及其应用系统,使之能够有效地存储数据,满足各种用户的应用需求(信息要求和处理要求)

在数据库领域内,常常把使用数据库的各类系统统称为数据库应用系统。
数据库是信息系统的核心和基础,它把信息系统中大量的数据按一定的模型组织起来,提供存储、维护、检索数据的功能,使信息系统可以方便、及时、准确地从数据库中获得所需信息。

数据库是信息系统的各个部分能否紧密地结合在一起以及如何结合的关键所在,数据库设计是信息系统开发和建设的重要组成部分。

数据库设计人员应该具备的技术和知识有:

  • 数据库的基本知识
  • 数据库设计技术
  • 计算机科学的基础知识
  • 程序设计的方法和技巧
  • 软件工程的原理和方法
  • 应用领域的知识

数据库设计的特点

数据库结构(数据)设计与行为(处理)设计相结合

  1. 数据库的结构设计

    • 数据库的结构设计指是根据给定的应用环境,进行数据库的模式或子模式的设计。它包括数据库的概念设计、逻辑设计和物理设计。
    • 数据库模式是各应用程序共享的结构,是静态的、稳定的,一经形成后通常情况下是不容易改变的,所以结构设计又称为静态模型设计。
  2. 数据库的行为设计

    • 数据库的行为设计是指确定数据库用户的行为和动作。而在数据库系统中,用户的行为和动作指用户对数据库的操作,这些要通过应用程序来实现,所以数据库的行为设计就是应用程序的设计。
    • 用户的行为总是使数据库的内容发生变化,所以行为设计是动态的,行为设计又称为动态模型设计。

数据库设计方法

数据库设计方法目前可分为四类:

  • 直观设计法
  • 规范设计法
  • 计算机辅助设计法
  • 自动化设计法

一、直观设计法
直观设计法也叫手工试凑法,它是最早使用的数据库设计方法。
这种方法依赖于设计者的经验和技巧,缺乏科学理论和工程原则的支持,设计的质量很难保证,常常是数据库运行一段时间后又发现各种问题,这样再重新进行修改,增加了系统维护的代价。
因此,这种方法越来越不适应信息管理发展的需要。

二、规范设计法
也称为新奥尔良法,它是目前公认的比较完整和权威的一种规范设计法。
新奥尔良法将数据库设计分成需求分析(分析用户需求)、概念设计(信息分析和定义)、逻辑设计(设计实现)和物理设计(物理数据库设计)。
其基本思想是:过程迭代和逐步求精。
下面简单介绍几种常用的规范设计方法。

  • 基于E-R模型的数据库设计方法
    该方法是在需求分析的基础上,用E-R图构造一个反映现实世界实体之间联系的企业模式,然后再将此企业模式转换成基于某一特定的DBMS的概念模式。
  • 基于3NF的数据库设计方法
    该方法是在需求分析的基础上,确定数据库模式中的全部属性和属性间的依赖关系,将它们组织在一个单一的关系模式中,然后再分析模式中不符合3NF的约束条件,将其进行投影分解,规范成若干个3NF关系模式的集合。
  • 基于视图的数据库设计方法
    该方法先从分析各个应用的数据着手,其基本思想是为每个应用建立自己的视图,然后再把这些视图汇总起来合并成整个数据库的概念模式。

计算机辅助设计法
ORACLE Designer 2000
SYBASE PowerDesigner


数据库设计的基本步骤

和其他软件一样,数据库的设计过程可以使用软件工程中的生存周期的概念来说明,称为“数据库设计的生存期”,它是指从数据库研制到不再使用它的整个时期。

按规范设计法可将数据库设计分为六个阶段:

  1. 系统需求分析阶段
  2. 概念结构设计阶段
  3. 逻辑结构设计阶段
  4. 物理设计阶段
  5. 数据库实施阶段
  6. 数据库运行与维护阶段

一、系统需求分析阶段

  • 需求分析是整个数据库设计过程的基础,要收集数据库所有用户的信息内容和处理要求,并加以规格化和分析。
  • 这是最费时、最复杂的一步,但也是最重要的一步,相当于待构建的数据库大厦的地基,它决定了以后各步设计的速度与质量。需求分析做得不好,可能会导致整个数据库设计返工重做。
  • 在分析用户需求时,要确保用户目标的一致性。

二、概念结构设计阶段

  • 概念设计是把用户的信息要求统一到一个整体逻辑结构中,此结构能够表达用户的要求,是一个独立于任何DBMS软件和硬件的概念模型。
  • 这个阶段是整个数据库设计的关键所在。

三、逻辑结构设计阶段

  • 逻辑设计是将上一步所得到的概念模型转换为某个DBMS所支持的数据模型,并对其进行优化。

四、物理设计阶段

  • 物理设计是为逻辑数据模型建立一个完整的能实现的数据库结构,包括存储结构和存取方法。

五、数据库实施阶段

  • 设计人员根据逻辑设计和物理设计的结果建立数据库,编制与调试应用程序,组织数据入库,并进行试运行。

六、数据库运行与维护阶段

  • 这一阶段主要是收集和记录实际系统运行的数据,数据库运行的记录用来提高用户要求的有效信息,用来评价数据库系统的性能,进一步调整和修改数据库。
  • 在数据库系统运行过程中必须不断地对其进行评价、调整与修改,以保持数据库的完整性,并能有效地处理数据库故障和进行数据库恢复。在运行和维护阶段,可能要对数据库结构进行修改或扩充。

设计一个完善的数据库应用系统,往往是上述六个阶段的不断反复。

按照上述原则,设计过程各个阶段的设计描述,可以用下图概括


需求分析

需求分析是数据库设计的起点,为以后的具体设计做准备。
需求分析的结果是否准确的反映了用户的实际要求,将直接影响到后面各个阶段的设计,并影响到设计结果是否合理和实用。
经验证明,由于设计要求的不正确或误解,直到系统测试阶段才发现许多错误,则纠正起来要付出很大代价。因此,必须高度重视系统的需求分析。

一、需求分析的任务

  • 从数据库设计的角度来看,需求分析的任务是:对现实世界要处理的对象(组织、部门、企业)等进行详细的调查,通过对原系统(手工系统或计算机系统)的了解,明确用户的各种需求,收集支持新系统的基础数据并对其进行处理,在此基础上确定新系统的功能。
  • 新系统必须充分考虑今后可能的扩充和改变,不能仅仅按当前应用需求来设计数据库。

二、需求分析的重点
需求分析的重点是调查、收集与分析用户在数据管理中的:

  • 信息要求 —— 指用户需要从数据库中获得信息的内容与性质。由用户的信息要求可以导出数据要求,即在数据库中需要存储哪些数据。
  • 处理要求 —— 指用户要完成什么处理功能,对处理的响应时间的要求,对处理方式的要求(批处理 / 联机处理)。
  • 安全性与完整性要求

三、需求分析的难点

  • 确定用户最终需求的难点
    • 用户缺少计算机知识,开始时无法确定计算机究竟能为自己做什么,不能做什么,因此无法一下子准确地表达自己的需求,他们所提出的需求往往不断地变化。
    • 设计人员缺少用户的专业知识,不易理解用户的真正需求,甚至误解用户的需求。
    • 新的硬件、软件技术的出现也会使用户需求发生变化。
  • 解决方法
    设计人员必须采用有效的方法,与用户不断深入地进行交流,才能逐步得以确定用户的实际需求。

四、需求分析的方法

  1. 首先是调查清楚用户的实际需求并进行初步分析,与用户达成共识,然后 进一步分析与表达这些需求。

    • 调查组织机构情况,包括了解组织部门的组成情况和各部门的职责等。
    • 调查各部门的业务活动情况,调查重点之一。包括了解各个部门输入和使用什么数据;如何加工处理这些数据;输出什么信息;输出到什么部门;输出结果的格式是什么,等等。
  2. 做需求调查时,往往需要同时采用多种方法。无论使用何种调查方法,都必须有用户的积极参与和配合。

  3. 设计人员应该和用户取得共同的语言,帮助不熟悉计算机的用户建立数据库环境下的共同概念,并对设计工作的最后结果共同承担责任。

常用调查方法包括:

  • 跟班作业。通过亲身参加业务工作了解业务活动的情况,能比较准确地理解用户的需求,但比较耗时。
  • 开调查会。通过与用户座谈来了解业务活动情况及用户需求。
  • 请专人介绍。
  • 询问。对某些调查中的问题,可以找专人询问。
  • 设计调查表请用户填写。如果调查表设计合理,则很有效,且易于为用户接受。
  • 查阅记录。查阅与原系统有关的数据记录。

五、进一步分析和表达用户需求
分析和表达用户的需求的常用方法是:
自顶向下的结构化分析方法(Structured Analysis,简称SA方法)

SA方法从最上层的系统组织机构入手,采用逐层分解的方式分析系统,并用数据流图和数据字典描述系统。

要反映更详细的内容,需要分解处理功能和数据。

  • 分解处理功能
    • 将处理功能的具体内容分解为若干子功能,再将每个子功能继续分解,直到把系统的工作过程表达清楚为止。
    • 分解后的处理过程,用判定表判定树来描述。
  • 分解数据
    • 在处理功能逐步分解的同时,其所用的数据也逐级分解,形成若干层次的数据流图。数据流图表达了数据和处理过程的关系。
    • 分解后的数据,用数据字典来描述

最后,将分析结果再次提交给用户,征得用户的认可。整个过程如下图所示:

例如,我们要开发一个学校管理系统。

  • 经过可行性分析和初步需求调查,抽象出该系统最高层数据流图,该系统由教师管理子系统、学生管理子系统、后勤管理子系统组成,每个子系统分别配备一个开发小组。
  • 进一步细化各个子系统。其中学生管理子系统开发小组通过进行进一步的需求调查,明确了该子系统的主要功能是进行学籍管理和课程管理,包括学生报到、入学、毕业的管理,学生上课情况的管理。通过详细的信息流程分析和数据收集后,他们生成了该子系统的数据流图。

数据字典

数据字典的用途
数据字典是各类数据描述的集合,是进行详细的数据收集和数据分析所获得的主要结果。数据字典在数据库设计中占有很重要的地位。

数据字典通常包括:

  • 数据项
  • 数据结构
  • 数据流
  • 数据存储
  • 处理过程

其中,数据项是数据的最小组成单位,若干个数据项可以组成一个数据结构。
数据字典通过对数据项和数据结构的定义来描述数据流、数据存储的逻辑内容。
下面,我们用学生学籍管理子系统的数据字典 进行说明

数据项

数据项是不可再分的数据单位。

数据项描述={ 数据项名,数据项含义说明,别名,
              数据类型,长度,取值范围,取值含义,
               与其他数据项的逻辑关系}

其中,取值范围、与其他数据项的逻辑关系定义了数据的完整性约束条件.

以“学号”为例:

数据项: 学号
含义说明:唯一标识每个学生
别名:  学生编号
类型:  字符型
长度:   8
取值范围:00000000至99999999
取值含义:前2位标明该学生所在年级,后面4位标明院系和班级,后2位按顺序编号
与其他数据项的逻辑关系:…… 

数据结构

数据结构反映了数据之间的组合关系。

一个数据结构可以由若干个数据项组成,也可以由若干个数据结构组成,或由若干个数据项和数据结构混合组成。

数据结构描述={ 数据结构名,含义说明,
             组成:{数据项或数据结构}} 

以“学生”为例:

数据结构: 学生
含义说明: 是学籍管理子系统的主体数据结构,定义了一个学生的有关信息
组成:   学号,姓名,性别,年龄,所在系,年级 

数据流

数据流是数据结构在系统内传输的路径。

数据流描述={ 数据流名,说明,数据流来源,
             数据流去向,组成:{数据结构},
                平均流量,高峰期流量 }

其中,数据流来源是说明该数据流来自哪个过程;数据流去向是说明该数据流将到哪个过程去;平均流量是指在单位时间(每天、每周、每月等)里的传输次数;高峰期流量则是指在高峰时期的数据流量。

以“体检结果”为例:

数据流:  体检结果
说明: 学生参加体格检查的最终结果
数据流来源:体检
数据流去向:审批
组成:   ……
平均流量: ……
高峰期流量:…… 

数据存储

数据存储是数据结构停留或保存的地方,也是数据流的来源和去向之一。

数据存储描述={ 数据存储名,说明,编号,
              流入的数据流,流出的数据流,
               组成:{数据结构},数据量,存取方式}  

其中,流入的数据流指出数据来源;流出的数据流指出数据去向;数据量是指每次存取多少数据,每天(或每小时、每周等)存取几次等信息;存取方法包括:批处理 / 联机处理、检索 / 更新、顺序检索 / 随机检索等。

以“学生登记表 ”为例:

数据存储: 学生登记表
说明:   记录学生的基本情况
流入数据流:入学登记
流出数据流:查询信息
组成:   ……
数据量:  每年3000张
存取方式: 随机存取 

处理过程

处理过程的具体处理逻辑一般用判定表或判定树来描述。数据字典中只需要描述处理过程的说明性信息。

处理过程描述={ 处理过程名,说明,输入:{数据流},
               输出:{数据流},处理:{简要说明}}

简要说明主要说明该处理过程的功能及处理要求:
功能是指该处理过程用来做什么;
处理要求是指处理频度要求(如单位时间里处理多少事务,多少数据量)、响应时间要求等。
处理要求是后面物理设计的输入及性能评价的标准。

以“分配宿舍 ”为例:

处理过程:分配宿舍
说明:  为所有新生分配学生宿舍
输入:  学生,宿舍
输出:  宿舍安排
处理:  在新生报到后,为所有新生分配学生宿舍。要求同一间宿舍只能安排同一性别的学生,同一个学生只能安排在一个宿舍中。每个学生的居住面积不小于3平方米。安排新生宿舍其处理时间应不超过15分钟。……

可见,数据字典是关于数据库中数据的描述,即元数据,而不是数据本身。
数据字典是在需求分析阶段建立,在数据库设计过程中不断修改,充实,完善的。


概念结构设计

概念结构

  • 需求分析阶段描述的用户应用需求是现实世界的具体需求,将需求分析得到的用户需求抽象为信息结构即概念模型的过程就是概念结构设计。
  • 概念结构是各种数据模型的共同基础,它比数据模型更独立于机器、更抽象,从而更加稳定。
  • 概念结构设计是整个数据库设计的关键。

概念结构设计的特点:

  • 能真实、充分地反映现实世界,包括事物和事物之间的联系,能满足用户对数据的处理要求。是对现实世界的一个真实模型。
  • 易于理解,从而可以用它和不熟悉计算机的用户交换意见,用户的积极参与是数据库的设计成功的关键。
  • 易于更改,当应用环境和应用要求改变时,容易对概念模型修改和扩充。
  • 易于向关系、网状、层次等各种数据模型转换。

描述概念模型的工具
E-R模型

概念结构设计的方法与步骤

设计概念结构的四类方法

  1. 自顶向下
    首先定义全局概念结构的框架,然后逐步细化。
  1. 自底向上
    首先定义各局部应用的 概念结构,然后将它们集成起来,得到全局概念结构 。
  1. 逐步扩张
    首先定义最重要的核心概念结构,然后向外扩充,以滚雪球的方式逐步生成其他概念结构,直至总体概念结构。
  1. 混合策略
    将自顶向下和自底向上相结合,用自顶向下策略设计一个全局概念结构的框架,以它为骨架集成由自底向上策略中设计的各局部概念结构 。

常用策略:

  • 自顶向下地进行需求分析
  • 自底向上地设计概念结构

这里只介绍自底向上设计概念结构的方法。它通常分为两步:第1步是抽象数据并设计局部视图,第2步是集成局部视图,得到全局的概念结构,如下图所示。

其中DD是数据字典,DFD是数据流


数据抽象

概念结构是对现实世界的一种抽象。从实际的人、物、事和概念中抽取所关心的共同特性,忽略非本质的细节。把这些特性用各种概念精确地加以描述,这些概念组成了某种模型。

三种常用抽象包括

  1. 分类(Classification)
    • 定义某一类概念作为现实世界中一组对象的类型,这些对象具有某些共同的特性和行为,它抽象了对象值和型之间的“is member of”的语义。
    • 在E-R模型中,实体型就是这种抽象。
    • 例如: 哪些人构成“学生”,哪些人构成“教师” 。
  1. 聚集(Aggregation)
    • 定义某一类型的组成成分,它抽象了对象内部类型和成分之间“is part of”的语义。
    • 在E-R模型中若干属性的聚集组成了实体型,就是这种抽象。
    • 例如:“学生”由学号、姓名、性别、班级等属性构成。
  1. 概括(Generalization)
    • 定义类型之间的一种子集联系,它抽象了类型之间的“is subset of”的语义。
    • 概括有一个很重要的性质:继承性。子类继承超类上定义的所有抽象。
    • 例如:本科生和研究生是学生的子类,学生是它们的父类。

原本的E-R图不具有概括,然后对E-R模型作了扩充,允许定义超类实体和子类实体。并用双竖边的矩形框表示子类,用直线加小圈圈表示超类—子类的联系,如下图所示

概括有一个很重要的性质:继承性。子类继续超类上定义的所有抽象。这样,本科生和研究生集成了学生类型的属性,子类也可以增加自己的某些特殊属性。


局部E-R模型设计

数据抽象是概念设计的第一步,其用途是:
对需求分析阶段收集到的数据进行分类、组织(聚集),形成实体、实体的属性,标识实体的码,并确定实体之间的联系类型(1:1,1:n,m:n),设计局部E-R模型。

E-R方法:

  • “实体-联系方法”(Entity-Relationship Approach)的简称。它是描述现实世界概念结构模型的有效方法。
  • 用E-R方法建立的概念结构模型称为E-R模型,或称为E-R图。
  • 实体型:用矩形框表示,框内标注实体名称。
  • 属性:用椭圆形框表示,框内标注属性名称。
  • 联系:指实体之间的联系,有一对一(1:1),一对多(1:n)或多对多(m :n)三种联系类型。
    例如系主任领导系,学生属于某一系,学生选修课程,工人生产产品,这里“领导”、“属于”、“选修”、“生产”表示实体间的联系,可以作为联系名称。联系用菱形框表示,框内标注联系名称。

现实世界的复杂性导致实体联系的复杂性。表现在E-R图上可以归结为下图所示的几种基本形式:

  1. 两个实体之间的联系
  1. 两个以上实体间的联系
  1. 同一实体集内部各实体之间的联系

需要注意的是,因为联系本身也是一种实体型,所以联系也可以有属性。如果一个联系具有属性,则这些联系也要用无向边与该联系连接起来。
例如,如下图所示,学生选修的课程有相应的成绩。这里的“成绩”既不是学生的属性,也不是课程的属性,只能是学生选修课程的联系的属性。

设计分E-R图具体做法

① 选择局部应用
在多层的数据流图中选择一个适当层次的数据流图,作为设计分E-R图的出发点。让这组图中每一部分对应一个局部应用。

由于高层的数据流图只能反映系统的概貌,而中层的数据流图能较好地反映系统中各局部应用的子系统组成,因此人们往往以中层数据流图作为设计分E-R图的依据(如图所示)。

②逐一设计分E-R图

选择好局部应用之后,就要对每个局部应用逐一设计分E-R图,亦称局部E-R图。

③ 实体与属性之间并没有形式上可以截然划分的界限,但可以给出两条准则:
(1)作为“属性”,不能再具有需要描述的性质。“属性”必须是不可分的数据项,不能包含其他属性。
(2)“属性”不能与其他实体具有联系,即E-R图中所表示的联系是实体之间的联系。
凡满足上述两条准则的事物,一般均可作为属性对待。

④例子
例如1:职工是一个实体,职工号、姓名、年龄是职工的属性,职称如果没有与工资、福利挂钩,换句话说,没有需要进一步描述的特性,则根据准则(1)可以作为职工实体的属性。但如果不同的职称有不同的工资、住房标准和不同的附加福利,则职称作为一个实体看待就更恰当,如下图所示。

例如2在医院中,一个病人只能住在一个病房,病房号可以作为病人实体的一个属性。但如果病房还要与医生实体发生联系,即一个医生负责几个病房的病人的医疗工作,则病房根据准则(2)应作为一个实体,如图所示。

例如3如果一种货物只存放在一个仓库,那么就可以把存放货物的仓库号作为描述货物存放地点的属性。但如果一种货物可以存放在多个仓库中,或者仓库本身又用面积作为属性,或者与职工发生管理上的联系,那么就应把仓库作为一个实体,如下图所示。

实例 销售管理子系统分E-R图的设计。

子系统的主要功能是:处理顾客和销售员送来的订单(接订单)

根据订货安排生产的(处理订单)

交出货物同时开出发票(开发票)

收到顾客付款后,根据发票存根和信贷情况进行应收款处理

得到了该子系统二层数据流图(共5张)和数据字典,其中包括14个数据结构和29个数据流。

图7.18是第一层数据流图。虚线部分划出了系统边界。图中把系统功能又分为四个子系统。图7.19至图7.22是第二层数据流图。

设计该分E-R图的草图(如图7.23所示)。

然后参照第二层数据流图和数据字典中的详尽描述,遵循前面给出的两个准则,进行了如下调整:

(1)每张订单由订单号、若干头信息和订单细节组成。订单细节又有订货的零件号、数量等来描述。按照准则(2),订单细节就不能作订单的属性处理而应该上升为实体。一张订单可以订若干产品,所以订单与订单细节两个实体之间是1:n的联系。

(2)原订单和产品的联系实际上是订单细节和产品的联系。每条订货细节对应一个产品描述,订单处理时从中获得当前单价、产品重要等信息。

(3)图7.21中“发票清单”是一个数据存储,是否应作为实体加入分E-R图呢?答案是不必。这里的数据存储对应手工凭证,发票上的信息在开具发票的同时已及时存入应收账款中了。

(4)工厂对大宗订货给予优惠。每种产品都规定了不同订货数量的折扣,应增加一个“折扣规则”实体存放这些信息,而不应把它们放在产品描述实体中。

最后得到分E-R图如图7.24所示。

对每个实体定义的属性如下:

顾客:{<u>顾客号</u>,顾客名,地址,电话,信贷状况,账目余额
订单:{<u>订单号</u>,顾客号,订货项数,订货日期,交货日期,工种号,生产地点}
订单细则:{<u>订单号,细则号</u>,零件号,订货数,金额}
应收账款:{<u>顾客号,订单号</u>,发票号,应收金额,支付日期,支付金额,当前余额,货款限额}
产品描述:{<u>产品号</u>,产品名,单价,重量}
折扣规则:{<u>产品号</u>,订货量,折扣}

视图的集成

各子系统的分E-R图设计好以后,下一步就是要将所有的分E-R图综合成一个系统的总E-R图。

  1. 有两种方式:

多个分E-R图一次集成,如图所示。

逐步集成,用累加的方式一次集成两个分E-R图,如图所示。

第一种方式比较复杂,做起来难度较大。

第二种方式每次只集成两个分E-R图,可以降低复杂度。

  1. 每次集成局部E-R图时都需要分两步走。

(1)合并。解决各分E-R图之间的冲突,将各分E-R图合并起来生成初步E-R图。

(2)修改和重构。消除不必要的冗余,生成基本E-R图。

一、合并分E-R图,生成初步E-R图

合理消除各分E-R图的冲突是合并分E-R图的主要工作与关键所在。

各分E-R图之间的冲突主要有三类:属性冲突、命名冲突和结构冲突。

  1. 属性冲突

    • 属性值域冲突 即属性值的类型、取值范围或取值集合不同。比如学号,有些部门将其定义为数值型,而有些部门将其定义为字符型。又如年龄,有的可能用出生年月表示,有的则用整数表示。

    • 属性取值单位冲突。
      比如零件的重量,有的以公斤为单位,有的以斤为单位,有的则以克为单位。

属性冲突属于用户业务上的约定,必须与用户协商后解决。

  1. 命名冲突
    命名不一致可能发生在实体名、属性名或联系名之间,其中属性的命名冲突更为常见。一般表现为同名异义或异名同义(实体、属性、联系名)。

    • 同名异义
      即同一名字的对象在不同的部门中具有不同的意义。
      比如,“单位”在某些部门表示为人员所在的部门,而在某些部门可能表示物品的重量、长度等属性。

    • 异名同义(一义多名),即同一意义的对象在不同的部门中具有不同的名称。
      比如,有的部门把教科书称为“课本“,有的部门则把教科书称为”教材“。

    • 命名冲突可能发生在属性级、实体级、联系级上。其中属性的命名冲突更为常见。

    • 命名冲突的解决方法同属性冲突,需要与各部门协商、讨论后加以解决。

  1. 结构冲突

    • 同一对象在不同应用中有不同的抽象,可能为实体,也可能为属性。例如,“系别”在某一局部应用中被当作实体,而在另一局部应用中则被当作属性。这类冲突在解决时,就是使同一对象在不同应用中具有相同的抽象,或把实体转换为属性,或把属性转换为实体。
    • 同一实体在不同应用中属性组成不同,或者是属性次序不同。 原因:不同的局部应用关心的是该实体的不同侧面。
      解决办法是:合并后实体的属性组成为各局部E-R图中的同名实体属性的并集,然后再适当调整属性的次序。
    • 同一联系在不同应用中呈现不同的类型。
      例如,E1与E2在某一应用中可能是一对一联系,而在另一应用中可能是一对多或多对多联系,也可能是在E1、E2、E3三者之间有联系。
      这种情况应该根据应用的语义对实体联系的类型进行综合或调整。

下面以教务管理系统中的两个局部E-R图为例,来说明如何消除各局部E-R图之间的冲突,进行局部E-R模型的合并,从而生成初步E-R图。
首先,这两个局部E-R图中存在着命名冲突,学生选课局部E-R图中的实体“系”与教师任课局部E-R图中的实体“单位”,都是指“系”,即所谓的异名同义,合并后统一改为“系”,这样属性“名称”和“单位”即可统一为“系名”。
其次,还存在着结构冲突,实体“系”和实体“课程” 在两个不同应用中的属性组成不同,合并后这两个实体的属性组成为原来局部E-R图中的同名实体属性的并集。解决上述冲突后,合并两个局部E-R图,生成如图所示的初步的全局E-R图。

修改与重构,消除不必要的冗余,生成基本E-R图

  • 所谓冗余,在这里指冗余的数据和实体之间冗余的联系。
    • 冗余的数据是指可由基本的数据导出的数据;
    • 冗余的联系是由其他的联系导出的联系。
  • 在上面消除冲突合并后得到的初步E-R图中,可能存在冗余的数据或冗余的联系。冗余的存在容易破坏数据库的完整性,给数据库的维护增加困难,应该消除。我们把消除了冗余的初步E-R图称为基本E-R图。
  • 通常采用分析的方法消除冗余。数据字典是分析冗余数据的依据,还可以通过数据流图分析出冗余的联系。

例如,在上图所示的初步E-R图中

  • “课程”实体中的属性“教师号”可由“讲授”这个教师与课程之间的联系导出,而学生的平均成绩可由“选修”联系中的属性“成绩”中计算出来,所以“课程”实体中的 “教师号” 与“学生”实体中的“平均成绩”均属于冗余数据。
  • 最终得到的基本E-R模型是企业的概念模型,它代表了用户的数据要求,是沟通“要求”和“设计”的桥梁。它决定数据库的总体逻辑结构,是成功建立数据库的关键。如果设计不好,就不能充分发挥数据库的功能,无法满足用户的处理要求。
  • 因此,用户和数据库人员必须对这一模型反复讨论,在用户确认这一模型已正确无误的反映了他们的要求后,才能进入下一阶段的设计工作。

逻辑结构设计

数据库逻辑设计的任务是将概念结构转换成特定DBMS所支持的数据模型的过程。从此开始便进入了“实现设计”阶段,需要考虑到具体的DBMS的性能、具体的数据模型特点。
从E-R图所表示的概念模型可以转换成任何一种具体的DBMS所支持的数据模型,如网状模型、层次模型和关系模型。
这里只讨论关系数据库的逻辑设计问题,所以只介绍E-R图如何向关系模型进行转换。

逻辑结构设计的步骤包括:

  • 将概念结构转化为一般的关系、网状、层次模型;
  • 将转化来的关系、网状、层次模型向特定DBMS支持下的数据模型转换
  • 对数据模型进行优化

转换内容

  • E-R图由实体、实体的属性和实体之间的联系三个要素组成,关系模型的逻辑结构是一组关系模式的集合。
  • 将E-R图转换为关系模型,也就是将实体、实体的属性和实体之间的联系转化为关系模式。

一、转换原则

  1. 一个实体型转换为一个关系模式
    • 实体型的属性就是关系的属性
    • 实体型的码就是关系的码
      例如,学生实体可以转换为如下关系模式:学生(学号,姓名,性别、出生日期,所在系,年级,平均成绩)
      宿舍、班级、档案材料、教师、课程、教室、教科书都可以分别转换为一个关系模式。
  1. 一个m:n联系转换为一个关系模式

    • 关系的属性:与该联系相连的各实体的码以及联系本身的属性
    • 关系的码:各实体码的组合
      例如,“选修”联系是一个m:n联系,可以将它转换为如下关系模式,其中学号与课程号为关系的组合码:
      选修(学号,课程号,成绩)
  2. 一个1:n联系可以转换为一个独立的关系模式,也可以与n端对应的关系模式合并

    • 转换为一个独立的关系模式
      关系的属性:与该联系相连的各实体的码以及联系本身的属性
      关系的码:n端实体的码
      例如,“系“和“教师”是1:n的联系,建立一个关系,记录教师和所在的系,以教师作为码。

    • 与n端对应的关系模式合并
      合并后关系的属性:在n端关系中加入1端关系的码和联系本身的属性
      合并后关系的码:不变。
      例如,“系“和“教师”是1:n的联系,在“教师“关系中加入”系“作为属性。
      这种可以减少系统中的关系个数,一般情况下更倾向于采用这种方法。

  3. 一个1:1联系可以转换为一个独立的关系模式,也可以与任意一端对应的关系模式合并

    • 转换为一个独立的关系模式
      关系的属性:与该联系相连的各实体的码以及联系本身的属性
      关系的候选码:每个实体的码均是该关系的候选码
      例如,“班长”和“班级”是1:1的联系,可以建立一个关系,记录班长和所在班级,这两个属性都可以作为该关系的候选码。

    • 与某一端对应的关系模式合并
      合并后关系的属性:加入对应关系的码和联系本身的属性
      合并后关系的码:不变
      例如,“班长”和“班级”是1:1的联系,可以在“班长“关系中增加班级属性,也可以在”班级“关系中加入班长属性。

      从理论上讲,1:1联系可以与任意一端对应的关系模式合并。
      但在一些情况下,与不同的关系模式合并效率会大不一样。因此究竟应该与哪端的关系模式合并需要依应用的具体情况而定。
      由于连接操作是最费时的操作,所以一般应以尽量减少连接操作为目标。
      例如,如果经常要查询某个班级的班长信息,那么将“班级“属性合并到“班长“关系中会更好些。

  4. 三个或三个以上实体间的一个多元联系转换为一个关系模式

    • 关系的属性:与该多元联系相连的各实体的码以及联系本身的属性
    • 关系的码:各实体码的组合
      例如,“授课”联系是一个三元联系,可以将它转换为如下关系模式,其中课程号、教师号和教材号为关系的组合码:  授课(课程号,教师号,教材号)
  1. 同一实体集的实体间的联系,即自联系,也可按上述1:1、1:n和m:n三种情况分别处理
    例如,如果教师实体集内部存在领导与被领导的1:n自联系,我们可以将该联系与教师实体合并,这时主码教师号将多次出现,但作用不同,可用不同的属性名加以区分: 教师:{教师号,姓名,性别,职称,系主任}
  1. 同一具有相同码的关系模式可合并
    • 目的:减少系统中的关系个数。
    • 合并方法:将其中一个关系模式的全部属性加入到另一个关系模式中,然后去掉其中的同义属性(可能同名也可能不同名),并适当调整属性的次序。
      例如,某关系模式记录了学生的身份证号:
        身份证(学号,身份证号)
          可以与学生关系模式合并为:
        学生(学号,姓名,出生日期,身份证号,……) 
      

向特定DBMS规定的模型进行转换

  • 一般的数据模型还需要向特定DBMS规定的模型进行转换。
  • 转换的主要依据是所选用的DBMS的功能及限制。 没有通用规则。
  • 对于关系模型来说,这种转换通常都比较简单。

二、数据模型的优化

  • 数据库逻辑设计的结果不是唯一的。
  • 得到初步数据模型后,还应该适当地修改、调整数据模型的结构,以进一步提高数据库应用系统的性能,这就是数据模型的优化。
  • 关系数据模型的优化通常以规范化理论为指导。

三、关系模式规范化

  • 应用规范化理论对上述产生的关系的逻辑模式进行初步优化,以减少乃至消除关系模式中存在的各种异常,改善完整性、一致性和存储效率。
  • 规范化理论是数据库逻辑设计的指南和工具,规范化过程可分为两个步骤:
    1. 确定规范式级别
      考查关系模式的函数依赖关系,确定范式等级,逐一分析各关系模式,考查是否存在部分函数依赖,传递函数依赖等,确定它们分别属于第几范式。
    2. 实施规范化处理
      确定范式级别后,利用第4章的规范化理论,逐一考察各个关系模式,根据应用要求,判断它们是否满足规范要求,可用已经介绍过的规范化方法和理论将关系模式规范化。

综合以上数据库的设计过程,规范化理论在数据库设计中有如下几方面的应用:

  • 在需求分析阶段,用数据依赖概念分析和表示各个数据项之间的联系。
  • 在概念结构设计阶段,以规范化理论为指导,确定关系键,消除初步E-R图中冗余的联系。
  • 在逻辑结构设计阶段,从E-R图向数据模型转换过程中,用模式合并与分解方法达到规范化级别。

四、模式评价与改进
关系模式的规范化不是目的而是手段,数据库设计的目的是最终满足应用需求。因此,为了进一步提高数据库应用系统的性能,还应该对规范化后产生的关系模式进行评价、改进,经过反复多次的尝试和比较,最后得到优化的关系模式。

模式评价:

  • 目的是检查所设计的数据库模式是否满足用户的功能要求、效率,确定加以改进的部分。
  • 模式评价包括:
    • 功能评价
    • 性能评价
  1. 功能评价

    • 功能评价指对照需求分析的结果,检查规范化后的关系模式集合是否支持用户所有的应用要求。
    • 关系模式必须包括用户可能访问的所有属性。在涉及多个关系模式的应用中,应确保联接后不丢失信息。如果发现有的应用不被支持,或不完全被支持,则应该改进关系模式。
    • 发生这种问题的原因可能是在逻辑设计阶段,也可能是在需求分析或概念设计阶段。是哪个阶段的问题就返回到哪个阶段去,因此有可能对前两个阶段再进行评审,解决存在的问题。
  2. 性能评价

  • 对于目前得到的数据库模式,由于缺乏物理设计所提供的数量测量标准和相应的评价手段,所以性能评价是比较困难的,只能对实际性能进行估计,包括逻辑记录的存取数、传送量以及物理设计算法的模型等。

模式改进

  • 根据模式评价的结果,对已生成的模式进行改进。
  • 如果因为需求分析、概念设计的疏漏导致某些应用不能得到支持,则应该增加新的关系模式或属性。
  • 如果因为性能考虑而要求改进,则可采用合并或分解的方法。
  1. 合并

    • 如果有若干个关系模式具有相同的主键,并且对这些关系模式的处理主要是查询操作,而且经常是多关系的查询,那么可对这些关系模式按照组合使用频率进行合并。
    • 这样便可以减少联接操作而提高查询效率。
  2. 分解

    • 为了提高数据操作的效率和存储空间的利用率,最常用和最重要的模式优化方法就是分解。
    • 根据应用的不同要求,可以对关系模式进行垂直分解和水平分解。
      ①水平分解

      • 是把关系的元组分为若干子集合,定义每个子集合为一个子关系
      • 对于经常进行大量数据的分类条件查询的关系,可进行水平分解,这样可以减少应用系统每次查询需要访问的记录数,从而提高了查询性能。
      • 例如,有学生关系(学号,姓名,类别……),其中类别包括大专生、本科生和研究生。如果多数查询一次只涉及其中的一类学生,就应该把整个学生关系水平分割为大专生、本科生和研究生三个关系。

        ②垂直分解

      • 把关系模式的属性分解为若干子集合,形成若干子关系模式。垂直分解的原则是把经常一起使用的属性分解出来,形成一个子关系模式 。
      • 垂直分解可以提高某些事务的效率,但也有可能使另一些事务不得不执行连接操作,从而降低了效率。因此是否要进行垂直分解要看分解后的所有事务的总效率是否得到了提高。垂直分解要保证分解后的关系具有无损连接性和函数依赖保持性。
      • 例如,有教师关系(教师号,姓名,性别,年龄,职称,工资,岗位津贴,住址,电话),如果经常查询的仅是前六项,而后三项很少使用,则可以将教师关系进行垂直分割,得到两个教师关系:
        教师关系1(教师号,姓名,性别,年龄,职称,工资)
        教师关系2(教师号,岗位津贴,住址,电话)
        这样,便减少了查询的数据传递量,提高了查询速度。

经过多次的模式评价和模式改进之后,最终的数据库模式得以确定。逻辑设计阶段的结果是全局逻辑数据库结构。对于关系数据库系统来说,就是一组符合一定规范的关系模式组成的关系数据库模型。
数据库系统的数据物理独立性特点消除了由于物理存储改变而引起的对应程序的修改。标准的DBMS例行程序应适用于所有的访问,查询和更新事务的优化应当在系统软件一级上实现。这样,逻辑数据库确定之后,就可以开始进行应用程序设计了。

数据库的物理设计

数据库最终要存储在物理设备上。对于给定的逻辑数据模型,选取一个最适合应用环境的物理结构的过程,称为数据库物理设计。物理设计的任务是为了有效地实现逻辑模式,确定所采取的存储策略。此阶段是以逻辑设计的结果作为输入,结合具体DBMS的特点与存储设备特性进行设计,选定数据库在物理设备上的存储结构和存取方法。
数据库的物理设计可分为两步:
确定物理结构,在关系数据库中主要指存取方法和存储结构;
评价物理结构,评价的重点是时间和空间效率。

  1. 存储记录结构的设计

    • 在物理结构中,数据的基本存取单位是存储记录。
    • 有了逻辑记录结构以后,就可以设计存储记录结构,一个存储记录可以和一个或多个逻辑记录相对应。存储记录结构包括记录的组成、数据项的类型和长度,以及逻辑记录到存储记录的映射。
    • 某一类型的所有存储记录的集合称为“文件”,文件的存储记录可以是定长的,也可以是变长的。
  2. 索引的设计

    • 存储记录是属性值的集合,主关系键可以惟一确定一个记录,而其他属性的一个具体值不能惟一确定是哪个记录。在主关系键上应该建立惟一索引,这样不但可以提高查询速度,还能避免关系键重复值的录入,确保了数据的完整性。
      在数据库中,用户访问的最小单位是属性。如果对某些非主属性的检索很频繁,可以考虑建立这些属性的索引文件。索引文件对存储记录重新进行内部链接,从逻辑上改变了记录的存储位置,从而改变了访问数据的入口点。关系中数据越多索引的优越性也就越明显。

    • 建立多个索引文件可以缩短存取时间,但是增加了索引文件所占用的存储空间以及维护的开销。因此,应该根据实际需要综合考虑。

  3. 数据存放位置的设计

  • 为了提高系统性能,应该根据应用情况将数据的易变部分、稳定部分、经常存取部分和存取频率较低部分分开存放。
  • 例如,目前许多计算机都有多个磁盘,因此可以将表和索引分别存放在不同的磁盘上,在查询时,由于两个磁盘驱动器并行工作,可以提高物理读写的速度。
  • 在多用户环境下,可能将日志文件和数据库对象(表、索引等)放在不同的磁盘上,以加快存取速度。另外,数据库的数据备份、日志文件备份等,只在数据库发生故障进行恢复时才使用,而且数据量很大,可以存放在磁带上,以改进整个系统的性能。
  1. 系统配置的设计
  • DBMS产品一般都提供了一些系统配置变量、存储分配参数,供设计人员和DBA对数据库进行物理优化。系统为这些变量设定了初始值,但是这些值不一定适合每一种应用环境,在物理设计阶段,要根据实际情况重新对这些变量赋值,以满足新的要求。
  • 系统配置变量和参数很多,例如,同时使用数据库的用户数、同时打开的数据库对象数、内存分配参数、缓冲区分配参数(使用的缓冲区长度、个数)、存储分配参数、数据库的大小、时间片的大小、锁的数目等,这些参数值影响存取时间和存储空间的分配,在物理设计时要根据应用环境确定这些参数值,以使系统的性能达到最优。

评价物理结构

  • 和前面几个设计阶段一样,在确定了数据库的物理结构之后,要进行评价,重点是时间和空间的效率。
  • 如果评价结果满足设计要求,则可进行数据库实施。
  • 实际上,往往需要经过反复测试才能优化物理设计。

数据库实施和维护

数据库实施是指根据逻辑设计和物理设计的结果,在计算机上建立起实际的数据库结构、装入数据、进行测试和试运行的过程。
数据库实施主要包括以下工作:

  1. 建立实际数据库结构;
  2. 装入数据;
  3. 应用程序编码与调试;
  4. 数据库试运行;
  5. 整理文档。

数据库运行和维护

  • 数据库试运行结果符合设计目标后,数据库就投入正式运行,进入运行和维护阶段。数据库系统投入正式运行,标志着数据库应用开发工作的基本结束,但并不意味着设计过程己经结束。
  • 由于应用环境不断发生变化,用户的需求和处理方法不断发展,数据库在运行过程中的存储结构也会不断变化,从而必须修改和扩充相应的应用程序。
  • 数据库运行和维护阶段的主要任务包括以下三项内容:
    1. 维护数据库的安全性与完整性;
    2. 监测并改善数据库性能;
    3. 重新组织和构造数据库。

维护数据库的安全性与完整性

  • 按照设计阶段提供的安全规范和故障恢复规范,DBA要经常检查系统的安全是否受到侵犯,根据用户的实际需要授予用户不同的操作权限。
  • 数据库在运行过程中,由于应用环境发生变化,对安全性的要求可能发生变化,DBA要根据实际情况及时调整相应的授权和密码,以保证数据库的安全性。
  • 同样数据库的完整性约束条件也可能会随应用环境的改变而改变,这时DBA也要对其进行调整,以满足用户的要求。
  • 另外,为了确保系统在发生故障时,能够及时地进行恢复,DBA要针对不同的应用要求定制不同的转储计划,定期对数据库和日志文件进行备份,以使数据库在发生故障后恢复到某种一致性状态,保证数据库的完整性。

监测并改善数据库性能

  • 目前许多DBMS产品都提供了监测系统性能参数的工具,DBA可以利用系统提供的这些工具,经常对数据库的存储空间状况及响应时间进行分析评价;
  • 结合用户的反应情况确定改进措施;
  • 及时改正运行中发现的错误;
  • 按用户的要求对数据库的现有功能进行适当的扩充。但要注意在增加新功能时应保证原有功能和性能不受损害。

重新组织和构造数据库

  • 数据库建立后,除了数据本身是动态变化以外,随着应用环境的变化,数据库本身也必须变化以适应应用要求。
  • 数据库运行一段时间后,由于记录的不断增加、删除和修改,会改变数据库的物理存储结构,使数据库的物理特性受到破坏,从而降低数据库存储空间的利用率和数据的存取效率,使数据库的性能下降。因此,需要对数据库进行重新组织,即重新安排数据的存储位置,回收垃圾,减少指针链,改进数据库的响应时间和空间利用率,提高系统性能。这与操作系统对“磁盘碎片”的处理的概念相类似。

  • DBMS一般都提供了重新组织和构造数据库的应用程序,以帮助DBA完成数据库的重组和重构工作。

  • 只要数据库系统在运行,就需要不断地进行修改、调整和维护。一旦应用变化太大,数据库重新组织也无济于事,这就表明数据库应用系统的生命周期结束,应该建立新系统,重新设计数据库。从头开始数据库设计工作,标志着一个新的数据库应用系统生命周期的开始。

小结

  • 本章介绍了数据库设计的六个阶段,包括:系统需求分析、概念结构设计、逻辑结构设计、物理设计、数据库实施、数据库运行与维护。对于每一阶段,都分别详细讨论了其相应的任务、方法和步骤。
  • 需求分析是整个设计过程的基础,需求分析做得不好,可能会导致整个数据库设计返工重做。
  • 将需求分析所得到的用户需求抽象为信息结构即概念模型的过程就是概念结构设计,概念结构设计是整个数据库设计的关键所在,这一过程包括设计局部E-R图、综合成初步E-R图、E-R图的优化。
  • 将独立于DBMS的概念模型转化为相应的数据模型,这是逻辑结构设计所要完成的任务。一般的逻辑设计分为三步:初始关系模式设计,关系模式规范化,模式的评价与改进。
  • 物理设计就是为给定的逻辑模型选取一个适合应用环境的物理结构,物理设计包括确定物理结构和评价物理结构两步。
  • 根据逻辑设计和物理设计的结果,在计算机上建立起实际的数据库结构,装入数据,进行应用程序的设计,并试运行整个数据库系统,这是数据库实施阶段的任务。
  • 数据库设计的最后阶段是数据库的运行与维护,包括维护数据库的安全性与完整性,监测并改善数据库性能,必要时需要进行数据库的重新组织和构造。

数据库编程

嵌入式SQL

SQL语言是应用程序和数据库之间的主要编程接口。
使用SQL语言编写访问数据库的代码时,可用两种方法存储和执行这些代码。

  • 在客户端存储代码,并创建向数据库服务器发送的SQL命令
    • 将SQL语句存储在数据库服务器端,然后由应用程序调用执行这些SQL语句。

存储在数据库服务器端供客户端调用执行的SQL语句就是存储过程。

嵌入式SQL的处理过程

主语言:
嵌入式SQL是将SQL语句嵌入程序设计语言中,被嵌入的程序设计语言,如C、C++、Java称为宿主语言
处理过程:预编译,如下图所示

在ESQL中,为了区分SQL语句和主语言语句,所有SQL语句都必须加前缀EXEC SQL,以(;)结束称为一个程序片断:

EXEC SQL<sql语句>;

嵌入式SQL语句与主语言之间的通信

将SQL嵌入到高级语言中混合编程,程序会有两种不同计算模型的语句

  • sql语句
    • 描述性的面向集合的语句
    • 负责操纵数据库
  • 高级语言语句
    • 过程性的面向记录的语句
    • 负责控制程序流程

数据库工作单元与源程序工作单元之间的通信:

  1. SQL通信区
  • 向主语言传递SQL语句的执行状态信息
  • 使主语言能够根据此控制程序流程

SQLCA:SQL Communication Area

  • SQLCA是一个数据结构
  • SQLCA的用途:

    • SQL语句执行后,系统反馈给应用程序信息
      描述当前系统工作状态
      描述运行环境

    • 将这些信息送到SQL通信区中

    • 应用程序从SQL通信区中取出这些状态信息,从而决定接下来执行的语句。
  • SQLCA使用方法:
    定义SQLCA
    用EXEC SQL INCLUDE SQLCA定义
    使用SQLCA
    SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE
    如果SQLCODE等于预定义的常量SUCCESS,则表示SQL语句成功,否则表示出错
    应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值,以了解该SQL语句执行情况并做相应处理
  1. 主变量
  • 主语言向SQL语句提供参数
  • 将SQL语句查询数据库的结果交主语言进一步处理
  • 主变量的类型:
    输入主变量
    输出主变量
    一个主变量有可能既是输入主变量又是输出主变量
    指示变量:一个主变量可以附带一个指示变量(Indicator Variable)
    什么是指示变量: 指示主变量的值或者条件
①在SQL语句中使用主变量和指示变量的方法

说明主变量和指示变量
BEGIN DECLARE SECTION
………
……… (说明主变量和指示变量)
………
END DECLARE SECTION

②使用主变量

说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现
为了与数据库对象名(表名、视图名、列名等)区别,SQL语句中的主变量名前要加冒号(:)作为标志
这些变量由宿主语言的程序定义,并用SQL的DECLARE语句说明。
Sql之外,主变量可以直接使用

③使用指示变量

指示变量前也必须加冒号标志
必须紧跟在所指主变量之后

  1. 游标
    SQL语言与主语言具有不同数据处理方式
    SQL语言是面向集合的,一条SQL语句原则上可以产生或处理多条记录
    主语言是面向记录的,一组主变量一次只能存放一条记录
    仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求
    嵌入式SQL引入了游标的概念,用来协调这两种不同的处理方式

    游标的定义
    游标是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果
    每个游标区都有一个名字
    用户可以用SQL语句逐一从游标中获取记录,并赋给主变量,交由主语言进一步处理

    使用游标的步骤

    1. 说明游标(DECLARE)
    2. 打开游标(OPEN)
    3. 推进游标指针并取当前记录( FETCH )
    4. 关闭游标(CLOSE)

①声明游标
使用DECLARE语句
语句格式

1
2
EXEC SQL DECLARE <游标名> CURSOR
FOR <SELECT语句>;

功能
是一条说明性语句,这时DBMS并不执行SELECT指定的查询操作。

②打开游标
使用OPEN语句
语句格式

1
EXEC SQL OPEN <游标名>;

功能
打开游标实际上是执行相应的SELECT语句,把所有满足查询条件的记录从指定表取到缓冲区中
这时游标处于活动状态,指针指向查询结果集中第一条记录

③推进游标指针并取当前记录
使用FETCH语句
语句格式

1
2
3
EXEC SQL FETCH [[NEXT|PRIOR|
FIRST|LAST] FROM] <游标名>
INTO <主变量>[<指示变量>][,<主变量>[<指示变量>]]...;

功能
指定方向推动游标指针,然后将缓冲区中的当前记录取出来送至主变量供主语言进一步处理
NEXT|PRIOR|FIRST|LAST:指定推动游标指针的方式
NEXT:向前推进一条记录
PRIOR:向回退一条记录
FIRST:推向第一条记录
LAST:推向最后一条记录
缺省值为NEXT

④关闭游标
使用CLOSE语句
语句格式

1
EXEC SQL CLOSE <游标名>;

功能
关闭游标,释放结果集占用的缓冲区及其他资源
说明
游标被关闭后,就不再和原来的查询结果集相联系
被关闭的游标可以再次被打开,与新的查询结果相联系

  1. 建立和关闭数据库连接
    建立数据库连接
1
2
EXEC SQL CONNECT TO target [AS connection-name] 
[USER user-name];

target是要连接的数据库服务器:

  • 常见的服务器标识串,如<dbname>@:
  • 包含服务器标识的SQL串常量
  • DEFAULT
    connect-name是可选的连接名,连接必须是一个有效标识符
    在整个程序内只有一个连接时可以不指定连接名

关闭数据库连接

1
EXEC SQL DISCONNECT [connection];

程序运行过程中可以修改当前连接 :

1
EXEC SQL SET CONNECTION connection-name | DEFAULT;

  1. 程序实例

[例1]依次检查某个系的学生记录,交互式更新某些学生年龄。

1
2
3
4
5
6
7
8
9
10
EXEC SQL BEGIN DEC LARE SECTION;  /*主变量说明开始*/ 
char deptname[64];
char HSno[64];
char HSname[64];
char HSsex[64];
int HSage;
int NEWAGE;
EXEC SQL END DECLARE SECTION; /*主变量说明结束*/
long SQLCODE;
EXEC SQL INCLUDE sqlca; /*定义SQL通信区*/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
int main(void)                    /*C语言主程序开始*/
{int count = 0;
char yn; /*变量yn代表yes或no*/
printf("Please choose the department name(CS/MA/IS): ");
scanf("%s", deptname); /*为主变量deptname赋值*/
EXEC SQL CONNECT TO TEST@localhost:54321 USER
"SYSTEM" /"MANAGER"; /*连接数据库TEST*/
EXEC SQL DECLARE SX CURSOR FOR /*定义游标*/
SELECT Sno, Sname, Ssex, Sage /*SX对应语句的执行结果*/
FROM Student
WHERE SDept = :deptname;
EXEC SQL OPEN SX; /*打开游标SX便指向查询结果的第一行*/

for ( ; ; ) /*用循环结构逐条处理结果集中的记录*/
{ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex,:HSage;
/*推进游标,将当前数据放入主变量*/
if (sqlca.sqlcode != 0) /* sqlcode != 0,表示操作不成功*/
break; /*SQLCA状态信息决定退出循环*/
if(count++ == 0) /*如果是第一行的话,先打出行头*/
printf("\n%-10s %-20s %-10s %-10s\n", "Sno", "Sname", "Ssex", "Sage");
printf("%-10s %-20s %-10s %-10d\n", HSno, HSname, HSsex, HSage); /*打印查询结果*/
printf(“UPDATE AGE(y/n)?”); /*询问用户是否要更新年龄*/
do{ scanf("%c",&yn);}
while(yn != 'N' && yn != 'n' && yn != 'Y' && yn != 'y');

if (yn == 'y' || yn == 'Y') /*如果选择更新操作*/
{ printf("INPUT NEW AGE:");
scanf(“%d”,&NEWAGE); /*输入新年龄到主变量中*/
EXEC SQL UPDATE Student /*嵌入式SQL*/
SET Sage = :NEWAGE
WHERE CURRENT OF SX ;
} /*对当前游标指向的学生年龄进行更新*/
}

EXEC SQL CLOSE SX; /*关闭游标SX不再和查询结果对应*/
EXEC SQL COMMIT WORK; /*提交更新*/
EXEC SQL DISCONNECT TEST; /*断开数据库连接*/ }
  1. 不用游标的SQL语句
    不用游标的SQL语句的种类
    • 说明性语句
    • 数据定义语句
    • 数据控制语句
    • 查询结果为单记录的SELECT语句
    • 非CURRENT形式的增删改语句

一、查询结果为单记录的 SELECT语句
这类语句不需要使用游标,只需要用INTO子句指定存放查询结果的主变量

[例2] 根据学生号码查询学生信息。假设已经把要查询的学生的学号赋给了主变量givensno。

1
2
3
EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept        	  INTO  :Hsno, : Hname ,:Hsex,:Hage,:Hdept
FROM Student
WHERE Sno=:givensno;

(1) INTO子句、WHERE子句和HAVING短语的条件表达式中均可以使用主变量
(2)查询返回的记录中,可能某些列为空值NULL。
(3) 如果查询结果实际上并不是单条记录,而是多条记录,则程序出错,RDBMS会在SQLCA中返回错误信息

[例3] 查询某个学生选修某门课程的成绩。假设已经把将要查询的学生的学号赋给了主变量givensno,将课程号赋给了主变givencno。

1
2
3
4
5
6
EXEC SQL SELECT Sno,Cno,Grade
INTO :Hsno,:Hcno,:Hgrade:Gradeid
/*指示变量Gradeid*/
FROM SC
WHERE Sno=:givensno AND
Cno=:givencno;

如果Gradeid < 0,不论Hgrade为何值,均认为该学生成绩为空值。

二、非CURRENT形式的增删改语句

在UPDATE的SET子句和WHERE子句中可以使用主变量,SET子句还可以使用指示变量
[例4] 修改某个学生选修1号课程的成绩。

1
2
3
4
EXEC SQL UPDATE SC  SET Grade=:newgrade       
/*修改的成绩已赋给主变量*/
WHERE Sno=:givensno;
/*学号赋给主变量givensno*/

[例5] 将计算机系全体学生年龄置NULL值。

1
2
3
4
Sageid=-1;
EXEC SQL UPDATE Student
SET Sage=:Raise :Sageid
WHERE Sdept= ‘CS’;

将指示变量Sageid赋一个负值后,无论主变量Raise为何值,RDBMS都会将CS系所有学生的年龄置空值 。
等价于:

1
2
3
EXEC SQL UPDATE Student
SET Sage=NULL
WHERE Sdept= 'CS'

[例6] 某个学生退学了,现要将有关他的所有选课记录删除掉。假设该学生的姓名已赋给主变量stdname。

1
2
3
4
5
6
EXEC SQL DELETE
FROM SC
WHERE Sno=
(SELECT Sno
FROM Student
WHERE Sname=:stdname);

[例7] 某个学生新选修了某门课程,将有关记录插入SC表中。假设插入的学号已赋给主变量stdno,课程号已赋给主变量couno。

1
2
3
4
gradeid=-1;            /*用作指示变量,赋为负值*/
EXEC SQL INSERT
INTO SC(Sno,Cno,Grade)
VALUES(:stdno,:couno,:gr :gradeid);

由于该学生刚选修课程,成绩应为空,所以要把指示变量赋为负值

  1. 必须使用游标的SQL语句
    一、 查询结果为多条记录的SELECT语句
    当SELECT语句查询结果是多个元组时,此时宿主语言程序无法使用,一定要用游标机制把多个元组一次一个地传送给宿主语言程序处理。
    二、CURRENT形式的UPDATE和DELETE语句
    在游标处于活动状况时,可以修改或删除游标指向的元组。

CURRENT形式的UPDATE语句和DELETE语句的用途
面向集合的操作
一次修改或删除所有满足条件的记录

如果只想修改或删除其中某个记录
用带游标的SELECT语句查出所有满足条件的记录
从中进一步找出要修改或删除的记录
用CURRENT形式的UPDATE语句和DELETE语句修改或删除之
UPDATE语句和DELETE语句中的子句:
WHERE CURRENT OF <游标名>
表示修改或删除的是最近一次取出的记录,即游标指针指向的记录


动态SQL

静态嵌入式SQL

  • 静态嵌入式SQL语句能够满足一般要求
  • 无法满足要到执行时才能够确定要提交的SQL语句

动态嵌入式SQL

  • 允许在程序运行过程中临时“组装”SQL语句
  • 支持动态组装SQL语句和动态参数两种形式

一、SQL语句主变量:

  • 程序主变量包含的内容是SQL语句的内容,而不是原来保存数据的输入或输出变量
  • SQL语句主变量在程序执行期间可以设定不同的SQL语句,然后立即执行

[例9] 创建基本表TEST

1
2
3
4
5
6
7
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "CREATE TABLE test(a int);";
/* SQL语句主变量 */
EXEC SQL END DECLARE SECTION;
... ...
EXEC SQL EXECUTE IMMEDIATE :stmt;
/* 执行语句 */

二、动态参数
动态参数
SQL语句中的可变元素
使用参数符号(?)表示该位置的数据在运行时设定
和主变量的区别
动态参数的输入不是编译时完成绑定
而是通过 (prepare)语句准备主变量和执行(execute)时绑定数据或主变量来完成

使用动态参数的步骤:

  1. 声明SQL语句主变量。
  2. 准备SQL语句(PREPARE)。
    EXEC SQL PREPARE <语句名> FROM ;

  3. 执行准备好的语句(EXECUTE)

1
2
3
EXEC SQL EXECUTE <语句名>
[INTO <主变量表>]
[USING <主变量或常量>];

[例10]向TEST中插入元组。

1
2
3
4
5
6
7
8
9
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "INSERT INTO test VALUES(?);";
/*声明SQL主变量 */
EXEC SQL END DECLARE SECTION;
... ...
EXEC SQL PREPARE mystmt FROM :stmt; /* 准备语句 */
... ...
EXEC SQL EXECUTE mystmt USING 100; /* 执行语句 */
EXEC SQL EXECUTE mystmt USING 200; /* 执行语句 */


存储过程

PL/SQL 块结构

基本的SQL是高度非过程化的语言,ESQL将SQL嵌入程序设计语言,借助高级语言的控制功能实现过程化,PL/SQL是对SQL的扩展,增加了过程化的语句功能。
PL/SQL 程序的基本结构是块,所有的PL/SQL程序都是由块组成的,这些块之间可以互相嵌套,每个块完成一个逻辑操作。如下图

常量变量的定义

  1. PL/SQL中定义变量的语法形式是:
1
2
变量名 数据类型[ [ NOT NULL] : = 初值表达式 ] 或
变量名 数据类型[ [NOT NULL ] 初值表达式]
  1. 常量的定义类似于变量的定义
    常量名 数据类型CONSTANT : = 常量表达式
    常量必须要给一个值,并且该值在存在期间或者常量的作用域不能改变,如果视图修改它,PL/SQL将返回一个异常。

  2. 赋值语句
    变量名称 : = 表达式

控制结构

PL/SQL提供了流程控制语句,主要有条件控制语句和循环控制语句,这些语句的语法、语义和一般的高级语言类似

一、条件控制语句
一般有三种形式的if语句:IF-THEN, IF -THEN -ELSE 和嵌套 IF 语句

1. IF condition THEN
    Sequence_of_statement;  /*条件为真时语句序列才会被执行*/
   END IF   /*条件为假货null时什么也不做,控制转移至下一个语句*/
2. IF condition THEN
      Sequence_of_statements1;  /*条件为真时执行语句序列1*/
   ELSE
      Sequence_of_statements2;  /*条件为假货null时执行语句序列2*/
   END IF;
3. 在THEN 和ELSE子句中还可以再包括IF语句,即IF语句可以嵌套 

二、循环控制语句
有三种:LOOP . WHILE-LOOP 和 FOR-LOOP

  1. 最简单的循环语句LOOP

    1
    2
    3
    LOOP
    Sequence_of_statements; /*循环体,一组PL/SQL语句*/
    END LOOP;
  2. WHILE_LOOP

1
2
3
WHILE condition LOOP
Sequence_of_statements;
END LOOP;

每次执行循环语句的时候,先对条件进行求值,如果条件为真,则执行循环体内的语句序列,如果条件为假,则跳过循环并且把控制传递给下一个语句

  1. FOR-LOOP
1
2
3
FOR count IN [REVERSE]bound1 .... bound2 LOOP
Sequence_of_statements;
END LOOP;

FOR循环的基本执行过程:

  • 将count设置为循环下界bound1,检查它是否小于上界bound2
  • 当指定REVERSER时则将count设置为循环的上界bound2,检查count是否大于下界bound1
  • 如果越界则执行跳出循环,否则执行循环体,然后按照步长(+1或者-1)更新count的值,重新判断条件

三、错误处理
如果PL/SQL在执行时出现异常,则应该在异常处停下来。SQL标准对数据库服务器提供什么样的异常处理作出了建议,要求PL/SQL管理器提供完善的异常处理机制,相对于ESQL简单的提供执行状态信息sqlcode,这里的异常处理就复杂多了。

存储过程

PL/SQL块有两种类型,即命名块和匿名块,上面介绍的是匿名块。匿名块每次执行都是要进行编译,不能被存储到数据库中,也不能在其他的PL/SQL块中调用。存储过程和函数是命名块,他们被编译后保存在数据库中,可以被反复调用,运行速度较快。

一、存储过程的优点
由PL/SQL语句书写的过程,经编译和优化后存储在数据库服务器中,使用时只要调用即可。
存储过程的优点:
(1) 运行效率高
(2) 降低了客户机和服务器之间的通信量
(3) 方便实施企业规则

允许模块化程序设计

  • 只需创建一次并存储在数据库中,就可以在应用程序中反复调用该存储过程

改善性能—-运行效率高

  • 在创建存储过程时对代码进行分析和优化,并在第一次执行时进行语法检查和编译,将编译好的可执行代码存储在内存的一个专门缓冲区中,以后再执行此存储过程时,只需直接执行内存中的可执行代码即可。

减少网络流量-降低了客户机和服务器之间的通信量

  • 只需要一条执行存储过程的代码即可实现,因此,不再需要在网络中传送大量的代码。

存储过程功能
接受输入参数并以输出参数的形式将多个值返回给调用者。
包含执行数据库操作的语句。
将查询语句执行结果返回到客户端内存中。

二、存储过程的用户接口
用户通过下面的SQL语句创建、重新命名、执行和删除存储过程:

  1. 创建存储过程
1
2
3
CREATE Procedure 过程名([参数1,参数2, .... ])/* 存储过程首部*/
AS
<PL/SQL块> /*存储过程体,描述该存储过程的操作*/

存储过程包括过程首部和过程体。
过程名:是数据库服务器合法的对象标识
参数列表:用名字来表示调用时给出的参数值,必须指定值的数据类型,存储参数也可以定义输入参数,输出参数或输入/输出参数,默认为输入参数
过程体:是一个,包括声明部分和可执行语句部分,的基本结构已经在前面有介绍了。

例1.不带参数的存储过程。查询计算机系学生的考试情况,列出学生的姓名、课程名和考试成绩。

1
2
3
4
5
6
7
CREATE  PROCEDURE  p_StudentGrade1
AS
SELECT Sname, Cname, Grade
FROM Student , SC,Course
WHERE Student.sno=Sc.sno
and Sc.cno=Course.cno
and Dept = '计算机系'

执行此存储过程:
EXEC p_StudentGrade1

例2.带输入参数的存储过程。查询某个指定系学生的考试情况,列出学生的姓名、所在系、课程名和考试成绩。

1
2
3
4
5
6
7
8
CREATE  PROCEDURE  p_StudentGrade2
@dept char(20)
AS
SELECT Sname, Dept, Cname, Grade
FROM Student , SC
WHERE Student.sno=Sc.sno
and Sc.cno=Course.cno
and Dept = @dept

执行存储过程,查询信息管理系学生的修课情况
EXEC p_StudentGrade2 ‘信息管理系’

例 利用存储过程来实现下面的应用,从一个账户转指定的数额的快想到另一个账户中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE PROCEDURE TRANSFER(inAcount INT ,outAcount INT ,amount FLOAT)
AS DECLARE
totalDeposit FLOAT;
BEGIN /*检查转出账户的余额*/
SELECT total INTO totalDeposit FROM ACCOUNT WHERE ACCOUNTNUM=outAccount;
IF totalDeposit IS NULL THEN /*账户不存在或账户中没有存款*/
ROLLBACK;
RETURN;
END IF;
IF totalDeposit <amount THEN /*账户存款不足*/
ROLLBACK;
RETURN;
END IF;
UPDATE account SET total != total - amount WHERE ACCOUNTNUM =outAccount; /*修改转出账户,减去转出额*/
UPDATE account SET total = total + amount WHERE ACCOUNTNUM =inAccount; /*修改转入账户,增加转出额*/
COMMIT; /*提交转账事务*/
END;
  1. 执行存储过程

CALL/PERFORM Procedure 过程名([参数1,参数2, … ]);

使用CALL或者PERFORM 等方式集火存储过程的执行,在PL/SQL中,数据库支持在过程体中调用其他存储过程

例 从账户1234567转一万元到1234568中

CALL Procedure TRANSFER (1234567 ,1234568 ,10000);

  1. 删除存储过程
    DROP PROCEDURE 过程名();

三、游标

和嵌入式SQL一样,在PL/SQL中如果select语句只返回一条记录,可以将该结果存放到变量中,当查询返回多条记录时,用游标对结果集进行处理,一个游标与一个SQL语句相关联。


ODBC编程

数据库互连概述

ODBC产生的原因

  • 由于不同 的数据库管理系统的存在,在某个RDBMS下编写的应用程序就不能在另一个RDBMS下运行,
  • 许多应用程序需要共享多个部门的数据资源,访问不同的RDBMS。

ODBC:

  • 是微软公司开放服务体系中有关数据库的一个组成部分
  • 建立了一组规范
  • 提供了一组访问数据库的标准API

ODBC的约束力:

  • 规范应用开发
  • 规范RDBMS应用接口

ODBC工作原理概述

分四个部分:用户应用程序,驱动程序管理器(ODBC Driver),数据库驱动程序(ODBC Driver),数据源(如RDBMS和数据库)

一、应用程序
提供用界面、用户逻辑和事务逻辑,应用程序调用的是标准的ODBC函数和SQL语句,应用层使用ODBC API 调用接口与数据库进行交互,使用ODBC来开发应用从系统的程序简称为ODBC应用程序,包括的内容有:

  • 请求连接数据库;
  • 向数据源发送SQL语句
  • 为SQL语句执行结果分配存储空间,定义所读取的数据格式
  • 获取数据库操作结果 ,或处理错误
  • 进行数据处理并向用户提交处理结果
  • 请求事务的提交和回滚操作
  • 断开与数据源的连接

二、驱动程序管理器

驱动程序管理器用来管理各种驱动程序的。管理应用程序和驱动程序之间的通信。主要功能包括了装载OBDC驱动程序、选择和连接正确驱动程序、管理数据源、检查ODBC调用参数的合法性及记录ODBC函数的调用等,当应用需要时返回驱动程序的有关信息。
ODBC驱动程序管理器可以建立、配置或删除数据源,并查看系统当前所安装的数据库ODBC驱动程序。
下图为ODBC应用系统的体系结构

三、数据库驱动程序

ODBC通过驱动程序来提供应用系统与数据库平台的独立性
ODBC应用程序不能直接存取数据库,各种操作请求由驱动程序管理器提交给某个RDBMS的ODBC驱动程序,通过调用驱动程序所支持的函数来存取数据库。数据库的操作结果也铜鼓哦驱动程序返回给应用程序。如果应用程序要操纵不同的数据库,就要动态的链接到不同的驱动程序上。

目前的ODBC驱动程序分单束和多束两类。

  • 单束:数据源和应用程序在同一台机器上,驱动程序直接完成对数据的I/O操作,驱动程序相当于数据管理器。
  • 多束: 支持客户机/服务器、客户机/应用服务器/数据库服务器等网络环境下的数据访问,由驱动程序完成数据库访问请求的提交和结果集接手,应用程序使用驱动程序提供的结果集管理接口操纵执行后的结果集数据。

四、ODBC数据源管理

数据源是最终用户需要访问的数据,包含了数据库位置和数据库类型等信息,实际上是一种数据连接的抽象。

ODBC给每个被访问的数据源指定唯一的,数据源名(Data Source Name,简称DSN)并映射到所有必要的、用来存取数据的底层软件。
在连接中,用数据源名来代表用户名、服务器名、所连接的数据库名等。最终用户无需知道DBMS或其他数据管理软件、网络以及有关ODBC驱动程序的细节,数据源最终对用户是透明的。

ODBC API基础

ODBC应用程序接口要符合两个方面的一致性:

  • API一致性,API一致性级别有核心级,扩展1级,扩展2级
  • 语法一致性,语法一致性级别有最低限度SQL语法级,核心SQL语法级,扩展SQL语法级。

一、函数概述
ODBC3.0标准提供76个函数接口,大概分为

  • 分配和释放环境句柄,连接句柄,语句句柄
  • 连接函数(SQLDriverConnect等)
  • 与信息相关的函数(如获取描述信息函数SQLGetinfo ,SQLGetFunction);
  • 事务处理函数(如SQLEndTran);
  • 执行相关函数(SQLExecdirect,SQLExecute等);
  • 编目函数,提供了11个编目函数如SQLTables、SQLColumn等,应用程序可以通过对编目函数的调用来获取数据字典的信息如权限、表结构等。

二、句柄及其属性
句柄是32为整数值,代表一个指针。
ODBC3.0中句柄分为环境句柄、连接句柄、语句句柄或描述符句柄四类。对于每种句柄不同的驱动程序有不同的数据结构,这四种句柄的关系如下图

  1. 每个ODBC应用程序需要建立一个ODBC环境,分配一个环境句柄,存取数据的全局性背景如环境状态,当前环境状态诊断,当前在环境上分配的连接句柄等。
  2. 一个环境句柄可以建立多个连接句柄,每一个连接句柄实现与一个数据源之间的连接
  3. 在一个连接中可以建立多个语句句柄,它不只是一个SQL语句,还包括SQL语句产生的结果集以及相关的信息等
  4. 在ODBC3.0中提出了描述符句柄的概念,是描述SQL语句的参数,结果集列的元数据集合。

三、数据类型
ODBC定义两套数据类型:
SQL数据类型:用于数据源
C数据类型:用于应用程序的C代码

转换关系如下表
|       | SQL数据类型 | C数据类型 |
|—-|—-|—-|
| SQL数据类型 | 数据源之间 | 应用程序变量传送到语句参数(SQLBindparameter) |
| C数据类型 | 从结果集列中返回到应用程序变量(SQLBindcol) | 应用程序变量之间转换 |

ODBC的工作流程

一、配置数据源
有两种方法:

  • 运行数据源管理工具进行配置
  • 使用Driver Manager提供的ConfigDsn函数来增加、修改或删除数据源,这种方法适用于在应用程序中创建的临时使用的数据源

二、初始化环境
由于还没和具体的驱动程序相关联,不是由具体的数据库管理系统驱动程序来进行管理,而是由Driver Manager来进行控制,并配置环境属性,直到应用程序通过调用连接函数和某个数据源进行连接后,Driver Manager才调用所连的驱动程序中的SQLAllocHandle,来实现真正分配环境句柄的数据结构

三、建立连接

应用程序调用SQLAllocHandle分配连接句柄,通过SQLConnect、SQLDriverConnect或SQLBrowseConnect与数据源连接,其中SQLConnect是最简单的连接函数,输入参数为配置好的数据源名称、用户ID和口令。

四、分配语句句柄
在处理任何SQL语句之前,还需要首先分配一个语句句柄,语句句柄含有具体的SQL语句以及输出的结果集等信息。在后面的执行函数中,语句句柄都是必要的输入参数。

五、执行SQL语句
应用程序处理SQL语句的方式有两种:

  • 预处理(SQLPrepare,SQLExecute适用于语句的多次执行)
  • 直接执行(SQLExecdirect)

如果SQL语句含有参数,应用程序为每个参数调用SQLBindParameter,并把它们绑定至应用程序变量。这样应用程序直接通过改变应用程序缓冲区的内容从而在程序中动态的改变SQL语句的具体执行。
接下来的操作会根据语句的类型进行相应的处理

  • 有结果集的语句(Select或是编目函数),则进行结果集处理
  • 没有结果集的函数,可以直接利用本语句句柄继续执行新的语句或是获取行计数(本次执行所影响的行数)之后继续执行。

在插入数据时,采用预编译的方法,首先通过SQLPrepare来预处理SQL语句,将每一列绑定到用户缓冲区。

预编译的好处,只需要进行一次语法分析,可以多次执行同一语句。

六、结果集处理
应用程序通过SQLNumResultCols来获取结果集中的列数,通过SQLDescribeCol或者SQLColAttribute函数来获取结果集每一列的名称、数据类型、精度、范围。

ODBC中使用游标来处理结果集数据。
游标可分为:

  • forward-only游标:只能在结果集中向前滚动,是默认游标类型
  • 可滚动游标:分为静态、动态,码集驱动和混合型驱动四中

七、中止处理
处理结束后,应用程序将首先释放语句句柄,然后释放数据库连接,并与数据库服务器断开,最后释放ODBC环境。

流程如下图


Just for fun!
------------- 文章已经到尾 -------------