IB Computer Science: Database Exam Essentials | IB 计算机：数据库考点精讲

📚 IB Computer Science: Database Exam Essentials | IB 计算机：数据库考点精讲

Databases are at the heart of almost every modern information system. For IB Computer Science students, understanding how data is structured, queried, and kept secure is not just an exam requirement – it is a foundational skill for any future software engineer or data professional. This revision guide covers the core topics you must master: relational models, keys, normalisation, SQL, transactions, and more.

数据库是几乎所有现代信息系统的核心。对于 IB 计算机科学的学生来说，理解数据的结构、查询和安全性不仅是考试要求，也是任何未来软件工程师或数据专家的基础技能。这份复习指南涵盖了必须掌握的核心主题：关系模型、键、规范化、SQL、事务等。

1. Introduction to Databases | 数据库简介

A database is an organised collection of structured data, typically stored electronically in a computer system. Unlike a spreadsheet, a database can manage huge volumes of information while allowing multiple users to access and update data concurrently without conflict. The software that manages databases is called a Database Management System (DBMS). Examples include MySQL, PostgreSQL, and Oracle.

数据库是有组织地存储的结构化数据集合，通常以电子方式保存在计算机系统中。与电子表格不同，数据库可以管理海量信息，同时允许多个用户同时访问和更新数据而不会发生冲突。管理数据库的软件称为数据库管理系统 (DBMS)，例如 MySQL、PostgreSQL 和 Oracle。

In the IB syllabus, databases appear mainly under Topic 3 (Networks and Data) and the Option topic ‘Databases’. You are expected to understand both the theoretical underpinnings and practical SQL skills. The exam may ask you to draw an Entity-Relationship Diagram, normalise a table to 3NF, write a complex query, or explain the ACID properties of a transaction.

在 IB 教学大纲中，数据库主要出现在主题 3（网络与数据）和选修主题“数据库”中。既要求掌握理论基础，也要求具备实践 SQL 技能。考试可能要求绘制实体关系图、将表规范化到 3NF、编写复杂查询，或解释事务的 ACID 特性。

2. Relational Database Concepts | 关系数据库概念

The relational model organises data into tables (relations). Each table consists of rows (records or tuples) and columns (attributes or fields). Every column has a defined data type, such as INTEGER, VARCHAR, DATE, or BOOLEAN. The power of the relational model lies in its ability to link tables through relationships, avoiding data duplication.

关系模型将数据组织成表（关系）。每张表由行（记录或元组）和列（属性或字段）组成。每个列都有定义的数据类型，例如 INTEGER、VARCHAR、DATE 或 BOOLEAN。关系模型的强大之处在于能通过关系链接表，避免数据重复。

A relation must satisfy certain properties: column names are unique within a table, the order of rows does not matter, and each cell holds a single atomic value. These principles help maintain data integrity and make querying predictable. In IB exams, you might be shown a flat file and asked to explain why a relational structure is preferable – always emphasise reduced redundancy and improved consistency.

关系必须满足某些属性：表中列名唯一、行的顺序无关紧要、每个单元格保存单一原子值。这些原则有助于维护数据完整性并使查询可预测。在 IB 考试中，可能会给一个平面文件，要求解释为什么关系结构更好——务必强调减少冗余和提高一致性。

3. Keys: Primary, Foreign, and Composite | 键：主键、外键和复合键

Keys are fundamental to maintaining row uniqueness and relationships. A primary key is a column (or combination of columns) that uniquely identifies each record in a table. It must be unique and not null. When a primary key is made up of two or more columns, it is called a composite key.

键对于保持行的唯一性和关系至关重要。主键是唯一标识表中每条记录的一列（或多列组合）。它必须唯一且不为空。当主键由两列或更多列组成时，称为复合键。

A foreign key is a column in one table that refers to the primary key of another table. This creates a link between the two tables. For example, a ‘StudentID’ in an Enrolment table might reference the primary key of the Student table. Foreign keys enforce referential integrity: you cannot insert a value in the foreign key column unless it already exists in the referenced primary key column.

外键是一个表中的列，它引用另一张表的主键。这在两张表之间创建了链接。例如，选课表中的 ‘StudentID’ 可能引用学生表的主键。外键强制引用完整性：不能在外部键列中插入值，除非该值已存在于被引用的主键列中。

Candidates often confuse candidate keys and alternate keys: a candidate key is any column or combination that could serve as a primary key, while an alternate key is a candidate key not chosen as the primary key. Be ready to identify them from a given schema.

考生常混淆候选键和备用键：候选键是任何可充当主键的列或组合，而备用键是未选作主键的候选键。要准备好从给定模式中识别它们。

