The preservation of the integrity of a database system is concerned with the maintenance of the correctness and consistency of the data In a multi-user database environment this is a major task, since integrity violations may arise from many different sources, such as typing errors by data entry clerks, logical errors in application programs, or errors in system software which result in data corruption.
Many commercial database management systems have an integrity subsystem, which is responsible for monitoring transactions, which update the database and detecting integrity violations. In the event of an integrity violation, the system then takes appropriate action, which should involve rejecting the operation, reporting the violation, and if necessary returning the database to a consistent state.
Integrity rules may be divided into three ‘broad categories:
• Domain integrity rules
• Entity integrity rules
• Referential integrity rules
We’ll be covering the following topics in this tutorial:
Domain integrity rules
Domain integrity rules are concerned with maintaining the correctness of attribute values within relations. A domain integrity rule therefore, is simply a definition of the type of the domain, and domain integrity is closely related to the familiar concept of type checking in programming languages. The definition of the type of a domain must be as precise as possible in order to avoid violations of domain integrity.
Example: If we have an attribute AGE, it is not sufficient to describe its type as INTEGER since this does not prevent unrealistic values for AGE (e.g. negative values) being entered into the database. At the very least we should be able to specify that the domain type for attribute AGE is POSITIVE_INTEGER, and ideally it should be possible to specify upper and lower bounds for values of AGE. Unfortunately commercial database management ‘systems typically provide only simple types for domains. For example, the ORACLE database management system provides the domain types: NUMBER, CHAR (variable length character strings), DATE and TIME. INGRES and DB2 provide similar restricted domain types.
Entity integrity rules
Entity integrity rules relate to the correctness of relationships among attributes of the same relation (e.g. function: dependencies) and to the preservation of key uniqueness.
Requirement of entity integrity rules: All entries are unique and no null entries in a primary key.
Purpose of Entity identity rules: Guarantees that each entity will have a unique
Referential integrity rules
Referential integrity rules are concerned with maintaining the correctness and consistency of relationships between relations.
Requirement of Referential integrity rules: Foreign key must have either a null entry or an entry that matches the primary key value in a table to which it is related.
Purpose of Referential integrity rules: Makes it possible for an attribute NOT to have a corresponding value, but it will be impossible to have an invalid entry. The enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table.
Example: A customer might not (yet) have an assigned sales representative (number), but it will be impossible to have an invalid sales representative (number).
Example of Entity integrity rule and referential integrity rule:
Consider the following Database Table features:
Table Name: CUSTOMER Table Fields: CUS_CODE, CUS_NAME, CUS_RENEW _DATE, AGENT_CODE Primary Name: CUS_CODE Foreign key: AGENT_CODE Table Name: AGENT Table Fields: AGENT _CODE, AGENT _AREA _CODE,AGENT _PHONE, AGENT LNAME Primary Name: AGENT CODE Foreign key: NoneEntity integrity: The CUSTOMER table’s primary key is CUS_CODE. The CUSTOMER primary key column has no null entries and all entries are unique. Similarly, the AGENT table’s primary key is AGENT_CODE, and this primary key column also is free of null entries.
Referential integrity: The CUSTOMER table contains a foreign key AGENT_CODE, which links entries in the CUSTOMER table to the AGENT table.
Dinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular