Database Normalization – page 2
Digg
DZone
Reddit
Slashdot
StumbleUpon
del.icio.us
Facebook
FriendFeed
Furl
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 |
The 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 Building |
Student Courses Table
| StudentID | CourseID | Grade |
| 12345 | 3100 | A |
| 12345 | 1300 | B |
In this example, grade was the only field dependent on the combination of StudentID and CourseID.
Let's
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"
The erroneous "Aprok" row will not be deleted. However, in the final design, using the following SQL:
Delete From StudentCourses where StudentID=12345 |
Original date of publication, 02/21/2001
Solid state disks (SSDs) made a splash in consumer technology, and now the technology has its eyes on the enterprise storage market. Download this eBook to see what SSDs can do for your infrastructure and review the pros and cons of this potentially game-changing storage technology.
