Relational Database Design
Table of Content:
Goal of designing a database
The goal of designing a database is to produce an efficient, high-quality and minimum cost database. The systematic process of designing a database is known as design methodology. Database design involves understanding the operational and business needs of an organization, modelling the specified requirements and realizing the requirements using a database. The overall database design and implementation process consists of several phases.
Database design and implementation process
Requirement collection and analysis: It is the crucial process of knowing and analyzing the expectations of the users for the new database application done by a team of analysts or requirement experts. They review the current file processing system or DBMS system and interact with the users to analyse the nature of the business area to be supported. The requirement specification techniques such as object-oriented analysis (OOA), data flow diagrams (DFDs), etc. are used to transform the initial requirements into a better structured form.
Conceptual database design: In this phase, the database designer selects a suitable data model and translates the data requirements resulting from the previous phase into a conceptual database schema by applying the concepts of the chosen data model. The entity-relationship (E-R) diagram is generally used to represent the conceptual database design.
Choice of a DBMS: The choice of a DBMS depends on many factors such as cost, DBMS features and tools, underlying model, portability and DBMS hardware requirements. The technical factors that affect the choice of a DBMS are the type of DBMS, storage structures and access paths that DBMS supports, the interfaces available, the types of high-level query languages and the architecture it supports.
Logical database design: Once an appropriate DBMS is chosen, the next step is to map the highlevel conceptual schema onto the implementation data model of the selected DBMS. In this phase, the database designer moves from an abstract data model to the implementation of the database.
Physical database design: In this phase, the physical features such as storage structures, file organization and access paths for the database files are specified to achieve good performance. The various options for file organization and access paths include various types of indexing, clustering of records, hashing techniques, etc.
Database system implementation: Once the logical and physical database designs are completed, the database system can be implemented. DDL statements of the selected DBMS are used and compiled to create the database schema and database files, and finally the database is loaded with the data.
Testing and evaluation: In this phase, the database is tested and fine-tuned for the performance, integrity, concurrent access and security constraints. This phase is carried out in parallel with application programming. If the testing fails, various actions are taken such as modification of physical design, modification of logical design or upgrading or changing DBMS software or hardware.