Database Redesign

Oct 23 • Notes • 5255 Views • 2 Comments on Database Redesign

Data is the most important part and need of database. It is the fundamental component of an information system. Through database design, we can arrange the required data for the organisation and arrange them in an organised structure. The ER approach has become the standard of database system design due to its simplicity, easy understanding and widely use. Database redesign in a process through which we can able to adjust the database as the new requirements of the user, modifications in the database, new data structures, new ER diagrams which normally show the string similarity with the original diagram.

Introduction to Database Redesign

Introduction
Database redesign is a process through which we can redesign the whole database, means, we can adjust the database by the new requirements of the user. We can maintain the old database through the redesign process. We can create new data structures through this redesign process. As ER approach is the standard approach in database design process, we can also reconstruct the ER diagrams as per the new requirements by the user.

Need of Database Redesign
During the initial database design, there are some mistakes in the process. Database redesign is used to fix this problem. Database redesign is required to adapt the database to do some changes in system requirements. When the new system is installed in the database, then new information system is to be required to be redesign in the database. As the user needs some new requirements, then the systems should be changed by the users needs.

Correlated Sub Query
Correlated sub queries are normally used for database redesign process. A regular sub query can be processed independently from the bottom up approach in the database design process. A correlated sub query cannot be independently executed. For a related sub query, the data processing is nested. In this type of sub query, a row from an upper query statement is used in the comparison with the rows in a lower level query.

DBMS statement related to correlated sub query.
The database code for correlated sub query is
Select w1.title , w1.copy
From work w1
Where w1.title in
(select w2.title
From work w2
Where w1.title = w2.title
And w1.workID <> w2.workID);

Correlated sub query for checking functional dependency
The following correlated sub query can be used to check for any rows that violate the functional dependency. Department-> BudgetCode
The required correlated statement is given below.

Select e1.Department, e1.BudgetCode
From Employee e1
Where e1.Department IN
(Select e2.Department
From employee e2
Where e1.Departmet=e2.Department
And e1.BudgetCode<>e2.BudgetCode);

Sequence of transformation validation

The sequence of transformation is valid when all intermediate diagrams generated during the execution of the sequence are structurally correct. For example, the creation of a relationship between the entities that does not exist in the old ER diagram may not precede the creation of the entities that will be related.

Optimal Sequence

An optimal sequence is a good sequence that satisfies other database conversion performance criteria. The criteria includes minimal quantity of physical access to secondary memory, smallest amount of bytes moved from one data structure to another, etc.

The sequence
Create-r(p,{P,D}, create-r(a{S,P}, create-e(P, exclude-r(m,{S,D}, create-r(ad,{S,T},D))))
Is a valid sequence but is not a good one.

Exists and Non exists

Exists and Non exists are the specialized form of correlated sub query. An exists condition is true if any row in the sub query meets the specified conditions. A non exists condition is true only if all rows in the sub query do not meet the specified condition.

Principles for database redesign
There are normally three principles for database redesign.
(a) We have to understand the current structure which is to be redesign and contents of the database before making and structure changes.
(b) We have to test the newly changes on a test database before making the real changes in the database.
(c) Create a complete backup of the operational database before making any structure changes.

Reverse engineering.
Reverse engineering is the technique which is used by database redesign approach. Reverse engineering is the process of reading the data from database and producing a data model from a database schema. A reverse engineering data model provides a basis to begin the database redesign project. It is neither truly a conceptual nor a internal schema. The reverse engineering model should be carefully reviewed it almost has some missing information.

About the Author: Niroj Pahi

Niroj Kumar Pahi B-Tech Computer Science Engineering From Oxford College of Engineering and Management, Bhubaneswar, Odisha. Currently Working in Ruva Customer Services pvt. Ltd as IT promotional Consultant