"Hi, I'm SuperNorm, here to guide you through the Normalisation Process"

Relaxation Corner | Site Map | FAQ's

Home  >  Theory  >  Database Concepts  >  Database Types  >  Normalisation Concepts  >  1NF
Click here to Search this Site

| Home| Database Concepts| Database Types| Normalisation Concepts| 1 NF| 3 NF| 4-7 NF|

Click her for Theory Pages

Click here for Exercise Pages

Click here for Useful Web Links

Click here for useful Downloads

Discussion Room - Click here to ask a question

Click here to contact us

Click here for the Glossary

Second Normal Form (2NF)

Aim: remove non-key attributes that only depend on part of the key, to form a new table.

2NF Rule 1:   All data must be in 1NF.

2NF Rule 2a: Every non-key attribute must be fully dependent on the (entire) primary key.

2NF Rule 2b: Remove non-key attributes that only depend on part of the key, to form a new table entity, the key of
                   which will be a copy of the part of the master table key on which the attributes were dependant.

What checks do 2NF Rules require to be done to the 1NF data model below?
As the data is already in First Normal Form, then the data model conforms with Rule 1. So now consider Rule 2.

Is every non-key attribute fully dependant on the whole key? This requires a check of every attribute with it's key.
A definition of Dependent: "two attributes are only dependent; given one you uniquely know the other." 
(J. Jones, UCLAN)

Given REGNO we can uniquely identify: the CAR MAKE, the CAR MODEL, the CAR UNIT PRICE, the CAR MILEAGE etc.
Given REGNO/RENTALNO we can uniquely identify: the RENTAL DATES and all the RENTAL MILEAGES.
So the CAR & RENTAL tables are already in Second Normal Form: here is some payback for the hard work done on 1NF!


Normalised Table Image


So what about the SERVICING table? Does it conform to Rules 2a & 2b?

Given the GARAGE NAME we can uniquely identify the GARAGE ADDRESS without the REGNO or SERVICE DATE keys.
So, as Rule 2b states that, 'we should remove all non-key attributes that only depend on part of the key', then GARAGE
ADDRESS should be removed and used to form a new table, taking a copy of the key on which it is dependent, with it.

So does this rule also apply to SERVICE MILEAGE & SERVICE COST? To understand why these attributes are dependent on the entire key (Rule 2a), then consider these questions about the key & attribute dependencies.
Given the SERVICE DATE key can Norm find the SERVICE COST attribute? The answer is Yes, but what use is servicing information without knowing which car it applies to or which garage carried out the servicing: therefore the REGNO key is needed to make the SERVICE MILEAGE data accurate. Similarly the GARAGE NAME key is needed to make the SERVICE COST data complete - Norm would sensibly include the GARAGE NAME when storing the SERVICE COST on his database, after paying the garage invoice. So SERVICE MILEAGE & COST are fully dependent on the entire 3 part SERVICING key. 

So to complete the 2NF of the SERVICING table; then a new table GARAGE needs to be created.

Normalised table Image


Note: that the relationship between SERVICING & GARAGE is M:1 - Many services are carried out by 1 garage.
Now it is time to apply Rules 2a & 2b. As PARTS has only one non-key attribute PART DESCRIPTION, then as with the SERVICING table, this attribute is only dependent on 1 element (PART CODE) of the 4 part compound key.
Hence, this PART DESCRIPTION attribute and it's key PART CODE should be removed and a new PART table created.
The finalised 2NF version of the data model now looks like this.

Normalisaed Table Image

Note: this singular PART (as opposed to the plural PARTS) table is a deliberate choice to signify that PART is the 'one' part of the M:1 relationship with PARTS. Also look carefully at PARTS, it now only contains keys, there are no non-key attributes: this is called a linking entity. 
Again, as with the last part of 1NF, the Normalisation 'rules of thumb' should be applied.
- Is a copy of the Master table Primary key present in every Detail table?
- Do all linked tables have at least one attribute in common?
- Do all 'long' attributes occur only once in the one part of a 1:M relationship?
- Is the entity with the most keys the many part of a 1:M relationship?
- Are all relationships 1:M?

As the answer to all the above questions is Yes, then we shall now proceed to Third Normal Form. 

<<<Back to First Normal Form

Next to Third Normal Form >>>