【系统分析师之路】第三章 数据库系统

【系统分析师之路】第三章 数据库系统

本章的重点有：数据库设计，ER模型，完整性约束，触发器，候选关键字，模式分解，分布式数据库，SQL语句，关系代数，数据仓库，范式，事务处理

• 数据库系统的各种概念

数据库管理系统DBMS	概念	是一种负责数据库定义，建立，操作，管理和维护的软件系统。其目的是保证数据库安全可靠，提高数据库应用的简明性和方便性，并有效的实现数据库三级之间的转化。
	主要职能	数据库的定义和建立，数据库的操作，数据库的控制，数据库的维护。
数据库系统DBS	概念	是实现有组织地，动态地存储大量关联数据，方便多用户访问计算机软件，硬件和数据资源组成的系统。它包括数据库，硬件软件和数据库管理员四个部分。
	设计目标	允许用户逻辑地处理数据，而不涉及这些数据在计算机中是怎么存储的。在数据组织和用户应用之间提供某种程度的独立性。
	三个级别	用户级（对应外模式），概念级和物理级（对应内模式）
	三级模式	外模式，概念模式，内模式。
	两级独立性	是指物理独立性和逻辑独立性。逻辑独立性比物理独立性更加难以实现。
数据模型	概念	一般先用图或表的形式抽象地反映数据彼此之间的关系。
	两个大类	实体联系模型和结构数据模型。
	三个组成部分	数据结构，数据操作和完整性约束。
	四个常用模型	层次模型，网状模型，关系模型和面向对象模型。

数据库系统其实就是一个分层结构的架构。外模式对应视图，概念模式对应关系表，内模式对应索引和文件。

•  数据库设计

四种数据模型的优缺点分析

关系模型	表示方式	优点	缺点
层次模型	树型结构	通过指针来实现，查找方便	只能表示1：n的关系。
网状模型	有向图	记录之间通过指针实现，易实现多对多	应用程序比较繁杂
关系模型	表格结构和外键	建立在严格的数学概念之上	存储路径透明，查询效率不高。
面向对象模型	面向对象观点	提高管理效率，降低用户使用复杂性	※1

※1：允许用自然并结合抽象的机制，对复杂对象建立模型。

• 关系代数

域	一组具有相同数据类型的值的集合。
笛卡尔积	第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。元素个数变成原先每个集合中元素个数的乘积
并	关系R和S有着相同的关系模式。将2个集合(或数据库查询的结果集)中的元素相加,并去掉重复元素(只留下1个)
差	关系R和S有着相同的关系模式。差就是过滤出属于R但是不属于S的关系组合。
交	取得将2个集合(或数据库查询的结果集)中共有的元素
投影	取得查询结果集中的部分字段,并去掉重复元素(数据库专有)
选择	选择操作在关系R中选择满足给定条件的所有元祖。它是系中找出满足给定条件的元组的操作。SQL语句中的【Where+条件】就是选择。
联接	将两个关系并成一个更宽的关系模式.从笛卡尔积中选择出满足条件的元祖，对应的SQL语句是JION ON
除法	同时从关系的水平和垂直方向进行运算，R（X,Y）和S(Y,Z)，XYZ是属性组。R/S应当满足元祖在X上的分量值x的象集，包含关系S在属性组Y上投影的集合。

投影是选列操作，也就是选属性的操作；选择是属性值满足特定的条件的操作。 连接操作：将S1和S2都有的字段只保留一个。没有写条件的连接操作就是自然连接。也就是S1.Sno=S2.Sno。笛卡尔积和自然连接的投影是不一样的，这点需要特别的注意。

并就是两个关系表并在一起，两边都有的记录只显示一次。 

连接与笛卡尔积最大的区别是连接会把两个表四中都有的字段只保留一个。

