Transforming UML Class Diagrams to the Relational Model

by K. Yue

1. Transforming OO Model to the Relational Model

• Once a conceptual OO data model is constructed, it needs to be mapped for implementation in the selected logical database model.
• If relational DB is used, the mapping will be from OO (classes, attributes, associations, etc.) to relational schema (relations, attributes, keys, etc.)
• Note that the relational model and the OO model are very different, even though diagrams representing the two models look similar (there are only finite number of common shapes).
• Computer-Aided Software Engineering (CASE) tools or DB modeling tools may provide varying degrees of facilities for automatic generating relational schema and corresponding SQL statements. We use Astah in this class.
• However, it is important to understand the mechanism a tool uses to generate the relational schema and make adjustment if needed.

Model Transformation

The problem is: Source Model --> Target Model.

• Source Model: UML
  1. Basic elements:
     1. class
     2. Attribute: can be multi-valued.
     3. Association
  2. Secondary elements:
     1. Object
     2. Multiplicity
     3. Data type
     4. Default value
     5. Constraint
     6. Stereotype for RDB extensions: e.g., candidate keys, primary keys, unique, derived, nullability, etc.
     7. ...
• Target Model: Relational Model
  1. Basic elements:
     1. Relation
     2. Attribute (columns/field): should be single-valued (atomic).
  2. Secondary elements:
     1. Row (tuple)
     2. Data type
     3. Nullability
     4. Constraint
     5. Candidate key
     6. Primary key
     7. Foreign key
     8. Index
     9. ...
• There are only two basic elements in the targeted model to consider:
  1. relation:
     1. more flexible,
     2. can hold attributes to store a collection of logically related data
     3. more complex.
  2. attribute:
     1. can only hold single-value (if good design, i.e., the first normal form, is to be assured.)
     2. less complex.
     3. Thus, if attributes are sufficient in model transformation, they are preferred.
• Different RDBMS provide different features.
1. Thus, the targeted RDB model is not universal.
2. It is necessary to define platform-specific transformation rules.

2. Transformation Rules

• We present a set of mapping rules below. It is not meant to be complete or universal.
• As alternative examples of transformation rules:
  • A relative simple one: http://web.fe.up.pt/~ssn/2010/lbaw/slides/lbaw-uml2rel.eng.pdf a
  • A more elaborated one based on agile methodology: http://www.agiledata.org/essays/mappingObjects.html.
• Do not mechanically follow these rules. Instead, understand the rationale behind the rules and adapt.
• All OO model details should be implemented in the targeted model in some ways.
  1. database level: preferred.
  2. middle layer level
  3. application level

2.1 Classes

C1. A class C is mapped or transformed to a relation RC.

1. Relations may later be merged in design refinement and performance tuning.
2. The relation may use the same name as the class.

Rationale:

1. A class is a logical unit for encapsulating related data and a relation has the same property.

2.2 Attributes

Basic:

ATT1. Include all single-valued attributes (with simple data types) of a class C as attributes of RC, the relation for the class C.

ATT2. For each multi-valued attribute A of the class C, create a new relation RCA containing the attribute A and the primary key, RCId, of the relation RC (which implements the class C).

1. (RCId, A) is a composite candidate key.
2. RCId is a foreign key referencing RC(RCId)
3. A surrogate key, such as RCA_Id, may be created to serve as the simple primary key.
4. The name of RCA should be meaningfully selected.

Secondary:

ATT3. If the multiplicity of an attribute is specified, to handle the case of 0:

1. If 0 is allowed in the UML model (e.g. 0..1, 0..* in the UML class diagram), the attribute is nullable. Add the NULL specifier in the column definition in the RDBMS. (NULL is usually the default)
2. If 0 is not allowed, add the NOT NULL specifier in the column definition.

ATT4. The default value of an attribute may be directly implemented in SQL DDL.

ATT5. Data type mapping should be handled effectively and consistently.

1. In later modeling phases, one may use SQL data types of the targeted DBMS in the class diagram.
2. If available, consider using user-defined data types in the targeted DBMS.

Example:

UML for toyu.student:

Relational schema (in HW assignment format):

Relational schema in SQL (with more implementation details):

CREATE TABLE IF NOT EXISTS Student    (
    stuId        INT NOT NULL,
    fname        VARCHAR(30) NOT NULL,
    lname        VARCHAR(30) NOT NULL,
    major        CHAR(4) NULL,
    minor        CHAR(4) NULL,
   -- ach: accumulated credit hours, including transferred credits.
    ach          INTEGER(3) NULL DEFAULT 0,
    advisor      INT NULL,
    CONSTRAINT Student_stuId_pk PRIMARY KEY(stuId),
   -- an artificial example of a CHECK constraint.
    CONSTRAINT Student_ach_cc CHECK ((ach>=0) AND (ach < 250)),
    CONSTRAINT Student_major_fk FOREIGN KEY (major)
        REFERENCES Department(deptCode) ON DELETE CASCADE,
    CONSTRAINT Student_minor_fk FOREIGN KEY (minor)
        REFERENCES Department(deptCode) ON DELETE CASCADE,
    CONSTRAINT Student_advisor_fk FOREIGN KEY (advisor)
        REFERENCES Faculty(facId)
);

Example:

Consider the class Member with the following attributes:

1. Member_Id: <<PK>>
2. Member_Screen]Name <<unique>>
3. Hobby[0..*]
4. Medal[0..*] <<ordered>>

Reasonable relation schema: three relations used

