📚 IB CCEA Computer Science: Database Key Points Review | IB CCEA 计算机：数据库考点精讲
Databases form the backbone of virtually every modern application, whether you’re working on a school project, running an online store, or designing critical information systems. This guide covers the essential database concepts required for both IB Computer Science and CCEA GCE Computer Science specifications, including relational models, ER diagrams, normalisation, SQL, transactions, and security. We break down each topic with bilingual explanations, clear examples, and practical exam tips to help you master the syllabus and tackle exam questions with confidence.
数据库是现代应用程序的基石，无论是学校项目、在线商店还是关键信息系统，都离不开数据库的支持。本文梳理了IB计算机和CCEA GCE计算机课程中数据库部分的核心考点，包括关系模型、ER图、规范化、SQL、事务和安全等内容。以中英双语搭配简明实例和考试技巧，帮助大家系统掌握知识点，自信应对各类考题。
1. Relational Databases and Terminology | 关系数据库核心术语
A relational database organises data into tables (relations) consisting of rows (records or tuples) and columns (fields or attributes). Each table stores data about one entity type. The relational model uses primary keys, foreign keys, and constraints to maintain data integrity.
关系数据库将数据组织在表（关系）中，每张表由行（记录或元组）和列（字段或属性）构成，每张表存储一种实体类型的数据。关系模型通过主键、外键和约束来保证数据的完整性。
The concept of a domain defines the permissible values for an attribute. For example, a ‘Gender’ attribute might have a domain of {M, F, X}. Every table must have a unique name, and each column must have a distinct name within that table.
域的概念界定了属性的合法取值，例如“性别”属性的域为 {M, F, X}。每张表必须有唯一的名称，每列在表内也必须有不同的列名。
Key terms to remember: relation, tuple, attribute, domain, degree (number of columns), cardinality (number of rows). These form the foundation for everything else.
需要熟记的关键术语：关系、元组、属性、域、度（列数）、基数（行数），这些都是后续所有知识的基础。
2. Entity-Relationship Diagrams (ERDs) | 实体关系图 (ERD)
An Entity-Relationship Diagram visually models the entities, attributes, and relationships in a database system. Entity types are shown as rectangles, attributes as ovals, and relationships as diamonds. In CCEA and IB contexts, you are often asked to draw a simplified ERD or interpret one.
实体关系图用图形方式对数据库中的实体、属性和关系进行建模。实体类型用矩形表示，属性用椭圆形，关系用菱形。在CCEA和IB考试中，经常要求绘制简化的ER图或解读给定的ER图。
Cardinality ratios express how many instances of one entity can be associated with another: one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N). In modern notation, you might see crow’s foot symbols, but the logic remains the same.
基数比表示一个实体实例与另一实体实例的关联数量：一对一 (1:1)、一对多 (1:M) 和多对多 (M:N)。现代符号常用“鸦爪”表示，但底层逻辑相同。
When designing an ERD, always identify strong entities, weak entities (if any), and the relationships among them. Then convert the ERD into a set of tables, ensuring that many-to-many relationships are resolved by introducing a linking table.
设计ER图时，先识别强实体、弱实体（如果有）以及它们之间的关系，随后将ER图转化为一组表，多对多关系必须通过引入关联表来解决。
3. Keys: Primary, Foreign, and Candidate | 主键、外键与候选键
A primary key uniquely identifies each row in a table. It must be not null and unique. A candidate key is any attribute or minimal set of attributes that could serve as the primary key. A secondary key is an attribute used for fast retrieval but not for unique identification.
主键唯一标识表中的每一行，必须非空且唯一。候选键是任何能够充当主键的属性或最小属性组。辅助键用于快速检索，但不唯一标识行。
A foreign key is a column (or set of columns) in one table that refers to the primary key of another table. It establishes relationships between tables and enforces referential integrity. For example, a ‘StudentID’ in an Enrolment table would be a foreign key referencing the Student table.
外键是一张表中的列（或列集），它引用另一张表的主键，用于建立表间关系并强制引用完整性。例如，选课表中的“学号”就是引用学生表主键的外键。
Composite keys use two or more columns to form a unique identifier. When a table has no single natural primary key, you may use a surrogate key (e.g., an auto-incremented ID) for simplicity.
复合键使用两个或更多列构成唯一标识符。当表中没有单一的自然主键时，可以采用代理键（如自增编号）简化设计。
4. Normalisation: 1NF, 2NF, 3NF | 规范化：第一、第二、第三范式
Normalisation is the process of organising data to minimise redundancy and prevent update anomalies. We progress through normal forms: First Normal Form (1NF) demands atomic values (no repeating groups) and a primary key. Each cell must contain a single value.
规范化是组织数据以最小化冗余并避免更新异常的过程。三种范式逐步推进：第一范式 (1NF) 要求属性值原子化（无重复组）并定义主键，每个单元格只能包含一个值。
Second Normal Form (2NF) requires the table to be in 1NF and every non-key attribute must be fully functionally dependent on the entire primary key, not just part of it. This mainly applies to composite primary keys; partial dependencies must be removed.
第二范式 (2NF) 要求表满足1NF，且每个非键属性完全函数依赖于整个主键，而非只依赖部分主键。这主要针对复合主键，需要消除部分依赖。
Third Normal Form (3NF) adds that no non-key attribute should be transitively dependent on the primary key. In other words, if A → B and B → C, then C is transitively dependent on A. We move such dependent attributes to a new table.
第三范式 (3NF) 进一步要求不存在非键属性对主键的传递依赖。即如果 A → B 且 B → C，则 C 传递依赖 A。此时应将这些属性拆分到新的表中。
In practice, most well‑designed databases aim for 3NF, which is sufficient for exam‑level questions. Understanding the logic of functional dependency is key to solving normalisation exercises.
实际中多数设计良好的数据库都达到3NF，这已足够应对考试。掌握函数依赖的逻辑是解决规范化题目的关键。
5. SQL Basics: SELECT, FROM, WHERE | SQL 基础查询
SQL (Structured Query Language) is the standard language for relational databases. The most common command is SELECT column1, column2 FROM table WHERE condition;. The WHERE clause filters rows, and you can use operators like =, <>, >, <, AND, OR, BETWEEN, LIKE.
SQL（结构化查询语言）是关系数据库的标准语言。最常用的命令是 SELECT 列1, 列2 FROM 表 WHERE 条件;。WHERE子句用于筛选行，可使用 =、<>、>、<、AND、OR、BETWEEN、LIKE 等运算符。
You can sort results with ORDER BY column ASC|DESC and eliminate duplicates with SELECT DISTINCT column. Aggregate functions like COUNT, SUM, AVG, MAX, MIN are often used with GROUP BY and HAVING to summarise data.
使用 ORDER BY 列 ASC|DESC 排序结果，使用 SELECT DISTINCT 列 去重。聚合函数如 COUNT、SUM、AVG、MAX、MIN 常与 GROUP BY 和 HAVING 连用以汇总数据。
Remember that the HAVING clause filters groups after aggregation, while WHERE filters individual rows before grouping. This distinction is frequently tested.
注意 HAVING 子句在分组后对聚合结果进行筛选，而 WHERE 在分组前对行进行筛选，这一区别经常被考到。
6. SQL Joins and Multi-table Queries | SQL 多表连接查询
To combine data from multiple tables, we use joins. INNER JOIN returns rows that have matching values in both tables. LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table; unmatched right columns are filled with NULL.
要从多张表中组合数据，需要使用连接。INNER JOIN 返回两个表中匹配的行。LEFT JOIN (LEFT OUTER JOIN) 返回左表所有行，右表无匹配时对应字段为 NULL。
A typical exam question might ask: “List all students and their course names, including students not enrolled in any course.” This calls for a LEFT JOIN from Student to Enrolment and Course.
典型的考题可能是：“列出所有学生以及他们的课程名，包括未注册任何课程的学生。”这就需要从学生表到选课表和课程表使用 LEFT JOIN。
Equi-joins (using = in the ON clause) are the most common, but non-equi joins (using >, <) are possible. Self-joins occur when a table is joined with itself, often to find hierarchical relationships.
等值连接（ON 子句中使用 =）最常见，但也可以有非等值连接（使用 >、<）。自连接是将表与其自身连接，常用于查找层级关系。
7. Data Integrity and Constraints | 数据完整性与约束
Data integrity ensures the accuracy and consistency of data over its lifecycle. Entity integrity is enforced by primary keys (no nulls, unique). Referential integrity ensures that foreign key values must match an existing primary key value or be null.
数据完整性保证数据在其生命周期内的准确性和一致性。实体完整性由主键强制执行（非空、唯一）。引用完整性确保外键值必须匹配现有主键值或为空。
Domain integrity restricts attribute values by data type, format, or range (e.g., using CHECK constraints). User-defined integrity covers business rules, such as “a customer’s credit limit must not exceed 10000”.
域完整性通过数据类型、格式或范围限制属性值（如使用 CHECK 约束）。用户自定义完整性涵盖业务规则，例如“客户信用额度不得超过10000”。
In SQL, constraints like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT are declared at column or table level. They are the building blocks for a robust database schema.
在 SQL 中，NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK 和 DEFAULT 等约束在列级或表级声明，是健壮数据库模式的基石。
8. Views, Indexes, and Performance | 视图、索引与性能
A view is a virtual table based on the result set of a SELECT query. It does not store data physically but presents a customised window into the database. Views improve security by hiding sensitive columns and simplify complex queries.
视图是基于 SELECT 查询结果集的虚拟表，不物理存储数据，而是提供一个定制的数据库窗口。视图可隐藏敏感列以改善安全性，并简化复杂查询。
An index is a data structure that speeds up data retrieval. It works like a book’s index, allowing the database engine to find rows quickly. However, indexes slow down INSERT, UPDATE, and DELETE operations because the index must be maintained.
索引是一种加速数据检索的数据结构，类似书的目录，使数据库引擎能快速定位行。但索引会降低 INSERT、UPDATE、DELETE 的速度，因为需要维护索引。
For exam purposes, know the trade‑off between read performance and write overhead. Primary keys are automatically indexed; you can create indexes on columns used in WHERE, JOIN, or ORDER BY clauses.
考试中需理解读性能与写开销之间的权衡。主键自动建立索引；可在 WHERE、JOIN 或 ORDER BY 子句中频繁使用的列上手动创建索引。
9. Transactions and ACID Properties | 事务与 ACID 特性
A transaction is a logical unit of work that must be executed completely or not at all. The ACID model describes the key properties: Atomicity (all or nothing), Consistency (transactions bring database from one valid state to another), Isolation (concurrent transactions do not interfere), and Durability (committed changes survive system failures).
事务是一个逻辑工作单元，必须完整执行或完全不执行。ACID模型描述了关键特性：原子性（全有或全无）、一致性（事务使数据库从一个有效状态变为另一个）、隔离性（并发事务互不干扰）和持久性（已提交的更改在系统故障后仍存在）。
In SQL, a transaction begins with BEGIN TRANSACTION and ends with COMMIT (make changes permanent) or ROLLBACK (undo changes). Understanding transaction boundaries is vital for preserving data integrity in multi‑user systems.
在 SQL 中，事务以 BEGIN TRANSACTION 开始，以 COMMIT（永久保存）或 ROLLBACK（撤销更改）结束。理解事务边界对多用户系统中的数据完整性至关重要。
10. Database Security and SQL Injection | 数据库安全与 SQL 注入
Database security involves preventing unauthorised access and malicious attacks. Common measures include user authentication, access rights (GRANT/REVOKE), encryption, and regular backups.
数据库安全涉及防止未授权访问和恶意攻击。常见措施包括用户身份验证、访问权限管理（GRANT/REVOKE）、加密和定期备份。
SQL injection is a code injection technique where attackers insert malicious SQL statements into an application’s input fields. For example, entering ' OR '1'='1 in a login form could bypass authentication. It remains one of the top web security threats.
SQL 注入是一种代码注入技术，攻击者将恶意 SQL 语句插入应用程序的输入字段。例如，在登录框中输入 ' OR '1'='1 可能绕过认证，仍是最高级的网络安全威胁之一。
Mitigation techniques include parameterised queries (prepared statements), input validation, escaping special characters, and using stored procedures. IB and CCEA both test knowledge of how SQL injection works and how to prevent it.
防范措施包括参数化查询（预编译语句）、输入验证、转义特殊字符以及使用存储过程。IB 和 CCEA 都考查 SQL 注入的原理及预防方法。
11. Data Warehousing and Big Data (Optional) | 数据仓库与大数据（选学）
A data warehouse is a central repository for storing integrated, historical data from multiple sources, designed for analysis and reporting rather than day‑to‑day transactions (OLAP vs OLTP). Big data refers to extremely large datasets characterised by volume, velocity, and variety.
数据仓库是集中储存来自多个数据源的整合历史数据的存储库，面向分析和报表而非日常事务处理（OLAP 与 OLTP 之分）。大数据指具有大量、高速、多样特征的超大规模数据集。
While not always heavily examined, these concepts may appear in CCEA A2 or IB higher‑level papers, especially in contexts of data mining, distributed storage (Hadoop), and data integrity in NoSQL systems.
虽然不总是考试重点，但这些概念可能出现在 CCEA A2 或 IB 高等级试卷中，特别是在数据挖掘、分布式存储（Hadoop）和 NoSQL 数据完整性等背景下。
12. CCEA & IB Exam Tips | CCEA 与 IB 考试技巧
For CCEA papers, focus on designing a relational database from a scenario, normalising to 3NF, and writing SQL queries with joins and aggregates. Diagrams should be neat, and foreign keys clearly labelled.
针对 CCEA 试卷，要重点练习根据场景设计关系数据库、规范化到3NF，并编写带有连接和聚合的 SQL 查询。图表要整洁，外键要清晰标注。
For IB, the database topic often appears in Paper 2 or the IA. Emphasise understanding of ERD notation, the transformation from ERD to tables, and the social and ethical issues of data collection. Command terms like “explain,” “compare,” or “evaluate” dictate the depth of your answer.
IB 考试中数据库常出现在卷2或内部评估中，强调对 ERD 符号的理解、ERD 到表的转换以及数据收集的社会与伦理问题。“解释”“比较”“评价”等指令词决定了答案的深度。
Always check your normalisation steps: remove repeating groups for 1NF, partial dependencies for 2NF, and transitive dependencies for 3NF. In SQL, test your logic mentally – would the query return the expected rows? Practice with past papers and timed conditions.
务必核对规范化步骤：消除重复组得到1NF，消除部分依赖得到2NF，消除传递依赖得到3NF。对 SQL，脑中运行一下逻辑，看查询是否返回预期的行。多做历年真题并计时练习。
Published by TutorHao | Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导