Skip to content

Latest commit

 

History

History
139 lines (113 loc) · 4.83 KB

Normalization Forms.md

File metadata and controls

139 lines (113 loc) · 4.83 KB

To create a database that demonstrates all these normalization forms (UNF, 1NF, 2NF, 3NF, EKNF, BCNF, 4NF, ETNF, 5NF, DKNF, 6NF), we need a step-by-step approach where each normalization form builds on the previous one. Below is an outline of a database structure that transitions through these forms:

1. UNF (Unnormalized Form)

  • Definition: Data is stored without any normalization; it may contain repeating groups or arrays.
  • Example Table: Orders
    OrderID CustomerName Products Quantities
    1 Alice [Apple, Orange] [2, 3]
    2 Bob [Banana, Grape, Apple] [1, 2, 1]

2. 1NF (First Normal Form)

  • Definition: Remove repeating groups; each column contains atomic values.
  • Example Table: Orders
    OrderID CustomerName Product Quantity
    1 Alice Apple 2
    1 Alice Orange 3
    2 Bob Banana 1
    2 Bob Grape 2
    2 Bob Apple 1

3. 2NF (Second Normal Form)

  • Definition: Remove partial dependencies; every non-prime attribute must be fully functionally dependent on the primary key.
  • Split into Tables:

    • Orders:

      OrderID CustomerName
      1 Alice
      2 Bob

    • OrderDetails:

      OrderID Product Quantity
      1 Apple 2
      1 Orange 3
      2 Banana 1
      2 Grape 2
      2 Apple 1

4. 3NF (Third Normal Form)

  • Definition: Remove transitive dependencies; non-prime attributes should not depend on other non-prime attributes.
  • Split into Tables:

    • Customers:

      CustomerID CustomerName
      1 Alice
      2 Bob

    • Orders:

      OrderID CustomerID
      1 1
      2 2

    • OrderDetails:

      OrderID ProductID Quantity
      1 1 2
      1 2 3
      2 3 1
      2 4 2
      2 1 1

    • Products:

      ProductID ProductName
      1 Apple
      2 Orange
      3 Banana
      4 Grape

5. BCNF (Boyce-Codd Normal Form)

  • Definition: Resolve overlapping candidate keys. Every determinant is a candidate key.
  • Example: Ensure that no anomalies exist in composite keys and resolve redundancy in keys by revisiting dependencies.

6. 4NF (Fourth Normal Form)

  • Definition: Eliminate multi-valued dependencies.
  • Split Example:

    • Orders:

      OrderID CustomerID
      1 1
      2 2

    • ProductsOrdered:

      OrderID ProductID
      1 1
      1 2
      2 3
      2 4
      2 1

    • ProductQuantities:

      ProductID OrderID Quantity
      1 1 2
      2 1 3
      3 2 1

7. 5NF (Fifth Normal Form)

  • Definition: Resolve join dependencies; ensure the database is decomposed into tables that can be recombined without loss of data.
  • Example: Further splitting based on multi-valued facts.

8. DKNF (Domain-Key Normal Form)

  • Definition: Ensure constraints are represented explicitly by domains and keys.
  • Example: Add checks on columns (e.g., Quantity > 0, ProductName is unique).

9. 6NF (Sixth Normal Form)

  • Definition: Decompose tables into irreducible components; applied in temporal databases to handle time-variant data.
  • Example:
    • OrderTime:
      OrderID ProductID Quantity StartDate EndDate
      1 1 2 2024-01-01 2024-02-01
      1 2 3 2024-01-01 2024-02-01

Key Observations

Each step introduces stricter requirements to minimize redundancy and ensure consistency. The database evolves to become more efficient but often requires more tables and joins for complex queries.