把前段時間沒搞懂的第二正規化
跟,第三正規化
搞懂了,順便筆記一下
資料庫的發明,是為了快速讀取或寫入當下所需的資料
像是進入部落格的首頁,伺服器端就會向資料庫query出每篇文章的摘要,再送回用戶的瀏覽器
想像如果等10秒才載入完畢,那我的部落格豈還有人會等著載入完看嗎
因此,將所有資料塞在同個table顯然不一定是好的選擇
而將資料分門別類獨立出不同table儲存的動作即稱為資料庫正規化
適當的資料庫正規化有助於提升query效能
但是當過度得正規化則會讓邊際效益呈負的
正規化種類
上面已經提到,將資料分類歸到不同table的動作叫資料庫正規化
而這個資料庫正規化
,也是有分程度的:
這是日常普遍資料庫常見的三個正規化,再下去就有點像為了正規化而正規化,效能不增反減
當然,正規化只是種強迫症分類概念
我也有接觸過好幾個快30萬條row的table刻意只做第一正規化
的
第一正規化
原則
- 資料表要有主鍵
- 每個欄位只存放一個值
- 沒有多個代表相同意義的欄位
說明
拿最近學校叫我們填的教師期中意見評量當靈感
假設有意見評量回覆的metadata,直接原封不動塞進table內
課碼 | 課堂名稱 | 教師代碼 | 教師名稱 | 學生學號 | 學生名稱 | 滿意度 | 意見回饋 |
---|---|---|---|---|---|---|---|
0001 | 微積分 | T1003 | 周週粥 | 105210420 104209232 |
好勃村 安東尼 |
4 5 |
教得好 句句重點 |
0002 | 資料庫 | T2210 | 陳誠呈 | 103116198 104130224 |
王小明 金小美 |
4 4 |
還不錯 生動活潑 |
0003 | 服務學習 | T1165 | 木林森 | 104209232 104209005 |
安東尼 湯馬士 |
1 1 |
垃圾課欸 幹,拔草?! |
每一個row就是一堂課所有的feedback匯集
表面上看起來沒什麼問題,但如果今天想要SELECT
某學生(e.g. 安東尼)對所有老師的回饋呢
由於一堂課只有一個row,最多只能過濾到安東尼修過哪些課,沒辦法直接SELECT
他單獨的意見回饋
現在,我們將table進行第一正規化
把每個欄位多餘的值新增成不同的row,然後決定table的主鍵
在這邊補充一下
主鍵不一定要是唯一的索引
當整個table只有一個主鍵時,它是唯一的索引沒錯;
如果整個table含有兩個以上的主鍵時,「它們」的聯集才是唯一的索引。
「它們」也就是所謂的複合主鍵。
這邊我們用課碼跟學生學號來當作複合主鍵
就不用再新增一個無用的column加上流水號來做以往認知上的單一主鍵
*課碼 | 課堂名稱 | 教師代碼 | 教師名稱 | *學生學號 | 學生名稱 | 滿意度 | 意見回饋 |
---|---|---|---|---|---|---|---|
0001 | 微積分 | T1003 | 周週粥 | 105210420 | 好勃村 | 4 | 教得好 |
0001 | 微積分 | T1003 | 周週粥 | 104209232 | 安東尼 | 5 | 句句重點 |
0002 | 資料庫 | T2210 | 陳誠呈 | 103116198 | 王小明 | 4 | 還不錯 |
0002 | 資料庫 | T2210 | 陳誠呈 | 104130224 | 金小美 | 4 | 生動活潑 |
0003 | 服務學習 | T1165 | 木林森 | 104209232 | 安東尼 | 1 | 垃圾課欸 |
0003 | 服務學習 | T1165 | 木林森 | 104209005 | 湯馬士 | 1 | 幹,拔草?! |
這樣就完成第一正規化
了,是不是如果要SELECT
安東尼修過哪些課的意見回饋就容易得多了呢
第二正規化
原則
- 符合第一正規化
- 消除任何欄位與主鍵部分相依
說明
第二正規化
要做的其實就是去除重複的資料
我們可以看到,剛剛的table在做完第一正規化
後出現了許多重複的值
*課碼 | 課堂名稱 | 教師代碼 | 教師名稱 | *學生學號 | 學生名稱 | 滿意度 | 意見回饋 |
---|---|---|---|---|---|---|---|
0001 | 微積分 | T1003 | 周週粥 | 105210420 | 好勃村 | 4 | 教得好 |
0001 | 微積分 | T1003 | 周週粥 | 104209232 | 安東尼 | 5 | 句句重點 |
0002 | 資料庫 | T2210 | 陳誠呈 | 103116198 | 王小明 | 4 | 還不錯 |
0002 | 資料庫 | T2210 | 陳誠呈 | 104130224 | 金小美 | 4 | 生動活潑 |
0003 | 服務學習 | T1165 | 木林森 | 104209232 | 安東尼 | 1 | 垃圾課欸 |
0003 | 服務學習 | T1165 | 木林森 | 104209005 | 湯馬士 | 1 | 幹,拔草?! |
而且還出現了「部分相依」
部分相依
某些欄位「只」與複合主鍵中的「部份」欄位相依,
「沒有」與複合主鍵中的「全部」欄位相依。
我們把欄位與主鍵的相依關係做成表格,以方便判別
與課碼相依 | 與學生學號相依 | |
---|---|---|
*課堂名稱 | O | X |
*教師代碼 | O | X |
*教師名稱 | O | X |
*學生名稱 | X | O |
滿意度 | O | O |
意見回饋 | O | O |
並且在部分相依的欄位打星號
為了達成第二正規化
,我們先將「部分相依」的欄位獨立成為新的table
courses:
*課碼 | 課堂名稱 | 教師代碼 | 教師名稱 |
---|---|---|---|
0001 | 微積分 | T1003 | 周週粥 |
0002 | 資料庫 | T2210 | 陳誠呈 |
0003 | 服務學習 | T1165 | 木林森 |
相依於課碼的歸一個table
students:
*學生學號 | 學生名稱 |
---|---|
105210420 | 好勃村 |
104209232 | 安東尼 |
103116198 | 王小明 |
104130224 | 金小美 |
104209005 | 湯馬士 |
相依於學生學號的歸一個table
頓時,我們原本的table就瞬間乾淨了許多
feedbacks:
*課碼 | *學生學號 | 滿意度 | 意見回饋 |
---|---|---|---|
0001 | 105210420 | 4 | 教得好 |
0001 | 104209232 | 5 | 句句重點 |
0002 | 103116198 | 4 | 還不錯 |
0002 | 104130224 | 4 | 生動活潑 |
0003 | 104209232 | 1 | 垃圾課欸 |
0003 | 104209005 | 1 | 幹,拔草?! |
到此,這就是所謂的第二正規化
了
第三正規化
原則
- 符合第二正規化
- 消除主鍵以外的欄位與主鍵間接相依
說明
經過上面第二正規化
後,students跟feedbacks的table其實也已經符合第三正規化
了
唯一剩下courses這個table,它不符合第三正規化
的原因在它存在主鍵以外的欄位與主鍵間接相依
間接相依
兩個欄位並不是直接相依的關係,而是有另一個欄位夾在中間。
譬如說A相依於B,B又相依於C,則A就間接相依於C。
course:
*課碼 | 課堂名稱 | 教師代碼 | 教師名稱 |
---|---|---|---|
0001 | 微積分 | T1003 | 周週粥 |
0002 | 資料庫 | T2210 | 陳誠呈 |
0003 | 服務學習 | T1165 | 木林森 |
我們從左往右掃一遍,可以看到
- 課堂名稱相依於課碼
- 教師代碼相依於課碼
- 教師名稱相依於教師代碼
換言之,教師名稱「間接相依」於課碼
為了達成第三正規化
,我們要將教師名稱與課碼的間接相依關係消除
來吧,大膽將間接相依的欄位分割出去
course:
*課碼 | 課堂名稱 | 教師代碼 |
---|---|---|
0001 | 微積分 | T1003 |
0002 | 資料庫 | T2210 |
0003 | 服務學習 | T1165 |
teachers:
*教師代碼 | 教師名稱 |
---|---|
T1003 | 周週粥 |
T2210 | 陳誠呈 |
T1165 | 木林森 |
這樣最終就完成了第三正規化
現在,我們來回顧做完第三正規化
的四個table
feedbacks:
*課碼 | *學生學號 | 滿意度 | 意見回饋 |
---|---|---|---|
0001 | 105210420 | 4 | 教得好 |
0001 | 104209232 | 5 | 句句重點 |
0002 | 103116198 | 4 | 還不錯 |
0002 | 104130224 | 4 | 生動活潑 |
0003 | 104209232 | 1 | 垃圾課欸 |
0003 | 104209005 | 1 | 幹,拔草?! |
course:
*課碼 | 課堂名稱 | 教師代碼 |
---|---|---|
0001 | 微積分 | T1003 |
0002 | 資料庫 | T2210 |
0003 | 服務學習 | T1165 |
teachers:
*教師代碼 | 教師名稱 |
---|---|
T1003 | 周週粥 |
T2210 | 陳誠呈 |
T1165 | 木林森 |
students:
*學生學號 | 學生名稱 |
---|---|
105210420 | 好勃村 |
104209232 | 安東尼 |
103116198 | 王小明 |
104130224 | 金小美 |
104209005 | 湯馬士 |
是不是更 難閱讀 整齊乾淨了呢^^
額外補充
判斷相依性
有些人可能會有疑問說剛剛這張表滿意度及意見回饋與課碼跟學生學號的相依性怎麼判斷
與課碼相依 | 與學生學號相依 | |
---|---|---|
*課堂名稱 | O | X |
*教師代碼 | O | X |
*教師名稱 | O | X |
*學生名稱 | X | O |
滿意度 | O | O |
意見回饋 | O | O |
這邊引用一個簡單的判斷方法
假設在關聯表 R(X,Y,Z)中,包含一組功能相依(X,Y)->Z
如果我們從關聯表 R中移除任一屬性 X 或 Y 時,
使得這個功能相依(X,Y)->Z 不存在,
此時我們稱 Z 為「完全功能相依」於(X,Y)。反之,若(X,Y)->Z 存在,我們稱 Z 為「部份功能相依」於(X,Y) 。
套用上面的公式(課碼,學生學號)->滿意度
如果拿掉課碼或學生學號任意一個,滿意度就沒有任何意義了,因此,滿意度為完全相依
依樣畫葫蘆
(課碼,學生學號)->意見回饋
拿掉課碼或學生學號任意一個,意見回饋也沒有任何意義
所以大可斷定意見回饋也是完全相依
正規化快速判別技巧整理
如果對於上面敘述太長沒有看完,或不是很懂上面的敘述,這邊再提供另一種簡單的方式
第一正規化
- 找出或規劃欄位為主鍵
- 解決一個欄位有多個值的問題
- 解決多個欄位代表相同意義的問題
第二正規化
- 將一直重複的欄位獨立出來
第三正規化
- 將與主鍵無關的欄位獨立出來
有關淺談資料庫的正規化就到這邊(打這麼多好像也不淺了...),謝謝大家這麼有耐心地看完。
謝謝大大的清楚講解^^
謝謝支持與鼓勵👍
很喜歡大大的筆記 清楚又詳細
想請問大大的學習資訊都是從哪裡來的
比如看哪寫線上學習網站或書之類的 可以推薦一下
謝謝
我是大二在學校聽教授上課一知半解
回家後自己複習弄懂後再整理整理的
希望可以幫助到與我一樣處境的人^^
感謝大大清楚的講解,太清楚了
^^
寫的內容親切好懂。感謝!(方便電郵跟您討論些問題嗎?)
^^
太感謝,淺顯易懂的說明!
^^
非常清楚!淺顯易懂