Case Studies on Integrity in Relational Databases

Integrity is one of the fundamental properties that a database must possess. Integrity describes a database's ability to consistently and accurately represent the characteristics of the real-world problem for which it has been designed.

These characteristics of the problem, identified during the analysis phase, define the constraints inherent to each set of objects, the objects themselves, and the properties associated with them. Furthermore, they describe the constraints that exist between sets of objects, as well as any constraints that may be present among the properties of those objects.

These constraints determine several aspects of the nature of the real-world problem. On the one hand, they establish how the properties of objects must be measured, that is, the domain within which each property must be defined and represented.

On the other hand, they determine how each object must be uniquely identified, ensuring that no ambiguity exists in the identification of any object that may belong to the problem domain at a given time.

In addition, these constraints define how relationships may be established, not only among the sets of objects that constitute the problem domain, but also among the individual objects that belong to those related sets.

All constraints, which must be clearly and comprehensively specified during the problem analysis phase, are often difficult to fully represent within the conceptual model through the conceptual schema (Entity–Relationship model) developed as part of that analysis. Nevertheless, the complete textual description of the characteristics and constraints of the real-world problem must be accurately and unambiguously translated and reflected in the relational model used to describe the database.

This manuscript presents the principal mechanisms that enable the accomplishment of this task. These mechanisms facilitate the description and specification, within a relational database, of the constraints that exist in the domain of a given problem.

To this end, several practical case studies are presented throughout the text. These cases consist of simple simulated problems represented through conceptual models. The conceptual models are subsequently transformed into relational models, the corresponding relational databases are implemented, and the potential integrity issues that may arise are examined.

For each case study, solutions to the identified integrity problems are described through the definition of integrity rules that support the construction of a consistent and reliable database.

The objective of this manuscript is to provide both database professionals and students with supporting material for the relational database design process. The simple case studies presented can be readily extrapolated to real-world scenarios encountered by readers, thereby offering practical guidance for the development of relational databases that preserve data integrity.