2nd Normal Form

1. 2nd Normal Form

Ensuring that only atomic values are stored in database tables is an important step towards normalizing a database. It is, however, the beginning of the normalization process, not the end. We will now discuss the next step in this process.

2. Example: school textbooks

Returning to our high school database example. School administrators would also like to manage the textbooks used in each of the school's courses within the database. A textbook table is proposed. This table has 5 columns representing the textbook id and title, the publisher's name, and website, and the number of books in stock in the schools bookstore.

3. Example: school textbooks

Here are a sample of 3 rows from this table. We will now explore why such a table structure is problematic.

4. Example: inconsistency from updating url

Imagine that ABC Publishing decides to update their website url. This sample only shows two of the 20 textbooks from this publisher that is stored in this textbook table. Updating the publisher underscore site column will require multiple table rows to be updated.

5. Example: inconsistency from updating url

In the case that the website url update is not properly applied to every relevant row, a risk exists that the website url for ABC Publishing could become inconsistent across rows. Here the publisher site is different for the same publisher when comparing rows 23 and 74. Storing the url in a single record is preferable because only one record would require an update.

6. Example: adding publisher without textbook

Adding to this table is also problematic. The high school administration is interested in carrying textbooks from a publisher, New Horizons, with which the school does not have a prior business relationship. While no particular textbook of this publisher is being carried yet, the administration would like to start developing a relationship with this publisher.

7. Example: adding publisher without textbook

However, the publisher cannot be stored in the textbook table with the other publishers because entry of a publisher requires specification of a textbook title.

8. Example: removing a textbook

Removing a textbook from this table also has the potential to cause data anomalies. The school's statistics teacher no longer wants to use Statistical Concepts, published by Martin House. However, this is the only textbook in the table with Martin House as the publisher.

9. Example: removing a textbook

If this textbook is removed from the table, so is any record of Martin House as a publisher which is not the desired outcome. The fundamental problem is that publisher information should not be stored in the textbook table. Such a set up duplicates information. Coupling these objects causes anomalies in certain data insertion and deletion scenarios. Conversion to 2nd Normal Form presents a remedy to these problems.

10. Satisfying 2nd Normal Form (2NF)

A database is in 2nd Normal Form when it satisfies 1st Normal Form and non-key columns only depend on the table's PRIMARY KEY.

11. Example: textbooks and publishers in 2NF

Ultimately, we still want to maintain a link between textbooks and publishers but we should do this outside of the textbook table. This can be done by, first, moving all publisher data to its own table in the database.

12. Example: textbooks and publishers in 2NF

By separating these objects, publishers can be added without also adding a textbook. Removing textbooks from the textbook table will not cause a publisher to be completely removed as an effect of this action. However, this separation of publishers and textbooks has also removed the connection between the objects.

13. Example: textbooks and publishers in 2NF

We can restore the connection by creating a FOREIGN KEY in the textbook table which references a record in the publisher table. Satisfaction of 2nd Normal Form often results in the creation of new tables. But the benefit is a reduction of data duplication which is one of our goals for performing normalization.

14. Let's practice!

Let's practice utilizing our knowledge of 2nd Normal Form.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.