Summary: Making an SQL92 table model

1. For each design model class representing an entity type, create a corresponding data model class representing an entity table:

   • Categorize the class with the stereotype «entity table» and apply the naming convention of lower-case plural table names. So, for instance, the design class name Book is converted to the «entity table» class name books.

   • Replace the UML standard identifier designation {id} with the primary key stereotype «pk».

   • Replace the platform-independent datatype names (such as String, Integer, Decimal, Date and Boolean) with corresponding SQL92 datatype names.

   This results in a model like the following one:

   

   Since SQL92 does not support the datatype Boolean, this requires using a workaround for it, such as CHAR(1) restricted to the two values ‘y’ and ‘n’.

2. Eliminate multi-valued attributes. In general, a multi-valued attribute m in a class C can be transformed into a table m having a single column with the same name and type as the attribute m, complemented by a foreign key attribute referencing C, as shown in the following diagram where company is the foreign key attribute referencing the companies table:

   

   Notice how a foreign key attribute is graphically expressed with a UML dependency arrow stereotyped «fk» with the attribute's name shown at its source end.

   In simple cases, multi-valued attributes may be represented by a string-valued attribute. This requires decomposing the string representing a set into its elements by using appropriate string manipulation operators. It only works in simple cases.

3. Turn any enumeration datatype into a corresponding «enumeration table» class and define suitable foreign keys referencing it in all «entity table» classes where the enumeration datatype has been used. For each enumeration literal from the enumeration datatype, insert a corresponding row in the enumeration table.⁽¹⁾

   

   If the target DBMS is MySQL, then the effort of introducing a special table and a foreign key for implementing an enumeration datatype can be avoided. Instead, MySQL's non-standard enum column type can be used in the following way:

   

4. For eliminating associations, we need to consider two cases:

   1. A functional association, mapping instances of its domain class to instances of its range class, is eliminated by

      1. Adding a suitable foreign key attribute (here, publisher) to the entity table class that corresponds to the domain class of the functional association (books).

      2. Adding a foreign key dependency arrow stereotyped with «fk», annotated with the name of the foreign key attribute (publisher) at its source end and pointing to the class at the functional association end (publishers).

      

   2. A many-to-many association is turned into a relationship table class with two foreign key attributes (here, authored_book and author), one for each of the two involved entity classes, and an attached integrity constraint (invariant) requiring the combination of both foreign key attributes to be unique.

      

5. Eliminate generalization relationships in the following way:

   1. Copy the standard identifier attribute(s) of the superclass to the subclass.

   2. Replace the generalization arrow with a corresponding foreign key dependency arrow from the subtable to the supertable such that the primary key attribute(s) of the subtable are foreign key attribute(s).

   

6. Define an index for any attribute that serves as a search field to speed up the execution of corresponding queries. This may be done with the help of the attribute stereotype «index».⁽²⁾

For performance reasons, to speed up the execution of certain queries and certain change operations, certain tables may have to be splitted into two or more parts (vertically or horizontally), which may be done in the implementation model. But we will not consider this issue here.

Finally, the resulting SQL-92 table model, which contains only table classes, foreign key dependency arrows and constraints, can be easily coded with SQL CREATE TABLE statements, as explained in the next section.