Transformation from Conceptual Models to Relational Models

(1)

Escuela Técnica Superior de Ingeniería Informática Departamento de Lenguajes

y Sistemas Informáticos 1. Model-driven development 2. Transformation

of entities 3. Transformation

of associations 4. Transformation

of classifications 5. Sample

transformation

y Sistemas Informáticos

Transformation from Conceptual Models to

Relational Models

Grupo de Ingeniería del Software y Bases de Datos Departamento de Lenguajes y Sistemas Informáticos

Universidad de Sevilla november 2013

transformation

Transformation from CM to RM

• Learning objectives

– Know the rules to transform conceptual models into relational models.

– Know the three classifications transformation strategies, their advantages and disadvantages.

– Be able to apply the transformation algorithm to a given conceptual model.

november 2013

Introduction to Software Engineering and Information Systems

1

(2)

transformation

• Model-Driven Development(MDD)

– Development approach in which products are generated by transforming models until obtaining source code.

– In the development of information systems with relational databases, conceptual models can be transformed into a relational model that can be then used to generate SQL code.

november 2013

Introduction to Software Engineering and Information Systems

2

Modelo relacional

c l ass Catál ogo de produc tos L aboratori o cif nombre dirección

Catál ogo fecha

L í neaD eCatál ogo precioMenosDeCien precioMásDeCien

Produc to código nombre descripción Al m ac én

nombre dirección

? al m ac enaProduc tosEn

*

* ref erenc i a

1

*

* publ i c a

Modelo conceptual

create table orders ( order_id number, order_dt date, cust_id references customer constraint pk_orders (order_id) )

….

Código SQL

MC MR MR SQL

transformation

Toro, 2011

Transformation from CM to RM

• Transformation of entities

– General rule:

• Each entity is transformed into a relation with the same name (usually in plural).

• Each attribute _e of the entity is transformed into an attribute _r of the relation with the same name.

– Pay attention to:

• Define domains for attribute _r depending on the semantics of the corresponding attribute _e . *

• Add an OID as primary key (PK), unless there are simple semantic keys.

• Add an alternate key (AK) for each

semantic key that has not been

used as OID.

(3)

transformation

• Transformation of entities

– Object Identifiers(OID)

• These are artificial attributes* used to identify objects without relying on semantic keys.

• The generation of their values is usually delegated to the DBMS (autoincrement, sequences, etc..).

– Semantic keys

• They are attributes or sets of attributes that are key to the problem domain.

• Sometimes, they can be used as OID because they really are so in the problem domain: ID, SSN, product code, etc..

november 2013

Introduction to Software Engineering and Information Systems

4

* Calledsurrogates, they are usually numeric.

transformation

Transformation from CM to RM

• Transformation of entities

– Example:

november 2013

Introduction to Software Engineering and Information Systems

5

OID_D denominación presupuesto ciudad 1 Historia 20.000€ Sevilla

2 Arte 5.000€ Sevilla

3 Dibujo 5.500€ Cádiz

.. … … …

Departamentos

Departamentos(OID_D, denominación, presupuesto, ciudad) PK(OID_D), AK(denominación)

class Entidad

Departamento denominación presupuesto ciudad

(4)

transformation

• Transformation of associations

• General rule:

• Associations 1: n are represented by a foreign key in the relation of the entity with role n.

• 1:1 associations are represented by a foreign key in any of the relations.

• Associations m: n are represented using an auxiliary relation with foreign keys to both relations.

– Pay attention to:

• If a role is {ordered}, we must add an attribute (if not already) that specifies the order in the relation including the foreign key.

november 2013

Introduction to Software Engineering and Information Systems

6

transformation

Toro, 2011

Transformation from CM to RM

• Transformation of associations

– Example(1:n)

nif nombre Apellidos OID_D 123… Luis Gómez Sanz 1 456… Ana García Díaz 3 789… Isabel Bernal Sanz 3 Empleados

Departamentos(OID_D, denominación, presupuesto, ciudad) PK(OID_D), AK(denominación)

Empleados(nif, nombre, apellidos, OID_D) PK(nif), FK(OID_D) / Departamentos

OID_D denominación presupuesto …

1 Historia 20.000€ …

2 Arte 5.000€ …

3 Dibujo 5.500€ …

Departamentos

class Asociación 1:n

Empleado nif nombre apellidos

Departamento denominación presupuesto ciudad

*

trabajaEn 1

(5)

transformation

• Transformation of associations

– Example (1:n ordered)

– Compositions are treated like any other association 1:n *.

november 2013

Introduction to Software Engineering and Information Systems

8

Facturas(número, fecha) PK(número)

LíneaFactura(OID_LF, cantidad, precio, factura, orden) PK(OID_LF), AK(factura, orden)

FK(factura) / Facturas

class Composición con orden

Factura número fecha

LíneaFactura cantidad precio 1..*

{ordered}

* There exists certain differences that will be studied in the lesson about SQL.

transformation

Transformation from CM to RM

• Transformation of associations

– Example(1:1)

– It is a particular case of the 1: n.

