Normalize A Database To 3nf
Essay Preview: Normalize A Database To 3nf
Report this essay
Simple STEP BY STEP METHOD TO NORMALIZE TABLES TO 3NF
STEP 1:
Ask the following question:
DOES THE TABLE IN QUESTION HAVE ANY REPEATING GROUPS?
IT IS IN 1NF.
IT IS UNNORMALIZED, SO TO PUT IT IN 1NF, REMOVE THE REPEATING GROUPS.
STEP 2:
DOES THE PRIMARY KEY CONTAIN ONLY ONE COLUMN?
YES- THEN THE TABLE IS ALREADY IN 2NF.
NO- ASK THE FOLLOWING QUESTION:
DO ANY OF THE COLUMNS THAT ARE NOT KEYS HAVE THEIR VALUES DETERMINED BY ONLY SOME, AND NOT ALL, OF THE COLUMNS THAT MAKE UP THE KEY?
YES- THEN THE TABLE IS NOT IN 2NF.
To put it in 2NF, remove any columns that are dependent upon only a portion of the key, and create separate or separate tables.
NO- THEN THE TABLE IS IN 2NF.
STEP 3:
ARE ALL OF THE COLUMNS IN THE TABLE DETERMINED ONLY BY CANDIDATE KEYS?
YES- THEN THE TABLE IS IN 3NF.
NO- REMOVE ANY COLUMNS THAT ARE NOT FULLY DETERMINED BY CANDIDATE KEYS, AND AGAIN CREATE ADDITIONAL TABLE(S) THAT CONTAIN THOSE COLUMNS.
CONSIDER THE FOLLOWING TABLE:
Assumptions: Assume a student only has one advisor, and can only take a course once.
Note that in this example, the primary key consists of 2 columns -STUDENTID and COURSECODE.
STUDENT (STUDENTID, STUDENTLASTNAME, STUDENTFIRSTNAME, ADVISORID, ADVISORLASTNAME, ADVISORFIRSTNAME, GRADE, COURSECODE)
SAMPLE TABLE:
Smith
Jones
Sally
CIT150
CIT160
CIT170
CIT180
Adams
William
Johnson
Charles
CIT150
CIT160
Davis
Robert
Jones
Sally
CIT150
CIT160
Consider the above table.
STEP 1:
Does it have any repeating groups?
YES. Notice that student 10 has 4 grades corresponding to 4 courses that she took. These are repeating groups. Therefore, the table is unnormalized, because it isnt in 1NF.
So, to put it in 1NF, we must REMOVE the repeating groups. One way to do that is as follows:
Smith
Jones
Sally
CIT150
Smith
Jones
Sally
CIT160
Smith
Jones
Sally
CIT170
Smith
Jones
Sally
CIT180
Adams
William
Johnson
Charles
CIT150
Adams
William
Johnson
Charles
CIT160
Davis
Robert
Jones
Sally
CIT150
Davis
Robert
Jones
Sally
CIT160
It is now in 1NF.
STEP 2:
DOES THE PRIMARY KEY CONTAIN ONLY ONE COLUMN?
No, it consists of 2 columns, so we need to ask an additional question:
DO ANY OF THE COLUMNS THAT ARE NOT KEYS HAVE THEIR VALUES DETERMINED BY ONLY SOME, AND NOT ALL, OF THE COLUMNS THAT MAKE UP THE KEY?
The answer is YES. Lets look at the columns that are determined by only a portion of the key:
The easiest way to express this is by using the determinant expression
A B
STUDENTID
STUDENTLASTNAME, STUDENTFIRSTNAME,
STUDENTFIRSTNAME, ADVISORID, ADVISORLASTNAME, ADVISORFIRSTNAME
ADVISORID
ADVISORLASTNAME,ADVISORFIRSTNAME
STUDENTID, COURSECODE GRADE
In this case, the ONLY column that is determined by the entire key is GRADE.
That means there is some real work to do here. Every other non-key column violates the 2NF condition.
Remember