Database Integrity

A database is a model of part of the real world. It should be consistent and its contents should match the section of reality it is modeling. Data integrity is one of the most important aspects to be taken into consideration when programming application software. You distinguish between the following:

• Semantic integrity

• Relational integrity

• Operational integrity

Semantic integrity

Semantic integrity refers to the consistency and completeness of the data. In a flight reservation system, for example, the field 'Occupied seats' for each flight must always contain a value which is less than or equal to the contents of the field 'Maximum number of seats' - to avoid overbooked flights. If this is not the case, the data is inconsistent. In addition, each new booking in the flight reservations table must increase the contents of the field 'Occupied seats' for the booked flight in the flights table. Together, both these operations form a unit and, if only one is executed, the database becomes incomplete.

In many database systems, you can check semantic integrity conditions with constraints or triggers. However, since these methods are not used in the R/3 System due to their lack of standardization, programmers must verify the semantic integrity of the database themselves.

As the flight reservation example shows, the data is usually in an inconsistent or incomplete state in the course of a transaction. For this reason, programmers need to be able to confirm the end of the transaction and thus the restoration of the semantic integrity of the data. This is achieved through the database commit.

Relational integrity

Relational integrity refers to the observation of the integrity rules governing the relational model. Since data modeling in the R/3 System is based on the relational data model, you must keep to these rules. They include:

• Primary key integrity
  
  You must be able to identify each database object uniquely by a primary key.
  Contrary to relational theory, you can create tables without a primary key in most database systems. In the ABAP/4 Dictionary, each table must have a primary key. For this reason, primary key integrity is automatic.
  

• Value range integrity
  
  Each value of a table field must exist in the value range of this field. In a flight reservation system, for example, you must be able to distinguish between private and business customers. This means that the field 'Business/private customer' can contain only the value 'B' (for 'business customer') or 'P' (for "private customer").
  In most database systems, you can specify value ranges as constraints when defining a table field. The database system then automatically prevents the occurrence of an invalid value in a database table. However, although these methods are not used in the R/3 System, you can define value ranges in the ABAP/4 Dictionary for both domains and database fields, and use them to guarantee the integrity of the database. In dialog transactions, the system uses this knowledge to check user input and to display possible entries.
  

• Foreign key integrity (referential integrity)
  
  Every foreign key must refer to an existing primary key. In a flight reservation system, for example, the contents of the field 'Airline carrier' must exist in the airline carriers table for each flight.
  In most database systems, you can specify foreign key relationships as a constraint when defining a table. The database system then automatically prevents the occurrence of an invalid line in a database table. However, although these methods are not used in the R/3 System, you can define foreign key relationships between tables in the ABAP/4 Dictionary and use them to guarantee the integrity of the database. In dialog transactions, the system uses this knowledge to check user input and to display possible entries.

Operational integrity

Operational integrity guarantees protection for the dataset against inconsistencies caused by several users attempting to gain access at the same time. The necessity for this is demonstrated by the next example, where clerks S1 and S2 want to make a booking in the flight reservation system at the same time.

• S1 reads the line of the planned flight from the flights table. The value 'Occupied seats' is less than the value 'Maximum number of seats'.

• S2 reads the line of the planned flight from the flights table. The value 'Occupied seats' is less than the value 'Maximum number of seats'.

• S1 makes a new flight reservation.

• S1 increases 'Occupied seats' by 1.

• S2 makes a new flight reservation.

• S2 increases 'Occupied seats' by 1.

Since both clerks find the same value in the field 'Occupied seats', the flight can be overbooked.

Database systems guarantee operational integrity automatically by enqueueing competing accesses. This process is achieved through database locking which can be demonstrated by the following variant of the above example:

• S1 reads the line of the planned flight from the flights table with the intention to change. The value of 'Occupied seats' is less than the value of 'Maximum number of seats'.

• S2 reads the line of the planned flight from the flights table with the intention to change. Since the line is locked by S1, S2 must wait.

• S1 makes a new flight reservation.

• S1 increases 'Occupied seats' by 1.

• S1 ends the LUW (Logical Unit of Work) and thus releases the database locks.

• S1 can now access the line of the planned flight from the flights table. The value of 'Occupied seats' is less then the value of 'Maximum number of seats'.

• S2 makes a new flight reservation.

• S2 increases 'Occupied seats' by 1.

• S2 ends the LUW (Logical Unit of Work) and thus releases the database locks.