软件中级设计师 - 数据库

数据库设计

**数据库DBS的组成：**数据库，硬件，软件，人员

三级模式-两级映像

• 内模式-物理模式

文件级，内部视图

• 概念模式–模式

表级，物理级数据库，DBA视图

• 外模式- 用户/子模式

视图级，用户视图，概念级数据

概念模式-内模式映射 和 外模式-概念模式映射

数据库设计

• 需求分析（产出物：数据流图， 数据字典，需要说明书）
• 用户需求分析
• 概念结构设计（ER模型）
• 初始关系模式
• 逻辑结构设计 （关系模式）
• 范式和关系模式对关系优化
• 物理设计
  • 设计数据库表

数据模型三要素：

数据结构（所研究的对象类型集合）

数据操作（对数据库中各种对象的实例允许执行的操作的集合）

数据的约束条件（一组完整性规则的集合）

常用的数据模型

• 层次模型 （非关系模型）
• 网状模型（非关系模型）
• 关系模型（二维表格 ，数据库表格）
• 面向对象数据模型
• 对象关系数据模型
• 半结构化数据模型

分布式数据库基本概念

• 分片透明：用户或者应用程序不需要知道逻辑上访问的具体是怎么分块存储的
• 复制透明：采用复制技术的分布式方法，用户不需要知道数据是复制到哪些结点，如何复制的
• 位置透明：用户无需知道数据存放的物理位置
• 逻辑透明：局部数据模型透明，用户或者应用程序无需知道局部场地使用的是哪种数据模型

ER 图

关系模式

• 一对一

• 一对多

• 多对多

关系代数

并，交， 差， 笛卡尔积， 投影， 选择， 联接

笛卡尔积(×)、自然联接 ⋈ 、投影(π)、选择(σ)

例如： 关系S1， 关系S2

并 ： S1 U S2， 两个关系中元组之和
交：S1 ∩  S2, 两个关系中相同的元组
差：S1-S2, 从S1中除掉S1S2交集元组
笛卡尔积：S1XS2   ,最终属性列S1+S2，最终记录数S1*S2
两个元组乘积，属性列为S1和S2属性列的和
投影： π1，2（S）  表示投影第1，2列属性
选择： σNo=123（S） 表示选择No值为123的记录 ； σ1=3（R*S） 表示选择R的第一列和S的第三列相同的记录
自然联接 ⋈： sql中的 并查询（join） -- 如果两个列名相同，且元素相同，则进行连接

笛卡儿积 和 自然连接的转化

• 考！

数据库范式和分解

键与约束

来自： https://blog.csdn.net/weixin_44177594/article/details/105749437

键

超键：能唯一标识此表的属性的集合，冗余的候选键

候选键：超键中去掉冗余的属性，**剩余的属性（元组）**就是候选键。候选键是能确定所有属性的键

主键：任选一个候选键，即可作为主键。

外键：其他表中的主键。

主属性：候选键内的属性作为主属性，其他属性为非主属性。

举例：
比如一个小范围的所有人，没有重名的，考虑以下属性

身份证 姓名 性别 年龄

身份证唯一，所以是一个超键
姓名唯一，所以是一个超键
（姓名，性别）唯一，所以是一个超键
（姓名，性别，年龄）唯一，所以是一个超键
–这里可以看出，超键的组合是唯一的，但可能不是最小唯一的

（身份证，姓名）用于确定所有属性，也是候选键
–这里可以看出，候选键是没有多余属性的超键

约束

实体完整性约束：即主键约束，主键值不能为空，也不能重复

参照完整性约束：即外键约束，外键必须是其他表中已经存在的主键的值，或者为空。

用户自定义完整性约束：自定义表达式约束，如设定年龄属性值为0-150之间

函数依赖

部分函数依赖：A可以确定C，(A,B)也可以确定C，(A,B)中的一部分（即A）可以确定C，称为部分函数依赖。

传递函数依赖：当A和B等价时，A可确定B，B可确定C，则A可确定C时传递依赖；若A和B等价，则不存在传递，则可直接确定C。

范式

常考！https://blog.csdn.net/qq_65201998/article/details/125114921

• 范式越高，冗余越低，效率越高！

第一范式(1NF)：属性不可分割，列不可再分！

比如现在有一列中存着一个地址：江苏省苏州市姑苏区。
数据库这一列中就可以再细分为哪个省哪个市。

第二范式(2NF)：满足第一范式；一张表只描述一件事情！

如果一个学校考试有许多科目，各科成绩要细分的话（比如需要统计出选择题，填空题和解答题各个的分数）那么每一门就需要创建多张表。

