Database integrity in the database application system

Database Integrity (db Integrity) refers to the correctness and compatibility of data in a database. Database integrity is guaranteed by a variety of integrity constraints, so it can be said that database integrity design is the design of database integrity constraints. Database integrity constraints can be implemented through a DBMS or application, and the integrity constraints of the DBMS are stored as part of the schema. The database integrity implemented by DBMS is designed according to the database design steps, and the database integrity implemented by the application software is integrated into the application software design (the former is mainly discussed in this paper). Database integrity for database application system is very important, its role is mainly reflected in the following aspects:

1. Database integrity constraints can prevent legitimate users from adding inappropriate data to the database when they use the database.

2. The use of DBMS based integrity control mechanism to implement business rules, easy to define, easy to understand, and can reduce the complexity of the application, improve the efficiency of the application. At the same time, the integrity control mechanism based on DBMS is centrally managed, so it is easier to realize the integrity of the database than the application.

3. Reasonable database integrity design, can take into account the integrity of the database and the effectiveness of the system. For example, when loading a large amount of data, as long as the database integrity constraints of the DBMS is temporarily invalidated before loading, then it will be effective, can guarantee the efficiency of data loading and the integrity of the database.

4. In the functional testing of the application software, perfect database integrity can help to discover the errors of application software as early as possible.

Database integrity constraints can be divided into 6 categories: column-level static constraints, tuple-level static constraints, relational-level static constraints, column-level dynamic constraints, dynamic constraints at the tuple level, dynamic constraints at the relationship level. Dynamic constraints are usually implemented by the application software. Database integrity supported by different DBMS is essentially the same, and Oracle-supported DBMS-based integrity constraints are shown in the following table:

Database Integrity Design Example

A good database integrity design first needs to identify the business rules to be implemented through database integrity constraints in the requirements analysis phase, and then, based on fully understanding the integrity control mechanism provided by the specific DBMS, according to the architecture and performance requirements of the system, follow the database design method and the application software design method, Reasonable choice of the implementation of each business rule, and finally, carefully test, eliminate implied constraint conflicts and performance problems. Database integrity design Based on DBMS is generally divided into the following stages:

1. Requirements Analysis Phase

Through the joint efforts of system analyst, database analyst and user, determine the objects that should be included in the system model, such as department, employee, manager of personnel and payroll management system, and various business rules.

After completing the search for business rules, determine the business rules to be used as database integrity and categorize the business rules. The integrity design that is part of the database schema is performed in the following procedure. and the database integrity design implemented by the application software will be carried out according to the software engineering method.

2. Conceptual structure Design phase

The conceptual structure design phase is to transform the results of the requirements analysis into a conceptual model that is independent of the specific DBMS, that is, the entity diagram (ERD). In the conceptual structure design phase, the real phase of database integrity design should begin, because the entity relationship of this phase will be transformed into entity integrity constraint and referential integrity constraint in the logical structure design stage, and the main work of design will be completed in the logical structure design phase.

3. Logical Structure design phase

This phase transforms the conceptual structure into a data model supported by a DBMS and optimizes it, including normalization of relational models. At this time, according to the integrity constraint mechanism provided by DBMS, the integrity constraint list in the logical structure has not been added, and the appropriate method is chosen to realize it.

At the end of the logical structure design phase, the integrity design that is part of the database schema is basically completed. Each business rule can be implemented in several ways, and you should choose the one that has the least impact on the performance of the database, sometimes through actual testing.

Database Integrity Design Principles

When implementing database integrity design, there are some basic principles to be grasped:

1. According to the type of database integrity constraint, the system level and mode of implementation are determined, and the effect on system performance is considered in advance. In general, static constraints should be included in the database schema as much as possible, while dynamic constraints are implemented by the application.

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

3. To be cautious with the current mainstream DBMS are supported by the trigger function, on the one hand, because of the performance of the trigger triggers a large, on the other hand, the trigger is not a multi-level trigger control, prone to errors, when not used, it is best to use before-type statement-level triggers.

4. In the requirement analysis stage, we must make the naming specification of the integrity constraint, and make use of the combination of meaningful English words, abbreviations, table names, column names and underscores so that they can be easily identified and remembered, such as: Ckc_emp_real_income_employee, Pk_employee, Ckt_ EMPLOYEE. If the case tool is used, there is usually a default rule that can be modified on this basis.

5. The database integrity is tested in accordance with business rules to eliminate conflicts between implicit integrity constraints and performance effects as early as possible.

6. A dedicated database design team is responsible for the analysis, design, testing, implementation and early maintenance of the database. Database designers are not only responsible for database integrity constraints based on DBMS design implementation, but also responsible for application software implementation of database integrity constraints audit.

7. Appropriate case tools should be used to reduce the workload in each phase of database design. A good case tool can support the lifecycle of the entire database, which will make the database designer work more efficiently and communicate with the user easily.