Entity-Relationship Model（实体-关系模型）

目录

Purpose of E/R Model

Framework for E/R

Entity Sets

E/R Diagrams

Example

Relationship Set

Multiway Relationships

Example: 3-Way Relationship

Many-Many Relationships

Many-One Relationships

Example: Many-One Relationship

One-One Relationships

Representing “Multiplicity”

Attributes on Relationships

Example: Attribute on Relationship

Equivalent Diagrams Without Attributes on Relationships

Example: Removing an Attribute from a Relationship

Roles

Example: Roles

Subclasses

Subclasses in E/R Diagrams

E/R Vs. Object-Oriented Subclasses

Keys

Keys in E/R Diagrams

Example

Weak Entity Sets

Example: Weak Entity Set

Weak Entity-Set Rules

Design Techniques

Avoid redundancy.

Don’t use an entity set when an attribute will do

Entity Sets Versus Attributes

Limit the use of weak entity sets

When Do We Need Weak Entity Sets?

From E/R Diagrams to Relations

Combining Relations

Risk with Many-Many Relationships

Handling Weak Entity Sets

Subclasses: Three Approaches

Object-oriented : One relation per subset of subclasses, with all relevant attributes.

Use nulls : One relation; entities have NULL in attributes that don’t belong to them.

E/R style : One relation for each subclass:

---

Purpose of E/R Model

• The E/R model allows us to sketch database schema designs.
• Includes some constraints（约束）but not operations.
• Designs are pictures called entity relationship diagrams.
• Later: convert E/R designs to relational DB designs.

Framework for E/R

• Design is a serious business.

The “boss” knows they want a database, but they don’t know what they want in it.

• Sketching the key components is an efficient way to develop a working database.

Entity Sets

• Entity = “thing” or object.
• Entity set = collection of similar entities.

Similar to a class in object-oriented languages.

Attribute = property of (the entities of) an entity set.

Attributes are simple values, e.g. integers or character strings, not structs, sets, etc.

属性只能是简单值，不能是集合、结构等

E/R Diagrams

In an entity-relationship diagram:

• Entity set = rectangle（矩形）
• Attribute = oval（椭圆）
• with a line to the rectangle representing its entity set

Example

• Entity set Beers has two attributes, name and manf (manufacturer)
• Each Beers entity has values for these two attributes, e.g. (Bud, Anheuser-Busch)

Relationship Set

• A relationship connects two or more entity sets.
• It is represented by a diamond, with lines to each of the entity sets involved.

The “value” of a relationship is a relationship set, a set of tuples with one component for each related entity set.

关系的“值”就是关系集，关系集由一组元组构成，每个元组包括与该关系相关的实体集的各一个实体

Multiway Relationships

• Sometimes, we need a relationship that connects more than two entity sets.
• Suppose that drinkers will only drink certain beers at certain bars.

Our three binary relationships Likes, Sells, and Frequents do not allow us to make this distinction.

But a 3-way relationship would.

Example: 3-Way Relationship

Many-Many Relationships

• Focus: binary relationships, such as Sells between Bars and Beers.
• In a many-many relationship, an entity of either set can be connected to many entities of the other set.（两个实体集中的任意一个实体都能与另一个集合中的多格式实体相连）

E.g., a bar sells many beers; a beer is sold by many bars.

Many-One Relationships

Some binary relationships are many - one from one entity set to another.

• Each entity of the first set is connected to at most one entity of the second set.（第一个集合中的每一个实体只能与第二个集合中的一个实体相连接）
• But an entity of the second set can be connected to zero, one, or many entities of the first set.（但第二个集合中的实体可以与第一个集合中的0个、1个或多个实体连接）

Example: Many-One Relationship

Favorite, from Drinkers to Beers is many-one.

A drinker has at most one favorite beer.

But a beer can be the favorite of any number of drinkers, including zero.

One-One Relationships