Database Normalization: Keys, Forms, and Integrity Rules

Database Keys and Normal Forms

You guarantee uniqueness by designating a Primary Key (PK) - a column or set of columns that contains unique values for a table.

Foreign Key (FK) is a column in one table that references the primary key of another table.

Database Normal Forms

First Normal Form (1NF)

It states that all column values must be atomic.

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF and every non-key column is fully dependent on the entire PK. That is, tables should store data relating to only one “thing” (or entity), and that entity should be fully described by its primary key.

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and if all non-key columns are mutually independent.

Database Integrity Rules

Entity Integrity Rule

It states that primary keys cannot contain null (missing) data.

Referential Integrity

This rule states that the database must not contain any unmatched foreign key values. This implies that:

• A row may not be added to a table with a foreign key unless the reference value exists in the referenced table.
• If a value in a table that’s referenced by a foreign key is changed (or the entire row is deleted), the rows in the table with the foreign key must not be orphaned.

In Access, we have two options when a referenced PK value changes or a row is deleted:

• Disallow: The change is completely disallowed.
• Cascade: For updates, the change is cascaded to all dependent tables. For deletions, the rows in all dependent tables are deleted.

Examples of Queries

All products having Product Name beginning with the letter 'C' like “C*”

All Products having Product Name ending with the letter 'e' like “*e”

All products having Product Name containing the letters 'ar' like “*ar*”

The list of all "Seafood", "Beverages", and "Condiments" like “Seafood”

All beverages products with Unit Price that is greater than 60 like “beverages” >60

A Dynaset for all Orders made in the year 1996 between 01-Jan-1996 and 31-Dec-1996

A Dynaset for all Orders made during the month of February of the year 1997

A dynaset for products from two categories that the user will enter after running the query Like [Value1] Like [value 2]

A dynaset for products having a unit price between two values that the user will enter after running the query between [1stprice] and [2ndPrice]