第三范式(3NF)：满足第二范式；表中的每一列和主键都是直接依赖关系，而不是间接依赖！

• 有时候为了提高效率，我们会不遵守第3NF！

大家QQ都发过说说吧。
你发一篇说说，那么你这篇说说的内容，作者，时间日期都会存到数据库当中。
当你的好友来评论时，你在下面进行回复，你回复的内容也会被存到数据库当中，那么这个回复的内容要不要把作者（也就是你）存进数据库呢？
其实是不用的。因为你在你的说说下回复了评论你的人
那么你这个回复是不是就可以通过评论你的这个人而找到发这篇说说的作者，那不就是找到回复的作者（你）。
这样就可以有效避免数据库数据冗余

BC范式（BCNF）【常考！】

R属于BCNF当且仅当其F中每个依赖的决定因素必定包含R的某个候选码，实例如下：

• 第一范式：所有属性都不可以再分割为两个活多个分量。
• 第二范式：非主属性 完全依赖于 全部主属性，而不能只依赖于主键的一部分（无部分依赖）。
• 第三范式：主键要直接决定所有非主属性，不能通过非主属性确定**（无传递依赖）**
• 候选键中的属性都包含依赖项（图中依赖项入度为0）

设有关系模式R(S,T,J),依赖集为F={SJ->T,T->J}

画图可以求出候选键为(S,T),(S,J) 【已知最少条件能获取到所有值的集合】

主属性为S，T，J 【候选键中的元素】
满足1，2，3范式！

F的依赖项为SJ,T , R的候选码为ST,SJ , 候选码中没有F的依赖项T，因此不满足BC范式！！

例：给定关系模式R(U,F),U={A,B,C,D} ,F={AB->C,Cd->B}
关系R（？） 两个候选关键字ACD , ABD
且分别有（？）4个主属性，0个非主属性

模式分解

范式之间的转化一般都是通过拆分属性，即模式分解，将具有部分函数依赖和传递依赖的属性分离出来，来达到一步步优化，一般分为以下两种情况：

保持函数依赖分解

对于关系模式R，有依赖集F，若对于R进行分解，分解出来的多个关系模式，保持原来的依赖集不变，则为保持韩式依赖的分解。另外，注意要消除掉冗余的依赖（如传递依赖）。

例：
关系模式R(A,B,C)  函数集F(A->B.B->C,A->C)
存在函数依赖， 保持函数依赖分解后
R1(A,B) , R2(B,C)
F1(A->B)  F2(B->C)

无损分解

分解后的关系模式能够还原出原关系模式，就是无损分解，不能还原就是有损，可以通过表格法求解。

有损分解

解决冗余进行分解

• 无损+保持函数依赖

事务管理_故障备份_数据仓库

事务管理

四种特性

原子性：要么全做，要么全不做，无法再分割

一致性：事务发生后数据是一致的

隔离性：事物之间互不干扰

持久性：数据库操作结果是持久的

并发控制的问题【重点】

丢失更新：后者操作覆盖当前操作

不可重复读：两次读取不一致

读脏数据：后者操作数据后回滚，但前者读取到操作的数

三级封锁协议

X锁是排它锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A,其他事务都 不能再对A加任何类型的锁，直到T释放A上的锁。

**S锁是共享锁。**若事务T对数据对象A加上S锁，则只允许T读取A,但不能修改A,其 他事务只能再对A加S锁（也即能读不能修改），直到T释放A上的S锁。

一级封锁协议：事务在修改数据R之前必须**先对其加x锁，直到事务结束才释放。**可解决丢失更新问题。

二级封锁协议：一级封锁协议的基础上加上事务T在读数据R之前必须先对其加s锁，读完后即可释放s锁。可解决丢失更新、读脏数据问题。

三级封锁协议：一级封锁协议加上事务T在读取数据R之前先对其加s锁，直到事务结束才释放。可解决丢失更新、读脏数据、数据重复读问题。

故障备份

数据库故障

数据库系统产生的故障如下所示：

故障类型	故障原因
事务内部故障	本身逻辑（可预期）、运算溢出（不可预期）
系统故障	系统停止运行任何事件，如操作系统故障、停电
介质故障	物理介质损坏，几率小破坏性最大
计算机病毒	人为的故障和破坏，在计算机程序中插入的破坏

数据库备份

静态转储：即冷备份，指在转储期间不允许对数据库进行任何存取、修改操作：

优点是非常快速的备份方法、容易归档（直接物理复制操作）；

缺点是只能提供到某一时间点上的恢复，不能做其他工作，不能按表或按用户恢复。

