📚 Database Essentials for A-Level CS | A-Level 计算机数据库考点精讲
Databases form a critical pillar of A-Level Computer Science, blending theory and practical SQL. Candidates must understand relational models, normalisation, keys, and how to construct and query a database effectively. This revision guide walks you through every exam-relevant concept with clear bilingual explanations.
数据库是A-Level计算机科学的重要支柱，融合了理论与SQL实践。考生必须理解关系模型、规范化、键以及如何有效地构建和查询数据库。本复习指南通过清晰的双语讲解，带你梳理每一个与考试相关的概念。
1. Introduction to Databases | 数据库简介
A database is an organised collection of structured data, typically stored electronically in a computer system. It is managed by a Database Management System (DBMS) which allows users to define, create, maintain and control access to data.
数据库是一个有组织的结构化数据集合，通常以电子方式存储在计算机系统中。它由数据库管理系统 (DBMS) 管理，该系统允许用户定义、创建、维护和控制对数据的访问。
Compared to traditional flat-file systems, databases minimise data redundancy, improve consistency, and enable efficient concurrent access. This is achieved via the relational model, which organises data into tables linked by keys.
与传统的平面文件系统相比，数据库最大限度地减少了数据冗余，提高了一致性，并实现了高效的并发访问。这是通过关系模型实现的，该模型将数据组织成通过键链接的表。
2. Relational Database Concepts | 关系型数据库概念
A relational database stores data in relations (tables). Each table consists of rows (records/tuples) and columns (fields/attributes). Every column has a defined data type, such as INTEGER, VARCHAR, or DATE.
关系型数据库将数据存储在关系（表）中。每个表由行（记录/元组）和列（字段/属性）组成。每列都有定义的数据类型，例如 INTEGER、VARCHAR 或 DATE。
Tables are linked through relationships: one-to-one, one-to-many, and many-to-many. A one-to-many relationship is the most common, where a foreign key in the ‘many’ side table references the primary key of the ‘one’ side table.
表通过关系链接：一对一、一对多和多对多。一对多关系最为常见，其中“多”侧表中的外键引用“一”侧表的主键。
3. Keys: Primary and Foreign Keys | 键：主键与外键
A primary key uniquely identifies each record in a table. It must be unique and cannot contain NULL values. A candidate key is any column or set of columns that could serve as the primary key; the chosen one becomes the primary key.
主键唯一标识表中的每条记录。它必须唯一且不能包含 NULL 值。候选键是任意一个可以作为主键的列或列集；被选定的那个就成为主键。
A foreign key is a column in one table that refers to the primary key in another table. It establishes a link between the two tables and enforces referential integrity – a foreign key value must match an existing primary key or be NULL.
外键是一个表中的列，它引用了另一个表中的主键。它在两个表之间建立链接，并强制实施引用完整性——外键值必须匹配现有的主键值或为 NULL。
4. Entity-Relationship Diagrams (ERD) | 实体-关系图 (ERD)
ERDs are used to model the structure of a database graphically. Entities (tables) are drawn as rectangles, attributes as ovals, and relationships as diamond shapes or lines. Cardinality ratios (1:1, 1:M, M:N) are marked on the connecting lines.
ERD 用于以图形方式对数据库结构进行建模。实体（表）绘制为矩形，属性绘制为椭圆形，关系绘制为菱形或线条。连接线上标有基数比（1:1、1:M、M:N）。
In crow’s foot notation, the ‘one’ side is shown with a single vertical line, and the ‘many’ side with a crow’s foot (three lines). Attributes can be marked as primary key (underlined) or multivalued (double oval).
在鸡爪符号表示法中，“一”侧用一条垂直线表示，“多”侧用鸡爪（三条线）表示。属性可以标记为主键（下划线）或多值（双椭圆）。
5. Normalisation: 1NF, 2NF, 3NF | 规范化：第一范式、第二范式、第三范式
Normalisation is a systematic process to eliminate data redundancy and undesirable characteristics like insertion, update and deletion anomalies. It decomposes tables into smaller, well-structured relations.
规范化是一个消除数据冗余以及消除插入、更新和删除异常等不良特性的系统过程。它将表分解为更小、结构良好的关系。
First Normal Form (1NF) requires that each cell holds atomic (indivisible) values, columns contain values of a single type, each row is unique (primary key present), and the order of rows does not matter.
第一范式 (1NF) 要求每个单元格包含原子（不可再分）值，列包含单一类型的值，每行唯一（存在主键），且行的顺序无关紧要。
Second Normal Form (2NF) builds on 1NF by removing partial dependencies. A table is in 2NF if it is in 1NF and all non-key attributes depend fully on the entire primary key (no partial dependency on a composite primary key).
第二范式 (2NF) 在 1NF 的基础上消除了部分依赖。如果一个表满足 1NF 并且所有非键属性完全依赖于整个主键（没有对复合主键的部分依赖），则该表属于 2NF。
Third Normal Form (3NF) requires that the table is in 2NF and there are no transitive dependencies. A non-key attribute must not depend on another non-key attribute; all non-key attributes must depend solely on the primary key.
第三范式 (3NF) 要求表满足 2NF 并且不存在传递依赖。一个非键属性不能依赖于另一个非键属性；所有非键属性必须仅依赖于主键。
6. SQL Data Definition Language (DDL) | SQL 数据定义语言
DDL commands define and modify the structure of database objects. The main statements are CREATE, ALTER, and DROP.
DDL 命令定义和修改数据库对象的结构。主要语句有 CREATE、ALTER 和 DROP。
To create a new table named Student: CREATE TABLE Student ( StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, DOB DATE );. This defines columns, data types, and constraints such as PRIMARY KEY and NOT NULL.
创建一个名为 Student 的新表：CREATE TABLE Student ( StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, DOB DATE ); 这定义了列、数据类型以及如 PRIMARY KEY 和 NOT NULL 这样的约束。
ALTER TABLE can add or drop columns, e.g. ALTER TABLE Student ADD Email VARCHAR(100);. DROP TABLE Student; deletes the whole table and its structure.
ALTER TABLE 可以添加或删除列，例如 ALTER TABLE Student ADD Email VARCHAR(100);。 DROP TABLE Student; 会删除整个表及其结构。
7. SQL Data Manipulation Language (DML) | SQL 数据操作语言
DML statements handle data within tables: SELECT to query, INSERT to add rows, UPDATE to modify existing data, and DELETE to remove rows.
DML 语句处理表中的数据：SELECT 用于查询，INSERT 用于添加行，UPDATE 用于修改现有数据，DELETE 用于删除行。
Example: SELECT Name, DOB FROM Student WHERE StudentID = 101; retrieves specific columns for a given ID. INSERT INTO Student (StudentID, Name) VALUES (102, 'Alice'); adds a new row.
例如：SELECT Name, DOB FROM Student WHERE StudentID = 101; 检索给定 ID 的特定列。 INSERT INTO Student (StudentID, Name) VALUES (102, 'Alice'); 添加新行。
UPDATE Student SET Email = 'alice@example.com' WHERE StudentID = 102; modifies data, and DELETE FROM Student WHERE StudentID = 102; removes the row. Always use a WHERE clause to avoid affecting all rows.
UPDATE Student SET Email = 'alice@example.com' WHERE StudentID = 102; 修改数据，DELETE FROM Student WHERE StudentID = 102; 删除行。务必使用 WHERE 子句以避免影响所有行。
8. SQL Queries with JOINs | 带连接的SQL查询
JOIN operations combine rows from two or more tables based on a related column. INNER JOIN returns rows where the join condition matches in both tables. Syntax: SELECT * FROM Orders INNER JOIN Customers ON Orders.CustID = Customers.CustID;
JOIN 操作根据相关列合并两个或多个表中的行。INNER JOIN 返回两个表中连接条件匹配的行。语法：SELECT * FROM Orders INNER JOIN Customers ON Orders.CustID = Customers.CustID;
LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right table; unmatched right-side columns become NULL. RIGHT JOIN works similarly from the right table.
LEFT JOIN（或 LEFT OUTER JOIN）返回左表的所有行以及右表中匹配的行；未匹配的右侧列变为 NULL。RIGHT JOIN 以类似方式从右表操作。
Joins are essential for normalised databases where information is spread across multiple tables. Always specify the ON condition clearly to avoid Cartesian products.
连接对于信息分布在多个表中的规范化数据库至关重要。务必清楚指定 ON 条件以避免笛卡尔积。
9. Database Integrity and Constraints | 数据库完整性与约束
Data integrity ensures accuracy and consistency. Entity integrity requires that every table has a primary key, and no primary key value is NULL. Referential integrity ensures foreign keys reference valid primary keys.
数据完整性确保准确性和一致性。实体完整性要求每个表都有一个主键，且主键值不能为 NULL。引用完整性确保外键引用了有效的主键。
Domain integrity restricts the allowed values for a column via data types, NOT NULL, UNIQUE, CHECK, and DEFAULT constraints. For example, CHECK (Age >= 18) enforces a business rule.
域完整性通过数据类型、NOT NULL、UNIQUE、CHECK 和 DEFAULT 约束来限制列允许的值。例如，CHECK (Age >= 18) 强制执行一条业务规则。
CASCADE options on foreign keys can automate referential actions: ON DELETE CASCADE removes child rows when the parent row is deleted, maintaining integrity.
外键上的 CASCADE 选项可以自动执行引用操作：ON DELETE CASCADE 在删除父行时删除子行，从而保持完整性。
10. Indexing and Performance | 索引与性能
An index is a data structure that speeds up data retrieval on a column. It works like a book’s index, allowing the DBMS to find rows quickly without scanning the entire table. A primary key is automatically indexed.
索引是一种数据结构，可加快对列的检索。它就像一本书的索引，使 DBMS 能够快速找到行而无需扫描整个表。主键会自动创建索引。
However, indexes also slow down write operations (INSERT, UPDATE, DELETE) because the index must be updated. Therefore, index only the columns frequently used in WHERE, JOIN, or ORDER BY clauses.
但是，索引也会减慢写操作（INSERT、UPDATE、DELETE）的速度，因为索引必须更新。因此，只应索引那些经常在 WHERE、JOIN 或 ORDER BY 子句中使用的列。
Multiple indexes can be created on a table, each for different query patterns. A composite index on (A, B) can accelerate queries that filter by A or both A and B, but not B alone.
可以在一个表上创建多个索引，每个索引用于不同的查询模式。针对 (A, B) 的复合索引可以加速按 A 或同时按 A 和 B 过滤的查询，但不能加速仅按 B 过滤的查询。
11. Database Management Systems (DBMS) | 数据库管理系统
A DBMS is the software that interfaces between users and the database, handling storage, retrieval, security, backup, and concurrency control. Popular relational DBMSs include MySQL, PostgreSQL, and Microsoft SQL Server.
DBMS 是连接用户与数据库的软件，处理存储、检索、安全、备份和并发控制。流行的关系型 DBMS 包括 MySQL、PostgreSQL 和 Microsoft SQL Server。
Concurrency control ensures that simultaneous transactions do not corrupt data. Techniques such as locking (shared and exclusive locks), timestamps, and multiversion concurrency control (MVCC) are used.
并发控制确保同时进行的事务不会损坏数据。使用了诸如锁定（共享锁和排他锁）、时间戳和多版本并发控制 (MVCC) 等技术。
ACID (Atomicity, Consistency, Isolation, Durability) properties guarantee reliable transaction processing. Atomicity means a transaction either fully completes or is entirely rolled back; consistency preserves database rules.
ACID（原子性、一致性、隔离性、持久性）属性保证了可靠的事务处理。原子性意味着事务要么完全完成，要么完全回滚；一致性保留了数据库规则。
12. Summary of Key Exam Points | 考试要点总结
In A-Level examinations, you may be asked to design ERDs, normalise a table to 3NF, write SQL DDL/DML with correct syntax, and explain concepts like referential integrity and indexing. Always justify your design decisions.
在A-Level考试中，你可能会被要求设计 ERD、将表规范化到 3NF、使用正确的语法编写 SQL DDL/DML，以及解释诸如引用完整性和索引等概念。务必证明你的设计决策是合理的。
Practice identifying partial and transitive dependencies, converting many-to-many relationships into junction tables, and explaining the trade-offs of denormalisation. Master the difference between DDL and DML, and know the standard SQL clauses order: SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY.
练习识别部分依赖和传递依赖，将多对多关系转换为联结表，并解释反规范化的权衡。掌握 DDL 和 DML 的区别，并了解标准 SQL 子句的顺序：SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY。
Remember that a well-normalised database reduces data duplication but may require more joins; performance must be balanced with data integrity. Use index strategies wisely and always consider the effects on CRUD operations.
请记住，规范化的数据库减少了数据重复，但可能需要更多的连接；必须在性能与数据完整性之间取得平衡。明智地使用索引策略，并始终考虑对 CRUD 操作的影响。
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课程辅导，国外大学本科硕士研究生博士课程论文辅导