Database Essentials for CCEA IGCSE Computer Science | 数据库考点精讲

📚 Database Essentials for CCEA IGCSE Computer Science | 数据库考点精讲

Databases form the backbone of modern software systems, storing and organising data so that it can be quickly retrieved, updated, and analysed. In the CCEA IGCSE Computer Science specification, you are expected to understand how relational databases work, how to query them using SQL, and how to design structures that minimise redundancy and preserve integrity. This revision guide walks you through every core concept, from basic terminology to normalisation, with clear explanations and exam-focused tips.

数据库是现代软件系统的支柱，它存储和组织数据，使之能被快速检索、更新和分析。在 CCEA IGCSE 计算机科学大纲中，你需要理解关系型数据库的工作原理，学会使用 SQL 进行查询，并能设计出减少冗余、保持完整性的数据结构。这份复习指南将带你逐一梳理从基本术语到规范化的每一个核心概念，讲解清晰，并附有应试技巧。

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

A database is a structured collection of data held electronically. It allows users to store information in a way that makes it easy to access, manage, and update. Instead of storing data in separate files, a database centralises the data, reducing duplication and improving consistency. Databases are managed by software called a Database Management System (DBMS), which provides tools to create, interrogate, and maintain the data. Examples of DBMS include Microsoft Access, MySQL, and Oracle.

数据库是以电子方式存储的结构化数据集合。它让用户能够以易于访问、管理和更新的方式存储信息。与将数据存放在分散的文件中不同，数据库将数据集中存放，从而减少重复、提高一致性。数据库由称为数据库管理系统（DBMS）的软件管理，该系统提供了创建、查询和维护数据的工具。常见的 DBMS 有 Microsoft Access、MySQL 和 Oracle。

2. Key Database Terminology | 关键数据库术语

Before diving deeper, you need to be confident with the building blocks of a database. A table is a collection of related data organised in rows and columns. Each row is called a record (or tuple) and represents a single entity, such as one student or one book. Each column is called a field (or attribute) and represents one piece of information about that entity, for example a student’s name or date of birth. Understanding the difference between a record and a field is fundamental to database theory and exam questions.

在深入之前，你需要熟悉构成数据库的基本构件。表是按行和列组织的相关数据集合。每一行称为一条记录（或元组），代表单个实体，例如一个学生或一本书。每一列称为一个字段（或属性），表示该实体的某一条信息，例如学生的姓名或出生日期。理解记录和字段之间的区别是数据库理论与考试题的基础。

Table: Stores data about a specific subject (e.g. Students, Books). | 表：存储关于特定主题的数据（如学生表、书籍表）。
Record: A complete set of information about one item (a row). | 记录：关于一个项目的完整信息集（一行）。
Field: A single category of information within a record (a column). | 字段：记录内某类单一信息（一列）。
Data value: The actual entry at the intersection of a record and a field. | 数据值：记录和字段交叉处的实际内容。

3. Data Types and Field Properties | 数据类型与字段属性

Every field in a database table must have a defined data type that restricts the kind of information it can store. Common data types include Text/String for names and addresses, Integer for whole numbers, Real/Decimal for numbers with fractional parts, Date/Time for dates, and Boolean for true/false values. Choosing the correct data type is vital because it improves data integrity, saves storage space, and enables correct validation.

数据库表中的每个字段都必须定义一个数据类型，限制它能存储的信息种类。常见的数据类型有：用于姓名地址的文本/字符串、用于整数的整型、用于小数的实型/十进制、用于日期的日期/时间和用于真假的布尔型。选择正确的数据类型至关重要，因为它能提高数据完整性、节省存储空间，并能进行正确的验证。

Field properties further control what can be entered. Field size limits the number of characters, format controls how data is displayed, input mask forces a specific pattern, and a default value automatically fills in a field when a new record is created. Exam questions often ask you to select appropriate data types and properties for a given scenario.

字段属性进一步控制输入内容。字段大小限制字符数量，格式控制数据显示方式，输入掩码强制特定模式，默认值在创建新记录时自动填充字段。考试题常要求你为给定场景选择合适的数据类型和属性。

4. Primary Keys and Foreign Keys | 主键与外键

