When it comes to deciding what is driving database design
, 1) functional dependencies or 2) entities,
this question is really a chicken and egg
Normalization seems to share little with the entity relationship model
However, it turns out that a 1) relational design based on normalization and 2) an ER design transformed into relational
tables have almost identical results, and the two approaches reinforce each other.
In the normalization approach, the designer starts with a real-world situation to be modeled and lists the data items that are candidates to become column names in relational tables, together with a list of rules with respect to how these data items are related.
The objective is to represent all these data items as attributes of tables so that obey restrictive conditions associated with what we call normal forms
These normal form definitions limit the acceptable form of a table so that it has certain desirable properties, thus avoiding various kinds of
anomalies. There exists a series of normal form definitions, where each definition is more restrictive than the previous.
What is most important is that there is consistency between the ER diagram and the functional dependencies
you identify in your relations. It makes no difference whether you design by looking for 1) functional dependencies or 2) entities.
In most cases database design is an iterative process (as mentioned in the database life cycle
) in which you create an initial design, check it, modify it, and check it again. You can look at either functional dependencies and/or entities at any stage in the process, checking one against the other for consistency
A table T in a database schema with Functional Dependency set F is said to be in second normal form (2NF) under the following condition:
For any functional dependency X --> A implied by F that lies in T, where A is a single attribute that is not in X and is nonprime, X is not a proper subset of any key K of T. A database schema is in 2NF when all the tables it contains are in 2NF.
Before you move on to the next lesson, complete the following exercise to reinforce your understanding of second normal form.
Second Normal Form - Exercise
The next lesson describes the limitations of second normal form.