Here is some data about students and library books they have borrowed:
Data (not yet in normalized form):
Student ID Telephone (Book Title Due on Number of pages Publisher)
Q12345 8157530001 ((C# for Us 10/4/16 258 Addison-Wesley),
(Son of C# 10/16/16 357 Murach))
Q23456 8157530002 ((C# for Us 11/1/16 258 Addison-Wesley)
(Secrets of C# 10/20/16 412 Pearson))
Q34567 8157430003 C# Strikes Back 11/2/16 456 Murach
Q45678 8157530004 Caught in .NET 11/4/16 388 McGraw-Hill
This really has three columns; the third is a list of bundles of information about books that have been borrowed. The notation was improvised.
We are assuming the multiple copies of books are all alike, so the two copies of C# for Us are identical.
To begin with, we should not have a list for each student of what that student has borrowed. A slight improvement is:
Student ID Telephone Book Title Due on Number of pages Publisher Q12345 8157530001 C# for Us 10/4/16 258 Addison-Wesley Q12345 8157530001 Son of C# 10/16/16 357 Murach Q23456 8157530002 C# for Us 11/1/16 258 Addison-Wesley Q23456 8157530002 Secrets of C# 10/20/16 412 Pearson Q34567 8157430003 C# Strikes Back 11/2/16 456 Murach Q45678 8157530004 Caught in .NET 11/4/16 388 McGraw-Hill
Now we have six actual columns.
Observe the following: some of the data depends only on the Book Title and some depends only on the Student ID.
We can make this into two tables:
Book Title Number of pages Publisher C# for Us 258 Addison-Wesley Son of C# 357 Murach Secrets of C# 412 Pearson C# Strikes Back 456 Murach Caught in .NET 388 McGraw-Hill>
Student ID Telephone Book Title Due on Q12345 8157530001 C# for Us 10/4/16 Q12345 8157530001 Son of C# 10/16/16 Q23456 8157530002 C# for Us 11/1/16 Q23456 8157530002 Secrets of C# 10/20/16 Q34567 8157430003 C# Strikes Back 11/2/16 Q45678 8157530004 Caught in .NET 11/4/16
Here Book Title is the key field of the first table and Student ID is the key of the second table. In the second table, Book Title is a "foreign key".
Notice that the two tables now have different numbers of rows and columns.
Some people will have the following objection: The Due Date depends on both Student ID and Book Title while Telephone depends only on Student ID. That is, the key for the second table is Student ID + Book Title, but one field, Telephone, depends on only part of that key. For that reason, it may be better to separate Student ID and Telephone into a third table.
We end up with:
BookTable: (Key is Book-Title) Book Title Number of pages Publisher C# for Us 258 Addison-Wesley Son of C# 357 Murach Secrets of C# 412 Pearson C# Strikes Back 456 Murach Caught in .NET 388 McGraw-Hill BorrowTable: (Key is Student ID + Book Title) Student ID Book Title Due on Q12345 C# for Us 10/4/16 Q12345 Son of C# 10/16/16 Q23456 C# for Us 11/1/16 Q23456 Secrets of C# 10/20/16 Q34567 C# Strikes Back 11/2/16 Q45678 Caught in .NET 11/4/16 TelephoneTable: (Key is Student ID) Student ID Telephone Q12345 8157530001 Q23456 8157530002 Q34567 8157430003 Q45678 8157530004