Chapter 12

E/R Model

Entity-Relationship Model 實體關係模型

The E/R model allows us to sketch database schema designs.

協助描繪資料庫 schema 設計，藉由 E/R model 映射到資料庫的 Relation model。

名詞定義

• Entity = “thing” or object.
  實體，對應到現實生活中的名詞，例如蘋果 mac2013、微軟 win8、 … 等。單指一個、一件實體存在。
• 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 時，呈現方式

• Entity set = rectangle.
  實體集合使用矩形圖表示
• Attribute = oval,
  屬性使用橢圓表示，並且連到實體集合，表示這些實體集合都具有這些屬性特徵。
  with a line to the rectangle representing its entity set.

• Relationships = diamond.
  用菱形連接兩個以上的實體集合，表示實體集合之間的關係。
  A relationship connects two or more entity sets, with lines to each of the entity sets involved.

• Relationship Set
  The “value” of a relationship is a relationship set, a set of tuples with one component for each related entity set.
  For the relationship Sells , we might have a relationship set like:

Bar	Beer
Joe’s Bar	Bud
Joe’s Bar	Miller
Sue’s Bar	Bud
Sue’s Bar	Pete’s Ale
Sue’s Bar	Bud Lite

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

Bar	Drinker	Beer
Joe’s Bar	Ann	Miller
Sue’s Bar	Ann	Bud
Sue’s Bar	Ann	Pete’s Ale
Joe’s Bar	Bob	Bud
Joe’s Bar	Bob	Miller
Joe’s Bar	Cal	Miller
Sue’s Bar	Cal	Bud Lite

• Rounded arrow = “exactly one,”
  實心箭頭表示確切對應一個關係，如果一個人會喜愛好幾種酒，其中最愛某一種。
  i.e., each entity of the first set is related to exactly one entity of the target set.

• Roles
  Label the edges between the relationship and the entity set with names called roles.
  例如丈夫、妻子之間的關係、夥伴之間的關係。

Husband	Wife
Bob	Ann
Joe	Sue

Buddy1	Buddy2
Bob	Ann
Joe	Sue

• Subclass = special case = fewer entities = more properties.
  從物件導向來說，一個繼承關係，得到的 Subclass 會繼承原本的 Class 的特徵、關係，有可能會具有額外的特徵。
  Let us suppose that in addition to all the properties (attributes and relationships) of beers, ales also have the attribute color . Isa triangles ( is a 的三角形) indicate the subclass relationship. Point to the superclass (指向父類別).

  • 比較 OO 和 E/R 對 subclass 的定義
    也就是說，OO 只會用 is a 描述一個 subclass 的繼承關係。但在 E/R model 中，isa 相當一個欄位，用來描述它是哪一個子類別，也就是說在紀錄時，如果它在某一個子類別時，那麼在父類別中也會記錄到它。
    • In OO, objects are in one class only. Subclasses inherit from superclasses.
    • In contrast, 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).

• Key
  Key 是一個 attribute 的集合構成，得到 f(Key) = at most one entity 。繪製時，使用底線 (underline) 於作為 key attribute 表示。
  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.

• Weak Entity Sets
  為了去辨識某個實體，必須倚靠一對一、或者多對一的關係，來確切地找到某個實體。
  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.

  • 例如 Player(name, nubmer) - Plays_on -> Team(name)。當知道 Team 是哪一個時，才能辨識出 Player，否則名稱跟號碼可能會在不同隊中出現重複的。因此 Player 就是一個 Weak Entity Sets 。

• Weak Entity-Set Rules
  A weak entity set has one or more many-one relationships to other (supporting) entity sets.
  並不是每一個 relationship 都是 weak entity set 需要的支持。然而被需要的 relationship 必然擁有一個 rounded arrow 。
  The key for a weak entity set is its own underlined attributes and the keys for the supporting entity sets.
  例如在之前的例子，可以使用 (player) number 和 (team) name 作為 Player 的 key。

Design Techniques

• Design Techniques
  • Avoid redundancy.
    避免冗餘
  • Limit the use of weak entity sets.
    限制 weak entity set 的使用
  • Don’t use an entity set when an attribute will do.
    如果能使用一個 attribute 表示，則無需增加一個 entity set 單獨表示一個 attribute。

分別對應以下幾點。

• Redundancy = saying the same thing in two (or more) different ways
  存在用好幾種方法說一件事情，或者提及某個屬性。

• Entity Sets Versus Attributes
  對應實體集合的屬性，要符合以下兩點，屬性應為很多實體共同擁有的名稱，並且擁有至少一個 nokey 屬性，或者屬於 many-one 或 many-many 關係中，many 那一方的 entity set。
  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. or
  • It is the “many” in a many-one or many-many relationship

• Weak Entity Sets 使用須知

  • 避免濫用，通常能夠創建一個 unique ID 來表示一個實體。
  • 當真的找不到一個普遍性的 unique ID 時，才會逼不得已使用 Weak entity set.

From E/R Diagrams to Relations

關於對應的名詞如下，轉換時找 attribute 時，E/R Diagrams 中的 relationships 也會成為一個 relation ，attribute 的找法，可以從關聯的 entity set 的 key 或者是關係的定義本身。

• Entity set -> relation.
• Attributes -> attributes.

• Relationships -> relations whose attributes are only:

  • The keys of the connected entity sets.
    將相連的實體集合的 key 值都拿來作為 attribute。
  • Attributes of the relationship itself.

• Combining Relations
  結合 Relation，減少 Relation 的個數，將多對一的 relationships 表單，而實體集合屬於 many 的那一方，則可以把 one 那一方結合在一起。切記一定要是多對一，如果是多對多則會造成冗餘發生。

  • 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).

• Handling Weak Entity Sets
  解決 weak entity set 轉化成 relation 時，必然要為它準備一個 complete key 來辨識不同實體於關連到不同的實體集合。如果一個 supporting relationship 不具有 attribute，而且屬於一個冗餘的紀錄，那麼把 relationship 另一方 “one” 的 entity set 的 key 值加入到 weak entity set 所創建的 relation。
  從之前的例子，把球隊名稱 team(name) 加入到 Player(number, name) 中，變成 relation Player(number, team_name, name)，這是在之前的 Combining Relations 中，就已經得到的結果，因此就可以忽略。

  • 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.
    父類別和子類別分開，各自成為一個 relation。
  • Use nulls : One relation; entities have NULL in attributes that don’t belong to them.
    把子類別的屬性上提，如果父類別不存在該屬性則填入 null。
  • E/R style : One relation for each subclass:
    存在父類別和子類別的 relation，但是共同屬性都會在父類別中，子類別只保留 key 和新增加的屬性。
    • Key attribute(s).
    • Attributes of that subclass.