Part 2: Transforming the Logical Model into Database Physical Models
In this section, you take the logical class model you previously defined and transform it to an offline database model. You see what basic transformations are created. You then see what basic transformations are created, then, you create UML stereotypes to define customized transformation rules that you want to apply to your physical model.
show more or lessRead more...

Knowing how the transformer works, and how to modify its behavior, you can create stereotypes to define your own transformation rules.

Step 1: Transform the Class Model into a Database Model

Before transforming the logical model, you create a specific project for your physical model, then you focus on some properties that might be interesting to observe on how the transformer behaves. From a database designer point of view, you should notice that in the class model, some names contain spaces, some class have no primary keys defined. Some have a generalization class, and there are many to many association and names defined on both side of associations. To create the first cut database diagram, perform the following steps:
  1. Click the Application icon and select New Project from the context menu to open the New Gallery. (alternatively, select File | New from the main menu. )

    New project option
  2. In the New Gallery select the Database Project option.

    The New Gallery

    Click OK.

  3. In the Create Database Project dialog, type PhysicalModel as the Project name

    The Create Database Project dialog

    Click Finish.

  4. The Application Navigator should look like the following:

    The Application Navigator
  5. Double click the PhysicalModel node to open the project properties dialog.

    The Project Properties
  6. In the Project Properties dialog, select the Dependencies node in the left pane and click the Edit button Edit button.

    The Project Properties
    Creating dependencies between projects. Show more or lessRead more...

    Complex applications generally comprise multiple projects, which may be related though dependencies. That is, project A (Physical model) must depend on project B (Logical model) when project A uses classes or resources from project B. When this dependency is set, compiling project A will automatically compile project B.

  7. In the Edit Properties dialog, expand the LogicalModel node and check the Build Output checkbox.

    Project Dependencies

    Click OK. Click OK again.

  8. Right-click the PhysicalModel node and select New from context.

    PhysicalModel project in the Application Navigator
  9. In the New Gallery, select the Database Tier | Offline Database Objects and select Offline Database.

    The New Gallery

    Click OK.

  10. In the Create Offline Database dialog type basic as the name.

    The Create Offline Database dialog

    Click OK.

  11. Click the Save All button Save All icon to save your work.

  12. Having the LogicalDatabase class diagram selected, select Edit --> Select All from the main menu to select all classes in the diagram.

    Main menu options
  13. Right-click within any class and select Transform --> Model Only from the context menu.

    Context menu
  14. In the Transform dialog, select UML to Offline Database Objects. If we had opened JDeveloper with another role, than the Database Edition role, we would have other options to choose from.

    Transform dialog

    Click OK.

    Offline database is a technology in JDeveloper that allows you to create and edit database object definitions within a project. Show more or lessRead more...

    Definitions are saved as .xml files, using the same editors that are used to create and edit database objects on live database connections.

    You can create new offline database objects, or import them from a connection to a live database. After you have finished working with them, you can generate DDL that can be used to create and update database definitions in online database schemas.

  15. In the Offline Database Objects from UML Class Model dialog, select the PhysicalModel project from the list, and select the Schema Based on the Name of Owning Package option.

    The Offline Database Objects

    Click Next.

    Notice that the default Offline Database is the one that you created just before. Show more or lessRead more...

    By default the Database to Emulate is set to Oracle11g Database Release 2. You can specify the type of database that the offline database emulates, thereby determining the kinds of offline database object and the data types that will be supported in the offline database.

  16. In the Set Naming Options step, check the following options:
    - Capitalize the UML name
    - Insert underscores between lower and uppercase letters
    - Attempt to pluralize table names

    The Offline Database Objects dialog

    Click Next

  17. Select the Transform root classes option, and check Transform many-to-many associations.

    The Offline Database Objects dialog

    Click Next.

  18. Expand all components that the wizard is prepared to create. See that column EMPLOYEE_TYPE is added as a discriminator column for handling Part Time and Full Time employee subtypes.

    The Offline Database Objects dialog

    Click Next then Finish.

  19. The transformed objects now appear in the Application Navigator. Notice the name generated for the intersection table (Department_Locationses).

     Application Navigator
  20. Double-click the Department_Locationses node to edit the table properties.

     Edit Table dialog

    Explore the various properties of this table: attributes, primary key and foreign keys. Notice that the intersection table has a primary key based on a created column (DEPARTMENT_LOCATIONS_ID) and has also 2 foreign keys one for department_id and the other for location_id.
    Click OK.

  21. In the Application Navigator, double-click the Employees table to edit it. Notice the comment created for the Employees_type column indicating that this column is the discriminator column for making distinction between Full Time and Part Time employees.

    Edit Table dialog
  22. Explore the various other properties created for this table.

    Edit Table dialog
  23. Notice that a Primary Key column EMPLOYEE_ID has been created.

    Edit Table dialog
  24. Examine the foreign key names and how they are made.

    Edit Table dialog

    Click OK.

  25. Click the Save All button Save All icon to save your work.

