全国计算机三级数据库学习

1 数据库应用系统(DBAS)生命周期

1.1 规划与分析

• 系统规划与定义

  • 任务陈述
  • 确定任务目标
  • 确定范围和边界
  • 确定用户视图

• 可行性分析

  • 技术、经济、操作可行性
  • 开发方案选择

• 项目规划

  • 项目团队、环境、活动
  • 成本预算
  • 进度计划

1.2 需求分析

• 数据需求分析

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

• 功能需求分析

  • 数据处理需求

    • 数据流图

      • DFD建模

        • 基本元素：数据流、处理、数据存储和外部项

          外部项是系统数据提供者或使用者

        • 规则：父图中描述的数据流必须在相应子图中出现；一个处理至少一个输入、一个输出；一个数据流至少由一段是处理框

      • IDEF0建模

        • 由箭头和矩形框两元素组成
        • 矩形框左侧“输入”，右侧“输出”，上方“控制”，下方“机制”

    • 事务规范

      • 事务名称
      • 事务描述
      • 访问数据项
      • 用户

  • 业务规则需求

• 性能需求分析

  • 数据操作响应时间
  • 系统吞吐量
  • 允许并发访问最大用户量
  • TPS代价值

• 存储、安全需求

• 备份和恢复需求

1.3 系统设计

1.3.1 数据库结构设计

• 概念模型设计

  • 明确建模目标
  • 定义实体集
  • 定义联系

  • 建立信息模型

    • ER图

      • 实体（矩形框）

      • 联系（菱形框）

        • 1:1
        • 1:n
        • m:n

      • 属性（椭圆）

    • IDEF1X

      • 实体集分为独立实体集(矩形框)；从属实体集(圆角矩形框)

      • 联系

        • 确定型联系

          • 标定型

            子女实体集的每个实例都由它与双亲的联系而确定

          • 非标定型

            子女实体集的每个实例不用凭借其与双亲的联系而能被唯一确认

        • 分类联系

        • 非确定型联系（多对多联系）

  • 确定实体集属性

  • 集成与优化信息模型

• 逻辑结构设计

  • 依据：概念模型；数据处理要求；数据约束及安全性要求；DBMS的相关信息
  • 目标：DBMS可处理的模式；数据库物理设计指南
  • 过程：关系模式的转化（一般要求满足3NF）

• 物理结构设计

  • 解决问题

    • 文件的组织
    • 文件的结构
    • 文件的存取

    • 索引技术

      • 有序索引

        • 两个主体：数据文件和索引文件

        • 分类

          • 聚集索引和非聚集索引—一个表或视图只允许有一个聚集索引

            索引文件按照其查找码的顺序与数据文件中数据记录的排列顺序一致，该索引文件为聚集索引。

          • 稠密索引和稀疏索引

            数据文件中的每个查找码值在索引文件中都对应一个索引记录，则该索引成为稠密

          • 主索引和辅索引

            在数据文件的主码属性上建立的索引为主

          • 唯一索引：确保索引列不包含重复值

          • 单层和多层索引（B+树索引即为多层）

      • 散列索引

  • 环节

    • 数据库逻辑模式描述

      • 满足应用要求的完整性约束的基本表和视图
      • 基本表业务规则

    • 文件组织与存取设计

      • 分析和理解数据库事物的数据访问特性

        • 事物-基本表交叉引用矩阵
        • 估计各事务的执行频率
        • 对每张基本表汇总作用于其上的各事务的操作频率

      • 选择合适的文件结构

        • 数据量少，且插删改操作频繁的基本表采用“堆文件”组织方式
        • 用户查询条件定义在查找码上，用“顺序文件”
        • 用户查询基于散列值域的等值匹配，用“散列文件”
        • 数据查询效率更高的“B+树文件”
        • 频繁执行且需要进行多表连接的操作，考虑将这些表组织为“聚集文件”

      • 设计合理的存取路径——建立索引

        • 原则

          • 经常更新、删除和插入或数据量小的表避免
          • 经常查询、连接、统计且数据量大的表优先

        • 属性选择

          • 为主码建立唯一索引
          • 参与连接操作
          • 查询条件
          • 分组和排序条件
          • 当一个属性有相对较多不同值时

    • 数据库分布设计

      • 物理分布

        • 数据文件和索引文件存放在不同的磁盘上——得到较快的文件读写速度
        • 数据文件和日志文件存放在不同磁盘上——改善I/O性能

      • 数据的划分

        • 水平划分
        • 垂直划分

      • 关系模式去规范化

    • 确定系统配置

    • 物理模式评估

