​

Database Principles and Applications Exam Related-2023

​

1. Introduction

1. 数据库的4个基本概念 P10

• data, database, database management system, database system

2. 数据库的定义 P14

• A database is an organized, large-scale, shared collection of data stored in a computer for a long time

3. 数据库（系统）的基本特征 P14 / P37 - P41

• Shareable by users, with low redundancy, easy expansion, and strong data independence

4. 数据库管理系统的主要功能 P17 - P19

• Data definition function (DDL), data organization, storage and management, data manipulation function (DML), database transaction management and remote management, database establishment and maintenance function, communication function of database and other software systems in the network

5. 数据库系统的构成 P20 - P21

• Databases, database management systems (and their development tools), applications and database administrators

6. 数据库系统的特点 P37-P41

• The data is structured, with high data shareability, low redundancy, easy expansion, and high data independence. The data is managed and controlled uniformly by DBMS.

7. 数据独立性高如何理解 P40

• Refers to an application’s database schema and data storage method that does not depend on other applications or operating system platform-specific implementations.

8. 数据库管理系统如何控制数据共享 P41

• Database access permission control, database metadata control, data encryption, database replication, database partitioning,

9. 概念模型、逻辑模型和物理模型的概念 P47

• The conceptual model is an abstraction and simplification of the real world. It is used to describe the data in the database and the relationship between the data. It is the core of database design and the basis of database modeling;
• A logical model is a logical representation of a conceptual model, which is used to store data in a database and the relationships between data;
• The physical model is the representation of the logical model on a physical storage device, which is used to store and manage data in the database;

10. 数据模型的组成要素 P57

• Entities, attributes, relationships, constraints between entities, data types and precision

11. 数据模型中的数据结构 P58

• The data structure in the data model refers to the way data is stored and represented in the database;
• Tables, views, indexes, primary and foreign keys,

12. 数据模型中的数据操作 P59

• Add, delete and modify, insert operations, query operations, data backup and recovery operations, transaction operations

13. 数据模型中的完整性约束条件 P60

• Refers to a set of rules used to ensure the integrity of data in a database;
• Entity integrity, attribute integrity, referential integrity, user-defined integrity

14. 关系模型谁提出的？ P84

• E.F. Codd, a researcher at the San Jose Research Laboratory of IBM Corporation in the United States

15. 类型和值的概念 P95 - P96

• Type: Type is an identifier used to describe data structure and data type. They usually appear in the form of keywords;
• Common types: integer, character and date;
• Value: Value refers to the specific content and value of data, usually data input by the user or generated by the program;

16. 数据库系统的三级模式结构 P99 - P105

• Physical layer: The physical layer refers to the actual physical device where data is stored, ensuring the reliability and security of the data;
• Conceptual layer: The conceptual layer refers to the data model and data structure in the database, which is the core of database design and implementation;
• Logical layer: The logical layer refers to the application program in the database management system and is the direct object of database management and maintenance.

17. 数据库系统的两级映射功能 P107 - P114

​

2. Relational database

1. 关系数据库的提出者和时间 P4

• E.F.Codd

2. 何为关系 P7

• It is a basic concept of database. It is used to describe the relationship between data in the database. It is the relationship between entities. It describes the connection and interaction between entities. It can be one-to-one, one-to-many, and many-to-many.

3. 域 P9

• A domain is a collection of values ​​of the same data type

4. 笛卡尔积 P10 - P14

• Cartesian product is a set operation on the field

5. 关系和笛卡尔积的关系 P17，P19

• Relations are finite subsets of Cartesian products

6. 码 P18

• Code refers to a coding method used to represent specific data or entities, usually consisting of a set of numbers, letters or other symbols.

7. 三类关系 P20

• One-to-one: One-to-one relationship between two tables;
• One-to-many: One-to-many relationship between two tables;
• Many-to-many: refers to the many-to-many relationship between three tables or above;

8. 基本关系的性质 P22

• 1. The columns are homogeneous, that is, the components in each column are the same type of data and come from the same domain.
• 2. Different columns can come from the same domain. Each column is called an attribute. Different attributes should be given different attribute names.
• 3. The order of the columns does not matter, that is, the order of the columns can be exchanged arbitrarily.
• 4. The candidate codes of any two tuples cannot have the same value.
• 5. The order of rows does not matter, that is, the order of columns can be exchanged arbitrarily.
• 6. The components must take on atomic values, that is, each component must be an indivisible data item.

