A Bitemporal SQL Database Design Method from the Enhanced Entity-Relationship Model

Abstract

Recently temporal supports have been included in the SQL standards, leading to a great bridge between research works and real-world applications. However, showing this bridge in practice needs to be considered more. Such a shortage needs to be resolved due to the fact that time is ubiquitous in every application domain. In this paper, we propose a novel design method to design a bitemporal SQL database from an enhanced-entity-relationship schema. From this conceptual schema, a bitemporal SQL database schema is defined automatically. As a theoretical evaluation, our proposed method is novel in the following points. Firstly, although the method is dedicated to bitemporal databases, its starting point is with the enhanced entity-relationship model which is so popular that practicing database designers can work with it well. Secondly, its temporal supports are explicitly extracted and included in the data model mapping in an automatic manner. The automatic mapping process is based on topological sorting and thus performed in a certain sequence to derive a bitemporal SQL database schema with the SQL temporal support. Above all, our method helps easily deploy bitemporal databases in real-world applications. Indeed, its illustration has been shown comprehensively for VNUMED, an intermediate research medical database.