1.3.2 DBAS功能设计与实施

总体、概要和详细设计分别与结构设计的三阶段相对应

• 总体设计

  • C/S结构

    • “胖”客户端

      • 用户界面
      • 应用业务规则

    • 数据库服务器

      • 事务逻辑
      • 数据存储和存取

  • B/S结构 (三层结构)

    • 表示层：“瘦”客户端-浏览器
    • 功能层：Web应用服务器
    • 数据层：数据库服务器

• 概要设计和详细设计

  • 表示层

    • 用户界面设计

  • 业务逻辑层

    • 概要：将各项业务活动表示为各种“构件”
    • 详细编码设计

  • 数据访问层：针对DBAS的数据处理需求设计用于操作数据库的各类事务

    • 概要设计：将数据处理需求的结果表示为事务规范

      • 事务名称
      • 事务所访问的关系表以及关系属性
      • 事务处理逻辑
      • 事务用户

    • 详细设计

  • 数据持久层：保存和管理应用系统数据（数据组织与存储）

• 安全架构设计

  • 数据安全

    • 数据库的安全性保护

      • 用户身份鉴定
      • 权限控制
      • 视图机制

    • 完整性保护

      • 列级完整性约束
      • 元组级完整性约束
      • 关系级别完整性约束

    • 并发控制

      • 封锁技术

      • 避免死锁

        • 按同一顺序访问资源
        • 采用小事务模式
        • 尽量使用“行锁”，避免使用表级别锁
        • 使用绑定连接

    • 备份与恢复

      • 双机热备
      • 数据转储
      • 数据加密存储-针对高敏感数据

    • 加密传输

      • 数字安全证书
      • 对称密钥加密
      • 数字签名
      • 数字信封

  • 环境安全

    • 漏洞与补丁（操作系统安全性）
    • 计算机病毒防护

    • 网络环境安全

      • 防火墙
      • 入侵检测系统
      • 网络隔离

    • 物理环境安全

  • 制度安全—技术层面和管理层面

1.4 实现与部署

• 建立数据库结构
• 数据加载
• 事务和应用程序的编码及测试
• 系统集成、测试与试运行
• 系统部署

1.5 运行与维护

• 日常维护

  • 备份与恢复
  • 完整性、安全性维护
  • 存储空间管理
  • 并发控制

• 监控与分析

  • 数据采集与统计
  • 操作分析
  • 基准程序评估

• 性能优化调整

  • 查询调整与优化
  • 索引调整，事务调整，模式调整，参数调整
  • 硬件调整和升级
  • 应用程序优化

• 系统升级

  主要由数据管理员DBA负责，有时也需要其他设计开发人员参与

  • 应用程序升级
  • 数据库重组
  • DBMS和OS升级

2 统一建模语言UML

2.1四层建模框架

每后一层是上一层概念的一个实例

• 元元模型
• 元模型
• 模型
• 用户模型

2.2 DBAS描述

• 业务流程-活动图

  • 用于描述系统、用例和程序模块中逻辑流程的先后执行顺序
  • 必须有且只有一个起始点，可以有多个结束点。利用分区将活动分配给对应的角色

• 需求表达-用例图

  • 组成

    • 系统
    • 角色：系统外部的人或其他实体
    • 用例：系统内部的功能描述

  • 关系

    • 角色间的通用化关系
    • 用例与角色间的关联关系

    • 用例与用例之间

      • 扩展关系
      • 使用（包含）关系
      • 关联（组合）关系

