一、数据库设计的重要性

• 设计数据库可使查询更高效、简洁。

• 减少数据冗余（data redundancy），提升表的整洁性。

二、Key Components of ER Modelling

实体-关系建模的基本构成

1. 实体（Entity）：表示有兴趣的对象或项目，例如学生、讲师、产品等。

2. 属性（Attributes）：描述实体的属性，例如学生的ID、名称、地址等。

3. 关系（Relationships）：实体之间的联系，例如学生与课程的注册关系。

 For example

 in a University database we might have entities 

for Students, Modules and Lecturers

• Students might have attributes such as their ID, Name, and Course

• Students could have relationships with Modules (enrolment) and Lecturers (tutor/tutee)

二、Purpose of ER（Entity - Relationship） Diagrams

• 提供数据库的概念性视图。conceptual view of the database.

• 独立于数据库管理系统（DBMS）。Independent of the choice of DBMS.

• 帮助识别设计中的潜在问题。

 

1.Entities 

• Entities 表示对象或感兴趣的事物。

• 可以是物理实体，例如学生、讲师、员工、产品。

• 也可以是抽象实体，如模块、订单、课程、项目。

Each entity has the following characteristics:

（1） 它是通用的类型或类，例如讲师或模块。

（2） 它有特定类型的实例（ instances） ，例如模块DBI和IAI。

（3） 它有属性，例如名称和电子邮件地址。

2. Attributes

• Attributes 是实体的事实、方面、属性或细节。

• 例如，学生有ID、姓名、课程、地址等属性；模块有代码、名称、学分权重和级别。

Each attribute includes the following:

（1）A name.

（2）关联的实体。An associated entity

（3）可能值的范围。Domains of possible values

（4）每个实例的值来自其属性域。a value from the attributes domain

3. Relationships 

• Relationship 是两个或多个实体之间的关联。 association

For example:

（1） 每个学生选修若干模块。

（2） 每个模块由讲师授课。

（3） 每位员工为单一部门工作。

Relationships include the following:

（1）A name.

（2） 参与关系的实体集合。A set of entities 

（3）度（degree）：参与关系的实体数量（通常为2）。

（4） 基数比率（Cardinality Ratio）。

三、实体和属性的表示方法

Representation of Entities and Attributes

• 实体用带圆角的矩形框表示。boxes with rounded corners.

 The box is labelled with the name of the class of objects 

• 属性用椭圆形 （ovals）表示，并通过线条连接到实体。

四、关系及基数比率

Relationships and Cardinality Ratios

• One-to-One (1:1)：例如，每位讲师有一个唯一办公室。offices are single occupancy 

• One-to-Many(1:M) or (1:*)：例如，一个讲师可能教多个学生。but each student has just one tutor

• Many-to-Many(M:M) or (M:N) or (*:*)：例如，每个学生选修多门课程。

• 关系通过两实体间的链接表示。

• 关系的名称写在一个菱形框中。diamond box.

• 链接的两端表示基数（Cardinality）。The ends of the link

基数是描述两个实体间关系中，一个实体实例可以与另一个实体实例关联的数量。

“Tutors” 表示讲师与学生的教学关系。

“Studies” 表示学生与模块之间的选课关系。

五、Steps to Design ER Models

 1. To make an ER model you need to identify: 

• Entities （are things or objects they are often nouns in the description）

• Attributes（are facts or properties, and so are often nouns also ）

• Relationships and Cardinality ratios（Verbs ）

2. 绘制ER图并检查是否有多对多关系。

3. Example: Relationships

• The completed diagram. All that remains is to remove M:M relationships

 

六、Issue: M:M Relationships

（1） 多对多关系 是指一个实体可以关联到多个另一个实体，例如：

一个学生可以选修多个课程（Student - Takes - Module）。

一个课程可以被多个学生选修。

（2） 在传统数据库设计中，直接表示多对多关系是困难的。

• 设计1：

 每个学生和课程关系单独占一行（冗余多）。

• 设计2：

 合并学生课程为一个字段，虽然减少冗余，但处理复杂（例如查询和更新）。

 

 

Resolving Many-to-Many Relationships

1. 多对多关系需拆分为两个一对多关系。split into two

2. 添加一个中间实体 intermediate entity (e.g., “Enrollment”)

从ER图转换为SQL表

The Enrolment table 

• 实体转换为表名，属性转换为列。

• 关系转换为外键。

• 表中包含学生ID (sID) 和课程代码 (mCode)。

• 表的主键由学生ID和课程代码组合而成，避免重复选课。

外键关系：

• Enrolment.sID 是指向 Student.sID 的外键。

• Enrolment.mCode 是指向 Module.mCode 的外键。

