Database Fundamentals and Core Concepts Analysis | 数据库原理与核心概念分析

📚 Database Fundamentals and Core Concepts Analysis | 数据库原理与核心概念分析

In the digital era, databases form the backbone of nearly every software application, from banking systems to social media platforms. Understanding database principles is essential for computer science students, particularly those preparing for CIE examinations. This comprehensive guide explores the fundamental concepts, models, and practical applications of database systems.

在数字时代，数据库几乎构成了所有软件应用的支柱，从银行系统到社交媒体平台。理解数据库原理对于计算机科学学生至关重要，尤其是那些准备CIE考试的学生。本综合指南探讨了数据库系统的基本概念、模型和实际应用。

1. What Is a Database? | 什么是数据库？

A database is an organised collection of structured data that is stored and accessed electronically. The term ‘organised’ is crucial because it distinguishes a true database from a random collection of files. Data within a database is systematically arranged to facilitate efficient retrieval, modification, and management.

数据库是有组织地存储和电子访问的结构化数据集合。术语”有组织”至关重要，因为它将真正的数据库与随机文件集合区分开来。数据库中的数据被系统地排列，以促进高效的检索、修改和管理。

Structured data: Data organised in a predefined format, such as tables with rows and columns.
Structured data | 结构化数据: 以预定义格式组织的数据，例如具有行和列的表。
Metadata: Data about data, describing the structure, constraints, and relationships within the database.
Metadata | 元数据: 关于数据的数据，描述数据库内的结构、约束和关系。
Database instance: The actual data stored in the database at a particular moment in time.
Database instance | 数据库实例: 在特定时刻存储在数据库中的实际数据。
Database schema: The overall design and structure of the database, defining tables, fields, and relationships.
Database schema | 数据库模式: 数据库的整体设计和结构，定义表、字段和关系。

2. Database Models | 数据库模型

Database models define the logical structure and organisation of data. The three traditional models are hierarchical, network, and relational. Each model offers distinct advantages and limitations in how data relationships are represented.

数据库模型定义了数据的逻辑结构和组织方式。三种传统模型是层次模型、网状模型和关系模型。每种模型在数据关系的表示方式上都有不同的优势和局限性。

The hierarchical model arranges data in a tree-like structure where each parent node can have multiple children, but each child has only one parent. This creates a one-to-many relationship structure that is intuitive but rigid for complex relationship representation.

层次模型以树状结构排列数据，每个父节点可以有多个子节点，但每个子节点只有一个父节点。这创建了一对多关系结构，直观但在表示复杂关系时不够灵活。

The network model extends the hierarchical approach by allowing each child to have multiple parents. This many-to-many relationship capability provides greater flexibility, though it increases structural complexity significantly.

网状模型通过允许每个子节点拥有多个父节点来扩展层次方法。这种多对多关系能力提供了更大的灵活性，但也显著增加了结构复杂性。

The relational model, proposed by Edgar F. Codd in 1970, organises data into two-dimensional tables called relations. This model is mathematically grounded in set theory and relational algebra, making it the most widely adopted approach in modern database systems.

关系模型由埃德加·F·科德于1970年提出，将数据组织成称为关系的二维表。该模型以集合论和关系代数为基础，使其成为现代数据库系统中最广泛采用的方法。

3. The Relational Model | 关系模型

The relational model represents data as a collection of tables, where each table consists of rows and columns. A row, also called a tuple or record, represents a single entity instance. A column, also called an attribute or field, represents a specific property of that entity.

关系模型将数据表示为表的集合，每个表由行和列组成。行（也称为元组或记录）表示单个实体实例。列（也称为属性或字段）表示该实体的特定属性。

StudentID \| 学号	Name \| 姓名	Subject \| 科目	Grade \| 成绩
S001	Alice	Computer Science	A
S002	Bob	Mathematics	B
S003	Carol	Physics	A

Key properties of relational tables include:

关系表的关键属性包括：

Atomicity of cells: Each cell contains exactly one value, never a set of values.
Cell atomicity | 单元格原子性: 每个单元格只包含一个值，绝不包含一组值。
Unique rows: Each row in a table is distinct and identifiable.
Unique rows | 行唯一性: 表中的每一行都是唯一的且可识别的。
Column homogeneity: All values in a column share the same data type.
Column homogeneity | 列同质性: 列中的所有值共享相同的数据类型。
No ordering: Rows and columns have no inherent order; positioning is insignificant.
No ordering | 无序性: 行和列没有固有顺序；位置无关紧要。

4. Keys in Relational Databases | 关系数据库中的键

Keys are special attributes that serve as identifiers and establish relationships between tables. Understanding different types of keys is fundamental to designing sound database structures.

键是作为标识符并建立表之间关系的特殊属性。理解不同类型的键是设计健全数据库结构的基础。

A primary key is an attribute or set of attributes that uniquely identifies each row in a table. It must satisfy two critical conditions: uniqueness (no two rows share the same primary key value) and minimality (removing any attribute from a composite key would destroy uniqueness).

主键是唯一标识表中每一行的属性或属性集合。它必须满足两个关键条件：唯一性（没有两行共享相同的主键值）和最小性（从复合键中移除任何属性都会破坏唯一性）。

A candidate key is any attribute or combination of attributes that could serve as a primary key. Among multiple candidate keys, the database designer selects one as the primary key; the remaining candidate keys become alternate keys.

候选键是任何可以作为主键的属性或属性组合。在多个候选键中，数据库设计者选择一个作为主键；其余的候选键成为备用键。

A foreign key is an attribute in one table that references the primary key of another table. It maintains referential integrity by ensuring that values in the foreign key column correspond to valid values in the referenced table’s primary key column.

外键是一个表中引用另一个表主键的属性。它通过确保外键列中的值与所引用表主键列中的有效值相对应来维护引用完整性。

A composite key consists of two or more attributes that together uniquely identify a row. Composite keys are necessary when no single attribute can uniquely identify records.

复合键由两个或多个属性组成，共同唯一标识一行。当没有任何单一属性能够唯一标识记录时，复合键是必需的。

5. Normalization | 数据库规范化

Normalization is a systematic process of organising data to reduce redundancy and prevent update anomalies. The process involves decomposing tables into smaller, well-structured tables that satisfy progressive normal forms.

规范化是一个系统性的数据组织过程，旨在减少冗余并防止更新异常。该过程涉及将表分解为满足渐进范式的小型、结构良好的表。

First Normal Form (1NF) requires that all attributes contain only atomic (indivisible) values. No attribute can hold multiple values or a set of values. This eliminates repeating groups and ensures each cell contains a single value.

第一范式（1NF）要求所有属性只包含原子（不可分割的）值。任何属性都不能包含多个值或一组值。这消除了重复组，确保每个单元格包含单个值。

1NF Violation: Student(StudentID, Name, Subjects[Computer Science, Mathematics])

1NF 违规：Student(StudentID, Name, Subjects[计算机科学, 数学])

The ‘Subjects’ attribute contains multiple values. To satisfy 1NF, we decompose it: Student(StudentID, Name) and StudentSubjects(StudentID, Subject).

‘Subjects’属性包含多个值。为了满足1NF，我们将其分解：Student(StudentID, Name) 和 StudentSubjects(StudentID, Subject)。

Second Normal Form (2NF) requires that the table is in 1NF and every non-key attribute is fully functionally dependent on the entire primary key. Partial dependencies, where non-key attributes depend on only part of a composite key, are eliminated.

第二范式（2NF）要求表处于1NF，并且每个非键属性完全函数依赖于整个主键。部分依赖（即非键属性仅依赖于复合键的一部分）被消除。

