AM-integrity in designing relational database models

The article discusses a special type of integrity in databases – ancestral multiplicity integrity (AM-integrity), a concept introduced by the authors at the conceptual–ontological level within “entity–relationship” models. In this paper, this concept is extended to the relational model for the purpose of practical application in creating databases. Related notions are examined, including the lineage of ascending ancestry, the relation of ancestral multiplicity, and the corresponding integrity constraint. Transitioning to the relational model requires consideration of primary and foreign keys, referential integrity, and table triggers. As a universal means of ensuring integrity in the relational environment, the paper proposes an approach based on database triggers. These triggers detect and block insert and update operations that would lead to violations of integrity. The need for procedural programming of triggers is noted, along with the challenges of transferring data between different database management systems due to variations in procedural programming languages. An alternative approach is suggested, based on leveraging built-in mechanisms for maintaining referential integrity, in which ancestral multiplicity integrity is ensured as part of referential integrity. The specific solution depends on the use of simple or composite, natural or surrogate keys, as well as on the technical limitations of the database implementation environment. In some cases, redundant key components may need to be introduced to track ancestor instances along ancestral lineages. Test examples of relational models are provided for different relational systems (complete implementations in MySQL and MariaDB, and partial implementations in PostgreSQL, MS SQL Server, and Oracle Database).