Database A-Level CIE Computer Science Revision | A-Level CIE 计算机：数据库考点精讲

📚 Database A-Level CIE Computer Science Revision | A-Level CIE 计算机：数据库考点精讲

This article provides a comprehensive revision guide for the CIE A-Level Computer Science database topic. We will cover relational models, keys, ER diagrams, normalisation, SQL, DBMS features, and transaction management, all aligned with the latest syllabus.

本文为 CIE A-Level 计算机科学数据库专题提供全面复习指南。我们将涵盖关系模型、键、ER 图、规范化、SQL、DBMS 特性以及事务管理，均与最新考纲一致。

1. Introduction to Databases | 数据库简介

A database is an organised collection of structured information, or data, typically stored electronically in a computer system. It is managed by a Database Management System (DBMS), which allows users to create, retrieve, update, and manage data efficiently. Databases solve problems associated with file-based systems, such as data redundancy, inconsistency, and poor data integrity.

数据库是有组织的结构化信息或数据的集合，通常以电子方式存储在计算机系统中。它由数据库管理系统 (DBMS) 管理，允许用户高效地创建、检索、更新和管理数据。数据库解决了基于文件系统的问题，如数据冗余、不一致性和数据完整性差。

Data in a database is accessed using a query language, most commonly SQL. The DBMS enforces integrity constraints and provides a level of abstraction so that users do not need to understand physical storage details.

数据库中的数据使用查询语言（最常见的是 SQL）访问。DBMS 强制执行完整性约束并提供一定的抽象级别，因此用户无需了解物理存储细节。

2. Relational Database Model | 关系数据库模型

In the relational model, data is organised into tables (relations) consisting of rows (tuples) and columns (attributes). Each table represents an entity set, and relationships between tables are represented by values stored across tables. The schema of a relation is the set of attribute names and their domains, for example, a STUDENT table might have attributes: StudentID, Name, DateOfBirth.

在关系模型中，数据组织为表（关系），由行（元组）和列（属性）组成。每个表表示一个实体集，表之间的关系通过跨表存储的值来表示。关系模式是属性名称及其域的集合，例如，STUDENT 表可能有属性：StudentID、Name、DateOfBirth。

A relational database eliminates duplicate rows by enforcing a unique key for each tuple. Operations such as selection, projection, and join form the foundation of relational algebra, which underpins SQL. The degree of a relation is the number of attributes, and its cardinality is the number of tuples.

关系数据库通过为每个元组强制使用唯一键来消除重复行。选择、投影和连接等操作构成了关系代数的基础，而关系代数是 SQL 的基础。关系的度是属性的数量，其基数是元组的数量。

3. Primary Keys, Foreign Keys, and Candidate Keys | 主键、外键与候选键

A candidate key is a minimal set of attributes that uniquely identifies each tuple in a relation. A primary key is a chosen candidate key used to reference tuples. A foreign key is an attribute (or set of attributes) in one table that refers to the primary key of another table, establishing a link between them.

候选键是最小属性集合，可唯一标识关系中的每个元组。主键是选定的候选键，用于引用元组。外键是一个表中的一个（或多个）属性，引用另一个表的主键，从而建立联系。

Entity integrity ensures that no primary key value is null. Referential integrity ensures that foreign key values must either be null or match an existing primary key value in the referenced table. A composite key is a primary key consisting of multiple attributes. A foreign key can be part of a composite primary key in a junction table.

实体完整性确保主键值不能为空。参照完整性确保外键值要么为空，要么与所引用表的现有主键值匹配。复合键是由多个属性组成的主键。在连接表中，外键可以成为复合主键的一部分。

4. Entity-Relationship (ER) Modelling | 实体联系（ER）建模

ER diagrams visually represent entities, attributes, and relationships. Entities are depicted as rectangles, attributes as ovals, and relationships as diamonds. The cardinality of relationships (one-to-one, one-to-many, many-to-many) is shown using symbols like 1, M, or crow’s feet.

ER 图可视化表示实体、属性和关系。实体用矩形表示，属性用椭圆，关系用菱形。关系的基数（一对一、一对多、多对多）使用 1、M 或鱼尾纹等符号表示。