• 内部结构的表达

  • 静态结构：类图

  • 动态结构

    • 顺序图（强调时间）：系统内对象间发送、接受消息的序列
    • 通信图（强调空间）：系统中对象间的练习以及对象间发送和接收的消息

• 微观设计的表达

  • 对象图：系统在某一时间的快照
  • 状态机图：陈述系统中有关事件或对象的状态转移（一个起始，可多个结束）
  • 时间图：当状态转换由时间因素决定时

• 宏观设计的表达

  • 包图：表达系统中不同包、命名空间或不同项目间的关系
  • 交互概述图：用交互框取代活动图中的活动框
  • 复合结构图：系统与外部系统之间的关系

• 实现与部署的表达

  • 组件图：展现组件 之间的组织和依赖
  • 部署图（配置图）：描述系统中硬件和软件的物理配置情况和系统体系结构

3 数据库及数据库对象

3.1 SQL Server2008系统数据库

• master
• msdb
• model—当用户创建数据库时，系统自动复制到新建数据库中
• tempdb—临时数据库，无需备份
• Resources：只读，包含所有系统对象

3.2 SQL Server数据库组成

• 数据文件

  • 主要文件.mdf
  • 次要文件.ndf

• 事务日志文件.ldf

• 数据库存储空间的分配

  • SQL Server2008中一页（数据最小存储单位）是8kb
  • 一行数据不能跨页存储

3.3 数据库文件组

• 主文件组：系统定义好的一个文件组，包含主要数据文件和其他没明确分配给其他文件组的数据文件（因此也会包含次要文件）

• 用户定义文件组

  • 一个文件不能是多个文件组的成员
  • 日志文件不包含在文件组中
  • 如果文件组有多个文件。在所有文件被填满之前，它们不会自动增长

3.4 T-SQL(2018年后貌似不考)

• 创建数据库
• 修改数据库
• 分离和附加数据库（数据库服务器启动，但是无用户使用的状态下）

3.5 架构（SCHEMA）

• 定义：存放数据库对象的容器.架构名可以是显式的，也可以由DBMS提供默认
• 创建：CREATE SCHEMA schema_name AUTHORIZATION user_name
• 删除：DROP SCHEMA schema_name

3.6 数据库对象

• 分区表

  • 分区条件：表当前和未来的数据量大小，以及对表中数据操作的特点

  • 步骤

    • 创建分区函数

            CREATE PARTITION FUNCTION function_name（int）括号中为用于分区的列的数据类型

            AS RANGE LEFT/RIGHT FOR VALUES(num1,num2,...)


       分区数=边界数+1


    - 创建分区方案:将分区隐射到文件组

            CREATE PARTITION SCHEME partition_scheme_name

            AS PARTITION partition_function_name

            TO(filegroup1,filegroup2,...)


    - 使用分区方案创建表

            CREATE TABLE table_name

            (col_name1 int,

            col_name2 char(10)

            )

            ON partition_scheme_name(col_name)

• 索引

    CREATE UNIQUE CLUSTERED/NONCLUSTERED INDEX index_name ON table_name(col_name)
  
    DROP INDEX index_name
  

• 索引视图

  • 首先定义一个视图 CREATE VIEW view_name AS...
  • 在视图上定义一个唯一聚集索引 CREATE UNIQUE CLUSTERED INDEX...

4 数据库后台编程技术

4.1 存储过程

可以有多个输入和多个输出

• 创建存储过程

    CREATE PROC/PROCEDURE proc_name
  
    @a int, @b int ,@c int output
  
    AS
  
    SELECT...
  

• 执行存储过程

  对于有输出参数的，执行前先生命输出参数

    DECLARE @c int
  
    EXEC proc_name 1, 2, @c output
  

  或者(但注意两种方式不能混用)

    EXEC proc_name @a=1,@b=2, @c output
  

4.2 用户定义函数