Third Normal Form (3NF) requires that the table is in 2NF and no transitive dependencies exist. A transitive dependency occurs when a non-key attribute depends on another non-key attribute, rather than directly on the primary key.

第三范式（3NF）要求表处于2NF，并且不存在传递依赖。传递依赖发生在非键属性依赖于另一个非键属性而非直接依赖于主键时。

6. Entity-Relationship Modelling | 实体关系建模

Entity-Relationship (ER) modelling is a visual approach to database design that captures the structure of data before implementation. An ER diagram represents entities as rectangles, attributes as ovals, and relationships as diamonds.

实体关系（ER）建模是一种数据库设计的可视化方法，在实现之前捕获数据的结构。ER图将实体表示为矩形，属性表示为椭圆，关系表示为菱形。

An entity is a real-world object or concept about which data is stored, such as a student, course, or lecturer. An attribute describes properties of an entity, such as a student’s name or age. A relationship defines how two entities are associated, such as ‘enrols in’ connecting Student and Course.

实体是一个现实世界中的对象或概念，关于它存储数据，例如学生、课程或讲师。属性描述实体的性质，例如学生的姓名或年龄。关系定义了两个实体如何关联，例如连接学生和课程的”选修”。

Relationships have cardinality, describing how many instances of one entity can relate to instances of another. The three main cardinality types are:

关系具有基数性，描述一个实体的多少个实例可以与另一个实体的实例相关联。三种主要基数类型是：

One-to-one (1:1): One instance of Entity A relates to exactly one instance of Entity B.
One-to-one (1:1) | 一对一: 实体A的一个实例恰好关联实体B的一个实例。
One-to-many (1:N): One instance of Entity A relates to multiple instances of Entity B.
One-to-many (1:N) | 一对多: 实体A的一个实例关联实体B的多个实例。
Many-to-many (M:N): Multiple instances of Entity A relate to multiple instances of Entity B.
Many-to-many (M:N) | 多对多: 实体A的多个实例关联实体B的多个实例。

7. Data Integrity and Constraints | 数据完整性与约束

Data integrity ensures that data stored in a database remains accurate, consistent, and reliable. Database constraints are rules enforced by the DBMS to preserve integrity across all operations.

数据完整性确保存储在数据库中的数据保持准确、一致和可靠。数据库约束是由DBMS执行的规则，用于在所有操作中保持完整性。

Entity integrity ensures that primary keys are unique and cannot be NULL. This guarantees that every row can be uniquely identified and accessed.

实体完整性确保主键是唯一的且不能为NULL。这保证了每一行都能被唯一识别和访问。

Referential integrity ensures that foreign key values in a table always reference existing primary key values in the referenced table. The DBMS rejects any operation that would create orphaned rows or broken references.

引用完整性确保表中的外键值始终引用所引用表中存在的主键值。DBMS拒绝任何会创建孤立行或破坏引用的操作。

Domain integrity ensures that all values entered into a column fall within the defined domain, including data type, format, and range constraints. For example, an ‘Age’ column might enforce values between 0 and 150.

域完整性确保输入到列中的所有值都在定义的域内，包括数据类型、格式和范围约束。例如，’Age’列可能强制值在0到150之间。

User-defined integrity allows custom rules specific to the application’s requirements, such as ensuring that a student’s graduation date is later than their enrolment date.

用户定义完整性允许针对应用需求的定制规则，例如确保学生的毕业日期晚于其入学日期。

8. Transactions and ACID Properties | 事务与ACID属性

A transaction is a sequence of database operations executed as a single logical unit of work. Transactions ensure data consistency even when multiple users access the database concurrently or when system failures occur.

事务是作为单个逻辑工作单元执行的一系列数据库操作。即使多个用户并发访问数据库或发生系统故障时，事务也能确保数据一致性。

The ACID properties are the four essential characteristics that guarantee transaction reliability:

ACID属性是保证事务可靠性的四个基本特征：

Atomicity | 原子性: Either all operations in a transaction complete successfully, or none of them take effect. There is no partial execution.
Atomicity | 原子性: 事务中的操作要么全部成功完成，要么全部不生效。不存在部分执行的情况。
Consistency | 一致性: A transaction transforms the database from one valid state to another, preserving all integrity constraints.
Consistency | 一致性: 事务将数据库从一个有效状态转变为另一个有效状态，保持所有完整性约束。
Isolation | 隔离性: Concurrent transactions appear to execute in isolation, as if they were the only transaction running.
Isolation | 隔离性: 并发事务似乎独立执行，就像它们在运行中的唯一事务一样。
Durability | 持久性: Once a transaction commits, its changes persist permanently, even in the event of a system crash.
Durability | 持久性: 一旦事务提交，其更改将永久持久化，即使在系统崩溃的情况下也是如此。

The transaction lifecycle involves three phases: BEGIN initiates the transaction, COMMIT permanently saves changes if all operations succeed, and ROLLBACK undoes all changes if any operation fails.

事务生命周期涉及三个阶段：BEGIN（开始）启动事务，COMMIT（提交）在所有操作成功时永久保存更改，ROLLBACK（回滚）在任何操作失败时撤销所有更改。

9. Structured Query Language (SQL) | 结构化查询语言（SQL）

SQL is the standard language for interacting with relational databases, encompassing sub-languages for different purposes. Mastery of SQL is essential for any database professional.

SQL是与关系数据库交互的标准语言，涵盖不同用途的子语言。掌握SQL对任何数据库专业人员都是必需的。

Data Definition Language (DDL) creates and modifies database structures. The three primary DDL commands are CREATE (create tables), ALTER (modify table structure), and DROP (remove tables).

数据定义语言（DDL）创建和修改数据库结构。三个主要的DDL命令是CREATE（创建表）、ALTER（修改表结构）和DROP（删除表）。

Data Manipulation Language (DML) handles data operations. The core commands include SELECT (retrieve data), INSERT (add new rows), UPDATE (modify existing data), and DELETE (remove rows).

数据操作语言（DML）处理数据操作。核心命令包括SELECT（检索数据）、INSERT（添加新行）、UPDATE（修改现有数据）和DELETE（删除行）。

Data Control Language (DCL) manages user permissions and access rights. Commands such as GRANT and REVOKE control who can perform specific operations on database objects.

数据控制语言（DCL）管理用户权限和访问权。诸如GRANT和REVOKE之类的命令控制谁可以对数据库对象执行特定操作。

A typical SQL query to retrieve data with a condition might look like:

一个带条件的典型SQL数据检索查询如下：

SELECT Name FROM Student WHERE Grade = ‘A’ ORDER BY Name ASC;

10. Database Management System Architecture | 数据库管理系统架构

A Database Management System (DBMS) is software that provides a structured interface between users and the database, managing storage, security, and concurrent access. The layered architecture ensures data independence between levels.

数据库管理系统（DBMS）是提供用户与数据库之间结构化接口的软件，管理存储、安全和并发访问。分层架构确保各级之间的数据独立性。

The external level (view level) presents individual user views, allowing different users to see customized perspectives of the same data. Multiple user views can coexist, each showing only relevant information.

外模式层（视图层）呈现单个用户的视图，允许不同用户看到相同数据的定制视角。多个用户视图可以共存，每个只显示相关信息。

The conceptual level (logical level) defines the overall logical structure, describing all entities, attributes, and relationships as seen by the entire organisation. This level is independent of storage considerations.

概念层（逻辑层）定义整体逻辑结构，描述整个组织所看到的所有实体、属性和关系。该层独立于存储考虑。

The internal level (physical level) handles actual data storage, including file organisation, indexing methods, and data compression techniques. Changes at this level do not affect higher levels due to data abstraction.