交是把公共部分找出来，成为一个新的关系。
差：把公共部分给去掉。
投影：是一种选列的操作，投影可以直接选上数字，数字表示投影的是要过滤出第几列。同样的选择关系中也是可以使用数字来替换关系属性的。
连接关系一般要加上条件作为下缀条件。如果没有写条件就表明用相同的字段做等值。

• 数据库访问接口

访问接口是指分布式环境中保证操作系统，通信协议，数据库等之间进行对话，互操作的软件系统。访问接口有下面三个透明性：

网络透明性	能支持所有类型的网络
服务器透明性	通过标准的SQL语言与不同的DBMS上的SQL语言连接起来。
语言透明性	被调用的功能应该像语言那样是独立的。

应用系统访问数据库接口的方式有多种：专用调用，ODBC，JDBC。

专用调用接口	优点是执行效率高，由于是专用，编程实现也比较简单。但它不具有通用性，所以对于网络数据库系统的应用来说是极其不方便的。
ODBC	开放数据库互联是由微软提供被当前业界广泛接受的哦应用程序编程接口API标准。ODBC实际上就是一个数据库访问的函数库。使用应用程序可以直接操纵数据库中的数据。ODBC是基于SQL语言的，是一种在SQL和应用界面之间的标准接口，解决了嵌入式SQL接口非规范核心。
JDBC	由一些Java语言编写的类和接口组成。是一种可用于执行SQL语句的JavaAPI。可将SQL语句传送给任何一种数据库。

ODBC主要有4个部分组成：应用程序，驱动程序管理器，驱动程序，数据源。 ODBC的API一致性级别分为三级。分别是核心级，扩展1级(增加一些函数)，扩展2级(再增加一些函数)。

应用程序	执行处理并调用ODBC API函数，以提交SQL语句并检索结果。
驱动程序管理器	根据应用程序需要加载/卸载驱动程序，处理ODBC函数调用，或把它们传送到驱动程序
驱动程序	处理ODBC的调用。提交SQL请求，并把结果返回到应用程序。
数据源	包括用户要访问的数据及其相关的操作系统，DBMS及用于访问DBMS的网络平台

• JDBC与ODBC的比较

1.ODBC并不适合在Java中直接使用。ODBC是一个C语言实现的API，从Java调用本地的C程序会带来一系列类似安全性，完整性，健壮性等问题。
2.完全精确实现从C代码的ODBC到JavaAPI写的ODBC的翻译也并不能令人满意。
3.ODBC并不容易学习，而JDBC保证了简单性
4.JDBC对于纯的Java方案是必须的，也更加适应任何Java平台。
其实它们之间最大的区别就是，JDBC以Java的风格和优点为基础，在ODBC的基础上并强化了它。

• 事务处理

DBMS运行的基本工作单位就是事务。事务是用户定义的一个数据库操作序列。这些序列要么全做要么全部不做，是一个不可分割的工作单位。事务通常以BEGIN TRANSACTION语句开始，以ROLLBACK(事务回滚)或者COMMIT(事务提交)语句结束。事务有以下四个属性：

原子性	atomicity	事务要么全做，要么全都不做。
一致性	Consistency	使数据库从一个一致性状态变到另外一个一致性的状态。
隔离性	Isolation	一个事务的执行不能被其他事务干扰
持续性	durablilty	指的是一个事务一旦提交，它对数据库的改变必须是永久的，即便是系统出现了故障

并发操作：在多用户共享系统中，许多事务可能同时对同一数据进行操作。
并发操作带来的问题有：丢失更新，读到过时的数据，读赃数据。
并发处理就是为了提高数据访问的效率。

丢失更新	两个事务T1和T2同时读入同一数据并修改，T2提交的结果将破坏T1的结果。T1的修改被丢失。
读到过时的数据	不可重复读。T1读取某一数据，T2读取某一数据，并进行修改，T1为了对读取值进行校对再读取时，得到不同结果
读赃数据	T1修改了某一个数据，T2读取了同一个数据，T1由于某种原因被撤销，则T2读到的数据就是赃数据。

