ServersDatabase Normalization Page 2

Database Normalization Page 2

ServerWatch content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Second Normal Form requires that any non-key field be dependent upon the entire key. For example,
consider the StudentCourses table below, where StudentID and
CourseID form a compound primary key.

StudentID CourseID StudentName CourseLocation Grade
12345 3100 April Math Building A
12345 1300 April Science Building B

Student Name field does not depend at all on CourseID, but only on
Student ID. CourseLocation has be dependency on StudentID, but only
on CourseID.

This data should be split into three tables as follows:

Students Table

StudentID Name
12345 April

Courses Table

CourseID CourseLocation
3100 Math Building
1300 Science

Student Courses Table

StudentID CourseID Grade
12345 3100 A
12345 1300 B

this example, grade was the only field dependent on the combination
of StudentID and CourseID.

suppose that in the first table design, the first row of data was
entered with a StudentName of Aprok, a simple typo. Now, suppose the
following SQL is run.

Delete from StudentCourses where StudentName=”April”

erroneous “Aprok” row will not be deleted. However, in the final
design, using the following SQL:

Delete From StudentCourses where StudentID=12345

delete every course that April was in by using the ID.

Original date of publication, 02/21/2001

Get the Free Newsletter!

Subscribe to Daily Tech Insider for top news, trends & analysis

Latest Posts

Related Stories