In relational implementation, a one-to-many relationship is modeled by posting the primary key of the “one” side as a foreign key in the “many” table. A many-to-many relationship is resolved using an associative (junction) table containing foreign keys referencing both participating tables.

在关系实现中，一对多关系通过将“一”端的主键作为外键发布到“多”表中来建模。多对多关系通过使用一个关联（连接）表来解决，该表包含引用两个参与表的外键。

5. Database Normalisation: 1NF, 2NF, 3NF | 数据库规范化：1NF, 2NF, 3NF

Normalisation is the process of organising data to reduce redundancy and improve integrity. The first normal form (1NF) requires that each attribute contain only atomic (indivisible) values and that there are no repeating groups. Table columns must hold single values.

规范化是组织数据以减少冗余和提高完整性的过程。第一范式 (1NF) 要求每个属性仅包含原子（不可再分）值，并且没有重复组。表格列必须保存单一值。

Second normal form (2NF) builds on 1NF: all non-key attributes must be fully functionally dependent on the whole primary key, not just part of it. This eliminates partial dependencies. Third normal form (3NF) further requires that there are no transitive dependencies: a non-key attribute must depend directly on the primary key, not on another non-key attribute.

第二范式 (2NF) 在1NF基础上：所有非键属性必须完全函数依赖于整个主键，而不仅是部分主键，从而消除部分依赖。第三范式 (3NF) 进一步要求不存在传递依赖：非键属性必须直接依赖于主键，而不依赖于另一个非键属性。

Consider an unnormalised Order table: OrderID, Date, CustName, Product1, Price1, Product2, Price2. After 1NF, repeat groups are separated into rows. 2NF removes partial dependency of customer details on part of a composite key. 3NF separates vendor details to eliminate transitive dependency.

考虑一个未规范化的订单表：OrderID、Date、CustName、Product1、Price1、Product2、Price2。经过1NF，重复组被拆分为行。2NF 移除了客户详情对复合键的部分依赖。3NF 分离出供应商详情以消除传递依赖。

6. SQL Data Definition Language (DDL) | SQL 数据定义语言

SQL DDL commands define and manage the database structure. CREATE TABLE defines a new table with columns, data types, and constraints (e.g., PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY). ALTER TABLE modifies an existing table’s structure, such as adding or dropping columns. DROP TABLE removes a table entirely.

SQL DDL 命令定义和管理数据库结构。CREATE TABLE 定义新表，包括列、数据类型和约束（如 PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY）。ALTER TABLE 修改现有表的结构，例如添加或删除列。DROP TABLE 完全删除表。

CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Email VARCHAR(100) UNIQUE);

创建表的示例：包含主键、非空约束和唯一约束。

Data types vary across DBMS but commonly include INTEGER, VARCHAR(n), DATE, FLOAT, and BOOLEAN. Constraints enforce business rules at the database level.

数据类型因 DBMS 而异，但通常包括 INTEGER、VARCHAR(n)、DATE、FLOAT 和 BOOLEAN。约束在数据库级别强制执行业务规则。

7. SQL Data Queries | SQL 数据查询

SELECT statements retrieve data from one or more tables. The basic syntax includes SELECT column(s) FROM table WHERE condition; ORDER BY sorts the result set. Aggregate functions (COUNT, SUM, AVG, MAX, MIN) are used with GROUP BY. HAVING filters groups, similar to WHERE for rows but applied after aggregation.

SELECT 语句从一张或多张表中检索数据。基本语法包括 SELECT 列 FROM 表 WHERE 条件；ORDER BY 对结果集排序。聚合函数（COUNT、SUM、AVG、MAX、MIN）与 GROUP BY 一起使用。HAVING 过滤组，类似于行的 WHERE，但在聚合之后应用。

SELECT Name, AVG(Mark) FROM Student JOIN Result ON Student.ID = Result.StudentID GROUP BY Name HAVING AVG(Mark) > 70;

该查询返回平均分数超过70的学生姓名和平均分，使用了连接和聚合。

