slug
type
status
category
summary
date
tags
password
icon
8 Databases
8 数据库
8.1 Database Concepts
8.1 数据库概念
Candidates should be able to:
考生应能够:
Show understanding of the limitations of using a filebased approach for the storage and retrieval of data
理解使用基于文件的方法存储和检索数据的局限性
Describe the features of a relational database that address the limitations of a file-based approach
描述关系数据库的特点,解决基于文件方法的局限性
Show understanding of and use the terminology associated with a relational database model
理解并使用与关系数据库模型相关的术语
Use an entity-relationship (E-R) diagram to document a database design
使用实体-关系(E-R)图来记录数据库设计
Show understanding of the normalisation process
理解规范化过程
Explain why a given set of database tables are, or are not, in 3NF
解释为什么给定的数据库表集合处于,或不处于,第三范式(3NF)
Produce a normalised database design for a description of a database, a given set of data, or a given set of tables
为数据库描述、给定的数据集或给定的表集合生成一个规范化的数据库设计
8.2 Database Management Systems (DBMS)
8.2 数据库管理系统(DBMS)
Candidates should be able to:
考生应能够:
Show understanding of the features provided by a Database Management System (DBMS) that address the issues of a file based approach
理解数据库管理系统(DBMS)提供的功能,解决基于文件方法的问题
Show understanding of how software tools found within a DBMS are used in practice
理解数据库管理系统(DBMS)中的软件工具在实际中如何使用
8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)
8.3 数据定义语言(DDL)和数据操纵语言(DML)
Candidates should be able to:
考生应能够:
Show understanding that the DBMS carries out all creation/modification of the database structure using its Data Definition Language (DDL)
理解DBMS使用其数据定义语言(DDL)执行数据库结构的所有创建/修改
Show understanding that the DBMS carries out all queries and maintenance of data using its DML
理解DBMS使用其数据操纵语言(DML)执行所有查询和数据维护
Show understanding that the industry standard for both DDL and DML is Structured Query Language (SQL)
理解DDL和DML的行业标准是结构化查询语言(SQL)
Understand given SQL (DDL) statements and be able to write simple SQL (DDL) statements using a sub-set of statements
理解给定的SQL(DDL)语句,并能够使用一组子语句编写简单的SQL(DDL)语句
Write an SQL script to query or modify data (DML) which are stored in (at most two) database tables
编写SQL脚本来查询或修改存储在(最多两个)数据库表中的数据
This outline covers a detailed overview of database concepts, database management systems (DBMS), and essential database languages (DDL and DML). Let's dive deeper into each topic:
8.1 Database Concepts
Limitations of a File-Based Approach
A file-based approach organizes data in separate files, often leading to challenges in scalability and consistency. Key limitations include:
- Data Redundancy and Inconsistency: Data is duplicated across multiple files, leading to inconsistency and increased storage requirements.
- Data Isolation: Retrieving related data spread across multiple files can be challenging.
- Limited Data Integrity: Maintaining rules to ensure data accuracy (like foreign keys) is hard to enforce.
- Concurrency Issues: File-based systems struggle with multiple users accessing data simultaneously.
- Security and Access Control: Difficulties arise in implementing robust access controls for sensitive data.
Features of a Relational Database Addressing File-Based Limitations
Relational databases solve many of the issues inherent in file-based systems:
- Data Consistency: By centralizing data, relational databases reduce redundancy.
- Data Integrity: Constraints like primary keys and foreign keys maintain data accuracy.
- Efficient Data Access: SQL enables complex querying, reducing the difficulty of retrieving related data.
- Concurrency Control: Databases can handle multiple users with mechanisms to manage simultaneous access.
- Enhanced Security: Access controls at the table or even column level help secure data.
Relational Database Terminology
Key terms include:
- Table (Relation): A collection of rows and columns representing an entity.
- Field (Attribute): A column in a table.
- Record (Tuple): A single row in a table.
- Primary Key: A unique identifier for records in a table.
- Foreign Key: A field linking to a primary key in another table, ensuring referential integrity.
Entity-Relationship (E-R) Diagrams
An E-R diagram represents entities (tables) and their relationships, often illustrating:
- Entities: Represent tables in the database.
- Attributes: Represent columns in the table.
- Relationships: Show how entities relate to each other, often with cardinality notations (e.g., one-to-many).
Normalisation Process
Normalization organizes data to reduce redundancy and improve integrity, typically through steps:
- First Normal Form (1NF): Ensures data is atomic, with each field containing a single value.
- Second Normal Form (2NF): Removes partial dependencies for tables with composite keys.
- Third Normal Form (3NF): Eliminates transitive dependencies, ensuring each attribute depends only on the primary key.
Third Normal Form (3NF) Validation
To determine if a set of tables meets 3NF requirements:
- No Partial Dependencies: For tables with a composite primary key, each attribute depends on the full primary key.
- No Transitive Dependencies: Attributes depend directly on the primary key, not other non-key attributes.
Producing a Normalized Database Design
Based on a data description or tables, one would:
- Identify and correct redundancies.
- Apply normalization steps (1NF to 3NF) to create a design that organizes data logically and consistently.
8.2 Database Management Systems (DBMS)
Features of a DBMS Addressing File-Based Issues
A DBMS provides functions to address the limitations of file-based approaches:
- Data Independence: Separates data storage from application logic.
- Efficient Query Processing: Optimizes retrieval and manipulation of data.
- Transaction Management: Ensures that multiple operations complete successfully together.
- Backup and Recovery: Helps restore data in case of system failures.
- Access Control: Manages permissions for users at various levels of access.
Practical Use of DBMS Tools
Common DBMS tools and their uses:
- Query Tools: Simplify data retrieval with SQL.
- Form and Report Generators: Provide user-friendly data input and output interfaces.
- Backup Utilities: Automate data backup and restore processes.
- Security Management Tools: Help assign roles and permissions to protect data.
8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)
DDL and DML in DBMS
- DDL (Data Definition Language): Manages database structure with statements like
CREATE
,ALTER
, andDROP
.
- DML (Data Manipulation Language): Manages data within tables using commands like
SELECT
,INSERT
,UPDATE
, andDELETE
.
SQL as the Industry Standard
Structured Query Language (SQL) is the standard for both DDL and DML, widely used across various DBMSs for consistency and compatibility.
Writing Basic SQL (DDL) Statements
Examples:
- Create Table:
CREATE TABLE Student (StudentID INT, Name VARCHAR(50), Age INT);
- Alter Table:
ALTER TABLE Student ADD Address VARCHAR(100);
- Drop Table:
DROP TABLE Student;
Writing SQL Scripts to Query or Modify Data (DML)
- Select Query:
SELECT Name, Age FROM Student WHERE Age > 18;
- Insert Data:
INSERT INTO Student (StudentID, Name, Age) VALUES (1, 'Alice', 20);
- Update Data:
UPDATE Student SET Age = 21 WHERE StudentID = 1;
This comprehensive foundation covers the fundamental concepts, terminology, and practical applications in databases essential for the CIE Computer Science exam.
以下是数据库概念、数据库管理系统(DBMS)和数据定义语言(DDL)与数据操纵语言(DML)的详细介绍:
8.1 数据库概念
使用基于文件的方法的局限性
基于文件的方法通过分散的文件组织数据,存在扩展性和一致性方面的问题,主要局限包括:
- 数据冗余和不一致:数据在多个文件中重复,导致数据不一致并增加存储需求。
- 数据隔离:分散在多个文件中的相关数据难以检索。
- 有限的数据完整性:难以维护数据准确性(例如外键)规则。
- 并发问题:基于文件的系统在多用户同时访问数据时容易出现问题。
- 安全和访问控制:难以对敏感数据实现强有力的访问控制。
关系数据库解决文件存储局限性的特点
关系数据库可以解决文件存储方法的一些缺点:
- 数据一致性:通过集中存储数据来减少冗余。
- 数据完整性:通过主键和外键等约束来保证数据准确性。
- 高效的数据访问:使用SQL进行复杂查询,简化数据检索。
- 并发控制:数据库能够处理多用户访问,具备控制机制。
- 增强的安全性:可以在表甚至列级别上控制数据访问。
关系数据库的术语
常用的术语包括:
- 表(关系):表示实体的行和列的集合。
- 字段(属性):表中的列。
- 记录(元组):表中的单行。
- 主键:表中记录的唯一标识符。
- 外键:链接到另一个表的主键的字段,以确保参照完整性。
实体-关系(E-R)图
E-R图表示实体(表)及其关系,通常包括:
- 实体:表示数据库中的表。
- 属性:表示表中的列。
- 关系:显示实体之间的关系,通常包含基数(如一对多)。
规范化过程
规范化用于减少冗余并提高数据的完整性,主要步骤包括:
- 第一范式(1NF):确保数据具有原子性,每个字段仅包含单一值。
- 第二范式(2NF):消除复合键的部分依赖。
- 第三范式(3NF):消除传递依赖,确保每个属性只依赖于主键。
判断数据库是否满足第三范式(3NF)
判断一个表集合是否满足3NF要求的标准:
- 无部分依赖:复合主键的表中,每个属性必须依赖整个主键。
- 无传递依赖:属性直接依赖于主键,而不是其他非键属性。
生成规范化的数据库设计
基于数据描述或给定的表集合,生成规范化设计步骤:
- 识别和纠正冗余。
- 依照规范化过程(1NF到3NF)生成逻辑和一致性设计。
8.2 数据库管理系统(DBMS)
DBMS解决文件存储问题的功能
DBMS提供的功能帮助解决文件存储的局限性,包括:
- 数据独立性:将数据存储与应用逻辑分离。
- 高效查询处理:优化数据的检索和操作。
- 事务管理:确保多个操作一同成功执行。
- 备份与恢复:在系统故障时帮助恢复数据。
- 访问控制:管理用户的不同访问权限以保护数据。
DBMS工具的实际使用
常见的DBMS工具及其使用方式:
- 查询工具:利用SQL简化数据检索。
- 表单和报表生成器:提供用户友好的数据输入和输出界面。
- 备份工具:自动进行数据备份和恢复过程。
- 安全管理工具:帮助分配角色和权限,保护数据。
8.3 数据定义语言(DDL)和数据操纵语言(DML)
DBMS中的DDL和DML
- DDL(数据定义语言):管理数据库结构的语言,包括
CREATE
、ALTER
和DROP
等语句。
- DML(数据操纵语言):管理表中的数据,包括
SELECT
、INSERT
、UPDATE
和DELETE
等命令。
SQL作为行业标准
结构化查询语言(SQL)是DDL和DML的行业标准,广泛应用于各种DBMS中以确保一致性和兼容性。
编写基本的SQL(DDL)语句
示例:
- 创建表:
CREATE TABLE Student (StudentID INT, Name VARCHAR(50), Age INT);
- 修改表:
ALTER TABLE Student ADD Address VARCHAR(100);
- 删除表:
DROP TABLE Student;
编写SQL脚本来查询或修改数据(DML)
- 查询数据:
SELECT Name, Age FROM Student WHERE Age > 18;
- 插入数据:
INSERT INTO Student (StudentID, Name, Age) VALUES (1, 'Alice', 20);
- 更新数据:
UPDATE Student SET Age = 21 WHERE StudentID = 1;
通过以上介绍,考生可以全面理解数据库的基本概念、术语以及实际应用中的DBMS功能和SQL语句的使用方式,这些内容对CIE计算机科学考试至关重要。
- 作者:现代数学启蒙
- 链接:https://www.math1234567.com/article/databasea
- 声明:本文采用 CC BY-NC-SA 4.0 许可协议,转载请注明出处。
相关文章