Discuss the entity integrity and referential integrity constraints. why is each considered important

A database schema implies the structure and design of database. Database schema is regarded as a collective term for various properties of database such as, tables, constraints, relations, etc. A relational database schema consists of a set of relational database schemas and set of integrity constraints.

There are mainly four different integrity constraints that are imposed on a relational database. These are mentioned as:

1. Domain Constraints- These constraints in the database schema state the conditions that each relational instance must satisfy. The value of each attribute of a relation must be an indivisible value and must be drawn from the domain associated with that attribute. Hence, the value of an attribute must confirm to the data type associated with the domain.

2. Key Constraints and NULL Value- These constraints imply the existence of candidate keys. As per the Key Constraints, in every instance of a relational database schema, the tuples can be uniquely identified by their values for certain attributes. This implies that each data record corresponding to a tuple of relation in a table must be distinct. That is, in other words, no two tuples in a relation can have the same combination of values for all their attributes. Every relation has at least one key, which is the combination of all its attributes and is known as Super-Key. The Super-key specifies the uniqueness constraints.

However; a Super-key may contain some additional attributes that are not necessary for unique identification. Minimal super key is that part of super key from which removal of any attribute from the Super-key leaves a set of attributes that is not a Super-key. That is, if any attribute is removed, then the uniqueness constraint is disturbed. The Minimal Super-key is also known as Candidate key.

In case a relation has many Candidate keys, then out of them one is arbitrarily chosen and regarded as Primary key. The attributes of Primary key are represented as underlined in a schema. The other Candidate keys are regarded as Alternate or Unique keys.

3. Entity Integrity Constraints- A Primary key is used to identify individual tuple in a relation. As per the Entity Integrity Constraints, the value of Primary key cannot be null. If in case, the Primary key has null value, then it implies that we cannot identify such tuples as they are same. This implies that tuples are duplicated and the uniqueness constraints are violated.

4. Referential Integrity Constraints- Referential integrity constraint is specified between two or more relations to maintain consistency among the tuples of such relations. These are the constraints implies by the existence of foreign keys. Hence, a tuple in one relation that refers to another relation must refer to an existing tuple in that other relation.

The above mentioned integrity constraints are certain conditions that are imposed on a database schema. The importance of integrity constraints is that by imposing certain specifications, the integrity constraints restrict the data that is stored in a database instance. In case, the database instance satisfies all the integrity constraints, then it is regarded as a legal database instance and hence, can be stored in the database. In case, these constraints are violated, then it implies that database is not maintained in an efficient manner and the database contains illegal database instances.

DB2® ensures referential integrity between your tables when you define referential constraints.

A referential constraint is the rule that the nonnull values of a foreign key are valid only if they also appear as values of a parent key. The table that contains the parent key is called the parent table of the referential constraint, and the table that contains the foreign key is a dependent of that table.

The relationship between some rows of the DEPT and EMP tables, shown in the following figure, illustrates referential integrity concepts and terminology. For example, referential integrity ensures that every foreign key value in the DEPT column of the EMP table matches a primary key value in the DEPTNO column of the DEPT table.

Figure 1. Referential integrity of DEPT and EMP tables

Two parent and dependent relationships exist between the DEPT and EMP tables.

• The foreign key on the DEPT column establishes a parent and dependent relationship. The DEPT column in the EMP table depends on the DEPTNO in the DEPT table. Through this foreign key relationship, the DEPT table is the parent of the EMP table. You can assign an employee to no department (by specifying a null value), but you cannot assign an employee to a department that does not exist.
• The foreign key on the MGRNO column also establishes a parent and dependent relationship. Because MGRNO depends on EMPNO, EMP is the parent table of the relationship, and DEPT is the dependent table.

A table can be a dependent of itself; this is called a self-referencing table. For example, the DEPT table is self-referencing because the value of the administrative department (ADMRDEPT) must be a department ID (DEPTNO). To enforce the self-referencing constraint, DB2 requires that a foreign key be defined.

Similar terminology applies to the rows of a parent-and-child relationship. A row in a dependent table, called a dependent row, refers to a row in a parent table, called a parent row. But a row of a parent table is not always a parent row—perhaps nothing refers to it. Likewise, a row of a dependent table is not always a dependent row—the foreign key can allow null values, which refer to no other rows.

Referential constraints are optional. You define referential constraints by using CREATE TABLE and ALTER TABLE statements.

To support referential integrity, DB2 enforces rules when users insert, load, update, or delete data.

Another type of referential constraint is an informational referential constraint. This type of constraint is not enforced by DB2 during normal operations. An application process should verify the data in the referential integrity relationship. An informational referential constraint allows queries to take advantage of materialized query tables.

Data corruption and inefficiency have drastically affected companies and firms proving its maintenance procedures to be costly, difficult and time consuming. The concept of data integrity thus evolved, conveying the precision and consistency of the data over processes for its entire life cycle. These include processes for instance updates, transfer, storage or retrieval of data. In much simpler terms the relationship a piece of data has with other data is also known as data integrity.Moreover, it is enforced when a database is at its designing stage by a set of rules which is consistently applied to all the data being entered in the database. These rules check for errors and validate data to maintain its precision and usefulness. The important rules through which data is validated are named as integrity constraints. The forms of data integrity constraints which are of utmost importance are entity integrity and referential integrity.Entity integrity is a prominent aspect of data integrity. It is an integrity rule which is maintained through the core concept of the primary key, which ensures that there are no replica records inside the row thus. Furthermore it emphasizes that a unique column or columns should be chosen to be a primary key and it restricts it to be duplicate and null. In addition to this, the system maintains entity integrity by not allowing operations such as insert and update that could produce an invalid primary key. Ensuring every student’s id in the students table to be unique is an example of entity integrity.The other type of data integrity constraint is termed as referential integrity. According to Blaha (2005) “referential integrity is a database constraint that ensures that references between data are indeed valid and intact. Referential integrity is a fundamental principle of database theory and arises from the notion that a database should not only store data, but should actively seek to ensure its quality.” Moreover referential integrity in context to a relational database concerns with the relationships between two tables. The concept of referential integrity is maintained through the combined concept of primary key and foreign key. This states that for a referential integrity to be enforced a foreign key should contain values from a parent table’s primary key field.Entity integrity and referential integrity both play a crucial role in any proper database design. As entity integrity ensures the accessibility and successful search for a present row, as well as it prevents data conflict between the rows. Furthermore any failure in searching the row will indicate that the row has no existence in the table. On the other hand, referential integrity is important because its presence ensures that assigning a non existing foreign key to a table would be impossible. Moreover, a further example for importance of referential integrity could be depicted by the instance of an insurance agent and customer table. The referential integrity enforcement in an insurance agent is assigned to a customer relationship means that it will be impossible to assign a non existing insurance agent to a customer. In addition to this, integrity could also beportrayed in the example of a store database. Whereby, customer id is the primary key and customer id in the invoice table is the foreign key. If one wants to delete customer record, all the other records such as invoices have to be deleted first.Lastly it could be said that the integrity constraints have obvious beneficial significance. As it improves data quality by reducing data redundancy and inconsistencies thus to maintain data accuracy and reliability, integrity constraints should be properly incorporated at a design level.

                                                                                        ~Copyright Vishaal Sagar 2012~

References: