8.1 Database Concepts

Limitations of a File-Based Approach

1. Data Redundancy and Inconsistency: Data is duplicated across multiple files, leading to inconsistency and increased storage requirements.

2. Data Isolation: Retrieving related data spread across multiple files can be challenging.

3. Limited Data Integrity: Maintaining rules to ensure data accuracy (like foreign keys) is hard to enforce.

4. Concurrency Issues: File-based systems struggle with multiple users accessing data simultaneously.

5. Security and Access Control: Difficulties arise in implementing robust access controls for sensitive data.

Features of a Relational Database Addressing File-Based Limitations

1. Data Consistency: By centralizing data, relational databases reduce redundancy.

2. Data Integrity: Constraints like primary keys and foreign keys maintain data accuracy.

3. Efficient Data Access: SQL enables complex querying, reducing the difficulty of retrieving related data.

4. Concurrency Control: Databases can handle multiple users with mechanisms to manage simultaneous access.

5. Enhanced Security: Access controls at the table or even column level help secure data.

Relational Database Terminology

• 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

1. Entities: Represent tables in the database.

2. Attributes: Represent columns in the table.

3. Relationships: Show how entities relate to each other, often with cardinality notations (e.g., one-to-many).

Normalisation Process

1. First Normal Form (1NF): Ensures data is atomic, with each field containing a single value.

2. Second Normal Form (2NF): Removes partial dependencies for tables with composite keys.

3. Third Normal Form (3NF): Eliminates transitive dependencies, ensuring each attribute depends only on the primary key.

Third Normal Form (3NF) Validation

1. No Partial Dependencies: For tables with a composite primary key, each attribute depends on the full primary key.

2. No Transitive Dependencies: Attributes depend directly on the primary key, not other non-key attributes.

Producing a Normalized Database Design

1. Identify and correct redundancies.

2. 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

1. Data Independence: Separates data storage from application logic.

2. Efficient Query Processing: Optimizes retrieval and manipulation of data.

3. Transaction Management: Ensures that multiple operations complete successfully together.

4. Backup and Recovery: Helps restore data in case of system failures.

5. Access Control: Manages permissions for users at various levels of access.

Practical Use of DBMS Tools

1. Query Tools: Simplify data retrieval with SQL.

2. Form and Report Generators: Provide user-friendly data input and output interfaces.

3. Backup Utilities: Automate data backup and restore processes.

4. 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, and DROP.

• DML (Data Manipulation Language): Manages data within tables using commands like SELECT, INSERT, UPDATE, and DELETE.

SQL as the Industry Standard

Writing Basic SQL (DDL) Statements

• 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;

8.1 数据库概念

使用基于文件的方法的局限性

1. 数据冗余和不一致：数据在多个文件中重复，导致数据不一致并增加存储需求。

2. 数据隔离：分散在多个文件中的相关数据难以检索。

3. 有限的数据完整性：难以维护数据准确性（例如外键）规则。

4. 并发问题：基于文件的系统在多用户同时访问数据时容易出现问题。

5. 安全和访问控制：难以对敏感数据实现强有力的访问控制。

关系数据库解决文件存储局限性的特点

1. 数据一致性：通过集中存储数据来减少冗余。

2. 数据完整性：通过主键和外键等约束来保证数据准确性。

3. 高效的数据访问：使用SQL进行复杂查询，简化数据检索。

4. 并发控制：数据库能够处理多用户访问，具备控制机制。

5. 增强的安全性：可以在表甚至列级别上控制数据访问。

关系数据库的术语

• 表（关系）：表示实体的行和列的集合。

• 字段（属性）：表中的列。

• 记录（元组）：表中的单行。

• 主键：表中记录的唯一标识符。

• 外键：链接到另一个表的主键的字段，以确保参照完整性。

实体-关系（E-R）图

1. 实体：表示数据库中的表。

2. 属性：表示表中的列。

3. 关系：显示实体之间的关系，通常包含基数（如一对多）。

规范化过程

1. 第一范式（1NF）：确保数据具有原子性，每个字段仅包含单一值。

2. 第二范式（2NF）：消除复合键的部分依赖。

3. 第三范式（3NF）：消除传递依赖，确保每个属性只依赖于主键。

判断数据库是否满足第三范式（3NF）

1. 无部分依赖：复合主键的表中，每个属性必须依赖整个主键。

2. 无传递依赖：属性直接依赖于主键，而不是其他非键属性。

生成规范化的数据库设计

1. 识别和纠正冗余。

2. 依照规范化过程（1NF到3NF）生成逻辑和一致性设计。

8.2 数据库管理系统（DBMS）

DBMS解决文件存储问题的功能

1. 数据独立性：将数据存储与应用逻辑分离。

2. 高效查询处理：优化数据的检索和操作。

3. 事务管理：确保多个操作一同成功执行。

4. 备份与恢复：在系统故障时帮助恢复数据。

5. 访问控制：管理用户的不同访问权限以保护数据。

DBMS工具的实际使用

1. 查询工具：利用SQL简化数据检索。

2. 表单和报表生成器：提供用户友好的数据输入和输出界面。

3. 备份工具：自动进行数据备份和恢复过程。

4. 安全管理工具：帮助分配角色和权限，保护数据。

8.3 数据定义语言（DDL）和数据操纵语言（DML）

DBMS中的DDL和DML

• DDL（数据定义语言）：管理数据库结构的语言，包括CREATE、ALTER和DROP等语句。

• DML（数据操纵语言）：管理表中的数据，包括SELECT、INSERT、UPDATE和DELETE等命令。

SQL作为行业标准

编写基本的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;