Chapter 4.2 : Steps in Normalisation

• First normal form (1NF). Any repeating groups have been removed, so that there is a single value at the intersection of each row and column of the table.
• Second normal form (2NF). Any partial functional dependencies have been removed.
• Third normal form (3NF). Any transitive dependencies have been removed.

Note: If a relation meets the criteria for 3NF, it also meets criteria for 2NF and 1NF. Most design problems can be avoided if the relations are in 3NF.

4.2.1 First Normal Form
Example:

4.2.2 Second Normal Form

A relation is in 2NF if:

• It is in INF, and
• all non-key attributes are fully functionally dependent on the primary key and not on only a portion of the primary key.

Steps to transform into 2NF:

• Identify all functional dependencies in INF.
• Make each determinant the primary key of a new relation.
• Place all attributes that depend on a given determinant in the relation with that determinant that depend on a given determinant in the relation with that determinant as non-key attributes.

All the functional dependencies in this case are:
ORDER-NO –> DATE, CUST-NO, CUSTNAME, CUST-ADDRESS
PART-NO –> PART-DESC
Note : In this case, we say that PART-NO is only partially functional dependent on the key.
(ORDER-NO, PART-NO) – - > QTY-ORDERED, QUOTE-PRICE

The partial functional dependency in
ITEM (ORDER-NO, PART-NO, QTY-ORDERES, QUOTE-PRICE)
creates redundancy in that relation, which results in anomalies when the table is updated.

• Insertion anomaly. To insert a row for the ITEM table, we must provide the part description information too.
• Deletion anomaly. If we delete a row for the ITEM table, we may lose some PART information.
• Modification anomaly. If a PART’s description changes, we must record the change in multiple rows in the ITEM table.

Example:

Chapter 4.1 : Basic Concepts

Normalization is a process for converting complex data structures into simple, stable data structures.Why normalisation is necessary ?

• The database design must be efficient (performance-wise).
• The amount of data should be reduced if possible.
• The design should be free of update, insertion and deletion anomalies.
• The design must comply with rules regarding relational databases.
• The design has to show pertinent relationship between entities.
• The design should permit simple retrieval, simplify data maintenance and reduce the need to restructure data.

Figure 1 on the left show the steps in normalization.

4.1.1 Functional Dependency
Normalisation is based on the analysis of functional dependence. A functional dependency is a particular relationship between two attributes. For any relation R, that attribute B is functionally dependent on attribute A if, for every valid instance of A, that value of A uniquely determines then value of B. This is usually represented by an arrow, as follows:
A –> B
An attribute may be functionally dependent on two (or more) attributes, rather than on a single attribute. For example, in the following relation:
ORDER (ORDER-NO, PART-NO, NO-ORDERED, PART-DESC, QUOTED-PRICE)
ORDER-NO, PART-NO –> NO-ORDERED, PART-DESC, QUOTE-PRICE

In this case, the attribute on the left-hand side of the arrow is called a determinant.
For examples:
CUST-NO – - > CUST-NAME, ADDRESS, COMPANY
INVOICE-NO – - > INVOICE-DATE, CUST-NO, ORDER-NO
CUST-NO and INVOICE-NO examples of determinants.

4.1.2 Keys
An attitude (or field), K, is the primary key of a table if:

• All columns (all the fields in the table) are functionally dependent on K.
• Each value is unique.
• If K is a composite/concatenate key then it must comply with the following conditions:
  • No portion of the key should be a primary key.
  • All attributes that make up the key are not null.

Chapter 4 : Normalization

Chapter Objectives

At the end of the chapter, you should be able to:

• understand the purpose of normalization;
• perform first, second and third normalization;
• merging relations (view integration);
• transforming E-R diagrams to relations.

Essential Reading

• Modem Database Management (4th Edition), red R. Mcfadden & Jeffrey A. Hoffer (1994),
• Benjamin/Cummings.[Chapter 6, page 199 - 237]