Database Normalization Slide Deck

Just uploaded the slideshow these posts will be based on. It is from an exercise I use in my college database classes. See link below...

Database Normalization on SlideShare

--Dan

1st normal form violation

The table violates first normal form's rule that all cell values must be unique. See below.

You cannot have two values in one cell. This would introduce numerous problems. You would not be able to do joins on this column. Nor would grouping on the column work correctly. Sorting would not work and so on.

To resolve this issue you need to update the table to correct this entry. below is the corrected table.

This table now has no issues that violate first normal form but it WILL violate second. We will discuss that in our next post.

---Dan D'Urso

I am going to put up a short series of posts on normalization. It is based on an exercise I found in a textbook, long since forgotten. But it is an interesting and clear example nonetheless. The goal will be to take an Excel spreadsheet and convert it to 3rd normal form. Later I may extend it to Boyce-Codd, which I consider a variation on 3rd normal, and 4th normal.

When designing a database from scratch one would, of course, aim for a properly designed database free from anomalies. Nevertheless, it is very helpful to understand the basics of normalization so that problems can be avoided. Plus, in today's environment much data exists in Excel spreadsheets. These must be normalized as they are imported into a relational database.

Business case

Tracking of large industrial equipment such as boilers, feed heaters, etc. in chemical plants in the South. Each piece of equipment has a composite primary key consisting of the plant name and equipment type. In other words something like styrene feed heater.

Normal forms

There are five normal forms, three of which we will consider in this series. They are cumulative in the sense that a table that satisfies 2nd normal form also satisfies 1st and so on. I will cover 1st in this post. 2nd and 3rd in future posts.

1st normal form

To be in 1st normal form a table must meet the following conditions:

All rows are unique

All cells are atomic

There are no repeating groups

The order of rows and columns does not matter

Original spreadsheet

Below is the original spreadsheet. Plant name and eqpt name form a composite identifier for each row. This spreadsheet contains a violation of the 1st normal form rules.

I will cover what the problem is in my next post. A succeeding post will then introduce 2nd normal form.

Regards,

Dan D'Urso

Laguna Niguel, CA

We recently created a short course in using Visio 2016 to create ERD's. Visio 2016 does not have a lot of traditional database design tool capabilities such as forward engineering to create scripts but it is simple and rather elegant. And it is already widely installed. Below is the slide deck for the class.

VIS201d Visio Database Diagramming from Dan D'Urso