Lecture 6 Entity-Relationship Model

:material-circle-edit-outline: 约 2910 个字 :material-clock-time-two-outline: 预计阅读时间 10 分钟

开始讲数据库系统设计

主要内容：

实体-联系模型（Entity-RelationshipModel）是一种概念模型，用于数据库分析阶段为现实世界建模。它使用ER图描述现实世界的实体（Entity）以及实体之间的联系（Relationship）。实体用以描述现实世界中可以区分的对象。实体所具有的特征称为实体的属性（Attribute）。实体之间存在着各种联系。

通过实体-联系方法得到现实世界的一个抽象模型，但这一模型并不能为数据库管理系统接受。要完成从现实世界到信息世界的转化，还必须将实体—联系方法所得的ER图转化为关系模式，并用SQL语句定义相应的表。

讲授实体-联系模型的各种要素，重点掌握采用实体-联系方法为现实世界建模的一般过程和要点。讲授ER模型中实体（包括弱实体）和联系（包括一对一、一对多、多对多联系）等的转换方法。

说白了，讲了什么是ER图，怎么将ER图转换为关系模式，怎么设计ER图

本节课我们继续使用下面的schema作为例子

University Schemas

下面这个就是关系模式，relational schemas

每一条都是一个relational schema

Database Design Process

1. 规范地确定需求
2. 概念设计
   1. E-R图
3. 逻辑设计
   1. 例如使用面向对象或关系模式，将概念设计的实现
4. 物理设计

E-R Diagram

这张图占了三分之一的知识点

一个方框是一个实体集合，实体之间的关系用菱形框表示。

实体与关系之间的连线种类表示不同的关系（一对一，一对多，……）。

Database Modeling

entity

• An entity is an object that exists and is distinguishable from other objects.
  • 比如一个蚂蚁，从信息关系角度是不可区分的，所以不能作为实体
  • Example: specific person, company, event, plant
  • Entities have attributes
    • Example: people have names and addresses
  • An entity set is a set of entities of the same type that share the same properties.
    • Example: set of all persons, companies, trees, holidays

• A database can be modeled as
  • a collection of entities
  • relationship among entities

Representing Entity sets in ER Diagram

• Entity sets can be represented graphically as follows
  • Rectangles represent entity sets.
  • Attributes listed inside entity rectangle
  • Underline indicates primary key attributes

Relationship Sets

Representing Relationship Sets in ER Diagrams

Relationship Sets with Attributes

An attribute can also be property of a relationship set.

Degree(度) of a Relationship Set

• most relationship sets in a database system are binary.

E-R Diagram with a Ternary（三元） Relationship：

Roles

角色

Entity sets of a relationship need not be distinct

Each occurrence of an entity set plays a “role” in the relationship

The labels course_idand prereq_idare called roles.

就是一个实体可能会以不同的身份参与一个关系的组成，比如预修课和主课都来自course

这里的连线就是多对多，因为一门课可能有好几门预修课，一门课可能是好几门主课的预修课

是单线不是双线的原因是，一门主课不一定有预修课，一门课不是任何课的预修课

Attributes

An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set.

Domain – the set of permitted values for each attribute Attribute types

• Attribute types:
  • Simple （简单）/ composite（复合）
  • Single-valued（单值） / multivalued（多值）
    • Example: multivalued attribute: phone_numbers
  • Derived（派生）
    • Can be computed from other attributes
    • Example: age, given date_of_birth

Composite Attributes

复合属性

方便输出用，例如name分为了这么三种，就可以只是用姓氏

孙先生，下午好

Representing Complex Attributes in ER Diagram

关系模型里面不能直接实现复合属性，需要转化

Mapping Cardinality Constraints

映射基数约束

Express the number of entities to which another entity can be associated via a relationship set.

• For a binary relationship set the mapping cardinality must be one of the following types:
  • One to one
  • One to many
  • Many to one
  • Many to many

