📚 Database Key Concepts for IB & WJEC Computer Science | IB WJEC 计算机数据库考点精讲
A database is a structured collection of data that is stored and accessed electronically. Understanding database principles is essential for both IB and WJEC computer science syllabi. This revision guide covers relational models, SQL, normalisation, and transaction management to help you master the topic and excel in your examinations.
数据库是以电子方式存储和访问的结构化数据集合。理解数据库原理对于 IB 和 WJEC 计算机科学课程都至关重要。本考点精讲涵盖关系模型、SQL、规范化和事务管理，助你掌握该主题，在考试中脱颖而出。
1. Introduction to Databases | 数据库简介
A database management system (DBMS) allows users to define, create, maintain and control access to the database. It eliminates redundancy, enforces integrity, and provides data independence.
数据库管理系统 (DBMS) 允许用户定义、创建、维护和控制对数据库的访问。它能消除冗余、强制完整性并提供数据独立性。
Common DBMS examples include MySQL, PostgreSQL, Microsoft SQL Server, and Oracle. In WBQ and IB contexts, you may encounter both desktop-based systems like MS Access and server-based systems.
常见的 DBMS 示例包括 MySQL、PostgreSQL、Microsoft SQL Server 和 Oracle。在 WJEC 和 IB 语境中，你可能会遇到像 MS Access 这样的桌面系统以及服务器端系统。
2. Relational Database Concepts | 关系数据库概念
A relational database organises data into tables (relations). Each table consists of rows (records/tuples) and columns (attributes/fields). The schema defines the table’s structure: table name, attributes, and data types.
关系数据库将数据组织成表（关系）。每个表由行（记录/元组）和列（属性/字段）组成。模式 (schema) 定义表的结构：表名、属性和数据类型。
Every row must be uniquely identifiable. This is achieved through a primary key. Tables are linked through foreign keys, enabling relationships such as one-to-one, one-to-many, and many-to-many (resolved via a junction table).
每一行必须是唯一可识别的。这通过主键实现。表之间通过外键链接，从而建立一对一、一对多和多对多（通过联结表解决）的关系。
3. Keys: Primary, Foreign, and Candidate | 键：主键、外键与候选键
A primary key is a column or combination of columns that uniquely identifies each record. It cannot contain NULL values and must be stable. A candidate key is any attribute or minimal set of attributes that can serve as a primary key.
主键是唯一标识每条记录的一列或多列组合。它不能包含 NULL 值且必须保持稳定。候选键是任何可以作为主键的属性或最小属性集。
A foreign key is a field in one table that refers to the primary key of another table, enforcing referential integrity. The database will reject an insert or update that would break a foreign key constraint.
外键是一个表中的字段，它引用另一表的主键，从而强制引用完整性。数据库将拒绝任何会破坏外键约束的插入或更新操作。
In IB/WJEC exams you might be asked to identify suitable primary keys for given tables. Always choose a column that is guaranteed unique and not subject to frequent changes.
在 IB/WJEC 考试中，你可能会被要求为给定表确定合适的主键。务必选择保证唯一且不常变更的列。
4. Entity-Relationship Diagrams | 实体关系图 (ERD)
An Entity-Relationship Diagram (ERD) models the data requirements of a system. Entities are objects or concepts (e.g., Student, Course). Relationships show associations (e.g., enrolls in). Attributes describe properties.
实体关系图 (ERD) 对系统的数据需求进行建模。实体是对象或概念（如学生、课程）。关系表示关联（如选修）。属性描述性质。
Common notation: rectangles for entities, diamonds for relationships (in Chen’s notation), and ovals for attributes. WJEC often uses a simplified crow’s foot notation. Cardinality ratios: 1:1, 1:M, M:N. Many-to-many must be resolved by adding an associative entity.
常见符号：矩形表示实体，菱形表示关系（陈氏表示法），椭圆表示属性。WJEC 通常使用简化的鱼尾纹表示法。基数比：1:1、1:M、M:N。多对多必须通过添加关联实体来分解。
5. Normalisation (1NF, 2NF, 3NF) | 规范化（第一、二、三范式）
Normalisation is the process of organising data to minimise redundancy and avoid anomalies. It splits tables into smaller, well-structured relations. The three main normal forms are tested in both syllabi.
规范化是组织数据以最小化冗余和避免异常的过程。它将表拆分成更小、结构良好的关系。三个主要范式在两个大纲中都会考查。
First Normal Form (1NF): Each column contains atomic values; there are no repeating groups. For example, a table storing multiple phone numbers in one column violates 1NF.
第一范式 (1NF)：每列包含原子值；没有重复组。例如，一个在一列中存储多个电话号码的表就违反了 1NF。
Second Normal Form (2NF): It is in 1NF and every non-key attribute is fully functionally dependent on the whole primary key. This applies to tables with composite primary keys.
第二范式 (2NF)：满足 1NF 且每个非键属性完全函数依赖于整个主键。这适用于具有复合主键的表。
Third Normal Form (3NF): It is in 2NF and no non-key attribute is transitively dependent on the primary key. In other words, no non-key attribute depends on another non-key attribute.
第三范式 (3NF)：满足 2NF 且没有非键属性传递依赖于主键。换句话说，没有非键属性依赖于另一个非键属性。
| Normal Form | Key Rule |
|---|---|
| 1NF | Atomic values, no repeating groups |
| 2NF | No partial dependencies on a composite key |
| 3NF | No transitive dependencies |
6. SQL Basics: SELECT, FROM, WHERE | SQL 基础：SELECT, FROM, WHERE
Structured Query Language (SQL) is the standard language for relational databases. The core retrieval statement is SELECT … FROM … WHERE. It combines projection (choosing columns) and selection (choosing rows).
结构化查询语言 (SQL) 是关系数据库的标准语言。核心检索语句是 SELECT … FROM … WHERE。它结合了投影（选择列）和选择（选择行）。
Basic syntax:
SELECT column1, column2 FROM table_name WHERE condition;
For example, to retrieve names of students older than 18:
SELECT name, age FROM Student WHERE age > 18;
You can use logical operators (AND, OR, NOT), string patterns (LIKE ‘%text%’), and range checks (BETWEEN, IN). Always end statements with a semicolon.
你可以使用逻辑运算符 (AND, OR, NOT)、字符串模式 (LIKE ‘%text%’) 以及范围检查 (BETWEEN, IN)。语句始终以分号结尾。
7. SQL Joins and Multi-Table Queries | SQL 连接与多表查询
Joins combine rows from two or more tables based on a related column. The most common is INNER JOIN, which returns only matching rows. LEFT JOIN returns all rows from the left table and matched rows from the right; unmatched right columns become NULL.
连接基于相关列组合两个或多个表中的行。最常见的是 INNER JOIN，它只返回匹配的行。LEFT JOIN 返回左表的所有行以及右表的匹配行；未匹配的右列显示为 NULL。
Example:
SELECT Student.name, Enrolment.course_id
FROM Student
INNER JOIN Enrolment ON Student.id = Enrolment.student_id;
For WJEC, you may also encounter implicit join syntax using WHERE clause (SELECT … FROM t1, t2 WHERE t1.id = t2.fk). IB tends to focus on explicit JOIN syntax. Both are correct; understand the differences.
对于 WJEC，你可能还会遇到使用 WHERE 子句的隐式连接语法（SELECT … FROM t1, t2 WHERE t1.id = t2.fk）。IB 则更关注显式 JOIN 语法。两者都正确；理解它们之间的区别。
8. Data Manipulation: INSERT, UPDATE, DELETE | 数据操作：INSERT, UPDATE, DELETE
INSERT adds new records into a table. Specify columns and values. If all columns are included in order, column names can be omitted.
INSERT 向表中添加新记录。需指定列和值。如果按顺序包含所有列，可省略列名。
INSERT INTO Student (id, name, age) VALUES (101, ‘Alex’, 17);
UPDATE modifies existing rows. Always use a WHERE clause to target specific rows; otherwise all rows will be updated. DELETE removes rows; similarly, use WHERE to avoid deleting the entire table.
UPDATE 修改现有行。务必使用 WHERE 子句定位特定行；否则所有行都会被更新。DELETE 删除行；同样，使用 WHERE 避免删除整张表。
UPDATE Student SET age = 18 WHERE id = 101;
DELETE FROM Student WHERE id = 101;
These statements are part of Data Manipulation Language (DML). In practice, databases enforce foreign key constraints that may prevent deletions if dependent rows exist.
这些语句属于数据操作语言 (DML)。在实际中，数据库会强制外键约束，若存在依赖行则可能阻止删除。
9. Transactions and ACID Properties | 事务与 ACID 特性
A transaction is a sequence of database operations treated as a single logical unit of work. It must either complete entirely (commit) or have no effect at all (rollback). This ensures consistency even in case of failures.
事务是被视为单个逻辑工作单元的数据库操作序列。它必须要么完全完成（提交），要么完全没有影响（回滚）。这确保了即使在故障情况下也能保持一致性。
ACID properties: Atomicity (all or nothing), Consistency (database remains in a valid state), Isolation (concurrent transactions do not interfere), Durability (committed changes persist).
ACID 特性：原子性（全做或全不做）、一致性（数据库保持有效状态）、隔离性（并发事务不干扰）、持久性（已提交的更改持久保存）。
IB includes transaction processing concepts and may ask to explain ACID. WJEC also requires understanding of commit and rollback in the context of data integrity.
IB 涵盖事务处理概念，可能会要求解释 ACID。WJEC 同样要求在数据完整性语境下理解提交和回滚。
BEGIN;
UPDATE account SET balance = balance – 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
If any statement fails, a ROLLBACK ensures no partial transfer occurs.
如果任一语句失败，ROLLBACK 确保不会发生部分转账。
10. Indexing and Performance | 索引与性能
An index is a data structure that improves the speed of data retrieval on a table at the cost of additional storage and slower writes. It works like a book’s index, allowing the DBMS to find rows without scanning the entire table.
索引是一种数据结构，可提高表上数据检索的速度，但代价是额外存储空间和较慢的写入。它类似于书的索引，使 DBMS 无需扫描整张表就能找到行。
A primary key automatically creates a clustered index. You can create secondary indexes on columns frequently used in WHERE, JOIN, and ORDER BY. However, too many indexes degrade UPDATE/INSERT/DELETE performance.
主键会自动创建聚集索引。你可以为经常在 WHERE、JOIN 和 ORDER BY 中使用的列创建二级索引。但是，过多的索引会降低 UPDATE/INSERT/DELETE 的性能。
WJEC and IB might ask you to explain why indexing is important for large databases and to suggest suitable columns for indexing.
WJEC 和 IB 可能会要求解释为什么索引对大型数据库很重要，并建议适合索引的列。
11. Database Security and Integrity | 数据库安全与完整性
Data integrity ensures accuracy and consistency. Entity integrity guarantees no duplicate primary keys; referential integrity ensures foreign key values match existing primary keys or are NULL.
数据完整性确保准确性和一致性。实体完整性保证没有重复的主键；引用完整性确保外键值与现有主键匹配或为 NULL。
Security involves access control, authentication, and encryption. Grant and revoke statements control user privileges (e.g., GRANT SELECT ON Student TO user1). SQL injection is a common threat where malicious code is injected via user input.
安全涉及访问控制、身份验证和加密。GRANT 和 REVOKE 语句控制用户权限（例如 GRANT SELECT ON Student TO user1）。SQL 注入是一种常见威胁，恶意代码通过用户输入注入。
Both exam boards expect you to discuss prevention: use parameterised queries, input validation, and least-privilege access.
两个考试局都希望你能讨论预防措施：使用参数化查询、输入验证和最小权限访问。
12. Common Exam-Style Questions and Tips | 常见考题与技巧
Typical exam questions include: identify primary/foreign keys, normalise a table to 3NF, draw an ERD from a scenario, write SQL queries for given tasks, and explain ACID or transactional integrity.
典型考题包括：识别主键/外键、将表规范至 3NF、根据场景绘制 ERD、为给定任务编写 SQL 查询，以及解释 ACID 或事务完整性。
For SQL, always test your logic: think about the result set before writing. When normalising, systematically check each normal form. For ERDs, ensure you resolve many-to-many relationships and include cardinalities.
对于 SQL，始终在编写前思考结果集。规范化时，系统检查每个范式。对于 ERD，确保分解多对多关系并标注基数。
Manage your time during the exam: sketching a quick ERD and identifying keys can earn easy marks before tackling complex queries.
考试中合理分配时间：在解决复杂查询之前，快速画出 ERD 并识别键可以轻松得分。
Published by TutorHao | IB WJEC Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导Cancel reply