The Normal Forms 3NF And BCNF
The Normal Forms
3NF and BCNF
Yunliang Jiang
Housekeeping
• HW2 due tonight
– Upload a single PDF/DOC file to Compass
• Stage 3 due tonight
• Midterm tomorrow
– During class time.
Preview
•
•
•
•
•
•
Normalization
Solution: Normal Forms
Introducing 3NF and BCNF
3NF
Examples
BCNF
Normalization
• Normalization is the process of efficiently organizing data in a database with two goals in mind
• First goal: eliminate redundant data
– for example, storing the same data in more than one table
• Second Goal: ensure data dependencies make sense – for example, only storing related data in a table
Benefits of Normalization
•
•
•
•
•
•
Less storage space
Quicker updates
Less data inconsistency
Clearer data relationships
Easier to add data
Flexible Structure
The Solution: Normal Forms
• Bad database designs results in:
– redundancy: inefficient storage.
– anomalies: data inconsistency, difficulties in maintenance • 1NF, 2NF, 3NF, BCNF are some of the early forms in the list that address this problem
Third Normal Form (3NF)
1) Meet all the requirements of the 1NF
2) Meet all the requirements of the 2NF
3) Remove columns that are not dependent upon the primary key.
1) First normal form -1NF
• 1NF : if all attribute values are atomic: no repeating group, no composite attributes
– Really easy to achieve
• The following table is not in 1NF
DPT_NO
MG_NO
EMP_NO
EMP_NM
D101
12345
20000
20001
20002
Carl Sagan
Mag James
Larry Bird
D102
13456
30000
30001
Jim Carter
Paul Simon
Table in 1NF
DPT_NO
MG_NO
EMP_NO
EMP_NM
D101
12345
20000
Carl Sagan
D101
12345
20001
Mag James
D101
12345
20002
Larry Bird
D102
13456
30000
Jim Carter
D102
13456
Paul Simon
30001
• all attribute values are atomic because there are no repeating group and no composite attributes.
2) Second Normal Form
• Second normal form (2NF) further addresses the concept of removing duplicative data:
– A relation R is in 2NF if
• (a) R is 1NF , and
• (b) all non-prime
3NF and BCNF
Yunliang Jiang
Housekeeping
• HW2 due tonight
– Upload a single PDF/DOC file to Compass
• Stage 3 due tonight
• Midterm tomorrow
– During class time.
Preview
•
•
•
•
•
•
Normalization
Solution: Normal Forms
Introducing 3NF and BCNF
3NF
Examples
BCNF
Normalization
• Normalization is the process of efficiently organizing data in a database with two goals in mind
• First goal: eliminate redundant data
– for example, storing the same data in more than one table
• Second Goal: ensure data dependencies make sense – for example, only storing related data in a table
Benefits of Normalization
•
•
•
•
•
•
Less storage space
Quicker updates
Less data inconsistency
Clearer data relationships
Easier to add data
Flexible Structure
The Solution: Normal Forms
• Bad database designs results in:
– redundancy: inefficient storage.
– anomalies: data inconsistency, difficulties in maintenance • 1NF, 2NF, 3NF, BCNF are some of the early forms in the list that address this problem
Third Normal Form (3NF)
1) Meet all the requirements of the 1NF
2) Meet all the requirements of the 2NF
3) Remove columns that are not dependent upon the primary key.
1) First normal form -1NF
• 1NF : if all attribute values are atomic: no repeating group, no composite attributes
– Really easy to achieve
• The following table is not in 1NF
DPT_NO
MG_NO
EMP_NO
EMP_NM
D101
12345
20000
20001
20002
Carl Sagan
Mag James
Larry Bird
D102
13456
30000
30001
Jim Carter
Paul Simon
Table in 1NF
DPT_NO
MG_NO
EMP_NO
EMP_NM
D101
12345
20000
Carl Sagan
D101
12345
20001
Mag James
D101
12345
20002
Larry Bird
D102
13456
30000
Jim Carter
D102
13456
Paul Simon
30001
• all attribute values are atomic because there are no repeating group and no composite attributes.
2) Second Normal Form
• Second normal form (2NF) further addresses the concept of removing duplicative data:
– A relation R is in 2NF if
• (a) R is 1NF , and
• (b) all non-prime