The 1. database integrity constraint can prevent legitimate users from adding semantic data to the database when using it.
2. Using the integrity control mechanism based on DBMS to realize business rules is easy to define and understand, which can reduce the complexity of the application and improve the running efficiency of the application. At the same time, the integrity control mechanism based on DBMS is centralized management, so the database is easier to achieve integrity than the application.
3. Reasonable database integrity design can give consideration to both database integrity and system efficiency. For example, when loading a large amount of data, as long as the database integrity constraint based on DBMS is temporarily invalidated before loading and then takes effect, the efficiency of data loading can be guaranteed without affecting the database integrity.
4. In the functional test of application software, perfect database integrity helps to find the errors of application software as early as possible.
Database integrity constraints can be divided into six categories: column-level static constraints, tuple-level static constraints, relationship-level static constraints, column-level dynamic constraints, tuple-level dynamic constraints and relationship-level dynamic constraints. Dynamic constraints are usually implemented by application software. The database integrity supported by different DBMS is basically the same, and the DBMS-based integrity constraints supported by Oracle are shown in the following table:
Example of database integrity design
A good database integrity design first needs to determine the business rules to be implemented through database integrity constraints in the demand analysis stage, and then on the basis of fully understanding the integrity control mechanism provided by a specific DBMS, according to the architecture and performance requirements of the whole system, following the database design method and application software design method, reasonably select the implementation method of each business rule; Finally, test carefully to eliminate hidden constraint conflicts and performance problems. Database integrity design based on DBMS can be roughly divided into the following stages:
1. Requirements analysis stage
Through the joint efforts of system analysts, database analysts and users, the objects that should be included in the system model are determined, such as departments, employees, managers and various business rules in the personnel compensation management system.
After finding the business rules, determine the business rules to be used as database integrity and classify the business rules. Among them, the integrity design as a part of the database schema is carried out according to the following process. The database integrity design realized by application software will be carried out according to the method of software engineering.
2. Conceptual structure design stage
In the conceptual structure design stage, the results of requirements analysis are transformed into a conceptual model independent of specific DBMS, that is, Entity Relationship Diagram (ERD). In the conceptual structure design stage, the substantive stage of database integrity design will begin, because the entity relationship in this stage 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 stage.
3. Logical structure design stage
This stage is to transform the conceptual structure into a data model supported by DBMS and optimize it, including standardizing the relational model. At this time, according to the integrity constraint mechanism provided by DBMS, the integrity constraint list that has not been added to the logical structure is realized one by one by choosing an appropriate way.
At the end of the logical structure design stage, the integrity design as a part of the database schema is basically completed. There may be many ways to implement each business rule, and the way that has the least impact on database performance should be chosen, sometimes it needs to be decided through actual testing.
Principles of database integrity design
In the realization of database integrity design, we need to master some basic principles:
1. According to the type of database integrity constraint, determine the system level and mode of its implementation, and consider the impact on system performance in advance. Generally speaking, static constraints should be included in the database schema as much as possible, while dynamic constraints are implemented by applications.
2. entity integrity constraint and referential integrity constraint are the most important integrity constraints in relational databases, and they should be applied as far as possible without affecting the key performance of the system. It is worthwhile to exchange certain time and space for the availability of the system.
3. We should carefully use the trigger function supported by the mainstream database management system. On the one hand, the performance overhead of triggers is high; On the other hand, the multi-level triggering of flip-flops is difficult to control and prone to errors. If necessary, it is best to use Before statement-level triggers.
4. In the demand analysis stage, it is necessary to formulate naming norms for integrity constraints, and try to use meaningful English words, abbreviations, table names, column names and underscores to make them easy to identify and remember, such as CKC _ employee _ real _ income _ employee, PK _ employee, CKT _ employee, etc. If you use the CASE tool, there are generally default rules, which can be modified and used on this basis.
5. According to the business rules, the integrity of the database should be carefully tested in order to eliminate the conflict between implicit integrity constraints and the impact on performance as soon as possible.
6. There should be a full-time database design team responsible for the analysis, design, testing, implementation and early maintenance of the database from beginning to end. Database designers are not only responsible for the design and implementation of database integrity constraints based on DBMS, but also for the audit of database integrity constraints implemented by application software.
7. Appropriate CASE tools should be adopted to reduce the workload in each stage of database design. A good CASE tool can support the whole database life cycle, which will greatly improve the work efficiency of database designers and facilitate communication with users.
You can express your opinions around the relevant content.