Database Essentials for IGCSE OCR Computer Science | IGCSE OCR 计算机：数据库 考点精讲

📚 Database Essentials for IGCSE OCR Computer Science | IGCSE OCR 计算机：数据库 考点精讲

Databases are at the heart of almost every modern application, from school management systems to social media platforms. In the OCR IGCSE Computer Science syllabus, you are expected to understand the fundamental concepts of databases, how they differ from spreadsheets, the structure of relational databases, basic SQL commands, and the importance of data integrity and security. This article breaks down all key topics into digestible sections, with clear examples and bilingual explanations to help you master the content.

数据库是几乎所有现代应用程序的核心，从学校管理系统到社交媒体平台。在 OCR IGCSE 计算机科学大纲中，你需要理解数据库的基本概念、它们与电子表格的区别、关系数据库的结构、基本的 SQL 命令，以及数据完整性和安全性的重要性。本文将把所有关键主题分解为易于理解的章节，配以清晰的示例和中英双语解释，帮助你掌握这些内容。

1. What is a Database? | 什么是数据库？

A database is a structured collection of data organised in a way that allows efficient retrieval, insertion, updating, and deletion of information. Unlike a simple list, a database minimises data redundancy and enforces consistency rules. In IGCSE terms, it is typically implemented as a computerised system, often using a Database Management System (DBMS) such as Microsoft Access, MySQL, or SQLite.

数据库是一种有组织的数据集合，其结构允许高效地检索、插入、更新和删除信息。与简单的列表不同，数据库最小化了数据冗余并强制执行一致性规则。在 IGCSE 语境中，数据库通常作为一个计算机化系统实现，往往借助数据库管理系统 (DBMS)，例如 Microsoft Access、MySQL 或 SQLite。

A database can be single-file (flat-file) or relational. The syllabus focuses primarily on relational databases because they reduce repetition by linking tables together via common fields. Understanding this concept is essential for tackling exam questions on database design.

数据库可以是单文件（平面文件）或关系型的。课程大纲主要关注关系数据库，因为它们通过公共字段将表链接起来，从而减少重复。理解这一概念对于解决有关数据库设计的考题至关重要。

---

2. Database vs Spreadsheet | 数据库与电子表格对比

Many students confuse databases with spreadsheets, but they serve very different purposes. A spreadsheet (e.g., Excel) is great for calculations, charting, and modelling data for an individual. A database is designed for multi-user environments, handling large volumes of structured data, and maintaining relationships between different entities.

许多学生将数据库与电子表格混淆，但它们的用途截然不同。电子表格（如 Excel）非常适合个人进行计算、绘制图表和建模数据。而数据库则是为多用户环境设计的，用于处理大量结构化数据，并维护不同实体之间的关系。

Feature | 特性	Database	Spreadsheet
Data redundancy | 数据冗余	Low (normalisation) | 低（规范化）	High (duplication common) | 高（常出现重复）
Data integrity | 数据完整性	Enforced by constraints | 通过约束强制	Limited, manual checks | 有限，需手动检查
Multi-user access | 多用户访问	Yes, with security | 是，有安全控制	Limited, risk of conflict | 有限，有冲突风险
Data volume | 数据量	Very large | 非常大	Moderate | 中等

---

3. Key Database Terminologies | 关键数据库术语

Before diving into the relational model, you must master the basic vocabulary. The OCR exam often tests your ability to define and identify these terms in a given table or scenario.

在深入关系模型之前，你必须掌握基本术语。OCR 考试经常考查你在给定表格或场景中定义并识别这些术语的能力。

• Table (Relation): A set of data organised in rows and columns. | 表（关系）：以行和列组织的数据集合。
• Record (Tuple): A single row in a table, representing one item or entity. | 记录（元组）：表中的一行，代表一个项目或实体。
• Field (Attribute): A column heading, defining one specific piece of data stored for each record. | 字段（属性）：列标题，为每条记录定义存储的一种特定数据。
• Primary Key: A field that uniquely identifies each record in a table. No two records can share the same primary key value; it cannot be null. | 主键：唯一标识表中每条记录的一个字段。没有两条记录可以共享同一个主键值；它不能为空。
• Foreign Key: A field in one table that refers to the primary key of another table, used to link records across tables. | 外键：一个表中的字段，它引用另一个表的主键，用于跨表链接记录。

Consider a simple school database: a Students table has fields StudentID, Name, Class. Here StudentID is the primary key. A Grades table might contain StudentID as a foreign key, linking a grade to a particular student.

设想一个简单的学校数据库：Students 表有字段 StudentID、Name、Class。这里 StudentID 是主键。Grades 表可能包含 StudentID 作为外键，将成绩与特定学生关联起来。

---

4. Relational Databases and Keys | 关系数据库与键

A relational database organises data into multiple tables that are linked by relationships. These relationships are built using primary and foreign keys. The main advantages are the elimination of data redundancy (through normalisation) and the ability to perform complex queries across tables.

关系数据库将数据组织到通过关系链接的多个表中。这些关系使用主键和外键建立。主要优点在于消除数据冗余（通过规范化）以及能够跨表执行复杂查询。

