tags: database note thu

ER/EER Modeling

回到首頁: DB-Note

簡介

我們的目標就是,在針對生活中的資訊建立資料庫時,
如何有結構的,並最大程度的避免資料庫裡的兩大禁忌:

  1. 不得空欄位 (null value)
  2. 不得重複的資料 (否則要更新資料的時候就要多個一起更新)

因此我們發展出了ER/EER Diagram。
它可以幫助我們釐清資料和資料之間的關係,以及我們如何去存放他們在schema裡。

本頁會主要聚焦於如何將資料轉換成ER/EER Diagram。

原則

ER-Model將一切的資料都拆解成三個部份 (類似物件導向的概念):

1. Entity / 實體

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

資料的形成勢必和某些物件或實體(Entity)有關。
其中還又有分:

  • Weak Entity
    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

    這種實體只能依存於其它的實體而存在。
    他的Primary Key只能在他自己的表格有用,出了他自己就不再是Candidate Key了。
    往往會跟Identifying Relationship一起出現。

2. Relationship / 關係

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

實體和實體之間的關係(Relationship)往往也會產生許多相關的資料。
其中也有分:

  • Identifying Relationship
    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

    當一個實體因另一個實體而存在於這個Database裡時(e.g. 員工的家屬、學校的電腦),就會形成Identifying Relationship。
    而主要的實體(e.g. 員工)的Primary Key往往會以Foreign Key的形式被加入次要的實體(e.g. 家屬)的表格裡。

而我們也因為想瞭解實體和實體之間的關係的細節,所以衍生了下列的符號:

  • Partial / Total Particapation

    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

    表示是否每個實體都有"參加該關係"。
    單線為Partial,雙線為Total。

  • Cardinality Ratio

    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

    數字表示如果"雙方都有實體參加該關係的前提下",那雙方實體的數量比為何。
    如果可能為1對多,則是1:N。
    如果雙方數量不定,則為M:N。
    有時會以下面的符號來表示:
    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

3. Attribute / 屬性

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

實體(Entity)、實體和實體之間的關係(Relationship)往往也都會產出衍生的屬性(Attribute)資料。
這些又分成以下幾種:

  • Key Attribute

    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

    通常為該實體/關係的Primary Key。

  • Multivalued Attribute

    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

    同一個㯗位有時也會有許多同性質的資料。(e.g. 台灣的城市、老師的外號etc)

  • Composite Attribute

    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

    像是名字、地址之類可以分很多段的資料。
    在形成DB Schema時,通常會被打散然後直接分開寫入DB。

  • Derived Attribute

    Image Not Showing Possible Reasons
    • The image file may be corrupted
    • The server hosting the image is unavailable
    • The image path is incorrect
    • The image format is not supported
    Learn More →

    當該屬性可以從同一個資料庫中得到線索並算出時,就被稱為Derived Attribute。
    e.g. 資料庫內已經有所有部門的清單了。那此時"部門數量"的欄位就是Derived Attribute。

Example

ER-Diagram

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

DB Schema

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

SQL Code

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →

Symbols

Image Not Showing Possible Reasons
  • The image file may be corrupted
  • The server hosting the image is unavailable
  • The image path is incorrect
  • The image format is not supported
Learn More →