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
In a one-one relationship, each entity of either entity set is related to at most one entity of the other set.
Example: Relationship Best-seller between entity sets Manfs (manufacturer) and Beers.
A beer cannot be made by more than one manufacturer, and no manufacturer can have more than one best-seller (assume no ties)
在畅销这个关系中，啤酒和供应商之间肯定是一对一的关系
Representing “Multiplicity”
Show a many-one relationship by an arrow entering the “one” side.
Remember: Like a functional dependency.
Show a one-one relationship by arrows entering both entity sets.
Rounded arrow = “exactly one”
each entity of the first set is related to exactly one entity of the target set
第一个集合中的实体必须且只能与目标集合中的一个实体相关联
Attributes on Relationships
Sometimes it is useful to attach an attribute to a relationship.
Think of this attribute as a property of tuples in the relationship set.
Example: Attribute on Relationship
Price is a function of both the bar and the beer, not of one alone.啤酒的种类决定了它的价格，酒吧也同样影响它的价格，所以特定情况下，啤酒的价格是由酒吧和啤酒的种类共同决定的
Equivalent Diagrams Without Attributes on Relationships
Create an entity set representing values of the attribute.
Make that entity set participate in the relationship.
将实体的属性提出来，成为一个独立的实体集，让这个实体集参与到关系中
Example: Removing an Attribute from a Relationship
Note convention: arrow from multiway relationship = “all other entity sets together determine a unique one of these.箭头指向的实体集由其他实体集共同唯一决定
Roles
Sometimes an entity set appears more than once in a relationship.
Label the edges between the relationship and the entity set with names called roles
有时候一个实体会在一个关系中频繁出现，但是可能是不同的作用，所以就需要一个区分的标签
Example: Roles
Subclasses
- Subclass = special case = fewer entities = more properties.（子类的实体更少，但是具有更多的属性）
- Example: Ales are a kind of beer. Not every beer is an ale, but some are.
Let us suppose that in addition to all the properties (attributes and relationships) of beers, ales also have the attribute color.
Subclasses in E/R Diagrams
Assume subclasses form a tree. no multiple inheritance.（子类只有一个直接超类）
Isa triangles indicate the subclass relationship. Point to the superclass.（倒三角箭头指向超类）
E/R Vs. Object-Oriented Subclasses
In OO, objects are in one class only.（面向对象中，对象只在一个类中显示）
Subclasses inherit from superclasses.（子类从超类继承属性和方法）
- In E/R entities have representatives in all subclasses to which they belong.
Rule: if entity e is represented in a subclass, then e is represented in the superclass (and recursively up the tree)
举例：如果麦芽酒在子类中出现了，那么在超类啤酒中也有显示（沿树向上递归）
Keys
- A key is a set of attributes for one entity set such that no two entities in this set agree on all the attributes of the key.（键是实体集的一组属性，任意两个实体集的键值不能完全相同）
- It is allowed for two entities to agree on some, but not all, of the key attributes.
We must designate a key for every entity set.（必须为实体集指定键值）
Keys in E/R Diagrams
- Underline the key attribute(s).
- In an Isa hierarchy（层次结构）, only the root entity set has a key, and it must serve as the key for all entities in the hierarchy（在继承结构中，只有根实体集能够定义键，并且这个键能够所有子类实体的键）
Example
- name is Key for Beers
- a Multi-attribute Key
多属性键，当一个属性不能唯一标识实体时，就需要使用多属性键
Weak Entity Sets
- Occasionally, entities of an entity set need “help” to identify them uniquely.
Entity set E is said to be weak if in order to identify entities of E uniquely, we need to follow one or more many- one relationships from E and include the key of the related entities from the connected entity sets.
当唯一确定实体需要使用到一个或多个多对一关系时，就称之为弱实体
Example: Weak Entity Set
- name is almost a key for football players, but there might be two with the same name.
- number is certainly not a key, since players on two teams could have the same number.
- But number, together with the team name related to the player by Plays-on should be unique.
Weak Entity-Set Rules
A weak entity set has one or more many-one relationships to other (supporting) entity sets.
Not every many-one relationship from a weak entity set need be supporting.
But supporting relationships must have a rounded arrow (entity at the “one” end is guaranteed).
- The key for a weak entity set is its own underlined attributes and the keys for the supporting entity sets.
E.g., (player) number and (team) name is a key for Players in the previous example.
Design Techniques
Avoid redundancy.
Redundancy = saying the same thing in two (or more) different ways.
Wastes space and (more importantly) encourages inconsistency.
Two representations of the same fact become inconsistent if we change one and forget to change the other.
Recall anomalies due to FD’s
Don’t use an entity set when an attribute will do
Entity Sets Versus Attributes
An entity set should satisfy at least one of the following conditions:
- It is more than the name of something; it has at least one nonkey attribute.
- It is the “many” in a many-one or many- many relationship
至少满足以上两个条件之一：
- 不仅只有一个名称，至少还应该有一个非键值
- 它应该是多对一关系中多的那一方
下面是一个好的设计：
下面是一个不好的设计：
Limit the use of weak entity sets
Beginning database designers often doubt that anything could be a key by itself.
They make all entity sets weak, supported by all other entity sets to which they are linked.
In reality, we usually create unique ID’s for entity sets.
Examples include social-security numbers, automobile VIN’s etc.
When Do We Need Weak Entity Sets?
The usual reason is that there is no global authority capable of creating unique ID’s.
Example: it is unlikely that there could be an agreement to assign unique player numbers across all football teams in the world.
现实生活中想要创建一种为每一个球员分配一个唯一编号是不可能实现的
From E/R Diagrams to Relations
Entity set -> relation.（实体集->表）
Attributes -> attributes.（属性->列）
Relationships -> relations whose attributes are only:
The keys of the connected entity sets.（与其他实体集关联的键）Attributes of the relationship itself.（自己的属性）
Relation: Beers(name, manf)
Combining Relations
OK to combine into one relation:
The relation for an entity-set E
The relations for many-one relationships of which E is the “many.”
Example: Drinkers(name, addr) and Favorite(drinker, beer) combine to make Drinker1(name, addr, favBeer)
假设有两个实体集A、B，满足多对一关系，A是多的一方，那么就可以考虑将实体集B的主键合并到实体集A中作为一个属性
Risk with Many-Many Relationships
Combining Drinkers with Likes would be a mistake. It leads to redundancy, as:
| name | addr | beer |
|---|---|---|
| Sally | 123 Maple | Bud |
| Sally | 123 Maple | Miller |
会出现数据冗余
Handling Weak Entity Sets
- Relation for a weak entity set must include attributes for its complete key (including those belonging to other entity sets), as well as its own, nonkey attributes.
- A supporting relationship is redundant and yields no relation (unless it has attributes)
为弱实体集创建表，主键应该是支持性实体集的键，然后包含支持性实体集的所有键和自身的非键属性
弱实体集的支持性关系不需要额外创建表，会有冗余，除非这个关系本身还有属性
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:
- Key attribute(s).
- Attributes of that subclass.
