To design an effective database system, a step by step procedure is required. In this procedure, different tasks are categorized to design database system. User can move forward to next phase after successfully completion of present phase and also move backward to previous phase to review it again and make necessary modifications if required. It makes a Data base system design cycle. The Database system design cycle shown in Figure 14.1 has Seven phases as discussed below.
1. Phase 1—Definition of the Problem
The first step in database system design cycle is problem identification. Following processes are involved in phase 1.
- Determine the need : DBA draws a rough outline of the project by understanding the actual need in database designing, as database design requirements are different for different organizations.
- Flexibility : This process is used to find the flexibility of rough outline of project like how much it can be enhanced in future etc.
- Cost estimation : A rough overall project cost is estimated to determine the expences incur that includes, purchasing of software, hardware, development cost, conversion of existing system to proposed system and training the personnel.
- Risks and benefits : All the risks and benefits are compared to determine overall benefit of using the proposed database system design.
- Feasibility analysis : The aim of feasibility analysis is to find out the best possible In this process various alternatives are considered and evaluate them to find out the best alternative or candidate. After selecting the best candidate by DBA and other organizational personel, the design of the database system begins.
2. Phase 2—Analyses of Existing System and Procedure
The second phase of cycle is very important in which designer study the existing system and procedures and then make a feasibility study of proposed solution in previous step. Processes involved in this phase are given below :
- Study of existing system and procedures : This process involves evaluating the current system by taking consideration of its capabilities, deficiencies, methods used for recording and processing data, area of improvements, basic requirements to migrate existing database system to new proposed system and current communication channels.
- Personal interviews : Personal interviews are the most fruitful procedure to determine data and processing Database analyzer invites one or more key users from each group individually and then determine their requirements to determine the data and processing needs for the whole group. A formal interview may be supplemented by a questionnaire.
- Analysis of the procedures and information flow : The requirements and other information gathered by different groups are analyzed together to analyze consistency and problem A detailed study of system is required to identify duplications, inefficiencies and limitations in the proposed design. This helps in determine the need of changes in procedures and its effect on the existing database system.
Analysis can be done by using some design tools like data flow diagrams. Data flow diagrams graphically represents the flow of data within organization. It represents data requirements and interconnection of various procedures. Designer use existing procedural documentation and information gathered from personal interviews to design these diagrams.
After designing the data flow diagrams of procedures, the proposed system is compared with the requirements that the system has to fulfill. This comparison helps in finding the need of any modification.
- Modification to proposed system : Modification required in current procedures to fulfill any particular requirement, improve efficiency, newly discovered requirement and remove limitations etc., are documented. Such modifications have to be discussed with the group of concerned persons.
- Preparing the initial proposal and requirement specifications : After doing all modifications to the proposed system, the initial proposal and requirement specifications are prepared and discussed with the group of concerned persons for further improvement.
Throughout this phase, the major factors considered in collecting and analyzing the requirements are:
- Types of activities to be performed.
- Levels of management to be supported.
- Nature of functions to be served.
The requirement specification can be categorized into two groups:
- Information requirements : It specify the types of entities, their attributes and relationship among various relations that are used to store data within database.
- Processing requirements : It specify the data–manipulation capabilities of the system by considering usage frequency and required turn around etc.
Each process is listed with information and processing requirements. These requirements should be consistent and semantically correct. After gathering requirements, Integrity Constraints are defined.
The output of this phase is as follows:
- Data and its processing requirements
- Relationship between various processes
- Constraints
- Properties of data
- Operations required to improve efficiency.
3. Phase 3—Preliminary Design
The third step in database system design cycle is Preliminary Design. It involves the following processes:
- Conceptual schema design : In designing, the conceptual schema, any one of the following two approaches can be used :
- Centralized schema design : In centralized schema design, the different requirement specifications of different groups of users are merged together to form a single set of After merging the specifications, the conceptual schema is designed from this single set. In case of any conflict during designing of schema, DBA examine each requirement specifications individually. After successful designing of conceptual schema, the views of the user groups are defined.
- View-integration approach : In this approach, the requirement specification of each user group is used to design their These views are integrated into the conceptual schema for the database. Conflicts may occur during this mapping but very easy to resolve. Conflicts that cannot be resolved by a conceptual schema to view mapping have to be examined. Example of these conflicts like one application uses unique employee number and second application named it employee ID or different applications use different data types for same attribute etc.
After resolving all the conflicts, the views are appropriately integrated to make conceptual schema. It is a step by step process, first two almost similar views are taken and merged, followed by merging of additional views one by one. There are two major approaches of integration :
- Top-down approach : In top-down approach, we start integration of entities, their attributes, and their Decision to split entities into number of specialized entities and add new relationships among them can also be taken.
- Bottom-up approach : In bottom-up approach, we start integration of a set of attributes into entities and relationships among The entities can be generalized and relationship is located at the higher levels.
Benefits of conceptual schema : Conceptual schema is DBMS independent and allows better understanding of information requirements and their inter-relationships. It can be easily understood by non specialist and can be easily documented.
- Design of applications and transactions : Various applications and transactions are designed by analyzing processing The response requirements of applications and transactions are also determined.
- Determine performance requirements : To determine performance requirements the designer need to study each operation in applications and transactions. Performance requirements plays a significant role in designing physical file structure, indexes for tables and physical devices etc.
The cycle of the steps consisting of definition of problem, existing system/procedure analysis, and preliminary design is repeated until a satisfactory design is obtained.
4. Phase 4—Computing System Requirements
In this phase, the designer need to choose appropriate DBMS software and the hardware equipments required. The choice of DBMS depends upon the following factors :
- Cost : The actual cost to design a new DBMS or to purchase a commercial DBMS and then customize Upgradation of existing DBMS, capital cost, initial training cost, operating cost including those for personnal, and maintenance of the hardware and software is also determined.
- Features provided by DBMS : Some important features that should be considered are providing support to distribution of database, communication facilities, form based user interface, report generation facilities, reusability factor and flexibility etc.
- Existing DBMS : Factors based on existing DBMS are type of existing DBMS (hierarchical, network, relational ), free storage space, conversion cost in case of upgradation of DBMS to different type and feasibility with new proposed DBMS etc.
- Actual requirements : A major factor is the actual requirement of user that should be fulfilled by DBMS.
- Availability : Factors based on availability are availability of services from the vendor, experience of the personal, features available in DBMS, and hardware and software availability to support DBMS etc.
- Flexibility of DBMS : Now days DBMS must be able to run on different hardware’s and operating systems.
By examining all the above factors with the budget of the organisation, the designer can determine the DBMS software and hardware requirement of an organisation.
5. Phase 5—Final Design
In this phase of database design, designer take the preliminary design developed in Phase 3 which is in database independent form and convert the conceptual scheme into choosed DBMS specific conceptual scheme. After this conversion, views of applications are derived from it. These views are external views. The schemes are generated as programs in the DDL of the target DBMS. Following are the processes involved in final design phase:
- Conversion of conceptual and schemes in the model of database : As discussed earlier, that E–R model is used to design conceptual schemes in Phase The designer, converts that scheme into proposed DBMS model. The conversion rule depends upon the type of selected DBMS model (Relational DBMS, Network DBMS, Hierarchical DBMS). After conversion of schemes, the designer starts work on designing the physical database.
- Designing of physical database : Following are the decisions that need to be taken by the designer during designing of physical database:
- Decision on flle organization : The choice of file organization directly affects the performance of The choice of file organization depends upon size of records, frequency of data manipulations and updation, distribution of storage location and size of organisation etc.
- Decision on supporting indexes : Indexes play a significant role in manipulation and updation of DBMS uses indexes to retrive any data. If data in database is properly indexed then response time is very small and vice versa. Indexes can be modified during database tuning and performance optimization. In database, a number of indexes are created for each record.
- Decision on clustering of records : Decisions should be taken regarding the partitioning of records into vertical, horizontal, or mixed Vertical fragmentation is appropriate if some of the record’s fields are accessed more frequently than others and horizontal fragmentation is appropriate if some occurrences of a record are more frequently used than others.
After taking decisions on all the factors, physical database is designed and its performance is determined and compared with expected value. If the performance is not near to the expected value, then physical database is modified again.
6. Phase 6—Implementation and Testing
In this phase of database design, final design is implemented and tested. Following are the processes involved in this phase:
- Implementation : Implementation process can be categorized into following steps :
- Conversion of functional specification into technical specification.
- Writing codes using technical specifications, DDL of DBMS and compiling the code.
- Developing application programs using high level languages.
- Creating of physical database.
- Loading test data into database.
- Testing : After development, system should undergo different test phases to verify its functionality, to remove bugs and to check consistency It includes
- Unit testing.
- Integrated testing
- Documentation : Documentation of success and failure of events are necessary to identify the actual Procedure for backing up and restarting after failure of various types are outlined. Documentation is also necessary for future enhancements. After removing all the bugs, the database system is ready to install at client site.
7. Phase 7—Operation and Tuning
This is the final phase of database design cycle in which design is completed and ready for day-to-day operation. Following are the processes involved in this phase:
- Installation : The database system is installed at the actual site and is ready for use.
- Onsite testing : Database system is again tested at actual site and the bugs have been After all necessary modification, its performance is measured.
- Performance tuning : If the measured performance is not upto mark then performance tuning is It may be done at hardware level by increasing the number of equipments, changing slow hardware etc., or at software level by increasing the number of buffers, size of buffers, defining additional indexes, modifying existing indexes, and clustering of records etc.
- Training : The users have been trained to use new applications throughout this phase.
Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)