What are the steps to put a table in second normal form?
To put a table in second normal form, follow these steps:
- Identify the functional dependencies: Determine which attributes in the table depend on which other attributes. This means that if you change the value of one attribute, it will affect the value of another attribute.
- Create separate tables: If any attributes depend on only part of the primary key, create a new table for them. Move the attribute(s) and the part of the primary key they depend on to the new table, along with a copy of the primary key.
- Assign foreign keys: In the original table, add a foreign key that references the primary key in the new table. This foreign key is used to link the two tables together.
- Repeat as necessary: If there are still attributes that depend on only part of the primary key in the new table(s), repeat steps 2 and 3 until all tables are in second normal form.
By following these steps, you can ensure that your table is in second normal form, which means that it is free from certain types of data anomalies that can occur in poorly designed tables.
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.