Introduction Relational Database and Normalization

Introduction Relational Database and Normalization

Introduction Relational Database and Normalization : After designing the logical ER model the next important step is to design the relational database structure using normalization, which is based on functional dependencies between the attributes. This chapter deals with both functional dependencies (FD’s) and normalization.

To put in simple words normalization is a process of decomposing a relation, having certain functional dependencies among its attributes, into smaller simpler relations that are free from anomalies but preserves original FD’s and constraints. The decomposition is such that original set of records can always be brought back by the join of such smaller decomposed relations.

A good relational database design should be free from any kind of anomalies that are discussed in the next sections. If anomalies are not removed at early stages that they may cause heavy re-work/re-design in the later Stages. Anomalies are removed by decomposing a relation into smaller relations based on functional dependencies(FD’s).

When designing a database, you have to make decisions regarding how best to take some system in the real world and model it in a database. This consists of deciding which tables to create, what columns they will Contain, as well as the relationships between the tables. While it would be nice if this process was totally intuitive and obvious, or even better automated, this is simply not the case. A well-designed database takes time and effort to conceive, build and refine.

The benefits of a database that has been designed according to the relational model are numerous. Some of them are:

  • Data entry, updates and deletions will be efficient.
  • Data retrieval, summarization and reporting will also be efficient.
  • Since the database follows a well-formulated model, it behaves predictably.
  • Since much of the information is stored in the database rather than in the application, the database is somewhat self-documenting.
  • Changes to the database Schema are easy to make.

There are two approaches for relational database design:

  1. Decomposition Approach
  2. Synthesis Approach

The decomposition approach starts with one Universal relation and associated set of constraints in the form of functional dependencies. If the relation has any kind of anomalies it is decomposed into smaller relational based on normalization rules. This approach is most popular.

Initially a Relational Database is:

A Universal relation : R = {A1, A2,…, An}(A1, A2 = Attributes of universal relation).

Set of functional dependencies: F (a ->b, b – c, e – f etc.)

Relational Database Design and Normalization

Later:

Decompose R using F to : D = (R1, R2, …, Rn(Using Normalization)

D is a decomposition of R under F.

The synthesis approach starts with a set of functional dependencies on a set of attributes. It then Synthesize relations of the third normal form.