9. 关系模式的定义 P26

• The description of a relationship is called a relationship description

10. 基本的关系操作 P36

• Query operations, two parts: insertion, deletion and modification operations
• Query operations can be divided into selection, projection, connection, division, union, difference, intersection, and Cartesian product

11. SQL的4大功能 P39

• Data definition, data query, data manipulation, data control

12. 三类完整性约束 P41

• Entity integrity, referential integrity, user-defined integrity

13. 实体完整性规则 P43 - P44

• If attribute (referring to one or a group of attributes) A ​​is the main attribute of basic relationship R, then A cannot take a null value

14. 外码 P50

• Suppose F is an attribute or a set of attributes of the basic relation R, but not the key of the relation R, and KS is the primary key of S. If F corresponds to KS, then F is said to be the foreign key of R, and the basic relationship R is called the reference relationship, and the basic relationship S is the referenced relationship or target relationship.

14. 参照完整性 P55 - P56

• Also known as referential integrity, it is a constraint between two related tables.

15. 用户定义的完整性 P60

• User-defined integrity is a constraint for a specific relational database, which reflects the semantic requirements that the data involved in a specific application must meet.

16. 关系代数运算符 P64

• Union, difference, intersection, Cartesian product and selection, projection, connection, division operation

17. 传统的集合运算 P66 - P74

• Union, difference, intersection, Cartesian product

18. 专门的关系运算（除运算除外） P85 - P103

• Selection, projection, concatenation, division operations

19. 关系运算符的运用 P110

• Learn by yourself

​

3. SQL

1. SQL的中文和英文 1P51

• Structured Query Language SQL

2. SQL的功能和特点 1P55 - 1P61

• Comprehensive and unified, highly non-procedural, set-oriented operation mode, providing multiple usage methods with the same syntax structure,
• Simple language, easy to learn and use
• Data query, data manipulation, data definition, data control

3. 内模式/模式/外模式 与 存储文件/基本表/视图 之间概念关联 1P64 - 1P67

• External schema: also called sub-schema or user schema, is a description of the logical structure and characteristics of local data that users can see and use;
• Schema: Also known as logical schema, it is a description of the logical structure and characteristics of all data in the database, and is a common data view of all data.
• Internal schema: also called storage schema, is a description of the physical structure and storage method of the database, and is the way data is represented inside the database.
• The data is provided to the user according to the description of the external mode and stored on the hard disk according to the description of the internal mode. The mode is between the external and internal modes and involves neither external access nor internal storage, thereby playing an isolation role and conducive to the independence of the data. The internal mode relies on the global logical structure, but can be independent of specific storage devices.
• Basic table: an independent table;
• View: a virtual table, which is a table derived from one or several basic tables;
• The database only stores the definition of the view and not the data corresponding to the view. These data are still stored in the basic table of the derived view. When the data in the basic table changes, the data queried from the view also changes.

4. 数据定义的所有代码 1P74 - 1P99

• Create: create schema; create table; create view; create index;
• Delete: drop schema; drop table; drop view; drop index;
• Modify: alter table; alter index

5. 数据字典是什么 1P101

• The data dictionary is a set of system tables within the relational database management system. It records all definition information in the database, including relational schema definitions, view definitions, index definitions, integrity constraint definitions, operating permissions of various users on the database, statistical information, etc.

6. 单表查询 1P106 - 1P137

• Single table query refers to a query involving only one table

7. 连接查询 2P8 - 2P24

• If a query involves more than two tables at the same time, it is called a join query.

8. 嵌套查询（EXISTS除外） 2P26 - 2P48

• A SELECT-FROM-WHERE statement is called a query block, and a query that nests a query block in the conditions of the WHERE clause or HAVING phrase of another query block is called a nested query.
• If the query conditions of a subquery depend on the parent query, it is called a related subquery, and the entire query statement is called a related nested query.
• Otherwise, it is called an irrelevant subquery.

9. 集合查询 2P58 - 2P63

• Union operation: UNION, intersection operation: INTERSECT, difference operation: EXCEPT

10. 基于派生表的查询 2P65 - 2P66

• Subqueries can not only appear in the WHERE clause, but also in the FROM clause. At this time, the temporary derived table generated by the subquery becomes the query object of the main query.

11. 插入数据 3P6 - 3P13

