Part 5 Normalization & Intersection Tables – Study Notes
Intersection tables and normalization overview
The intersection table is used to implement many-to-many relationships by gluing two tables together. If you have a student table and a schedule, you add a third (intersection) table in the middle to connect them.
The columns in the intersection table correspond to the keys from the related tables. A simple rule of thumb from the lecture:
Count the number of primary keys on the left side (e.g., one key) and the right side (e.g., two keys).
The intersection table will have a total of that many columns in its primary key.
Example: if one side has 1 key and the other side has 2 keys, the intersection table’s primary key has 3 columns. This can be expressed as: .
Normally, the intersection table has no other data besides the foreign keys that link the two tables. Its sole purpose is to glue the two mini tables together.
In more complex or later scenarios, you might store extra information in the intersection table (e.g., the date you signed up for a course), but that isn’t the standard use at this stage.
Example 1 (simplified): A student table and a schedule table join through an intersection table. The intersection’s PK consists of the student key and the schedule-course key(s) from the related tables; its total PK columns equal the sum of the PKs from both sides.
Example 2 (more complex): A more elaborate scenario with attributes such as semester, course, section, professor, etc., joined with a student table. If the intersection involves
3 keys on the left (e.g., semester, course, section) and 1 key on the right (e.g., student ID), then the intersection PK would have 4 columns: .
Duplicates after normalization:
After normalization, you generally should not be missing any columns and you should have very few, if any, duplicates.
Duplicates can occur, but usually only in the context of foreign keys. For example, two rows might reference the same student ID, but that duplication is acceptable because it’s tied to foreign key relationships, not data redundancy in non-key attributes.
Archiving historical values (practical note):
Sometimes you need historic values (e.g., unit price at the time of an order) rather than the current live price.
The common approach is to copy the value to a separate field or table (a historic snapshot, often described as a Xerox copy) so you preserve the past price.
This technique is discussed later in the course.
Quick wrap-up of normal forms (high level):
1NF: A functional dependency issue; a relation with a primary key. Focuses on ensuring the data is in a single-valued-domain format per attribute.
2NF: Introduces the idea of a composite primary key; every non-prime attribute must be fully functionally dependent on the entire candidate key (no partial dependency). The intuition per lecture: everything has to be related to both parts of a composite key.
3NF: Addresses transitive dependencies where one attribute determines another which then determines a third. The classic view is a “little hop” of dependencies: A -> B and B -> C implies A -> C; 3NF aims to eliminate transitive dependencies for non-prime attributes.
BCNF: A stricter form than 3NF; close the loopholes by ensuring every determinant is a candidate key. The description in practical terms is often framed as focusing on overlapping candidate keys.
4NF: Multivalued dependencies. A practical way to understand a 4NF violation is when adding one item would require inserting multiple rows (i.e., multivalued dependencies that are not tied to a superkey).
Supplemental materials mentioned in the course
A review of normal forms that revisits these concepts
Chapter 3 drills for practice
These resources are recommended to reinforce understanding and provide exercises.
Final takeaway from the chapter
You’ve reached the end of the chapter. Expect to see more on normalization, practice drills, and related topics in the next chapter.