Database integrity design

Database Integrity refers to the correctness and compatibility of data in the Database. Database integrity is critical to the database application system and is reflected in many aspects. [Keyword] SQLServer Database Integrity refers to the correctness and compatibility of data in the Database. Database integrity is guaranteed by various integrity constraints. Therefore, database integrity design is the design of database integrity constraints. Database integrity constraints can be implemented through DBMS or applications. DBMS-based integrity constraints are saved into the database as part of the pattern. The database integrity implemented by DBMS is designed according to the database design steps, while the database integrity implemented by the application software is included in the application software design (this article mainly discusses the former ). Database integrity is critical to the database application system. Its role is mainly reflected in the following aspects:

1. database integrity constraints prevent legal users from adding non-semantic data to the database when using the database.

2. the DBMS-based integrity control mechanism is used to implement business rules, which is easy to define and understand. In addition, the complexity of applications can be reduced and the running efficiency of applications can be improved. At the same time, the DBMS-based integrity control mechanism is centrally managed, so it is easier to achieve database integrity than applications.

3. A reasonable database integrity design can take into account both the database integrity and system efficiency. For example, when loading a large amount of data, you only need to temporarily invalidate the DBMS-based database integrity constraints before loading and then make them take effect, this ensures that neither the efficiency of data loading nor the integrity of the database.

4. In the functional test of application software, perfect database integrity helps to Detect application software errors as soon as possible.

Database integrity constraints can be divided into six categories: column-level static constraints, meta-group-level static constraints, link-level static constraints, column-level dynamic constraints, meta-group-level dynamic constraints, and link-level dynamic constraints. Dynamic constraints are usually implemented by application software. The database integrity supported by different DBMS is basically the same. The following table lists the DBMS-based integrity constraints supported by Oracle:

Database integrity design example

A good database integrity design first needs to determine the business rules to be implemented through database integrity constraints in the requirement analysis phase, and then, on the basis of a full understanding of the Integrity Control Mechanism provided by a specific DBMS, follow the database design methods and application software design methods according to the system architecture and performance requirements, and reasonably select the implementation method of each business rule. Finally, perform the test carefully, eliminate implicit contract conflicts and performance issues. DBMS-based database integrity design is divided into the following stages:

I. Demand analysis stage

Through the joint efforts of system analysts, database analysts, and users, determine the objects to be included in the system model, such as departments, employees, managers, and various business rules in the personnel and wage management system.

After searching for business rules, determine the business rules to be used as database integrity and classify the business rules. The integrity design as part of the database mode is carried out in the following process. The database integrity design implemented by application software is carried out in accordance with the software engineering method.

Ii. Conceptual Structure Design Stage

In the conceptual structure design phase, the results of requirement analysis are converted into a conceptual model independent of a specific DBMS, that is, the Entity Relationship Diagram (ERD ). In the conceptual structure design phase, the substantive phase of Data Warehouse integrity design is required, because the entity relationships in this phase will be converted into Entity integrity constraints and reference integrity constraints in the logical structure design phase, the main work of the design will be completed at the logical structure design stage.

Iii. Logical Structure Design Stage

In this phase, the conceptual structure is converted to the data model supported by a DBMS and optimized, including the standardization of the relational model. In this case, based on the integrity constraints mechanism provided by DBMS, select an appropriate method one by one for the list of integrity constraints that have not yet been added to the logical structure.

At the end of the logical structure design phase, the integrity design as part of the database model is basically completed. There may be several implementation methods for each type of business rule. You should select one that has the least impact on the database performance, and sometimes it needs to be determined by actual tests.

Database integrity design principles

Some basic principles need to be grasped when implementing database integrity design:

1. Determine the implementation system level and method based on the type of database integrity constraints, and consider the impact on system performance in advance. Generally, static constraints should be included in the database mode as much as possible, while dynamic constraints are implemented by applications.

2. entity integrity constraints and reference integrity constraints are the most important integrity constraints of relational databases. They should be applied as far as possible without affecting critical system performance. It is worthwhile to exchange a certain amount of time and space for the ease of use of the system.

3. use the trigger functions supported by mainstream DBMS with caution. On the one hand, because of the high performance overhead of the trigger, on the other hand, the multi-level trigger of the trigger is difficult to control, and errors are prone to occur, it is best to use the Before statement-Level Trigger.

4. in the demand analysis stage, naming rules for integrity constraints must be formulated. Meaningful English words, acronyms, table names, column names, and underscores should be used whenever possible to make it easy to recognize and remember, such: CKC_EMP_REAL_INCOME_EMPLOYEE, PK_EMPLOYEE, and CKT_EMPLOYEE. If the CASE tool is used, there are generally default rules, which can be modified and used on this basis.

5. perform detailed tests on database integrity according to business rules to eliminate conflicts between implicit integrity constraints and impact on performance as soon as possible.

6. A full-time database design team should be in charge of database analysis, design, testing, implementation and early maintenance from start to end. Database designers are not only responsible for the design and implementation of database integrity constraints based on DBMS, but also for reviewing the database integrity constraints implemented by application software.

7. Appropriate CASE tools should be used to reduce the workload of each stage of database design. A good CASE tool can support the entire lifecycle of the database, which greatly improves the efficiency of the Database Designer and is easy to communicate with users.