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

TL;DR

This paper analyzes how well structural constraints from ER models, especially participation and cardinality, are preserved after transforming into relational schemas, revealing limitations in representing these constraints explicitly.

Contribution

It provides a detailed analysis of the limitations in representing participation and cardinality constraints in standard ER-to-relational transformations.

Findings

Minimum participation constraints are not unambiguously represented.

Exact maximum participation constraints are often not preserved.

Many-to-many relationships only indicate maximum cardinality exceeds one.

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.