ER/EER Modeling
回到首頁: DB-Note
簡介
我們的目標就是,在針對生活中的資訊建立資料庫時,
如何有結構的,並最大程度的避免資料庫裡的兩大禁忌:
- 不得 有 空欄位 (null value)
- 不得 有 重複的資料 (否則要更新資料的時候就要多個一起更新)
因此我們發展出了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 →