📚 IB OCR Computer Science: Database Essentials | IB OCR 计算机：数据库 考点精讲
Databases are central to modern information systems, and mastering them is key for both IB and OCR Computer Science. This guide unpacks relational theory, normalization, SQL, and ties them directly to exam requirements. Let’s build a robust understanding from the ground up.
数据库是现代信息系统的核心，掌握数据库知识对于 IB 和 OCR 计算机科学考试至关重要。本指南将深入解析关系理论、规范化、SQL，并紧密结合考试要求，帮助你从基础开始建立扎实的理解。
1. Fundamental Concepts & Terminology | 基本概念与术语
A database is an organized collection of structured data, typically controlled by a Database Management System (DBMS). Key components include tables (relations), records (tuples), fields (attributes), and primary keys. The DBMS handles security, concurrency, and integrity, insulating users from physical storage details.
数据库是有组织的结构化数据集合，通常由数据库管理系统 (DBMS) 控制。关键组件包括表（关系）、记录（组）、字段（属性）和主键。DBMS 负责处理安全性、并发性和完整性，让用户无需关心物理存储细节。
Entities are real-world objects represented as tables, and relationships between them can be one-to-one, one-to-many, or many-to-many. A flat-file database stores all data in a single table, which leads to redundancy, whereas a relational database splits data across linked tables to reduce duplication.
实体是现实世界中的对象，在数据库中表示为表，它们之间的关系可以是一对一、一对多或多对多。平面文件数据库将所有数据存储在单一表中，会导致冗余，而关系数据库将数据拆分到相互关联的多个表中以减少重复。
2. The Relational Model & Keys | 关系模型与键
The relational model, proposed by E.F. Codd, represents data as mathematical relations. Each table must have a primary key that uniquely identifies each record; this key cannot be null (entity integrity). A foreign key in one table references the primary key of another, enforcing referential integrity and enabling joins.
关系模型由 E.F. Codd 提出，将数据表示为数学关系。每个表必须有一个能够唯一标识每条记录的主键；主键不能为空（实体完整性）。一个表中的外键引用另一个表的主键，确保了参照完整性并支持表的连接。
Candidate keys are minimal sets of attributes that can qualify as the primary key; one is chosen as the primary key, the rest become alternate keys. A composite key uses two or more attributes to form a unique identifier. A secondary index on a non-key field speeds up searches without affecting physical row order.
候选键是能够充当主键的最小属性集；其中一个被选为主键，其他的则成为备用键。复合键使用两个或更多属性来构成唯一标识符。在非键字段上建立的二级索引可以加快搜索速度，但不影响行的物理顺序。
3. Entity-Relationship (ER) Diagrams | 实体关系图
ER diagrams graphically model entities (rectangles), attributes (ellipses), and relationships (diamonds). For exam success, you must accurately map complex relationships and cardinalities (1:1, 1:M, M:N). A many-to-many relationship is resolved by introducing an associative (link) table that holds the primary keys of both entities as a composite primary key and foreign keys.
ER 图用图形方式表示实体（矩形）、属性（椭圆）和关系（菱形）。考试成功的关键在于准确绘制复杂关系及其基数（1:1, 1:M, M:N）。多对多关系通过引入一个关联表来解决，该表将两个实体的主键作为复合主键和外键。
Weak entities depend on a strong (owner) entity for their existence and borrow part of their primary key from the owner. In an ER diagram, a weak entity is shown as a double rectangle, with the identifying relationship drawn as a double diamond.
弱实体依赖于强（属主）实体存在，并从属主实体借用部分主键。在 ER 图中，弱实体用双矩形表示，标识关系用双菱形表示。
4. Normalization: Eliminating Anomalies | 规范化：消除异常
Normalization structures data to minimize redundancy and prevent update, insertion, and deletion anomalies. The progression through First, Second, and Third Normal Forms (1NF, 2NF, 3NF) is examinable and must be applied methodically.
规范化通过构造数据结构来最大限度地减少冗余，并防止更新异常、插入异常和删除异常。第一范式、第二范式和第三范式 (1NF, 2NF, 3NF) 的递进是考试重点，必须按部就班地应用。
1NF requires that every column holds atomic (indivisible) values and there are no repeating groups. 2NF builds on 1NF by demanding that all non-key attributes are fully functionally dependent on the whole primary key (no partial dependencies). 3NF adds the rule that no non-key attribute should be transitively dependent on the primary key.
1NF 要求每列包含原子（不可分割）值，且没有重复组。2NF 在 1NF 的基础上，要求所有非键属性完全函数依赖于整个主键（无部分依赖）。3NF 则增加了非键属性不应传递依赖于主键的规则。
For a table ORDER(OrderID, CustomerID, CustomerName, ProductID, ProductName), the partial dependency of ProductName on ProductID (when the primary key is OrderID+ProductID) violates 2NF. Splitting into separate ORDER, CUSTOMER, and PRODUCT tables, then linking via foreign keys, solves this.
对于 ORDERS(订单ID, 客户ID, 客户姓名, 产品ID, 产品名称) 表，若主键为 (订单ID + 产品ID)，产品名称对产品ID 的部分依赖就违反了 2NF。将其拆分为独立的 ORDERS、CUSTOMERS 和 PRODUCTS 表，并通过外键连接即可解决。
5. Structured Query Language (SQL) – Data Definition | SQL – 数据定义语言
SQL is divided into DDL (Data Definition Language) and DML (Data Manipulation Language). DDL commands define and modify the database schema. Knowing precise syntax for CREATE, ALTER, and DROP is non-negotiable for high marks.
SQL 分为 DDL (数据定义语言) 和 DML (数据操纵语言)。DDL 命令用于定义和修改数据库模式。准确掌握 CREATE、ALTER 和 DROP 的语法是获取高分的必要条件。
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
DoB DATE,
TutorID INT,
FOREIGN KEY (TutorID) REFERENCES Tutor(TutorID)
);
ALTER TABLE Student ADD Email VARCHAR(100); DROP TABLE Student; are common exam tasks. Data types such as INT, VARCHAR, DATE, BOOLEAN, and FLOAT must be chosen appropriately for each attribute.
ALTER TABLE Student ADD Email VARCHAR(100); DROP TABLE Student; 是常见的考试任务。必须为每个属性合理选择数据类型，如 INT、VARCHAR、DATE、BOOLEAN 和 FLOAT。
6. SQL – Data Manipulation (DML) | SQL – 数据操纵语言
DML enables querying, inserting, updating, and deleting data. The SELECT statement, with its clauses (FROM, WHERE, GROUP BY, HAVING, ORDER BY), is the centerpiece of SQL proficiency.
DML 用于查询、插入、更新和删除数据。SELECT 语句及其子句 (FROM, WHERE, GROUP BY, HAVING, ORDER BY) 是 SQL 技能的核心。
SELECT Name, DoB FROM Student WHERE TutorID = 12 ORDER BY Name ASC; retrieves specific columns with filtering and sorting. Aggregate functions COUNT, SUM, AVG, MAX, MIN are used with GROUP BY to produce summary reports.
SELECT Name, DoB FROM Student WHERE TutorID = 12 ORDER BY Name ASC; 通过过滤和排序检索特定的列。聚合函数 COUNT、SUM、AVG、MAX、MIN 与 GROUP BY 一起使用以生成汇总报告。
INSERT INTO Student (StudentID, Name, DoB) VALUES (101, ‘Alice’, ‘2005-06-15’); UPDATE Student SET Email = ‘alice@school.edu’ WHERE StudentID = 101; DELETE FROM Student WHERE StudentID = 101; Remember the critical importance of the WHERE clause in UPDATE and DELETE to avoid altering all rows.
INSERT INTO Student (StudentID, Name, DoB) VALUES (101, ‘Alice’, ‘2005-06-15’); UPDATE Student SET Email = ‘alice@school.edu’ WHERE StudentID = 101; DELETE FROM Student WHERE StudentID = 101; 切记在 UPDATE 和 DELETE 中 WHERE 子句至关重要，以免修改所有行。
7. Inner & Outer Joins | 内连接与外连接
Joins combine rows from two or more tables based on a related column. An INNER JOIN returns only rows where the join condition is true in both tables. Most business queries use INNER JOIN to assemble normalized data.
连接根据相关列将两个或多个表中的行合并。INNER JOIN 只返回两个表中连接条件都为真的行。大多数业务查询使用 INNER JOIN 来组合规范化数据。
LEFT (OUTER) JOIN returns all rows from the left table plus matched rows from the right; unmatched right columns are filled with NULL. RIGHT JOIN and FULL OUTER JOIN work similarly but are used less frequently. Exam questions often ask students to predict the output of a given JOIN operation on small datasets.
LEFT (OUTER) JOIN 返回左表的所有行以及右表的匹配行；不匹配的右表列用 NULL 填充。RIGHT JOIN 和 FULL OUTER JOIN 工作方式类似，但使用较少。考试题常要求学生预测在小型数据集上给定 JOIN 操作的输出。
8. Database Integrity & Constraints | 数据库完整性与约束
Integrity constraints protect data accuracy and consistency. Entity integrity (primary key NOT NULL and unique), referential integrity (foreign key must match an existing primary key or be NULL), and domain integrity (restricting data types or value ranges) are the three pillars.
完整性约束保护数据的准确性和一致性。实体完整性（主键 NOT NULL 且唯一）、参照完整性（外键必须匹配现有主键或为 NULL）和域完整性（限制数据类型或值范围）是三大支柱。
CHECK constraints allow custom rules, e.g., CHECK (Age >= 0 AND Age <= 120). UNIQUE constraints enforce alternate keys. A transaction is a logical unit of work that must be ACID-compliant (Atomicity, Consistency, Isolation, Durability) to maintain integrity during concurrent access and system failures.
CHECK 约束允许自定义规则，例如 CHECK (Age >= 0 AND Age <= 120)。UNIQUE 约束强制备用键。事务是一个逻辑工作单元，必须满足 ACID（原子性、一致性、隔离性、持久性）才能保证在并发访问和系统故障期间的完整性。
9. Database Security & Views | 数据库安全与视图
Security involves authentication, authorization (granting and revoking privileges via DCL commands GRANT and REVOKE), and encryption. A view is a virtual table based on the result set of a SQL statement. It masks underlying table complexity and can restrict sensitive columns from certain users, enhancing authorization.
安全性涉及身份验证、授权（通过 DCL 命令 GRANT 和 REVOKE 授予和撤销权限）和加密。视图是基于 SQL 语句结果集的虚拟表。它隐藏了底层表的复杂性，并能对某些用户隐藏敏感列，从而增强授权控制。
CREATE VIEW StudentContact AS SELECT Name, Email FROM Student; As a stored query, a view always shows up-to-date data but cannot contain an ORDER BY clause in most SQL dialects unless combined with TOP or LIMIT.
CREATE VIEW StudentContact AS SELECT Name, Email FROM Student; 作为一个存储的查询，视图始终显示最新数据，但在大多数 SQL 方言中不能包含 ORDER BY 子句，除非与 TOP 或 LIMIT 结合使用。
10. Data Warehousing & Big Data Contexts | 数据仓库与大数据背景
Beyond OLTP (Online Transaction Processing) used in everyday business, data warehouses support OLAP (Online Analytical Processing) for decision-making. They hold historical, aggregated data across multiple dimensions, often structured in star or snowflake schemas.
在日常业务中使用的 OLTP（在线事务处理）之外，数据仓库支持用于决策的 OLAP（在线分析处理）。它们保存跨多个维度的历史汇总数据，通常采用星型或雪花型模式结构。
Data mining discovers patterns and knowledge from large datasets. The exam may discuss the move from traditional SQL databases to NoSQL (document, key-value, graph, column-family stores) for unstructured data and horizontal scaling. While not calc-intensive, understanding the CAP theorem (Consistency, Availability, Partition Tolerance) and the principle of eventual consistency is becoming increasingly relevant.
数据挖掘从大型数据集中发现模式和知识。考试可能会讨论从传统 SQL 数据库转向 NoSQL（文档、键值、图、列族存储）以处理非结构化数据和实现水平扩展的趋势。虽然不涉及大量计算，但理解 CAP 定理（一致性、可用性、分区容忍性）和最终一致性原则正变得越来越重要。
11. Examination Pitfalls & Model Answers | 考试陷阱与高分策略
A common mistake is confusing degrees of a relationship with entity types. Cardinality describes the number of instances (1:1, 1:M), while the degree of a relationship refers to the number of entities involved (binary, ternary).
一个常见错误是将关系的度与实体类型混淆。基数描述的是实例数量 (1:1, 1:M)，而关系的度是指所涉及的实体数量（二元、三元）。
When writing SQL, always handle NULLs explicitly with IS NULL/IS NOT NULL, not ‘= NULL’. In normalization questions, justify your decomposition by stating “there exists a partial dependency of attribute X on part Y of the primary key”. Show the before and after table structures clearly.
编写 SQL 时，务必使用 IS NULL/IS NOT NULL 显式处理 NULL 值，而不是 ‘= NULL’。在规范化问题中，通过说明“属性 X 对主键中部分 Y 存在部分依赖”来证明你的分解。清楚地展示分解前后的表结构。
For ER diagrams, never forget to label primary keys, foreign keys, and cardinality. If a question asks to resolve a many-to-many, always draw the resulting link table with its composite primary key.
对于 ER 图，切勿忘记标注主键、外键和基数。如果题目要求解决多对多关系，始终要画出生成的关联表及其复合主键。
12. Practical Design & Implementation Think-through | 实际设计与实现思路
When given a scenario like a library management system, start by identifying core entities (Book, Member, Loan). Determine primary keys (ISBN, MemberID) and relationships (a Member borrows many Books; a Book is borrowed by many Members over time, suggesting a Loan table to resolve M:N).
当面对如图书管理系统的场景时，首先要识别核心实体（图书、会员、借阅）。确定主键（ISBN、会员ID）和关系（一个会员可借阅多本图书；一本图书可被多个会员在不同时间借阅，这提示需要一个借阅表来解决 M:N）。
Define attributes with appropriate data types: LoanDate DATE, Returned BOOLEAN. Apply normalization: check that Loan table has no partial dependencies (if LoanID is the single primary key, all non-key attributes depend on it entirely). Write SQL queries to answer typical questions: “Find all books currently on loan by a specific member.”
使用适当的数据类型定义属性：LoanDate DATE, Returned BOOLEAN。实施规范化：检查借阅表是否有部分依赖（如果 LoanID 是单一主键，则所有非键属性完全依赖于它）。编写 SQL 查询来回答典型问题：“查找特定会员当前借出的所有图书。”
Practice layering clauses: SELECT Title FROM Book JOIN Loan ON Book.ISBN = Loan.ISBN WHERE Loan.MemberID = 42 AND Loan.Returned = FALSE; This approach, systematically translating requirements into SQL and schema, ensures you capture all marks on design and implementation sections.
练习分层使用子句：SELECT Title FROM Book JOIN Loan ON Book.ISBN = Loan.ISBN WHERE Loan.MemberID = 42 AND Loan.Returned = FALSE; 这种系统地将需求转化为 SQL 和模式的方法，可以确保你在设计和实现部分拿到所有分数。
Published by TutorHao | IB OCR Computer Science Revision Series | aleveler.com
更多咨询请联系16621398022（同微信）
屏轩国际教育cambridge primary/secondary checkpoint, cat4, ukiset,ukcat,igcse,alevel,PAT,STEP,MAT, ibdp,ap,ssat,sat,sat2课程辅导，国外大学本科硕士研究生博士课程论文辅导