4. Entity-Relationship Diagrams (ERD) | 实体关系图

An ERD is a graphical way to represent the logical structure of a database. Entities (like Student, Course, Teacher) are drawn as rectangles. Attributes are listed inside ellipses connected to their entity, while relationships are shown as diamonds linking entities. Cardinality describes how many instances of one entity can relate to another: one-to-one (1:1), one-to-many (1:M), or many-to-many (M:N).

实体关系图是表示数据库逻辑结构的一种图形方式。实体（如学生、课程、教师）用矩形绘制。属性列在连接到实体的椭圆中，关系则用连接实体的菱形表示。基数描述一个实体的多少个实例可以与另一个实体关联：一对一 (1:1)、一对多 (1:M) 或多对多 (M:N)。

In the IB exam, you may be asked to draw an ERD from a scenario. Use clear notation and always resolve M:N relationships by introducing an associative entity (bridge table). For example, a Student-Enrols-Course relationship with M:N should become two 1:M relationships linked by an Enrolment entity containing foreign keys and possibly additional attributes like enrolment date.

在 IB 考试中，可能要求根据情景绘制 ERD。使用清晰的符号，并始终通过引入关联实体（桥接表）来解决 M:N 关系。例如，M:N 的 Student-Enrols-Course 关系应变成两个 1:M 关系，并通过包含外键和可能还有注册日期等属性的 Enrolment 实体连接起来。

5. Normalisation: 1NF, 2NF, 3NF | 规范化：1NF, 2NF, 3NF

Normalisation is a step-by-step process to eliminate data redundancy and avoid update anomalies. The first three normal forms are routinely tested:

1NF (First Normal Form): All attributes must contain atomic values – no repeating groups or arrays. Each intersection of row and column holds exactly one value.
2NF (Second Normal Form): Must be in 1NF and have no partial dependencies. A non-key attribute must depend on the whole of a composite primary key, not just part of it.
3NF (Third Normal Form): Must be in 2NF and have no transitive dependencies. Non-key attributes must depend only on the primary key, not on another non-key attribute.

规范化是一个逐步消除数据冗余和避免更新异常的过程。前三范式是常规考查内容：

1NF（第一范式）：所有属性必须包含原子值——没有重复组或数组。行与列的每个交点恰好保存一个值。
2NF（第二范式）：必须满足 1NF 且没有部分依赖。非键属性必须依赖于复合主键的整体，而不仅仅是其中一部分。
3NF（第三范式）：必须满足 2NF 且没有传递依赖。非键属性必须只依赖于主键，而不依赖于另一个非键属性。

A common exam task is to take an unnormalised table full of redundancy and transform it stepwise into 3NF, clearly stating which fields are moved to new tables and what the keys become. Always show the resulting tables and underline primary keys.

一种常见的考试任务是拿一个充满冗余的非规范化表格，将其逐步转换成 3NF，清楚地说明哪些字段移到新表，以及键变成什么。始终展示结果表，并在主键底下画线。

6. SQL Basics: SELECT, FROM, WHERE | SQL 基础：SELECT, FROM, WHERE

SQL (Structured Query Language) is the standard language for interacting with relational databases. The most common operation is data retrieval using the SELECT statement. The basic syntax is:

SELECT column1, column2 FROM table WHERE condition;

SQL（结构化查询语言）是与关系数据库交互的标准语言。最常见的操作是使用 SELECT 语句进行数据检索。基本语法如下：

SELECT column1, column2 FROM table WHERE condition;

You can use operators like =, <>, <, >, AND, OR, NOT, BETWEEN, LIKE, and IN. The LIKE operator supports wildcards: % for any sequence of characters, _ for a single character. For instance, SELECT * FROM Student WHERE Name LIKE 'A%' returns all students whose name starts with ‘A’.

可以使用 =、<>、<、>、AND、OR、NOT、BETWEEN、LIKE 和 IN 等运算符。LIKE 运算符支持通配符：% 表示任意字符序列，_ 表示单个字符。例如，SELECT * FROM Student WHERE Name LIKE 'A%' 返回所有名字以 ‘A’ 开头的学生。

The result of a SELECT is not a permanent table but a virtual result set. You can sort the output with ORDER BY (ASC or DESC) and limit the number of rows using keywords such as LIMIT (in MySQL) or FETCH FIRST n ROWS ONLY (in standard SQL).

SELECT 的结果不是永久表，而是一个虚拟结果集。可以用 ORDER BY （ASC 或 DESC）排序，并用 LIMIT （MySQL 中）或 FETCH FIRST n ROWS ONLY （标准 SQL 中）限制返回的行数。

