Functional dependency and normalization

Question

I am trying to find a great resource to study for functional dependency and normalization.

Anyone have any idea where should I look to? I am having difficulty differentiating whether a FD is in 1NF, 2NF or 3NF?

I've been reading Wikipedia and used Google search to find good research, but can't find any that explains it in simple terms.

Maybe you all can share on how you learned FD's and normalization during your life as well.

Answer 1

A functional dependency defines a functional relationship between attributes. For example: PersonId functionally determines BirthDate (normally written as PersonId -> BirthDate). Another way of saying this is: There is exactly one Birth Date for any given instance of a person. Note that the converse may or may not be true. Many people may have been born on the same day. Given a BirthDate we may find many PersonId sharing that date.

Sets of functional dependencies may be used to synthesize relations (tables). The definition of the first 3 normal forms, including Boyce Codd Normal Form (BCNF) is stated in terms of how a given set of relations represent functional dependencies. Fourth and fifth normal forms involve Multi-Valued dependencies (another kettle of fish).

Here are a few free resources about Functional Dependencies, Normalization and database design. Be prepared to exercise your brain and math skills when studying this material.

The following are "slide shows" from various academic sites...

• Functional Dependencies
• Functional Dependencies and Normalization for Relational Databases
• The Relational Data Model: Functional-Dependency Theory

The following are academic papers. Heavier reading but well worth the effort.

• The Application of Functional Dependency Theory to Relational Databases
• A Simple Guide to Five Normal Forms in Relational Database
• Simple Conditions for Guaranteeing Higher Normal Forms in Relational Databases

If you are seriously interested in this subject I suggest you put out the cash for a good book on the subject of Relational Database Design. For example: An Introduction to Database Systems by C.J. Date

Answer 2

A functional dependency is a constraint between columns of your table. For example in the table person:

SSN         | Name         | Date of birth | Address     | Phone number 
------------------------------------------------------------------------
123-98-1234 | Cindy Cry    | 15-05-1983    | Los Angeles | 123-456-7891
121-45-6145 | John O'Neill | 30-01-1980    | Paris       | 568-974-2562
658-78-2369 | John Lannoy  | 30-01-1980    | Dallas      | 963-258-7413

Here, the value in the column SSN (Social Security Number) determines the values in columns name, date of birth, address and phone number. This means that if we had two rows with the same value in the SSN column, then values in columns name, date of birth, address and phone number would be equal. A person with SSN 123-98-1234 is always called Cindy Cry, is born on 15-05-1983, and so on. A situation like this is called functional dependency.

The notion of functional dependencies is used to define second, and third normal form, and the Boyce-Codd normal form (BCNF).

To read more about functional dependencies and normalization you can go to then well-known academic books like Introduction to Databases by C.J. Date, or any of the books by the H. Garcia-Molina, J.Ullman, J.Widom trio.

If you want a less formal approach, we're starting a series of posts on data normalization on our company blog.