Functional Dependency
Prime attribute -> non-Prime attribute
Partial Dependency
Part of Prime attribute -> non-Prime attribute
Transitive Dependency
non-Prime attribute -> non-Prime attribute
remove in BCNF
non-Prime attribute -> Prime attribute
1NF
- Eliminate repeating groups in table ( 消滅多重記錄 每一個Cell 只有一組資料 )
- Identify each set of related data with a primary key (每一條資料都需要獨一無二的主鍵)
Trading Table
| Customer Name | Date | Number |
|---|---|---|
| Mr. Chan | Monday | 19.00, -28.00 |
| Mr. Wong | Wed | -80.00, -80.00 |
| Mr. Lee | Friday | 100.00, -40.00, 150 |
- after 1NF.1
Trading Table
| Customer Name | Date | Number |
|---|---|---|
| Mr. Chan | Monday | 19.00 |
| Mr. Chan | Monday | -28.00 |
| Mr. Wong | Wed | -80.00 |
| Mr. Wong | Wed | -80.00 |
| Mr. Lee | Friday | 100.00 |
| Mr. Lee | Friday | -40.00 |
| Mr. Lee | Friday | 150 |
- after 1NF.2
Trading Table
| primary key | Customer Name | Date | Number |
|---|---|---|---|
| 1 | Mr. Chan | Monday | 19.00 |
| 2 | Mr. Chan | Monday | -28.00 |
| 3 | Mr. Wong | Wed | -80.00 |
| 4 | Mr. Wong | Wed | -80.00 |
| 5 | Mr. Lee | Friday | 100.00 |
| 6 | Mr. Lee | Friday | -40.00 |
| 7 | Mr. Lee | Friday | 150 |
2NF
- be in firsts normal form (1NF) (符合1NF)
- All attributes (Non-key Columns) dependent on the key (所有資料都是完全功能相依於主鍵 資料需直接相關於主鍵)
Price Table
| Item ID (primary key) | Price | Supplier ID | Supplier Name | Supplier Address |
|---|---|---|---|---|
| 100 | 59 | 1 | A Supplier | SA A |
| 102 | 20 | 1 | A Supplier | SA A |
| 100 | 69 | 2 | B Supplier | SA B |
- after 2NF.2
Price Table
| Item ID (primary key) | Supplier ID | Price |
|---|---|---|
| 100 | 1 | 59 |
| 102 | 1 | 20 |
| 100 | 2 | 69 |
Supplier Table
| Supplier ID (primary key) | Supplier Name | Supplier Address |
|---|---|---|
| 1 | A Supplier | SA A |
| 2 | B Supplier | SA B |
3NF
- the entity is in second normal form (2NF) (符合2NF)
- no non-prime attribute is transitively dependent of any key ( 如果 AB 可以求得C AB -> C , C 就不應該出現)
EX1
Order Table
| Order ID | Customer Name | Unit Price | Quantity | Total |
|---|---|---|---|---|
| 100 | David | 35 | 3 | 105 |
| 101 | Jim | 25 | 2 | 50 |
| 102 | Bob | 25 | 3 | 75 |
Total可由 Unit Price + Quantity 求得 (Unit Price + Quantity ) -> Total
所以刪除 Total
| Order ID | Customer Name | Unit Price | Quantity |
|---|---|---|---|
| 100 | David | 35 | 3 |
| 101 | Jim | 25 | 2 |
| 102 | Bob | 25 | 3 |
EX2
Price Table
| Item ID | Price | Supplier ID | Supplier Name | Supplier Address |
|---|---|---|---|---|
| 100 | 59 | 1 | A Supplier | SA A |
| 102 | 20 | 1 | A Supplier | SA A |
| 100 | 69 | 2 | B Supplier | SA B |
Supplier Address 可由 Supplier ID 求得 Supplier ID -> Supplier Address
所以刪除 Supplier Address 結果如上2NF 例子
BCNF / 3.5 NF
- It should be in 3re Normal Form
- For any dependency A -> B, A should be a super key
College Enrollment Table
| student_id | subject | professor |
|---|---|---|
| 101 | Java | P.Java |
| 101 | C++ | P.Cpp |
| 102 | Java | P.Java.2 |
| 103 | C# | P.Chash |
| 104 | Java | P.Java |
Candidate Key
(student_id, subject) -> professor
(non-prime attribute) professor -> subject (prime attribute)
professor can find subject but professor not super key NOT IN BCNF
| p_id | professor | subject |
|---|---|---|
| 201 | P.Java | Java |
| 202 | P.Cpp | C++ |
| 203 | P.Java.2 | Java |
| 204 | P.Chash | C# |
| student_id | p_id |
|---|---|
| 101 | P.Java |
| 101 | P.Cpp |
| 102 | P.Java_2 |
| 103 | P.Chash |
| 104 | P.Java |
https://zh.wikipedia.org/wiki/第一正規化
https://en.wikipedia.org/wiki/First_normal_form
http://epaper.gotop.com.tw/pdf/AED000900.pdf
http://www.gotop.com.tw/epaper/e0815/AED000900.pdf