Database Design

Database design is organizing data in certain format which enables the user to fetch or manipulate the data efficiently. It comprises a series of steps which involves analyzing, planning, developing, testing and the maintenance of the data. Physical and logical models are created to ensure that there is no redundancy or wastage of space and data accuracy. These two aspects plays a vital role as if there is any erroneous data is the database, any decision taken with regard to the data will also lead to misleading results. The process starts with analyzing the complete requirement from the user followed by designing the required tables with appropriate keys, triggers and sequences. The more effort and concentration on the design process will help in easy accessibility and avoid frustrations in the development phase. Tables has to be created with meaningful column names, comments, constraints , Log information and for repeating/dependent columns master tables with probable values has to be created and can be referenced wherever required which in turn preserves data integrity. This allows us to do alterations only in the master tables and all the other referenced tables will be updated automatically.

Normalization:

To reduce redundancy in the database, the concept of normalization has been introduced. Handling the same data in multiple fields increases the load as well as difficult to administrate the database. The concept of normalization has been introduced to reduce the redundancy in the database. The concept emphasize on breaking the tables to atomic level, where related data’s can be maintained in individual table and can be referenced wherever needed. For a table to be normalized, the following points has to be satisfied.

1. Any field in a table should not have more than one value. All the data should be single/atomic value.

2. There should not be any partial dependency, which means if a table has composite primary key from 2 columns, any other non-prime column should not depend on a part (one column) of primary key.

3. Transitional Dependency should not exist. Any non-prime column should not depend on other non-prime column. In this case, Dependent non-prime columns can form a separate table and primary key of that table can be referred here.

4. For all the dependencies, Prime attribute should refer to the Non-Prime Attribute and not vice versa.

ER Diagram:

ER Diagrams sketch the design of the database with the relationships of the entities and their attributes which shows the logical structure of the database. Entities are the objects such as table and attributes define the properties of the objects. These diagrams are mainly used for the documentation of the existing schema or designing a new database. Entities, relations and flow are created by geometric representations. Every entity should occur only once in the diagram and names for entity, relations and attribute should be defined. Colors can be used to highlight the important parts in the diagram. The redundant relations should be eliminated and relationships should be connected to each other.