Joins combine tables: INNER JOIN returns matching records; LEFT JOIN returns all from the left table and matching from the right, filling with NULLs where no match; RIGHT JOIN is the opposite. A self-join can be used to compare rows within the same table.

连接合并表：INNER JOIN 返回匹配记录；LEFT JOIN 返回左表所有记录及右表匹配记录，无匹配时填充 NULL；RIGHT JOIN 相反。自连接可用于比较同一表中的行。

8. SQL Data Manipulation (DML) | SQL 数据操作

DML statements modify the data itself. INSERT adds new rows: INSERT INTO table (columns) VALUES (values). UPDATE changes existing rows: UPDATE table SET column = value WHERE condition. DELETE removes rows: DELETE FROM table WHERE condition. Without WHERE, all rows are affected.

DML 语句修改数据本身。INSERT 添加新行：INSERT INTO 表（列）VALUES（值）。UPDATE 更改现有行：UPDATE 表 SET 列 = 值 WHERE 条件。DELETE 删除行：DELETE FROM 表 WHERE 条件。没有 WHERE，将影响所有行。

INSERT INTO Student (ID, Name) VALUES (101, ‘Alice’);

插入新学生的示例。

Subqueries can be embedded inside DML statements, for instance, using a SELECT inside INSERT to copy data, or within a condition. The COMMIT and ROLLBACK commands control transaction state after DML.

子查询可嵌入 DML 语句，例如在 INSERT 中使用 SELECT 复制数据，或在条件中使用。COMMIT 和 ROLLBACK 命令在 DML 之后控制事务状态。

9. DBMS Features & Data Dictionary | 数据库管理系统特性与数据字典

A DBMS provides crucial services: data storage and retrieval management, transaction support, concurrency control, security and authorisation, backup and recovery, and a data dictionary. The data dictionary is a read-only collection of system tables storing metadata about the database structure, including table definitions, column information, constraints, indexes, and user privileges.

DBMS 提供关键服务：数据存储与检索管理、事务支持、并发控制、安全与授权、备份与恢复以及数据字典。数据字典是一组只读系统表，存储有关数据库结构的元数据，包括表定义、列信息、约束、索引和用户权限。

The data dictionary ensures that any operation, such as a SELECT query, is first checked against the metadata for existence of tables and columns, and that access rights are enforced. It is maintained automatically by the DBMS.

数据字典确保任何操作（例如 SELECT 查询）首先对照元数据检查表和列是否存在，并强制实施访问权限。它由 DBMS 自动维护。

10. Transactions, Concurrency & Security | 事务、并发与安全

A transaction is a sequence of database operations performed as a single logical unit of work. It must satisfy ACID properties: Atomicity (all operations succeed or the entire transaction is rolled back), Consistency (the transaction transforms the database from one valid state to another), Isolation (concurrent transactions do not interfere), and Durability (once committed, changes survive system failures).

事务是作为单个逻辑工作单元执行的一系列数据库操作。它必须满足 ACID 属性：原子性（所有操作成功，否则整个事务回滚）、一致性（事务将数据库从一个有效状态转换为另一个有效状态）、隔离性（并发事务互不干扰）和持久性（一旦提交，更改在系统故障后依然存在）。

Concurrency control uses locking mechanisms – shared locks allow reading, exclusive locks allow writing – to prevent issues like lost updates, dirty reads, and unrepeatable reads. Record locking ensures serialisability.

并发控制使用锁定机制——共享锁允许读取，排他锁允许写入——以防止丢失更新、脏读和不可重复读等问题。记录锁定确保了可串行化。

Security measures include user authentication (passwords, biometrics), authorisation (granting specific privileges via GRANT and REVOKE), encryption of sensitive data, and regular backups. Backups may be full, differential, or incremental, with recovery procedures tested routinely.

安全措施包括用户身份验证（密码、生物识别）、授权（通过 GRANT 和 REVOKE 授予特定权限）、敏感数据加密以及定期备份。备份可以是完整、差异或增量备份，恢复程序应定期测试。

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

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

Database A-Level CIE Computer Science Revision | A-Level CIE 计算机：数据库 考点精讲