程序代写代做代考 database Functional Dependencies Microsoft Word – Third_Normal_Form

Microsoft Word – Third_Normal_Form

Third Normal Form Page 1 of 3

© Claire Ellul

Third Normal Form

Normalisation

Normalisation is the process used to ensure that the logical design process
has produced high-quality structures for the tables within a database. By
quality, we mean structures that will perform optimally, and do not contain
any duplicate information.

If a logical structure is normalised, then each attribute in the structure will
have the same importance. This is useful during the physicalisation process,
when the database is actually created using SQL, because indexes can be
assigned to each attribute to make searching and retrieval on that attribute
faster.

Normalisation is a useful verification tool, which indicates amendments to
be made to the logical design process, but does not substitute for the
conceptual and logical design. All design stages should be undertaken to
ensure that the design covers all aspects of the required system.

The Normalisation Process

The process of normalising a logical model can be summarised as follows:
1. Convert all table structures to First Normal Form
2. Identify Functional Dependencies
3. Convert all table structures from First Normal Form to Second Normal

Form
4. Convert all table structures from Second Normal form to Third Normal

Form and (very rarely) into Boyce-Codd Normal Form

Problems with Second Normal Form

A relation in second normal form can still show data insert and update
anomalies, as shown by the ORDERS table below which is in Second Normal
Form (key fields in this case are Customer Name, Product ID and Date).

ORDERS
Customer Name Product ID Date Quantity %Discount

James Smith 23 12/12/2003 50 10

James Smith 24 15/12/2003 100 15

Martin Jones 23 2/11/2002 50 10

Alex Hayley 23 15/1/2003 150 20

Considering the operations we wish to do to the table:
1. Update – what happens if the quantity for the first sale to James

Smith changes? This means that the value in the %Discount field must
also be changed.

2. Insert – A value for %Discount cannot be inserted until a value for
quantity is known.

Third Normal Form Page 2 of 3

© Claire Ellul

For database tables Functional Dependencies describe how the value of
one attribute depends on the value of another – i.e. when you change one
attribute, you must also change another. They need to be eliminated
between NON-KEY fields.

Listing the functional dependencies for the above table:
 Quantity → %Discount
 Customer Name, Product ID, Date → Quantity
 Customer Name, Product ID, Date → %Discount

In other words:
 the value of %Discount is determined by the Quantity purchased, as

well as by the values of Customer Name, Product ID and Date.
 the value for Quantity is in turn dependant on the values of Customer

Name, Product ID and Date.

This situation can be described in general as follows:
 The value of a non-key attribute A must be changed when the value

of another non-key attribute B changes
 The value of B must be changed when the primary key changes

The situation is known as a transitive dependence.

Third Normal Form

Formally: A relation is in third normal form (3NF) if and only if it is in
Second Normal Form (2NF) and every non-key attribute is non-transitively
dependent on the primary key.

Therefore a table is in third normal form only if the NON-KEY attributes are:
 Mutually independent (one non-key field is not dependent on any

other non-key fields)
 Fully dependant on the primary key

A NON-KEY attribute is any attribute that does not participate in the
primary key of the table concerned. Attributes are mutually independent if
none of them is functionally dependant on any combination of the others.
Independence implies that any attribute can be updated separately to any
other, without affecting the value of any other.

The table above should therefore be decomposed (split into two or more
tables) to overcome the problems caused by Second Normal Form. This
decomposition will bring the resulting tables into Third Normal Form.

Customer Name Product ID Date Quantity
James Smith 23 12/12/2003 50

James Smith 24 15/12/2003 100

Third Normal Form Page 3 of 3

© Claire Ellul

Martin Jones 23 2/11/2002 50

Alex Hayley 23 15/1/2003 150

Quantity %Discount
50 10
100 15
150 20