处理并发控制的主要方法是采用封锁技术。主要有两种类型的封锁：一种是X封锁，一种是S封锁。

X封锁	排他锁	事务T对数据A加上X锁时,只允许事务T读取和修改数据A
S封锁	共享锁	事务T对数据A加上S锁时,其他事务只能再对数据A加S锁,而不能加X锁,直到T释放A上的S锁

并发控制—封锁协议

封锁协议	说明	作用
一级	事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。	可防止丢失修改。
二级	一级封锁协议的加上事务T在读取数据R之前先对其加S锁。读完后即可释放S锁。	可防止丢失修改，还可以防止读赃数据
三级	一级封锁协议的加上事务T在读取数据R之前先对其加S锁。直到事务结束才释放。	可防止丢失修改，防止读脏数据，防止数据重复读。

还有一个叫做两段锁协议，所有事务必须分两个阶段对数据项进行加锁和解锁。它的特点是可串行化，可能发生死锁。封锁粒度大则并发性高，封锁粒度小则并发性低。 

• 范式

非规范化的关系模式可能存在的问题包括：数据冗余，更新异常，插入异常，删除异常。
更新异常：比如我们更新了某一个字段的信息的时候，相应的字段都要同时进行修改的话，就是更新异常。而删除异常简单来说就是把不该删的也删了。
使用图示法可以找出候选键，候选键一般都是入度为零。
拆分数据表，可以提高范式，拆分了过细虽然可以提高范式，但是往往也会带来性能上速度变慢的问题。
第一范式，第二范式，主键等都会用到函数依赖。依赖可以分为部分函数依赖和传递函数依赖。
函数依赖的概念：给定一个X值，就能得到Y，那么它就是函数。从学号确定姓名就是一种函数依赖。但是从姓名就不能确定学号。

1NF	第一范式	属性值都是不可分的原子值
2NF	第二范式	消除非主属性对候选键的部分依赖
3NF	第三范式	消除非主属性对候选键的传递依赖
BCNF	BC范式	消除主属性对候选键的传递依赖

超键	唯一标识元组，可能存在冗余属性，候选键不存在冗余属性
候选键	唯一标识元组，比如：(SNo,SName,SSex) 其中(SNo,SName)。候选键不存在冗余属性。在超键的基础上消除冗余属性就有了候选键。
主键	在候选键中任意选择一个作为主键，主键只能有一个，候选键可以有多个。
外键	其他关系的主键
传递函数依赖	A字段确定B字段，而B字段又可以确定C字段，那么我们就说A字段与C字段是传递函数依赖。当然有个前提是B字段不能确定A字段。
部分函数依赖	（学号，课程号）姓名。主键是两个字段的组合键，而单一的一个字段学号就能确定姓名

部分函数依赖：主键的一个部分可以确定某一个属性。单属性的主键是不可能有部分函数依赖的。
传递函数依赖：A确定B，B确定C，而B不能反过来确定A。如果B能确定A，那么AB是等价的，也就不存在传递依赖的说法了

图示法求候选键非常的方便和容易。先找出入度为零的点，然后尝试遍历整个图。候选键理论上是可以遍历全图的。 

•  数据仓库

数据仓库是一个面向主题的，集成的，相对稳定的，且随时间变化的数据集合，用于支持管理决策。

面向主题	比如我要分析商品这个主题，那么就把商品的信息从各个主题中抽出来。
相对稳定的	放入数据仓库的数据一般就不做插入删除的操作了。
反映历史变化	隔一段时间更新一次最新的数据到数据仓库。

数据仓库反映历史变化的属性主要表现在：
1.数据仓库中的数据时间期限要远远长于传统操作型数据系统中的数据时间期限。数据仓库中的数据往往要存上个几十年。
2.传统操作型数据系统中可能包含也可能不包含时间元素，数据仓库中一定包含时间元素。
3.传统数据操作型数据系统中的数据含有”当前值“的数据，当访问时这些数据会被更新，但是数据仓库中的数据仅仅是一系列某一时刻生成的快照。

