Normal Forms 1 NF A table that qualifies

Normal Forms • 1 NF – A table that qualifies as a relation is in 1 NF. (Back) • 2 NF – A relation is in 2 NF if all of its nonkey attributes are dependent on all of the primary key. • 3 NF – A relation is in 3 NF if it is in 2 NF and has no determinants except the primary key. • Boyce-Codd Normal Form (BCNF) – A relation is in BCNF if every determinant is a candidate key. “I swear to construct my tables so that all nonkey columns are dependent on the key, the whole key and nothing but the key, so help me Codd. ”

Normalization • Normalization is a process of evaluating and converting a relation to reduce modification anomalies ﺍﻻﺷﻴﺎﺀ ﺍﻟﺸﺎﺫﻩ • Essentially, normalization detects and eliminates data redundancy

Normal Forms • Any table of data is in 1 NF if it meets the definition of a relation • A relation is in 2 NF if all its non-key attributes are dependent on all of the key (no partial dependencies) – If a relation has a single attribute key, it is automatically in 2 NF • A relation is in 3 NF if it is in 2 NF and has no transitive dependencies • A relation is in BCNF if every determinant is a candidate key • A relation is in fourth normal form if it is in BCNF and has no multi-valued dependencies

First Normal Form (1 NF) • To be in First Normal Form (1 NF) a relation must have only single-valued attributes -- neither repeating groups nor arrays are permitted

First Normal Form “any table of data that meets the definition of a relation” Note : A Student can only have One Activity if SID is the Key

Second Normal Form (2 NF) • To be in Second Normal Form (2 NF) the relation must be in 1 NF and each nonkey attribute must be dependent on the whole key (not a subset of the key) • No Partial Dependencies

Combination Key with Partial Dependence Key : SID, Activity Fee; Activity Fee

Second Normal Form “when all of a relation’s nonkey attributes are dependent on all of the key” (Note 2 Themes), or No Partial Dependencies 100 Golf 175 Swimming 200 Golf 65

Second Normal Form • Conversion to 1 NF involves removing repeating groups from potential relations Examples : any table consisting of atomic values • Conversion of 2 NF involves removing partial dependencies from 1 NF relations Example on next slide

ASSIGN PERSON_ID* PROJECT_BUDGET S 75 S 79 S 80 32 40 32 27 40 PROJECT* TIME_SPENT P 1 P 2 P 1 P 3 P 2 7 3 4 1 5 Functional Dependencies in ASSIGN PERSON_ID + PROJECT TIME_SPENT PROJECT_BUDGET To convert to 2 NF, factor out the partial dependence of PROJECT_BUDGET on PROJECT into a separate relation

PROJECTS PROJECT* PROJECT_BUDGET P 1 32 P 2 40 P 3 27 P 4 17 ASSIGNMENTS PERSON_ID* PROJECT* TIME_SPENT S 75 P 1 7 S 75 P 2 3 S 79 P 1 4 S 79 P 3 11 S 80 P 2 5

Note that the PROJECTS and ASSIGNMENTS Relations are in 2 NF, and have captured the basic ideal of DKNF with each relation modeling a basic fact about the database. Demonstrate the Join of PROJECTS and ASSIGNMENTS