Note: Some elements in A and B may not be mapped to any elements in the other set

Representing Cardinality Constraints in ER Diagram

箭头是单，直线是多

• We express cardinality constraints between the relationship set and the entity set. by
  • drawing either a directed line (→), signifying “one,”
  • or an undirected line (—), signifying “many,”

Total and Partial Participation

total就是要求一一对应，元素全部参与组成关系；partial就不强求，可以有不参与关系组成的元素

• Total participation （=）
  • indicated by double line
  • every entity in the entity set participates in at least one relationship in the relationship set
• Partial participation（-）
  • some entities may not participate in any relationship in the relationship set

Primary Key

Primary keys provide a way to specify how entities and relations are distinguished.

• We will consider Primary key for:
  • Entity sets
  • Relationship sets
  • Weak entity sets

Primary key for Entity Sets

就之前讲的主键

Primary Key for Relationship Sets

关系的主键就是两个实体主键的组合

Primary key for Binary Relationship

老师直接跳了

Weak Entity Sets（弱实体集）

An entity set that does not have a primary key is referred to as a weak entity set.

• The existence of a weak entity set depends on the existence of a identifying entity set（标识性实体集）
  • It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set
  • Identifying relationship（标识性联系） depicted using a double diamond

The discriminator (分辨符，or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set when the identifying entity they depend is known.

We underline the discriminator of a weak entity set with a dashed line.

We put the identifying relationship of a weak entity in a double diamond.

Primary key for section – (course_id, sec_id, semester, year)

Redundant Attributes

不懂

Reduction to Relational Schemas

实体怎么转换，联系怎么转换，多值属性怎么转换

画了ER图该怎么转化为关系模式

Representing Entity Sets

Entity sets and relationship sets can be expressed uniformly as relation schemas that represent the contents of the database.

A database which conforms to an E-R diagram can be represented by a collection of schemas.

For each entity set and relationship set there is a unique schema that is assigned the name of the corresponding entity set or relationship set

Representing Entity Sets With Simple Attributes

强实体直接转换，弱实体将所依赖的强实体的primary key拿过来和自己的组合

• A strong entity set reduces to a schema with the same attributes
  • course(course_id, title, credits)
• A weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set
  • 将其所依赖的primary key拿过来，和自己的partial key组合
  • section ( course_id, sec_id, semester, year )

转化带有多值属性的实体，则将多值属性单独拆出来另外转换，怎么转换见后文

Representing Relationship Sets

就是把两端的primary key 拿过来，和自己的属性组合

many-to-many

A many-to-many relationship set is represented as a schema with attributes for the primary keys of the two participating entity sets, and any descriptive attributes of the relationship set.

Redundancy of Schemas

Many-to-one and one-to-many relationship sets that are total on the many-side can be represented by adding an extra attribute to the “many” side, containing the primary key of the “one” side

Example: Instead of creating a schema for relationship set inst_dept, add an attribute dept_name to the schema arising from entity set instructor

就是，如果是total的一对多、多对一的模式，不用给关系单独弄一个schema，可以直接合并到many的entity的schema中

方法是用将one的主键放到many里面

For one-to-one relationship sets, either side can be chosen to act as the “many” side

就是关系储存在哪边都无所谓

Representing Composite and Multivalued Attributes

Composite Attributes

Composite attributes are flattened out by creating a separate attribute for each component attribute

就直接将里面的属性分别定义，他们的头头就不要了，在命名时注意一下就好

例如一个组合属性：

• name
  • first_name
  • last_name

直接改成：

• name_first_name
• name_last_name

如果没有歧义，甚至可以直接删掉前缀，直接就用原来的名字

Multivalued Attributes

实体和关系的多值属性一样规则，这里只讲实体为例

多值属性从实体中拆出来，单独作为一个新的实体

新的实体要包含原来实体的主键

A multivalued attribute M of an entity E is represented by a separate schema EM

Schema EM has attributes corresponding to the primary key of E and an attribute corresponding to multivalued attribute M

Special case

Entity time_slot has only one attribute other than the primary-key attribute, and that attribute is multivalued

根据之前说的，带有多值属性的实体需要将多值属性拆出来单独转换

根据之前的规则，我们得到以下转换结果：

• time_slot(time_slot_id)
• time_slot_detail(time_slot_id, day, start_time, end_time)

但是这样实体只剩一个属性，而且和detail重复了，可以优化一下，省去原来的实体

当然不是一定要省去，有利有弊

有利就是少一张表

有弊就是外键不能定义了

Optimization: Don’t create the relation corresponding to the entity, just create the one corresponding to the multivalued attribute

• time_slot(time_slot_id, day, start_time, end_time)

Caveat（警告）: time_slot attribute of section (from sec_time_slot) cannot be a foreign key due to this optimization

???

Design Issues

怎么设计ER图

一千个人有一千张ER图

Common Mistakes in E-R Diagrams

表示关系有两种方式：

• 显式的：直接画菱形框
• 隐含的：两个方框有相同的属性

上面错就错在两种方式都用了，表达的是同一个关系

这是想要记录学生每次作业的分数

这是一个多值属性，拆开来就没办法转换了

上面两图是可能的改正结果

权衡与选择

Use of entity sets vs. attributes

一个属性是否该拆出来作为单独的实体

这是一个权衡问题

Use of phone as an entity allows extra information about phone numbers (plus multiple phone numbers)

• 利：表达更多的属性、支持一对多关系
  • 比如不仅储存字符串，还储存int类型的，这样系统打电话更方便
• 弊：多了张表和对应的关系

选哪个看具体情况

Use of entity sets vs. relationship sets

在之前的ER图中，takes是作为关系本设计的

但是也可以按照下面的方式设计为实体

Possible guideline is to designate a relationship set to describe an action that occurs between entities

即尽量设计为关系

但是如果这个关系需要经常发生操作，还是用实体更好

Placement of relationship attributes

即关系的属性需不需要转换到实体里面去

放到实体的话有个问题，就是一对多、多对多的情况下就有点麻烦了

• 还有些其它的选择与权衡
  • Binary Vs. Non-Binary Relationships
    • Converting Non-Binary Relationships to Binary Form
    • 就是插入关系，让三个联系变成三个关系
  • The use of an attribute or entity set to represent an object.
  • Whether a real-world concept is best expressed by an entity set or a relationship set.
  • The use of a ternary relationship or binary relationships.
  • The use of a strong or weak entity set.

Extended ER Features

之前都是讲的传统ER，新的ER有OOP的思想

Specialization/Generalization

设计ER图的两种不同的思路

一个从实的往上抽象进行概括

• 从多到少，不断概括

一个从抽象往下特殊化为实的

• 从少到多，不断泛化

• Specialization(特化)
  • Top-down design process;
    • we designate subgroupings within an entity set that are distinctive from other entities in the set.
  • Attribute inheritance
    • a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked.

• Generalization（概化）
  • A bottom-up design process
    • combine a number of entity sets that share the same features into a higher-level entity set.

Specialization and generalization are simple inversions of each other; they are represented in an E-R diagram in the same way.

The terms specialization and generalization are used interchangeably.

上面老师学生都可抽象为人，就定义一个人的class，然后各个实体就只放自己的特别属性

Completeness constraint （完全性约束）

• Total（全部）
  • an entity must belong to one of the lower-level entity sets
• Partial（部分）
  • an entity need not belong to one of the lowerlevel entity sets

partial是指，person可能是student和employee之外的，也可能两者都是

total是指，employee要么是instructor，要么是secretary，且不能两者都都是

• Disjoint(不相交)
  • an entity can belong to only one lower-level entity set
  • Noted in E-R diagram by having multiple lower-level entity sets link to the same triangle
• Overlapping（重叠）
  • an entity can belong to more than one lower-level entity set