Implicit Representation of Structural Constraints in ER-to-Relational Transformation: An Analysis of Cardinality Preservation

Abstract: This study examines the extent to which structural constraints specified in conceptual schemas are represented after transformation to logical schemas. Focusing on the conceptual-to-logical mapping, an Entity-Relationship (ER) model containing binary relationship types is transformed into a Relational Database Schema (RDS). The analysis is conducted under the classical transformation framework in which the logical schema is defined solely by primary key (PK) and foreign key (FK) constraints. Using generalised ER models with variable structural constraint values, the resulting RDS structures are evaluated to determine whether minimum and maximum participation constraints are represented unambiguously. The findings show that, for one-to-one and one-to-many relationships, RDSs do not unambiguously capture minimum participation constraints and do not encode exact maximum participation beyond limited cases. For many-to-many relationships, the schema indicates only that maximum cardinalities exceed one, without preserving exact values. These results clarify the representational limits of standard ER-to-relational transformations and have implications for schema design and constraint enforcement.