Step 2: Transform Logical Design Using UML Stereotypes

The way the transformer behaves may not fully satisfy your expectations and needs. You want to have more control on the rules that apply to the transformation of classes into tables. For example, you want the Employee table to be named Emp, the column empno to be used as the primary key, the Foreign Key columns to be named using the association name, and the intersection tables to be named differently than this attempt of pluralization.

  1. In the Application Navigator, right-click the package.uml_pck node and select Properties from context.

    The Application Navigator
  2. In the Package properties dialog, select the Profile Application node and click the Add button Add button.

    The Package properties dialog
  3. In the Properties pane, select DatabaseProfile from the list.

    The Package properties dialog
  4. Select the Applied Stereotype node and click the Add button Add icon. In the Properties pane, Type SCOTT as the name to give as the database name.

    The Package properties dialog

    Click OK.

  5. A new entry is now created in the Application Navigator named DatabaseProfile.uml_pa. Right-click and select Properties from dialog.

    The Application Navigator
  6. The properties dialog shows what database profile is going to be used from now on.

    The properties dialog

    Click OK.

  7. Right-click Employee.uml_cla and select Properties from context. In the Property dialog select Applied Stereotype and click the Add button Add icon, then select Database Class from the list.

    The properties dialog
  8. Enter EMP in the value field. This is the name you want to be generated as the table name for the employee class.

    The properties dialog
  9. Expand the Owned Attribute | empno node and select Applied Stereotype, then click the Add button Add icon.

    The properties dialog
  10. Enter the following property values:

    Property Value
    Datatype NUMBER
    Datatype (Oracle) NUMBER(8,0)
    Datatype (SQL Lite) INT
    Is Primary Key? (checked)
    The attribute properties dialog
    Notice all the different databases you can emulate. Show more or lessRead more...

    Make sure that the properties you specify for a specific database are compatible with that database type. No validation controls is done at this point.

  11. Repeat the operation for the name attribute, entering the following property values:

    Property Value
    Datatype VARCHAR
    Datatype (DB2 Universal Database) VARCHAR(25)
    Datatype (Oracle) VARCHAR2(30)
    Datatype (SQL Lite) CHAR(20)
    The Attribute properties dialog
  12. Repeat the operation for the salary attribute. Enter the following properties:

    Property Value
    Datatype NUMBER(8,2)
    Datatype (Oracle) NUMBER(10,2)
    Datatype (SQL Lite) NUMBER(8,2)
    The Attribute properties dialog

    Click OK.

  13. Right-click emps in dept.uml_cla in the Application Navigator and select Properties from context. In the Properties dialog, select Applied Stereotype and clicking the Add button Add icon, select Database Association.

    The Association properties dialog
  14. From the Naming Rule dropdown list, notice the rule that you can choose from and select Both Tables.

    The Association properties dialog
  15. Expand the Owned End | made up of node, select Applied Stereotype and click the Add button Add icon to add a new rule.

    The Association properties dialog
  16. Type Deptno as the value for the database name field. It is the name you want to be used for the foreign key attribute.

    The Association properties dialog

    Click OK.

  17. Right-click department.uml_cla in the Application Navigator and select Properties from context. In the Class Properties dialog, select Applied Stereotype and clicking the Add button Add icon, select Database Class.

    The Class properties dialog
  18. Type Departments as the Database name for this table.

    The Class properties dialog

    Click OK.

  19. Right-click Location.uml_cla in the Application Navigator and select Properties from context. In the Class Properties dialog, select Applied Stereotype and clicking the Add button Add icon, select Database Class.

    The Class properties dialog
  20. Type Locations as the name to give to the generated table.

    The Class properties dialog

    Click OK.

  21. Right-click within the diagram and select Select All.

    The Context menu
  22. Right-click within the diagram and select Transform --> Model.

    The Context menu
  23. In the Transform dialog, Click OK to accept the default. (UML to offline Database Objects).

    The Transform dialog
  24. In step 2 of the wizard click the New button next to the Offline Database field.

    The Offline Database dialog
  25. In the Create Offline Database dialog, type Oracle11g as the Name and choose Oracle11g Database Release 2 as the database to emulate.

    The Offline Database dialog

    Click OK.

    Oracle 11g Database Release 2 is the default database emulation in this version. Show more or less Read more...

    It is recommended to clearly identify the database emulation you are using for the transformation process.

  26. Back in the main dialog, click Next.

    The Offline Database dialog
  27. On the Set Naming options step, select Capitalize the UML name and check Insert underscores between lower- and upper -case letters.

    The Offline Database dialog

    Click Next.

  28. In the Set Class Options step, choose the following options:

    The Structure pane

    Click Next.

  29. In the Preview step, notice the new names for tables and attributes.

    The Offline Database dialog

    Click Next then Finish.

  30. The Database node Oracle11g is created in the Application Navigator. Expand the nodes and double-click the EMP table name to open the Edit Table dialog.

    The Application Navigator
  31. Review each column characteristics. Example for column Empno:

    The Table properties
  32. Example for column Name:

    The Table properties
  33. Example for column Salary:

    The Table properties
  34. Review the Primary Key:

    The Table properties
  35. Review the Foreign key and notice the new names generated:

    The Table properties

    Click OK.