7. SQL Joins: INNER, LEFT, RIGHT, FULL | SQL 连接：内连接、左连接、右连接、全连接

Joins are used to combine rows from two or more tables based on a related column. The most important type is INNER JOIN, which returns only rows where there is a match in both tables. Syntax:

SELECT * FROM A INNER JOIN B ON A.key = B.key;

连接用于根据相关列将两个或多个表中的行组合起来。最重要的类型是内连接，它只返回两个表中都有匹配的行。语法：

SELECT * FROM A INNER JOIN B ON A.key = B.key;

LEFT (OUTER) JOIN returns all rows from the left table plus matched rows from the right; if no match, NULL values appear for right-side columns. RIGHT JOIN is the mirror opposite, while FULL OUTER JOIN returns rows when there is a match in either table – but note that not all DBMS support FULL OUTER JOIN (MySQL does not).

左（外）连接返回左表的所有行加上右表的匹配行；如果没有匹配，右表列显示 NULL 值。右连接是其镜像，而全外连接在任一表有匹配时都返回行——但注意并非所有 DBMS 都支持全外连接（MySQL 不支持）。

Exam tip: When asked to write a query that lists ‘all students and their course titles, even if they are not enrolled in any course’, you need a LEFT JOIN from Student to Enrolment, and then another JOIN to Course. Carefully trace the path through foreign keys.

考试提示：当要求编写一个查询来列出“所有学生及其课程名称，即使他们没有选修任何课程”时，需要从 Student 到 Enrolment 进行左连接，然后再连接到 Course。要仔细通过外键跟踪路径。

8. Data Manipulation: INSERT, UPDATE, DELETE | 数据操纵：INSERT、UPDATE、DELETE

In addition to reading data, SQL provides commands to modify the contents of tables. The INSERT statement adds new rows:

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

除了读取数据，SQL 还提供修改表内容的命令。INSERT 语句添加新行：

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

UPDATE modifies existing rows and almost always uses a WHERE clause to target specific records (omitting WHERE would update every row). DELETE removes rows, and again a WHERE clause is essential to avoid wiping out the entire table. Both commands should be used with caution, especially in production databases.

UPDATE 修改现有行，并且几乎总是使用 WHERE 子句来定位特定记录（省略 WHERE 会更新每一行）。DELETE 删除行，同样，WHERE 子句对于避免清空整个表至关重要。这两个命令都应谨慎使用，尤其是在生产数据库中。

The IB expects you to understand the implications of these commands on data integrity. For instance, if you delete a student, what happens to their related enrolment records? This depends on the referential action defined: CASCADE, SET NULL, or RESTRICT. Make sure to mention these in your answers when analysing deletion scenarios.

IB 期望你理解这些命令对数据完整性的影响。例如，如果删除一个学生，他们的相关选课记录会怎样？这取决于定义的引用操作：级联删除、设为空值或限制删除。在分析删除情景时，务必在答案中提及这些操作。

9. Database Transactions and ACID | 数据库事务与 ACID

A transaction is a sequence of database operations that must be performed as a single logical unit of work – either all succeed, or none do. The classic example is transferring money from one bank account to another: debit one account and credit the other. If the system crashes after the debit but before the credit, the data becomes inconsistent.

事务是一种数据库操作序列，必须作为单一逻辑工作单元执行——要么全部成功，要么全部不做。经典示例是从一个银行账户向另一个账户转账：借记一个账户并贷记另一个。如果系统在借记之后、贷记之前崩溃，数据就会变得不一致。

The ACID properties guarantee reliable processing of transactions:

Atomicity: All operations in the transaction complete successfully, or the entire transaction is rolled back.
Consistency: A transaction brings the database from one valid state to another, preserving all defined rules and constraints.
Isolation: Concurrent transactions do not interfere with each other; intermediate states are invisible to other transactions.
Durability: Once a transaction is committed, its changes persist even in the event of a system failure.

ACID 属性保证事务的可靠处理：

原子性 (Atomicity)：事务中的所有操作要么全部成功完成，要么整个事务回滚。
一致性 (Consistency)：事务将数据库从一个有效状态带到另一个有效状态，保留所有定义的规则和约束。
隔离性 (Isolation)：并发事务不会相互干扰；中间状态对其他事务不可见。
持久性 (Durability)：一旦事务提交，其更改即使在系统故障的情况下也会持久保存。

In an IB answer, you might be presented with a banking scenario and asked to explain which ACID properties are most relevant. Always connect each property to the scenario, using phrases like “Atomicity ensures that if the debit fails, the credit does not occur.”

