Steps in Database Design Process

sponsored links

Friends, we have already discussed introduction and few more important topics of Data Base Management System. Today we shall discuss about the steps involved in the Database Design Process. The Data Base Design Process can be divided into six steps. The E-R Model is most relevant to the first three steps. 

Note : To make the  concepts clear we shall try to understand the steps using a small example.  Assume that you  want to build a home. Now read the below steps by keeping this example in mind.

1. Requirements Analysis : Before building the house, we have to analyze our requirements. How many bedrooms, how many bathrooms, how many kitchens and what will be the area etc... In simple words, we analyze our requirements. This is the very first step of designing process. In this we have to analyze that what data to be stored in the database, what applications must be built on top of it and what operations are most frequent and subject to performance requirements. In this stage we must find out what the users want from the database. 
2. Conceptual Database Design : After analyzing our requirements, we will come to a basic idea of the building according to our requirements. That means drawing a rough sketch. In this step the information gathered in the requirements analysis step is used to develop a high-level description of the data to be stored in the database, along with the constraints that are known to hold over this data. This step is often carried out using E-R model or a similar high-level data model.
3. Logical Database Design : Now based on the above design we make a building plan. Simply, In our above step we focused on what to do. Here in logical database design we focus on how to do. We must choose a DBMS to implement our database design and convert the conceptual database design into a database schema in the data model of the chosen DBMS. Task here is to convert E-R Schema into relational database schema.
4. Schema Refinement : To check whether our plan is perfect or not. Refinement means to remove unwanted elements. In this step we analyze the collections of relations in our relational database schema to identify potential problems and to refine it. In contrast to the requirements analysis and conceptual design steps which are essentially subjective, schema refinement can be guided by some elegant and powerful theory.
5. Physical Database Design : Based on the plan we start construing the house. So we should focus on arranging the required cement, clay, sand, iron and wood etc. and we construct the house according the the capacity of the basement. In this step we must consider typical expected workloads that our database must support and further refine the database design to ensure that it meets desired performance criteria. This step may simply involve building indexes on some tables and clustering some tables, or it may involve a substantial redesign of parts of the database schema obtained from the earlier design steps. 
6. Security Design : Our building is almost completed. Now we don't allow everybody into our home. There are some restrictions. If the person is a stranger then we talk to him outside the gate. If he is a friend, we let him come inside the home and make him sit in the hall and talk, if he a relative we will allow him to stay with us... In this step, we identify different user groups and different roles played by various users (e.g., the development team for a product, the customer support representatives, the product manager). For each role and user group, we must identify the parts of the database that they must be able to access and the parts of the database that they should not be allowed to access and take steps to ensure that they can access only the necessary parts.

sponsored links