七、Entities and Attributes

• 在数据库设计中，有时候难以区分某一数据项是“实体”还是“属性”。比如，一个“地址”是作为一个独立的实体，还是一个属性取决于它的用途。

• 实体可以有属性，而属性不能再细分。

• 实体之间可以有关系，但属性是单独属于某个实体的。

• 判断标准：如果某个概念可以被细分为多个部分（如地址包括街道、城市、邮编），通常将其作为独立实体。

1. Example : Entities/Attributes

 

2. Issue: One-to-One Relationships

• 冗余的（Redundant）一对一关系可以合并（merged into）为单个实体。

• 合并后的实体保留所有原始实体的属性，避免关系冗余。

• 供应商和地址的一对一关系可合并。merged

八、ER建模的步骤

 Steps in ER Modelling

1. 确定实体、属性、关系、基数比率。

2. 检查冗余的一对一关系并解决多对多关系。

split into two one to many links, using an intermediate entity

3. 将ER图转换为SQL表。

• Entities Become table names.

• Attributes of an entity becomes the columns.

• Relationships become foreign keys

九、Relationships as Foreign Keys

1:1、1:M、M:N关系如何通过外键实现。

• 1:1 are usually not used, or can be treated as a special case of M:1

• M:1 are represented as a foreign key from the M-side to the 1.

在1:M关系中，外键会存放在“多”的一方。例如，一个学生注册多个课程，课程表需要包含学生的ID作为外键。

• M:M are split into two M:1 relationships.

学生和课程之间的M:N关系通过创建一个“Enrolment”中间表实现，包含学生ID和课程代码。

十、ER Diagram in SQL 

通过工具生成ER图：

• 如果数据库已经存在，可以通过数据库工具（如phpMyAdmin）的设计模式（Designer）自动生成ER图。

• 工具会根据表和表之间的外键关系，自动生成实体和关系的可视化模型。（自动展示表的结构、外键关系以及图示化的ER图）

这些图片涵盖了以下几个主要知识点：

十一、Example

1. 实体和属性的定义及设计

• 实体 (Entity) 是数据库中的表，每个表包含多个记录。

• 属性 (Attribute) 是表中的列，描述了实体的具体特征。例如：

• Guest 的属性包括用户名、真实姓名、护照号等。

• Room 的属性包括楼层号、房间号和房间类型。

2. 关系 (Relationship) 的设计

• 关系 描述了实体之间的交互。

• 示例中提到：

• Guest 和 Room 之间存在一个多对多 (M:N) 关系，称为 Booking。

• 多对多关系需要引入第三张表（如 Booking）来解决。

设计实践：

• Booking 表包含外键字段，分别指向 Guest 和 Room。

• 外键字段用于建立实体间的关联。

3. 房间类型设计

• 房间类型可以通过以下方式处理：

1. 使用域值限制，比如指定房间类型必须是 “VIP” 或 “普通”。

2. 单独创建一个 RoomType 表，用于存储类型和描述信息。

• 第二种方式更加灵活，便于扩展。

4. SQL 表的实现

• (floorlvl, roomno) references room.

• Passport_id references guest.

• Booker_passport also references guest

 Booking 表的设计

• 注意：

• 使用 FOREIGN KEY 明确关系。

• 房间号 (roomno) 和楼层号 (floorlvl) 组合形成房间的唯一标识。

5. 设计问题

• 静态状态问题：

 表中直接保存 booked 状态的设计可能不合理，因为预订信息是动态变化的（随着时间段或用户需求变化）。

 一个房间的预订状态实际上取决于时间范围（如入住日期和退房日期），直接使用布尔值无法处理复杂场景。

• 冗余问题：

 如果房间的预订状态频繁变化，直接更新 booked 字段会导致数据库维护困难。

 无法追踪具体的预订信息（如谁预订了、什么时候预订的）。

改进方法：

• 使用 Booking 表来动态存储预订信息。

• 根据时间范围（如开始日期和结束日期）来判断房间是否被预订。

动态查询：

• 通过关联查询动态计算房间的预订状态。例如：

SELECT roomno, floorlvl
FROM rooms r
WHERE NOT EXISTS (
    SELECT 1
    FROM booking b
    WHERE r.roomno = b.roomno
      AND r.floorlvl = b.floorlvl
      AND CURRENT_DATE BETWEEN b.startdate AND b.enddate
);--返回所有当前未被预订的房间

6. 自然键和代理键

• 自然键 (Natural Key)：来自业务逻辑的唯一标识，例如，对于 rooms 表，房间号 (roomno) 和楼层号 (floorlvl) 的组合可以作为自然键。

• 代理键 (Surrogate Key)：系统生成的标识，如主键自增ID。