3 Primary Levels of Database Normalization

Bellow you will find a copy of the original table: Grade Report

Student_ID Student_Name Major Course_ID Course_Title Instructor_Name Instructor_Location Grade
168300458 Williams IT ITS420 Database Imp Codd B104 A
168300458 Williams IT ITP310 Programming Parsons B317 B
543291073 Baker Acctg ITD320 Database Imp Codd B104 C
543291073 Baker Acctg Acct201 Fund Acctg Miller H310 B
543291073 Baker Acctg Mktg300 Intro Mktg Bennett B212 A

This table is in an un-normalized form due to the fact that is has repeating groups. For instance the Student_ID = 168300458 shows here twice and Student_ID = 543291073 shows 3 times. So there is no single field that may be used as a primary key for this table.  We will bring this table to 1NF (first normal form) in a moment, but for now let’s look at the dependence of the attributes of this table.

The functional dependencies are shown below:

  • Student_ID -> Student_Name, Major
  • Course_ID -> Course_Title, Instructor_Name, Instructor_Location
  • Student_ID, Course_ID -> Grade
  • Instructor_Name -> Instructor_Location

The dependencies of the attributes can be shown in the following dependence diagram.

To decompose this table into the first normal form (1NF) we will need to expand the primary key to include the both the Student_ID and the Course_ID and will create a composite key. When the primary key is expanded we now have 5 distinct records in the table. The table would now look like this:

Grade Report (Student_ID, Student_Name, Major, Course_ID, Course_Title, Instructor_Name,                 Instructor_Location, Grade)

Next, in order to get the table from a 1NF to a 2NF we will need to get rid of all partial dependencies. These dependencies are found whenever you have a composite key (key made of more than one field). So in short we will create a couple of tables and these table will contain each part of the former composite key so there will no longer be a composite key.

Students

Student_ID Student_Name Major
168300458 Williams IT
168300458 Williams IT
543291073 Baker Acctg
543291073 Baker Acctg
543291073 Baker Acctg

Courses

Course_ID Course_Title Instructor_Name Instructor_Location
ITS420 Database Imp Codd B104
ITP310 Programming Parsons B317
ITD320 Database Imp Codd B104
Acct201 Fund Acctg Miller H310
Mktg300 Intro Mktg Bennett B212

Grades

Student_ID Course_ID Grade
168300458 ITS420 A
168300458 ITP310 B
543291073 ITD320 C
543291073 Acct201 B
543291073 Mktg300 A

These tables can also be shown as:

Students (Student_ID, Student_Name, Major)

Courses (Course_ID, Course_Title, Instructor_Name, Instructor_Location)

Grades (Student_ID, Course_ID, Grade)

Foreign key: Student_ID to Students table

Foreign key: Course_ID to Courses table

We still have an issue that were a user could enter the Instructor_Name or Instructor_Location incorrectly and there could be a data mismatch. This is referred to as a data anomaly. For instance if a user was to in one record saw the instructor Codd was in location B104 and in another record indicate that Codd was in location B317, there is nothing to prevent this to happen. To prevent this kind of anomaly we will need to pull the Instructor_Name and Instructor_Location out and create a 4th table. In the Course table I will create a new field called Instructor_ID and this will be a foreign key to the Instructors table; due to the fact that Instructor_Name is too vague and could reference two instructors with the same name. In that 4th table the Instructor_ID will be the primary key.  The newly created database will now look like this and are now in a 3NF form:

Students

Student_ID Student_Name Major
168300458 Williams IT
168300458 Williams IT
543291073 Baker Acctg
543291073 Baker Acctg
543291073 Baker Acctg

Courses

Course_ID Course_Title Instructor_ID
ITS420 Database Imp 1001
ITP310 Programming 1002
ITD320 Database Imp 1001
Acct201 Fund Acctg 1003
Mktg300 Intro Mktg 1004

Grades

Student_ID Course_ID Grade
168300458 ITS420 A
168300458 ITP310 B
543291073 ITD320 C
543291073 Acct201 B
543291073 Mktg300 A

Instructors

Instructor_ID Instructor_Name Instructor_Location
1001 Codd B104
1002 Parsons B317
1003 Miller H310
1004 Bennett B212

These tables can also be shown as such:

Students (Student_ID, Student_Name, Major)

Courses (Course_ID, Course_Title, Instructor_ID)

Foreign key: Instructor_ID to Instructors table

Grades (Student_ID, Course_ID, Grade)

Foreign key: Student_ID to Students table

Foreign key: Course_ID to Courses table

Instructors (Instructor_ID, Instructor_Name, Instructor_Location)

Bellow you will find a Chen’s Model ER diagram of the 3NF tables:

References:

Adamski, J., & Finnegan, K. (2008). Microsoft Office Access 2007. Boston: Course Technology.

http://www.sethi.org/classes/cet415/lab_notes/lab_03.html, retrieved May 1, 2009

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s