Database Normalization
Crow's Foot Diagrams use boxes to represent tables and lines with notation symbols to represent relationships between tables.
From Repeated Names to Relationships
Imagine a music database in which every track row stores the artist's full name. At first, this seems convenient because the name is visible wherever the track appears. But if the same artist has many tracks, the same string is repeated across many rows. Database normalization addresses this repetition by storing the artist once in a separate table and using a numeric key to refer to that artist from each track row.
The fundamental rule presented here is: never repeat the same string data in a column more than once. When a value repeats, consider storing it once in a separate table and referencing it with a numeric ID.
What do you think happens?
A database stores the artist name in every track row. What is the main change normalization makes?
Reveal answer
Answer: Replace the repeated name with a numeric artist ID and create an Artist table
The Artist table stores each artist once. Track rows store the artist's numeric ID, which creates a relationship to the matching artist row.
The Before-and-After Structure
Before normalization, the Track table can contain the artist string repeatedly. After normalization, a dedicated Artist table contains one row for The Beatles with artist_id 1. Track rows contain artist_id 1 instead of repeating The Beatles. The ID is not the artist's name; it is the value used to locate the matching row in Artist.
How Numeric Keys Create Links
Connecting Let It Be to its artist
A Track row for Let It Be contains artist_id 1. The Artist table contains a row with artist_id 1 and the name The Beatles. How does the database connect the track to the artist?
Read the Track key: The Track row contains artist_id 1.
Find the matching Artist row: The database looks for the Artist row whose ID is also 1.
Follow the relationship: The matching Artist row identifies The Beatles, so the track is connected to The Beatles without storing that name in the Track row.
The shared numeric ID establishes the relationship between the Track row and the Artist row.
A relationship is a connection between rows in different tables established through matching numeric IDs. In this example, artist_id in Track refers to the ID in Artist. If the artist's name changes, the name can be updated once in Artist rather than being changed in every Track row.
Normalization reduces redundancy, simplifies updates, saves storage space, and helps prevent inconsistent copies of the same string data.
Reading Crow's Foot Diagrams
A Crow's Foot Diagram turns a data model into a visual structure. Each table is shown as a rectangular box. The top section contains the table name, and the sections below list its columns. A primary key is commonly marked with PK or an asterisk. A foreign key is commonly marked with FK or a dagger symbol. Lines connect related tables through their key columns.
The line between Artist and Track represents their relationship. The primary key in Artist is connected conceptually to the foreign key in Track. Reading from the Artist end, one artist can be associated with many tracks. The key columns make the dependency visible: Track.artist_id refers to Artist.artist_id.
Cardinality and Optionality Symbols
The notation at each end of a relationship line communicates two separate ideas. Cardinality describes how many related records are involved. Optionality describes whether the relationship is required or may be absent. You must interpret the symbols from the perspective of the table at the end where they appear.
| Notation combination | Cardinality | Optionality | Meaning |
|---|---|---|---|
| Single dash with perpendicular line | One | Mandatory | Exactly one record is required |
| Single dash with circle | One | Optional | Zero or one record is allowed |
| Crow's foot with perpendicular line | Many | Mandatory | Many records are required |
| Crow's foot with circle | Many | Optional | Zero or many records are allowed |
The four combinations formed by cardinality and optionality symbols
A single dash expresses one, while a crow's foot expresses many. A perpendicular dash expresses mandatory participation, while a circle expresses optional participation. Combining one symbol from each category gives the complete meaning at that end of the line. The relationship type becomes clear only after both ends are read together.
Constructing a Relationship Diagram
- Identify the tables that contain the related data.
- Draw a box for each table and place the table name and columns inside it.
- Mark the primary key in each table.
- Identify the foreign key that refers to another table's primary key.
- Connect the related key columns with a relationship line.
- Read the relationship from both ends and add the cardinality and optionality symbols that describe the allowed records.
Building the Artist-to-Track model
Represent the relationship between the Artist and Track tables in the music database.
Separate the entities: Use Artist for artist information and Track for track information. This separation prevents the artist string from being repeated in every track row.
Mark the keys: Artist contains artist_id as its primary key. Track contains its own primary key and artist_id as a foreign key.
Connect the keys: The Track artist_id refers to the matching Artist artist_id, creating the relationship between rows.
Interpret the relationship: The diagram communicates that one Artist can be associated with many Tracks. The exact optionality is represented by the symbols placed at the two ends of the line.
The completed diagram has Artist and Track boxes, a key-based relationship line, and notation describing how many related rows are allowed or required.
The three fundamental relationship types are one-to-one, one-to-many, and many-to-many. Their notation patterns become recognizable by reading the cardinality symbols at both ends of the relationship line.
Mistakes That Preserve Redundancy
Repeating the artist name in every Track row
The same string data is repeated, so a name change requires updates in multiple rows and can produce inconsistent values.
Fix:
Store The Beatles once in Artist and place its numeric artist_id in the related Track rows.Treating a numeric ID as the displayed data itself
The numeric ID is a reference used to find the related Artist row.
Fix:
Follow artist_id to the matching Artist row when the artist information is needed.Reading only one end of a relationship line
The other end supplies the corresponding cardinality, and both ends also contain optionality information.
Fix:
Interpret both ends of the line from the perspective of the table where each symbol is attached.Ignoring primary-key and foreign-key markings
The relationship is created through a primary key and a matching foreign key.
Fix:
Mark the key columns and connect the foreign key in one table to the referenced primary key in the other.
Practice: Trace and Normalize
A Track table contains three rows. Two rows repeat the same artist string. Describe the normalization changes you would make, name the two tables, identify the numeric key, and explain how a Track row would find its artist.
Hints
- Look for the string that repeats across rows.
- Create a separate table whose row stores that string once.
- Replace the repeated string in Track with a numeric artist_id.
- Match the Track artist_id with the corresponding Artist primary key.
When reading a Crow's Foot Diagram, describe the meaning of each end of a relationship line in two parts: first state the cardinality, then state whether the relationship is mandatory or optional. Use the four combinations in the notation table to form complete interpretations.
Hints
- A single dash means one.
- A crow's foot means many.
- A perpendicular dash means mandatory.
- A circle means optional.
Normalization in One View
- Normalization identifies repeated string data and moves it into a separate table where it is stored once.
- Numeric keys replace repeated strings in related rows and establish relationships between tables.
- A Crow's Foot Diagram represents tables as boxes and relationships as lines connecting key columns.
- Cardinality symbols describe one versus many, while optionality symbols describe required versus optional participation.
- Reading both ends of a relationship line reveals the complete relationship structure.
Key Takeaways
- Normalization prevents the same string data from being repeated across rows by storing it once in a separate table.
- Numeric primary and foreign keys connect related rows without repeating descriptive strings.
- Crow's Foot Diagrams use table boxes, key columns, relationship lines, and notation symbols to show database structure.
- Dashes and crow's feet communicate cardinality; perpendicular dashes and circles communicate optionality.
- A relationship must be read from both ends of its line to understand the complete connection.