A primary key is a field (or combination of fields) that uniquely identifies each record in a table. No two records can have the same primary key value, and the field cannot be left blank. Typical choices include a StudentID, ProductCode, or an automatically generated number. The primary key is the most important constraint in a table because it guarantees entity integrity.

主键是唯一标识表中每条记录的一个字段（或字段组合）。任意两条记录的主键值不得重复，且该字段不能为空。常见的主键有 StudentID、ProductCode 或自动生成的数字。主键是表中最重要的约束，因为它保证了实体完整性。

A foreign key is a field in one table that refers to the primary key of another table. It establishes a link between two tables and is the basis of relational database design. For example, in a Library database, the Loans table might contain a foreign key ‘StudentID’ that references the primary key in the Students table. Foreign keys enforce referential integrity: you cannot enter a value in the foreign key column unless it already exists as a primary key in the related table.

外键是某表中的一个字段，它引用另一张表的主键。它在两张表之间建立联系，是关系数据库设计的基础。例如，在图书馆数据库中，借阅表可能包含一个外键“StudentID”，它引用学生表中的主键。外键强制参照完整性：你不能在外键列中输入一个在关联表中并不存在的主键值。

5. Relational Databases and Relationships | 关系数据库与关系

A relational database organises data into multiple tables that are linked by common fields. This approach reduces redundancy by splitting data into logical groups. The three types of relationships you must know are: one-to-one (one record in Table A relates to exactly one record in Table B), one-to-many (one record in Table A relates to many records in Table B, e.g. one customer to many orders), and many-to-many (many records in Table A relate to many records in Table B, e.g. students and courses). Many-to-many relationships are implemented using a junction (or link) table that contains two foreign keys.

关系数据库将数据组织成多张通过公共字段连接的表。这种方法通过将数据分割为逻辑组来减少冗余。你必须掌握的三种关系类型是：一对一（表 A 中的一条记录恰好与表 B 中的一条记录相关）、一对多（表 A 中的一条记录与表 B 中的多条记录相关，如一个客户对应多笔订单）和多对多（表 A 中的多条记录与表 B 中的多条记录相关，如学生与课程）。多对多关系通过包含两个外键的连接表来实现。

In CCEA exams, you may be asked to interpret an entity-relationship diagram or suggest how tables should be linked using primary and foreign keys. Always identify the “one” side and the “many” side, then place the foreign key on the “many” side.

在 CCEA 考试中，你可能需要解读实体关系图，或建议如何使用主键和外键连接表。始终先识别“一”端和“多”端，然后把外键放在“多”端。

6. Introduction to SQL | SQL 简介

SQL (Structured Query Language) is the standard language used to communicate with relational databases. You need to know two main categories: Data Definition Language (DDL) for creating and altering table structures, and Data Manipulation Language (DML) for querying and modifying the data itself. At IGCSE level, the focus is on DML statements: SELECT, INSERT, UPDATE, and DELETE. SQL commands are not case-sensitive, but it is conventional to write keywords in uppercase to improve readability.

SQL（结构化查询语言）是与关系数据库通信的标准语言。你需要了解两大类语言：用于创建和修改表结构的数据定义语言（DDL），以及用于查询和修改数据本身的数据操纵语言（DML）。在 IGCSE 阶段，重点是 DML 语句：SELECT、INSERT、UPDATE 和 DELETE。SQL 命令不区分大小写，但习惯上将关键字大写以提高可读性。

A basic SELECT statement has the structure:

SELECT field1, field2 FROM TableName;

You can also select all fields using the asterisk wildcard: SELECT * FROM Students;. Every SQL statement ends with a semicolon.

基本的 SELECT 语句结构为：

SELECT 字段1, 字段2 FROM 表名;

你也可以使用星号通配符选择所有字段：SELECT * FROM Students;。每条 SQL 语句以分号结束。

7. SQL Queries: SELECT and WHERE | SQL 查询：SELECT 与 WHERE

The real power of SQL comes from filtering data with the WHERE clause. This allows you to retrieve only those records that meet a specific condition. For example:

SQL 的真正威力来自于用 WHERE 子句过滤数据。你可以通过它只检索满足特定条件的记录。例如：

SELECT Name, Grade FROM Students WHERE Grade = ‘A’;

