Online College Courses for Credit

+
First Normal Form

First Normal Form

Rating:
(0)
Author: Sophia Tutorial
Description:

Recognize correctly normalized tables in first normal form based on an unnormalized data set.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores the first normal form on an unnormalized data set. <be></be>

With the first normal form, as you remember, we can only have single values that are permitted at the intersection of each row and column meaning that there should be no repeating groups of data. In order to manage this appropriately, we need to take any repeating group and remove that repeating group by creating two new relations. As a starting point, the primary key of the new relation will be a combination of the primary key of the original relation in addition to the attribute from the newly created relation to keep it unique.

Let us start here with the movie rating table below.

Here in the Movie table, we have the following attributes:

  • Movie (User, MovieTitle, NumericRating, TextualRating, Actor, Genre, ReleaseDate)
In reviewing the data, we do not have any attributes that uniquely identify each row. We will create a new PK called MovieId:

  • Movie (MovieId, User, MovieTitle, NumericRating, TextualRating, Actor, Genre, ReleaseDate)
In this table, we have a few repeating groups including the Actor as there can be many actors in a movie. In the Genre column, this is also a repeating group as there are many genres that a movie can be in. Let us first start by removing the repeating groups for the Actor and Genre and creating a new table for each. We will also need to create a primary key on the Rating table.

  • Movie (MovieId, User, MovieTitle, NumericRating, TextualRating, ReleaseDate)
  • Actor (MovieId, ActorId, ActorName)
  • Genre (MovieId, GenreId, GenreName)
The resulting ERD should look like the following:

By going through the first normal form, we have removed al of the repeating groups (multiple entries for a single record). However, along with the first normal form, there can be scenarios where we may have multiple values stored in a single column. In our case, the Actor’s name and the User can be split into a first and last name creating the following:

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

This should be the complete first normal form ready for us to move to the second normal form.


summary
The first normal form will ensure that we will be able to uniquely identify each row in each table as well as remove all repeating groups from our tables.