• INSERT INTO (table name) VALUES (values)
• INSERT INTO (table name) subquery

12. 修改数据 3P15 - 3P20

• UPDATE (table name) SET (column name) WHERE (condition)

13. 删除数据 3P22 - 3P27

• DELETE FROM (table name) WHERE (condition)

14. Drop和DELETE的区别

• Drop is generally used to delete overall data, such as tables, schemas, indexes, views and integrity restrictions.
• DELETE is used to delete local data, such as a tuple in a table

15. 空值相关 3P29 - 3P37

• The null value is a very special value that contains uncertainty.
• Attribute definitions (domain definitions) with NOT NULL constraints cannot take null values, attributes with UNIQUE restrictions cannot take null values, and code attributes cannot take null values.

16. 视图的特点 3P39

• View: a virtual table, which is a table derived from one or several basic tables;
• The database only stores the definition of the view and not the data corresponding to the view. These data are still stored in the basic table of the derived view. When the data in the basic table changes, the data queried from the view also changes.
• 17. 建立视图 3P42 - 3P52
• CREATE VIEW (view name) AS (subquery) [WITH CHECK OPTION]

18. 删除视图 3P53

• DROP VIEW (view name) [CASCADE]
• CASCADE can delete other views exported by this view.

19. 视图更新的限制 3P66 - 3P68

• 1. If the view is exported from more than two basic tables, this view is not allowed to be updated;
• 2. If the fields of the view come from field expressions or constants, INSERT and UPDATE operations are not allowed on this view, but DELETE operations are allowed;
• 3. If the fields of the view come from aggregate functions, this view is not allowed to be updated;
• 4. If the view definition contains a GROUP BY statement, this view is not allowed to be updated;
• 5. If the view definition contains the DISTINCT phrase, this view is not allowed to be updated;
• 6. If there is a nested query in the view definition, and the table involved in the FROM clause of the inner query is also the basic table for exporting the view, this view is not allowed to be updated;
• 7. A view defined on a view that is not allowed to be updated is not allowed to be updated.

20. 视图的作用 P72

• 1. Simplify user operations;
• 2. Enable users to view the same data from multiple perspectives;
• 3. Provides a certain degree of logical independence for reconstructing the database;
• 4. Able to provide security protection for confidential data;
• 5. Appropriate use of views can express queries more clearly.

​

4. Database security

1. 数据库安全性问题的提出 P4

• Database security refers to protecting the database from data leakage, alteration or destruction caused by illegal use

2. 数据库的不安全因素 P8 - P11

• 1. Malicious access and damage to the database by unauthorized users;
• 2. Important or sensitive data in the database is leaked;
• 3. The vulnerability of the security environment;

3. 数据库安全性控制的流程 P35

• 1. User identification and authentication (static password authentication, dynamic password authentication, biometric authentication, smart card authentication);
• 2. Access control (autonomous access control, mandatory access control);
• 3. Authorization: grant and revoke (grant, revoke, permission to create database schema);
• 4. Database roles (creation of roles, authorization of roles, granting a role to other roles or users, withdrawal of role permissions);

4. 用户身份鉴定的概念和方法 P38 - P43

• Static password authentication:
• Dynamic password authentication:
• Biometric authentication:
• Smart card authentication:

5. 存取控制 P46

• The access control mechanism mainly includes two parts: defining user permissions and checking legal permissions;

6. 常用的两种存取控制方法 P47 - P48

• Autonomous access control: Users have different access permissions for different database objects, and different users also have different permissions for the same object. Users can also delegate their access permissions to other users, so autonomous access control is very flexible;
• Mandatory access control: Each database object is marked with a certain confidentiality level, and each is subsequently granted a certain level of license. For any object, only users with legal licenses can access it, so mandatory access control is relatively strict.
• 7. 权限授予的SQL代码 P54 - P68

GRANT（权限）ON（对象类型）（对象名）TO（用户）[WITH GRANT OPTION]

8. 权限回收的SQL代码 P69 - P73

REVOKE（权限）ON（对象类型）（对象名）FROM（用户）[CASCADE|RESTRICT]

9. 数据库角色的概念 P76

• A database role is a named set of permissions related to database operations. A role is a collection of permissions.

10. 数据库角色的SQL代码 P77 - P83

CREATE ROLE（角色名）；

GRANT（权限）ON（对象类型）（对象名）TO（用户）[WITH GRANT OPTION]；

