Refining The Database Schema

Till now we've seen various models, and ways of creating a database. we've primly discussed about  the evolution of the ER Model, and the different types of keys that are seen in a relation. Today we'll see one of the most important step in the construction of a database. If we buy something, don't we check before using it? if we don't i don't say its bad, but the chances of bad quality are not null anyways. Hence in every aspect we check and try to purify things for refining and optimizing its efficiency.

In the same trend, once we create a relation for a given problematic data, its not directly implemented. Its first checked for some hidden errors that may cause the database to have run time disorders. First of all, we'll have a close look at the errors that might cause the following limitations of database:

• Delayed Processing of Requests
• Errors in responses
• Sometimes database may fail to result in the right response.

Redundancy is the prime of all the errors that occurs in a database, its simply the occurrence of  duplicates of a data. This may result in many further disorders in the relation.

There are three main errors that creep into a database, due to bad design of relation. they are:

• Insert
• Delete
• Update

These errors are called Anomalies, in the database terminology.

Insert Anomaly occurs, when data cannot be inserted without the presence of other information.

Delete Anomaly occurs, when deletion of a data causes loss of some other information.

Update Anomaly occurs, when updating a data needs to be done again and again.

The method of refining a relation by removing the redundancies that creep into the database, is called Normalization. Here we refine and if needed trim the relation, so that the relation in turn becomes simple and with no anomalies.

A Relation when satisfies a rule of Normalization is said to be called a Normal Form.

There are basically 5 Normal Forms, each having a rule to be satisfied to be called so:

• 1 NF
• 2 NF
• 3 NF
• BCNF
• 4 NF

1st Normal Form: The Relation should have a single value in each cell, multiple values should be removed or made into new rows.

2nd Normal Form: The Relation should be made free of Functional Dependency, there by causing the anomalies to be flushed off.

3rd Normal Form: The Relation should be freed of Partial and Transitive Dependencies.

3.5th Normal Form or Boyce- Codd Normal Form (BCNF): 

A Relation is in BCNF iff an FD X->A is such that: 

• A belongs to X and 

• X is a Super Key

4th Normal Form: The relation should not contain multi valued dependencies.

We'll have a close look at each of the Normal Forms in the next Posts....