Design and Implementation of Data Integrity in Database Application System

This paper mainly introduces the design method of data integrity in the SQL Server database application system on the server side and the client side, and expounds the strategy of realizing data integrity in combination with the hospital management information system (JY-HMIS for short).

Keywords: data integrity database transaction processing

order

Data integrity refers to the correctness and consistency of data, which has two meanings: (1) the correctness of data values, that is, the values of field columns must conform to the provisions of data value range, type and precision; (2) Consistency of related data, that is, matching of column values of contact fields of related tables. In database application system, ensuring data integrity is the basic requirement of application system design; The realization of data integrity is related to the correctness, consistency and reliability of the data system and the success or failure of the system. JY-HMIS adopts C/S mode architecture, and its database application system is: SQL Server 7.0 client development tool is PowerBuilder 6.5. The following is mainly introduced from two aspects: server-side database management system and client-side application program.

1 Server uses SQL Server 7.0 database to realize data integrity.

When using INSERT, DELETE and UPDATE statements to modify database contents, the integrity of data may be destroyed. In order to ensure the correctness and consistency of stored data, SQL Server imposes one or more data integrity constraints on the data. These constraints limit the data values in the database, or limit the data values generated by database modification, or limit the modification of some values in the database.

In the SQL Server relational database, there are three main types of data integrity: entity integrity (ensuring that all rows in the table are unique); Referential integrity (maintenance of primary key and foreign key relationship, involving consistency maintenance of data of two or more tables); Domain integrity (a valid set of columns, which is a restriction on business management or database data). There are two ways to realize data integrity at the telegraph end: define the integrity constraints of Creat Table, and define rules, default values, indexes and triggers.

1. 1 defines the createtable integrity constraint.

This method is to add table-level constraints or column-level constraints to the command statement of creating database tables to realize data integrity. For example, add non-empty constraints, default constraints, unique code constraints, primary key constraints, foreign key constraints, check constraints and so on. In the table construction statement. Its main features are: simple definition, safety and reliability, and convenient maintenance.

1. 1. 1 non-empty constraint, default constraint and check constraint

Non-empty constraint limit column value cannot be empty; The default constraint specifies that when inserting data into the database, if the user does not explicitly give the value of a column, SQL Server will automatically enter the predetermined value; Check constraints are used to limit the range of columns.

For example, when creating a book registration form, column values such as registration date, book category code, registration number, Chinese name, etc. are defined. Cannot be empty; The default value of the number of pages is1; The default value of unit price is 0; The status of a book can only be one of "in the library", "lent" and "lost".

1. 1.2 primary key constraint and unique constraint

Both primary key constraint and unique constraint establish unique indexes for the specified columns, that is, columns with unique indexes are not allowed to have the same values. The primary key constraint is stricter, not only duplicate values are not allowed, but also null values are not allowed.

For example, in the department coding table, create a primary key constraint on column ksbm and a unique constraint on ksmc.

1. 1.3 foreign key constraint

Foreign key constraint, also known as referential integrity constraint, is used to restrict the matching of key code column values outside this table with primary key code field column values of related tables, that is, to ensure the consistency of related data.

For example, when creating the doctor code table, the department ssks to which the doctor belongs is a foreign key code, which is limited to be consistent with the value of the department code ksbm column in the department code table.

1.2 define rules, default values, indexes and triggers.

Although the method of defining constraints in the database table creation command statement is simple, convenient and safe, it is only effective for a specific table and cannot be applied to other tables, and the constraints can only be modified or deleted by the alter table command, which lacks certain flexibility. On the contrary, creating rules, indexes and trigger objects that are relatively independent of tables in the database can also achieve data integrity and achieve more complex and perfect data integrity constraints. Its main features are strong function, high efficiency and convenient maintenance.

1.2. 1 Define rules

Rules are similar to check constraints in table definitions and are used to limit the range of columns. But it is not limited to a specific table, and can be used for columns of other tables or user-defined data types.

