Functional Dependencies
The value of one attribute in a table is determined entirely by the value of another attribute.
For example, the birthday of a Customer can be determined by the primary key of the Customer, in which record the birthday belongs. Here an Customer’s birthday depends on the Customer’s primary key.
Customer birthday (depends on) -> Customer ID
The notation X -> Y means if and only if each X value is associated with at most one Y value.
We can find the birthday of a Customer, thru the ID of the Customer. Using the ID, for a one to one relationship (that is one Customer has ONLY one birthday), we can find the only birthday of a Customer. Where as using the birthday, we might find ID’s of several Customers, since same birthday might belong to multiple persons.
Understanding First Three Normal Forms
First normal form sample:
1. Create atomic values, i.e. which can’t be broken down more.
Address: Location + City + State + Country
Should be formed with separate fields:
Address table: Address ID, Location, City, State, and Country.
2. Remove repetitive column groups.
Customer table: Customer ID, Customer Name, Address ID, Address ID
Should be formed as:
Customer table: Customer ID, Customer Name
Customer Address table: Customer ID, Address ID
Second normal form sample:
Remove partial dependency.
Order Item table: Order ID, Inventory Item ID, Inventory Item Sell Price, Inventory Item Sell Quantity
Should be formed as:
Order Item table: Order ID, Inventory Item ID, Inventory Item Sell Quantity
Inventory Item table: Inventory Item ID, Inventory Item Sell Price
Third normal form sample:
Move the non-keys to a separate table, which don’t depend on keys (or depends on non-key).
Sales Order table: Sales Order No, Date, Customer No, Customer Name
Should be formed as:
Customer table: Customer No, Customer Name
Sales Order table: Sales Order No, Date, Customer No