5 - Transforming the Conceptual Data Model to SQL

Abstract

This chapter focuses on the database life-cycle step that is of particular interest when designing relational databases: transformation of the conceptual data model to candidate tables and their definition in SQL. There is a natural evolution from the entity-relationship (ER) and unified modeling language (UML) data models to a relational schema. The evolution is natural, in fact, that it supports the contention that conceptual data modeling is an effective early step in relational database development. This contention is proven to some extent by the widespread commercialization and use of software design tools that support not only conceptual data modeling but also the automatic conversion of these models to vendor-specific SQL table definitions and integrity constraints. Entities, attributes, and relationships in the ER model and classes, attributes, and associations in UML can be transformed directly into SQL (SQL-99) table definitions with some simple rules. Entities are transformed into tables, with all attributes mapped one-to-one to table attributes. Tables representing entities that are the child (“many'” side) of a parent–child (one-to-many or one-to-one) relationship must also include, as a foreign key, the primary key of the parent entity. A many-to-many relationship transforms into a table that contains the primary keys of the associated entities as its composite primary key; the components of that key are also designated as foreign keys in SQL.