This section presents how to transform a conceptual model into a logical model in a consistent way. There are two categories of transformation rules: general rules [3], [5], [8], [9], and specific rules. General rules take into account classes, two relationships (i.e. associations, and generalizations), and properties of these relationships, while specific rules take into account also properties of related classes.
The general rules of transformation of conceptual model into a logical model are as follows:
G.1 A class from a conceptual model is transformed into a table.
G.2 A class attribute is transformed into a column in the corresponding table provided that there exists a mapping from attribute type into a column type of the table.
G.3 The table representing a class is assigned a primary key selected from a set of candidate keys. The candidate keys not chosen as primary ones are considered as alternative keys, and must fulfill the table constraint UNIQUE.
G.4 A binary association of the many–to–many multiplicity type is transformed into a table. The primary key of the table consists of two foreign keys which are primary keys of tables representing associated classes.
G.5 A generalization is represented by a foreign key of the table representing subclass. This foreign key is also a primary key. Its value indicates uniquely a row in the table representing the superclass.
We consider the transformation rules for binary association of the many–to–many multiplicity type. Figure 4 illustrates an application of rules G.1–G.4.
Figure 4. Transformation rules for binary, many–to–many association
In the example, we assume that the attributes a1 and b1 are candidate keys of classes A and B, respectively. The association is transformed into the table AB. The table is related to tables A and B by foreign keys. The result of the transformation — the logical model — is presented in the Rational Rose profile notation [16]. Note, that the notation used by Rational Profile for relationships between tables is not consistent with UML. The meaning of the filled diamond in the Profile is not consistent with composition relationship.
Alternative presentation form of the logical model in SQL notation is shown below:
CREATE TABLE AB ( a1 SMALLINT NOT NULL, b1 SMALLINT NOT NULL,
CONSTRAINT PK_AB PRIMARY KEY (a1, b1) );
CREATE TABLE B ( b1 SMALLINT NOT NULL, b2 SMALLINT NOT NULL,
CONSTRAINT PK_B PRIMARY KEY (b1) );
CREATE TABLE A ( a1 SMALLINT NOT NULL, a2 SMALLINT NOT NULL,
CONSTRAINT PK_A PRIMARY KEY (a1) );
ALTER TABLE AB ADD CONSTRAINT FK_A_A1 FOREIGN KEY (a1) REFERENCES A (a1); ALTER TABLE AB ADD CONSTRAINT FK_B_B1 FOREIGN KEY (b1) REFERENCES B (b1);
The second example illustrates the application of the rule G.5 related to the trans- formation of generalization — see Figure 5.
Figure 5. Application of G.5 rule for model transformation
CREATE TABLE A ( a1 INTEGER NOT NULL, a2 INTEGER NOT NULL,
CONSTRAINT PK_A PRIMARY KEY (a1) );
CREATE TABLE B ( a1 INTEGER NOT NULL, b1 INTEGER NOT NULL, b2 INTEGER NOT NULL,
CONSTRAINT PK_B PRIMARY KEY (a1) );
ALTER TABLE B ADD CONSTRAINT FK_A_A1 FOREIGN KEY (a1) REFERENCES A (a1); ALTER TABLE B ADD CONSTRAINT TC_B1 UNIQUE (b1);
Now, let us consider a new rule resulting from the {bag} as a new property of an association end. The respective rule is a modification of G.4 rule:
S.1 A binary association of the many–to–many multiplicity type is transformed into a table. The primary key of the table is an artificial key. The primary keys of the tables representing associated classes are foreign keys in the table representing the association.
Figure 6 illustrates the application of the S.1 rule.
Figure 6. Multiset specification and their representation at logical level
The presence of {bag} at the association ends allows for duplicates of links between the same pair of objects. The links are not discriminated by primary keys of tables representing associated classes. Therefore, an artificial key is necessary to represent these links. The logical model in textual form is presented in the SQL listing below:
CREATE TABLE AB ( b1 SMALLINT NOT NULL, a1 SMALLINT NOT NULL, ak SMALLINT NOT NULL,
CONSTRAINT PK_AK PRIMARY KEY (ak) );
ALTER TABLE AB ADD CONSTRAINT FK_A1 FOREIGN KEY (b1) REFERENCES B (b1); ALTER TABLE AB ADD CONSTRAINT FK_A2 FOREIGN KEY (a1) REFERENCES A (a1);
When the multiplicity of at least one association end is m..n, where m > 0, and n is limited, and n > 1, then an additional check of consistency should be performed, for example, by a trigger.
Next specific rules are concerned with generalization. The following assumptions are anticipated. We do not consider generalizations with multi-inheritance. Transformation of a generalization set depends on the generalization set properties. The interesting ones are: (a) {complete}, (b) {incomplete, overlapping}, (c) {incomplete, disjoint}, (d) {complete, overlapping}, (e) {complete, disjoint}.
S.2 A superclass with only one subclass and the property {complete} is represented by one table. Columns of the table represent attributes of the super- and subclass.
S.3 A superclass with many subclasses and the property {overlapping} is realized according to the G.5 rule separately for each subclass. All tables representing subclasses are expected to have the same primary key. (In general, primary keys in tables representing different subclasses may have the same values.)
S.4 A superclass with many subclasses and the property {incomplete, disjoint} is transformed according to the S.3 rule with the additional constraint that the set of values of primary keys of tables representing different subclasses must be disjoint.
S.5 Transformation of a superclass with many subclasses and the property
{complete, disjoint} depends on that if the super class is associated with other classes.
(a) if the superclass is not associated with other classes then the S.2 rule is applied, with the additional constraint that the set of values of primary keys of tables representing different subclasses must be disjoint;
(b) otherwise, the S.4 rule is applied, with the additional constraint that each row in the table representing the superclass is referenced exactly by one foreign key of one of the tables representing subclass.
Transformation rules for generalization are explained by a simple example of the conceptual model, presented in Figure 7.
Figure 7. The conceptual model with generalization relationship between many subclasses
Figure 8 shows an example of a logical model resulting from applying the S.5a rule to the conceptual model presented in Figure 7.
The logical model is listed in SQL below:
CREATE TABLE B ( a1 SMALLINT NOT NULL, a2 SMALLINT NOT NULL, b1 SAMLLINT NOT NULL, b2 SMALLINT NOT NULL,
CONSTRAINT PK_B PRIMARY KEY (a1), CONSTRAINT TC_B UNIQUE (b1) );
CREATE TABLE C ( a1 SMALLINT NOT NULL, a2 SMALLINT NOT NULL, c1 INTEGER NOT NULL, c2 INTEGER NOT NULL,
CONSTRAINT PK_C PRIMARY KEY (a1) CONSTRAINT TC_C UNIQUE (c1), );
The last specific rule relates to powertypes. We assume that names of generalization sets in a given conceptual model are unique, and at least one powertype is defined.
Figure 8. The logical model resulting from applying the S.5a rule to the conceptual model presented in Figure 7.
S.6 Powertypes in a conceptual model are represented by two tables in a logical model – see Figure 9.
(a) The first table, called PowerTypeFamily, declares existence of powertypes and the respective superclasses. It contains two columns: powertype name (the primary key), and the name of related superclass.
(b) The second table, called PowerTypeInstances, defines a set of instances of each powertype. It contains two columns: powertype name, and the names of subclasses – instances of the powertype. The primary key of the table is composed of these two names.
(c) Optionally, when a given powertype has own properties, a new table for a such powertype should be defined. It is suggested that the name of the table be the name of the powertype, and the schema of the table reflects properties of this powertype. The primary key of this table is the attribute, whose values are all names of the powertype’s instances.
The logical model in textual form is presented in SQL listing below:
CREATE TABLE PowerTypeFamily (
PowerTypeName VARCHAR(128) PRIMARY KEY, GenName VARCHAR(128) NOT NULL,
);
CREATE TABLE PowerTypeInstances ( SpecName VARCHAR(128),
PowerTypeName VARCHAR(128) REFERENCES PowerTypeFamily,
CONSTRAINT PK_PowerTypeName_SpecName PRIMARY KEY (PowerTypeName, SpecName)
);
CREATE TABLE PowerType (
SpecName VARCHAR(128) PRIMARY KEY, prop1 ...,
prop2 ..., );
4. Conclusions
The paper presents the new features of UML 2.0 that seem to be interesting in data modeling. These features allow conceptual models to be more precise, and adequate to an application domain.
In database modeling, conceptual models are transformed into logical ones. The transformation is based on the set of commonly known rules [3], [5], [8], [9]. Extension of the UML entails the need of revision of existing transformation rules and elaboration of some new ones.
The paper presents two groups of transformation rules. The first group contains general, commonly used rules dealing with transformation of conceptual models written in UML 1.x [3], [5], [8]. The second group gathers specific rules that can be applied to new features of the UML 2.0.
We have limited our considerations to binary associations, and to generalizations with single inheritance which are the most frequently used in practice. We have not taken into account abstract classes, but transformation rules for generalization with {complete} property can be adopted for them.
In the paper we used two different notations for logical model presentation: Rational Rose Database profile, and SQL99 standard.
The application of the proposed transformation rules to many examples has convinced us that they preserve information contained in conceptual model.
The proposed transformation rules can be easily implemented in existing CASE tools.
The UML 2.0 specification document [15] has not been formally approved. While analyzing it, along with publications presenting UML 2.0, e.g. [11] some interpretation problems of UML 2.0 features have been encountered. For example, the problem is how to interpret multiplicity specified at the end of association labelled by bag property. It is not clearly defined whether the multiplicity is interpreted either as the limit of associated objects or as the limit of objects’ references. Depending on the choosen interpretation two different logical models can be obtained.
The other problem is related to the multiplicity of the association end at the power type class in the case when the respective power is set with {incomplete} constraint, see Figure 3. Such multiplicity should be set as 0..1, while in the examples presented in B. Hnatkowska et al. / Data Modeling with UML 2.0 73
[11], and what is surprising in the UML specification [15], the multiplicity is set to 1. This is not correct because there may exist some objects of the respective superclass that are not members of its subclasses.
References
[1] Ambler, Scott W.: Agile Database Techniques. Effective Strategies for the Agile Software Developer.,
John Wiley&Sons, 2004.
[2] ANSI/ISO/IEC International Standards (IS).: Database Language SQL — Part 2: Foundation (SQL/Foundation), 1999, Available at: http://www.cse.iitb.ac.in/dbms/Data/Papers- Other/SQL1999/ansi-iso-9075-2-1999.pdf
[3] Blaha M., Rumbaugh J.: Object-Oriented Modeling and Design with UML, Second Edition, Pearson
Education, 2005.
[4] Booch G., Rumbaugh J., Jacobson L.: The Unified Modeling Language User Guide. Addison-Wesley, 1999 (táum. pol., UML podrĊcznik uĪytkownika, WNT, 2002).
[5] Connolly T., Begg C.: Datbase Systems, A practical Approach to Design, Implementation, and Management. Addison-Wesley, Fourth Edition, 2005.
[6] Date C., Darwen H.: Omówienie standardu jĊzyka SQL, WNT, Warszawa 2000.
[7] Krutchen P.: Rational Unified Process. An Introduction. Addison Wesley Longman Inc., 1999. [8] Maciaszek L., Liong B.: Practical Software Engineering. A Case Study Approach, Pearson Addison-
Wesley, 2005.
[9] Maciaszek L.: Requirements Analysis and System Design. Addison Wesley. Second Edition. 2005. [10] Martin, J., Odell James J.: Podstawy metod obiektowych., WNT, Warszawa, 1997.
[11] Rumbaugh J., Jacobson L., Booch G.: The Unified Modeling Language. Reference Manual, Second Edition, Addison-Wesley, 2005.
[12] Naiburg E. J., Maksimchuk R. A.: UML for Database Design, Addison-Wesley, 2001. [13] Sparks G.: Database Modeling in UML, Methods & Tools, pp.10 – 22, 2001.
[14] Ullman J. D., Widom J.: Podstawowy wykáad z systemów baz danych, WNT, Warszawa 2000. [15] UML 2.0 Superstructure Specification, OMG, September 2003.