REVOKE（权限）ON（对象类型）（对象名）FROM（用户）[CASCADE|RESTRICT]

• The view mechanism indirectly implements the user permission definition that supports access predicates. Through the view mechanism, the data to be kept confidential is hidden from users who do not have access rights, thereby automatically providing a certain degree of security protection for the data.

​

5. Database integrity

1. 数据库完整性的概念 P4

• Database integrity refers to the correctness and compatibility of data;
• The correctness of the data means that the data conforms to the semantics of the real world and reflects the current actual situation;
• Data compatibility means that the data of the same object in the database in different relational tables is logical

2. 数据库完整性和安全性的异同 P5

• Data integrity is to prevent the existence of data that does not conform to semantics in the database, that is, to prevent the existence of incorrect data in the database;
• Data security is to protect the database from malicious damage and illegal access.

3. 数据库管理系统如何维护数据库的完整性 P6 - P10

• 1. Provide a mechanism for defining integrity constraints;
• 2. Provide methods for integrity checking;
• 3. Handle breach of contract;

4. 实体完整性定义的代码 P13 - P16

（列名）PRIMARY KEY；

PRIMARY KEY（列名）（必须设置列名不为空值）

6. 实体完整性的检查和违约处理 P18

1. Check whether the main code value is unique. If not, reject insertion or modification;
2. Check whether each attribute of the main code is empty. As long as one of the attributes is empty, the insertion or modification will be refused.

7. 参照完整性定义的代码 P24 - P25

• Referential integrity uses the FOREIGN KEY phrase in CREATE TABLE to define which columns are foreign keys, and the REFERENCES phrase is used to indicate which tables’ primary keys these foreign keys refer to.

FOREIGN KEY（列名）REFERENCES （表名）（列名）

8. 参照完整性的检查和违约处理 P27 - P35

• Reject (NO ACTION) execution: The change operation is not allowed to be executed. This policy is generally set as the default policy;
• Cascade (CASCADE) operation: When deleting or modifying a tuple in the referenced table causes inconsistency with the reference table, delete or modify all tuples in the reference table that cause inconsistency.
• Set to null: When deleting or modifying a tuple in the referenced table causes inconsistency, set the corresponding attributes of all tuples in the reference table that cause inconsistency to null.

9. 用户定义完整性 P40 - P50

• User-defined integrity is the semantic requirement that data for a specific application must meet.

1. Constraints on attributes p163
2. Definition of constraints on attributes
3. Checking and violation handling of constraints on attributes
4. Constraints on tuples p163
5. Definition of constraints on tuples
6. Checking and violating constraints on tuples
7. Integrity Constraint Named SQL P53 - P57

CONSTRAINT（完整性约束条件名）（完整性约束条件）

完整性约束条件包括
NOT NULL，UNIQUE，PRIMARY KEY，FOREIGN KEY，CHECK短语
可以使用ALTER TABLE语句修改表中的完整性限制

ALTER TABLE Student DROP CONSTRAINT C4;

ALTER TABLE Student ADD CONSTRAINT C1 CHECK(Sno BETWEEN 900000 AND 999999);

10. 触发器的定义、激活与删除 P68 - P78

delimiter 自定义结束符号
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin
    -- 触发器内容主体，每行用分号结尾
end
自定义的结束符合

delimiter ;

• A trigger is a special event-driven process defined by the user on a relational table.
• Definition of trigger P168
• The execution of a trigger is activated by a triggering event and is automatically executed by the database server.
• 1. Execute the BEFORE trigger on the table
  2. SQL statement to activate trigger
  3. Execute the AFTER trigger on the table

删除触发器的SQL语法：DROP TRIGGER（触发器名）ON（表名）

​

6. Relational Data Theory

• Data dependency is a constraint relationship between the internal attributes of a relationship. This constraint relationship is a correlation between data reflected by the equality of values ​​between attributes. It is the abstraction of the interconnections between real-world attributes, the inherent nature of data, and the embodiment of semantics.

1. 现实世界已知语义得到的函数依赖 P13 - P14

• Functional dependency is a ubiquitous type of data dependency
• Improper functional dependencies can cause many problems:
• 1. Data redundancy
• 2. Update exception
• 3. Insertion exception
• 4. Delete exceptions

2. 函数依赖 P22 - P26

• Functional dependency, like other data dependencies, is a semantic category concept, and a functional dependency can only be determined based on semantics.

3. 平凡函数依赖与非平凡函数依赖 P27 - P28
4. 完全函数依赖与部分函数依赖 P29 - P30
5. 传递函数依赖 P31

6. 1NF、2NF、3NF、BCNF的定义以及判断 P41 - P56，P81

(1NF, Normal Form) If the domain value of each attribute A in a relational schema R is atomic, that is, the attribute value cannot be subdivided, then the relational schema R belongs to the first normal form, abbreviated as R ∈ 1NF. If every relational schema in database schema R is 1NF, database schema R∈1NF.

​

Given R = {A, B, C} and the functional dependency set F = {B → C, AC → B}, determine whether the relation schema is in 2NF

​

Given R = {A, B, C, D} and the functional dependency set F = {A → C, AD → B}, determine whether the relation schema is in 2NF

​

Given R(A, B, C) and the functional dependency set F = {B → C, AC → B}, determine whether the relation schema is in 3NF

​

Given R(A, B, C, D) and the functional dependency set F = {AB → C, C → D}, determine whether the relation schema is in 3NF

​

7. Database design

1. 数据库设计的概念 1P6

3. 数据库高效率的运行环境 1P7

4. 数据库设计的特点 1P10 - 1P12

5. 数据库设计的基本步骤 1P18

• Requirements design
• conceptual structural design
• Logical structure design
• physical structure design
• Database implementation
• Database operation and maintenance

6. 数据库设计各个阶段的数据设计描述 1P25

• P 209

7. 需求分析的任务 1P36 - 1P38

• information request
• processing request
• Security and integrity requirements

8. 需求分析的方法 1P40

• Investigate the status of the organization
• Investigate the business activities of various departments
• Based on familiarity with business activities, assist users to clarify various requirements for the new system, including information requirements, processing requirements, security and integrity requirements
• Determine the boundaries of the new system

• Follow-up homework
• Hold an investigation meeting
• Please introduce someone
• ask
• Please fill in the design questionnaire
• View records

9. E-R模型的提出者 1P58

• P.P.S.Chen

10. 实体之间联系的类型 1P59 - 1P67

• Relationship between two entity types: one-to-one, one-to-many, many-to-many
• The relationship between two or more entity types
• Relationships within a single entity type

11. E-R图 1P68 - 1P74

12. 实体与属性的划分原则 1P77

13. E-R图的集成 1P87 - 1P94

• Merge and resolve conflicts between E-R diagrams
• Modify and refactor to eliminate unnecessary redundancy
• Conflict types: 1. Attribute conflict, 2. Naming conflict, 3. Structure conflict

14. E-R图向关系模型的转换 2P7 - 2P13

• An entity type is converted into a relationship schema. The attributes of the relationship are the attributes of the entity, and the code of the relationship is the code of the entity.

15. 优化数据模型的方法 2P19

• Determine data dependencies
• Minimize data dependencies between various relational schemas
• Analyze the relationship patterns one by one according to the theory of data dependence
• Based on the processing requirements obtained in the requirements analysis stage, analyze whether these patterns are suitable for such an application environment and determine whether certain patterns need to be merged or decomposed.
• Perform necessary decomposition of the relational model to improve data operation efficiency and storage space utilization. Two commonly used decomposition methods are horizontal decomposition and vertical decomposition.

16. 数据库物理设计的步骤 2P32

• The process of selecting a physical structure that best suits application requirements for a given logical data model is the physical design of the database.
• 1. Determine the physical structure of the database, which in relational databases mainly refers to the access method and storage structure.
  2. Evaluate physical structures with emphasis on time and space efficiency
  3. Common access methods of database management systems 2P39
• Index method and clustering method, B+ tree index and hash index P236

17. 影响数据存放位置和存储结构的因素 2P53

• Access time, storage space utilization, maintenance cost, hardware environment, application requirements

​

8. Database Programming

1. python关联MySQL的包（Package）是什么 P9

• PyMySQL

2. 游标的好处 P22

• A cursor is a data buffer opened by the system for users to store the execution results of SQL statements. Each cursor area has a name.
• Users can obtain records one by one through the cursor and assign them to the main variable, which is then handed over to the main language for further processing.
• After using the cursor function, we can save the results first, and then we can do our own programming at will to get the result set we ultimately want.
  Cursor = (数据库名).db.cursor
  

3. tornado web服务器的四大组件 P62-P63

