U 9 - Database

Chapter 9: Databases

Overview

This chapter focuses on the fundamentals of single-table databases, covering their design, structure, and usage. It introduces key concepts such as fields, records, data types, primary keys, and SQL (Structured Query Language) for querying data. 本章重点介绍单表数据库的基础知识，涵盖其设计、结构和用法。它介绍了字段、记录、数据类型、主键和用于查询数据的 SQL（结构化查询语言）等关键概念。

Definition: A database is a structured collection of data that facilitates information extraction. 数据库是便于提取信息的结构化数据集合。

Single-table databases: The chapter emphasizes only single-table databases for IGCSE, which consist of one table containing records.

A database is defined as a structured collection of data that allows users to store, retrieve, and manage information efficiently. The primary purpose of a database is to facilitate the extraction of information in a way that meets the specific needs of users or applications. Databases are essential in various fields, including business, healthcare, education, and research, as they enable organizations to maintain vast amounts of data systematically. 数据库被定义为结构化的数据集合，允许用户高效地存储、检索和管理信息。数据库的主要目的是方便以符合用户或应用程序特定需求的方式提取信息。数据库在商业、医疗保健、教育和研究等各个领域都必不可少，因为它们使组织能够系统地维护大量数据。

• Structured Collection: Data in a database is organized in a specific format, which makes it easier to access and manipulate. This structure typically involves tables, which consist of rows (records) and columns (fields), allowing for a clear representation of data relationships. 数据库中的数据以特定格式组织，以便于访问和操作。这种结构通常涉及表，表由行（记录）和列（字段）组成，可以清晰地表示数据关系。

Single-Table Databases

In the context of the IGCSE Computer Science curriculum, the chapter focuses exclusively on single-table databases. This type of database consists of one table that contains all the relevant records pertaining to a specific subject or entity.

• Characteristics:

  • One Table: A single-table database contains only one table, which simplifies the design and management of the database, making it suitable for basic applications.

  • Records and Fields: Each record in the table represents a single entry or instance of the data being stored (e.g., a single patient record), while each field corresponds to a specific attribute or property of that record (e.g., the patient's name, admission date, or consultant). 表中的每个记录代表所存储数据的单个条目或实例（例如，单个患者记录），而每个字段对应于该记录的特定属性或特性（例如，患者的姓名、入院日期或顾问）。

• Purpose and Use Cases: Single-table databases are particularly useful for small-scale applications where data complexity is minimal. Common use cases include:

  • Patient Records: Storing information about hospital patients, including their names, admission dates, and ward numbers.

  • Library Catalogs: Maintaining a list of books, where each entry includes fields like title, author, and ISBN.

  • Event Registrations: Tracking participants in events, where records include names, contact information, and registration dates.

    Advantages:

• • Simplicity: The straightforward design of single-table databases makes them easier to understand and manage, especially for beginners.

  • Efficiency: For applications with limited data requirements, single-table databases can perform well without the overhead of managing multiple tables and complex relationships.

     Limitations: While single-table databases are advantageous for their simplicity, they may not be suitable for more complex applications that require:

• • Data Relationships: Handling multiple related entities often necessitates a multi-table relational database structure.

  • Scalability: As data volume grows, single-table databases can become unwieldy, leading to challenges in data management and retrieval.

Key Terms and definition

Table

Definition: A collection of related records in a database. 数据库中相关记录的集合。

• Explanation: Tables are organized into rows and columns, where each row (record) represents a specific entry and each column (field) represents an attribute of that entry.

Record

Definition: A collection of fields that describe one item, person, or event. A collection of fields that describe one item, person, or event. 描述一个项目、人员或事件的字段集合。 描述一个项目、人员或事件的字段集合。

• Explanation: Each record in a table corresponds to a single entity, such as a patient or a book, and contains all relevant details about that entity.

Field

Definition: A single piece of data within a record, representing a specific attribute. 记录中的单个数据，代表特定属性。

• Explanation: Fields are organized into columns in a table and can be named meaningfully to indicate the type of data they hold (e.g., "FirstName," "DateOfAdmission").

Primary Key

Definition: A field in a database that uniquely identifies a record. 数据库中唯一标识一条记录的字段。

• Explanation: The primary key must contain unique values for each record, ensuring that no two records have the same identifier. This allows for reliable data retrieval.

Data Type

Definition: A classification that specifies the type of data that can be stored in a field. 指定可存储在字段中的数据类型的分类。

• Explanation: Data types dictate how data is stored, displayed, and the operations that can be performed on it. Common data types include text, integer, Boolean, real, and date/time.

Validation

Definition: The process of checking the accuracy and quality of data before it is entered into a database. 在数据输入数据库之前检查其准确性和质量的过程。

• Explanation: Validation ensures that the data complies with specific rules and formats (e.g., a ward number must be between 1 and 10) to maintain data integrity.

SQL (Structured Query Language)

Definition: The standard language used for querying and manipulating databases. 用于查询和操作数据库的标准语言。

• Explanation: SQL allows users to perform operations such as retrieving data (SELECT), filtering records (WHERE), and sorting results (ORDER BY).

Query

Definition: A request for data or information from a database. 从数据库请求数据或信息。

• Explanation: Queries are written in SQL and can specify conditions to filter results based on certain criteria.

Aggregate Functions

Definition: Functions that perform a calculation on a set of values and return a single value. 对一组值执行计算并返回单个值的函数。

• Explanation: Common aggregate functions include COUNT (to count records) and SUM (to calculate the total of a numeric field).

Condition

Definition: A specific criterion used in queries to filter records. 查询中用来过滤记录的特定标准。

• Explanation: Conditions are often used in the WHERE clause of SQL statements to specify which records to include based on field values (e.g., WHERE Consultant = 'Mr Smith').

Sort Order

Definition: The arrangement of query results based on specified criteria. 根据指定的条件对查询结果进行排列。

• Explanation: Sort order can be ascending or descending, depending on how the data needs to be presented (e.g., ORDER BY FamilyName).

Data Types

Text/Alphanumeric

Definition: A data type that can store a combination of letters and numbers. 可以存储字母和数字组合的数据类型。

Explanation: This type is used for fields that may include both characters and numbers, such as names or descriptions. It can vary in length, allowing for flexibility in data entry. 此类型用于可能包含字符和数字的字段，例如名称或描述。其长度可以变化，从而允许灵活地输入数据。

• Example: A field named BookTitle might store values like "The Great Gatsby" or "1984".

Character

Definition: A data type that stores a single character. 存储单个字符的数据类型。

Explanation: This is typically used for fields that only require a single character input, such as gender indicators (e.g., 'M' for male, 'F' for female). 这通常用于只需要输入单个字符的字段，例如性别指示符（例如，“M”代表男性，“F”代表女性）。

• Example: A field named Gender could store values like 'M' or 'F'.

Boolean

Definition: A data type that can hold one of two possible values: True or False. 可以保存两个可能值之一的数据类型：True 或 False。

Explanation: This type is useful for fields that require binary options, such as yes/no selections. 此类型对于需要二进制选项的字段很有用，例如是/否选择。

• Example: A field named IsActive might store values like True (indicating the record is active) or False (indicating it's not).

Integer

Definition: A data type for whole numbers (no decimal places). 整数（无小数）的数据类型。

Explanation: This type is used for fields that require numerical values without fractions, suitable for counting or identifying. 此类型用于需要无分数的数值的字段，适合计数或识别。

• Example: A field named WardNumber might store integer values like 1, 2, or 3.

Real

Definition: A data type for numbers that may include decimal points. 可能包含小数点的数字数据类型。

Explanation: This type is used for fields that require precise values, such as measurements, prices, or ratings. 此类型用于需要精确值的字段，例如测量值、价格或评级。

• Example: A field named PatientWeight could store values like 70.5 (indicating a weight of 70.5 kg).

Date/Time

Definition: A data type used to store dates and/or times. 用于存储日期和/或时间的数据类型。

Explanation: This type is essential for fields that require tracking of specific dates or time instances, facilitating chronological data management. 对于需要跟踪特定日期或时间实例的字段来说，此类型至关重要，有助于按时间顺序管理数据。

• Example: A field named DateOfAdmission might store values like '2022-10-12' to indicate when a patient was admitted. 名为 DateOfAdmission 的字段可能会存储诸如“2022-10-12”之类的值来指示患者入院的时间。

SQL Queries 

SELECT

Definition: The SELECT statement is used to fetch specific fields (columns) from a table. SELECT 语句用于从表中获取特定字段（列）。

Explanation: This command allows users to specify which columns they want to see in the results of their query. It retrieves data from one or more fields in a table. 此命令允许用户指定他们想要在查询结果中看到的列。它从表中的一个或多个字段中检索数据。

Example:

SELECT FirstName, FamilyName FROM PATIENT;

This query retrieves the first and family names of all records in the PATIENT table. 此查询检索 PATIENT 表中所有 records 的 first 和 family names。

FROM

Definition: The FROM clause identifies the table from which to retrieve data. FROM 子句标识从中检索数据的表。

Explanation: It is used in conjunction with the SELECT statement to specify the source of the data. 它与 SELECT 语句一起使用来指定数据的来源。

Example:

SELECT * FROM PATIENT;

This query selects all fields from the PATIENT table. 此查询从 PATIENT table 中选择所有 fields。

WHERE

Definition: The WHERE clause filters records based on specified conditions.

Explanation: It restricts the results to only those records that meet the specified condition, allowing for targeted data retrieval. 它将结果限制为仅满足指定条件的记录，从而允许有针对性地检索数据。

Example:

SELECT * FROM PATIENT WHERE Consultant = 'Mr Smith';
This query fetches all records of patients whose consultant is Mr. Smith. 此查询获取顾问为Mr. Smith 的患者的所有记录。

ORDER BY

Definition: The ORDER BY clause sorts the results of a query by specified fields. 定义：ORDER BY 子句按指定的字段对查询结果进行排序。

Explanation: It can arrange the results in ascending or descending order based on one or more columns. 它可以根据一列或多列按升序或降序排列结果。

Example:
SELECT * FROM PATIENT ORDER BY FamilyName ASC;

This query retrieves all records from the PATIENT table and sorts them alphabetically by family name. 此查询从 PATIENT 表中检索所有记录，并按 family name 的字母顺序排序。ASC will have to be DESC if you want to sort to descending order. 如果要按降序排序，则 ASC 必须是 DESC。

SUM

Definition: The SUM function calculates the total of a specified numeric field. SUM 函数计算指定数字字段的总和。

Explanation: It is often used in conjunction with the SELECT statement to aggregate numeric data. 它通常与 SELECT 语句结合使用来聚合数字数据。

Example:
SELECT SUM(BedNumber) FROM PATIENT;

This query calculates the total of all bed numbers in the PATIENT table. 此查询计算 PATIENT 表中所有床位号的总和。

COUNT

Definition: The COUNT function counts the number of records that meet a specified condition. COUNT 函数计算满足指定条件的记录数。

Explanation: It is useful for determining how many entries exist in a table or how many records meet certain criteria. 它对于确定表中存在多少个条目或有多少条记录符合特定条件很有用。

Example:
SELECT COUNT(*) FROM PATIENT WHERE WardNumber = 6;

This query counts how many patients are in ward number 6. 此查询计算 6 号病房中有多少名患者。

Questions  from past papers

0478_s24_qp_22

0478_s24_qp_23

0478/21/O/N/23 

0478_w23_qp_22

0478_w23_qp_23