There are three types of relationships you should recognise: one-to-one (e.g., one person – one passport), one-to-many (e.g., one teacher – many students), and many-to-many (e.g., many students – many subjects, resolved by a junction table). In IGCSE, the focus is on one-to-many relationships, which are implemented by placing the primary key of the “one” side as a foreign key in the “many” side table.

你应该能识别三种类型的关系：一对一（例如，一个人 — 一本护照）、一对多（例如，一位老师 — 多名学生）和多对多（例如，多名学生 — 多门科目，通过连接表解决）。在 IGCSE 中，重点是一对多关系，其实现方法是将 “一” 侧的主键作为外键放在 “多” 侧表中。

---

5. Introduction to SQL | SQL 入门

Structured Query Language (SQL) is the standard language for manipulating relational databases. OCR IGCSE expects you to write and interpret basic SQL statements. The most common commands are SELECT, INSERT, UPDATE, and DELETE. You must also understand the WHERE clause for filtering and ORDER BY for sorting.

结构化查询语言 (SQL) 是操作关系数据库的标准语言。OCR IGCSE 要求你能编写并解释基本的 SQL 语句。最常见的命令是 SELECT、INSERT、UPDATE 和 DELETE。你还必须理解用于筛选的 WHERE 子句和用于排序的 ORDER BY。

In the exam, you will be given a table structure and asked to produce SQL to achieve a certain result, or you may be asked to describe what a given SQL statement does. Always pay attention to the exact field names and table names provided.

在考试中，你会得到一个表结构，并被要求编写 SQL 来实现某种结果，或者可能要求你描述给定 SQL 语句的作用。务必注意所提供的准确字段名和表名。

---

6. SQL Queries: SELECT and WHERE | SQL 查询：SELECT 与 WHERE

The SELECT statement retrieves data from a database. Its basic syntax is:

SELECT 语句用于从数据库检索数据。其基本语法为：

SELECT column1, column2 FROM table_name WHERE condition;

If you want all columns, use the asterisk *. The WHERE clause filters records based on a condition. Operators like =, >, <, >=, <=, <> (not equal), and LIKE (for pattern matching) are commonly examined.

如果要选择所有列，请使用星号 *。WHERE 子句根据条件筛选记录。常见运算符包括 =、>、<、>=、<=、<>（不等于）和 LIKE（用于模式匹配），这些都是常考内容。

Example: Find the names of all students in class ’10A’ from the Students table.

示例：从 Students 表中查询班级为 “10A” 的所有学生的姓名。

SELECT Name FROM Students WHERE Class = '10A';

Note that text values must be enclosed in single quotes. The exam might also use logical operators AND, OR, NOT to combine conditions.

请注意，文本值必须用单引号括起来。考试也可能使用逻辑运算符 AND、OR、NOT 来组合条件。

---

7. Sorting and Filtering Data: ORDER BY | 数据排序与筛选：ORDER BY

To sort the result set, the ORDER BY clause is used. It can be followed by ASC (ascending, default) or DESC (descending). You can sort by multiple columns.

要对结果集排序，可使用 ORDER BY 子句。其后可加 ASC（升序，默认）或 DESC（降序）。也可以按多个列排序。

Retrieve all products from the Products table sorted by price in descending order, and then by name alphabetically:

从 Products 表检索所有产品，先按价格降序排列，再按名称字母顺序排列：

SELECT * FROM Products ORDER BY Price DESC, Name ASC;

Another important keyword is DISTINCT, which removes duplicate values. For instance, SELECT DISTINCT Country FROM Customers; returns a list of unique countries.

另一个重要关键字是 DISTINCT，它可以消除重复值。例如，SELECT DISTINCT Country FROM Customers; 返回唯一国家/地区列表。

---

8. Updating Data: INSERT, UPDATE, DELETE | 数据更新：插入、更新、删除

Data manipulation is a core function of any DBMS. The three main SQL commands to modify data are:

数据操作是任何 DBMS 的核心功能。用于修改数据的三个主要 SQL 命令是：

• INSERT INTO: adds new records to a table.
• UPDATE: modifies existing records.
• DELETE FROM: removes records.

Example of INSERT:

INSERT INTO Students (StudentID, Name, Class) VALUES (105, 'Alex Chen', '10B');

Example of UPDATE (changing the class of student 105):

UPDATE Students SET Class = '10A' WHERE StudentID = 105;

Omitting the WHERE clause in an UPDATE or DELETE command is catastrophic – it will affect all rows. The exam may ask you to identify such risks.

在 UPDATE 或 DELETE 命令中省略 WHERE 子句是灾难性的——它会影响到所有行。考试可能会要求你指出此类风险。

---

9. Database Normalisation | 数据库规范化

Normalisation is the process of organising data to reduce redundancy and improve data integrity. The syllabus introduces the first three normal forms (1NF, 2NF, 3NF). You should be able to identify whether a given table satisfies these forms or how to break an unnormalised table into normalised ones.