1. Member(MemberId, ScreenName):
   1. CK: [1] MemberId, [2] ScreenName
2. Hobby(HobbyId, MemberId, Hobby):
   1. CK: [1] HobbyId, [2] MemberId, Hobby
   2. FK: [1] MemberId references Member(MemberId)
   3. A surrogate key, HobbyId, is created as the primary key.
3. Medal(MedalId, MemberId, Medal):
   1. CK: [1] MedalId, [2] MemberId, Medal.
   2. FK: [1] MemberId references Member(MemberId)
   3. A surrogate key, MedalId, is created as the primary key.

All columns in the table above are not nullable.

ATT6. A single-valued attribute of a composite data type (such as set, list, array) can be mapped in various ways.

1. If there is an comparable composite data type in the targeted DBMS, it can be implemented as an attribute of that data type in the relation.
   • The relation will no longer be in the first normal form.
   • Care should be taken in handling the difference in data type mapping.
2. Otherwise, regard the attribute as a multi-valued attribute and apply rule ATT2.

ATT7. For a derived attribute A:

1. It can be implemented and stored as an attribute of the relation.
• Mechanism, such as triggers or stored procedures, should be used to ensure data consistency. That is the derived column should be consistent with the data that derives its value.
2. It may not be stored as a column directly in any relation.
   • Mechanism, such as virtual columns, views or stored functions, may be used to provide standard access to the derived attributes.

Example:

A class Rectangle has three attributes:

• Length
• Width
• \Area or <<derived>>: derived.

What may the relational schema look like?

One solution:

Rectangle(RectangleId, Length, Width)

with a view Rect define as

select RectangleId, Length, Width, Length * Width as Area
from Rectangle;

Alternatively, using virtual column: a column that is computed in real-time by an expression and not stored.

CREATE or replace TABLE rectangle (
  width DOUBLE,
  height DOUBLE,
  area DOUBLE AS (width * height) virtual
);

2.3 Keys and Constraints

KC1. If a relation R implements a class C or an association (class) A, and C or A has candidate keys K's, set all K's as candidate keys of R.

KC2. If a relation R implements a class C or an association (class) A, and C or A has no candidate key, create a surrogate primary key SK for R.

1. This is needed as every relation must have at least one candidate key.

KC3. All candidate keys can be implemented by using the 'unique' and non-null constraint in SQL.

KC4. If many CK but no primary key are specified, select a candidate key as the primary key and set it accordingly in the relation.

Example:

for the class Department in toyu:

Relational schema (in HW assignment format):

Relational schema in SQL (with more implementation details):

CREATE TABLE IF NOT EXISTS Department (
    deptCode    CHAR(4) NOT NULL,
    deptName    VARCHAR(30) NOT NULL,
    schoolCode  CHAR(3) NULL,
    numStaff  TINYINT NULL,
    CONSTRAINT Department_deptCode_pk PRIMARY KEY (deptCode),
   -- alternate keys: [1] deptName  
    CONSTRAINT Department_name_ck UNIQUE (deptName),
    CONSTRAINT Department_schoolCode_fk FOREIGN KEY (schoolCode)
        REFERENCES School(schoolCode)
);

KC5. For a stereotype:

1. Some may be directly implemented in SQL DDL, e.g. PK, CK, unique, etc.
2. Otherwise, it is necessary to consider where and how it is implemented.

2.4 Associations

A1. For a many to one association between C1 (the class with the one multiplicity) and Cm, add a column R1_Id into the relation Rm (which implements Cm).

1. Assume that R1_Id is the primary key of the relation R1.
2. R1_Id is a foreign key of Rm referencing R1(R1_Id).
3. The name R1_Id may be renamed.
4. R1_Id is not null in Rm iff (if and only if) 0 is not allowed (i.e. 1..1) for C1.
5. Any single valued attribute of the association is mapped to a column in Rm.
6. If you have composite or multi-valued attributes of the relationship, you should consider promoting the association to an association class or a class in your UML class diagram.

Example:

For:

We have the three numbered associations implemented by the three foreign keys below.

A2. For a many to many association (including association classes) between classes CA and CB, create a new relation RAB(RA_Id, RB_Id).

1. (RA_Id, RB_Id) is a candidate key.
2. RA_Id references RA(RA_Id) as a foreign key.
3. RB_Id references RB(RB_Id) as a foreign key.
4. An additional surrogate key, such as RAB_Id, can be created.

Example:

For:

We have:

A3. For a one to one association between classes CA and CB, there are several options:

1. Treat CA as C1 and CB as Cm and apply A1.
2. Treat CA as Cm and CB as C1 and apply A1.
3. Merge the two relations RA and RB into one. (In this case, you may want to refactor the class diagram.)

A4. For any n-ary association (n>2), a new relation is needed.

1. You should consider using binary associations instead.
2. A ternary association can be modeled as a class with three binary associations with the participating classes in the ternary association.

Example:

Consider the ternary association between the classes Supplier, Part, and Warehouse with an association attribute quantity.

It can be replaced by a new class and three binary association.

Supply(SupplyId, SupplierId, PartId, WarehouseId, Quantity):

1. CK: [1] SupplyId, [2] SupplierId, PartId, WarehouseId
   
2. FK: [1] SupplierId references Supplier(SupplierId), [2] PartId references Part(PartId), [3] WarehouseId references Warehouse(WarehouseId).

Example:

Toyu UML diagram:

toyu schema: toyu_schema.pdf

toyu SQL: createtoyu.sql.txt