Database Normalization: Understanding 1NF, 2NF, 3NF, BCNF, and 4NF, Slides of Database Management Systems (DBMS)

Download Database Normalization: Understanding 1NF, 2NF, 3NF, BCNF, and 4NF and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

4 Normal Form

Overview

• 1 NF
• 2 NF
• 3 NF
• BCNF
• 4 NF
• Conclusion

First Normal Form (1 NF)

• All values in the columns are atomic (simple,

indivisible). This is, they contain no

repeating values.

• There are no repeating groups: two columns

do not store similar information in the same

table.

• Basically: 1 NF is to eliminate duplicate

columns

Second Normal Form (2 NF)

• A relation is in 2 NF if it is in 1 NF and every

non-key attribute is fully functionally

dependant on the primary key

transitive dependency

Cust_ID Name Salesperson Region

transitive dependency

Cust_ID Name Salesperson

Salesperson Region

Cust_ID Name Salesperson Region

BCNF Example

• Given: R (A, B, C , D)
• A --> B, C, D
• B --> A, C, D
• C --> A , B, D
• D --> A, B, C

BCNF Example (Cont...)

• Determinants:
  • A, B, C, and D
• Candidate keys:
  • A, B, C, and D
• Since all the determinants are candidate

keys, this is BCNF.

Definition of MVD

• A multivalued dependency X->-> Y is an

assertion that if two tuples of a relation

agree on all the attributes of X, then their

components in the set of attributes Y may

be swapped, and the result will be two

tuples that are also in the relation

MVD Example

X Y Z

A B1 C

A B2 C

X Y Z

A B1 C

A B2 C

X ->-> Y

R(x, y, z)

4 NF Example

200 Arabic Singing

200 English Cooking

100 French Cooking

100 Kurdish Politic

100 English Teaching

Eid Language Skill

Assume the following relation:

Employee (Eid:pk1, Language:pk2, Skill:pk3)

4 NF Example (conti...)

200 Arabic Singing

200 English Cooking

100 French Cooking

100 Kurdish Politic

100 English Teaching

Eid Language Skill

Recall that a relation is in BCNF if all its determinant are candidate keys. Because relation Employee has only one determinant (Eid, Language, Skill) , which is the composite primary key. Since the primary is a candidate key, R is in BCNF.

Assume that there is no MVD, then this relation is 4 NF.

4 NF Example (conti...)

Thus employee 100 when she teaches, she uses English; but when she cooks, she uses French. This relation is in fourth normal form.

200 Arabic Singing

200 English Cooking

100 French Cooking

100 Kurdish Politic

100 English Teaching

Eid Language Skill

Not 4 NF Example

Assume the following relation with multivalued dependency:

Employee (Eid:pk1, Languages:pk2, Skills:pk3)

Eid --->> Languages Eid --->> Skills

Languages and Skills are independent.