从结构的角度看，数据仓库模型有三种类型：企业仓库，数据集市和虚拟仓库。数据集市：就是部门级的数据仓库。

企业仓库	跨越整个企业的各个主题的所有信息，它提供全企业范围的数据集成。
数据集市	包含对特定用户有用的，企业范围数据的一个子集，它的范围限定为选定的主题。
虚拟仓库	是操作型数据库上视图的结合。

BI商业智能的核心技术就是数据仓库和数据挖掘。而数据仓库的建立对于企业来说是一件风险很高的事情，分期来建数据仓库，分阶段来建数据仓库，这样风险会小很多。而数据挖掘则可以挖掘到人类所不知道的事物。 数据集市更像是部门级的数据仓库。

• 数据挖掘方法分类

数据挖掘方法分类：决策树，神经网络，遗传算法，关联规则挖掘算法
数据挖掘分类：关联分析，序列模式分析，分类分析，聚类分析。

关联分析	挖掘出隐藏在数据间的相互关系。
序列模式分析	侧重点是分析数据间的前后关系（因果关系）
分类分析	为每一个记录赋予一个标记，并按照标记进行分类。
聚类分析	分类分析法的逆过程。

• 数据库完整性约束

完整性约束是数据库系统必须遵守的约束，他限定了根据数据模型所构建的数据库的状态以及状态变化，以便维护数据库中数据的正确性、有效性和相容性。它是提高数据可靠性的一种机制。
完整性约束一共分为三种：实体完整性约束，参照完整性约束，用户自定义完整性约束。实体完整性约束的是主键，约束它不能为空，不能重复；参照完整性是用来约束外键的，填入的数据必须是另一个表中的主键的内容；比如年龄大于0小于200，就是用户定义完整性。

实体完整性	关系中的每一个元组对应一个实体。在关系中用主关键字来唯一标识一个实体，实体具有独立性。
参照完整性	用于约定两个关系之间的联系，理论上规定：若M是关系S中的一属性组，且M是另一关系Z的主关键字，则称M为关系S对应关系Z的外关键字。若M是关系S的外关键字，则S中每一个元组在M上的值必须是空值或是对应关系Z中某个元组的主关键字值。
用户定义完整性	约束是用户定义某个具体数据库所涉及的数据必须满足的约束条件，是由具体应用环境来决定的。例如，约定学生成绩的数据必须小于或等于100。

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。
触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。
触发器除了有完整性功能保护以外，还有安全性保护功能。
触发器可以写脚本来约束数据库的要求，更加复杂的要求往往是通过触发器来完成的。

• 数据库安全

措施	说明
用户标识和鉴定	最外层的安全保护措施，可以使用用户帐户，口令及随机数检验的方式。
存取控制	对用户进行授权，包括操作类型（如查找，插入，删除，修改等动作）和数据对象（主要是数据范围）的权限。
密码存储和传输	对远程终端信息用密码传输
视图的保护	对视图进行授权
审计	使用一个专用文件或数据库，自动将用户对数据库的所有操作记录下来。

•  数据库故障与恢复

故障关系	故障原因	解决方法
事务本身的可预期的故障	本身逻辑	在程序中预先设计RollBack语句
事务本身的不可预期的故障	算术溢出，违反存储保护	由DBMS的恢复子系统通过日志，撤销事务对数据库的修改，回退到事务初始状态
系统故障	系统停止运转	通常使用检查方法
介质故障	外存被破坏	一般使用日志重做业务

• 数据库备份

日志文件：事务日志是针对数据库改变所作的记录，它可以记录针对数据库的任何操作，并将记录结果保存在独立的文件中。日志文件是数据库备份的重要依据。
数据库恢复：把数据库从错误状态恢复到某一个已知的正确状态的功能，数据恢复的基本原理就是冗余。建立冗余的方法有数据备份和登录日志文件等。

