A-Level WJEC Computer Science: Databases Revision Guide | A-Level WJEC 计算机：数据库 考点精讲

📚 A-Level WJEC Computer Science: Databases Revision Guide | A-Level WJEC 计算机：数据库 考点精讲

This guide covers the essential database topics for the WJEC A-Level Computer Science specification. We explore relational database theory, entity‑relationship modelling, normalisation, SQL, transaction management, and data integrity — all presented in clear, exam‑focused language.

本指南覆盖 WJEC A‑Level 计算机科学大纲中数据库部分的核心考点。我们将讲解关系数据库理论、实体关系建模、规范化、SQL、事务管理以及数据完整性，全部采用清晰且紧扣考试的语言。

1. The Relational Model | 关系模型

The relational model organises data into tables (relations) consisting of rows (tuples) and columns (attributes). Each table represents an entity set, and the relationships between entities are implemented through shared attributes. The model was proposed by E.F. Codd and remains the foundation of modern database systems. A key benefit is data independence, where the physical storage is separated from the logical structure seen by users.

关系模型将数据组织成由行（元组）和列（属性）组成的表（关系）。每个表代表一个实体集，实体之间的关系通过共享属性实现。该模型由 E.F. Codd 提出，至今仍是现代数据库系统的基础。其关键优势在于数据独立性，即物理存储与用户所见的逻辑结构分离。

2. Keys: Primary, Foreign, and Candidate | 键：主键、外键与候选键

A primary key is an attribute (or combination of attributes) that uniquely identifies each tuple in a relation. No part of a primary key can be NULL (entity integrity). A foreign key is an attribute in one table that matches the primary key of another table, enforcing referential integrity. A candidate key is any minimal set of attributes that could serve as the primary key; one is chosen and the rest become alternate keys. Composite keys use two or more attributes together to form a unique identifier.

主键是一个（或组合）属性，能够唯一标识关系中的每个元组。主键的任何部分都不能为 NULL（实体完整性）。外键是一张表中与另一张表的主键相匹配的属性，用于强制参照完整性。候选键是任何能够充当主键的最小属性集；选中一个作为主键后，其余成为备用键。复合键使用两个或多个属性共同构成唯一标识符。

3. Entity‑Relationship Modelling | 实体关系建模

Entity‑Relationship (ER) diagrams visually represent the data structure. Entities are shown as rectangles, attributes as ovals, and relationships as diamond shapes (or lines using Crow’s Foot notation). The degree of a relationship can be one‑to‑one (1:1), one‑to‑many (1:M), or many‑to‑many (M:N). M:N relationships must be resolved into an associative entity during the design phase. Participation constraints indicate whether all (mandatory) or only some (optional) entities take part in a relationship.

实体关系（ER）图直观地表示数据结构。实体用矩形表示，属性用椭圆表示，关系用菱形（或使用 Crow’s Foot 记号的连线）表示。关系的度可以是一对一（1:1）、一对多（1:M）或多对多（M:N）。多对多关系在设计阶段必须通过关联实体来解决。参与约束指明是所有实体参与（强制）还是部分实体参与（可选）。

4. Normalisation: 1NF, 2NF, and 3NF | 规范化：第一范式、第二范式与第三范式

Normalisation reduces data redundancy and prevents update anomalies. First Normal Form (1NF) requires that each cell holds a single atomic value and there are no repeating groups. Second Normal Form (2NF) builds on 1NF by removing partial dependencies — every non‑key attribute must depend on the whole of a composite primary key. Third Normal Form (3NF) removes transitive dependencies; a non‑key attribute must not depend on another non‑key attribute. The process is often summarised as: ‘The key, the whole key, and nothing but the key, so help me Codd.’

规范化可减少数据冗余并防止更新异常。第一范式（1NF）要求每个单元格存放单一原子值且无重复组。第二范式（2NF）在 1NF 的基础上消除部分依赖——每个非键属性必须依赖于整个复合主键。第三范式（3NF）消除传递依赖；非键属性不得依赖于另一个非键属性。这一过程常被概括为：“依赖于键，依赖于整个键，除键之外别无依赖，Codd 保佑。”

5. Introduction to SQL: Data Definition Language | SQL 入门：数据定义语言

SQL is divided into Data Definition Language (DDL) and Data Manipulation Language (DML). DDL commands include CREATE TABLE, ALTER TABLE, and DROP TABLE. When creating a table, you specify column names, data types (e.g., VARCHAR, INTEGER, DATE), and constraints such as PRIMARY KEY, FOREIGN KEY ... REFERENCES, NOT NULL, and UNIQUE. For example:

CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, DOB DATE);

SQL 分为数据定义语言（DDL）和数据操作语言（DML）。DDL 命令包括 CREATE TABLE、ALTER TABLE 和 DROP TABLE。创建表时需要指定列名、数据类型（如 VARCHAR、INTEGER、DATE）以及约束，例如 PRIMARY KEY、FOREIGN KEY ... REFERENCES、NOT NULL 和 UNIQUE。示例见上方。

6. SQL Data Manipulation: Queries and Joins | SQL 数据操作：查询与连接

DML statements retrieve and modify data. The SELECT statement forms the core of queries: SELECT column1, column2 FROM table WHERE condition ORDER BY column ASC;. The WHERE clause uses operators like =, <, >, LIKE, IN, BETWEEN. To combine data from multiple tables, we use joins: INNER JOIN returns rows with matching values in both tables; LEFT JOIN returns all rows from the left table and matched rows from the right. Aggregate functions (COUNT, SUM, AVG, MAX, MIN) are used with GROUP BY and filtered by HAVING.

