Database Revision Guide for IGCSE AQA Computer Science | IGCSE AQA 计算机：数据库考点精讲

📚 Database Revision Guide for IGCSE AQA Computer Science | IGCSE AQA 计算机：数据库考点精讲

A solid understanding of databases is essential for the IGCSE AQA Computer Science exam. This guide breaks down every key concept, from relational theory to practical SQL, helping you master queries, keys, normalisation, and more.

扎实掌握数据库知识对 IGCSE AQA 计算机科学考试至关重要。本文精讲从关系理论到实用 SQL 的每一个核心概念，助你攻克查询、键、规范化等考点。

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

A database is an organised collection of data stored and accessed electronically. A database management system (DBMS) allows users to create, read, update and delete data, while enforcing security and integrity.

数据库是以电子方式存储和访问的有组织的数据集合。数据库管理系统 (DBMS) 允许用户创建、读取、更新和删除数据，同时强制实施安全与完整性控制。

The most common type of database examined at IGCSE is the relational database, where data is structured in tables linked by keys. This reduces duplication and improves data consistency.

IGCSE 考试中最常见的是关系数据库，其中数据被组织在通过键链接的表中。这减少了重复，提高了数据一致性。

2. Tables, Records, Fields | 表、记录和字段

In a relational database, data is organised into tables. Each table represents an entity, such as STUDENT, BOOK or LOAN. A table consists of rows and columns.

在关系数据库中，数据被组织成表。每个表代表一个实体，例如 STUDENT、BOOK 或 LOAN。表由行和列组成。

A record (row) holds all information about one instance of the entity. A field (column) stores a single piece of data for each record, such as StudentID or DateOfBirth.

一条记录（行）保存着关于实体一个实例的所有信息。一个字段（列）则为每条记录存储单个数据项，例如 StudentID 或 DateOfBirth。

Every field has a defined data type, limiting the kind of values it can contain to ensure consistency.

每个字段都规定了数据类型，限制其可包含的值种类，从而确保一致性。

3. Primary Keys | 主键

A primary key is a field that uniquely identifies each record in a table. No two records can have the same primary key value, and it cannot be null.

主键是一个能唯一标识表中每条记录的字段。任意两条记录都不能有相同的主键值，且主键不能为空。

Good examples include StudentID, ProductCode, or ISBN. Sometimes a combination of fields is used as a composite primary key when no single field is unique.

合适的例子有 StudentID、ProductCode 或 ISBN。当单个字段无法保证唯一性时，可以使用字段组合作为复合主键。

Primary keys are essential for maintaining entity integrity and for establishing relationships between tables.

主键对维护实体完整性以及建立表间关系至关重要。

4. Foreign Keys | 外键

A foreign key is a field in one table that refers to the primary key of another table. It creates a link between two tables, enabling relational joins.

外键是一个表中的字段，它引用了另一个表的主键。它在两个表之间建立链接，从而实现关系连接。

For example, in a LOANS table, BookID could be a foreign key referencing the BOOK table’s primary key. This ensures referential integrity, meaning you cannot reference a non-existent book.

例如，在 LOANS 表中，BookID 可作为外键引用 BOOK 表的主键。这确保了引用完整性，即你无法引用不存在的书籍。

Foreign keys allow efficient organisation of related data and reduce redundancy by avoiding repeated data storage.

外键允许高效组织相关数据，并通过避免重复的数据存储来减少冗余。

5. Data Types | 数据类型

Choosing the correct data type for each field is critical for database performance and validation. Common data types include:

为每个字段选择正确的数据类型对数据库性能和验证至关重要。常见的数据类型有：

INTEGER: whole numbers, e.g. age, quantity
REAL/FLOAT: numbers with decimal points
VARCHAR/TEXT: strings of variable length, e.g. name
DATE/TIME: calendar dates and times
BOOLEAN: true/false values

