A-Level Computer Science: Database Fundamentals and Applications | A-Level计算机：数据库基础知识与应用

📚 A-Level Computer Science: Database Fundamentals and Applications | A-Level计算机：数据库基础知识与应用

A database is an organised collection of data that can be easily accessed, managed and updated. In computer science, databases are essential for storing large amounts of information efficiently, enabling users to retrieve, modify and analyse data in a structured way.

数据库是一种有组织的数据集合，可以方便地访问、管理和更新。在计算机科学中，数据库对于高效存储大量信息至关重要，它使用户能够以结构化方式检索、修改和分析数据。

1. The Concept of a Database | 数据库的概念

A database is a shared collection of logically related data, designed to meet the information needs of multiple users within an organisation. Unlike a simple file system, a database provides a centralised, controlled environment where data is stored independently of applications.

数据库是逻辑相关数据的共享集合，旨在满足组织内多个用户的信息需求。与简单的文件系统不同，数据库提供了一个集中且受控的环境，数据在其中独立于应用程序存储。

Data integrity, security and reduced redundancy are key advantages of using databases. Through a database management system (DBMS), users can define, manipulate and query data without needing to know its physical storage details.

数据完整性、安全性和减少冗余是使用数据库的关键优势。通过数据库管理系统（DBMS），用户可以在无需了解物理存储细节的情况下定义、操作和查询数据。

2. Types of Databases | 数据库的类型

Several types of databases exist, each suited to different needs. The relational database is the most widely used in A-Level Computer Science. Other types include hierarchical, network, object-oriented and NoSQL databases.

存在多种类型的数据库，每种适应不同需求。关系数据库是A-Level计算机科学中最广泛使用的。其他类型包括层次型、网络型、面向对象型和NoSQL数据库。

Relational database – stores data in tables (relations) with rows and columns.
关系型数据库 – 以表格（关系）形式存储数据，包含行和列。

Hierarchical database – organises data in a tree-like structure where each record has a single parent.
层次型数据库 – 以树状结构组织数据，每个记录只有一个父节点。

Network database – allows each record to have multiple parents, creating a graph structure.
网络型数据库 – 允许每个记录有多个父节点，形成图结构。

Object-oriented database – stores data as objects, supporting encapsulation and inheritance.
面向对象型数据库 – 以对象形式存储数据，支持封装和继承。

NoSQL database – designed for large-scale distributed data, often using key-value pairs or document stores.
NoSQL数据库 – 专为大规模分布式数据设计，通常使用键值对或文档存储。

3. The Relational Model | 关系模型

A relational database organises data into tables called relations. Each relation consists of rows (records or tuples) and columns (attributes or fields). A table represents an entity type, such as Student, Course or Teacher.

关系数据库将数据组织成称为关系的表。每个关系由行（记录或元组）和列（属性或字段）组成。一个表表示一种实体类型，例如学生、课程或教师。

Relation = Table | Tuple = Row | Attribute = Column

Keys are crucial in a relational database. A primary key uniquely identifies each row in a table, while a foreign key links tables together by referencing a primary key in another table.

键在关系数据库中至关重要。主键唯一标识表中的每一行，而外键通过引用另一个表中的主键来连接不同表。

For example, a Student table may have StudentID as its primary key. A Course table may include StudentID as a foreign key to show which student is enrolled in which course.

例如，学生表可以将StudentID设为主键。课程表可以包含StudentID作为外键，以显示哪个学生选修了哪门课程。

4. Keys and Relationships | 键与关系

Keys define the structure of relationships between tables. The main types of keys include primary, foreign, candidate and composite keys.

键定义了表之间关系的结构。主要键类型包括主键、外键、候选键和复合键。

Primary key – a unique identifier for each record in a table.
主键 – 表中每条记录的唯一标识符。

Foreign key – a field in one table that refers to the primary key in another table.
外键 – 一个表中的字段，引用另一个表中的主键。

Candidate key – a potential primary key; all values are unique and non-null.
候选键 – 可能成为主键的字段；所有值均为唯一且非空。

Composite key – a key made up of two or more fields that together uniquely identify a record.
复合键 – 由两个或更多字段组成，共同唯一标识一条记录。

Relationships between tables can be one-to-one (1:1), one-to-many (1:N) or many-to-many (M:N). For example, one teacher may teach many students (1:N), but one student may also be taught by many teachers (M:N).

