|
Normalization
- Minimize data redundancy, maintain db consistency, save storage, prevent
misspell
- Process of dividing table into > 2 with defined relationship.
1 NF ( Remove duplicate columns)
- No duplicate group.
- Create separate tables for each group of related data and identify
each row with a
Unique column (the primary key).
Student
id |
Advisor |
Advisor room |
Cls1 |
Cls2 |
1 |
Jon |
5 |
A |
B |
2 |
Tim |
6 |
C |
D |
Not in NF1 because of Cls1 , Cls2 ( duplication of column)
2. NF2 (remove duplicative rows)
-Must be in NF1
-every non-key column is fully dependent upon the primary key.
Student id |
Advisor |
Advisor Room |
Cls |
1 |
Jon |
5 |
A |
1 |
Jon |
5 |
B |
2 |
Tim |
6 |
C |
2 |
Tim |
6 |
D |
In NF1, but Not in NF2 because duplication of row
Studentid 1 & 2 occurs twice in the records. We need to remove them
to get into NF2
1
| Studentid |
Advisor |
Advisor Room |
1 |
Jon |
5 |
2 |
Tim |
6 |
|
2
Studentid |
Cls |
1 |
A |
1 |
B |
2 |
C |
2 |
D |
|
DB in NF2, but not NF3.
Advisor -> studentid, BUT Advisor room Not dependent
on student id (non-depend key)
3. NF3 (all attribute Must Be functionally
dependent on PK )
(remove non-dependent key on PK)
From table 1, divide into 2 table to minimize redundancy
Studentid |
Advisor |
1 |
Jon |
2 |
Tim |
|
Advisor |
Advisor Room |
Jon |
5 |
Tim |
6 |
|
Back
|
Student
|
Enrollment
| SID |
CID |
EnrDate |
| 1 |
333 |
|
| 2 |
222 |
|
| |
|
|
|
Courses
| CID |
Title |
Unit |
Price |
Total |
| 333 |
|
|
|
|
| 333 |
|
|
|
|
| 222 |
|
|
|
|
| 222 |
|
|
|
|
|
Analyst
-Enrollment has composite key PK =(SID,CID)
-Student has 1-to-1 Enrollment
-Enrollment has 1-to-many Courses
-CID (PK of Courses ) is FK for Enrollment-
-DB is in 2NF ->No duplicate columns (1NF), No duplicate row
in Enrollment
-DB NOT in 3NF -> Total is dependent on Unit+price. Kick it
out
_DB is in 3NF ->all atts fully func dependent on PK |
Back
|
