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.