小白学习MySQL Day19-20 20240905-0906

一、ER建模 Entity Relationship Modelling

1、概念

是一种用于设计和组织数据库结构的图形化方法，被用于数据库的概念设计阶段。

它帮助开发者理解数据实体之间的关系，以及这些实体如何相互关联和存储数据。

广泛用于数据库设计过程中，不特定于任何数据库系统，包括MySQL。

例如，在一个大学数据库中，可能会有学生（Students）、课程模块（Modules）和讲师（Lecturers）这三个实体。

• 学生实体可能具有诸如学号（ID）、姓名（Name）和所学课程（Course）等属性。

• 学生实体可能与课程模块实体存在关系（如注册），表示学生注册了哪些课程模块。此外，学生实体还可能与讲师实体存在关系（如导师/学生），表示哪位讲师负责指导哪位学生。

通过这样的ER建模，我们可以清晰地理解大学数据库中各个实体之间的关系，以及它们各自具有的属性，从而为数据库的物理设计和实现提供基础。

2、ER图 Entity-Relationship Diagrams

提供数据库的概念视图（conceptual view of the database），

独立于数据库管理系统（DBMS）的选择，有助于识别设计中的问题。

组成

• 实体 Entities

现实世界中的事物或概念，在数据库中表示为表。

每个实体代表了一类具有共同属性和关系的数据项。

        • 代表了一个一般类型或类别。

        • 有该特定类型的实例。例如，DB（数据库）和AI（人工智能）是模块的实例。

        • 具有一系列属性，这些属性用于描述实体的具体特征。

• 属性 Attributes

是关于实体的特性、性质、事实、方面、属性或细节。

描述了实体的具体信息，在数据库中对应于表的列。

例如，学生有ID、姓名、课程、地址等。

每个属性都有：

        • 一个名称

        • 一个相关联的实体

        • 一个可能的值域

        • 对于相关联实体的每个实例，属性值域中的一个值

• 关系 Relationships

实体之间的联系或关联。

定义了实体之间如何相互作用或相互关联，在数据库中通常通过外键等机制来实现。

例如，学生和模块之间可能存在一个关系，表示学生选修了哪些模块。

每个学生选择多个模块：学生和模块之间存在一个多对多的关系。

                                        因为一个学生可以选多个模块，而一个模块也可以被多个学生选择。

每个模块由一个讲师教授：模块和讲师之间存在一个一对多的关系。

                                         因为一个讲师可以教授多个模块，但一个模块通常只由一个讲师主讲。

每个关系都有：

        • 名称

        • 参与实体

        •  度 degree ：参与关系的实体数量。the number of entities that participate (usually 2)

        •  基数比  cardinality ratio ：

描述两个实体之间关系中的实例数量比例。

Cardinality defines the possible number of occurrences in one entity which is associated with the number of occurrences in another.

通常表示为一对一（1:1）、一对多（1:N）、多对一（N:1）或多对多（N:M）的关系。

一对一（1:1）关系： 表A中的每一行都与表B中的一行有且仅有一个对应关系。例如，在数据库中，员工信息表（表A）和员工的电子邮件地址表（表B）之间可能是一对一的关系，因为每个员工只有一个电子邮件地址，而每个电子邮件地址也只对应一个员工。

一对多（1:M 或 1:*）关系： 表A中的一行可以与表B中的多行相关联，但表B中的每一行只能与表A中的一行相关联。讲师表（表A）与学生表（表B）之间是一对多关系，因为一位讲师可以教多名学生，但每名学生只由一位讲师指导。

