📚 Database Exam Essentials for Edexcel A-Level Computer Science | A-Level Edexcel 计算机：数据库 考点精讲
This article provides a comprehensive revision guide for the database topic in the Edexcel A-Level Computer Science specification. We cover relational databases, normalisation, SQL, ACID transactions, and security to help you prepare for exam-style questions and practical scenario analysis.
本文为 Edexcel A-Level 计算机科学考试中的数据库部分提供全面复习指南。文章涵盖关系数据库、规范化、SQL、事务与 ACID、安全等核心考点，帮助你熟悉考试常见问题与场景分析。
1. What is a Database? | 数据库基础概念
A database is an organised collection of structured data stored electronically in a computer system. A Database Management System (DBMS) is software that interacts with users, applications, and the database itself to capture and analyse data, providing controlled access, efficient retrieval, and data integrity.
数据库是储存在计算机系统中的有组织、结构化的数据集合。数据库管理系统 (DBMS) 是一种软件，它在用户、应用程序和数据库之间交互，完成数据的捕获和分析，同时提供受控访问、高效检索以及数据完整性保障。
Flat-file databases store all data in a single table, which leads to data redundancy, inconsistency, and difficulty in maintaining relationships. Relational databases overcome these limitations by splitting data into multiple linked tables.
平面文件数据库将所有数据存在单张表中，这会导致数据冗余、不一致以及关系维护困难。关系数据库通过将数据拆分到多张相互关联的表中来克服这些缺点。
2. Relational Databases and Tables | 关系型数据库与表
In a relational database, data is logically organised into relations (tables). A table consists of rows (called records or tuples) and columns (called fields or attributes). The number of attributes defines the degree of the table, and the number of tuples is its cardinality.
在关系型数据库中，数据被逻辑地组织成关系（表）。表由行（记录 / 元组）和列（字段 / 属性）组成。属性的数量定义表的度，元组的数量定义表的基数。
Each column has a specific data domain — a set of allowed values (e.g., INTEGER, VARCHAR, DATE). Domains enforce data consistency by restricting what can be entered.
每一列都有特定的数据域 — 即允许取值的集合（如整型、可变字符串、日期）。数据域通过限制输入内容来加强数据一致性。
A fundamental rule of the relational model is that each row must be uniquely identifiable; this is achieved through a primary key. The order of rows and columns is logically insignificant.
关系模型的基本规则是每一行必须能被唯一标识，这通过主键实现。行和列的顺序在逻辑上没有意义。
3. Keys and Referential Integrity | 键与参照完整性
A primary key is a column (or minimal combination of columns) that uniquely identifies each tuple. No two rows can have the same primary key value, and it cannot be NULL. A composite key uses two or more columns together to form a unique identifier.
主键是一列（或最小的列组合），能唯一标识每条元组。任意两行的主键值不能相同，且主键不允许为 NULL。复合键则联合两列或多列共同构成唯一标识。
A foreign key is a column in one table that references the primary key of another table, creating a logical link. This enforces referential integrity: a foreign key value must match an existing primary key value in the referenced table, or be NULL if allowed.
外键是表中的一列，它引用另一表的主键，从而建立逻辑联系。外键必须满足参照完整性约束：外键值必须与被引用表中的主键值匹配，或在允许的情况下为 NULL。
Candidate keys are columns that could serve as primary keys, while an alternate key is any candidate key not chosen as the primary. A superkey is any set of columns that uniquely identifies a tuple, possibly containing extra columns.
候选键是可以充当主键的列；备用键则是未被选作主键的候选键。超键是任意能唯一标识元组的列集合，可能包含多余的列。
4. Entity-Relationship Diagrams (ERDs) | 实体关系图
ERDs visually model the structure of a database. Entities (strong or weak) are represented by rectangles, attributes by ovals, and relationships between entities by diamonds. A weak entity depends on another entity for its existence and uses a partial key in combination with the owner’s primary key.
实体关系图直观地建模数据库的结构。实体（强实体或弱实体）用矩形表示，属性用椭圆表示，实体间的关系用菱形表示。弱实体依赖于另一实体才能存在，它使用部分键与所有者的主键共同构成唯一标识。
Relationships have cardinalities: one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N). In implementation, a many-to-many relationship must be decomposed into two one-to-many relationships via an associative (junction) table containing foreign keys to both original tables.
关系具有基数：一对一 (1:1)、一对多 (1:M) 和多对多 (M:N)。在实际实现中，多对多关系必须通过一个关联表（连接表）分解为两个一对多关系，该表包含指向原表的外键。
Primary keys can be shown by underlining attribute names in the ERD. Foreign keys are not typically shown in the conceptual ERD but are added during logical design. Composite and multi-valued attributes may also be depicted with specific notations.
在 ERD 中，主键可通过在属性名下加下划线表示。外键通常不在概念 ERD 中直接表示，而是在逻辑设计阶段添加。复合属性和多值属性也可使用特定记号标出。
5. Normalisation: 1NF, 2NF, 3NF | 规范化：第一、二、三范式
Normalisation is a systematic process of organising table structures to minimise data redundancy and eliminate insertion, update, and deletion anomalies. For A-Level, you are expected to understand and apply up to third normal form (3NF).
规范化是一种系统化组织表结构的方法，目的是最小化数据冗余，消除插入、更新和删除异常。对于 A-Level 考试，要求理解并能应用至第三范式 (3NF)。
1NF (First Normal Form): A table is in 1NF if every attribute contains atomic (indivisible) values and there are no repeating groups. A repeating group means a record can contain multiple values for the same attribute (e.g., multiple phone numbers stored as a comma-separated string). To convert to 1NF, split such data into separate rows.
第一范式 (1NF)： 如果表中每个属性都包含原子值（不可再分），且没有重复组，则该表满足 1NF。重复组意味着一行中某个属性可以有多个取值（如用逗号分隔的多个电话号码）。要转换为 1NF，需将这类数据拆分成独立的行。
2NF (Second Normal Form): A table is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the whole primary key. This means there can be no partial dependency — where a non-key attribute depends on only part of a composite key. Tables with a single-column primary key are automatically in 2NF if they are in 1NF.
第二范式 (2NF)： 在满足 1NF 的基础上，若表中所有非主属性都完全函数依赖于整个主键，则满足 2NF。这意味着不能存在部分依赖 — 即非主属性仅依赖于复合主键的一部分。若主键为单列，在满足 1NF 的情况下自动满足 2NF。
3NF (Third Normal Form): A table is in 3NF if it is in 2NF and there are no transitive dependencies. A transitive dependency occurs when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key. In 3NF, all non-key attributes must depend directly on the primary key.
第三范式 (3NF)： 在满足 2NF 的基础上，若表中没有传递依赖，则满足 3NF。传递依赖是指某个非主属性通过另一个非主属性间接依赖于主键。在 3NF 中，每个非主属性都必须直接依赖于主键。
Example: Student(StudentID, CourseID, CourseTitle, TutorName) — functional dependencies: {StudentID, CourseID} → whole key nsitive dependency TutorName → CourseTitle (which may further require a separate Tutor table for 3NF).
示例： 表 Student(StudentID, CourseID, CourseTitle, TutorName) 中函数依赖为：{StudentID, CourseID} → 主键，但 CourseID → CourseTitle 且 CourseID → TutorName。该表不满足 2NF，因为 CourseTitle 和 TutorName 只依赖于主键的一部分 (CourseID)。将其拆分成 StudentCourse(StudentID, CourseID) 和 Course(CourseID, CourseTitle, TutorName) 即可满足 2NF，并消除了传递依赖（TutorName 可能需单独建表以满足 3NF）。
6. SQL Data Definition Language (DDL) | SQL 数据定义语言 (DDL)
SQL is the standard language for relational databases. The DDL subset deals with defining and modifying the database schema. Key statements: CREATE TABLE, ALTER TABLE, and DROP TABLE. Each column is defined with a data type (e.g. INTEGER, VARCHAR(n), DATE, BOOLEAN) and optional constraints.
SQL 是关系数据库的标准语言。DDL 子集用于定义和修改数据库模式。关键语句包括 CREATE TABLE、ALTER TABLE 和 DROP TABLE。每列需定义数据类型（如 INTEGER, VARCHAR(n), DATE, BOOLEAN）和可选的约束。
Constraints enforce rules at the database level:
- PRIMARY KEY — uniquely identifies each row.
- FOREIGN KEY — links to a primary key in another table.
- NOT NULL — the column cannot have empty values.
- UNIQUE — ensures all values in a column are distinct.
- CHECK — restricts the range of permitted values.
- DEFAULT — supplies a fallback value if none is given.
约束在数据库层面强制规则：
- PRIMARY KEY — 唯一标识每一行。
- FOREIGN KEY — 引用另一表的主键。
- NOT NULL — 列不允许空值。
- UNIQUE — 确保列中的所有值互不相同。
- CHECK — 限定允许值的范围。
- DEFAULT — 未提供值时使用默认值。
Example CREATE TABLE statement:
CREATE TABLE Student (
StudentID INTEGER PRIMARY KEY,
FullName VARCHAR(50) NOT NULL,
DateOfBirth DATE,
TutorID INTEGER,
FOREIGN KEY (TutorID) REFERENCES Tutor(TutorID)
);
建表示例：
CREATE TABLE Student (
StudentID INTEGER PRIMARY KEY,
FullName VARCHAR(50) NOT NULL,
DateOfBirth DATE,
TutorID INTEGER,
FOREIGN KEY (TutorID) REFERENCES Tutor(TutorID)
);
7. SQL Data Manipulation Language (DML) | SQL 数据操作语言 (DML)
DML commands handle data within existing tables. The four fundamental operations are SELECT, INSERT, UPDATE, and DELETE.
DML 命令用于操作表中现有的数据。四个基本操作为 SELECT、INSERT、UPDATE 和 DELETE。
SELECT retrieves data with optional filtering, sorting, and grouping:
SELECT column1, column2 FROM Table WHERE condition ORDER BY column1 ASC;
Use JOIN to combine rows from multiple tables based on a related column. Common types: INNER JOIN (matching rows only), LEFT JOIN (all rows from left table, plus matches from right), RIGHT JOIN. Aggregation functions (COUNT, SUM, AVG, MAX, MIN) are used with GROUP BY to summarise data; HAVING filters the grouped results.
SELECT 查询数据，可附带筛选、排序和分组：
SELECT column1, column2 FROM Table WHERE condition ORDER BY column1 ASC;
使用 JOIN 根据关联列组合多张表的行。常见类型：INNER JOIN（仅匹配行）、LEFT JOIN（左表所有行加上右表匹配行）、RIGHT JOIN。聚合函数（COUNT, SUM, AVG, MAX, MIN）结合 GROUP BY 汇总数据；HAVING 子句用于过滤分组后的结果。
INSERT INTO Student (StudentID, FullName) VALUES (101, 'Alice Brown');
UPDATE Student SET FullName = 'Alice Smith' WHERE StudentID = 101;
DELETE FROM Student WHERE StudentID = 101;
INSERT INTO Student (StudentID, FullName) VALUES (101, 'Alice Brown');
UPDATE Student SET FullName = 'Alice Smith' WHERE StudentID = 101;
DELETE FROM Student WHERE StudentID = 101;
Remember that DELETE removes whole rows; dropping a column requires ALTER TABLE. Always use a WHERE clause with care because omitting it would affect all rows.
注意 DELETE 删除整行；删除列需使用 ALTER TABLE。始终谨慎使用 WHERE 子句，若遗漏将影响所有行。
8. Indexing and Performance | 索引与性能
An index is a separate data structure (often a B-tree) that allows the DBMS to find rows more quickly than scanning the entire table. An index on a column stores a sorted list of values together with pointers to the actual rows, much like an index at the back of a book.
索引是一种独立的数据结构（通常是 B-树），它使 DBMS 能比全表扫描更快地定位行。列上的索引存储有序值列表以及指向实际行的指针，类似于书后的索引。
A primary index is created automatically on the primary key to enforce uniqueness and speed up retrieval. Secondary indexes can be added to columns frequently used in WHERE, JOIN, or ORDER BY clauses. However, indexes consume extra storage space and slow down write operations (INSERT, UPDATE, DELETE) because the index structure must be updated too.
主键上自动创建主索引，以强制执行唯一性并加速检索。对于经常出现在 WHERE, JOIN 或 ORDER BY 中的列，可以添加二级索引。但索引会占用额外存储空间，并降低写操作（INSERT, UPDATE, DELETE）的速度，因为索引结构也必须更新。
The choice of indexing strategy balances read speed against write overhead. In exam questions, you might be asked to suggest suitable columns for indexing based on query patterns, or explain why an index speeds up certain searches.
选择索引策略时需要平衡读取速度与写入开销。考试中可能会要求根据查询模式推荐适合加索引的列，或解释索引为何能加速特定搜索。
9. Transaction Management and ACID | 事务管理与 ACID 特性
A transaction is a logical unit of work consisting of one or more database operations. It must transform the database from one consistent state to another. The ACID properties guarantee reliable processing:
事务是一个逻辑工作单元，由一个或多个数据库操作组成，必须将数据库从一种一致状态转变为另一种一致状态。ACID 特性确保了可靠的处理：
| Property | Description |
| Atomicity | All operations within a transaction are completed successfully or none are applied (all-or-nothing). If any part fails, the entire transaction is rolled back. |
| Consistency | A transaction must leave the database in a valid state, respecting all defined constraints, cascades, and triggers. |
| Isolation | Concurrent transactions operate as if they were the only transaction running; intermediate results are not visible to others until committed. |
| Durability | Once a transaction has been committed, its changes persist even in the event of a system failure (e.g., stored on non-volatile media). |
事务提交使用 COMMIT 语句，使用 ROLLBACK 可撤销未提交的更改。理解 ACID 有助于分析为何银行转账必须作为一个不可分割的事务处理 — 扣款与入账必须同时成功或同时失败。
10. Database Security and Backup | 数据库安全与备份策略
Protecting data is a critical aspect of database design. Security mechanisms include user authentication (usernames and passwords) and authorisation (permissions granted via GRANT and revoked via REVOKE statements). Fine-grained access control can restrict who can read, insert, update, or delete specific tables or views.
保护数据是数据库设计的关键方面。安全机制包括用户身份验证（用户名和口令）和授权（通过 GRANT 授予权限，REVOKE 撤销权限）。细粒度访问控制可限制谁能读取、插入、更新或删除特定表或视图。
SQL injection is a common attack where malicious SQL statements are injected into input fields. This can be prevented by using parameterised queries (prepared statements) and validating all user input on the server side.
SQL 注入 是一种常见攻击，恶意 SQL 语句被插入输入字段。可以通过使用参数化查询（预编译语句）以及在服务器端验证所有用户输入来防止此类攻击。
Backup strategies are essential for disaster recovery. A full backup copies the entire database. Incremental backups record only data changed since the last backup (full or incremental). Differential backups record changes since the last full backup. Regular backups and off-site storage ensure data can be restored after hardware failure, corruption, or malicious activity.
备份策略 对于灾难恢复至关重要。全备份复制整个数据库；增量备份仅记录自上次备份（全备份或增量备份）以来更改的数据；差异备份记录自上次全备份以来的所有更改。定期备份和异地存储确保硬件故障、数据损坏或恶意活动后数据仍可恢复。
Views can also be used as a security mechanism: they present a virtual table exposing only selected columns or rows, hiding sensitive data from unauthorised users without granting direct table access.
视图也可用作安全机制：它提供虚拟表，只暴露选定的列或行，向未授权用户隐藏敏感数据，而无需授予直接的表访问权限。
Published by TutorHao | Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
或截图后用「扫一扫 → 相册」选择本图