📚 Database Essentials for IB & Edexcel Computer Science | 数据库考点精讲（IB/Edexcel 计算机）
In both IB Computer Science and Edexcel A‑Level Computer Science, databases form a core topic that bridges theoretical concepts with practical problem‑solving. This article distils the essential knowledge you need: from foundational terminology and relational models to ER diagrams, normalisation, SQL, and transaction management, all aligned with your syllabus requirements.
数据库是 IB 计算机科学和 Edexcel A‑Level 计算机课程的核心课题，它连接了理论概念与实际问题的解决。本文精讲必备知识点：从基本术语、关系模型到 ER 图、规范化、SQL 以及事务管理，完全贴合你的考纲要求。
1. What is a Database? | 什么是数据库？
A database is a structured collection of data that is stored and accessed electronically. Unlike a simple file‑based system, a database offers controlled redundancy, data consistency, and concurrent access by multiple users. Modern databases are managed by a Database Management System (DBMS), which acts as an interface between users/applications and the physical data.
数据库是以电子方式存储和访问的结构化数据集合。与简单的文件系统不同，数据库能控制冗余、保证数据一致性并支持多用户并发访问。现代数据库由数据库管理系统（DBMS）管理，该系统充当用户/应用程序与物理数据之间的接口。
The main functions of a DBMS include data definition (creating schemas), data manipulation (insert, update, delete), data retrieval (queries), and administration (security, backup, recovery). Popular DBMS examples are MySQL, PostgreSQL, Oracle, and SQLite.
DBMS 的主要功能包括数据定义（创建模式）、数据操作（插入、更新、删除）、数据检索（查询）以及管理（安全、备份、恢复）。常见的 DBMS 有 MySQL、PostgreSQL、Oracle 和 SQLite。
2. Key Terminology | 关键术语
Table / Relation – A collection of related data entries organised in rows and columns. In relational theory, a relation corresponds to a table.
中文：表/关系 — 按行和列组织的相关数据条目的集合。在关系理论中，关系对应一张表。
Record / Tuple – A single row in a table, representing one instance of an entity.
中文：记录/元组 — 表中的一行，代表实体的一个实例。
Field / Attribute – A column in a table, describing a property of the entity.
中文：字段/属性 — 表中的一列，描述实体的某个特性。
Primary Key – A field (or combination of fields) that uniquely identifies each record in a table. Must be unique and not null.
中文：主键 — 能唯一标识表中每条记录的一个字段（或字段组合）。必须唯一且非空。
Foreign Key – A field in one table that refers to the primary key in another table, establishing a link between them.
中文：外键 — 一张表中的字段，它引用另一张表的主键，从而建立两表之间的联系。
Composite Key – A primary key consisting of two or more attributes.
中文：复合键 — 由两个或多个属性组成的主键。
Candidate Key – An attribute or set of attributes that could serve as a primary key (unique and minimal).
中文：候选键 — 可以作为主键使用的属性或属性集（唯一且最小）。
3. The Relational Model & Schema | 关系模型与模式
The relational model, proposed by E.F. Codd, represents data as a set of relations (tables). Each relation has a name, a set of attributes with domains (data types), and a set of tuples. The schema is the logical structure that defines the tables, fields, data types, and constraints.
关系模型由 E.F. Codd 提出，将数据表示为一组关系（表）。每个关系有一个名称、一组带域（数据类型）的属性以及一组元组。模式是定义表、字段、数据类型和约束的逻辑结构。
Constraints are crucial: entity integrity (primary key cannot be null), referential integrity (foreign key values must match an existing primary key or be null), and domain constraints (values must belong to the specified domain). These rules maintain data accuracy and consistency.
约束至关重要：实体完整性（主键不能为空）、参照完整性（外键值必须匹配已有的主键或为空）以及域约束（值必须属于指定的域）。这些规则维持数据的准确性和一致性。
4. Entity‑Relationship (ER) Diagrams | 实体‑联系（ER）图
ER diagrams are a conceptual modelling tool used to design databases. They represent entities (rectangles), attributes (ovals), and relationships (diamonds). Cardinality (one‑to‑one, one‑to‑many, many‑to‑many) shows how entities interact.
ER 图是用于数据库设计的概念建模工具。它们用矩形表示实体，椭圆表示属性，菱形表示联系。基数（一对一、一对多、多对多）显示实体间的交互方式。
For IB and Edexcel exams, you must be able to draw simple ER diagrams and convert them into relational schemas. For example, a one‑to‑many relationship is implemented by placing the primary key of the “one” side as a foreign key in the “many” side table. A many‑to‑many relationship requires a junction (link) table containing the primary keys of both participating entities.
在 IB 和 Edexcel 考试中，你必须能够绘制简单的 ER 图并将其转换为关系模式。例如，一对多联系通过将“一”端的主键作为外键放入“多”端的表来实现。多对多联系需要一个包含两个参与实体主键的联结表。
5. Normalisation: 1NF, 2NF, 3NF | 规范化：1NF、2NF、3NF
Normalisation is a systematic process to reduce data redundancy and avoid update anomalies (insertion, deletion, modification). It organises data into progressively stricter normal forms.
规范化是一个减少数据冗余并避免更新异常（插入、删除、修改）的系统化过程。它将数据组织成逐步严格的范式。
First Normal Form (1NF) – Each cell contains only atomic (indivisible) values; there are no repeating groups or arrays. All entries in a column must be of the same type.
中文：第一范式（1NF）— 每个单元格只含原子（不可分割）值；没有重复组或数组。同一列中的所有条目必须类型相同。
Second Normal Form (2NF) – The table is in 1NF and every non‑key attribute is fully functionally dependent on the whole primary key (no partial dependency). This is relevant only when a composite primary key exists.
中文：第二范式（2NF）— 表满足 1NF，且每个非键属性完全函数依赖于整个主键（无部分依赖）。仅当存在复合主键时才需考虑。
Third Normal Form (3NF) – The table is in 2NF and all non‑key attributes are non‑transitively dependent on the primary key (no transitive dependency, i.e., a non‑key attribute depends on another non‑key attribute).
中文：第三范式（3NF）— 表满足 2NF，且所有非键属性都非传递依赖于主键（无传递依赖，即非键属性不依赖于另一个非键属性）。
A common exam task is to take unnormalised data and normalise it to 3NF by splitting tables and identifying appropriate keys.
常见的考试任务是给出未规范化的数据，通过拆分表并确定适当的键，将其规范化到 3NF。
6. Structured Query Language (SQL) | 结构化查询语言（SQL）
SQL is the standard language for interacting with relational databases. You need to know two main subsets: Data Definition Language (DDL) for creating and modifying schemas, and Data Manipulation Language (DML) for querying and updating data.
SQL 是与关系数据库交互的标准语言。你需要了解两个主要子集：数据定义语言（DDL），用于创建和修改模式；数据操作语言（DML），用于查询和更新数据。
DDL Examples:
CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR(50), DOB DATE);
ALTER TABLE Student ADD Email VARCHAR(100);
DROP TABLE Student;
DDL 示例：
CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR(50), DOB DATE);
ALTER TABLE Student ADD Email VARCHAR(100);
DROP TABLE Student;
DML Queries:
SELECT Name FROM Student WHERE DOB > '2006-01-01';
INSERT INTO Student VALUES (101, 'Alice', '2005-11-02');
UPDATE Student SET Email = 'alice@school.edu' WHERE StudentID = 101;
DELETE FROM Student WHERE StudentID = 101;
DML 查询：
SELECT Name FROM Student WHERE DOB > '2006-01-01';
INSERT INTO Student VALUES (101, 'Alice', '2005-11-02');
UPDATE Student SET Email = 'alice@school.edu' WHERE StudentID = 101;
DELETE FROM Student WHERE StudentID = 101;
Remember to use JOINs to combine tables. An INNER JOIN returns rows where there is a match in both tables; a LEFT JOIN returns all rows from the left table, with matching rows from the right (or NULL).
记住使用 JOIN 来合并表。INNER JOIN 返回两表都有匹配的行；LEFT JOIN 返回左表所有行，右表匹配的行（若无匹配则为 NULL）。
Example: SELECT Student.Name, Course.Title FROM Student INNER JOIN Enrollment ON Student.StudentID = Enrollment.StudentID INNER JOIN Course ON Enrollment.CourseID = Course.CourseID;
示例：SELECT Student.Name, Course.Title FROM Student INNER JOIN Enrollment ON Student.StudentID = Enrollment.StudentID INNER JOIN Course ON Enrollment.CourseID = Course.CourseID;
7. Data Types & Domain Constraints | 数据类型与域约束
Common SQL data types: INT (integer), DECIMAL(p,s) (fixed‑point), VARCHAR(n) (variable‑length string), CHAR(n) (fixed‑length), DATE, BOOLEAN, FLOAT. Choosing the correct type enforces domain constraints and improves storage efficiency.
常见的 SQL 数据类型：INT（整数）、DECIMAL(p,s)（定点数）、VARCHAR(n)（变长字符串）、CHAR(n)（定长字符串）、DATE、BOOLEAN、FLOAT。选对类型能强制域约束并提高存储效率。
Additional constraints include NOT NULL, UNIQUE, CHECK, and DEFAULT. For instance, Age INT CHECK (Age >= 0) ensures only non‑negative age values are accepted.
其他约束包括 NOT NULL、UNIQUE、CHECK 和 DEFAULT。例如，Age INT CHECK (Age >= 0) 确保只接受非负的年龄值。
8. Indexing & Query Efficiency | 索引与查询效率
An index is a data structure that speeds up data retrieval on a database table. It works like a book’s index – instead of scanning the entire table, the DBMS uses the index to locate data quickly. Indexes are typically created on primary keys automatically, but may be manually added on foreign keys or frequently queried columns.
索引是一种加速数据库表数据检索的数据结构。它的作用类似书本的索引——DBMS 无需扫描整张表，而是利用索引快速定位数据。索引通常自动创建在主键上，但可以手动添加到外键或常被查询的列。
Exam questions often ask about the trade‑off: indexes speed up SELECT queries but slow down INSERT, UPDATE, and DELETE because the index must be updated. You should also be aware of clustered vs non‑clustered indexes.
考题常询问其中的权衡：索引加快 SELECT 查询，但会拖慢 INSERT、UPDATE 和 DELETE，因为索引自身也需要更新。你还应了解聚集索引与非聚集索引的区别。
9. Database Transactions & ACID | 数据库事务与 ACID
A transaction is a sequence of database operations that must be executed as a single unit of work. Transactions are fundamental for maintaining consistency in multi‑user environments. The ACID properties guarantee reliable processing:
事务是必须作为一个工作单元执行的一系列数据库操作。事务对于在多用户环境中维持一致性至关重要。ACID 特性保证了可靠的处理：
- Atomicity – All operations in the transaction succeed, or none do. If any part fails, the entire transaction is rolled back.
原子性 — 事务中的所有操作要么全部成功，要么全部不执行。任何部分失败，整个事务回滚。 - Consistency – A transaction takes the database from one valid state to another, preserving all defined rules (constraints, triggers).
一致性 — 事务使数据库从一个有效状态转换到另一个有效状态，保持所有已定义的规则（约束、触发器）。 - Isolation – Concurrent transactions do not interfere with each other; intermediate states are invisible to other transactions.
隔离性 — 并发事务互不干扰；中间状态对其他事务不可见。 - Durability – Once a transaction is committed, its changes survive system failures (e.g., written to non‑volatile storage).
持久性 — 事务一旦提交，其更改即使在系统故障后也不会丢失（如写入非易失性存储）。
In an exam, you might be asked to explain why ACID matters for a banking transfer: atomicity ensures the debit and credit both happen or neither; consistency keeps the total balance correct; isolation prevents one transaction seeing another’s partial update; durability saves the result permanently.
在考试中，你可能会被要求解释 ACID 对银行转账的重要性：原子性确保借记和贷记同时发生或都不发生；一致性保持总余额正确；隔离性防止一个事务看到另一个事务的部分更新；持久性将结果永久保存。
10. Database Security & Backup | 数据库安全与备份
Database security involves protecting data from unauthorised access, corruption, and theft. Methods include user authentication, access rights (GRANT/REVOKE in SQL), encryption, and views (virtual tables that restrict which columns/rows a user can see).
数据库安全涉及保护数据免受未授权访问、损坏和盗窃。方法包括用户身份验证、访问权限（SQL 中的 GRANT/REVOKE）、加密以及视图（限制用户可查看哪些列/行的虚拟表）。
Regular backups are essential for disaster recovery. A full backup copies the entire database; incremental backups only copy changes since the last backup. Exam questions may ask you to discuss the relative advantages or design a backup strategy.
定期备份对灾难恢复至关重要。完全备份复制整个数据库；增量备份仅复制自上次备份以来的更改。考题可能要求你讨论相对优势或设计备份策略。
11. Distributed Databases & Big Data Concepts | 分布式数据库与大数据概念
As data volumes grow, single‑server databases struggle. Distributed databases spread data across multiple sites, offering improved scalability and fault tolerance. The CAP theorem states that a distributed system can simultaneously provide only two of Consistency, Availability, and Partition tolerance.
随着数据量增长，单服务器数据库不堪重负。分布式数据库将数据分散到多个站点，提供更好的可扩展性和容错能力。CAP 定理指出，分布式系统同时只能提供一致性、可用性和分区容忍性中的两者。
Big data often uses NoSQL databases (document, key‑value, column‑family, graph) that sacrifice strict ACID for horizontal scaling. While IB and Edexcel focus on relational databases, awareness of these alternatives may be useful for high‑band answer discussions.
大数据常使用 NoSQL 数据库（文档、键值、列族、图），它们牺牲严格的 ACID 以换取水平扩展。虽然 IB 和 Edexcel 侧重关系型数据库，了解这些替代方案可能有助于高分答案的论述。
12. Key Practical Skills & Exam Tips | 关键实践技能与应试技巧
You must be able to interpret a given scenario, draw an ER diagram, derive a relational schema (with primary and foreign keys), and write SQL statements to create tables, insert data, and perform queries including JOINs. Normalisation questions often present a table with repeating groups and ask you to convert it to 3NF by splitting into new tables and underlining primary keys.
你必须能够解读给定的场景，绘制 ER 图，推导出关系模式（含主键和外键），并编写 SQL 语句来创建表、插入数据以及执行包含 JOIN 的查询。规范化题目通常会给出一个含有重复组的表，要求你将其拆分为新表并标出主键，从而转换到 3NF。
Common pitfalls: forgetting to specify foreign keys when creating tables; confusing WHERE and HAVING (HAVING is for aggregated conditions after GROUP BY); missing composite key partial dependencies when normalising to 2NF; and misidentifying cardinality in ER diagrams. Practise past paper questions systematically.
常见易错点：建表时忘记指定外键；混淆 WHERE 和 HAVING（HAVING 用于 GROUP BY 之后的聚合条件）；在规范化到 2NF 时遗漏复合键的部分依赖；以及在 ER 图中错误判断基数。请系统性地练习历年真题。
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课程辅导，国外大学本科硕士研究生博士课程论文辅导