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:
- Each subclass has its own table. Therefore, a specific subclass has no NULL values of attributes that are defined by another subclass.
- To query all attributes of a subclass an expensive join is needed.
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:
-
The theoretically best way is the immediate constraint check: The OCL
expressions are immediately evaluated when objects are changed. In databases,
the unit of consistency is the transaction. In object-oriented applications,
check points are more difficult to find because most runtime systems like
the Java Virtual Machine do not provide transaction support.
-
A more realistic way in many cases is an independent constraint check:
The OCL expression is only checked at selected points to achieve a trade-off
between consistency maintenance and efficiency. As a consequence, inconsistent
states of objects respectively of the database are possible.
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: