Design of Relational Database

The goal of relational data base design to generate set of relations schemas that allows as to store information with unnecessary redundancy. The one approach design schemas of that are in an appropriate normal form.

Pit-fall in Relational Database system:

                The relational database design among the undesirable properties that a bad designed may have all.

     1. Repetation of information.

     2. Inability to represent certain information.

Example :

    The problem used in modified database design for over bank lending relation.

Branch name	Branch city	Asset	Customer Name	Loan number	amount
SBI	Salem	10000	Kumar	L-101	2000
IOB	Trichy	20000	Sampath	L-102	5000
UCO	Salem	15000	Mani	L-103	6000

The relation schema= {Branch Name, Branch City, Asset, Customer Name, Loan Number, Amount}

In this above relation lending schema has following meaning :

t[Asset]->Assets for the Branch Name

t[Branch Name], t[Branch City]->These are the city in which branch name is located.

t[Loan number]->It is the number assign to a loan made by a branch name.

t[Customer Name], t[Amount]->It is the amount of loan whose number is t[Loan Number]. Suppose to add a new loan to our database the loan is[IOB, Trichy, 20000, Sampath, Murali, L-102, 5000].

                In the above lending relation the assets and city data for a branch much appear once for each loan mode by a branch. The repetition  information required by use of our alternative design is undesirable.

                The repetition information wasted spaces it complicate to update the database.

Decomposition:

                The above lending relation of a bad design suggest that we should decompose a relation schema that many attribute in the several schema. consider the alternative design in which lending schema is decompose  into two schema.

Branch-Customer Schema={Branch Name, Branch City, Assets, Customer Name}

Customer-loan Schema={Customer Name, Loan Number, Loan Number, Customer Name}

                In the above schema to represent database information about which customer or borrowers. Because of these lose of lending schema into branch customer schema and customer loan schema losses decomposition. It should create from our example that a losses decomposition is a bad database design.

                There is one attribute common between branch customer schema and customer loan schema,

Branch Customer Schema ⋂ Customer Loan schema = {Customer Name}

                The above lost less joining is central to match relation database design a set of relation schema r1,r2,r3,……rn is decomposition of R if R=R1υR2…Rn.