Explain the Database Designing Steps (Or) What are the main steps of Database Designing?

The database design is considered to be the most important task while following database approach for a reality. Therefore, Database design is the Process of constructing a stable DB structures from the User requirement Analysis.

The database design structures the grouping of Fields/Attributes into different files and establishes meaningful associations between different Files in an Optimal manner, which helps to minimize the response time while accessing and manipulating Databse during its use.

Thus, one must be analyze the user requirements, identify the basic needs and find the correct means of logical representation to accomplish the Conceptual Datamodel.

STEPS OF DATABASE DESIGN:

The database designing steps can be categorized into

1.       Requirement Analysis

2.      Design of Conceptual Data Model

3.      Design of Internal Data Model or Implementation Design and

4.      Design of Physical Data Model.

Requirement Analysis:

The first step of the DB design is to perform requirement Analysis. The requirements of the users of the proposed system are to be identified either through interviews with the members concerned or through reports that are presently used by the users.  Thus, the format of each and every report is to be collected along with related processing requirements such as Periodicity and nature of each report etc.

From each report, you can obtain

      ·        Name of the report

      ·         List of Data items from the report to be stored in the Database and

      ·         Each Data element in each of the report

      ·         Associations between different data items

      ·         Name of Data Item, its Data Type (Char, Numeric, Date, Logical etc), its width

      ·         Update authority

      ·         Allowable range (if data item is Numeric) and

      ·         Source of Data item etc.

Design of Conceptual Data Model: 

It is a Logical Data model. It is the process of designing a DBMS independent Data Model, which gives a stable comprehensive View of the entire Database. The basic input for this step is, the set of relations of different reports from the user views.

The Conceptual Data Model is, thus, an independent of a specific model of the 3 types of Data models, such as, Hierarchical Data Model, Network Data Model and Relational Data Model. It gives a Conceptual foundation of the organization of different files and related associations between them. Finally, the conceptual Data model is mapped into any ONE of the 3 above data models in reality, then the mapped Data Model becomes DBMS Dependent.

The steps of designing a Conceptual DM can be summarized as,

  §  Input the requirement specifications in the form of relations

  §  Apply Normalization on each and every relation up to a specified level of Normalized Form

  §  Perform View Integration

  §  Construct a Stable Conceptual Data Model

  §  Design LAM (Logical Access Map) for each user view.

NORMALIZATION is the process of Functional dependency analysis which helps to split the relations of each user view into stable sub groups of Data items. Each sub groups of fields is called a File.

VIEW INTEGRATION is the process of merging the sub groups of data items of different user views, which are having the same key field(s).

CONSTRUCTION OF A CONCEPTUAL DM, means,  organizing the final set of files obtained through the view integration into a meaningful schema with relevant associations between different files in it.

LOGICAL ACCESS MAP (LAM)  is a kind of flow chart defined over the Conceptual DM to access and manipulate it to print a report/ user view.

Therefore, before mapping the Conceptual DM into any DBMS dependent DM , One has to do a careful analysis by taking the following factors into consideration.

1.       The best of the 3 DM’s should be able to completely define the realistic problem on hand.

2.      Due to financial constraints, if it is not possible to procure the related S/w tool to access the best fitting DM or, if there is no expert available with in the company or, there is difficulty in recruiting persons with the required skills/ expertise, then one can choose the best fitting Data Model.

Design of Internal Data Model:   After designing the Conceptual DM, (which is independent to the Database environment),  it should be implemented/ Mapped into any one of the following Data Models.

                                                                                I.            Hierarchical Data Model

                                                                              II.            Network Data Model and

                                                                            III.            Relational Data Model

Selecting the right type of DM, to suit the reality is an important task, since it will improve the efficiency of operations of the system. The implementation design is an important step of the database design , which decides the selection of the best fitting DM and maps the conceptual DM into the selected DM.

Steps of Implementation Design:

1.  Input the following

                        a)  DBMS Independent Schema/ Conceptual DM

                        b)  Data Volume and Usage Details

                        c)  Characteristics of the selected DBMS Datamodel

            2.  Map the Conceptual DM into the desired Logical DM

            3.  Design sub-schemas of different users of the system

            4.  Prepare Program design guidelines for each sub schema

            5.  Perform review design and carry out corrections, if any

Design of Physical Data Model:  After designing the Internal Data Model, the next step is to design a Physical Data Model.  It is concerned with deciding the following factors.

     a)      Stored record format

     b)      Selecting Accessing methods

     c)      Database Security

     d)      Integrity

     e)      Back up procedures

     f)       Recovery Methods etc.

    Thus the design of physical data model decides the data storage, its location and accessing methods with required security measures.