数据库原理-考研复试准备

💡 一、ER图转换原则

实体类型的转换：
将每个实体类型转换成一个关系模式，实体的属性即为关系的属性，实体标识符即为关系的键。
二元联系类型的转换：
① 实体间的联系是1:1
可以在两个实体类型转换成两个关系模式中的任意一个关系模式的属性中加入另一个关系模式的键和联系类型的属性。
② 如实体间的联系是1:N
则在N端实体类型转换成的关系模式中加入1端实体类型转换成的关系模式的键和联系类型的属性。
③ 如实体间的联系是M:N
则将联系类型也转换成关系模式，其属性为两端实体类型的键加上联系类型的属性，而键为两端实体键的组合。
一元联系类型的转换：同二元联系

三元联系类型的转换：总是将三元联系类型转换成关系模式，其属性为三端实体类型的键加上联系类型的属性，而键为两端实体键的组合。

💡 二、数据库并发机制

在多个事务并发做数据库操作的时候，如果没有有效的避免机制，就会出现种种问题。大体上有四种问题，归结如下：

1、丢失更新，如果两个事务都要更新数据库一个字段，两个不同事物同时获得相同数据，然后在各自事务中同时修改了该数据，那么先提交的事务更新会被后提交事务的更新给覆盖掉。

2、脏读：一个事务读到另一个事务还没有提交的记录。

3、不可重复读：一个事务在自己没有更新数据库数据的情况，同一个查询操作执行两次或多次的结果不一致。

4、幻读，比如事务A读的时候读出了15条记录，事务B在事务A执行的过程中 增加 了1条，事务A再读的时候就变成了 16 条。

从程序员的角度看，锁分为以下两种类型：

悲观锁，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制。
乐观锁是一种更高效的机制，它的原理就是每次不加锁去执行某项操作，只在提交操作时检查是否违反数据完整性，如果发生冲突则失败并重试，直到成功为止，其实本质上不算锁，大多是基于数据版本（ Version ）记录机制实现。

💡 三、建立数据库的流程

数据库软件系统的建立流程如下：
（1）需求分析：了解用户的数据需求、处理需求、安全和完整性需求。
（2）概念结构设计：通过数据抽象，设计系统的概念模型，一般为E-R模型。
（3）逻辑结构设计：设计系统的模式和外部模式，特别是关系模型的基本表和视图。
（4）物理结构设计：设计数据的存储结构和访问方法，如索引的设计。
（5）系统实现：组织数据存储，编写应用程序，试运行。
（6）运行和维护：系统投入运行，进行长期维护

💡 四、数据库范式
数据库有六大范式。可以尽量减少数据冗余、避免插入异常、删除异常和更新异常。

关系数据库中的关系满足一定要求的，满足不同程度要求的为不同的范式。满足最低要求的叫第一范式，简称1NF；在第一范式的基础上满足进一步要求的称为第二范式，简称2NF，其余范式以此类推。

第一范式 1NF

强调数据表列的原子性，属于第一范式关系的所有属性都不可再分，即数据项不可分。在实际应用场景中，「电话」是可以被拆分为「家庭电话」以及「工作电话」的。所以我们说，「电话」这一列不符合原子性原则。

2NF
在第一范式的基础上，要求每个非主属性完全依赖于任何一个候选码。
举例学生成绩表（学号，姓名，科目，成绩，所属院系，系主任）
此表中由于成绩是由科目和学号共同确定的，只有当学号确定，科目确定，才能知道成绩。
所以这个表的码就是（学号，科目）这两个组成的。然后就是说主属性是学号和科目，非主属性是姓名，成绩，所属院系，系主任。
因为姓名可以不需要科目只由学号单独就能确定出来，所以说非主属性（姓名）部分函数依赖于码（学号，科目）。所以此表不符合第二范式，要想他符合2NF必须将此表分成两个表：学生表（学号，姓名，所属院系，系主任），成绩表（学号，科目，成绩）。
3NF，不存在非主属性对码的部分函数依赖和传递函数依赖。
举例学生表（学号，姓名，所属院系，系主任）
在这个表中，主属性是学号，由学号可以确定其他的属性，
但是由以下情况 学号–>所属院系，所属院系–>系主任，学号–>系主任
具有传递关系，即存在非主属性（系主任）对码（学号）的传递函数依赖，所以不符合第三范式。
要想符合3NF就拆分表：学生表（学号，姓名，所属院系） ，院系表（院系，系主任）。

BCNF 在 3NF 的基础上消除主属性对于码的部分与传递函数依赖。

💡 五、数据库完整性

