Navigation: Introduction - 1. The university model - 2. Transformation rules - 3. The animation
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:
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:
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.
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:
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