INTEGER（整型）：整数，如年龄、数量
REAL/FLOAT（浮点型）：带小数点的数字
VARCHAR/TEXT（变长字符串）：可变长度字符串，如姓名
DATE/TIME（日期/时间）：日历日期和时间
BOOLEAN（布尔型）：真/假值

Selecting an appropriate data type enables the DBMS to check validity and optimise storage.

选择合适的数据类型使 DBMS 能够检查有效性并优化存储。

6. SQL Basics: SELECT and WHERE | SQL 基础：SELECT 与 WHERE

SQL (Structured Query Language) is used to manipulate relational databases. The most common command is SELECT, which retrieves data from one or more tables.

SQL（结构化查询语言）用于操作关系数据库。最常见的命令是 SELECT，它从一张或多张表中检索数据。

A basic query: SELECT FirstName, LastName FROM Student;
This returns all students’ first and last names.

基本查询：SELECT FirstName, LastName FROM Student;
这将返回所有学生的名和姓。

The WHERE clause filters records based on a condition:
SELECT * FROM Student WHERE YearGroup = 11;
This returns all fields for students in Year 11.

WHERE 子句基于条件筛选记录：
SELECT * FROM Student WHERE YearGroup = 11;
这返回所有 Year 11 学生的全部字段。

Operators like =, <, >, <=, >=, <> (not equal), AND, OR can be used in conditions.

条件中可使用 =、<、>、<=、>=、<>（不等于）、AND、OR 等运算符。

7. SQL: ORDER BY and Aggregate Functions | SQL：排序与聚合函数

The ORDER BY clause sorts results in ascending (ASC) or descending (DESC) order. For example:
SELECT Name, Score FROM Exam ORDER BY Score DESC;

ORDER BY 子句按升序 (ASC) 或降序 (DESC) 对结果排序。例如：
SELECT Name, Score FROM Exam ORDER BY Score DESC;

Aggregate functions perform calculations on a set of values and return a single value. The most common are:

聚合函数对一组值执行计算并返回单个值。最常见的有：

Function / 函数	Description / 描述
COUNT(*)	Counts number of records / 统计记录数
SUM(column)	Totals values in a column / 对列值求和
AVG(column)	Calculates average / 计算平均值
MAX(column)	Returns highest value / 返回最大值
MIN(column)	Returns lowest value / 返回最小值

Example: SELECT AVG(Mark) FROM Results WHERE Subject = 'Math';

示例：SELECT AVG(Mark) FROM Results WHERE Subject = 'Math';

8. SQL: Insert, Update, Delete | SQL：插入、更新、删除

To add a new record, use INSERT INTO:
INSERT INTO Student (StudentID, Name, Year) VALUES (107, 'Aisha', 10);

要添加新记录，使用 INSERT INTO：
INSERT INTO Student (StudentID, Name, Year) VALUES (107, 'Aisha', 10);

The UPDATE statement modifies existing records. Always use a WHERE clause to avoid changing all rows:
UPDATE Student SET Year = 11 WHERE StudentID = 107;

UPDATE 语句用于修改现有记录。务必使用 WHERE 子句，以免更改所有行：
UPDATE Student SET Year = 11 WHERE StudentID = 107;

DELETE removes records. Again, include a WHERE clause:
DELETE FROM Student WHERE StudentID = 107;

DELETE 用于删除记录。同样要包含 WHERE 子句：
DELETE FROM Student WHERE StudentID = 107;

These commands are known as data manipulation language (DML). They do not change the structure of the table.

这些命令被称为数据操作语言 (DML)，它们不会更改表的结构。

9. SQL Joins | SQL 连接

Joins combine rows from two or more tables based on a related column, typically a foreign key. The most common is the INNER JOIN.

连接基于相关列（通常是外键）将两个或多个表中的行组合起来。最常用的是 INNER JOIN。

Example: List each student’s name and the title of the book they borrowed.
SELECT Student.Name, Book.Title
FROM Student
INNER JOIN Loan ON Student.StudentID = Loan.StudentID
INNER JOIN Book ON Loan.BookID = Book.BookID;