在 IB 答案中，可能会给出一个银行情景，要求解释哪些 ACID 特性最相关。始终将每个特性与情景联系起来，使用诸如“原子性确保如果借记失败，贷记就不会发生”之类的表述。

10. Indexing and Performance | 索引与性能

As tables grow, query performance can degrade. An index is a data structure (typically a B-tree) that allows the DBMS to locate rows more quickly, similar to a book index. Indexes can be created on one or more columns. While indexes speed up SELECT queries dramatically, they slow down INSERT, UPDATE, and DELETE operations because the index must be maintained.

随着表的增长，查询性能可能下降。索引是一种数据结构（通常是 B 树），使 DBMS 能更快地定位行，类似于书籍的索引。索引可以创建在一列或多列上。虽然索引极大加快了 SELECT 查询的速度，但它们会减慢 INSERT、UPDATE 和 DELETE 操作，因为必须维护索引。

Primary keys are automatically indexed, but you may need to create additional indexes on frequently queried foreign keys or WHERE clause columns. In the IB exam, you need only understand the concept and trade-offs, not the implementation details. A typical question might ask: “Explain why adding an index on a customer’s last name column improves search performance.”

主键会自动被索引，但可能需要在经常查询的外键或 WHERE 子句列上创建额外的索引。在 IB 考试中，只需理解概念和权衡，不需要实现细节。一个典型的问题可能是：“解释为什么在客户姓氏列上添加索引可以提高搜索性能。”

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

Security involves protecting data from unauthorized access, while integrity ensures data is accurate and consistent. Common security measures include authentication (username/password), access rights (granting SELECT, INSERT privileges to specific users), and encryption of sensitive data. A view can be used to restrict access to a subset of columns or rows, providing an extra layer of security.

安全性涉及保护数据免受未经授权的访问，而完整性确保数据准确一致。常见的安全措施包括身份验证（用户名/密码）、访问权限（将 SELECT、INSERT 权限授予特定用户）以及对敏感数据进行加密。视图可用于限制对列或行子集的访问，提供额外的安全层。

Data integrity is enforced through constraints: primary key (unique + not null), foreign key (referential integrity), NOT NULL, UNIQUE, CHECK, and data type restrictions. The DBMS rejects any operation that would violate these constraints. In the exam, you might be asked to write a CREATE TABLE statement that includes relevant integrity constraints. For example:

CREATE TABLE Employee (EmpID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Salary DECIMAL(8,2) CHECK (Salary > 0));

数据完整性通过约束来实施：主键（唯一 + 非空）、外键（引用完整性）、NOT NULL、UNIQUE、CHECK 以及数据类型限制。DBMS 会拒绝任何违反这些约束的操作。在考试中，可能要求编写包含相关完整性约束的 CREATE TABLE 语句。例如：

CREATE TABLE Employee (EmpID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Salary DECIMAL(8,2) CHECK (Salary > 0));

12. Exam Tips and Common Mistakes | 应试技巧与常见错误

Avoid these pitfalls: confusing the terms ‘entity’ and ‘table’ (entity is logical, table is physical); forgetting to underline primary keys in normalised relations; using incorrect JOIN types for ‘all of this, possibly none of that’ scenarios; writing DELETE without WHERE; and mixing up 2NF and 3NF by not recognising partial versus transitive dependencies.

避免这些陷阱：混淆“实体”和“表”这两个术语（实体是逻辑的，表是物理的）；在规范化关系中忘记在主键下面画线；针对“所有这些东西，可能没有那些东西”的情景使用了错误的连接类型；不带 WHERE 编写 DELETE；以及由于未能识别部分依赖和传递依赖而混淆 2NF 和 3NF。

When drawing ERDs, neatly label cardinalities and always resolve M:N. In SQL questions, read the wording carefully: ‘all customers and their orders, if any’ signals a LEFT JOIN. When explaining ACID, don’t just list the properties – explain their meaning and consequences. Finally, normalisation questions often carry high marks; practice reducing large tables stepwise and generate the 3NF tables with clear key declarations.

在绘制 ERD 时，整齐地标注基数，并始终解决 M:N 关系。在 SQL 问题中，仔细阅读措辞：“所有客户及其订单（如果有）”表明需要左连接。在解释 ACID 时，不要仅仅列出特性——要解释它们的含义和后果。最后，规范化问题通常分值很高；练习逐步缩减大表，并生成带有清晰键声明的 3NF 表。

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

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

IB Computer Science: Database Exam Essentials | IB 计算机：数据库 考点精讲