Database Key Concepts for IB & CIE Computer Science | IB CIE 计算机：数据库考点精讲

📚 Database Key Concepts for IB & CIE Computer Science | IB CIE 计算机：数据库考点精讲

Databases form the backbone of nearly every modern software system, and a solid understanding of relational database theory, SQL, normalisation, and transaction management is essential for success in both the IB and CIE Computer Science curricula. This article distils the key concepts you must master, from flat-file vs relational models to ACID properties, with a focus on practical examination skills such as writing SQL queries and normalising tables to third normal form.

数据库几乎是所有现代软件系统的核心，牢固掌握关系数据库理论、SQL、规范化以及事务管理对于在 IB 和 CIE 计算机科学课程中取得成功至关重要。本文提炼了必须掌握的关键概念，从平面文件与关系模型对比到 ACID 特性，重点培养实用的考试技巧，例如编写 SQL 查询和将表规范化到第三范式。

1. Data, Information and the Need for Databases | 数据、信息与数据库的必要性

At the most fundamental level, data represents raw, unprocessed facts and figures, such as a student’s ID number or a temperature reading. Information emerges when data is processed, organised, and placed into a meaningful context—for instance, generating a report that shows the average temperature for a particular month. A database is a structured collection of related data, designed to facilitate efficient storage, retrieval, and management of information. Without databases, redundant data would proliferate, leading to inconsistencies, wasted storage, and difficulty in maintaining data integrity.

在最基础的层面上，数据代表原始的、未经处理的事实和数字，例如学生学号或温度读数。当数据被处理、组织并置于有意义的上下文中时，就产生了信息——例如生成一份显示某个月份平均温度的报告。数据库是相关数据的结构化集合，旨在方便高效地存储、检索和管理信息。没有数据库，冗余数据将泛滥，导致不一致、存储浪费以及难以维护数据完整性。

Traditional file-based approaches often suffer from several limitations: data is isolated in separate files, the same piece of information may be duplicated across multiple records (data redundancy), and any change must be manually propagated to every copy, which can easily result in data inconsistency. A centralised database management system (DBMS) overcomes these issues by providing a single, controlled repository where all data can be shared among authorised users and applications.

传统的基于文件的方法常常存在几个局限：数据被隔离在单独的文件中，同一信息可能在多条记录中重复（数据冗余），任何更改都必须手动传播到每个副本，这很容易导致数据不一致。集中式数据库管理系统（DBMS）通过提供一个单一的、受控的仓库克服了这些问题，所有数据可以在授权用户和应用程序之间共享。

2. Flat-File vs Relational Databases | 平面文件数据库与关系数据库

A flat-file database stores all data in a single table, much like a spreadsheet. For trivial applications this may suffice, but for any complex scenario involving many entities, the flat-file model breaks down quickly. If a school tried to store pupil details alongside exam results and club memberships all in one flat table, the same pupil name and address would be repeated for every exam entry, causing immense redundancy and opening the door to anomalies during updates and deletions.

平面文件数据库将所有数据存储在单个表中，就像电子表格一样。对于简单的应用这可能足够，但对于涉及多个实体的任何复杂场景，平面文件模型很快就会失效。如果一所学校尝试在一个平面表中同时存储学生详细信息、考试成绩和社团成员，那么每一条考试记录都会重复学生的姓名和地址，造成巨大的冗余，并在更新和删除时带来异常。

The relational model, proposed by E.F. Codd, resolves these problems by organising data into multiple related tables (relations). Each table represents one entity type, and tables are linked through primary and foreign keys. This separation minimises redundancy and allows complex queries across tables using JOIN operations. For both IB and CIE syllabuses, you are expected to understand why the relational approach is superior and to be able to identify and justify the links between tables.

由 E.F. Codd 提出的关系模型通过将数据组织到多个相关的表（关系）中解决了这些问题。每个表代表一种实体类型，表之间通过主键和外键连接。这种分离最大限度地减少了冗余，并允许使用 JOIN 操作跨表进行复杂查询。无论是 IB 还是 CIE 大纲，都要求你理解为什么关系方法更优越，并能识别和证明表之间的链接。

3. Primary Keys, Foreign Keys, and Referential Integrity | 主键、外键与参照完整性