Step 3: Use Custom Primitive Type
The UML Class modeler allows you to create your own Primitive Types. To create a primitive type and use it for attributes, perform the following steps:
  1. In the Component Palette, drag the Primitive Type icon primitive type icon onto the diagram. Rename it MyCharType.

    The class diagram
  2. The new datatype appears on the diagram.

    A primitive datatype class
  3. In the Application Navigator right-click MyChartType.uml_prt to open the Properties dialog. Select the Applied Stereotype node and click the Add button Add icon.

    Primitive datatype class properties
  4. In the Properties pane, enter the following values:

    Property Value
    Datatype VARCHAR(50)
    Datatype (DB2 Universal Database) CHAR(50)
    Datatype (Oracle) CHAR(50)
    Datatype (SQL Lite) VARCHAR2(50)
    Primitive datatype class properties

    Click OK.

  5. In the Department Class, using 'in place' edit, type MyCharType for the group attribute.

    The Department Class
  6. Double-click the Location class to open its properties. In the properties dialog, select the Owned Attribute | area node in the Structure pane and next to the Type field click the Select element button Select icon.

    The Class Properties dialog
  7. In the Select Element dialog, expand logicalmodel and select MyCharType.

    The Select Element dialog

    Click OK.

  8. Repeat the operation for the country attribute.

    The Select Element dialog

    Click OK then OK again.

  9. Back the the class diagram, double-click the Department class to open it. In the Properties dialog, select Owned Attribute | name and click the Select element button next to the Type.

    The Class properties dialog
  10. In the Select Element dialog, select UML | Database Library | Primitive Types | DatabaseString30.

    The Select Element dialog

    Click OK .

  11. Additional datatypes. Show more or lessRead more...

    JDeveloper provides few additional datatypes that could be useful, and are often used.

    For example a DatabaseBoolean type is available. Also, as its name suggest, the DatabaseString30 is a predefined String of 30 characters. It can be useful for attributes such as names, country, states....

  12. Click the Save All button Save All icon to save your work.

  13. Your diagram should now look like the following:

    The Class diagram
  14. Right-click within the diagram and select Select All.

    Context menu
  15. Right-click in one of the classes and select Transform --> Model only.

    Context menu
  16. In the Transform dialog click OK.

    The Transform dialog
  17. In the Offline Database Objects from UML Class Model, select PhysicalModel for the Project.

    The Offline Database Objects from UML Class Model

    Click Next.

  18. In the Set Naming Option, check that the following options are selected:

    The Offline Database Objects from UML Class Model

    Click Next.

  19. In the Set Class Option, check that the following options are selected: .

    The Offline Database Objects from UML Class Model

    Click Next.

  20. In the Preview step, click Finish.

    The Offline Database Objects from UML Class Model
  21. Repeat the operation to generate an Oracle Lite table structure: In the diagram, having the classes selected, right-click and select Transform --> Model Only.

    The Context menu
  22. In the Transform dialog, click OK.

    The Transform dialog
  23. In the Offline Database Objects from UML Class Model dialog, in the Specify Target step, select the PhysicalModel project, then click the New button next to the Offline Database field.

    The Offline Database Objects from UML Class Model
  24. In the Create Offline Database type OracleLite as the Name and select Oracle10g Lite Release 3 as the Database to emulate.

    The Create Offline Database dialog d

    Click OK.

  25. Back in the Specify Target step, and since we want all the default options to be applied, click Finish.

    The Offline Database Objects from UML Class Model
  26. In the Application Navigator, you now have two offline database schemas defined. One for Scott in a Oracle Lite database and one for Scott in an Oracle 11g database.

    The Application Navigator
  27. Double-click the Departments table in the OracleLite database and review the GROUP column characteristics. Notice the VARCHAR (50) type corresponding to your custom datatype for Oracle Lite databases.

    The Edit Table
  28. Select the NAME column now and review its properties.

    The Edit Table

    Click OK.

  29. In the Application Navigator, double-click the Departments table in the Oracle11g database and review the GROUP column characteristics. Notice the CHAR (50) type corresponds to your custom datatype for Oracle 11g database.

    The Edit Table
  30. Select the NAME column now and review its properties.

    The Edit Table

    Click OK.

  31. Click the Save All button Save All icon to save your work.

    Additionally, you could also experiment generating a database schema for a DB2 database and review the column properties. Show more or lessRead more...

    In the diagram, having the classes selected, right-click and select Transform --> Model Only. In the Offline Database Objects from UML Class Model dialog, in the Specify Target step, select the PhysicalModel project, then click the New button next to the Offline Database field and create the DB2 database emulation.

    The Create Offline Database

Bookmark Print Expand all | Hide all
Back to top
Copyright © 2011, Oracle and/or its affiliates. All rights reserved.