| Lesson 2 | Limitations of second normal form |
| Objective | Explain limitations of Second Normal Form |
Limitations of Second Normal Form
Second normal form is an improvement over first normal form, but it still has some limitations. Consider the following relation, which stores monthly sales data for different categories of CDs offered by Stories on CD. In this relation, we assume each CD belongs to a single category.
Item (ItemNo, Title, Price, CategoryID, CategoryName, CategoryManager)The functional dependency[1] for the relation would be written:
ItemNo -> (Title, Price, CategoryID, CategoryName, CategoryManager)
2NF Characteristic
Since each CD belongs to a single category, every non-key attribute of the relation is determined by the primary key (in this case, ItemNo), so the relation is in 2NF. There is a problem with this relation and that is, it contains a transitive dependency.
As you will see later in this module, third normal form is designed to eliminate transitive dependencies[2]. The next lesson defines transitive dependencies.
As you will see later in this module, third normal form is designed to eliminate transitive dependencies[2]. The next lesson defines transitive dependencies.
If a>b and b>c, then a>c.In terms of a database, a transitive dependency refers to a relationship between two entities that is dependent upon a second relationship between one of the first two entities and a third entity.
Second Normal Form
First normal form requires every table to have a primary key. This primary key can consist of one or more columns.
The primary key is the unique identifier for that row, and second normal form states that there must be no partial dependences of any of the columns on the primary key. For example, imagine that you decide to store a list of films and people who starred in them. The data being stored is film ID, film name, actor ID, actor name, and date of birth. You could create a table called ActorFilms using those columns:
The primary key is the unique identifier for that row, and second normal form states that there must be no partial dependences of any of the columns on the primary key. For example, imagine that you decide to store a list of films and people who starred in them. The data being stored is film ID, film name, actor ID, actor name, and date of birth. You could create a table called ActorFilms using those columns:
ActorFilms table
| Field Name | Data Type | Notes |
| FilmId | integer | Primary key |
| FilmName | varchar(100) | |
| ActorId | integer | Primary key |
| ActorName | varchar(200) | |
| DateOfBirth | date |
This table is in first normal form, in that it obeys all the rules of first normal form. In this table, the primary key consists of FilmId and ActorId. Combined they are unique, because the same actor cannot have been in the same film twice.
However, the table is not in second normal form because there are partial dependencies of primary keys and columns.
FilmName is dependent on FilmId, and there is no real link between a film's name and who was in the film..
ActorName and DateOfBirth are dependent on ActorId, but they are not dependent on FilmId, because there is no link between a film ID and an actor's name or their date of birth. To make this table comply with second normal form, you need to separate the columns into three tables.
First, create a table to store the film details, somewhat like the Films table in the example database:
Table 1: Film Details
| Field Name | Data Type | Notes |
| FilmId | integer | Primary key |
| FilmName | varchar(100) |
Next, create a table to store details of each actor:
Table 2: Actor Details
| Field Name | Data Type | Notes |
| ActorId | integer | Primary key |
| ActorName | varchar(200) | |
| DateOfBirth | date |
Finally, create a third table storing just FilmIds and ActorIds to keep track of which actors starred in which films:
Table 3: Actor to Films correspondence
| Field Name | Data Type | Notes |
| FilmId | integer | Primary key |
| ActorId | integer | Primary key |
[1]functional dependency: A one-way relationship between two attributes so that, for any unique value of one attribute, there is only one value of the other attribute.
[2]transitive dependency: A term derived from the mathematical equation for a transitive property.