• 标量函数

    CREATE FUNCTION fuc_name(@a int）
  
    RETURNS int
  
    AS
  
    BEGIN
  
    DECLARE @x int
  
    ...
  
    RETURN @x
  
    END
  

• 表值函数

  • 内联表值函数

      CREATE FUNCTION fuc_name（@c char(10))
    
      RETURNS table
    
      AS
    
      RETURN
    
      (
    
      SELECT...
    
      )
    

  • 多语句表值

        CREATE FUNCTION fuc_name(@c char(10))

        RETURNS @table_name  TABLE(商品名 varchar(50),...)

        AS

        BEGIN

        INSERT INTO @table_name

        ...

        RETURN

        END

4.3 触发器

CREATE TIGGER trigger_name ON table_name

FOR(AFTER)/INSTEAD OF INSERT/UPDATE/DELET

AS

...

• 后触发器
• 前触发器：在一个表上针对同一个数据操作只能建立一个

4.4 游标

• 声明游标

    DECLARE cursor_name CURSOR FOR ...
  

• 打开游标

• 提取数据

  • 当前行的前一行：FETCH PRIOR FROM cursor_name INTO @存放结果集
  • 当前行后一行：FETCH NEXT
  • 每次提取数据都会对@@FETCH_STATUS进行赋值

• 关闭游标

• 释放游标

5 安全管理

5.1 SQL Server安全控制

• 身份验证模式

  • WINDOWS身份验证模式
  • 混合身份验证模式

• 登录账户 “LOGIN”：CREATE LOGIN SQL_User WITH PASSWORD='123456'

• 数据库用户：一个登录账户可以隐射为多个数据库中的用户 “USER”；但每个数据库用户只能对应一个登录账户

• 权限管理

  • 授权|收权|拒绝【对象】级别：GRANT|REVOKE|DENY 【SELECT/INSERT/UPDATE/DELETE/REFERENCE/EXECUTE】 ON ... TO|FROM|TO ...
  • 语句级别：GRANT 【CREATE TABLE/BACKUP DATABASE】 TO user

• 角色

  • 固定服务器角色
  • 固定数据库角色

  • 用户定义角色（属于数据库这一级别）

    • 创建：CREATE ROLE role_name AUTHORIZATION ...
    • 删除：DROP ROLE role_name

5.2 Oracle安全控制

• 分为数据库级别的安全控制；表级、行级、列级的安全控制

  • 数据库级：通过用户身份认证和授予用户相应权限保证
  • 表、行、列级：通过授予或回收对象特权保证

• 用户按操作权限分为DBA用户（拥有全部系统特权）和普通用户两类

6 运行维护与优化

6.1 运行维护

• 数据库的转储与恢复
• 数据库的安全性与完整性控制
• 数据库性能的监控分析和改进

• 数据库的重组和重构

  • 重组：不改变原有逻辑和物理结构（如调整磁盘分区方法和存储空间，整理回收碎块等）
  • 重构：部分修改数据库的模式和内模式（如改变数据类型）

6.2 性能优化

• 运行环境和参数调整

  • 外部：CPU，网络
  • 调整内存分配
  • 调整磁盘I/O
  • 调整竞争

• 模式调整与优化

  • 增加派生性冗余列
  • 增加冗余列
  • 去规范化重新组表

  • 分割表

    • 水平分割
    • 垂直分割

  • 新增汇总表

• 存储优化

  • 物化视图：预先计算并保存 费时的操作结果
  • 聚集：将具有公共列值的多个表中的数据行存储在一起，由公共列构成聚集码

• 查询优化

  • 合理使用索引

    • 聚簇索引最适合用于范围查询
    • 散列索引适合等值查询（尤其是在连接操作中）
    • B+树支持作为搜索码属性上的等值查询和范围查询

  • 使用临时表加速查询

  • 避免或者简化排序
  • 避免复杂的正则表达式
  • 消除对大型表的顺序存取（对连接的列进行索引）
  • 用排序取代非顺序磁盘存取
  • 尽量避免不充分的连接条件
  • 对于频繁使用的SQL语句建议使用存储过程（不用编译直接执行）
  • 不要随意使用游标
  • 一个事务中尽量少的处理