A primary key is a field (or combination of fields) that uniquely identifies each record in a table. No two rows can share the same primary key value, and it may never be null. Common choices include automatically generated integer IDs or natural keys like a passport number. In exams, you must be able to choose a suitable primary key and explain why it guarantees uniqueness. A foreign key is a field in one table that references the primary key of another table. This linkage enables the DBMS to enforce referential integrity: every foreign key value must either match an existing primary key in the referenced table or be null, thereby preventing orphaned references.

主键是唯一标识表中每条记录的一个字段（或字段组合）。没有两行可以共享相同的主键值，而且主键绝不能为空。常见的选择包括自动生成的整数 ID 或像护照号码这样的自然键。在考试中，你必须能够选择合适的主键并解释它为何能保证唯一性。外键是一个表中的字段，它引用另一表的主键。这种链接使得 DBMS 能够强制参照完整性：每个外键值必须要么与引用表中存在的主键匹配，要么为空，从而防止孤立引用。

Consider a database with a Student table (primary key StudentID) and an Enrolment table. The Enrolment table contains StudentID as a foreign key. If we attempt to insert an enrolment for StudentID 999 and no student with that ID exists, the DBMS will reject the operation. Similarly, deleting a student who still has enrolment records would break referential integrity unless cascade delete or a similar strategy is implemented.

考虑一个包含学生表（主键 StudentID）和选课表的数据库。选课表包含 StudentID 作为外键。如果我们试图插入一个 StudentID 为 999 的选课记录，但没有该 ID 的学生存在，DBMS 将拒绝该操作。同样，删除一个仍有选课记录的学生会破坏参照完整性，除非实现了级联删除或类似策略。

4. Entity-Relationship Diagrams and Relationships | 实体关系图与关系类型

Entity-Relationship (ER) diagrams provide a visual blueprint of a database’s logical structure. Entities are typically drawn as rectangles, attributes as ovals, and relationships as diamond shapes (or simply labelled lines, depending on the notation used). For examinations, you need to identify the types of relationships: one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N). A classic example is a library database: a book can be borrowed by many members, and a member can borrow many books, forming an M:N relationship that must be resolved by introducing a linking (junction) table, such as Loan.

实体关系图（ER 图）提供了数据库逻辑结构的可视化蓝图。实体通常绘制为矩形，属性为椭圆，关系为菱形（或根据使用的符号体系仅为标注的线条）。在考试中，你需要识别关系的类型：一对一（1:1）、一对多（1:M）和多对多（M:N）。一个经典的例子是图书馆数据库：一本书可以被许多会员借阅，而一个会员可以借阅多本书，形成 M:N 关系，必须通过引入链接（联结）表（例如 Loan）来解决。

When drawing or interpreting ER diagrams, pay attention to cardinality and participation constraints. For CIE papers, you may be asked to produce a logical entity-relationship diagram or to explain how a many-to-many relationship is implemented in a relational schema. IB students should be familiar with creating simple ER diagrams and mapping them into a set of normalised tables.

在绘制或解释 ER 图时，要注意基数和参与约束。对于 CIE 试卷，你可能被要求产生一个逻辑实体关系图，或解释如何在关系模式中实现多对多关系。IB 学生应熟悉创建简单的 ER 图并将其映射为一组规范化表。

5. Normalisation: First, Second, and Third Normal Form | 规范化：第一、第二和第三范式

Normalisation is a systematic process used to eliminate data redundancy and undesirable characteristics like insertion, update, and deletion anomalies. The process involves decomposing a large, unnormalised table into smaller, well-structured relations. The three normal forms required for both IB and CIE examinations are 1NF, 2NF, and 3NF.

规范化是一个系统化的过程，用于消除数据冗余以及诸如插入、更新和删除异常之类的不良特性。该过程涉及将一个大的、未规范化的表分解为更小、结构良好的关系。IB 和 CIE 考试都要求掌握 1NF、2NF 和 3NF 这三个范式。

Normal Form	Requirement	示例要求
1NF	All attributes contain atomic values; no repeating groups.	所有属性包含原子值；无重复组。
2NF	All non-key attributes are fully functionally dependent on the entire primary key (no partial dependencies).	所有非键属性完全函数依赖于整个主键（无部分依赖）。
3NF	No transitive dependencies: non-key attributes depend only on the key, not on other non-key attributes.	无传递依赖：非键属性仅依赖于键，而不依赖于其他非键属性。

