4.10 First, second, and third normal form

functional dependence

• Dependence of one column on another is called functional dependence.

• e.g. the data is linked.

  • Customer ID: Unique identifier for each customer.

  • Order ID: Unique identifier for each order.

  • Customer Name: Name of the customer.

  • Product: Purchased product details.

• Customer Name is dependent on Customer ID. Knowing the Customer ID uniquely determines the Customer Name.

Redundancy

Redundancy is the repetition of related values in a table.

Normal forms

• Normal forms are rules for designing tables with less redundancy.

• eliminates redundancy by decomposing a table into two or more tables.

• Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF

first normal form

1. Every cell of a table contains exactly one value. A table is in first normal form when

2. the table has a unique primary key.

3. no duplicate rows. Has Primary key that is unique, so every row is unique.

second normal form

• A table is in second normal form when all non-key columns depend on the whole primary key.

• a non-key column cannot depend on part of a composite primary key. A table with a simple primary key is automatically in second normal form.

third normal form

Informally, a table is in third normal form when all non-key columns depend on the key, the whole key, and nothing but the key