Conceptual Design in Database Design Process
Conceptual design is the first stage in the database design process. The goal at this stage is to design a database that is independent of database software and physical details. The output of this process is a conceptual data model that describes the main data entities, attributes, relationships, and constraints of a given problem domain. This design is descriptive and narrative in form. Keep in mind the following minimal data rule:
"All that is needed is there, and all that is there is needed".
In other words, make sure that all data needed are in the model and that all data in the model are needed. All data elements required by the database transactions must be defined in the model, and all data elements defined in the model must be used by at least one database transaction. The conceptual design has four steps, which are as follows.
1. Data analysis and requirements
2. Entity relationship modeling and normalization
3. Data model verification
4. Distributed database design
1. Data Analysis and Requirements:
The first step in conceptual design is to discover the characteristics of the data elements. Appropriate data element characteristics are those that can be transformed into appropriate information. Therefore, the designer’s efforts are focused on:
• Information needs. What kind of information is needed—that is, what output (reports and queries) must be generated by the system, what information does the current system generate, and to what extent is that information adequate?
• Information users. Who will use the information? How is the information to be used? What are the various end-user data views?
• Information sources. Where is the information to be found? How is the information to be extracted once it is found?
• Information constitution. What data elements are needed to produce the information? What are the data attributes? What relationships exist among the data? What is the data volume? How frequently are the data used? What data transformations are to be used to generate the required information? The designer obtains the answers to those questions from a variety of sources in order to compile the necessary information. Note these sources:
• Developing and gathering end-user data views. The database designer and the end user(s) interact to jointly develop a precise description of end-user data views. In turn, the end-user data views will be used to help identify the database’s main data elements.
• Directly observing the current system: existing and desired output. The end user usually has an existing system in place, whether it’s manual or computer-based. The designer reviews the existing system to identify the data and their characteristics.
• Interfacing with the systems design group. The database design process is part of the Systems Development Life Cycle (SDLC). In some cases, the systems analyst in charge of designing the new system will also develop the conceptual database model.
2. Entity Relationship Modeling and Normalization:
Before creating the ER model, the designer must communicate and enforce appropriate standards to be used in the documentation of the design. The process of defining business rules and developing the conceptual model using ER diagrams can be described using the following steps.
1. Identify, analyze, and refine the business rules.
2. Identify the main entities, using the results of Step 1.
3. Define the relationships among the entities, using the results of Steps 1 and 2.
4. Define the attributes, primary keys, and foreign keys for each of the entities.
5. Normalize the entities. (Remember that entities are implemented as tables in an RDBMS.)
6. Complete the initial ER diagram.
7. Validate the ER model against the end users’ information and processing requirements.
8. Modify the ER model, using the results of Step 7.
3. Data Model Verification:
The data model verification step is one of the last steps in the conceptual design stage, and it is also one of the most critical ones. In this step, the ER model must be verified against the proposed system processes in order to corroborate that the intended processes can be supported by the database model. Verification requires that the model be run through a series of tests against:
• End-user data views.
• All required transactions: SELECT, INSERT, UPDATE, and DELETE operations.
• Access rights and security.
• Business-imposed data requirements and constraints.
4. Distributed Database Design:
Although not a requirement for most databases, sometimes a database may need to be distributed among multiple geographically disperse locations. Processes that access the database may also vary from one location to another. For example, a retail process and a warehouse storage process are likely to be found in different physical locations. If the database data and processes are to be distributed across the system, portions of a database, known as database fragments, may reside in several physical locations.
A database fragment is a subset of a database that is stored at a given location. The database fragment may be composed of a subset of rows or columns from one or multiple tables.
Database Development Life Cycle