To illustrate, imagine an unnormalised Orders table containing OrderID, ProductID, ProductName, CustomerID, CustomerName, and OrderDate. After 1NF, we ensure atomicity and split repeating groups. For 2NF, if there is a composite primary key (OrderID, ProductID), we remove attributes that depend on only part of the key (e.g. ProductName depends only on ProductID). For 3NF, we eliminate transitive dependencies (e.g. CustomerName depends on CustomerID, not directly on OrderID). The result would be separate Customer, Product, OrderHeader, and OrderDetail tables—a clean, anomaly‑free design.

举例说明，设想一个未规范化的订单表，包含 OrderID、ProductID、ProductName、CustomerID、CustomerName 和 OrderDate。经过 1NF 后，我们确保原子性并拆分重复组。对于 2NF，如果存在复合主键（OrderID、ProductID），我们移除仅依赖于部分键的属性（例如 ProductName 仅依赖于 ProductID）。对于 3NF，我们消除传递依赖（例如 CustomerName 依赖于 CustomerID，而非直接依赖于 OrderID）。结果将是独立的 Customer、Product、OrderHeader 和 OrderDetail 表——这是一个干净、无异常的设计。

6. SQL: Data Definition and Data Manipulation | SQL：数据定义与数据操作

SQL (Structured Query Language) is the standard language for interacting with relational databases, and it is divided into two main sublanguages: DDL (Data Definition Language) and DML (Data Manipulation Language). DDL commands include CREATE, ALTER, and DROP, which define and modify the database schema. DML commands—SELECT, INSERT, UPDATE, DELETE—are used to query and alter the data stored in tables. Both IB and CIE exams expect you to write syntactically correct SQL statements, often against a given schema.

SQL（结构化查询语言）是与关系数据库交互的标准语言，分为两个主要子语言：DDL（数据定义语言）和 DML（数据操作语言）。DDL 命令包括 CREATE、ALTER 和 DROP，用于定义和修改数据库模式。DML 命令——SELECT、INSERT、UPDATE、DELETE——用于查询和更改存储在表中的数据。IB 和 CIE 考试都期望你根据给定的模式编写语法正确的 SQL 语句。

CREATE TABLE Student (StudentID INTEGER PRIMARY KEY, Name VARCHAR(50) NOT NULL);

This DDL statement creates a new table with a primary key constraint and a non‑null constraint. You must be able to interpret such statements and identify the data types and constraints used.

这个 DDL 语句创建一个新表，带有主键约束和非空约束。你必须能够解释这类语句，并识别所使用的数据类型和约束。

SELECT Student.Name, Course.Title FROM Student INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID INNER JOIN Course ON Enrolment.CourseID = Course.CourseID WHERE Enrolment.Grade > 80;

This query demonstrates an inner join across three tables, filtering for high‑grade enrolments. Examiners are particularly keen on correct use of JOIN syntax, aliasing, and the appropriate placement of WHERE and GROUP BY clauses. Remember also to use DISTINCT to eliminate duplicates and ORDER BY to sort results.

此查询展示了跨三个表的内连接，并过滤了高分的选课记录。考官尤其看重 JOIN 语法的正确使用、别名以及 WHERE 和 GROUP BY 子句的适当位置。还要记得使用 DISTINCT 消除重复，以及使用 ORDER BY 对结果进行排序。

7. Aggregate Functions, GROUP BY, and HAVING | 聚合函数、GROUP BY 与 HAVING

SQL provides powerful aggregate functions—COUNT, SUM, AVG, MAX, and MIN—that operate on sets of rows. When you need to summarise data by categories, you combine these functions with a GROUP BY clause. The HAVING clause is then used to filter groups, much as WHERE filters individual rows, but applied after aggregation. CIE Paper 4 and IB Paper 2 often include questions requiring a query that groups data and applies a condition on the aggregated result.

SQL 提供了强大的聚合函数——COUNT、SUM、AVG、MAX 和 MIN——它们对行集进行操作。当你需要按类别汇总数据时，可以将这些函数与 GROUP BY 子句结合使用。然后使用 HAVING 子句来过滤分组，就像 WHERE 过滤单行那样，但 HAVING 应用于聚合之后。CIE 试卷 4 和 IB 试卷 2 经常包含要求对数据分组并对聚合结果应用条件的查询题目。

SELECT Department, COUNT(*) AS EmployeeCount FROM Employee GROUP BY Department HAVING COUNT(*) > 5;

This query counts employees in each department and returns only those departments with more than five employees. Note the logical order of clause execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Understanding this order prevents many common errors.

此查询计算每个部门的员工数量，并仅返回员工数超过五人的部门。注意子句执行的逻辑顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。理解这个顺序可以防止许多常见错误。

8. Indexing and Performance | 索引与性能

An index is a data structure (often a B‑tree) associated with a table that accelerates data retrieval operations on one or more columns. Without an index, the DBMS must perform a full table scan, reading every row to locate the required data. With an index, the system can directly navigate to the relevant records, much like using the index at the back of a book. While indexes dramatically speed up SELECT queries, they also slow down INSERT, UPDATE, and DELETE operations because the index itself must be maintained. The choice of which columns to index is therefore a critical design decision—primary keys are indexed automatically, but frequently queried foreign keys and filter columns are also strong candidates.

索引是一种与表关联的数据结构（通常是 B 树），可加速对一个或多个列的数据检索操作。没有索引，DBMS 必须执行全表扫描，读取每一行来定位所需数据。有了索引，系统可以直接导航到相关记录，就像使用书后的索引一样。尽管索引显著加快了 SELECT 查询速度，但也会减慢 INSERT、UPDATE 和 DELETE 操作，因为索引本身也必须维护。因此，选择哪些列建立索引是一个关键的设计决策——主键会被自动索引，但经常被查询的外键和过滤列也是强有力的候选。

Both IB and CIE syllabi expect you to explain why indexing improves query performance and to recognise the trade‑off involved. In paper questions, you might be asked to suggest suitable indexes for a given query pattern or to explain why an index is not beneficial for a table that is heavily written but rarely read.

IB 和 CIE 大纲都期望你解释为什么索引能提高查询性能，并认识到其中的权衡。在试卷问题中，你可能会被要求为给定的查询模式建议合适的索引，或解释为什么索引对于写入频繁但读取很少的表没有益处。

9. Transactions and ACID Properties | 事务与 ACID 特性

A transaction is a sequence of database operations that are treated as a single logical unit of work. The classic example is transferring money between bank accounts: deduct the amount from one account and credit it to another. Both operations must either complete successfully together or not happen at all. To guarantee reliability, transactions adhere to ACID properties:

事务是被视为单个逻辑工作单元的一系列数据库操作。经典示例是在银行账户之间转账：从一个账户扣除金额并记入另一个账户。这两个操作必须要么一起成功完成，要么根本不发生。为保证可靠性，事务遵循 ACID 特性：

Atomicity – All or nothing: the transaction either completes in its entirety or is rolled back. / 原子性——全有或全无：事务要么完整执行，要么回滚。
Consistency – The transaction moves the database from one valid state to another, preserving all defined constraints. / 一致性——事务将数据库从一个有效状态转变为另一个有效状态，保持所有定义的约束。
Isolation – Concurrent transactions appear as if they were executed serially, preventing interference. / 隔离性——并发事务表现得如同顺序执行，防止相互干扰。
Durability – Once committed, the results survive system failures and are permanently recorded. / 持久性——一旦提交，结果在系统故障后仍能保存，并被永久记录。

IB Computer Science emphasises the role of transactions in multi‑user environments and the need for concurrency control. CIE expects you to be able to describe each ACID property with a suitable example and to understand how commit and rollback commands work within a transaction block.

IB 计算机科学强调事务在多用户环境中的作用以及并发控制的必要性。CIE 期望你能够用适当的例子描述每个 ACID 特性，并理解 commit 和 rollback 命令如何在事务块中工作。

10. Concurrency Control and Deadlock | 并发控制与死锁

When multiple users access and modify the same data simultaneously, the DBMS must employ locking mechanisms to prevent lost updates, dirty reads, and other concurrency problems. Shared locks allow reading, while exclusive locks are required for writing. A common problem is deadlock, where two or more transactions are each waiting for the other to release a lock, causing a standstill. Deadlocks are typically resolved by the DBMS through timeout and transaction rollback.

