|
|
| 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!
|
|
|
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.
|
|
|
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. |
|
|
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 >>>
|
|