规范化是组织数据以减少冗余并提高数据完整性的过程。大纲介绍了前三个范式（1NF、2NF、3NF）。你应该能够判断给定的表是否满足这些范式，或者如何将未规范化的表分解为规范化的表。

Normal Form | 范式	Rule | 规则
1NF	Atomic values; no repeating groups. | 原子值；无重复组。
2NF	Already in 1NF, and all non-key attributes depend on the whole primary key (no partial dependencies). | 已满足 1NF，且所有非键属性都依赖于整个主键（无部分依赖）。
3NF	Already in 2NF, and no transitive dependencies (non-key attributes should not depend on other non-key attributes). | 已满足 2NF，且无传递依赖（非键属性不应依赖于其他非键属性）。

For example, a table with repeating phone number columns violates 1NF. Splitting it into two tables (People and PhoneNumbers) resolves this. A classic exam scenario asks you to normalise a flat-file database into third normal form.

例如，一个含有重复电话号码列的表违反了 1NF。将其拆分为两个表（People 和 PhoneNumbers）可以解决此问题。经典的考试场景会要求你将平面文件数据库规范化到第三范式。

---

10. Data Integrity and Validation | 数据完整性与验证

Data integrity means the accuracy and consistency of data over its lifecycle. Databases use several mechanisms to maintain integrity:

数据完整性是指数据在其生命周期中的准确性和一致性。数据库使用多种机制来维护完整性：

• Entity integrity: ensured by primary keys (no duplicate, no null). | 实体完整性：由主键保证（无重复，无空值）。
• Referential integrity: ensured by foreign keys; a foreign key value must match an existing primary key value in the referred table, or be null if allowed. | 引用完整性：由外键保证；外键值必须匹配被引用表中现有的主键值，或如果允许则为空。
• Validation rules: constraints on data entry, such as range checks, format checks, presence checks, and length checks. These are enforced by the DBMS at the time of data input. | 验证规则：对数据输入的约束，如范围检查、格式检查、存在性检查和长度检查。这些由 DBMS 在数据输入时强制执行。

Verification, on the other hand, is the process of checking that data entered matches its source (e.g., double entry, proofreading). Validation does not guarantee that data is correct – only that it follows rules.

另一方面，验证（verification）是检查输入数据与原始数据是否一致的过程（例如，双重输入、校对）。验证规则不能保证数据是正确的——只能保证它符合规则。

---

11. Database Security | 数据库安全

Protecting data from unauthorised access, corruption, or theft is critical. The OCR syllabus expects you to discuss methods such as:

保护数据免受未经授权的访问、损坏或盗窃至关重要。OCR 大纲要求你讨论以下方法：

• User access levels: assigning different permissions (read, write, delete) to different users based on their roles. | 用户访问级别：根据角色为不同用户分配不同权限（读取、写入、删除）。
• Encryption: encoding data so that it cannot be understood without a decryption key, protecting data at rest and in transit. | 加密：对数据进行编码，使没有解密密钥就无法理解，保护静态数据和传输中的数据。
• Backups: regular copies of the database stored in a separate location to allow recovery after data loss. | 备份：将数据库的定期副本存储在单独位置，以便在数据丢失后进行恢复。
• Physical security: locks, biometric access to server rooms. | 物理安全：服务器机房的门锁、生物识别访问。

You may be asked to suggest appropriate security measures for a given scenario, such as a hospital database. Always consider both logical and physical barriers.

你可能会被要求为某个场景（如医院数据库）建议适当的安全措施。请始终考虑逻辑和物理两方面的屏障。

---

12. Summary and Exam Tips | 总结与应试技巧

To excel in the database section of OCR IGCSE Computer Science, remember these key points:

要在 OCR IGCSE 计算机科学的数据库部分取得优异成绩，请记住以下关键点：

• Distinguish clearly between flat-file and relational databases. | 清楚区分平面文件和关系数据库。
• Be fluent in SQL: SELECT...FROM...WHERE...ORDER BY. Practice writing queries from scenarios. | 熟练使用 SQL：SELECT...FROM...WHERE...ORDER BY。根据场景练习编写查询。
• Understand normalisation steps and be able to identify 1NF, 2NF, 3NF violations. | 理解规范化步骤，能够识别违反 1NF、2NF、3NF 的情况。
• Explain the use of primary keys, foreign keys, and referential integrity. | 解释主键、外键和引用完整性的用途。
• Discuss security measures using technical vocabulary (encryption, access rights). | 使用专业词汇（加密、访问权限）讨论安全措施。

The exam questions often combine theory with application. When asked to write SQL, read the table structure carefully and use the exact field names provided. For normalisation, think about removing repeating data and ensuring each table describes one “theme”. Above all, check your answers for common mistakes like missing quote marks or forgotten WHERE clauses.

考试题目通常结合理论和应用。当要求编写 SQL 时，请仔细阅读表结构并使用所提供的确切字段名。对于规范化，考虑消除重复数据并确保每个表只描述一个 “主题”。最重要的是，检查你的答案是否犯常见错误，例如遗漏引号或忘记 WHERE 子句。

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

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