DML 语句用于检索和修改数据。SELECT 语句构成查询的核心：SELECT 列1, 列2 FROM 表 WHERE 条件 ORDER BY 列 ASC;。WHERE 子句使用 =、<、>、LIKE、IN、BETWEEN 等运算符。要合并多个表的数据，我们使用连接：INNER JOIN 返回两表匹配的行；LEFT JOIN 返回左表所有行及右表的匹配行。聚合函数（COUNT、SUM、AVG、MAX、MIN）与 GROUP BY 配合使用，并由 HAVING 进行过滤。

7. Data Modification and Views | 数据修改与视图

To change existing data, we use INSERT INTO table (columns) VALUES (values);, UPDATE table SET column = value WHERE condition;, and DELETE FROM table WHERE condition;. A view is a virtual table based on the result‑set of a SELECT query. Created with CREATE VIEW view_name AS SELECT ..., views can simplify complex queries and enhance security by restricting user access to certain columns or rows.

要修改现有数据，我们使用 INSERT INTO 表 (列) VALUES (值);、UPDATE 表 SET 列 = 值 WHERE 条件; 以及 DELETE FROM 表 WHERE 条件;。视图是基于 SELECT 查询结果的虚拟表。通过 CREATE VIEW 视图名 AS SELECT ... 创建，视图可以简化复杂查询并通过限制用户对特定列或行的访问来增强安全性。

8. Database Management Systems (DBMS) | 数据库管理系统

A DBMS is software that manages the structure, storage, retrieval, and security of data. It provides concurrent access for multiple users, enforces integrity constraints, handles backup and recovery, and maintains a data dictionary. The DBMS separates the physical storage from the logical view, supports query optimisation, and offers utilities for import/export and performance monitoring. Examples include Microsoft SQL Server, MySQL, and SQLite.

DBMS 是管理数据结构、存储、检索和安全性的软件。它为多用户提供并发访问，强制执行完整性约束，处理备份与恢复，并维护数据字典。DBMS 将物理存储与逻辑视图分离，支持查询优化，并提供用于导入/导出和性能监控的实用程序。例子包括 Microsoft SQL Server、MySQL 和 SQLite。

9. Transaction Management and ACID | 事务管理与 ACID

A transaction is a logical unit of work consisting of one or more SQL statements that must be executed entirely or not at all. The ACID properties ensure reliable processing: Atomicity — all or nothing; Consistency — any transaction takes the database from one valid state to another; Isolation — concurrent transactions do not interfere; Durability — once committed, changes survive system failures. SQL uses BEGIN TRANSACTION, COMMIT, and ROLLBACK to control transactions.

事务是一个逻辑工作单元，包含一条或多条 SQL 语句，必须全部执行或全部不执行。ACID 属性确保可靠处理：原子性——全做或全不做；一致性——任何事务都将数据库从一个有效状态变更到另一个有效状态；隔离性——并发事务互不干扰；持久性——一旦提交，所做更改即使在系统故障后也能保留。SQL 使用 BEGIN TRANSACTION、COMMIT 和 ROLLBACK 来控制事务。

10. Indexes and Query Performance | 索引与查询性能

An index is a data structure (often a B‑tree) that speeds up data retrieval on a table at the cost of additional storage and slower writes. Indexes can be created on one or more columns using CREATE INDEX idx_name ON table(column);. While the DBMS automatically chooses whether to use an index, a well‑designed index dramatically reduces the number of disk reads needed for WHERE and JOIN operations. Over‑indexing, however, degrades performance on INSERT, UPDATE, and DELETE operations.

索引是一种数据结构（通常为 B‑树），能以额外的存储空间和较慢的写入为代价，加快表上的数据检索。可使用 CREATE INDEX 索引名 ON 表(列); 在一列或多列上创建索引。虽然 DBMS 会自动选择是否使用索引，但设计良好的索引能显著减少 WHERE 和 JOIN 操作所需的磁盘读取次数。然而，过度索引会降低 INSERT、UPDATE 和 DELETE 操作的性能。

11. Data Integrity and Security | 数据完整性与安全性

Data integrity is enforced through constraints: entity integrity (primary keys, no NULLs), referential integrity (foreign keys must point to existing primary keys), and domain integrity (data type, check constraints, NOT NULL, UNIQUE). Security is managed through user authentication, access privileges (GRANT/REVOKE), and encryption. The database administrator (DBA) defines roles and permissions to restrict who can read, insert, update, or delete data.

数据完整性通过约束强制执行：实体完整性（主键，无 NULL）、参照完整性（外键必须指向已存在的主键）以及域完整性（数据类型、检查约束、NOT NULL、UNIQUE）。安全性通过用户认证、访问权限（GRANT/REVOKE）和加密进行管理。数据库管理员（DBA）定义角色和权限，以限制谁可以读取、插入、更新或删除数据。

12. Database Design Lifecycle | 数据库设计生命周期

Designing a database follows a structured lifecycle: requirements analysis → conceptual design (ER models) → logical design (mapping to tables, normalisation) → physical design (indexing, storage) → implementation (DDL) → testing and maintenance. During logical design, M:N relationships are decomposed into linking tables, and attributes are checked against normal forms. Good design reduces redundancy and ensures that the database can evolve without major restructuring.

数据库设计遵循结构化的生命周期：需求分析 → 概念设计（ER 模型）→ 逻辑设计（映射为表、规范化）→ 物理设计（索引、存储）→ 实现（DDL）→ 测试与维护。在逻辑设计阶段，多对多关系被分解为连接表，属性需对照范式进行检查。良好的设计可减少冗余，并确保数据库无需大规模重构即可演进。

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

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