– Placing the foreign key role in the relation with optional rol we can avoid store null values.

november 2013

Introduction to Software Engineering and Information Systems

9

Empleados(nif, nombre, apellidos) PK(nif)

Solicitudes(OID_S, fecha, …, nif) PK(OID_S), FK(nif) / Empleados

class Asociación 1:1

Solicitud fecha 1 ...

presenta 0..1

(6)

transformation

• Transformation of associations

– Example (m:n)

november 2013

Introduction to Software Engineering and Information Systems

10

Empleados(nif, nombre, apellidos) PK(nif)

Proyectos(OID_P, nombre, fechaInicial, presupuesto) PK(OID_P) ParticipaEn(OID_PE, nif, OID_P) PK(OID_PE), AK(nif, OID_P)

FK(nif) / Empleados, FK(OID_P) / Proyectos

class Asociación m:n

Proyecto nombre fechaInial presupuesto

*

participaEn

*

nif nombre …

123… Luis …

456… Ana …

789… Isabel …

… … …

Empleados

OID_P nombre … 1 Apollo … 2 Viking …

3 Mars …

… … …

Proyectos nif

OID_PE

OID_P

123… 1 2

123… 2 3

789… 3 2

… … …

ParticipaEn

transformation

Toro, 2011

Transformation from CM to RM

• Transforming classifications

– Three strategies of transformation for a classification hierarchy:

• A relation for each class of the hierarchy.

• A relation for each concrete subclass.

• A single relation for the entire hierarchy.

– Example:

class Clasificación

Vehículo matrícula

Automóvil plazas combustible

Camión ejes

{completa, disjunta}

(7)

transformation

• Transforming classifications

• A relation for each class of the hierarchy.

november 2013

Introduction to Software Engineering and Information Systems

12

class Clasificación Vehículo matrícula

Camión ejes {completa, disjunta}

Vehículos(OID, matrícula) PK(OID) AK(matrícula) Camiones(OID, ejes)

PK(OID), FK(OID) / Vehículos Automóviles(OID, plazas, combustible)

PK(OID), FK(OID) / Vehículos

OID matrícula 1 1212-BFD 2 5588-KMK 3 1111-BCH

… …

Vehículos

plazas combust. OID

5 Diésel 1

4 Gasolina 3

… … …

Automóviles

OID ejes

2 3

… …

Camiones

transformation

Transformation from CM to RM

• Transforming classifications

– A relation for each concrete subclass *

november 2013

Introduction to Software Engineering and Information Systems

13

Camiones(OID, matrícula, ejes) PK(OID), AK(matrícula) Automóviles(OID, matrícula, plazas, combustible)

PK(OID), AK(matrícula)

OID matrícula ejes 2 5588-KMK 3

… … …

Camiones Automóviles

OID matrícula plazas combust.

1 1212-BFD 5 Diésel 3 1111-BCH 4 Gasolina

… … … ….

* Los valores de los OIDs de las relaciones deberían ser disjuntos si se desean hacer consultas sobre la superclase.

(8)

transformation

• Transforming classifications

– A single relation for the entire hierarchy (with discriminant)

november 2013

Introduction to Software Engineering and Information Systems

14

Vehículos(OID, matrícula, clase, plazas, combustible, ejes)

PK(OID), AK(matrícula)

Vehículos

OID matrícula clase plazas combust. ejes

1 1212-BFD A 5 Diésel null

2 5588-KMK C null null 3

3 1111-BCH A 4 Gasolina null

… … … ….

transformation

Toro, 2011

Transformation from CM to RM

• Transforming classifications

– A single relation for the entire hierarchy (with Boolean indicators for each subclass)

Vehículos(OID, matrícula, esA, plazas, combustible, esC, ejes)

PK(OID), AK(matrícula)

Vehículos

OID matrícula esA plazas combust. esC ejes

1 1212-BFD true 5 Diésel false null

2 5588-KMK false null null true 3

3 1111-BCH True 4 Gasolina false null

(9)

transformation

• Comparing strategies

november 2013

Introduction to Software Engineering and Information Systems

16

Strategy

Clasification

Incomplete Complete Disjoint Overlapped A table per

class

++ +

A lot of joins to recover objects.

+

A table for concrete subclass

--

Not allowed

++ ++ -

Redundancy for attributes of objects that belong to several subclasses.

A single table (

discriminant

)*

+

The discriminant may be null.

A lot of nulls.

+

A lot of nulls.

+

A lot of nulls.

+

The discriminant should be able to express that an object belong to more than one class.

A single table (booleans)*

+

All indicators could be false.

A lot of nulls.

+

A lot of nulls.

+

A lot of nulls.

++

* These are recommended when there are few subclasses and they have few attributes.

transformation

Transformation from CM to RM

• Example of transformation

november 2013

Introduction to Software Engineering and Information Systems

17

class Vinos Bodega nombre denominaciónOrigen

Vino nombre grados

Uva nombre

Joven Crianza

tiempoCrianza

Añada añocalidad

{una añada por año: cada vino de crianza tiene como máximo una añada por año.}

{nombre únicos: los nombre de las bódegas, vinos y uvas son únicos.}

1..*

{ordered}

0..*

compuestoDe 1..*

{completa, disjunta}

1 produce

1..*

(10)

transformation

• Example of transformation

– Generalized use of OIDs.

– Semantics key as alternate keys.

– A single table with discriminant.*

november 2013

Introduction to Software Engineering and Information Systems

18

Relaciones PK AK FK

Bodegas (

OID_B, nombre, denominaciónOrigen) OID_B nombre Vinos (

OID_V, nombre, grados, clase,

tiempoCrianza, OID_B) OID_V nombre OID_B /Bodegas Uvas (

OID_U, nombre) OID_U Nombre

CompuestoDe(

OID_CD, OID_V, OID_U) OID_CD (OID_V, OID_U) OID_V / Vinos OID_U / Uvas Añadas (

OID_A, OID_V, año, calidad)

El orden depende del año OID_A (OID_V, año) OID_V / Vinos

* The hierarchy has just two clases with a few attributes.

transformation

Toro, 2011

Transformation from Conceptual Models to Relational Models

Transformation from Conceptual Models to

Relational Models

Grupo de Ingeniería del Software y Bases de Datos Departamento de Lenguajes y Sistemas Informáticos

Universidad de Sevilla november 2013

Transformation from CM to RM

• Learning objectives

– Know the rules to transform conceptual models into relational models.

– Know the three classifications transformation strategies, their advantages and disadvantages.

– Be able to apply the transformation algorithm to a given conceptual model.

Introduction to Software Engineering and Information Systems

• Model-Driven Development(MDD)

– Development approach in which products are generated by transforming models until obtaining source code.

– In the development of information systems with relational databases, conceptual models can be transformed into a relational model that can be then used to generate SQL code.

Introduction to Software Engineering and Information Systems

Modelo relacional

Modelo conceptual

Código SQL

Transformation from CM to RM

• Transformation of entities

– General rule:

• Each entity is transformed into a relation with the same name (usually in plural).

• Each attribute e of the entity is transformed into an attribute r of the relation with the same name.

– Pay attention to:

• Define domains for attribute r depending on the semantics of the corresponding attribute e . *

• Add an OID as primary key (PK), unless there are simple semantic keys.

• Add an alternate key (AK) for each

semantic key that has not been

used as OID.

• Transformation of entities

– Object Identifiers(OID)

• These are artificial attributes* used to identify objects without relying on semantic keys.

• The generation of their values is usually delegated to the DBMS (autoincrement, sequences, etc..).

– Semantic keys

• They are attributes or sets of attributes that are key to the problem domain.

• Sometimes, they can be used as OID because they really are so in the problem domain: ID, SSN, product code, etc..

Introduction to Software Engineering and Information Systems

Transformation from CM to RM

• Transformation of entities

– Example:

Introduction to Software Engineering and Information Systems

OID_D denominación presupuesto ciudad 1 Historia 20.000€ Sevilla

2 Arte 5.000€ Sevilla

3 Dibujo 5.500€ Cádiz

.. … … …

Departamentos

Departamentos(OID_D, denominación, presupuesto, ciudad) PK(OID_D), AK(denominación)

• Transformation of associations

• General rule:

• Associations 1: n are represented by a foreign key in the relation of the entity with role n.

• 1:1 associations are represented by a foreign key in any of the relations.

• Associations m: n are represented using an auxiliary relation with foreign keys to both relations.

– Pay attention to:

• If a role is {ordered}, we must add an attribute (if not already) that specifies the order in the relation including the foreign key.

Introduction to Software Engineering and Information Systems

Transformation from CM to RM

• Transformation of associations

– Example(1:n)

nif nombre Apellidos OID_D 123… Luis Gómez Sanz 1 456… Ana García Díaz 3 789… Isabel Bernal Sanz 3 Empleados

Departamentos(OID_D, denominación, presupuesto, ciudad) PK(OID_D), AK(denominación)

Empleados(nif, nombre, apellidos, OID_D) PK(nif), FK(OID_D) / Departamentos

OID_D denominación presupuesto …

1 Historia 20.000€ …

2 Arte 5.000€ …

3 Dibujo 5.500€ …

Departamentos

• Transformation of associations

– Example (1:n ordered)

– Compositions are treated like any other association 1:n *.

Introduction to Software Engineering and Information Systems

Facturas(número, fecha) PK(número)

LíneaFactura(OID_LF, cantidad, precio, factura, orden) PK(OID_LF), AK(factura, orden)

FK(factura) / Facturas

Transformation from CM to RM

• Transformation of associations

– Example(1:1)

– It is a particular case of the 1: n.

– Placing the foreign key role in the relation with optional rol we can avoid store null values.

Introduction to Software Engineering and Information Systems

Empleados(nif, nombre, apellidos) PK(nif)

• Each attribute _e of the entity is transformed into an attribute _r of the relation with the same name.

• Define domains for attribute _r depending on the semantics of the corresponding attribute _e . *