完全备份	备份所有数据
差量备份	仅仅备份上一次完全备份之后变化的数据
增量备份	备份上一次备份之后变化的数据

差量备份是直接根据上一次的完全备份而备份的。增量备份：是备份上一次备份之后的备份，不管上一次进行的是什么样的备份。

1	静态海量转储	在系统中无法运行事务时进行，每次转储全部数据库。
2	静态增量转储	在系统中无法运行事务时进行，每次只转储上一次转储后更新过的数据。
3	动态海量转储	转储期间允许对数据库进行存储或修改，每次转储全部数据库。
4	动态增量转储	转储期间允许对数据库进行存储或修改，每次只转储上一次转储后更新过的数据。

备份方式		特点	优点	缺点
冷备份	静态备份	是将数据库正常关闭，在停止状态下将数据库文件全部备份下来	非常快速的备份方法（只需备份文件）；容易归档（简单复制即可），容易恢复到某个时间点上（只需将文件再复制回去），能与归档方法相结合，做数据库“最佳状态”的恢复，低度维护，高度安全。	单独使用时，只能提供到某一时间点上的恢复；在实施备份的全过程中，数据库必须要作备份而不能做其他工作，若磁盘空间有限只能复制到磁盘等其他外部存储设备上，速度会很慢；不能按表或按用户恢复。
热备份	动态备份	利用备份软件，在数据库正常运行的状态下，将数据库中的数据文件备份出来。	可在表空间或数据库文件级备份，备份的时间短，备份时数据库仍可使用；可达到秒级恢复，可对几乎所有数据库实体做恢复，恢复是快速的	不能出错，否则后果严重，若热备份不成功所得结果不可用于时间点的恢复；因难于维护，所有要特别小心，不允许以失败告终。

• 数据库优化

数据库性能的调整是数据库管理员的日常工作之一。性能调整工作可以从逻辑上和物理上两个方面进行。
数据库性能可以分为三部分：SQL的性能优化，数据库的性能优化，查询优化。
索引是一个双刃剑，它提高了查询速度的同时，也增加了插入删除的时间。Select*就会带来效率上的降低。
数据库连接池：把对数据库的连接放入到一个池中，这样就可以避免频繁创建和销毁连接。
分布式数据库的优化：还要考虑通信代价的问题。

• 数据库设计与建模

数据库设计是指对一个给定的应用环境，提供一个确定最优数据模型与处理模式的逻辑设计，以及一个确定数据库存储结构与存取方法的物理设计，建立起能反映现实世界信息和信息联系以及满足用户数据要求和加工要求，以能够被某个DBMS所接受，同时能实现系统目标并有效存取数据的数据库。
基于DBS生存期的数据库设计分成五个阶段，分别为规划，需求分析，概念设计，逻辑设计和物理设计。
ER模型是描述概念世界，建立概念模型的实用工具。ER图中包括三个要素：实体（用矩形框表示，框内标注实体名称）；属性（用椭圆形表示，并用连线与实体连接起来）；实体之间的联系（用菱形框表示，框内标注联系名称，并用连线将菱形框分别与有关实体相连，并在连线上注明联系类型）。

ER图与关系模式之间的转换：一个实体转化为一个关系模式。

1对1的联系	两个实体两个关系模式，联系可以归并到两个当中任意的关系模式中
1对多联系	单独将联系转化为一个关系模式。也可以将联系归到多的联系的这一侧。
多对多的联系	至少要转成三个关系模式。

集成产生的冲突及解决办法：

属性冲突	包括属性域冲突和属性取值冲突。
命名冲突	包括同名异义和异名同意的冲突。
结构冲突	包括同一对象在不同应用中具有不同的抽象。同一实体在不同局部ER图中所包含的属性个数和属性排列次序不完全相同。

