In this chapter, you will learn about the methodology for the database design stage of the database system development lifecycle for relational databases. The methodology is depicted as a bit by bit guide to the three main phases of database design, namely: conceptual, logical, and physical design.
The primary aim of each phase is as follows:
- Conceptual database design - to build the conceptual representation of the database, which has the identification of the important entities, relationships, and attributes.
- Logical database design - to convert the conceptual representation to the logical structure of the database, which includes designing the relations.
- Physical database design - to make a decision how the logical structure is to be physically implemented (as base relations) in the target Database Management System (DBMS).
Introduction to the Database Design Methodology
A structured approach which uses procedures, techniques, tools, and documentation help to support and make possible the process of design is called Design Methodology.
A design methodology encapsulates various phases each containing some stages, which guide the designer in the techniques suitable at each stage of the project. A design methodology also helps the designer to plan, manage, control, and evaluate database development and managing projects. Furthermore, it is a planned approach for analyzing and modeling a group of requirements for a database in a standardized and ordered manner.
Conceptual Database Design
In this design methodology, the process of constructing a model of the data is used in an enterprise, independent of all physical considerations. The conceptual database design phase starts with the formation of a conceptual data model of the enterprise that is entirely independent of implementation details such as the target DBMS, use of application programs, programming languages used, hardware platform, performance issues or any other physical deliberations.
Critical Success Factors in Database Design
The following planning strategies are often critical to the success of database design:
- Deal with task interactively with the users as much as possible.
- Follow a prearranged methodology throughout the data modeling process.
- Make use of a data-driven approach.
- Incorporate structural and integrity considerations into the data models.
- Combine conceptualization, normalization, and transaction validation methods into the data modeling methodology.
- Use figures for representing as much of the data models as possible.
- Use a Database Design Language (DBDL) to represent additional data semantics that cannot normally be represented in a diagram.
- Build a data dictionary to add-on the data model diagrams and the DBDL.
- Be willing to repeat steps.
These factors are constructed into the methodology that is presented for database design.
What are the steps for Conceptual Database Design?
Conceptual database design steps are:
- Build a conceptual data model
- Recognize entity types
- Recognize the relationship types
- Identify and connect attributes with entity or relationship types
- Determine attribute domains
- Determine candidate, primary, and alternate key attributes
- Consider use of improved modeling concepts (optional step)
- Check model for redundancy
- Validate the conceptual model against user transactions
- Review the conceptual data model with user
Building a Conceptual Data Model
The first step in conceptual database design is to build one (or more) conceptual data replica of the data requirements of the enterprise. A conceptual data model comprises these following elements:
- entity types
- types of relationship
- attributes and the various attribute domains
- primary keys and alternate keys
- integrity constraints
The conceptual data model is maintained by documentation, including ER diagrams and a data dictionary, which is produced throughout the development of the model.