【软件设计师】数据库

1、数据库基本概念

1.1 基本概念

        数据库系统DBS：是一个采用了数据库技术，有组织地，动态地存储大量相关数据，方便多用户访问的计算机系统。其由以下四个部分组成：数据库、硬件、软件、人员

1.2 数据库系统的体系结构

        集中式数据库系统：数据是集中的，数据管理是集中的，数据库系统的素有功能（从形式的用户到DBMS核心）都集中在DBMS所在计算机。就是我们常说的单机系统。

        C/S结构：客户端负责数据表示服务；服务器主要负责数据库服务；数据库系统分为前端和后端；ODBJ，JDBC

        分布式数据库：物理上分布，逻辑上集中；物理上分布，逻辑上分布；透明性；最开始提出的时候是为了容灾

        并行数据库：共享内存式；无共享式

1.3 分布式数据库特点

        数据独立性：除了数据的逻辑独立性与物理独立性外，还有数据分布的独立性（分布透明性）（分布性）

        集中与自治共享结合的控制结构：局部DBMS可以独立第管理局部数据库，具有自治的功能。同时又设有集中控制机制，协调个局部DBMS的工作，执行全局应用。（自治性、共享性）

        适当增加数据冗余度：在不同的场地存储同一数据的副本，可以提高系统的可靠性和可用性，同时也能提高系统性能（当系统中某个节点发生故障，因为数据有其他副本在非故障场地上，对其他场地来说，数据仍然是可用的，从而保证数据的完备性）（故障--可用性）

        全局的一致性、可串行性和可恢复性

1.4 分布式数据库透明性

        分片透明性，用户或应用程序不需要知道 逻辑上存储的表具体是如何分块存储的，不必关心数据是如何分片的，对数据的操作是在全局关系上进行。

        复制透明性：用户或应用程序不关心复制的数据从何而来，不必关心数据库在网络中各个节点的复制情况，被复制的数据的更新都是由系统自动完成。

        位置透明性：应用程序不关心数据存储物理位置，即数据被分配到哪个或哪些站点存储对用户是透明的。

         逻辑透明性（局部映像透明性）：最低层次的透明性，该透明性提供数据到局部数据库的映像，用户或应用程序无需知道局部使用的是哪种数据结构（数据模型），不需知道适用哪种数据操作语言等。

1.5 分布式数据库分片模式

 水平分片：将表中水平的记录分别存在不同的地方。
 垂直分片：将表中的垂直的列值分别存在不同的地方。

1.6三级模式-两级映象

        内模式（文件级）：内模式也称存储模式，是数据物理结构和存储方式的描述，是数据在数据库内部的表示方法（存储文件），定义所有的内部记录类型，索引和文件的组织方式。

        概念模式（表级）：概念模式也称模式，就是我们通常使用的基本表，根据应用，需求将物理数据划分一张张表。

        外模式（视图级）：用户模式，将表进行一定处理后在提供给用户使用。

        模式/内模式映象：存在于概念级和内部级之间，实现了概念模式和内模式之间的相互转换，实现了物理独立性，当数据的物理结构发生变化时，应用程序不用改变。但是为了保证应用程序能正确执行，需修改该层次的映像

        外模式/模式映象：存在于外部级和概念级之间，实现了外模式和概念模式之间的相互转换；实现逻辑独立性，当数据的逻辑结构发生变化时，用户程序不用改变。但是为了保证应用程序能正确执行，需修改该层次的映像

        !注意不存在外模式/内模式的直接映射

1.7 数据仓库

 数据仓库特点：

        1.不是为了应用，是面向主题的，数据按照主题组织

        2.集成的：消除了源数据中的不一致性，提供整个企业的一致性全局信息

        3，相对稳定的（非易失的）：主要进行查询操作，只有少量的修改和删除操作（或是不删除）（同时会在特定的时间点做大量的插入）

        4，反映历史变化（随着时间变化）：记录了企业从过去某一时刻到当前各个阶段的信息，可对发展历程和未来趋势做定量分析和预测（集成不同表）

        形成数据仓库后有两个作用，一个是用来做数据的查询、分析、生成报表（OLAP，联机分析系统），另一个是使用数据挖掘工具对这些历史数据进行挖掘，查询数据之间的关系，发现剩余价值（OLTP，联机事务系统）。 

  

数据挖掘的分析方法
 关联分析：关联分析主要用于发现不同事物之间的关联性，即一个事物发生的同时，另一个事物也经常发生。
 序列分析：序列分析主要用于发现一定时间间隔内接连发生的事件，这些事件构成一个序列，发现的序列应该具有普遍意义。
 分类分析：分类分析通过分析具有类别的样本特点，得到决定样本属于各种类别的规则或方法。分类分析时首先为每个记录赋予一个标记，即按标记分类记录，然后检查这些标定的记录，描述出这些记录的特征。
 聚类分析：聚类分析师根据“物以类聚”的原理，将本身没有类别的样本聚集成不同的组，并且对每个这样的组进行描述的过程。

