The main idea behind the relational database management system (RDBMS) is the concept of relationships that exist among objects or entities. A relationship is an association of entities based on how they interact. The following terms are used in relation to modeling a relational database model: Entity, Attributes, Primary Key, and Foreign key (Begg & Connoly, 2003). Using a RDBMS, three types of relationships can be defined among entities: one-to-one, one-to-many, and many-to-many. To be appropriate, relationships must observe some sort of data integrity. In this context, the following paragraphs describe the importance of data integrity.
The three fundamental types of data integrity are entity integrity, referential integrity, and domain integrity. Entity integrity is a form of data integrity that requires every table in a relational database to have a unique column or columns that ensure that each row is uniquely identified. The unique column or columns is known as the primary key, thus no duplication of rows within a given table is acceptable. In essence, a primary key should be unique but not null (Gilfillan 2000). To ensure that there is validity and accuracy of data, a database designer should be able to select the correct primary key that reflects the requirements of the database in regard to information search, updating, and deletion of the stored data. For instance, when designing a school database, tables such as the STUDENT table and COURSES table may be included in the database. Thus, to ensure the accuracy of the entities, student number in the STUDENT table can act as the primary key, while course number in the COURSES table may act as the primary key. Both student number and course number uniquely identify other information in their respective tables hence entity integrity.
Another type of data integrity is the referential integrity. These are rules that are followed to preserve the defined relationships between tables when records are added or deleted. Referential integrity is typically enforced with a primary key and a foreign key combination. To link a table with another table, these two keys must exist. A foreign key is a key in one table that must match a primary key in another table to establish a relationship. Moreover, referential integrity requires that the matching fields must have the same data type, field properties, and same data. Secondly, both tables must belong to the same database. Lastly, a record cannot be deleted or updated from the primary table if matching records exist in a related table (Watson 2005). An example of referential integrity may exist in a Company database in which we have the EMPLOYEE table and the DEPARTMENT table. To connect EMPLOYEE table to DEPARTMENT table, Employee number field acts as a primary key and Department number field act as a foreign key, thus ensuring consistent relationships. Enforcing referential integrity is typically provided by the database management system (Navathe, 2000).
Lastly, domain integrity defines the required features of a specific column in a table. Every column has a certain domain that stems from the specified data type. Domain integrity is characterized by the data type, the NULL value recognition, the allowable values, and the default value. For instance, if we define the attribute of UnitNumber, of a STUDENT entity, as an integer, the value of every occurrence of such element must be a number and an integer. If we specify that the value should be positive, then a negative figure is not allowed. Again, having it as mandatory value in the column shows that the column is specified as NOT NULL (Begg & Connoly, 2003). In essence, each attribute should be specified with the following domain information: Data Type, Length, Date Format, Range, Constraints, Null Support, and Default Value. Domain integrity ensures consistency of data, validity of data, and identification of data.
Therefore, entity integrity, referential integrity, and domain integrity are important in relational databases because they ensure that data is valid and consistent, and thus relationships are set. Secondly, they reduce the redundancy of data since records can only be entered once. Thirdly, searching records is much faster (Gilfillan Ian, 2000). For instance, having Product number as a primary key in a Products table ensures that records are stored once and searches become faster. On the other hand, having supplier number as a foreign key in the same table enable a relationship to be set between the Product table and the Supplier table, hence increasing searches and lessening design difficulties.
References
Begg, C., & Connoly, T., 2003, Database Systems, 3rd Ed., Pearson Education Limited, Delhi.
Gilfillan, I., 2002, Introduction to Relational Databases, Web.
Navathe, S., 2000, Fundamentals of Database Systems, 3rd Ed., Addison Wesley, USA.
Watson, R., 2005, Data management: databases and organizations, 5th Ed., John Wiley, New York.