内模式层（物理层）处理实际数据存储，包括文件组织、索引方法和数据压缩技术。由于数据抽象，该层的更改不会影响更高层。

External Level → Conceptual Level → Internal Level

外模式层 → 概念层 → 内模式层

11. Security and Backup Strategies | 安全与备份策略

Database security protects data from unauthorised access, corruption, or loss. A comprehensive security framework combines multiple layers of protection to address various threat vectors.

数据库安全保护数据免受未经授权的访问、损坏或丢失。全面的安全框架结合多层保护以应对各种威胁向量。

Access control utilises authentication mechanisms to verify user identity and authorisation to determine permitted operations. The principle of least privilege ensures that users only have access to what is necessary for their role.

访问控制利用认证机制验证用户身份，利用授权确定允许的操作。最小权限原则确保用户只拥有其角色所需的数据访问权限。

Encryption converts plaintext data into ciphertext using cryptographic algorithms. Data may be encrypted in transit (e.g., via TLS/SSL) and at rest (e.g., full-disk encryption).

加密使用密码算法将明文数据转换为密文。数据可以在传输过程中（如通过TLS/SSL）和静态存储时（如全盘加密）进行加密。

Backup strategies ensure data recoverability after failures. Common approaches include:

备份策略确保故障后的数据可恢复性。常见方法包括：

Full backup: A complete copy of the entire database, creating a baseline for recovery.
Full backup | 全量备份: 整个数据库的完整副本，为恢复创建基准。
Incremental backup: Captures only changes since the last backup of any type, minimising storage requirements.
Incremental backup | 增量备份: 仅捕获自上次任何类型备份以来的更改，最小化存储需求。
Differential backup: Captures changes since the last full backup, balancing storage requirements and recovery speed.
Differential backup | 差异备份: 捕获自上次全量备份以来的更改，在存储需求和恢复速度之间取得平衡。

12. Modern Database Trends | 现代数据库趋势

Beyond traditional relational databases, modern data management introduces new paradigms that address emerging application demands. Understanding these trends contextualises core database principles within contemporary practice.

在传统关系数据库之外，现代数据管理引入了解决新兴应用需求的新范式。理解这些趋势有助于将核心数据库原理置于当代实践中。

NoSQL databases provide flexible schemas for unstructured or semi-structured data. Key models include document stores (e.g., MongoDB), key-value stores (e.g., Redis), column-family stores, and graph databases (e.g., Neo4j).

NoSQL数据库为结构化或半结构化数据提供灵活的模式。关键模型包括文档存储（如MongoDB）、键值存储（如Redis）、列族存储和图数据库（如Neo4j）。

Cloud databases offer scalable, managed database services deployed on cloud infrastructure. They provide automatic replication, customisable performance tiers, and cost-efficient pricing models.

云数据库提供部署在云基础设施上的可扩展托管数据库服务。它们提供自动复制、可定制的性能层级和成本效益高的定价模型。

Data warehousing involves collecting and integrating data from multiple sources for analytical processing. In contrast to operational databases optimised for transactions, warehouses are designed for complex queries and business intelligence.

数据仓库涉及从多个来源收集和整合数据以进行分析处理。与为事务优化的事务型数据库相比，仓库是为复杂查询和商业智能而设计的。

As data continues to grow exponentially, the principles of efficient organisation, integrity maintenance, and secure access remain more relevant than ever. A solid understanding of database fundamentals provides the foundation for tackling advanced topics such as distributed databases, query optimisation, and data mining.

随着数据持续指数级增长，高效组织、完整性维护和安全访问的原则比以往任何时候都更加相关。扎实理解数据库基础为攻克分布式数据库、查询优化和数据挖掘等高级主题奠定了基础。

Published by TutorHao | Computer Science Revision Series | aleveler.com

更多咨询请联系16621398022（同微信）

Database Fundamentals and Core Concepts Analysis | 数据库原理与核心概念分析