2.数据库设计过程    

        需求分析：即分析数据存储的要求，主要产出物有数据流图，数据字典，需求说明书。

        概念结构设计：就是设计E-R图，即实体-属性图，与物理实现无关，说明有哪些实体，哪些属性，

        逻辑结构设计：将E-R图转成关系模式，即转换为实际的表和表中的列属性。（关系规范化）

        物理设计：根据生成的表等概念，生成物理数据库。（聚簇索引--整个的修改内容会涉及对物理具体的分布）

2.1 E-R模型

        E-R模型：即实体-联系模型，使用椭圆表示属性，长方形表示实体（有时候会加平行线表示特殊实体），菱形表示联系，联系两端要标注联系类型。

        联系类型：一对一1:1（1：1），一对多1：N（1：*），多对多N:M（*：*）。

        属性分类：简单属性和复合属性（属性是否可以分割），单值属性和多值属性（一个属性是否有多个取值），NULL属性（表示无意义或不知道），派生属性（可以由其他属性得来）。

扩充E-R模型

        弱实体：一种特殊的依赖联系，该联系是指某实体是否存在对于另一些实体具有很强的依赖关系，即使=一个实体存在必须以另一个实体存在为前提，而这类实体被称为弱实体，如家属与职工的联系，附件与邮件；在图例上弱实体以双矩形表示，关系以双菱形表示，弱实体的属性值中必须有被依赖的实体的键（如家属的属性里应写上职工号，再记录对应的家属信息）

        特殊化：可以理解为父类和子类之间抽象的关系

        聚集：一个联系作为另一个联系的一端

2.2 逻辑结构设计

        数据模型：层次模型（如树状），网状模型，关系模型，面向对象模型

        数据模型三要素：数据结构、数据操作、数据的约束条件

关系模型

        格式：关系模式名（属性1，属性2......)

        典型形式：二维表（模式名就是表名，属性名就是列名）

 

关系模型的相关概念：

        目或度：关系模式中属性的个数

        超键：能唯一标识此表的属性的组合。

        候选码（候选键）：唯一标识元组，且无冗余（超键中去掉冗余属性就是候选键），是一个“属性集合”

        主码（主键）：在候选键中任选一个

        外键：其他关系模式的主键

        全码：关系模式中所有的属性组是这个关系的候选码     

关系的三种类型：基本关系、查询表（虚表）、视图表（虚表） 

完整性约束：

        实体完整性约束：即主键约束，即主键唯一，也不能为空。
        参照完整性约束：即外键约束，外键必须是其他表中已经存在的主键的值，或者为空。
        用户自定义完整性约束：自定义表达式约束（通常用check约束），如性别限制为“女”

（触发器可以完成一些复杂的完整性约束条件的设定）

 E-R图转换为关系模式：每个实体都对应这种关系模式，联系分为三种：
 1:1关系中，联系可以放在任意两端的实体中，作为一个属性（要保证1:1的两端要关联）；如果联系要作为独立的关系模式，需要并入两端任一端的主键，此时联系的主键就是任一端的主键

 1:N关系中，联系可以单独作为一个关系模式，此时联系的主键就是多端的主键；也可以在N端中加入1端实体的主键；

        
 N:M关系中，联系必须作为一个单独的关系模式，其主键是N和M端的联合主键。

 

3.关系代数 

关系代数运算
        并(∪)：结果是两张表所有记录的合并，相同记录只显示一次。
        交(∩)：结果是两张表中相同的记录。
        差(-)：S1-S2，结果是S1表中有而S2表中没有的记录。

        

        笛卡尔积(×):S1×S2，产生的结果包括S1和S2的所有属性列，并且S1中的每条记录依次和S2中所有记录组合成一条记录，最终属性列为S1+S2属性列，记录数为S1*S2记录数。
        投影(π)：实际是按条件选择某关系模式中的某列，列也可以用数字表示,垂直方向.
        选择(σ)：实际是按条件选择某关系模式中的某条记录,水平方向

        自然连接(⋈)：自然连接的结果是显示全部的属性列，但相同的属性列只显示一次(保留⋈前面的那个列名)，显示两个关系模式中属性相同且值相同的记录。

        

        自然连接可以等价于"针对笛卡尔积先做选择后做投影",我们会认为自然连接在同等情况下效率优于笛卡尔积(先对表做压缩,再对表做连接)

 

select x     (选择)

from A,B    (A,B做笛卡尔积)

where      (投影)

3.1 规范化理论概述 

函数依赖
        X->Y:给定一个X，能唯一确定一个Y，就称X确定Y，或者说Y依赖于X。

函数依赖又可以扩展一下两种规则：
        部分函数依赖：A可以确定C，（A,B）也可以确定C。（A,B）中的一部分（即A）可以确定C，称为部分函数依赖。
        传递函数依赖：当A和B不等价时，A可确定B，B可确定C，则A可以确定C，是传递函数依赖。

 候选键:       

答案:A,ABCD,B

3.2 范式规范 