Comparison operators include =, <, >, <=, >=, and <> (not equal). You can combine conditions using AND (both must be true) and OR (at least one must be true). For text patterns, the LIKE operator is used with wildcards: ‘%’ represents any sequence of characters, and ‘_’ represents a single character. Example: WHERE Name LIKE ‘J%’ finds all names starting with J.

比较运算符包括 =、<、>、<=、>= 和 <>（不等于）。你可以用 AND（必须同时为真）和 OR（至少一个为真）组合条件。对于文本模式，使用 LIKE 运算符配合通配符：’%’ 表示任意字符序列，’_’ 表示单个字符。例如：WHERE Name LIKE ‘J%’ 查找所有以 J 开头的名字。

You may also need to eliminate duplicate results with the DISTINCT keyword: SELECT DISTINCT Subject FROM Teachers; returns each subject only once.

你或许还需要用 DISTINCT 关键字消除重复结果：SELECT DISTINCT Subject FROM Teachers; 每个科目只返回一次。

8. SQL for Data Modification: INSERT, UPDATE, DELETE | 数据操作 SQL：INSERT、UPDATE、DELETE

Adding new records is done with the INSERT statement. The syntax is:

添加新记录使用 INSERT 语句。语法为：

INSERT INTO Students (StudentID, Name, Year) VALUES (105, ‘Mia’, 11);

You must list the fields in the same order as the values. If you are inserting values for every field in the same order as the table definition, you can omit the field list, but it is safer to include it.

字段列表必须与值的顺序一致。如果你按表定义的顺序为每个字段插入值，则可以省略字段列表，但最好还是写出来。

To change existing data, use UPDATE:

要修改现有数据，使用 UPDATE：

UPDATE Students SET Year = 12 WHERE StudentID = 105;

Always include a WHERE clause in UPDATE and DELETE statements; otherwise, the change will apply to every row in the table. This is a favourite trap in exam analysis questions.

在 UPDATE 和 DELETE 语句中务必包含 WHERE 子句，否则更改将作用于表中所有行。这是考试分析题中常见的陷阱。

DELETE removes rows:

DELETE 删除行：

DELETE FROM Students WHERE StudentID = 105;

9. Sorting and Searching with SQL | 使用 SQL 排序和搜索

The ORDER BY clause sorts the result set in ascending (ASC) or descending (DESC) order. For example:

ORDER BY 子句按升序（ASC）或降序（DESC）对结果集排序。例如：

SELECT Name, Score FROM Results ORDER BY Score DESC;

You can sort by multiple fields: ORDER BY Year ASC, Surname ASC. This is useful when you need to present data logically, for example listing students by year group and then alphabetically.

你可以按多个字段排序：ORDER BY Year ASC, Surname ASC。这在需要逻辑地呈现数据时很有用，例如按年级、然后按字母顺序列出学生。

Searching for data in CCEA exams often involves writing SQL to retrieve records that match complex criteria. Practise translating English-language requirements into precise SQL, paying attention to word clues: “all”, “only”, “greater than”, “between”, “starting with”. Sometimes you must use the IN operator to check against a list of values: WHERE Subject IN (‘Maths’, ‘Physics’, ‘Chemistry’).

CCEA 考试中的数据搜索往往要求编写 SQL 来检索符合复杂条件的记录。练习将英语描述转化为精确的 SQL，注意关键词：“所有”“只有”“大于”“介于”“以……开头”。有时你需要使用 IN 运算符来检查值列表：WHERE Subject IN (‘Maths’, ‘Physics’, ‘Chemistry’)。

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

Data integrity means that the data is accurate, consistent, and reliable. Several validation techniques help maintain integrity at the point of entry. Presence check ensures a field is not left blank (required field). Range check verifies that a number or date falls within a specified scope. Type check confirms the data is of the correct type (e.g. integer). Length check restricts the number of characters. Format check tests whether the data matches a specific pattern, such as a postcode. Check digit is commonly used in barcodes and ISBNs to detect input errors.

数据完整性意味着数据准确、一致且可靠。几种验证技术有助于在输入时维持完整性。存在检查确保字段不为空（必填字段）。范围检查验证数字或日期是否在指定范围内。类型检查确认数据为正确类型（如整数）。长度检查限制字符数量。格式检查测试数据是否匹配特定模式，如邮政编码。校验位常用于条形码和 ISBN 以检测输入错误。