7 故障管理，备份与恢复

7.1 故障管理

• 事务内部故障

  事务故障的恢复是由系统自动完成，对用户是透明的

  • 事务特征

    • 原子性、一致性、隔离性、持久性

  • 预期的—可以通过事务程序本身发现，通过将事务回滚使数据库回到一致性的状态

  • 非预期的—不能由事务程序处理（如运算溢出，并发死锁等）。利用日志文件撤销其对数据库的修改。

• 系统故障

  • 原因：硬件故障、数据库软件和操作系统漏洞或突然停电
  • 恢复：REDO已提交的事务，UNDO未完成的事务。必须使用日志文件

• 介质故障

  • 原因：物理存储设备损坏

  • 容错对策

    • 软件容错：使用数据库备份和事务日志文件恢复数据库到备份结束时的状态

    • 硬件容错：采用双物理存储设备

      • 磁盘保护技术：磁盘阵列

        • RAID0：采用数据分块，并行传送方式。能够提高读写速度，但对系统可靠性没有帮助
        • RAID1：提高读速度，加强系统可靠性。但是硬盘利用率50%（另一半作为镜像），同时写速度没有提高
        • RAID5：比RAID1磁盘利用率高，但保障程度低；读取速度与RAID0相近；写入速度比单个磁盘还低。
        • RAID10:冗余度为50%，但读写速度均提高

      • 服务器容错：active-standby工作模式（提高可靠性，但因为始终只有一台服务器工作，所以对性能没有提升）

      • 数据库镜像

        • 分类

          • 双机互备援：两台均工作，互相监视
          • 双机热备份：一台工作，一台监视

        • SQL Server数据库镜像三种实现

          • 高可用性
          • 高保护性
          • 高性能

• 计算机病毒故障

  • 软件容错方法恢复

    • 数据转储

      • 静态：期间不能运行其他事务
      • 动态：允许与用户事务并发执行
      • 引入日志文件。使用动态转储的备份副本加上日志文件进行恢复。静态转储时非必须使用。
      • 检查点技术：最大限度减少数据库完全恢复时所必须执行的日志部分

      • 转储机制

        • 完全转储
        • 增量转储：只复制上次转储后发生变化的文件或者数据块
        • 差量转储（差异转储）：对最近一次数据库完全转储以来发生的数据变化进行转储

7.2 SQL Server恢复模式

• 简单恢复模式：不备份事务日志
• 完整恢复
• 大容量日志恢复模式：只对大容量操作进行最小记录，是完整恢复模式的附加模式

7.3 SQL Server备份机制

• 备份设备：备份的数据库文件可以建立在磁带上，或者磁盘上

• 备份类型

  • 数据库备份

    备份数据文件、日志文件，文件的存储位置以及数据库的全部对象。不影响用户对数据库数据进行增删改等操作，能将备份过程中的操作也备份下来

    • 完整
    • 差异 WITH DIFFERENTIAL

  • 文件备份

  • 事务日志备份：仅用于完整恢复模式和大容量日志恢复模式

    • 纯日志备份：不包含在大容量日志恢复模式下执行的任何大容量更改的备份
    • 大容量操作日志备份：不允许对其进行时点恢复
    • 结尾日志备份：可以包括纯日志记录或大容量操作日志记录。用以捕获尚未备份的日志记录，是恢复计划中最后一个相关备份

8 大规模数据库架构

8.1 分布式数据库

• DDBS:物理上分散的，但逻辑上集中的数据库系统

  • 分布式查询：除了考虑CPU代价和I/O代价，，还要考虑站点之间的通信代价

  • 分布式事务管理

    • 恢复控制：基于两阶段的提交协议
    • 基于封锁协议的并发控制