多对多（M:M) 或 (M:N) 或 (*:*）关系：表A中的多行可以与表B中的多行相关联。学生表（表A）和课程模块表（表B）之间是多对多关系，因为每名学生可以选修多门课程，而每门课程也可以被多名学生选修。为了表示这种关系，通常需要一个额外的表（称为关联表或中间表），其中包含两个外键，分别指向两个主表的主键。

基数比对于理解数据的结构和约束非常重要，对数据库的物理设计有直接影响。

在“一对多”关系中，通常会将“多”端实体的外键放置在它自己的表中，指向“一”端实体的主键。

在“多对多”关系中，则需要创建一个新的表（称为关联表或中间表）来存储两个实体之间的关联，这个表将包含两个外键，分别指向两个原始实体表的主键。

二、设计ER模型

根据现实世界需求创建ER图。

1、流程

(1）识别实体

从需求中识别出所有的实体。

(2)  定义关系

分析实体之间如何相互关联，并定义这些关系。

需要考虑关系的基数比：（1：1），（1：*）或（M：N）。

即一个实体的实例可以与多少个另一个实体的实例相关联。

(3)  确定属性

为每个实体确定其属性。属性是描述实体特征的数据项。

(4)  绘制ER图

在图中：

实体用矩形表示，

属性用椭圆或未封闭的矩形表示（并附加到实体上），

关系用菱形表示，并用线连接相关的实体。

在关系线上，可以标注关系的类型和基数比。

2、例一

关注词性，从问题描述中获取这些信息：

        • 一般准则

        • 实体：事物或对象，因此在描述中通常是名词

        • 属性：事实或特性，因此也通常是名词

        • 关系：通常是动词

• 如何区分实体与属性？

                • 实体可以有属性，但属性没有更小的组成部分。

                • 实体之间可以存在关系，但属性只属于一个实体。

问题描述：

A university consists of a number of departments. Each department offers several courses. A number of modules make up each course. Students enroll in a particular course and take modules towards the completion of that course. Each module is taught by a lecturer from the appropriate department (several lecturers work in the same department), and each lecturer tutors a group of students. A lecturer can teach more than one module but can work only in one department.

一所大学由多个系组成。每个系都提供几门课程。每门课程由多个课程模块组成。学生注册特定的课程并完成该课程的模块。每个课程模块都由相应系的讲师授课（同一系有多名讲师），并且每位讲师辅导一组学生。一名讲师可以教授多个课程模块，但只能在一个系工作。

• 实体Entities：学生Student，系Department，讲师Lecturer，课程Course，课程模块Module。

• 关系Relationships： 

        • " Offers":         Department——Course (1:*)

        • "Make Up":    Course——Module (*:*)

        • "Enroll":         Student——Course (*:1)

        • "Take":          Student——Module (*:*)

        • "Taught By":  Module——Lecturer (1:1)

        • "Work in":      Lecturer——Department (1:*)

        • "Tutors":        Lecturer——Student (1:*)

• 属性Attributes：暂无。

现存问题：多对多关系 （M:M）

多对多关系会给模型以及应用程序开发过程增加复杂性和混乱。

将一个多对多（M:M）关系分解为两个一对多（1:M）关系的关键是： 

                                        创建一个额外的实体来代表这个多对多关系。

在关系型数据库中，多对多关系不能直接通过两个表之间的直接连接来表示，通常通过一个称为“关联表”或“中间表”的额外表来实现。

重难点：表示与处理M:M关系

有两个实体：Student（学生）和Module（课程模块），它们之间存在多对多关系。

即一个学生可以注册多个课程模块，而一个课程模块也可以被多个学生注册。

(1) 定义实体表

1. Student表包含学生的信息，如student_id（主键）、name等。
2. Module表包含课程模块的信息，如module_id（主键）、module_name等。

(2) 创建关联表

1. 创建一个新的表，来存储学生和课程模块之间的关联。这个表通常包含两个外键，分别指向Student和Module表的主键。例如，可以将关联表命名为Enrollment。
2. Enrollment表包含student_id（外键，指向Student）、module_id（外键，指向Module），以及可能的其他属性，如enrollment_date（注册日期）。

CREATE TABLE Student (    
    student_id INT AUTO_INCREMENT PRIMARY KEY,    
    name VARCHAR(255) NOT NULL    
);    
    
CREATE TABLE Module (    
    module_id INT AUTO_INCREMENT PRIMARY KEY,    
    module_name VARCHAR(255) NOT NULL    
);    
    
CREATE TABLE Enrollment (    
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY,    
    student_id INT,    
    module_id INT,    
    enrollment_date DATE,    
    CONSTRAINT FK_Enrollment_Student FOREIGN KEY (student_id) 
        REFERENCES Student(student_id),    
    CONSTRAINT FK_Enrollment_Module FOREIGN KEY (module_id) 
        REFERENCES Module(module_id)    
);

 

• The Enrollment table:

        • Will have columns for the student ID and module code attributes.

        • Will have a foreign key to Student for the "has" relationship.

        • Will have a foreign key to Module for the "in" relationship.

注意：在Enrollment表中，enrollment_id作为主键是可选的，具体取决于是否需要为每次注册生成一个唯一的标识符。如果不需要，可以省略它，但通常保留它以便将来可能需要的引用完整性或查询优化。

(3) 查询

当需要查询某个学生注册的所有课程或某个课程模块的所有注册学生时，可以通过连接Student、Module和Enrollment表来实现。

(1) 查询学生John的所有课程

SELECT M.module_name  
FROM Module AS M  
JOIN Enrollment AS E ON M.module_id = E.module_id  
JOIN Student AS S ON E.student_id = S.student_id  
WHERE S.name = 'John';

• (2) 查询Programming课程的所有注册学生

SELECT S.name
FROM Student AS S
JOIN Enrollment AS E ON S.student_id=E.student_id
JOIN Module AS M ON M.module_id=E.module_id
WHERE M.module_name='Programming';

4、更新和删除

当需要更新或删除关系时，通常是在Enrollment表上进行操作。

例如，删除学生John的所有注册信息，可以直接在Enrollment表中根据student_id删除相关记录。

DELETE FROM Enrollment  
WHERE student_id = (  
    SELECT student_id  
    FROM Student  
    WHERE student_name = 'John'  
);

在大多数数据库系统（如MySQL、PostgreSQL、SQL Server等）中，不能直接在DELETE语句中使用JOIN来从多表中删除数据，尤其是当你只想从单个表中删除数据时。

因此，在WHERE子句中使用子查询。

要从Enrollment表中删除名为John的学生的注册信息，需要先找到该学生的student_id（通过查询Student表来获取），然后使用这个student_id来执行DELETE操作。

3、例二

We want to represent information about products in a database. Each product has a description, a price and a supplier. Suppliers have addresses, phone numbers, and names. Each address is made up of a street address, a city name, and a postcode.

我们想要在数据库中表示产品信息。每个产品都有描述、价格和供应商。供应商有地址、电话号码和名称。每个地址都由街道地址、城市名称和邮政编码组成。

第一步：先筛选出所有名词 Entities实体 or attributes属性

• product

• description

• price

• supplier

• address

• phone number

• name

• street address

• city name

• postcode

第二步：根据是否可以被分解为更具体的部分，区分实体和属性

• Products, suppliers, and addresses all have smaller parts so we make them entities.

• The others have no smaller parts and belong to a single entity so we make them attributes.

第三步：根据名词之间的动词，定义关系

(1) "Each product has a supplier":

        • Each product has a single supplier.

        • A supplier supplying many products.

因此，Product和Supplier之间是 (1:*) 的关系。

(2) " Each supplier has an address":

        • A supplier has a single address.

        • It does not seem sensible for two different suppliers to have the same address.

因此，Supplier和Address之间是 (1:1) 的关系。

重难点：处理冗余关系

(1) 判断冗余

实体A和B之间的关系可能是冗余( redundant)的，如果：

        •  A和B之间存在1:1的关系。

        •  每个A都与一个B相关联，且每个B都与一个A相关联。即一一对应关系。

示例：

供应商-地址关系是一对一的，每个供应商都有一个地址，不需要与供应商无关的地址。

(2) 处理冗余

将参与冗余关系的两个实体合并(merge)在一起

• 它们将成为一个单一的实体

• 新实体将包含旧实体的所有属性

4、总结

(1) ER Diagram构建步骤

Summary of Steps：

1. 从需求描述中识别以下要素 From a description of the requirements, identify：
   • 实体（Entities）：代表现实世界中的对象或概念，如“学生”、“课程”等。
   • 属性（Attributes）：描述实体的特征或性质，如“学生”实体有“姓名”、“学号”等属性。
   • 关系（Relationships）：描述实体之间如何相互关联，如“学生”与“课程”之间可能存在“选课”关系。
   • 关系的基数比（Cardinality Ratios of Relationships）：指明关系两端实体参与关系的数量，如一对多（1:N）、多对一（N:1）、多对多（M:N）等。
2. 绘制ER图Draw the ER diagram，并进行以下检查：
   • 检查一对一关系：一对一关系有时可能存在冗余的实体或属性。如果冗余redundant，两个实体之间的信息可能可以合并到一个实体中。检查这些关系是否真正必要，或者是否可以通过调整实体结构来简化模型。
   • 处理多对多关系：多对多关系在ER图中通常不直接表示，通常会被分解为两个一对多关系，通过引入一个中间实体（Intermediate Entity）来实现。这个中间实体会包含两个原始实体之间关系的额外信息（如果有的话），并作为两个原始实体之间联系的桥梁。例如，“学生”与“课程”之间的“选课”关系（多对多）可以通过引入“选课记录”这一中间实体来分解为两个一对多关系：“学生”到“选课记录”和“课程”到“选课记录”。

(2) 从ER图到SQL表

从概念设计（ER图）到物理实现（SQL表）的平滑过渡：

• 实体转化为表名：Entities Become table names.

在ER图中定义的每一个实体都会转换成一个对应的SQL表。

表的名字通常与实体的名字相同或相似，以便于理解和维护。

• 实体的属性转化为列：Attributes of an entity becomes the columns.

每个实体的属性都会转化为表中对应的列（字段）。

列的数据类型会根据属性的性质来定义，

如果属性是名字，那么数据类型可能是VARCHAR；

如果属性是年龄，那么数据类型可能是INT。

• 关系转化为外键：Relationships become foreign keys.

ER图中定义的关系在转化为SQL表时，通常通过外键来体现。

        • (1:M) 或 (M:1) 的关系：M:1 are represented as a foreign key from the M-side to the 1.

在“M”的那一方的表中添加一个外键列，指向“1”的那一方表的主键。

在大多数情况下，若两个实体之间存在严格的1:1关系，则它们可能会被合并为一个单一的实体。

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

        • (M:M) 的关系：M:M are split into two M:1 relationships.

引入一个中间实体（表），将多对多关系转化为两个一对多的关系。

中间表会包含两个外键，分别指向参与多对多关系的两个实体的主键。

(3) 好的数据库设计的特征  

Characteristics of Good DB Designs：

• 所需的最少属性数量，以支持企业的数据需求；

 The minimal number of attributes necessary to support the data requirements of the enterprise;

• 逻辑上关系紧密的属性位于同一个关系中；

 Attributes with a close logical relationship are found in the same relation;

• 最小冗余，每个属性仅表示一次，但形成全部或部分外键的属性是重要例外；

Minimal redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys.

三、数据库设计示例

You have been hired by a large, multi-branch car sales company to design a database capable of storing the company’s information. You are asked to develop a database system capable of storing the company’s Offices, Inventory and Staff information. The requirements for the database are provided below:

• There are several Offices in different locations across the United Kingdom.

• Each Office has its own Name, Address and Telephone number.

• Each Office will have a unique Telephone number.

• Each Office employs many sales-persons. A sales-person can only be employed by a single Office.

• A sales-person has a Name, Age, Salary and a unique ID number.

• Each Office will have many cars associated to it. A car may only be associated to a single Office.

• Information relating to each Car is also stored. This information includes:

Manufacturer, Model, Production Year and Number of Doors and a unique ID number.

• A car may be sold by a single sales-person. A sales-person may sell many cars.

你被一家大型、多分支的汽车销售公司雇佣来设计一个能够存储公司信息的数据库系统。你的任务是开发一个数据库系统，该系统能够存储公司的办公室、库存和员工信息。以下是数据库的具体要求：

• 在英国各地设有多个办公室。

• 每个办公室都有自己的名称、地址和电话号码。

• 每个办公室的电话号码都是唯一的。

• 每个办公室雇佣多名销售人员。一名销售人员只能受雇于一个办公室。

• 销售人员具有姓名、年龄、薪水和唯一的身份证号码。

• 每个办公室都关联着多辆车。一辆车只能关联到一个办公室。

• 还存储了与每辆车相关的信息，包括制造商、型号、生产年份、车门数量和唯一的身份证号码。

• 一辆车可以由一名销售人员售出。一名销售人员可以售出多辆车。

Entities (Tables): Office

Attributes: Name, Address and Telephone number

Entities (Tables): Sales Person

Attributes: Name, Age, Salary, ID number

Relationship: Office——Sales Person is (1:M)     "Employ relationship"

Entities (Tables): Car

Attributes: Manufacturer, Model, Production Year and Number of Doors, ID number

Relationship: Office——Car is (1:M)         "Stock relationship"

                      Sales Person——Car is (1:M)        "Sold relationship"