• ioloop instance, app instance, handler class, routing table

4. GET和POST的区别 P65

• The methods of passing parameters are different: Get includes the parameters in the URL, and Post passes the parameters through the request body;
• URL visibility is different: the parameters of the get request can be seen directly, but the parameter URL of the post request is not visible;
• The size of the data passed is different: the length of the data passed in the get request is limited by the URL size, while the post request has no length limit
• The impact of going back on the page is different: going back on get will not have any impact, but going back on post will resubmit;
• Different encoding methods: get requests only support URL encoding, while post requests support multiple encoding methods;

5. SQL 变量的定义 P73 - P76

6. 流程控制 P80 - P97

• Flow control statements can be used in stored procedures and custom functions to control the flow of the program.

流程控制语句：
IF 语句；
CASE语句；
LOOP语句；
LEAVE语句；
ITERATE语句；
REPEAT语句；
WHILE语句；

7. 匿名块与命名块 P100

• Anonymous block: It must be compiled every time it is executed. It cannot be stored in the database and cannot be called in other procedural SQL blocks;
• Named block: After compilation, it is saved in the database and is called a persistent storage module. It can be called repeatedly and runs faster. Procedures and functions are named blocks.

8. 存储过程的定义 P102

• A stored procedure is a database object that stores complex programs in the database so that they can be called by external programs.

9. 存储过程的优点 P103

• High efficiency when traveling far away
• Reduced communication volume between client and server
• Facilitate implementation of corporate rules

10. 存储过程的缺点 P104

• Stored procedures are often customized to a specific database because of the different supported programming languages. When switching to a database system from another manufacturer, the original stored procedures need to be rewritten.
• Performance tuning and writing of stored procedures, limited by various database systems

11. 存储过程的SQL代码 P107 - P117

创建存储过程：CREATE PROCEDURE （过程名）（参数）（过程化SQL块）

声明语句结束符，可以自定义：DELIMITER $$或DELIMITER//

DELIMITER $$

CREATE PROCEDURE delete_sno ( IN p_sno CHAR(9))

BEGIN

DELETE FROM Student WHERE sno = p_sno ;

END$$

DELIMITER ;

执行存储过程：CALL（过程名）（参数）

CALL delete__sno(‘201215122’);

删除存储过程：DROP PROCEDURE（过程名）

12. 函数与存储过程的异同 P119

• Same: both are persistent storage modules
• Exception: The function must specify the return type

13. 函数的SQL代码 P120 - P122

1.定义语句格式：CREATE FUNCTION （函数名）（参数）RETURN（类型）（过程化SQL块）

DELIMITER //

CREATE FUNCTION func_student ( f_Sno CHAR(9))

RETURNS CHAR(20) DETERMINISTIC 

COMMENT ’ 查 询 某 个 学 生 的 姓 名 ’

BEGIN 

RETURN (SELECT Sname FROM Student WHERE Sno = f_Sno ) ;

END // 

DELIMITER ;

2.函数的执行语句格式：SELECT（函数名）（参数）

SELECT func_student(‘201215123’);

SELECT func_student(sno) FROM Stduent;

3.删除函数：DROP FUNCTION（函数名）

​

9. MySQL optimization principles

1. MySQL各组件之间协同工作的逻辑架构 P6 - P9

• 9-MySQL optimization principles.pdf

2. MySQL的查询过程 P12，P38

• 1. Client/server communication protocol
• 2. Query cache
• 3. Grammar parsing and preprocessing
• 4. Query optimization
• 5. Query execution engine
• 6. Return the results to the client

​

10. Database recovery technology

1. 事务的概念 P7

• A transaction is a user-defined database operating system sequence. These operations are either done or not done at all. It is an indivisible unit of work;
• Transaction is the basic unit of database recovery and concurrency control

2. 事务的显示控制 P8 - P9

• 10-Database recovery technology.pdf

3. 事务的ACID特性 P13 - P19

• Atomicity: A transaction is a logical unit of work in the database, either all are done or none are done;
• Consistency: The result of transaction execution must be to change the database from one consistency state to another consistency state;
• Isolation: The execution of a transaction cannot be interfered with by other transactions;
• Continuity: Continuity is also called permanence. Once a transaction is committed, its changes to the data in the database should be permanent, and subsequent other operations or failures should not have any impact on its execution results.

4. 数据库恢复的背景和原因 P21

