Online College Courses for Credit

+
Second Normal Form

Second Normal Form

Rating:
(0)
Author: Sophia Tutorial
Description:

Recognize correctly normalized tables in second normal form based on a set of tables in first normal form.

(more)
See More
Tutorial

what's covered
This tutorial explores the second normal form built on a first normal form design in two parts:
  1. Introduction
  2. Moving Ratings Example

1. Introduction

With the second normal form, the database has to, first of all, be in the first normal form. Once it is in the first normal form, the other criterion is to ensure that each non-key attribute has to be functionally dependent on the primary key. As a reminder, functional dependency means that each column in a table that is not a primary key should be determined by that primary key. We will need to review each column to determine if it is dependent on and specific to the primary key in each table.

2. Moving Ratings Example

At the end of the first normal form, for the movie ratings database, we had the following:

  • Movie (MovieId, UserFirstName, UserLastName, MovieTitle, NumericRating, TextualRating, ReleaseDate)
  • Actor (MovieId, ActorId, ActorFirstName, ActorLastName)
  • Genre (MovieId, GenreId, GenreName)
Entity-relationship diagram

In the Movie table:

  • MovieId – The MovieId is the primary key of the Movie table so we can ignore it.
  • UserFirstName – No, the UserFirstName does not depend on the MovieId as it is specific to the user that is creating the rating.
  • UserLastName – No, the UserLastName does not depend on the MovieId as it is specific to the user that is creating the rating.
  • MovieTitle – Yes, the MovieTitle should be directly dependent on the MovieID as a different MovieId should also mean a different MovieTitle.
  • NumericRating – No, the NumericRating is not unique to a MovieId. A MovieId could have many different NumericRatings by different users.
  • TextualRating – No, the TextualRating is not unique to a MovieId. A MovieId could have many different TextualRating by different users.
  • ReleaseDate – Yes, the ReleaseDate should be directly dependent on the MovieID as a different MovieId should also mean a different ReleaseDate.
In the Actor table:

  • MovieId, ActorId – Primary keys, for now, we can ignore it.
  • ActorFirstName – Not entirely, the ActorFirstName is dependent on the ActorId but not on the MovieId. This creates an issue with a many-to-many relationship between the Movie and Actor table.
  • ActorLastName - Not entirely, the ActorLastName is dependent on the ActorId but not on the MovieId. This creates an issue with a many-to-many relationship between the Movie and Actor table.
In the Genre table:

  • MovieId, GenreId– Primary keys, for now, we can ignore it.
  • GenreName - Not entirely, the GenreName is dependent on the GenreId but not on the MovieId. This creates an issue with a many-to-many relationship between the Movie and Genre table.
Let us first start with the UserFirstname and the UserLasName. Both of these are unique to a user although, on their own, the fields are not unique as two different users could have the same name. This would create some problems in our data if we are trying to keep them separate. To get around that, we need to create a primary key which we will call UserId for the User table:

  • User (UserId, UserFirstName, UserLastName)
The Rating table will be next to look at. The rating is related to the User and the Movie and should contain the UserId and MovieId as foreign keys along with the NumericRating and TextualRating:

  • Rating (UserId, MovieId, NumericRating, TextualRating)
Next, we need to determine if those two foreign keys combined would be unique. This will go to the business rules as it depends if a user can submit more than one rating per movie. If a user cannot post more than one rating per movie, the combined values of the UserId and MovieId can be set as a composite primary key. However, as this was not specified either way in the business rules, it would be safer to create a separate primary key to allow the possibility of a user being able to add in another rating for a movie. With that, the Rating table would look like this:

  • Rating (RatingId, UserId, MovieId, NumericRating, TextualRating)
This would leave our Movie table with the following:

  • Movie (MovieId, MovieTitle, ReleaseDate)
We also have the following tables that also do not fully depend on the primary key due to the many-to-many relationships that we have still in place.

  • Actor (MovieId, ActorId, ActorFirstName, ActorLastName)
  • Genre (MovieId, GenreId, GenreName)
The actor can act in many movies and a movie can have many actors. In a movie, the actor acts as a character in a role. The business requirements do not define this so we do not have to add in a RoleFirstName or RoleLastName so our many-to-many relationship will be defined by a bridge table named Role to join the two tables of Actor and Movie together.

  • Movie (MovieId, MovieTitle, ReleaseDate)
  • Role (ActorId, MovieId)
  • Actor (ActorId, ActorFirstName, ActorLastName)
The ActorId and MovieId can be set up as a composite primary key. We can also do the same for the Genre table:

  • Movie (MovieId, MovieTitle, ReleaseDate)
  • MovieGenre (MovieId, GenreId)
  • Genre (GenreId, GenreName)
The resulting ERD for our second normal form would look like the following:

Entity-relationship diagram


summary
To build the second normal form, the ERD should be in the first normal form. We then ensure that all columns are functionally dependent on the primary key.

Source: Authored by Vincent Tran