For example, when defining the coding rule Rul-ypbm for drugs, you can bind it to other tables.

1.2.2 define the default value.

Default is similar to the default constraint in the table definition, and is used to set the default value input of the column. It is also not limited to a specific table, and can be used for columns of other tables or user-defined data types.

1.2.3 define the index

An index is similar to a unique constraint in a table definition to ensure the uniqueness of column values. In addition, it can also use clustered index and nonclustered index options to enhance the performance of data retrieval.

For example, according to the charging date and charging serial number, the non-clustered index of outpatient charging subsidiary ledger is established, which greatly improves the data query speed. Before the index is established, it takes about 5 minutes to find out the qualified records from more than 200,000 records, and only 4 seconds after the index is established.

1.2.4 define triggers

Trigger is an effective method to realize complex integrity constraints. When the data it protects is modified, the trigger will be activated automatically to prevent incorrect, unauthorized or inconsistent modification of the data. Similar to referential integrity constraints in table definitions; Triggers can also be used to ensure the consistency of related data. You can also accomplish some tasks that cannot be accomplished by referential integrity constraints defined by tables.

The client uses the application developed by PB to realize data integrity.

2. 1 Use PB control to ensure data integrity.

In the application program written with PB development tool, the correctness of the input data can be checked by using the column check attribute of the data window object. In the window input interface, you can also use drop-down lists, check boxes, radio buttons and other controls to select and input fixed values to ensure the correctness of data; In addition, the application itself can effectively check the input data to ensure the integrity of the data.

2.2 Use the characteristics of transactions to ensure the consistency of data.

Because every operation of PB is based on events in the control, a complete transaction should be concentrated in an event or an operation unit. For example, payment processing in outpatient pricing charges; The data under the same charging serial number should be recorded in the subsidiary ledger, the first-level subsidiary ledger and the general ledger after processing, and the Registration Form of Charging Serial Number should be updated at the same time; All these processes are regarded as a complete task, and all statements can not be submitted to the database until they are successfully executed; If the execution of a statement is unsuccessful, all operations of the transaction should be cancelled and the transaction should be returned to the initial state to ensure the consistency of data. The method of using client application to realize data integrity is characterized by good interactivity and strong functions, but it is too large in programming, difficult to maintain and poor in reliability.

Three Strategies for Realizing Data Integrity

As can be seen from the above, in the SQL Server database application system, the methods to achieve data integrity have their own characteristics. For specific application systems, one or more methods can be used to establish the data integrity mechanism according to the needs. The strategy is:

(1) For the data entered through window operation, the client application is generally used to ensure the integrity of the data. In this way, on the one hand, illegal data can be rejected before it is submitted to the database, on the other hand, users can get operational feedback information in time and make correct choices.

(2) For the data transmitted through other channels, the database management system on the server side is generally needed to realize the data integrity.

(3) In order to maintain the consistency of data, it is suitable to realize the constraints of triggers or table definitions of server-side databases. This can reduce the development of client applications and improve the operating efficiency, maintainability and reliability of application systems.

(4) For systems that require high data integrity and security, multi-layer protection barriers are needed to ensure data integrity and security. For example, not only the validity of the input data is checked in the client application system program, but also the table constraints, rules, default values and triggers for data integrity constraints are established in the server database. This can improve the reliability and security of system data.

4 conclusion

Database integrity is one of the most basic requirements of database application system. In SQL Server database application system, data integrity can be achieved by three methods: defining the constraints of server-side database tables, database rules, default values, indexes, triggers and client applications. They have their own characteristics and should be used flexibly in the application system according to the specific situation.

refer to

[1] Yang SQL Server 7.0 Relational Database System Management and Development Guide. Ministry of Machinery Industry Press, 2000.

[2] Microsoft SQL Server 7.0 database system management and application development. Beijing People's Posts and Telecommunications Publishing House, 1999.

[3] Xue Huacheng. Management information system. Tsinghua University Publishing House, 1995.