According to the standard design method, considering the whole process of database and its application system development, the database design is divided into the following 6 stages ：
1. Requirements analysis (Usually from the top down)
The first step in database design is to accurately understand and analyze user needs (including data and processing). Requirements analysis is the foundation of the entire design process, and it is also the most difficult and time-consuming step. Whether the requirements analysis is done adequately and accurately determines the speed and quality of building a database building on it. Poor requirements analysis will result in the entire database design being reworked and redone .
The task of demand analysis is to fully understand the work overview of the original system through a detailed investigation of the objects to be dealt with in the real world, make clear the various needs of users, and then determine the new system functions on this basis. The new system also has to fully consider the possible expansion and change in the future, not only to design according to the current application needs.
The survey focuses on data and processing. Meet information requirements, processing requirements, security and integrity requirements.
The Structured Analysis (SA) method is commonly used to analyze the system from the top to the bottom.
2. Conceptual structure design (Usually bottom-up)
The conceptual structure design is the key of the whole database design. It forms a conceptual model independent of the specific DBMS by synthesizing, summarizing and abstracting the user needs. There are generally four types of approaches to designing conceptual structures:
- Top down. That is to define the framework of the global conceptual structure first, and then gradually refine.
- Bottom up. That is to define the concept structure of each local application first, and then integrate them to get the global concept structure.
- Gradually expand. First define the most important core conceptual structure and then expand outward, snowballing other conceptual structures to the overall conceptual structure.
- Mix it up. The combination of top-down and bottom-up.
3. Logical structure design (E-R chart)
Logical structure design is to transform the conceptual structure into a data model supported by a DBMS and to be optimized.
At this stage, the e-R diagram becomes extremely important. You have to learn the properties defined by each entity to draw the overall E-R diagram.
There are three main types of conflicts among e-R graphs: attribute conflict, naming conflict, and structure conflict.
In the transformation of E-R graph to relational model, the problem to be solved is how to transform the entity and the relation between entities into relational model, and how to determine the attributes and codes of these relational model.
4. Physical structure design
Physical design is to select a physical structure (including storage structure and access method) that is most suitable for the application environment for the logical data structure model.
First of all, the transaction running should be analyzed in detail to obtain the parameters needed to choose the physical database design. Secondly, the internal characteristics of the RDBMS used should be fully understood, especially the access method and storage structure provided by the system.
There are three commonly used access methods: 1. Index method, currently mainly B+ tree index method. 2. Clusterin method. 3. The HASH method.
5. Database implementation
In the implementation stage of database, designers operate the database language provided by DBMS (such as SQL) and its host language, establish database according to the results of logical design and physical design, compile and debug application programs, organize data into the database, and conduct trial operation.
6. Database operation and maintenance
After trial operation, database application system can be put into formal operation. In the process of database system operation, it must be constantly evaluated, adjusted and modified.
