Automating the generation of database artifacts: From ER+ to SQL

Data engineers often need to transform a conceptual understanding of an application into deployable database artifacts spanning operational and analytical layers, heterogeneous locations, and explicit data-transformation pipelines. In this setting, conceptual specifications are valuable not only for the initial authoring of schemas and queries but also for coherently propagating subsequent conceptual changes into implementation artifacts. ER+ offers constructs for grouping, aggregation, line functions, and data transport, but translating these constructs into consistent executable artifacts remains a demanding task. This paper presents an extension of Online Database Architect (ONDA) that supports ER+ and automatically generates relational schemas and executable Structured Query Language (SQL) for both operational and analytical layers. The approach is defined by explicit mapping rules grounded in relational-algebraic semantics and constrained by invariants that ensure deterministic naming, key preservation, referential integrity, and sound compilation of grouping, aggregation, and line-function semantics. In particular, summaries involving line functions and aggregates are compiled through Common Table Expression (CTE)-based SQL patterns that preserve the intended grouping grain and avoid mixed-granularity expressions. We evaluate the approach using two case studies: a handcrafted ER+ model illustrating the end-to-end workflow and a TPC-H Q11-based scenario that represents a realistic analytical pattern. The generated artifacts show that ER+ specifications can be translated systematically into executable relational and analytical structures. A lightweight comparative evaluation further suggests reduced manual effort in the initial production of artifacts, improved consistency of generated analytical SQL, and maintainability when conceptual changes must be propagated into dependent implementations. These results indicate that conceptual models can serve as a practical basis for producing and evolving deployment-ready database artifacts.