1）数据库的完整性是指数据的正确性和相容性，其目的是为了防止数据库中的数据存在不符合语义的数据，也是防止数据库中存在不正确的数据。
（2）数据库的完整性有以下三种：实体完整性、参照完整性和域完整性。前两种是关系型数据库必须要满足的，域完整性是应用领域需要遵循的约束条件，体现了具体领域中的语义约束。
（3）实体完整性：要求每个表中只有一个主键，且主键必须唯一，不能为空。例如学生表中的学号必须唯一，且不能为空，否则无法对学籍信息进行操作。
（4）参照完整性：表中的外键必须是另一个表的主键的有效值，或者取空值。它是数据表间数据参照引用的约束，也就是对外键的约束。
（5）域完整性：针对某一具体关系型数据库的约束条件，它反映某一具体应用涉及的数据必须满足的语义要求。例如某个属性必须取唯一值、某个非主属性不能取空值等。

💡 六、数据库索引
数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引是对数据库表中一个或多个列的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息
常见的查询算法：顺序查找、二分查找、二叉排序树查找、哈希散列法、分块查找、平衡多路搜索树B树（B-tree）
索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现
优点：1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：1.索引需要占用数据表以外的物理存储空间
2.创建索引和维护索引要花费一定的时间
3.当对表进行更新操作时，索引需要被重建，这样降低了数据的维护速度。

💡 七、数据库的视图

视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。视图只供查询，数据不可更改

视图的作用： 1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。 2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限） 3、从而加强了安全性，使用户只能看到视图所显示的数据。 4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。

💡 八、数据库delete和drop的作用

一、 删除内容的不同：

drop：用于删除数据库（drop database 数据库名称）、删除数据表（ use 数据库名称 drop table 数据表1名称，数据表2名）或删除数据表字段（use 数据库名称 alter table 数据表名称 drop column 字段名（列名称））。
delete:删除数据表中的行（某一行或所有行）
格式：delete from 表名称 where 列名称=值；
truncate:清空表数据，但不删除这个表，只是把里面存的数据内容清空掉了

二、语句类型的不同：
delete：是dml（数据库操作语言）,这个操作会放到rollback segement（你在数据库中的一些存储空间，用来临时的保存当数据库数据发生改变时的先前值）中,事务提交之后才生效;如果有相应的trigger（触发器）,执行的时候将被触发.
drop是ddl（数据库定义语言）, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
truncate:删除表中的所有行，只删除数据，表的结构及其列约束依然保留。操作立即生效，不触发trigger; 三、删除速度的不同： 一般来说: drop>truncate>delete

💡 九、数据库触发器

（1）触发器(Trigger)是用户定义在关系表上的一-类由事件驱动的特殊过程。一旦定义，任何用户对表的增删改操作均由服务器自动激活相应的触发器，在DBMS(数据库管理系统)核心层进行集的完整性控制。
（2）触发器类似于约束，但是比约束更加灵活，可以实施比外键约束、CHECK约束更为复杂的检查和操作，具有更精细和更强大的数据控制能力。

💡 十、数据库的三级模式和两级映像

