Notes on Normal Forms in Database Design

First Normal Form (1NF)

  • Definition: The First Normal Form (1NF) is the foundational level of database normalization.
  • Conditions for 1NF:
    • Atomic Values: Each cell in a table must contain a single atomic value.
    • No Repeating Groups: There should be no sets of values or groups of data in a single cell.
    • Uniqueness of Records: Each record in the table must be unique.

Examples of 1NF


  • Bad Design Example:

Bars NameAddressLicenseBeer
Joe's Place1234 E Main StbeerBud, Corona
Sue's Bar4321 W University AvefullBud, Wicked Ale, MGD


  • Normalized (1NF):



    • Each beer is put on a separate row:

    • Bars NameAddressLicenseBeer
    Joe's Place1234 E Main StbeerBud
    Joe's Place1234 E Main StbeerCorona
    Sue's Bar4321 W University AvefullBud
    Sue's Bar4321 W University AvefullWicked Ale
    Sue's Bar4321 W University AvefullMGD

    Anomalies in Database Design

    • Update Anomaly: Occurs when a single fact or data item is altered in one location but not in others, leading to inconsistencies.
    • Deletion Anomaly: Happens when deletion of a record causes loss of important information, even if that information might be needed elsewhere.

    Second Normal Form (2NF)

    • Definition: The Second Normal Form (2NF) improves upon 1NF by eliminating partial dependencies.
    • Conditions for 2NF:
      • Must first satisfy 1NF.
      • No Partial Dependency: All non-key attributes must depend on the entire primary key, not just a part of it.

    Concept of Partial Dependency and Composite Keys

    • Applies only to tables with composite primary keys (a key made up of multiple attributes).
    • Every non-primary attribute must depend on the whole composite primary key to avoid partial dependencies.

    Examples of 2NF

    • Composite Key Example:
      • BarMenu table: | BarName | Beer | Brewer | Price |
        |---------------|-------|-----------|-------|
        | Joe's Place | Bud | Anheuser | 2.50 |
        | Joe's Place | Corona| Crown | 3.50 |
        | Sue's Bar | Bud | Anheuser | 2.00 |
        | Sue's Bar | Wicked Ale | Pete's | 2.50 |
      • Does not satisfy 2NF because Brewer depends only on Beer, not on both Bar_Name and Beer.

    Resolving Partial Dependencies

    • Tables After Decomposition for 2NF:

      1. Beers Table:

        BeerBrewer
        BudAnheuser
        CoronaCrown
        Wicked AlePete's
        MGDMiller

      2. BarPrice Table:
        | BarName | Beer | Price |
        |---------------|------------|-------|
        | Joe's Place | Bud | 2.50 |
        | Joe's Place | Corona | 3.50 |
        | Sue's Bar | Bud | 2.00 |
        | Sue's Bar | Wicked Ale | 2.50 |
        | Sue's Bar | MGD | 3.00 |

    • These new tables now satisfy the 2NF as they eliminate partial dependencies and ensure all attributes are dependent on the full primary key where applicable.