ER图的设计过程：确定局部视图的范围，识别实体及其标识，确定实体间的联系，分配实体及联系的属性。
视图的集成通常有两种方式：一次集成，逐步集成。
在将各个ER图之间必定存在冲突的问题，因此合并ER图时并不能简单地将各个局部ER图画到一起，而是必须着力消除各个局部ER图中的不一致，以形成一个能为全系统中所有用户共同理解和接受的统一的概念模型。
在集成ER图之后，还需要对ER模型进行评审。评审的作用在于确认建模任务是否全部完成，通过评审可以避免重大的疏漏或错误。

• 分布式数据库系统

是由一组数据组成的，这组数据分布在计算机网络的不同计算机上，网络中的每个节点具有独立处理的能力（成为场地自治），它可以执行局部应用，同时每个节点也能通过网络通信子系统执行全局应用。

大量的数据在网络上传输是不合适的，再次背景下才有了分布式数据库。
各个网点都存在自己的数据。但在逻辑上又是一个统一的整体。
比如不在开户银行办理业务的时候，就是分布式数据库的应用。
分布模式：什么数据存在什么卫视是由分布模式来管理的。
分片模式：所有网点的数据库应该保持它的一致性。
水平分片：上海的数据存放在上海，杭州的数据存放在杭州；
垂直分片：一个表中一部分数据存放在A，另一部分存放在B。
混合分片：水平和垂直分片两者都有的情况。
分片透明性包括：水平透明性和垂直透明性，还有混合分片。
分布模式：什么数据存放在什么地方 。

• 数据库模式分解

把一个关系模式分解成若干个关系模式的过程，称为关系模式的分解。分解有保持函数依赖的分解和无损分解两种。
把低一级的关系模式分解为若干个高一级的关系模式的方法不是唯一的。
只有能够保证分解后的关系模式与原关系模式等价，分解方法才有意义。
分解后的关系模式中的函数依赖加起来 和 原先的关系模式中的函数依赖是一样的。
无损连接分解：指将一个关系模式分解成若干关系模式后，通过自然连接和投影等运算仍旧可以还原到原来的关系模式。 
分解之前有哪些函数依赖，分解以后这些函数依赖依然存在。
有损压缩是指不能够还原的压缩，数据库模式中这个概念也能够通用。将关系R分解成三个表，这三个表如能通过连接操作还原到R关系的话，他便是无损的。

• 联邦数据库系统

是一个彼此协作却又相互独立的成员数据库的集合，它将成员数据库系统按不同程序进行集成，对该系统整体提供控制和协同操作的软件叫做联邦数据库管理系统。联邦数据库系统只是有公共的接口。但彼此又是和美国联邦政府一样，实现了自制。

• NoSQL（非关系型的数据库）

随着互联网web2.0网站的兴起，传统的关系数据库在应付web2.0网站，特别是超大规模和高并发的SNS类型的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题，而非关系型的数据库则由于其本身的特点得到了非常迅速的发展。NoSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。
NoSQL适用的场景：
1、数据模型比较简单；
2、需要灵活性更强的IT系统；
3、对数据库性能要求较高；
4、不需要高度的数据一致性；
5、对于给定key，比较容易映射复杂值的环境。
NoSQL缺点不足：
成熟度不够，大量关键特征有待实现
开源数据库产品的支持力度有限
数据挖掘与商务智能支持不足，现有的产品无法直接使用NoSQL
擅长NoSQL的专家较少，大部分都处于学习阶段。

	关系数据库模式	NoSQL模式
并发支持	支持并发，但效率低	并发性能高
存储与查询	关系表方式存储，SQL查询	海量数据存储，查询效率高
扩展方式	向上扩展	向外扩展
索引方式	B树，哈希等	键值索引
应用领域	通用领域	特定应用领域

• 反规范化

由于规范化会使表不断的拆分，从而导致数据表过多，这样虽然减少了数据冗余，提高了增，删，改的速度，但会增加查询的工作量。系统需要进行多次连接，才能进行查询操作，使得系统效率大大下降。反规范化说白了就是以空间换时间。
反规范化的技术手段：增加派生性冗余列，增加冗余列，重新组表，分割表。