ER to Relational Model Mapping: Information Preserved Generalized Approach

Abstract

The Entity-Relationship (ER) model is widely used to create ER schemas to represent application domains in the Information Systems development field. However, when an ER schema is transformed to a Relational Database Schema (RDS), some critical information modeled on the ER schema may not be represented meaningfully on the RDS. This situation causes a loss of information during the transformation process. In our previous studies, we showed that the deficiencies that exist both in the ER model and the transformation algorithm cause this situation. Accordingly, we modified the ER model and the transformation algorithm to eliminate the deficiencies and thereby to preserve the information in the transformation process. We then showed that a mapping that is one-to-one and onto exists from the ER Schema to the RDS, and the information represented on the ER schema is preserved on the RDS. For this purpose, the ER schema should be created using the modified ER model and transformed to the RDS by the modified transformation algorithm. However, this concept has not yet been proved formally. It needs to be testified for any ER schema representing any application domain. Subsequently, following the modified ER model, we also proposed a generic ER schema ̶ an ER schema that represents any real-world phenomena in symbolic notation ̶ for using in a future proof creation process. Thus, in this work, we aim to create a formal proof for validating the work that we had done. For simplicity, we use a generic ER schema that consists of two regular (strong) entity types and a one-to-many-relationship-type. We first show that the generic ER schema can be partitioned into unique segments. We call them ER-construct-units, where each one represents a unique semantic meaning in the real world. The ER schema can be viewed to have been made up of the set of ER-construct-units. Both the ER schema and the ER-construct-unit set are equivalent. Second, we transform the generic ER schema to its corresponding RDS using the modified transformation algorithm. We then show that the RDS can also be partitioned into unique segments, which we call Relation-schema-units. Next, we show that a mapping that is one-to-one and onto exists from the set of ER-construct-units to the set of Relation-schema-units. In conclusion, we show that any ER-construct-unit in the ER schema has its own and unique Relation-schema-unit on the RDS. Therefore, any piece of information represented on the ER schema has its own and unique representation on the RDS. The proof can be expanded to any generic ER schema that is even bigger than the current one, and accordingly, the same result can be obtained. Since the generic ER schema means a generalized representation of any real-world ER schema, we conclude that information represented on any ER schema is preserved on its corresponding RDS.