• DDB：DDBS中各场地上数据库的逻辑集合

  • 最基本特征：本地自治、非集中式管理，高可用性

  • 数据分布策略

    • 数据分片

      • 水平分片：每一行至少属于一个片段
      • 垂直分片：各片段中必须包含对应关系的KEY
      • 导出分片：依据其他关系属性的条件进行分片
      • 混合分片

    • 数据分配

      • 集中式：所有数据片段安排在一个场地
      • 分割式：全局数据被分割成若干片段，每个被分配在特定场地
      • 全复制式：全局数据有多个副本，每个场地有一个完整副本
      • 混合式：介于分割和全复制之间

  • 分布透明性

    • 数据分片独立性（分片透明性）：最高级别的透明性
    • 位置独立性：数据分配片的分配位置对用户是透明的。只需要考虑数据分片情况，无需了解各分片在各场地的分配情况
    • 数据复制独立性（局部数据模型透明性）

  • 体系结构

    • 全局外模式：用户视图
    • 全局概念模式：全体数据逻辑结构和特征
    • 分片模式：全局数据的逻辑划分视图
    • 分配模式：各片段到物理存放场地的映像
    • 局部概念模式：描述全局关系在场地上存储的物理片段的逻辑结构以及特征
    • 局部内模式：描述局部概念模式设计的数据在本场地的物理存储

8.2 并行数据库

• 系统结构

  • 共享内存
  • 共享磁盘
  • 无共享（适用于银行出纳，民航售票等OLTP类应用）
  • 层次结构：顶层无共享，底层共享内存或者磁盘

• 数据划分

  • 一维划分

    • 论转法：扫描整个关系
    • 散列划分：适合于点查询
    • 范围划分：利于范围查询和点查询

  • 多维划分

• 并行算法

  • 并行排序
  • 并行连接
  • 选择，消除重复，投影，聚集函数

8.3 云计算数据库架构

• 云计算：包含Internet上的应用服务及在数据中心提供这些服务的软硬件设施

• 云+数据库体系结构（以Google AppEgine）

  • 分布式编程环境Map/Reduce
  • 文件系统 Google File System
  • 分布式锁机构 Chubby

  • 大规模分布式数据库BigTable

    • 索引是行关键字，列关键字和时间戳
    • 可以随意增减行的数量，还可以对列的数量进行扩展
    • 稀疏存储的

8.4 XML数据库

• 定义：支持对XML格式文档进行存储和查询的DBMS

• 优势

  • 对半结构化数据（如网页内容）进行存取管理
  • 提供对标签和路径的操作
  • 便于对层次化数据进行操作

9 数据仓库与数据挖掘

9.1 基于数据仓库(Data Warehouse)的决策支持系统（DDS）

• 体系结构

  • 数据仓库是核心

    • 特性：面向主题的，集成的，非易失的且随时间变化
    • 体系结构：数据处理，数据管理和数据应用三个层次

    • 数据组织

      • 粒度：粒度越小，细节程度越高，空间代价越大

      • 数据分区

        • 系统层分区：各分区在逻辑上是一个表，但物理上属于不同表
        • 应用层分区：不同分区在逻辑和物理上都是不同的表

    • 元数据：描述数据的数据

      • 技术型
      • 业务型

    • 数据的更新维护

      • 维护策略

        • 实时维护
        • 延时维护
        • 快照维护：无法提供最新的数据

      • 导出数据的刷新

        • 重新计算
        • 增量式维护

  • 操作型系统是基本数据源

    • 与数据仓库之间有一个“操作型数据存储”(ODS)的数据层

      • 特点：面向主题的，集成的，可变的，数据是当前或接近当前的
      • 用以支撑及时OLAP和全局型OLTP应用
      • 分类：ODSI(秒级)；ODSII(小时级)；ODSIII(天级)

  • DDS是数据的需求者

• 数据

  • 操作型数据

  • DSS型数据（支持分析型应用）

    • 面向主题的
    • 不更新
    • 对性能要求宽松
    • 松弛的可用性

9.2 OLAP联机分析处理

• 多维分析

  • 钻取与卷起
  • 切片
  • 旋转

9.3 关联规则挖掘

• 关联规则的成立与否一般由“支持度”和“置信度”两个指标描述