范式:
        第一范式1NF：若关系模式R每一个分量是不可再分的数据项，则关系模式R∈1NF。（符合原子性）
        第二范式2NF：若关系模式R∈1NF，且每一个非主属性完全依赖主键（不存在部分函数依赖）时，则关系模式R∈2FN。（步骤：先找候选键，再找非主属性，最后观察候选键的部分是否就可以决定非主属性（部分依赖）
        第三范式3NF：若关系模式R∈2FN，且R中没有非主属性传递依赖于候选键时，则关系模式∈3NF。一般解决方式是拆分传递依赖的非主属性为一个新的关系模式。本质就是主键要直接决定所有非主属性，不能通过非主属性间接决定。（步骤：先找候选键，再找非主属性，最后观察非主属性是否决定了其他非主属性）
        BC范式BCNF：不存在函数依赖，也不存在传递依赖，函数依赖左侧决定因素包含候选键（步骤：先找候选键，再看函数依赖左侧决定因素是否在候选中出现）

        只要没到达3NF,我们都认为 数据冗余、修改异常、插入异常、删除异常 都存在

        候选键只有一个列名，则至少满足第二范式，没有非主属性，则至少满足第三范式

3.3 模式分解

原则：

        1.保持函数依赖，对于冗余函数依赖不需要保留

        2.无损分解（无损连接分解：指的是将一个关系模式分解成若干个关系模式后，通过自然连接等运算仍能还原到原来的关系模式） 

 

步骤（方法1）：

        1.先找候选键

        2.找是否有相同属性列

        3.相同属性列在这两个关系模式里是不是作为候选键（主键）存在（需要每一个相同属性列都是才可以说是无损）

步骤（方法2：表格法，通用，推荐这个）：

        1.先画表

        2.找有相同属性列的列

        3.看原文中是否有作为函数依赖的左端出现过

        4.如果有则在该依赖的右端的那个字母下面把×改成√；如果不能则不能还原；如果右边那端的字母下均是X，则也不能无损。

        5.若某一行全√了，那就说明该分解是无损分解

步骤（方法3：公式法，超过两个关系模式就不能使用公式法了）：

        1.如果A∩B  -->   A-B     或者     A∩B  -->   B-A（--->是依赖的意思）

        2.只要A∩B 能决定  A-B  或  B-A，则为无损连接

4 SQL语言概述 

4.1 SQL语句

分类：

        1.数据查询：select

        2.数据定义：create,drop,alter

        3.数据操作：insert,updata,delete

        4.数据控制：grant,revork

 分组查询：group by 列名 having 条件表达式

        结果排序（升序降序）：group  by 字段名 desc/asc

        集函数（统计，和，平均值，最大值，最小值）：count/sum/avg/max/min([distinct|all] 列名）

        对结果分组：group by 列名

        对结果筛选：having 条件表达式

增 insert（create） 删 delete 改 update 查 select 

字符串匹配 like，%匹配多个字符，_匹配任意一个字符 

授权：grant 权限 on 对象类型、对象名   to 用户  [with grant option]

 收回权限：revoke 权限 on 对象类型、对象名   from 用户  [restrict | casede]

##casede表示级联收回，如收回s1的权限，则由s1授权的s2,s3等的权限都要一次性收回

4.2 并发控制 

事务特性：
        原子性（操作）：要么都做，要么都不做。事务开始begin transaction，事务提交commit ，事务回滚rollback。
        一致性（数据）：事务发生后数据是一致的。
        隔离性（执行）：任一事务的更新操作直到其成功提交的整个过程对其他事务都是不可见的，互不干涉。
        持续性（改变）：事务操作的结果是持续性的。

 

对数据库来说，是先写日志再执行操作      

并发控制
        并发操作带来的问题主要是数据的不一致性，主要有三类：丢失更新/修改，不可重复读，读脏数据。

 

并发控制技术
        并发控制的主要技术是封锁，基本封锁的类型有排他锁（简称X锁或者写锁）和共享锁（简称S锁或者读锁）。
        排它锁：若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他事务都不能再对A加任何类型的锁，直达T释放A上的锁。
        共享锁：若事务T对数据对象A加上S锁，则只允许T读取A，但不能修改A，其他事务只能在对A加S锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A进行任何修改。

 

三级封锁协议
 一级封锁协议：事务在修改数据R前必须对其加X锁，直到事务结束才释放。一级封锁协议可以解决丢失更新问题
 二级封锁协议：在一级封锁协议的基础上，加上事务T在读数据R前必须先对其加S锁，读完后立即释放S锁。二级封锁协议可以解决读脏数据的问题
 三级封锁协议：在一级封锁协议的基础上，加上事务T在读数据R前必须先对其加S锁，直到事务结束时释放S锁。三级封锁协议除了防止修改和不读“脏”数据外，还进一步防止了不可重复读。

反规范化技术
        技术手段包括：增加派生性冗余列，增加冗余列，重新组表，分割表。
        主要就是增加冗余，提高查询效率，为规划化操作的逆操作。