（1）模式 模式也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。模式实际上是数据库数据在逻辑级上的视图。一个数据库只有一个模式。
（2）外模式 外模式也称子模式或用户模式，它是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。外模式通常是模式的子集。一个数据库可以有多个外模式。
（3）内模式 内模式也称存储模式，一个数据库只有一个内模式。它是数据物理结构和存储方式的描述是数据在数据库内部的组织方式。
为了能够在系统内部实现这三个抽象层次的联系和转换，数据库管理系统在这三级模式之间提供了两层映像:外模式/模式映像和模式/内模式映像。这两层映像保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性。
(2）二级映像
①外模式/模式映像 模式描述的是数据的全局逻辑结构，外模式描述的是数据的局部逻辑结构。对应于同一个模式可以有任意多个外模式。对于每一个外模式，数据库系统都有一个外模式/模式映像，它定义了该外模式与模式之间的对应关系。
②模式/内模式映像 数据库中只有一个模式，也只有一个内模式，所以模式/内模式映像是唯一的，它定义了数据全局逻辑结构与存储结构之间的对应关系。
(3）数据独立性 当数据库的存储结构改变时，由数据库管理员对模式/内模式映像作相应改变，可以使模式保持不变，从而应用程序也不必改变。保证了数据与程序的物理独立性，简称数据的物理独立性。

💡 十一、数据库死锁

死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。

预防死锁的发生

① 一次封锁法：一次性将所有要使用的数据全部加锁，否则就不能继续执行存在的问题：扩大了封锁范围，降低了系统的并发度；

② 顺序封锁法：预先对数据对象规定一个封锁顺序，所有事务都按照这个顺序实施封锁。

死锁的诊断一般使用超时法或事物等待图法。

如果一个事务的等等事件超过了规定的时限，就认为发生了死锁。超时法实现简单，但可能误判死锁。并且，时限如果设置得太长，死锁发生后不能及时发现。

事务等待图动态地反映了所有事务的等待情况。并发控制子系统周期性地（比如每隔数秒）生成事务等待图，并进行检测。如果发现图中存在回路，则表示系统中出现了死锁。

💡 十二、主键和外键

主键：表用于唯一标识一条记录的一个或多个字段。不能为空，不能重复。用于唯一地标识一条记录；保证实体的完整性；加快数据库的操作速度。

外键：如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。

只能引用外表中的列的值或用空值，可以重复。

用于保持数据一致性、完整性；控制存储在外键表中的数据，使两张表形成关联(用来和其他表建立联系用的)。

💡 十三、数据库DBA、DBMS的作用

带有数据库的计算机系统，除具备一般的硬件、软件外，必须有用以存储大量数据的直接存取存储设备、管理并控制数据库的软件——数据库管理系统(DBMS)、管理数据库的人员——数据库管理员 (DBA)。这样的数据、硬件、软件和管理人员的总体构成数据库系统。数据库仅是数据库系统的一个组成部分。

DBMS 对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过 DBMS 访问数据库中的数据，数据库管理员也通过 DBMS 进行数据库的维护工作。DBMS 允许多个应用程序或多个用户使用不同的方法，在同一时刻或不同时刻去建立、修改和询问数据库。DBMS 的主要功能如下：

数据定义、数据操纵、数据库的运行管理、数据组织、存储与管理、数据库的维护、通信。

数据库管理员DBA，是从事管理和维护数据库管理系统(DBMS)的相关工作人员的统称，属于运维工程师的一个分支，主要负责业务数据库从设计、测试到部署交付的全生命周期管理。DBA 的核心目标是保证数据库管理系统的稳定性、安全性、完整性和高性能。

💡 用户能不能对数据库进行增删改查？
💡 十四、数据库的存储程序

一组存储和执行在数据库服务器端的程序。存储程序总是在服务器的进程或线程的内存中执行的。

存储程序分为3类，分别是：

• 存储过程：有输入和输出参数，可以执行一组sql语句。
• 存储函数：有一个返回值，可以执行一组sql语句，可以传递参数。
• 触发器：执行一组sql语句，由事件驱动自动执行，不能传递参数。

💡 十五、数据库的事务

事务是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。事务和程序是两个概念。一般来说 一个程序中包含多个事务。

💡 sql server中事务的语句有哪些？

事务是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。事务和程序是两个概念。一般来说 一个程序中包含多个事务。

💡 十六、数据库怎么消除冗余，举个例子

数据冗余是同一数据存储在不同数据文件中的现象。

产生这种现象基于重复存储或传输数据以防止数据的丢失、简化流程、方便处理、大量数据的索引。

数据库范式能帮忙解决数据冗余问题。

比如，定时将过期数据移动至硬盘里，当前数据库只存放近一年的数据。

比如，遵从数据库表建立合适的数据表属性。供应链中将仓库按实体位置分为成品仓和原料仓。

💡 十七、数据库的ACID特性（数据库有哪些特性） 什么是数据库一致？

(1）原子性(Atomicity)
事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做。
(2)一致性(Consistency)
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。即指系统从一个正确的状态到另一个正确的状态。举个例子，银行有A向B转账，A有50元，向B转100元，这时事务会提示余额不足，回滚，这就保证了银行系统的一致性，因为余额不可为负数。
(3）隔离性(Isolation)
一个事务的执行不能被其他事务干扰。即一个事务的内部操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
（4）持续性(Durability)
持续性也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其执行结果有任何影响。
（5）恢复技术能保证事务的原子性、一致性和持续性。

💡 十九、数据库了解哪些系统？

库存、人力、物流。ERP系统企业资源管理计划是指组织用于管理日常业务活动的一套软件，这些活动包括会计、采购、项目管理、风险管理和合规性、供应链运营等等。完整的 ERP 套件还包括企业绩效管理软件，用于帮助企业针对财务结果制定计划和编制预算，以及预测和报告财务结果。

这些 ERP 系统将大量业务流程联系在一起，实现了各业务流程之间的数据流动。通过从多个来源收集组织的共享事务数据，ERP 系统可以整合信息源，以消除数据重复并确保数据的完整性。

💡 二十、数据库设计模式有哪些？

1、主扩展模式通常用来将几个相似的对象的共有属性抽取出来，形成一个”公共属性表“，且“公共属性表”与“专有属性表”是“一对一”的关系。扩展表的主键既是扩展表的主键也是主表的外键。

2.主从模式应用场景最多。是典型的一对多的关系。比如贴吧的实现，整个吧就是一个主表。而贴吧有许多的从表就是不同楼主发的帖子，而每个帖子有用很多从表那就是每个楼所对应的信息。

3.名值模式，通常用来描述在系统设计阶段不能完全确定属性的对象，这些对象的属性在系统运行时会有很大的变更，或者是对个对象之间的属性存在很大的差异。

4、多对多模式，也是比较常见的一种数据库设计模式，他所描述的两个对象部分主次，地位对等，互为一对多关系。多对多模式需要在两个表之间建立一个关联表，这个关联表是多对多关系的核心。

两个对象之间互为一对多关系，则使用“多对多模式”。比如对于A表来说，一条记录对应着B表的多条记录，反过来对于B表来说，一条记录也对应着A表的多条记录