Verification is different from validation: it ensures that data has been entered correctly by comparing two copies, for example entering an email address twice or visually checking a form. CCEA often asks you to distinguish between these two concepts and suggest suitable checks for a given context.

验证与校验不同：它通过对比两份副本确保数据输入无误，例如两次输入电子邮件地址或目视检查表单。CCEA 常常要求你区分这两个概念，并为给定情景建议合适的检查方法。

11. Reducing Data Redundancy with Normalisation | 通过规范化减少数据冗余

Data redundancy occurs when the same piece of data is stored in multiple places, leading to wasted space and potential inconsistencies. Normalisation is the process of organising data to minimise duplication by splitting tables and establishing relationships. The first three normal forms are sufficient for IGCSE:

数据冗余是指相同的数据被存储在多个位置，导致空间浪费和潜在的不一致性。规范化是通过拆分表并建立关系来尽量减少重复的过程。前三范式足以应对 IGCSE：

1NF (First Normal Form): Each field contains only atomic (indivisible) values, and each record is unique. No repeating groups. | 第一范式（1NF）：每个字段只包含原子（不可再分）值，每条记录唯一，没有重复组。
2NF (Second Normal Form): Must be in 1NF and all non-key fields are fully dependent on the whole primary key (no partial dependencies). | 第二范式（2NF）：必须满足 1NF，且所有非键字段完全依赖于整个主键（无部分依赖）。
3NF (Third Normal Form): Must be in 2NF and no non-key field depends on another non-key field (no transitive dependencies). | 第三范式（3NF）：必须满足 2NF，且没有非键字段依赖于另一个非键字段（无传递依赖）。

In the exam, you might be given a flat-file table and asked to normalise it by identifying how to split it into smaller, linked tables. Look for fields that repeat, or fields that describe something other than the primary key.

在考试中，你可能会看到一个平面文件表，并被要求通过拆分表来规范化它。注意寻找重复的字段，或那些描述主键以外内容的字段。

12. Exam Tips and Common Pitfalls | 考试技巧与常见误区

Database questions in CCEA IGCSE Computer Science often combine theory with practical SQL writing. Here are the most frequent pitfalls and how to avoid them:

CCEA IGCSE 计算机科学中的数据库题目常常将理论与实践 SQL 编写相结合。以下是最常见的误区及避免方法：

Forgetting the semicolon: Every SQL statement must end with a semicolon in your answer unless the question specifies otherwise. | 忘记分号：每条 SQL 语句必须以分号结尾，除非题目另有说明。
Omitting WHERE in UPDATE/DELETE: If you write UPDATE Students SET Year = 13; without a WHERE clause, you will alter every record. The examiner expects you to state this risk when analysing code. | 在 UPDATE/DELETE 中遗漏 WHERE：如果写 UPDATE Students SET Year = 13; 而没有 WHERE 子句，你将修改每一条记录。考官期望你在分析代码时指出这一风险。
Choosing the wrong data type: Storing a telephone number as an integer may lose leading zeros. Always use text for numbers that are not used in calculations. | 选择错误的数据类型：将电话号码存储为整数可能丢失前导零。不参与计算的数字应始终使用文本型。
Confusing verification and validation: Validation checks if the data is reasonable; verification checks if it is entered correctly. Use precise terminology. | 混淆校验与验证：验证检查数据是否合理；校验检查数据是否输入正确。请使用准确术语。
Not linking tables: When designing a relational database, always show how tables are linked with primary and foreign keys. A question that says “a student can borrow many books” implies a one-to-many relationship; the foreign key goes in the Loans table. | 未连接表：设计关系数据库时，务必展示如何用主键和外键连接表。题目说“一个学生可以借多本书”意味着一种一对多关系；外键应在借阅表中。

Practise writing SQL for different scenarios, and always read the scenario carefully to identify the tables, their fields, and the required output. Time spent understanding the problem will reward you with accurate, full-mark answers.

多练习为不同情景编写 SQL，并始终仔细阅读题目以识别表、字段和所需输出。花在理解问题上的时间将帮助你获得准确、满分的答案。

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

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

Database Essentials for CCEA IGCSE Computer Science | 数据库考点精讲