动态转储：即热备份，在转储期间允许对数据库进行存取、修改操作，因此，转储和用户事务 可并发执行；

优点是可在表空间或数据库文件级备份，数据库扔可使用，可达到秒级恢复，

缺点是不能出错，否则后果严重，若热备份不成功，所得结果几乎全部无效。

完全备份：备份所有数据。

差量备份：仅备份上一次完全备份之后变化的数据。

增量备份：备份上一次备份之后变化的数据。

日志文件：在事务处理过程中，DBMS把事务开始、事务结束以及对数据库的插入、删除和修 改的每一次操作写入日志文件。

一旦发生故障，DBMS的恢复子系统利用日志文件撤销事务对 数据库的改变，回退到事务的初始状态。

数据仓库

数据仓库是一种特殊的数据库，也是按数据库形式存储数据的，但是目的不同：数据库经过长时间的运行里面的数据会保存的越来越多，就会影响系统运行效率，对于某些程序而言，很久之前的数据并非必要的因此，可以删除掉以减少数据，增加效率，考虑到删除这些数据比较可惜，因此，一般都将这些数据从数据库中提取出来保存到另外一个数据库中，称为数据仓库。

由此，可知，数据仓库的目的不是为了应用，是面向主题的，用来做数据分析，集成不同表，而且是相对稳定的，一般不会做修改，同时会在特定的时间点，做大量的插入，反映历史的变化。

数据挖掘的分析方法

关联分析：关联分析主要用于发现不同事件之间的关联性，即一个事件发生的同时，另一个事件也经常发生。

序列分析：序列分析主要用于发现一定时间间隔内接连发生的事件，这些事件构成一个序列，发现的序列应该具有普遍意义。

分类分析：分类分析通过分析具有类别的样本特点，得到决定样本属于各种类别的规则或方法。分类分析时首先为每个记录赋予一个标记（一组具有不同特征的类别），即按标记分类记录，然后检查这些标定的记录，描述出这些记录的特征。

聚类分析：聚类分析是根据“物以类聚”的原理，将本身没有类别的样本聚集成不同的组，并且对每个这样的组讲行描述的过程

商业智能-BI

BI系统主要包括数据预处理、建立数据仓库、数据分析和数据展现四个主要阶段。

数据预处理是整合企业原始数据的第一步，它包括数据的抽取(Extraction)、转换 (Transformation)和加载(Load)三个过程(ETL过程);

建立数据仓库则是处理海量数据的基础：

数据分析是体现系统智能的关键，一般采用联机分析处理(OLAP)和数据挖掘两大技术。联 机分析处理不仅进行数据汇总/聚集，同时还提供切片、切块、下钻、上卷和旋转等数据分 析功能，用户可以方便地对海量数据进行多维分析。数据挖掘的目标则是挖掘数据背后隐藏 的知识，通过关联分析、聚类和分类等方法建立分析模型，预测企业未来发展趋势和将要面 临的问题：

在海量数据和分析手段增多的情况下，数据展现则主要保障系统分析结果的可视化。

反规范化技术

由前面介绍可知，规范化操作可以防止插入异常、更新、删除异常和数据冗余，一般是通过模式分解，将表拆分，来达到这个目的。

但是表拆分后，解决了上述异常，却不利于查询，每次查询时，可能都要关联很多表，严重降低了查询效率，因此，有时候需要使用反规范化技术来提高查询效率。

技术手段包括：增加派生性冗余列，增加冗余列，重新组表，分割表。

主要就是增加冗余，提高查询效率，为规范化操作的逆操作。

大数据

特点：大量化、多样化、价值密度低、快速化。

大数据和传统数据的比较如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PsVm0yxy-1668504926152)(数据库.assets/996a9a9c6276408cb37797016cef8d63.png)]

要处理大数据，一般使用集成平台，称为大数据处理系统，其特征为：

高度可扩展性、高性能、高度容错、支持异构环境、较短的分析延迟、易用且开放的接口、较低成本、向下兼容性。

⭐SQL

插入权限语句

GRANT 权限类型 ON 权限级别值 TO user [IDENTIFIED BY '密码'] WITH GRANT OPTION;

• WITH GRANT OPTION获得了权限的用户开可以将权限授予他人

创建函数

create function f_name         --f_name 函数名称
(@a varchar(20),@b int)	--@a 第一个参数 varchar类型 @b第二个参数 int类型 
returns varchar(20)             --返回值类型
as
begin
	declare @c varchar(30)  --创建变量，在函数体中使用
	set @c='基本操作'          --对变量赋值
	return (@c)			--返回值，这里注意一下，好像要打括号才能把值返回出去
end