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 decide 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 that 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 usually 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 the 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.
Found This Useful? Share This Page!
As a database design expert with a deep understanding of the intricacies involved in the development lifecycle of relational databases, I'd like to provide insights into the concepts covered in the article you shared.
The methodology described in the article outlines a systematic approach to the three main phases of database design: conceptual, logical, and physical design. Let's delve into each of these phases and related concepts:
-
Conceptual Database Design:
- Objective: The primary aim is to build a conceptual representation of the database, identifying important entities, relationships, and attributes.
- Process: This phase involves constructing a model of the data in an enterprise, independent of physical considerations such as the target DBMS, application programs, hardware, or performance issues.
- Key Steps:
- Build a conceptual data model.
- Recognize entity types.
- Recognize relationship types.
- Identify and connect attributes with entity or relationship types.
- Determine attribute domains.
- Determine candidate, primary, and alternate key attributes.
- Consider the use of improved modeling concepts (optional step).
- Check model for redundancy.
- Validate the conceptual model against user transactions.
- Review the conceptual data model with users.
-
Logical Database Design:
- Objective: Convert the conceptual representation to the logical structure of the database, including designing relations.
- Process: This phase focuses on defining the logical relationships and structures of the database based on the conceptual model.
-
Physical Database Design:
- Objective: Decide how the logical structure is to be physically implemented in the target Database Management System (DBMS).
- Process: This involves making decisions on storage structures, indexing, and other physical aspects to optimize performance.
-
Design Methodology:
- Definition: A structured approach using procedures, techniques, tools, and documentation to support the design process.
- Role: It guides designers through various phases and stages of the project, helping plan, manage, control, and evaluate database development projects.
-
Critical Success Factors:
- Planning Strategies: Strategies critical to the success of database design, including interactive user engagement, following a methodology, data-driven approach, incorporation of structural and integrity considerations, and more.
-
Conceptual Data Model:
- Elements: It includes entity types, relationship types, attributes, attribute domains, primary and alternate keys, and integrity constraints.
- Documentation: Maintained through ER diagrams and a data dictionary.
The article emphasizes the importance of a well-structured design methodology, adherence to planning strategies, and the incorporation of critical success factors to ensure effective and successful database design.