表之间的关系可以是一对一（1:1）、一对多（1:N）或多对多（M:N）。例如，一位教师可以教许多学生（1:N），但一个学生也可以被许多教师教（M:N）。

5. Structured Query Language (SQL) – DDL | 结构化查询语言 – 数据定义语言

SQL is the standard language used to interact with relational databases. It is divided into two main parts: Data Definition Language (DDL) and Data Manipulation Language (DML).

SQL是与关系数据库交互的标准语言。它分为两大部分：数据定义语言（DDL）和数据操纵语言（DML）。

DDL statements create, alter and delete database structures. The most common commands are CREATE, ALTER and DROP.

DDL语句用于创建、修改和删除数据库结构。最常用的命令是CREATE、ALTER和DROP。

CREATE TABLE Student (StudentID VARCHAR(10) PRIMARY KEY, Name VARCHAR(50), Age INT);

This command creates a table called Student with three columns. The PRIMARY KEY constraint ensures that StudentID is unique and not null.

此命令创建一个名为Student的表，包含三列。PRIMARY KEY约束确保StudentID唯一且非空。

6. SQL – DML | SQL – 数据操纵语言

DML statements are used to retrieve, insert, update and delete data within tables. The four main commands are SELECT, INSERT, UPDATE and DELETE.

DML语句用于在表中检索、插入、更新和删除数据。四个主要命令是SELECT、INSERT、UPDATE和DELETE。

SELECT Name FROM Student WHERE Age > 16;

This query selects the Name column from all records where the Age is greater than 16. The WHERE clause is used to filter records based on a condition.

此查询从所有Age大于16的记录中选取Name列。WHERE子句用于基于条件过滤记录。

To insert a new record, the INSERT INTO command is used. The UPDATE command modifies existing data, and DELETE removes records. For example:

要插入新记录，使用INSERT INTO命令。UPDATE命令修改现有数据，DELETE删除记录。例如：

INSERT INTO Student VALUES (‘S001’, ‘Alice’, 17);

UPDATE Student SET Age = 18 WHERE StudentID = ‘S001’;

DELETE FROM Student WHERE StudentID = ‘S001’;

These commands allow applications to manage data dynamically while preserving database structure.

这些命令使应用程序能够动态管理数据，同时保持数据库结构。

7. Database Normalisation | 数据库规范化

Normalisation is the process of organising data to reduce redundancy and improve integrity. The three main normal forms are First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF).

规范化是组织数据以减少冗余并提高完整性的过程。三种主要范式是第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。

First Normal Form (1NF) – a table is in 1NF if all attributes contain atomic values, and each record is unique.

第一范式（1NF） – 如果所有属性包含原子值，且每条记录唯一，则表处于1NF。

Each column must contain a single value, not a list of values.
每一列必须包含单个值，而不是值列表。

Non-normalised: StudentID → {Module1, Module2, Module3}

Normalised (1NF): separate rows for each module

Second Normal Form (2NF) – the table must already be in 1NF, and all non-key attributes must be fully functionally dependent on the primary key.

第二范式（2NF） – 表必须已经处于1NF，且所有非键属性必须完全函数依赖于主键。

Third Normal Form (3NF) – the table must already be in 2NF, and no transitive dependency exists between non-key attributes.

第三范式（3NF） – 表必须已经处于2NF，且非键属性之间不存在传递依赖。

8. Transactions and ACID Properties | 事务与ACID特性

A transaction is a sequence of operations performed as a single logical unit of work. Transactions must follow the ACID properties to ensure reliable database operations.

事务是作为单个逻辑工作单元执行的一组操作序列。事务必须遵循ACID特性，以确保数据库操作的可靠性。

Atomicity	All operations in a transaction are completed successfully, or none are applied.
原子性	事务中的所有操作要么全部成功完成，要么全部不应用。
Consistency	A transaction transforms the database from one consistent state to another.
一致性	事务将数据库从一个一致状态转换为另一个一致状态。
Isolation	Concurrent transactions do not interfere with each other.
隔离性	并发事务之间互不干扰。
Durability	Once a transaction is committed, changes persist even after a system failure.
持久性	一旦事务提交，即使系统发生故障，更改也会持久保存。

For example, when a student transfers from one course to another, the database must update both the old and new course records atomically. If one update fails, the entire transaction is rolled back.

