Second Normal Form (2NF) Normalization Exercise

database-designnormalization

I'm using a past exam paper for practice, can someone help me normalise this database to 2NF? I have uploaded a picture of the original below, and also my attempt at normalising it myself. I am certain I have gone wrong with the YEAR column, but can't be sure.

Best Answer

You have the right idea and have just gone a little wrong with the year column.

Background

The table given shows that Company Id + Year is the key to the table - meaning that each row must have a unique combination of the value of these two columns. This is indicated by the solid line under these two columns. A table is normalized when that table is a faithful representation of a relation. To approximate a relation, the table must:

Have unique unordered rows
Have uniquely labeled unordered columns
Have a single value of whatever domain the column is defined upon in cell

In looking at the table in the exercise these conditions are met. Once a table is normalized, it can be further normalized as a way to eliminate certain redundancies which occur due to functional dependencies between the columns. By functional dependency we simply mean that the value of one column always determines the value of the second. This is based on the mathematics of algebraic functions. For example, the value 2, when plugged into the algebraic function 2x + 3 will always yield 7. 2NF is defined to mean that each non-key column in the table is fully dependent on the entire key - thus no partial key functional dependencies.

The Exercise

When inspecting the example table we can easily see Company Nameis dependent only on Company Id and not on Year. Each time for example we see a value of 295 for Company Id we see a value of Miracle Holidays. The repair is exactly as you have done - split Company Id and Company Name out into their own table where there can be a single unique row for each Company Id with the corresponding Company Name. Now Company Name is fully dependent on the key - Company Id. Year simply stays in the original table and there is no need to redundantly split it out into a third table.

A Caution

The exercise is asking you to infer the functional dependency of Company Name on Company Id based solely on inspection of the data. While in this simple example it is obvious, in the real world you cannot simply assume a functional dependency exists based on the existing data. What appears to be a functional dependency might turn out to instead be coincidental. Because of this, in the real world you would always ask the expert in the domain of knowledge the table represents what the functional dependency should be.

Getting More Information

Normalization is a very complex topic and I have glossed over many important concepts. CJ Date has written an entire book with respect to it called Database Design and Relational Theory: Normal Forms and all that Jazz. While definitive, it is hard to grasp all the formalisms. An excellent reference that presents the formalisms in language more easily understood by common practitioners is Fabian Pascal's Practical Database Foundation Series. Studying both of these references - first Fabian's and then Date's - will give you all the information you need to master normalization as a repair procedure.

Related Solutions

How to handle “many columns” in OLAP RDBMS

Regarding why you would want to do this, imagine you want to see which words/short phrases in customer emails are associated with costly repairs, and you want to be able to analyze this using OLAP. It can be costly to tokenize/grammify many documents, so you might want to store the tokens/grams in a form which your OLAP server understands, ie columns.

Consider MonetDB, which allows for a practically unlimited number of columns.

Redshift maxes out at 1600 columns.

Another option would be to use Principal Component Analysis and only choose the top 1600 components, but that can make interpretation difficut.

Another option would be to use Postgres and store the tokenized strings or n-grams in a string array field, but your OLAP server would need to support that.

How to convert a table into second normal form

1NF: Only the key
    Remove repeated data/data groups 
    i.e. having a person and address table instead of single mega-table
2NF: The whole key
    Ensure that all nonkey columns are dependent on the whole key
    So with a table where employee name and location could be a primary key, 
    and there is a skill attribute the skill attribute isn’t dependent 
    on the whole of the key as 
    it isn’t dependent on location therefore must be split into two tables
3NF And nothing but the key
    Should not have any dependencies on things other than the primary key

Now this seems like a bit of a homework question so I won't do it for you but make a module table, make a room table. If you want third normal form making a booking table with module code and room number as a compound primary key, and the fields relevant to a booking.

Remember normalisation is to make all the data related and remove repetition.