Database Design Methodology Overview (database solutions. Second Edition)

The following content is excerpted from database solutions. Second Edition

Key success factors in Database Design:

The following guidelines are important for successful Database Design:

· Communicate with users as much as possible

· Use a structured methodology throughout the database modeling process

· Use data-driven methods

'Add structural and integrity considerations to the Data Model

'Combine normalization and transaction validity into methods'

· Use graphs to represent data models as much as possible

· Use the database design language (dbdl)

'Build a data dictionary to supplement the data model Graph

'You are willing to repeat the above steps

 

Logical database design consists of the following two main steps:

Step 1: Create an ER Model and check whether the model has the minimum redundancy and supports user transactions. The output of this step is to create an ER model, which fully and accurately expresses the enterprise's requirements for data.

Step 2: map the ER model to the table set and check the structure of each table using normalization. Normalization ensures that the table structure is consistent and logical, with minimal redundancy. The table is also checked to ensure they support

The required transactions and the integrity constraints required by the database.

Details are as follows:

Step 1: Create and check the ER Model

1.1 Identify entity

1.2 identity relationship

1.3 identify the relevant attributes of an object or link

1.4 determine the attribute domain

1.5 determine candidate key, primary key, and backup key attributes

1.6 special/extensive entities (optional steps)

1.7 check whether the model supports user transactions

1.8 check the model with the user

 

Step 2: map the ER model to a table

2.1 create a table

2.2 Use the normalization method to check the table structure

2.3 check whether user transactions are supported

2.4 check Business Rules

2.5 discuss logical database design with users

 

Physical database design includes six main steps

Step 3: Use the functions of the target DBMS to design basic tables and integrity constraints

Step 4: select the file organization mode and index for the basic table. Generally, DBMS provides a certain number of available file organization methods for data.

Step 5: design the user view determined during the requirement analysis and collection phases of the database system development lifecycle

Step 6: design security measures to prevent unauthorized users from accessing data

Step 7: consider relaxing the canonicalized constraints on the table to improve the performance of the entire system. This step only needs to be performed as needed, because some problems may occur when redundancy is introduced, and the consistency still needs to be maintained.

Step 8: constantly monitor and adjust the operating system to identify and solve performance problems caused by the design, and achieve new or changed requirements.

Database design is an iterative process that requires continuous optimization from the beginning.