Normalize Your Database to 1NF
First normal form eliminates repeating groups. This means that multi-valued attributes have been removed, and there is only a single value at the crossroads where the table record and column come together. Primary keys are also established with the first normal form; uniquely identifying records in a table.
You would start off with a collection of records equal to what you could organize and put into a spreadsheet, for example. This is how you would prepare your data for conversion during the normalization process. If you are still looking at books, you can see the example below that has book information including the available media such as print, e-book, or online. From there, you can transform the data into something better suited for database use.
Books Table (with repeating groups)
Title | MediaType |
Database Design | Print, e-book |
PHP | e-book |
Intro to Programming | Print, e-book, Online |
Since the MediaType column had repeating types, the first thing you would do is create a record for each unique grouping of records, and collect them into a new table for media.
Once you do that, you can establish a primary key on the Books table as BookID. There you know that each record is unique.
To further normalize the table, you would replace the word that describes the media type and replace it with a number that matches up with the new Media table. Convert the MediaType field into MediaID, serving as a foreign key in the Books table and referring to the primary key of the Media table.
Books Table (1NF)
(PK) BookID | Title | (FK) MediaID |
1 | Database Design | 1 |
2 | Database Design | 2 |
3 | PHP | 2 |
4 | Intro to Programming | 1 |
5 | Intro to Programming | 2 |
6 | Intro to Programming | 3 |
Media Table (1NF)
(PK) MediaID | Media |
1 | |
2 | eBook |
3 | Online |
Normalize Your Database to 2NF
Build on previous normalizations as you continue to normalize your data. That means the second normal form (2NF) must meet the 1NF requirements. In addition, 2NF requires that partial dependencies are removed. This creates more correctly grouped new entities.
What is a partial dependency?
Good question!
Attributes in a table should be dependent on the primary key.
For example, an Orders table like the one below would have an OrderID primary key. The other fields, like OrderDate, CustomerID, and so on, should be fully dependent on the primary key and nothing else.
Orders Table (with partial dependencies)
OrderID | OrderDate | CustomerID | LastName | Address | BookID | Title | Price | Quantity |
You have a partial dependency when an attribute depends on the primary key only partially, as you have probably guessed! But it takes some experience to be able to spot partial dependencies and determine where one table could be split into two or more separate tables.
Let's look at the example.
In the Orders table, the book information (BookID, Title, and Price) are only partially dependent on the primary key, OrderID. One book could be associated with multiple orders.
In this case, you would split the table into three separate ones, as shown below:
Orders Table (2NF)
OrderID | OrderDate | CustomerID | LastName | Address |
BooksCost Table (3NF)
BookID | Title | Price |
BookOrderQuantity Table (3NF)
OrderID | BookID | Quantity |
The table is transformed from the first normal form into the second normal form. (BookOrderQuantity and BookCost are already in the third normal form since you can’t normalize the tables any further.)
Why would we split off BookID and quantity into their own table?
If you kept those fields in the Orders table, you would end up with repeating groups, which you need to eliminate to meet the 1NF criteria.
Also, quantity is dependent on both OrderID and BookID. In the BookOrderQuantity table, the OrderID and BookID form a composite primary key, meaning the two fields together make the primary key. In the table, all non-key attributes (just the Quantity attribute) must be fully dependent on this composite primary key.
Normalize Your Database to 3NF
As mentioned, you should build upon the previous normal form as you normalize your data. The third normal form (3NF) must meet the 2NF requirements. In addition, you must remove any transitive dependencies.
What is a transitive dependency?
Transitive dependencies are values which are not at all dependent on the primary key of the table, but rather on another field or fields within the table.
Let's have a look at the example. Can you spot the transitive dependencies that remain?
Orders Table (with transitive dependencies)
OrderID | OrderDate | CustomerID | LastName | Address |
LastName and Address are dependent on the CustomerID field, which is a non-key field in this table.
Converting from 2NF to 3NF is as simple as splitting up the entity into two or more separate entities, with like attributes grouped together:
Orders Table (3NF)
OrderID | OrderDate | CustomerID |
Customers Table (3NF)
CustomerID | LastName | Address |
This is an easy step (with a little practice)! You'll find transitive dependencies where fields in a table create duplicate data and are not uniquely identified by the primary key. Here we break one table into two, and a closer look shows there isn't any redundant or duplicate data once you start building and populating the tables.
Let’s Recap!
Data must be arranged in a spreadsheet-like format to begin the normalization process.
Remove repeating groups as a first step in transforming data to 1NF (Recommended step order).
Establish primary keys on each table that uniquely identifies the record to be in 1NF for the last step (Recommended step order).
Second normal form, 2NF, must be in 1NF and partial dependencies removed.
Third normal form, 3NF, must be in 2NF with no transitive dependencies.
We're ready to optimize a database! Next we'll recap what we've learned throughout the course.
