How to design a database Part 1

Designing a database process can divide into six steps. The following sections will describe each step so that you would gain a basic knowledge on database designing.

Gathering data

Before designing a database we should know the user requirements correctly in order to design an effective database. As a first step, we should collect data which is relevant to the respective organization. Then analyze the data in order to identify correctly the requirements of the user. To identify the requirements correctly we need to have a good idea about the other sections in the information system that interacts with the database. Therefore, at the requirement gathering stage the following points should be remembered.

• Must have a correct idea about the major application areas and user groups that will use the database or whose work will be affected by the database.
• Existing documentations that concerns the application should be closely looked into e.g. Documentations such as policy manuals, forms,reports. Organization charts, etc.
• The current operation should be analyzed correctly in order to identify different types of transactions and their frequency. Flow of information within the system is another main aspect which has to be analyzed.
• Written response to sets of questions are sometimes collected from the potential database users and user groups.

This step is normally carried out by a group of experts for final users or customers who will use the information system. The initial requirement is mostly informal, incomplete, inconsistent and partially incorrect. Therefore we have to transfer these initial requirements into specifications that can be used
by the developers as the starting point for writing the implementation and test cases.

Derive an abstract view of the database

In this step two consecutive activities are carried out simultaneously. They are developing an abstract plan of the database and transaction and application design.

Abstract plan of the database

This stage will create a plan which will show how each item in the database interacts with the other, and the different constraints that lie between users. For example, to create a student information system database for an academic institute, this step will identify the kind of constraints that face students, the different courses available, teachers, marks and the different subjects in a course. It will also focus on how each user interacts with others.

Transaction Design

This will design different transactions that will occur within the database, and it will identify inputs and outputs and functional behavior which are used to specify the transactions at a conceptual level. After identifying the transactions it can be grouped into three categories, such as retrieval transactions (transactions which relate to retrieve data from the database), update transactions (transactions which update data within the database) and mixed transactions (transaction which involve both retrieval of some of the data and update some of the information in the database).

Logical Database Design

This step creates an abstract and external plan of the database model by mapping the output of the previous step. The result of this phase is Data Definition Language (DDL) statements in the language of the chosen DBMS that specifies the abstract and external plan of the database system.