示例：列出每位学生的姓名及其所借书的书名。
SELECT Student.Name, Book.Title
FROM Student
INNER JOIN Loan ON Student.StudentID = Loan.StudentID
INNER JOIN Book ON Loan.BookID = Book.BookID;

An INNER JOIN returns only rows where there is a match in both tables. Other types include LEFT JOIN and RIGHT JOIN, which include non-matching rows from one side.

INNER JOIN 只返回两个表中都有匹配的行。其他类型包括 LEFT JOIN 和 RIGHT JOIN，它们会包含一侧未匹配的行。

10. Entity-Relationship Diagrams | 实体关系图

An Entity-Relationship (ER) diagram is a visual tool for designing databases. It shows entities (tables) and the relationships between them.

实体关系 (ER) 图是设计数据库的可视化工具。它显示了实体（表）以及它们之间的关系。

Relationships can be one-to-one (1:1), one-to-many (1:M), or many-to-many (M:N). For example, one student can borrow many books, so Student to Loan is 1:M.

关系可以是一对一 (1:1)、一对多 (1:M) 或多对多 (M:N)。例如，一个学生可以借多本书，因此 Student 对 Loan 是 1:M。

Many-to-many relationships are typically resolved using a linking table, which breaks them into two 1:M relationships.

多对多关系通常通过使用链接表来解决，将其分解为两个 1:M 关系。

ER diagrams help ensure the database design is logical, eliminates redundancy, and supports required queries.

ER 图有助于确保数据库设计逻辑合理、消除冗余并支持所需的查询。

11. Normalisation | 规范化

Normalisation is the process of organising data to reduce redundancy and improve integrity. The first three normal forms (1NF, 2NF, 3NF) are relevant to IGCSE.

规范化是组织数据以减少冗余、提高完整性的过程。前三个范式（1NF、2NF、3NF）与 IGCSE 相关。

1NF: Eliminate repeating groups; every field contains atomic values; each record is unique with a primary key.
1NF：消除重复组；每个字段包含原子值；每条记录通过主键唯一标识。
2NF: Achieve 1NF and remove partial dependencies – all non-key fields must depend on the entire primary key (important for composite keys).
2NF：满足 1NF 且消除部分依赖——所有非键字段必须完全依赖于整个主键（对于复合主键尤为重要）。
3NF: Achieve 2NF and remove transitive dependencies – non-key fields must depend only on the primary key, not on another non-key field.
3NF：满足 2NF 且消除传递依赖——非键字段只能依赖于主键，而不能依赖于另一个非键字段。

Normalised databases are easier to maintain and less likely to suffer from update anomalies.

规范化的数据库更易于维护，并且不太可能出现更新异常。

12. Data Integrity and Validation | 数据完整性与验证

Data integrity means data is accurate, consistent, and reliable. It is enforced through validation rules, data types, and referential integrity.

数据完整性指数据准确、一致、可靠。它通过验证规则、数据类型和引用完整性来强制实施。

Validation checks include presence check (required field), range check (value between min and max), type check (correct data type), format check (pattern match), and length check.

验证检查包括存在检查（必填字段）、范围检查（值介于最小和最大之间）、类型检查（正确的数据类型）、格式检查（模式匹配）和长度检查。

Verification, such as double entry, ensures data entered matches the original source, while validation ensures it follows rules.

验证（如双重输入）确保输入的数据与原始数据源一致，而有效性检查则确保其遵守规则。

Referential integrity means that foreign key values must match existing primary key values in the referenced table, preventing orphan records.

引用完整性意味着外键值必须与被引用表中的现有主键值匹配，从而防止孤立记录。

Together, these measures maintain the quality of the database as data is added, updated or deleted.

这些措施共同保证数据在添加、更新或删除时数据库的质量。

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

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

Database Revision Guide for IGCSE AQA Computer Science | IGCSE AQA 计算机：数据库 考点精讲