当多个用户同时访问和修改相同数据时，DBMS 必须采用锁定机制来防止丢失更新、脏读和其他并发问题。共享锁允许读取，而独占锁则用于写入。一个常见的问题是死锁，即两个或多个事务各自等待对方释放锁，导致停滞。死锁通常由 DBMS 通过超时和事务回滚来解决。

You should be able to draw and interpret resource allocation graphs and to identify deadlock cycles. For IB, understanding the phantom deadlock and the difference between optimistic and pessimistic locking is often required. CIE may ask you to explain why record locking is necessary and to propose strategies to reduce deadlock risk, such as always accessing resources in the same order.

你应该能够绘制和解释资源分配图，并识别死锁循环。对于 IB，经常要求理解幻象死锁以及乐观锁和悲观锁之间的区别。CIE 可能会要求你解释为什么记录锁定是必要的，并提出减少死锁风险的策略，例如始终以相同顺序访问资源。

11. Data Security and Database Administration | 数据安全与数据库管理

Data security in a database context encompasses protecting data against unauthorised access, modification, and destruction. The DBMS provides a privilege system based on GRANT and REVOKE commands, enabling the database administrator (DBA) to assign specific rights (SELECT, INSERT, UPDATE, DELETE) to users or roles. Encryption, both at rest and in transit, provides an additional layer of protection. Backups and recovery strategies—full, differential, and incremental—are critical for disaster recovery and form part of the DBA’s responsibility.

在数据库上下文中，数据安全包括保护数据免受未经授权的访问、修改和销毁。DBMS 提供基于 GRANT 和 REVOKE 命令的权限系统，使数据库管理员（DBA）能够向用户或角色分配特定的权利（SELECT、INSERT、UPDATE、DELETE）。静态数据和传输中数据的加密提供了额外的保护层。备份和恢复策略——完整备份、差异备份和增量备份——对于灾难恢复至关重要，也是 DBA 职责的一部分。

IB’s syllabus may explore the social and ethical implications of databases, such as privacy concerns when personal data is stored without consent. CIE places more emphasis on the practical commands used to manage access rights and on the distinction between logical and physical backup methods. Both require a broad awareness of why security is not merely a technical afterthought but a fundamental design consideration.

IB 的大纲可能会探讨数据库的社会和伦理影响，例如未经同意存储个人数据时的隐私问题。CIE 更强调用于管理访问权限的实际命令，以及逻辑备份与物理备份方法的区别。两者都要求广泛认识到为什么安全性不仅仅是一个技术上的事后考虑，而是基本的设计考量。

12. Distributed Databases and Big Data Concepts | 分布式数据库与大数据概念

As data volumes explode, the traditional centralised database often gives way to distributed architectures. A distributed database spreads data across multiple networked sites, which may be replicated or partitioned (sharded). While this increases availability and fault tolerance, it introduces challenges such as maintaining consistency across replicas and handling network partition failures—summarised by the CAP theorem (Consistency, Availability, Partition tolerance). IB students particularly encounter these ideas in the context of web‑based applications and cloud storage.

随着数据量爆炸式增长，传统的集中式数据库常常让位于分布式架构。分布式数据库将数据分散到多个联网站点，这些站点上的数据可能被复制或分区（分片）。虽然这提高了可用性和容错能力，但也带来了挑战，例如维护副本间的一致性和处理网络分区故障——这由 CAP 定理（一致性、可用性、分区容错性）总结。IB 学生尤其在基于 Web 的应用程序和云存储的背景下遇到这些想法。

Relatedly, the term Big Data refers to datasets so large or complex that conventional relational databases struggle to process them efficiently. Technologies such as Hadoop and NoSQL databases (document stores, key‑value stores, graph databases) emerge as alternatives. You need to understand the characteristics of Big Data (volume, velocity, variety) and the trade‑offs made by NoSQL systems, such as relaxing immediate consistency to achieve horizontal scalability.

与此相关，术语大数据指的是那些规模巨大或复杂到传统关系数据库难以有效处理的数据集。Hadoop 和 NoSQL 数据库（文档存储、键值存储、图数据库）等技术应运而生。你需要了解大数据的特征（大量、高速、多样）以及 NoSQL 系统所做的权衡，例如放宽即时一致性以实现水平可扩展性。

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

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

Database Key Concepts for IB & CIE Computer Science | IB CIE 计算机：数据库 考点精讲