例如，当一个学生从一门课程转到另一门课程时，数据库必须原子地更新旧课程和新课程记录。如果一次更新失败，整个事务将被回滚。

9. Entity-Relationship (ER) Modelling | 实体-联系（ER）建模

An entity-relationship diagram (ERD) is a visual model used to represent the structure of a database before implementation. Entities are objects or concepts, and relationships describe how entities interact.

实体-联系图（ERD）是一种可视化模型，用于在实现前表示数据库的结构。实体是对象或概念，关系描述了实体之间的相互作用。

In an ER diagram, entities are usually drawn as rectangles, attributes as ovals, and relationships as diamonds. Cardinality (1:1, 1:N, M:N) is shown on the connecting lines.

在ER图中，实体通常用矩形表示，属性用椭圆表示，关系用菱形表示。基数（1:1、1:N、M:N）显示在连接线上。

Student (Entity) — Enrols (Relationship) — Course (Entity)

This model helps database designers identify tables, keys and relationships early, reducing errors and redundancy in the final database.

这种模型帮助数据库设计者提前识别表、键和关系，减少最终数据库中的错误和冗余。

10. Database Management Systems (DBMS) | 数据库管理系统

A DBMS is software that controls the storage, retrieval, deletion and security of data. Examples include MySQL, Microsoft Access and Oracle. The DBMS provides many important functionalities.

DBMS是控制数据存储、检索、删除和安全的软件。例如MySQL、Microsoft Access和Oracle。DBMS提供了许多重要功能。

Data definition – creating and modifying table structures through DDL.
数据定义 – 通过DDL创建和修改表结构。

Data manipulation – inserting, updating, deleting and querying data.
数据操纵 – 插入、更新、删除和查询数据。

Security management – controlling user access through passwords and permissions.
安全管理 – 通过密码和权限控制用户访问。

Backup and recovery – restoring databases after data loss or corruption.
备份与恢复 – 在数据丢失或损坏后恢复数据库。

DBMS also provides a query language (typically SQL) and concurrency control to support simultaneous users. This makes it a fundamental tool for modern information systems.

DBMS还提供查询语言（通常是SQL）和并发控制，以支持多用户同时访问。这使其成为现代信息系统的基本工具。

11. Database Security and Integrity | 数据库安全性与完整性

Protecting data from unauthorised access and ensuring its correctness are key concerns in database management. Physical security, access controls and backup procedures are all part of a robust security strategy.

保护数据免遭未授权访问并确保其正确性是数据库管理的核心问题。物理安全、访问控制和备份程序都是健全安全策略的一部分。

Data integrity is maintained through constraints such as primary keys, foreign keys, unique constraints and checks. For example, a CHECK constraint can ensure that an age value is never negative.

数据完整性通过主键、外键、唯一约束和检查等约束来维护。例如，CHECK约束可以确保年龄值永不为负。

CHECK (Age >= 0)

Encryption, user authentication and audit trails further enhance security. Regular backups ensure that data can be recovered after hardware failures or cyber attacks.

加密、用户身份验证和审计跟踪进一步增强了安全性。定期备份确保在硬件故障或网络攻击后可以恢复数据。

12. Real-World Applications of Databases | 数据库的实际应用

Databases are used in nearly every domain of modern life. Banks rely on databases to process transactions and manage customer accounts. Schools use databases to store student records, timetables and examination results.

数据库几乎应用于现代生活的各个领域。银行依赖数据库处理交易和管理客户账户。学校使用数据库存储学生记录、课程表和学习成绩。

E-commerce platforms such as Amazon use databases to track products, customers and orders. In healthcare, hospitals store patient records in secure database systems, allowing doctors to access relevant information quickly.

像Amazon这样的电子商务平台使用数据库跟踪产品、客户和订单。在医疗保健领域，医院将患者记录存储在安全的数据库系统中，使医生能够快速访问相关信息。

Social media networks, transport systems and government agencies all rely on databases for efficient data management. As data continues to grow exponentially, the importance of well-designed databases becomes ever greater.

社交媒体网络、交通系统和政府机构都依赖数据库进行高效的数据管理。随着数据呈指数级增长，设计良好的数据库的重要性日益凸显。

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

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

A-Level Computer Science: Database Fundamentals and Applications | A-Level计算机：数据库基础知识与应用