Full Process of Database Design

Database technology is the most effective means of information resource management. Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, and effectively storing data to meet user information requirements and processing requirements.
In the demand analysis phase of database design, the application requirements of various users are integrated (real-world requirements ), in the conceptual design stage, a conceptual model (information world model) independent of the machine characteristics and various DBMS products is formed, which is described by E-R diagram. In the logic design stage, the E-R diagram is converted into the data model supported by the specific database product, such as the relational model, to form the database logic model. Then, based on the user's processing requirements and security considerations, the necessary view is created on the basis of the basic table to form an external data mode. In the physical design phase, you can arrange physical storage based on the characteristics and processing needs of the DBMS, design indexes, and form the internal database mode.
1. Demand analysis stage
Requirement collection and analysis: the data requirement described by the data dictionary is obtained (and the processing requirement described by the data flow diagram ).
Requirement Analysis focuses on investigating, collecting, and analyzing users' information requirements, processing requirements, and security and integrity requirements in data management.
Requirement Analysis Methods: Investigate the organization and organization, investigate the business activities of each department, assist users in clarifying various requirements for the new system, and determine the boundaries of the new system.
Common survey methods include: follow-up operations, opening survey meetings, inviting special personnel to introduce, ask, and designing surveys. Users are requested to fill in and check records.
The methods for analyzing and expressing user requirements mainly include top-down and bottom-up methods. The top-down structured analysis method (SA) starts with the upper-level system organization and analyzes the system layer by layer, the data flow diagram and data dictionary are used to describe each layer.
The data flow chart shows the relationship between the data and the processing process. The data in the system is described using the data dictionary (dd.
A data dictionary is a collection of various data descriptions. It describes the data in a database, that is, metadata, rather than the data itself. A data dictionary consists of data items, data structures, data streams, data storage, and processing processes (at least the Data Type of each field and the Primary and Foreign keys in each table should be included ).
Data item description = {data item name, description, alias, data type, length,
Value range, value meaning, and logical relationship with other data items}
Data Structure Description = {Data Structure name, description, composition: {data item or data structure }}
Data Stream description = {data stream name, description, data stream source, data stream destination,
Composition: {data structure}, average traffic, peak traffic}
Data storage description = {data storage name, description, number, inbound data stream, outbound data stream,
Composition: {data structure}, data volume, access method}
Process description = {process name, description, input: {data stream}, output: {data stream },
Handling: {brief description }}
2. Conceptual Structure Design Stage
A conceptual model independent of a specific DBMS is formed through the synthesis, induction and abstraction of user requirements, which can be expressed by a E-R diagram.
The conceptual model is used for modeling the information world. The conceptual model does not depend on the data model supported by a DBMS. A conceptual model can be converted to a specific data model supported by a DBMS on a computer.
Concept model features:
(1) Strong semantic expression ability, which can easily and directly express various semantic knowledge in applications.
(2) It should be simple, clear, and easy to understand. It is a language for communication between users and database designers. [Page]
A common method for conceptual model design is the idef1x method, which applies the entity-contact method to a Semantic Modeling Technology in the semantic data model and is used to establish a system information model.
The steps to create a E-R model using the idef1x method are as follows:
2.1 Step 1 -- initialize the project
In this phase, the task starts from the description of the purpose and scope, determines the modeling target, develops the modeling plan, organizes the modeling team, collects the source material, and develops constraints and specifications. Collecting source materials is the focus of this phase. Through investigation and observation results, business processes, input and output of the original system, various reports, and collection of raw data, a basic data table is formed.
2.2 Step 1 -- Define an object
Entity integrators have a common set of features and attributes that can identify most entities directly or indirectly from the collected source material-basic data tables. Mark the entities represented by the terms in the source material name table that indicate things and terms that end with "code", such as customer code, agent code, and product code, in this way, potential entities are preliminarily identified to form a preliminary entity table.
2.3 Step 2 -- Define contact
Only binary connections are allowed in the idef1x model. N-element connections must be defined as N binary connections. Based on actual business requirements and rules, the entity contact matrix is used to identify the binary relationship between entities. Then, the potential, Link name, and description of the connection relationship are determined based on the actual situation, and the link type is determined, whether it is an identity link, a non-identity Link (mandatory or optional), a non-deterministic link, or a classification link. If each instance of the Child body needs to be identified by the relationship with the parent object, the relationship is identified, otherwise it is not identified. In a non-identity relationship, if the instance of each child entity is associated with only one parent entity, it is mandatory; otherwise, it is not mandatory. If the parent entity and the Child entity represent the same real object, they are classification relationships.
Step 3-definition code
Remove the non-deterministic relationship generated in the previous stage by introducing the cross entity, and then identify the Hou code attribute from the non-cross entity and the independent entity to uniquely identify the instance of each entity, then confirm the master code from the candidate code. To determine the validity of the master code and link, the non-empty rules and non-multi-value rules are used to ensure that an attribute of an object instance cannot be null, you cannot have more than one value at the same time. Identify the identified relationship by mistake, break down the entity, and finally construct the key base view of the idef1x model (kb chart ).
2.5 Step 4 -- Define attributes
Extract descriptive terms from the source data table to develop an Attribute Table and determine the attribute owner. Defines non-primary Code attributes and checks non-null and non-multi-value rules of attributes. In addition, check fully dependent function rules and non-pass dependency rules to ensure that a non-primary code attribute must depend on the primary code, the entire primary code, and only the primary code. In this way, the full attribute view of the improved idef1x model, which is at least in line with the third paradigm of the relational theory, is obtained.
Step 5: Define other objects and rules
Defines the data type, length, precision, non-null, default value, and constraint rules of an attribute. Defines trigger, stored procedure, view, role, synonym, sequence, and other object information.
3. Logical Structure Design Stage
Converts a conceptual structure to a data model (such as a relational model) supported by a DBMS and optimizes it. To design the logical structure, select the data model that best suits to describe and express the corresponding conceptual structure, and then select the most appropriate DBMS.
To convert a E-R diagram to a relational model is to convert the relationship between the object, the object attributes, and the object into a relational model. This conversion generally follows the following principles:
1) A real shape is converted into a relational model. An object attribute is a link attribute. The entity code is the link code.

2) One M: N contact is converted into a relational mode. The codes of the entities connected to the contact and the attributes of the contact are converted to the Link Attributes. The link code is a combination of Entity Codes. [Page]
3) A 1: n link can be converted into an independent link mode, or merged with the link mode corresponding to n. If it is converted to an independent link mode, the codes of the entities connected to the contact and the attributes of the contact are converted to the link attributes, the link code is the entity code of N.
4) A link can be converted into an independent link mode, or merged with the link mode corresponding to any end.
5) One multivariate link between three or more entities is converted into a relational model. The codes and attributes of each object connected to the multivariate link are converted to the link attributes. The link code is a combination of Entity Codes.
6) links between entities in the same entity set, that is, self-links, can also be handled in three situations:, 1: N, and M: N.
7) The link mode with the same code can be merged.
In order to further improve the performance of the database application system, the data model structure should be modified and adjusted appropriately under the guidance of standardization theory. This is the optimization of the data model. Determine the data dependency. Eliminate redundant connections. Determine which relationship modes belong to the nth paradigm. Determine whether to merge or break them down. In general, the relationship is divided into 3nf criteria, namely:
Each value in the table can be expressed only once.
Each row in the table should be uniquely identified (with a unique key ).
The table should not store non-key information that depends on other keys.
4. Physical database design phase
Select the physical structure (including the storage structure and access method) that best suits the application environment for the Logical Data Model ). Arranges physical storage and designs indexes based on DBMS features and processing needs to form the internal database mode.
5. database implementation stage
Use the Data Language (such as SQL) provided by DBMS and its host language (such as C) to establish a database based on the logic design and physical design results, compile and debug applications, and organize the data warehouse receiving, and run the test. Database implementation mainly includes the following tasks: defining the database structure with DDL, organizing data warehouse receiving, compiling and debugging applications, and database trial run
6. Database Operation and Maintenance
The database application system can be put into operation after trial run. During the operation of the database system, it must be constantly evaluated, adjusted, and modified. Including: Database dumping and recovery, database security, integrity control, database performance monitoring, analysis and improvement, database re-organization and re-construction.
Use of modeling tools
To speed up database design, there are currently many database auxiliary tools (CASE tools), such as Rational Rose, Erwin and bpwin of CA, sybase powerdesigner and oracle designer.
Erwin is mainly used to establish conceptual and physical models of databases. It can describe the attributes of objects, links, and objects in a graphical manner. Erwin supports the idef1x method. By using the Erwin modeling tool to automatically generate, change, and analyze the idef1x model, we can not only obtain excellent business functions and data requirement models, but also transform the idef1x model to the physical design of the database. The model drawn by Erwin tool corresponds to two types: Logical Model and physical model. In the logical model, the idef1x toolbox can be used to easily create and draw object links and attributes in a graphical manner. In the physical model, Erwin can define the corresponding tables and columns and automatically convert them to appropriate types for various database management systems.
Designers can select appropriate database design modeling tools as needed. For example, after the requirement analysis is complete, the designer can use Erwin to draw an erdiagram, convert the erdiagram into a relational data model, generate a database structure, draw a data flow diagram, and generate an application.