2. OCL2 to SQL transformation - Some Background Information

Navigation: Introduction - 1. The university model - 2. Transformation rules - 3. The animation

 

2 OCL2 to SQL transormation

This part explains which transformation rules the tool OCL22SQL uses, that is, how classes of the UML model can be mapped to SQL tables and how the contraints are transformed into SQL statements.

It will use the university model which was explained in the first part (open the model and its OCL rules in a new window, open the window withouth JavaScript).

The chapter is divided into the following parts:

2.1 Class Tables

2.1.1 Typed transformation
2.1.2 Vertical transformation

2.2 Class views

2.3 Transformation of OCL constraints

2.3.1 The VIEW approach

2.4 Summary

 

2.1 Class Tables back to top

An interesting question during transformation of an UML/OCL model into a database is how to transform the classes into tables of the database schema. Each class should be represented in a table in the database.

Below you can see the university example which was explained in part 1 of the tutorial.

The class Grade will be transformed into the following SQL-Code:

CREATE TABLE T_Grade (
    PK_Grade VARCHAR(255) PRIMARY KEY,
    FK_person VARCHAR(255),
    value INT,
    name VARCHAR(255)
);

As you can see, each attribute of the class Grade is transformed into one column of the table. The table also contains the primary key PK_Grade that is needed to distinguish Grades which have the same values in all attributes, that is, they have the same name and the same value. Additionally, there is a column FK_Person which is a foreign key reference to a Person related to the specific grade.

Until now, transformation from UML/OCL to SQL seems to be very easy. But what happens if a class has defined subclasses? Should subclasses be transformed into the same table as their root class or should they be represented in extra tables? The OCL22SQL tool supports two different transformation strategies for subclasses: typed transformation and vertical transformation.

 

2.1.1 Typed transformation back to top

The typed transformation transforms a class and their subclasses into only one table. This operation will be explained using the class Person.

As you can see in the university model, the class Person has the subclasses Student, Employee and PhDStudent. Typed transformation generates one table for all these classes. The specific SQL statement to create these table is:

CREATE TABLE T_Person (
    PK_Person VARCHAR(255) PRIMARY KEY ,
    FK_grade VARCHAR(255),
    FK_supervisor VARCHAR(255),
    dissSubject VARCHAR(255),
    firstName VARCHAR(255),
    lastName VARCHAR(255),
    isMarried BOOL,
    birthDate DATE,
    type VARCHAR(255),
    soSecNr VARCHAR(255),
    matDate DATE,
    matNr INT,
    wage FLOAT,
    taxClass VARCHAR(255),
    age INT
);

It is obvious that the table contains all attributes of the classes Person, Student, Employee and PhDStudent. This causes some advantages and disadvantages:

 

2.1.2 Vertical transformation back to top

The vertical transformation puts every class and every subclass into its own table. Again, the class Person from the university model and its subclasses are used to explain the transformation. The transformation of these classes results into the following SQL queries:

CREATE TABLE T_Person (
    PK_Person VARCHAR(255) PRIMARY KEY,
    FK_grade VARCHAR(255),
    FK_supervisor VARCHAR(255),
    age INT,
    firstName VARCHAR(255),
    birthDate DATE,
    isMarried BOOL,
    lastName VARCHAR(255)
);

CREATE TABLE T_Employee (
    PK_Person VARCHAR(255) PRIMARY KEY,
    taxClass VARCHAR(255),
    wage FLOAT,
    soSecNr VARCHAR(255)
);

CREATE TABLE T_PhDStudent (
    PK_Person VARCHAR(255) PRIMARY KEY,
    dissSubject VARCHAR(255)
);

CREATE TABLE T_Student (
    PK_Person VARCHAR(255) PRIMARY KEY,
    matDate DATE,
    matNr INT
);

As explained before, each class is transformed into its own table which contains a column for each attribute of the class. In the example the table of the class Person contains its primary key PK_Person and foreign keys to represent relations with other classes. Like the superclass, the subclasses get a primary key column named PK_Person. This key is also used as a foreign key to the table T_Person, where the classes have (some of) their attributes which were defined in the root class Person.

For example the attribute age of each Student can be found in the Table T_Person using a join between T_Student.PK_Person and T_Person.PK_Person.

Vertical transformation causes advantages and disadvantages, too:

 

2.2 Class views back to top

As seen in part 2.1, the OCL22SQL tool supports two strategies to transform classes from the model to tables in the database. This causes different queries to get the data for a specific class instance.

Using typed transformation, all attributes of the class are in one table. To get the attributes of a Student called 'Martin Mustermann' you have to use the following SQL query:

SELECT firstName, lastName, birthDate, age, isMarried, matNr, matDate
FROM T_Student
WHERE firstName = 'Martin'
    AND
lastName = 'Mustermann'
;

Using vertical transformation, the query gets more complicated:

SELECT firstName, lastName, birthDate, age, isMarried, matNr, matDate
FROM T_Student AS stud, T_Person AS pers
WHERE stud.PK_Person = pers.PK_Person
    AND firstName = 'Martin'
    
AND lastName = 'Mustermann';

Different mapping strategies lead to different queries to get the same data.

To solve this problem, the OCL22SQL tool also generates views for each class in the UML/OCL model. For example, using vertical transformation, the tool generates the following query to create a view for the class Student:

CREATE VIEW OV_Student
AS (
    SELECT
        T_Student.PK_Person,
        T_Person.age as age,
        T_Person.birthDate as birthDate,
        T_Person.firstName as firstName,
        T_Person.isMarried as isMarried,
        T_Person.lastName as lastName,
        T_Student.matDate as matDate,
        T_Student.matNr as matNr,
        T_Person.FK_grade as FK_grade,
        T_Person.FK_supervisor as FK_supervisor
    FROM T_Student,T_Person
    WHERE T_Student.PK_Person=T_Person.PK_Person
);

Using typed transformation would generate a view which contains the same columns as above (but which would be generated by a different select query without a join).

With the created views in the database - containing all attributes of a class -, the query to get all attributes of the Student 'Martin Mustermann' will be the same for both transformation strategies:

SELECT firstName, lastName, birthDate, age, isMarried, matNr, matDate
FROM OV_Student
WHERE firstName = 'Martin' AND lastName = 'Mustermann'
;

This is a big advantage: Using views to represent the different classes, a user does not need to know which transformation strategy was used to create the tables. He simply uses the views to get data from the database.

This is also important during the generation of OCL constraints.

 

2.3 Transformation of OCL constraints back to top

Using OCL as a language for business rule specification, extended investigations can be carried out, which aims at maintaining the software integrity by checking business rules during the execution of a business process. This check can be done by two basic strategies:

2.3.1 The VIEW approach back to top

Driven by our implementation experience and motivated by database literature, the OCL22SQL tool propose an approach to realise either an independent or an immediate constraint check for an SQL based implementation of OCL specified business rules. The basic element of its approach are SQL views generated from OCL invariants. Each single OCL invariant is translated into a separate view definition.

For example, the constraint

/*
* The academic grade of a students supervisor must be greater
* than the academic grade of the supervised person.
*
* Used patterns: BASIC TYPE, NAVIGATION, CLASS AND ATTRIBUTE
*/

context Student
inv tudOclInv2: self.supervisor.grade.value > self.grade.value

is transformed into the following SQL query:

create or replace view tudOclInv2
as (
    select *
    from OV_Student as ALIAS2
    where not (
        (
            (
                select value
                from OV_Grade as foo
                where PK_Grade in (
                    select FK_grade
                    from OV_Person as foo
                    where PK_Person in (
                        select FK_supervisor
                        from OV_Student as foo
                        where PK_Person = ALIAS2.PK_Person
                    )
                )
            )
        >
            (
                select value
                from OV_Grade as foo
                where PK_Grade in (
                    select FK_grade
                    from OV_Person as foo
                    where PK_Person = ALIAS2.PK_Person
                )
            )
        )
    )
)


As you can see, the SQL query for a simple OCL constraint gets much more complicated than the transformation of a class into a table.

The approach to transform OCL constraints into views yields a number of advantages:

 

2.4 Summary back to top

You have learned how an UML/OCL model can be transformed into a SQL database schema.The third and last step of the tutorial will demonstrate how to use the OCL22SQL tool.

 

 

Navigation: Introduction - 1. The university model - 2. Transformation rules - 3. The animation