• Failures are inevitable:
• 1. Computer hardware failure;
  2. software errors;
  3. operator error
  4. malicious destruction
• Impact of failure:
• 1. Abnormal interruption of running transactions affects the correctness of data in the database
  2. Corruption of the database, complete or partial loss of data

5. 数据库恢复的概念 P22

• Database recovery: Database management must have the function of restoring the database from an error state to a known correct state (also known as a consistent state or a complete state). This is the database recovery management system’s response to failures; the recovery subsystem is an important part of the database management system; recovery technology is an important indicator to measure the quality of the system.

6. 故障的种类 P24 - P38

• Failure within the accident
• System failure
• media failure
• computer virus

7. 恢复机制的两个关键问题 P40

• How to create redundant data (1. Data dump 2. Register log file 3. Both are often used together)
• How to use these redundant data to implement database recovery

8. 数据转储的概念 P43 - P44

• Data dump is the basic technique used in database recovery
• Dumping is a process in which the database administrator periodically copies the entire database to tape, disk, or other storage media.
• These spare data texts are called backup copies or fallback copies

9. 数据转储的方法 P47 - P52

• Static dump and dynamic dump
• Mass dump and incremental dump
• Summary of dump methods (dynamic mass dump, static mass dump, dynamic incremental dump, static incremental dump)

10. 日志文件的概念 P55

• Log files are files used to record transactions and database update operations.

11. 以记录为单位的日志文件内容 P56 - P57

• Start tag of each transaction
• End mark of each transaction
• All update operations for each transaction
• Here the start mark of each transaction, the end mark of each transaction and each update operation are treated as a log record in the log file

1. Transaction ID
2. Operation type
3. Operation object
4. The old value of the data before updating
5. The new value of the updated data
6. 以数据块为单位的日志文件内容 P58

• transaction mark
• updated data block

13. 日志文件的作用 P59 - P61

• Perform transaction failure recovery
• Perform system failure recovery
• Assists standby copies with media failure recovery

14. 登记日志文件的原则 P63
15. The order of registration is strictly based on the time order of concurrent transaction execution.
16. The log file must be written first and then the database.

• (Write log file operation: write the log record representing this modification to the log file;
• Write database operation: write the modifications to the data into the database;

15. 先写日志文件的原因 P64

• Writing to the database and writing to the log are two different operations
• A failure may occur between these two operations
• If the database modification is written first and the modification is not registered in the log file, the modification cannot be restored later.
• If the log is written first, but the database is not modified, restoring based on the log file will only perform one more unnecessary UNDO operation, which will not affect the correctness of the database.
• Therefore, for safety, you must write the log file first, that is, first write the log records to the log file, and then write the modifications to the database.

16. 事务故障的恢复方法 P67

• Modifications that have been made to the database by this transaction are undoed (UNDO) by the recovery subsystem using the log file.

17. 事务故障恢复的步骤 P68 - P69

• Scan the file log in reverse direction (that is, scan the log file from the end forward) to find the update operation of the transaction
• Performs the reverse operation of the transaction’s update operation. That is, writing the “pre-update value” in the log record to the database
• (Insertion operation: If the “value before update” is empty, it is equivalent to a delete operation;
• Delete operation: If the “updated value” is empty, it is equivalent to an insertion operation;
• If it is a modification operation, it is equivalent to replacing the modified value with the pre-modified value)

18. 系统故障的恢复方法 P71
    1. Undo (undo) the unfinished transaction when the failure occurred;
    2. Redo (redo) completed transactions
19. 系统故障的恢复步骤 P72 - P73
    1. Scan the log file forward (that is, scan the log file from the beginning) to find transactions that were committed before the failure occurred
    2. Undo (UNDO) processing of undo (UNDO) queue transactions
    3. Redo (REDO) processing of redo (REDO) queue transactions

1. Reinstall database
2. Redo completed transactions
3. 介质故障的恢复步骤 P76 - P77
4. Load the latest standby database copy (the dump copy closest to the time of the failure) to restore the database to the consistency state of the latest dump
5. Load a copy of the relevant log file (a copy of the log file at the end of the dump) and redo the completed transaction
6. 为什么要具有检查点的恢复技术 P81
7. Searching the entire log will take a lot of time
8. Many transactions that need to be redone have actually written the results of their update operations to the database, but the recovery subsystem has to re-perform these operations, which wastes a lot of time.