Third Normal Form 3 NF To be in
Third Normal Form (3 NF) • To be in Third Normal Form (3 NF) the relation must be in 2 NF and no transitive dependencies may exist within the relation. ﻻﻳﻮﺟﺪ ﺗﻌﺪﺩ ﺍﻋﺘﻤﺎﺩﻳﺔ • A transitive dependency is when an attribute is indirectly functionally dependent on the key (that is, the dependency is through another non-key attribute)
Violation of 3 NF and SID Building
Third Normal Form “if it is in second normal form and has no transitive dependencies”(Note 2 Themes)
3 NF - Another Example SUPPLIERS SUPPLIER* CITY DISTANCE S 1 S 2 S 3 S 4 BOSTON CHICAGO 200 1800 Functional Dependencies in SUPPLIERS SUPPLIER CITY SUPPLIER DISTANCE CITY DISTANCE, Therefore, by transitivity SUPPLIER CITY DISTANCE
Factor out the transitive dependence into a separate relation SUPPLIERS DISTANCE SUPPLIER*CITY S 1 BOSTON S 2 BOSTON S 3 CHICAGO S 4 CHICAGO CITY* BOSTON CHICAGO DISTANCE 200 1800 Again, the DKNF ‘basic fact / relation’ is captured Remember that 3 NF involves removing dependencies among nonprime attributes from 2 NF relations Show the Join of SUPPLIERS and DISTANCE
Fourth Normal Form (4 NF) • To be in Fourth Normal Form (4 NF) the relation must be in BCNF and the relation may not contain multi-valued dependencies. • Example: Student (SID*, Major*, Activity*) SID, Major, Activity NULL SID ->> Major SID ->> Activity
Example: 4 NF Note: To add 100 Skiing we need to add two rows
Example: 4 NF Note: To add 100 Skiing we need to add only one row
- Slides: 8