📚 Database Fundamentals: Concepts and Architecture | 数据库基础：概念与体系结构
Databases are the backbone of modern information systems, storing and organising data in a structured way to support efficient retrieval, update, and management. In the CIE A-Level Computer Science syllabus, understanding the core concepts and architectural layers of a database is essential for designing robust systems and reading/writing SQL.
数据库是现代信息系统的核心支柱，它以结构化方式存储和组织数据，以支持高效的检索、更新和管理。在 CIE A-Level 计算机科学考纲中，理解数据库的核心概念与体系结构层，是设计稳健系统以及编写和解读 SQL 的基础。
1. What Is a Database? | 什么是数据库？
A database is an organised collection of interrelated data, stored persistently and managed as a single logical unit. It is designed to represent some aspect of the real world, known as a mini-world or universe of discourse.
数据库是按组织方式存储的相关数据集合，它以持久化的方式保存，并作为一个整体逻辑单元进行管理。数据库通常用来表示现实世界的某个方面，这一方面被称为“小型世界”或“论域”。
For example, a school database may contain data about students, teachers, courses, and examination results, where each record is connected to the others through defined relationships.
例如，一所学校的数据库可能包含学生、教师、课程和考试成绩等数据，其中每条记录都通过明确定义的关系与其他记录相连。
Key properties of a database:
数据库的关键特性包括：
- It is a shared, integrated collection of data.
- 它是共享的、集成的数据集合。
- It has minimal redundancy compared to file-based storage.
- 与基于文件的存储相比，它具有最小冗余度。
- It enforces data consistency and integrity through constraints.
- 它通过约束来保证数据的一致性和完整性。
2. DBMS and Database System | DBMS 与数据库系统
A Database Management System (DBMS) is a software package that enables users to define, create, query, update, and administer a database. Examples include MySQL, PostgreSQL, Oracle, and Microsoft SQL Server.
数据库管理系统（DBMS）是一种软件包，它允许用户定义、创建、查询、更新和管理数据库。常见的例子包括 MySQL、PostgreSQL、Oracle 和 Microsoft SQL Server。
A database system (also called a database application system) consists of the database itself, the DBMS, the application programs, and the users. The users may be end users, application programmers, or database administrators.
数据库系统（也称为数据库应用系统）由数据库本身、DBMS、应用程序和用户组成。用户包括最终用户、应用程序员和数据库管理员。
The main functions of a DBMS include:
DBMS 的主要功能包括：
- Data storage, retrieval, and update.
- 数据的存储、检索和更新。
- A user-accessible catalog (data dictionary) describing the metadata.
- 用户可访问的目录（数据字典），用于描述元数据。
- Transaction management and concurrency control.
- 事务管理和并发控制。
- Security and access control.
- 安全性和访问控制。
3. Three-Level Architecture | 三层体系结构
The ANSI-SPARC architecture separates a database system into three levels: the external level, the conceptual level, and the internal level. This separation provides data independence and hides implementation details from users.
ANSI-SPARC 体系结构将数据库系统划分为三个层次：外部层、概念层和内部层。这种划分提供了数据独立性，并向用户隐藏了实现细节。
External level – the view presented to individual users or applications. Each external view shows only the data relevant to a particular user, often as a subset of the database. Different users may see different views of the same data.
外部层——呈现给单个用户或应用程序的视图。每个外部视图只显示与特定用户相关的数据，通常是数据库的一个子集。不同用户可能看到同一数据的不同视图。
Conceptual level – the community view of the whole database. It describes what data is stored, the relationships among data items, and the constraints applied, but it does not describe how the data is physically stored.
概念层——整个数据库的全局视图。它描述存储了哪些数据、数据项之间的关系以及应用的约束，但不描述数据在物理上是如何存储的。
Internal level – the physical storage representation, including file organisation, indexing methods, and data compression. It is hidden from end users.
内部层——物理存储表示，包括文件组织、索引方法和数据压缩。它对外部用户完全隐藏。
Data independence is the ability to change the schema at one level without changing the schema at the next higher level. Two types exist:
数据独立性是指在不改变更高级别模式的情况下修改某一级别模式的能力。分为两种类型：
- Logical data independence: changing the conceptual schema without affecting external views.
- 逻辑数据独立性：修改概念模式而不影响外部视图。
- Physical data independence: changing the internal schema without affecting the conceptual schema.
- 物理数据独立性：修改内部模式而不影响概念模式。
4. Data Models | 数据模型
A data model is a collection of concepts used to describe the structure of a database, including data types, relationships, and constraints. The main categories are:
数据模型是用于描述数据库结构的概念集合，包括数据类型、关系和约束。主要类别有：
- Hierarchical model – organises data in a tree structure, with parent-child relationships. Each child has exactly one parent. This model is fast for certain queries but inflexible for many-to-many relationships.
- 层次模型——以树形结构组织数据，具有父子关系。每个子节点只有一个父节点。该模型对某些查询很快，但难以灵活处理多对多关系。
- Network model – extends the hierarchical model by allowing each child to have multiple parents, represented using records and sets. It supports many-to-many relationships more naturally.
- 网状模型——扩展了层次模型，允许每个子节点有多个父节点，使用记录和系表示。它更自然地支持多对多关系。
- Relational model – represents data in two-dimensional tables (relations). It is the most widely used model in modern DBMSs due to its simplicity and strong mathematical foundation.
- 关系模型——以二维表（关系）表示数据。由于简单且具有坚实的数学基础，它是现代 DBMS 中使用最广泛的模型。
5. Relational Database Structure | 关系数据库基本结构
A relational database stores data in tables called relations. Each table consists of rows (tuples) and columns (attributes). The degree of a relation is the number of attributes; the cardinality is the number of tuples.
关系数据库将数据存储在称为关系的表中。每个表由行（元组）和列（属性）组成。关系的度是属性的数量，基数（势）是元组的数量。
| StudentID | Name | Grade |
|---|---|---|
| S001 | Aisha | A |
| S002 | Bilal | B |
Each attribute has a domain, which is the set of permitted values, such as integers, strings, or dates. A relation schema defines the relation name and its attributes, while a relation instance is the actual set of tuples at a particular moment.
每个属性都有一个域（domain），即允许取值的集合，例如整数、字符串或日期。关系模式定义了关系名及其属性，而关系实例是某一时刻元组的实际集合。
Relations must satisfy the following properties:
关系必须满足以下性质：
- Each cell contains a single, atomic value.
- 每个单元格只包含单个不可再分的值。
- All tuples in a relation are distinct (no duplicate rows).
- 关系中的所有元组互不相同（没有重复行）。
- Attribute names are unique within a relation.
- 属性名在关系内是唯一的。
6. Keys in Relational Databases | 关系数据库中的键
Keys are essential for maintaining consistency and establishing relationships between tables. The most important keys are:
键对于维护一致性以及建立表之间的关系至关重要。最重要的键包括：
- Primary key – an attribute or minimal set of attributes that uniquely identifies each tuple in a relation. It cannot contain NULL values.
- 主键——能够唯一标识关系中每个元组的一个属性或最小属性组合。它不能包含 NULL 值。
- Candidate key – an attribute or set of attributes that could serve as the primary key. One candidate key is selected as the primary key; the others become alternate keys.
- 候选键——能够充当主键的一个属性或属性集合。从候选键中选出一个作为主键，其余的是备用键。
- Foreign key – an attribute in one relation that references the primary key of another relation, establishing a link between the two tables.
- 外键——某一关系中的属性，它引用另一个关系的主键，从而在两个表之间建立联系。
- Composite key – a key consisting of two or more attributes, used when a single attribute cannot uniquely identify a tuple.
- 复合键——由两个或多个属性组成的键，当单个属性无法唯一标识元组时使用。
For example, in a Course table, CourseID could be the primary key. In an Enrolment table, the pair (StudentID, CourseID) may form a composite primary key, while StudentID and CourseID are individual foreign keys referencing the Student and Course tables.
例如，在 Course 表中，CourseID 可以作为主键。在 Enrolment 表中，对 (StudentID, CourseID) 可以构成复合主键，而 StudentID 和 CourseID 分别是引用 Student 表和 Course 表的外键。
7. Database Languages | 数据库语言
A DBMS provides various languages to define and manipulate data. In the relational model, SQL (Structured Query Language) is the standard language, divided into three main categories:
DBMS 提供了多种语言来定义和操作数据。在关系模型中，SQL（结构化查询语言）是标准语言，分为三大类：
- DDL (Data Definition Language) – used to define the database schema, including creating and altering tables. Commands include CREATE, ALTER, and DROP.
- DDL（数据定义语言）——用于定义数据库模式，包括创建和修改表。命令包括 CREATE、ALTER 和 DROP。
- DML (Data Manipulation Language) – used to retrieve and modify data. Commands include SELECT, INSERT, UPDATE, and DELETE.
- DML（数据操作语言）——用于检索和修改数据。命令包括 SELECT、INSERT、UPDATE 和 DELETE。
- DCL (Data Control Language) – used to manage user permissions and access rights. Commands include GRANT and REVOKE.
- DCL（数据控制语言）——用于管理用户权限和访问权限。命令包括 GRANT 和 REVOKE。
For instance, the following SQL statement creates a simple table:
例如，下面的 SQL 语句创建一个简单的表：
CREATE TABLE Student (
StudentID VARCHAR(10) PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT)
;
While the above example is illustrative, CIE A-Level questions often ask candidates to write and trace SQL queries involving SELECT, JOIN, and GROUP BY, so mastering the syntax is vital.
虽然以上示例仅用于说明，但 CIE A-Level 试题经常要求考生编写和追踪涉及 SELECT、JOIN 和 GROUP BY 的 SQL 查询，因此掌握语法至关重要。
8. Database Design and ER Modelling | 数据库设计与 ER 模型
Database design typically proceeds in three phases: conceptual design, logical design, and physical design. In the conceptual phase, an Entity-Relationship (ER) model is often created.
数据库设计通常经历三个阶段：概念设计、逻辑设计和物理设计。在概念阶段，通常会创建实体-联系（ER）模型。
In an ER diagram, the main components are:
在 ER 图中，主要成分包括：
- Entity – an object or concept that has independent existence, such as a STUDENT or a COURSE.
- 实体——具有独立存在的对象或概念，例如 STUDENT 或 COURSE。
- Attribute – a property that describes an entity, such as StudentID, Name, or Email.
- 属性——描述实体的性质，例如 StudentID、Name 或 Email。
- Relationship – an association between two or more entities, such as “enrols in”.
- 联系——两个或多个实体之间的关联，例如“选修”。
- Cardinality – the type of relationship: one-to-one (1:1), one-to-many (1:N), or many-to-many (M:N).
- 基数（映射基数）——联系的类型：一对一 (1:1)、一对多 (1:N) 或多对多 (M:N)。
For example, a Student “enrols in” many Courses, and a Course can be taken by many Students. This is a many-to-many relationship, which is usually resolved into two one-to-many relationships using an intermediary table called a junction or link table.
例如，一个学生可以选修多门课程，一门课程也可以被多名学生选修。这是多对多联系，通常通过一个称为连接表或中间表的关系来解决，将其拆分为两个一对多联系。
9. Normalisation | 规范化
Normalisation is the process of organising data to reduce redundancy and improve integrity. The most common normal forms are 1NF, 2NF, and 3NF.
规范化是组织数据以减少冗余并提高完整性的过程。最常见的范式包括第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。
First Normal Form (1NF): a relation is in 1NF if all attributes contain only atomic (indivisible) values and there are no repeating groups.
第一范式（1NF）：如果关系中的所有属性都只包含原子（不可再分）值，且没有重复组，则该关系处于 1NF。
Second Normal Form (2NF): a relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the entire primary key, not just part of it. This applies mainly to composite primary keys.
第二范式（2NF）：如果关系满足 1NF，并且每个非键属性都完全依赖于整个主键（而不是仅依赖于主键的一部分），则该关系处于 2NF。这主要适用于复合主键的情况。
Third Normal Form (3NF): a relation is in 3NF if it is in 2NF and there are no transitive dependencies, meaning non-key attributes do not depend on other non-key attributes.
第三范式（3NF）：如果关系满足 2NF，并且没有传递依赖，即非键属性不依赖于其他非键属性，则该关系处于 3NF。
For instance, in a table with (StudentID, CourseID, CourseName), CourseName depends only on CourseID, not on the full key (StudentID, CourseID). This violates 2NF, so the table must be split into two tables.
例如，在包含 (StudentID, CourseID, CourseName) 的表中，CourseName 仅依赖于 CourseID，而不是完全依赖于复合键 (StudentID, CourseID)。这违反了 2NF，因此必须将该表拆分为两个表。
10. Data Integrity and Security | 数据完整性与安全性
Data integrity ensures that data is accurate, consistent, and reliable over time. The main types of integrity are:
数据完整性确保数据在长期内保持准确、一致和可靠。主要的完整性类型包括：
- Entity integrity – every table must have a primary key, and no primary key attribute may be NULL.
- 实体完整性——每个表都必须有主键，且主键属性不能为 NULL。
- Referential integrity – a foreign key must either match an existing primary key value or be NULL, ensuring that relationships between tables remain valid.
- 引用完整性——外键要么匹配已存在的主键值，要么为 NULL，从而确保表之间的关系始终有效。
- Domain integrity – every attribute value must lie within its defined domain, such as a specified range or data type.
- 域完整性——每个属性值都必须位于其定义的域内，例如指定的范围或数据类型。
- User-defined integrity – additional business rules, such as “age must be greater than 0”.
- 用户定义完整性——额外的业务规则，例如“年龄必须大于 0”。
Database security includes authentication (verifying user identity), authorisation (assigning privileges) and encryption (protecting data at rest and in transit). Backup and recovery procedures also protect data against loss or corruption.
数据库安全性包括身份验证（验证用户身份）、授权（分配权限）和加密（保护静态数据和传输中的数据）。备份和恢复过程还能防止数据丢失或损坏。
11. Advantages and Disadvantages of Databases | 数据库的优点与缺点
Compared with traditional file-based systems, database systems offer several advantages:
与传统的基于文件的系统相比，数据库系统具有以下优点：
- Reduced data redundancy and inconsistency.
- 减少数据冗余和不一致。
- Data sharing among multiple users and applications.
- 允许多个用户和应用共享数据。
- Centralised control through a DBMS.
- 通过 DBMS 实现集中控制。
- Enforcement of integrity constraints and security.
- 可以强制执行完整性约束和安全性。
- Support for concurrent access with transaction control.
- 通过事务控制支持并发访问。
- Data independence between programs and data.
- 实现程序与数据之间的数据独立性。
However, databases also have some disadvantages:
然而，数据库也有一些缺点：
- Higher initial cost for hardware, software, and training.
- 硬件、软件和培训的初始成本较高。
- Greater complexity and the need for skilled database administrators.
- 更复杂，需要熟练的数据库管理员。
- Potential performance bottlenecks under heavy concurrent load.
- 在大量并发负载下可能出现性能瓶颈。
- Single point of failure if backup and recovery are not properly managed.
- 如果备份和恢复管理不善，可能成为单点故障。
12. Summary and Exam Tips | 总结与考试提示
In this revision guide, we have covered the essential concepts of database systems: the definition of a database, the role of a DBMS, the three-level architecture, relational data structures, keys, SQL categories, ER modelling, normalisation, and integrity/security considerations.
在本复习指南中，我们涵盖了数据库系统的核心概念：数据库的定义、DBMS 的角色、三层体系结构、关系数据结构、键、SQL 分类、ER 建模、规范化以及完整性和安全性考虑。
For the CIE examination, remember the following tips:
针对 CIE 考试，请记住以下提示：
- Be able to define key terms with precise wording, such as primary key, foreign key, and redundancy.
- 能够使用准确措辞定义关键术语，例如主键、外键和冗余。
- Understand the difference between logical and physical data independence.
- 理解逻辑数据独立性与物理数据独立性之间的区别。
- Practise drawing simple ER diagrams and converting them into relational tables.
- 练习绘制简单 ER 图，并将其转换为关系表。
- Apply normalisation to given datasets and explain why a table is not in 2NF or 3NF.
- 对给定数据集应用规范化，并解释某表为何不满足 2NF 或 3NF。
- Know the SQL keywords for DDL, DML, and DCL and be able to write basic queries.
- 掌握 DDL、DML 和 DCL 的 SQL 关键字，并能编写基本查询。
Mastering these database fundamentals will give you a strong foundation for both multiple-choice questions and structured exam questions on the CIE Computer Science paper.
掌握这些数据库基础将为您在 CIE 计算机科学试卷中的选择题和结构化问答题打下坚实基础。
Published by TutorHao | Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
或截图后用「扫一扫 → 相册」选择本图