How can I make database normalization more clear?

Question

Database normalization is needlessly clouded by over-explanation. I reviewed a wide array of web articles and several textbooks before starting to teach it. After I became thoroughly familiar with the point of it in my own terms and then understood the rules, I reduced the rules down to this list:

• 1NF eliminate repeating values and columns.
• Assign a Primary Key.
• 2NF all attributes depend on all parts of a Composite PK.
• 3NF each attribute depends only on the PK.

In lecture, I literally scribble this on the whiteboard then work through examples. I assign a couple problems from a textbook and ask them to explain every step that they use, in a simple text document. Some get it fairly well, which is good enough for me. Others seem to get no traction at all and hand in a long, wandering story which repeats the statement of the rules in various ways, but not effectively.

Besides stating the rules this tersely, walking through examples and pointing to the vital sentences in the textbook, are there other ways to convey this kind of material? (This could be similar to other technical topics which have a simple 'core' but are abstract and hard to learn.)

Answer 1

One thing that hasn't been mentioned (enough) in the earlier answers is the reason that one wants to do normalization in the first place. In many parts of computing redundancy is good. Java gives explicit types to variables and then checks that the assigned values have those types. That is actually redundant, as the Python language shows. However, the benefit of static typing is that certain kinds of errors in programs can be caught earlier (in the compiler or intelligent editor) rather than later (runtime).

However, in storing data, redundancy is generally bad. The problem is that if you store the same information in two places, independently, they can get inconsistent when one is updated and the other not. As a simple example, suppose you store someone's age somewhere, but also store their birthday somewhere else, and they aren't dependent in any way on each other. Can lead to a bit of trouble.

Database Normalization is a way to reduce redundancy. It is especially needed in Relational Databases since the database doesn't actually store the semantics of the data (other than weakly via field names). The semantics there is the semantics of relations (rows, columns, project, join...), not the semantics of, say, "Employee Payment and Taxation" (Name, id, address...).

There are several levels of normalization, each reducing a certain kind of redundancy. The field is complex since some of the higher levels are inconsistent and are inconsistent with other "measures of goodness" of storage protocol.

Therefore, I suggest that in teaching normalization you start with an example that exhibits redundancy problems and demonstrate how it leads to losing information (lossyness) through inconsistency. Then show how the, say, First Normal Form normalization makes it better, but maybe not yet perfect. Then apply 2NF, etc.

So, start concrete in this case and work toward the abstract.

Answer 2

There are some things you can only "get" by having repetition wear it into your brain.

This seems to me like a clear use case for first making them do it the hard way. So, construct a non-normalized schema and make them walk, painfully, through keeping everything in synch. Done right, they will be thoroughly sick of it after just a few updates.

Then, step by step introduce the normal forms, and look at how much easier updates are at each step.

Answer 3

I agree that simplified rules such as this, in concept, can help students grasp the main concepts! Regarding these particular simplifications, I think for the last one you meant BCNF instead of 3NF. 3NF allows an attribute to not depend on the PK, but rather on some other element, if that attribute is part of some other candidate key. The distinction between 3NF and BCNF is a pretty important one.

I find that motivating and explanation 3NF with my students is quite hard. BCNF is much easier.

---

• PK = Primary Key
• BCNF = Boyce-Codd Normal Form

---

Here's how I roughly explain the roles of the most-popular normal forms to my students:

1NF: It's a relational database

BCNF (Boyce-Codd Normal Form): what you intuitively want. Each attribute depends just on the primary key. The catch is that you can't always decompose a relation that's not in BCNF into multiple relations that are in BCNF, while retaining integrity of your data.

3NF: a compromise. It's not as good or as obvious as BCNF, but you can provably losslessly always decompose your data into 3NF. You can always get to 3NF, though it's kind of counter-intuitive as to what it is. You can't always get as far as BCNF, though that's the intuitive one.

2NF: I only mention it briefly and indicate that its importance or lack thereof is largely historical.

In other words, I pretty much put all of my attention on BCNF as a solid goal, and on 3NF as a compromise if you can't get there.

Answer 4

I think that the "problem first" approach applies here. Rules like normal form weren't handed down from on high; they were developed as logical solutions to actual existing problems.

One key principle from a favorite article on teaching:

Coax into action the student’s mind to derive and establish all data which can be derived or established from the axioms or theories.

In application of this, I wouldn't even teach someone the normal forms as the first step. I would mention that there are different ways of structuring your relations, and that there are rules to follow which lead to a logical structure, and that we would get to them shortly.

The basic axiom I would teach is that each piece of information should be stored in only one place, so that it can't go out of sync (become inconsistent).

Then I would get some student interaction going and get the students to give examples of pieces of information they would keep track of. This PDF was my first introduction to database design. Borrowing and expanding an example from that, I might ask students: what information would they would want to track for a database of books?

Title and author are obvious. Some others will be mentioned: Page count. Publication date. Publication format. Publisher. How about "language"? ISBN.

Author's date of birth and date of death may be mentioned.

Writing these out across a chalkboard and start considering the matter further with a few examples and the huge number of problems with having a single relation will all of these attributes will become apparent:

What happens when a book has more than one author collaborating? What happens when multiple books were written by the same author, and then the author dies (and you have to update "date of death" in multiple places)? What happens when the same book was published more than once, in different years, by different publishers, with different formats and page counts?

Of course you wouldn't throw all those problems at them at the same time. But gradually, little by little, you could decompose that giant "BOOKS" relation into a database with all originally mentioned attributes, but in proper normal form.

And if you're doing it really well, your students would do all the normalizing, even if they don't know the rules they're following.

After any particular student suggestion (like making a separate "authors" relation to hold such information as dates of birth and death), I could then acknowledge it, validate it, and then present the general rule.

---

In short, rather than dumping data and explanations on top of them, I would get them to look and derive and establish the data for themselves, so they could really use it. Only then might I add additional explanation.

Answer 5

It seems that the presentation of the material is done well. Given that the problems assigned from the textbook are in a sequence that increases with complexity, there isn't much you can change.

A possibility is to assign one problem at a time, going over the results of it before moving to the next one. That way, maybe, you can find out where the ones without traction are skidding out of control.

As you have said, they intuitively know how separating things makes sense already. If not included in the lecture, you could explain the trade-offs between doing the normalization (the design work), and storage space for the extra tables vs. the potential savings in coding and possible duplication of data. To emphasize that point you might have some examples of code (or pseudo code) to handle both a normalized and non-normalized version of the same dataset and the storage requirements for a sample dataset at each level of normalization. Take what they know "intuitively" and make it explicit.

Answer 6

Great question! I went through the same dilemma as you. After reading multiple perspectives on database normalization, I condensed my learning into the article at https://bkmjournal.wordpress.com/2010/07/29/database-normalization-as-i-remember-it/.

One suggestion I have for you is to make students "measure" the cost of a denormalized schema by executing update and delete statements on schemas that are not normalized. One example of how the cost can be measured is in measuring the number of SQL statements needed to execute one logical update (a full address repeated in every pending order of a customer).