Tool Mentor: Designing and Modeling Databases Using Rational Rose Data Modeler
This tool mentor describes creating a data model with Rational Rose Data Modeler. This tool mentor also provides information on generating a new DDL or database schema from the Rose data model, and how to reverse engineer a database to create a data model.
Tool: Rational Rose
Relationships
Related Elements
Main Description

Overview

Rational Rose Data Modeler features allow the database designer and the software developer to develop the application and database design using the same tool. As a database designer or developer, you can use Rational Rose Data Modeler to model and design databases, and to integrate your application and database. 

Rational Rose Data Modeler uses or creates three model types an object model, a data model, and an optional data storage model. An object model represents classes, their behaviors, and the relationships between classes. The Rational Rose Data Modeler "object" model generally corresponds to the RUP Design Model work product. The Rose class diagram represents a view of the object model. A data model represents the structure of the database as implemented by the enterprise. The optional data storage model represents the physical storage structure of the database.  The Rational Rose Data Modeler "Data Model" and "Data Storage Model" generally correspond to the RUP Data Model work product.

You can create a model of the physical design of the database by transforming the persistent classes in the object model to tables in a data model.  The persistent classes must be assigned to a component and be located in the same logical package.  Another way to create a model of the physical database design is by reverse engineering an existing database schema or DDL script.

Using Rose Data Modeler transformation options, you can integrate application and database designs. Transformation options map elements contained in an object model to create a data model, or transform elements in a data model to create an object model. You must understand object-oriented analysis and design (OOAD) concepts and the Unified Modeling Language (UML) to create the object model. Creating a data model requires understanding relational database design. Rose Data Modeler uses database terminology and UML stereotypes to represent database elements.
 

Tool Steps

  1. Develop Logical Data Model (Optional)
  2. Develop Physical Database Design
  3. Review the Results

In addition to the steps described above, this tool mentor also provides information on the following additional topics related on building and managing the Data Model in Rational Rose.

See the  Helpbook icon Getting Started section of the Rose Data Modeler online Help topics for an overview of how to design and model databases using Rational Rose Data Modeler. 

1. Develop Logical Data Model (Optional)

Some projects may need to create a idealized "logical" model of the database design that captures an application independent view of the key logical data entities and their relationships.  This "Logical Data Model" can be thought of as an "analysis" type of model similar to the optional Artifact: Analysis Model that may be used in the development of the application design.   It should be noted that the Logical Data Model is included in the Artifact: Data Model, and is not considered to be a separate RUP work product. 

The Logical Data Model may be created using the Rational Rose Data Modeler features for building a new data model using Data Model Diagrams. In Rational Rose Data Modeler, the Logical Data Model will be enclosed in a separate schema package in the Rational Rose Logical View.  Consult the following online Help topics for information on building a Data Model:

  • helpbook icon Building a Data Model
  • helpbook icon Create a Schema
  • helpbook icon Working with Data Model Diagrams.

The development of an idealized Logical Data Model is optional based on the specific project needs.  Projects may choose to develop the data model through the use of Rational Rose Data Modeler Object-to-Table transformation capabilities instead of building the model independently. 

2. Develop Physical Database Design

The physical database design is the detailed table designs of the database created using Data Model Diagrams in the Logical View.  The physical database design may be represented as a "Physical Data Model" which also includes model elements for database views, indexes, constraints, stored procedures, and other elements as described in the helpbook icon Data Model Elements online Help topic.  This Physical Data Model is not considered to be a separate work product but is instead part of the Artifact: Data Model, and may be contained in one or more schema packages in the Rational Rose Logical View. 

The initial physical database design model elements can be initially created in one of the following ways:

  • Use the Rational Rose Data modeler Object-to-Table transformation features to create an initial set of tables.
  • Reverse engineer an existing database schema or DDL script (See  helpbook icon Reverse Engineering a Database or DDL File)
  • Develop an initial physical data model through the evolution of the logical database design contained in an optional logical data model.

The remaining steps in this tool mentor discuss the approach of starting the physical database design using the Object-to-Table transformation process. Consult the helpbook icon Transforming an Object Model to a Data Model on-line Help topic for information on pre-requisites for using the Object-to-Table transformation feature.

Create Domains

Create Domains to implement user-defined data types that can be used throughout the data model to enforce database design standards. See the following online help topic,  helpbook icon Working with Domains, for an overview of what domains are and how they are used.  When a schema package in the Data Model is first created, Rational Rose Data Modeler also creates a Global Data Types package in the Logical View that is used to store domain packages and domains. Refer to the following Rational Rose Data Modeler online help topics for more details on creating Domains.

  • helpbook icon Domains
  • helpbook icon Create a Domain Package
  • helpbook icon Create a Domain

Create Initial Physical Database Design Elements

Using Rational Rose Data Modeler, you can transform classes and their relationships in the object model to create tables and data model relationships in the data model. See the Rational Rose Data Modeler online Help topic helpbook icon Transforming Object Model Elements Mapping for a detailed description of how the object model elements are transformed into Data Model elements.

 Before transforming an object model to a Data Model:

  • Set the state of classes to persistent.
  • Assign classes to a component that uses the languages Java, Visual Basic, or Analysis.
  • Group the classes in the same logical package.

The specific transformation steps are described in the Rational Rose Data Modeler helpbook icon Transform an Object Model to a Data Model online Help topic. Object model elements transform to the data model elements using data type mappings specific to the selected DBMS. When the transformation is complete, you can create Data Model Diagrams to begin work on developing the detailed physical database design.  See helpbook icon Create Data Model Diagrams for more information.

You can modify the tables and/or create additional tables and relationships in the Data Model. Refer to the following topics for more information on creating tables and relationships:

  • helpbook icon Tables and helpbook icon Create Tables
  • helpbook icon Columns and helpbook icon Create a Column
  • helpbook icon Relationships and helpbook icon Create Relationships

Define Reference Tables

Create reference tables as needed for managing any static data items in the database.  See the list of topics on tables and relationships in the preceding step of this tool mentor for information on creating tables and relationships.

Create Primary Key and Unique Key Constraints

Define primary key constraints and unique key constraints to identify rows of information in a table.  For information on how to create and use primary and unique key constraints consult the following Rose Data Modeler online help topics:

  • helpbook icon Key Constraints
  • helpbook icon Create a Key Constraint
  • helpbook icon Create Keys (Primary and Unique)

Define Data and Referential Integrity Enforcement Rules

Defining referential integrity rules to ensure that database updates are managed properly.  Rational Rose Data Modeler supports declarative referential integrity (DRI) and system generated referential integrity (RI) triggers.  Consult the following online Help topics for information on

  • helpbook icon Referential Integrity
  • helpbook icon Define Referential Integrity

Apply check constraints to enforce business rules in the Data Model.  Rational Rose Data Modeler allows check constraints to be assigned to a column, a domain, or a table.  The following online help topics provide more information on creating and using check constraints.

  • helpbook icon Check Constraints
  • helpbook icon Create Check Constraints
  • helpbook icon Apply Business Rules

Foreign key constraints are another important aspect of data and referential integrity enforcement.  Foreign key constraints are obtained by creating a relationship between tables.  When a relationship is created, the primary key of the parent table is migrated to the child table as the foreign key. Consult the following online help topics for more information on creating foreign key constraints helpbook icon Key Constraints and helpbook icon Migrating Keys.

De-normalize Database Design to Optimize for Performance

Occasionally, it may be desirable for performance improvement to store objects that are retrieved together in the same table.  This technique is called de-normalization.  To represent this in the Data Model, combine the unique columns from the two tables into one and remove the second table.  For more information on de-normalization and optimization, consult the following Rational Rose Data Modeler online Help topics:

  • helpbook icon De-normalizing the Data Model
  • helpbook icon Optimizing the Data Model

Optimize Data Access

An additional optimization technique is to use column indexing to access data in tables more efficiently. See the following topics for information on how to create indexes in the Data Model.

  • helpbook icon Indexes
  • helpbook icon Create an Index
  • helpbook icon Optimizing the Data Model

Additionally, views may be defined to improve data access.  Views can be used to create a virtual table consisting of columns from one or more tables and/or other views that are accessed frequently by the application. The following Rational Rose Data Modeler online Help topics provide detailed information on creating views and relationships between views and tables in the model:

  • helpbook icon Views and helpbook icon Create Views
  • helpbook icon Working with Views
  • helpbook icon Dependencies and  helpbook icon Working with View Dependencies

Define Storage Characteristics

You model the physical storage of your data by creating a data storage model. A data storage model consists of a database that contains one or more tablespaces.  This "data storage model" is considered to be part Physical Data Model, which is included in the Artifact: Data Model in RUP, and is not a separate work product.  The data storage model is contained in the Component View of the overall Rose Model.

A tablespace is a logical storage element that stores your table data. You can assign one or more tables to your tablespace and distribute your table data across one or more containers. A container is a physical storage device, such as a disk, file, or directory. Each container is segmented into extents or pages and measured in kilobytes.  See the list of topics in the following Rose Data Modeler online Help sections for more information on databases and table spaces.

  • helpbook icon Modeling Data Storage
  • helpbook icon Building a Data Storage Model

These help sections also include topics that provide information on defining data storage model elements that are specific to the Database Management Systems (DBMSes) supported by Rational Rose Data Modeler.

Design Stored Procedures to Distribute Class Behavior to the Database

Define stored procedures as needed to support efficient storage and retrieval of information in the database. Examine the operations of the design classes that were used to create the initial tables for candidate stored procedures.  Stored procedures can be implemented as procedures or functions.  For more information how to create stored procedures in the Data Model see the following Rational Rose Data Modeler online Help topics:

  • helpbook icon Stored Procedures
  • helpbook icon Creating Stored Procedures
  • helpbook icon Working with Stored Procedures

Also, you can define triggers as needed to further control and manage modifications to the information in the tables. Consult the following online help topics for more information on creating triggers in the Data Model:

  • helpbook icon Custom Triggers
  • helpbook icon Creating Custom Triggers
  • helpbook icon Working with Custom Triggers

3. Review the Results

Review the results of the database design in the Data Model for consistency with the application design in the Design Model and with the overall application architecture structure.  Refer to Checklist: Data Model for some specific items to review.

Additional Topics

This section of the tool mentor describes some additional items related to roundtrip engineering and maintenance of the data model and database.

Forward Engineer the Data Model

When the detailed database design (including the data storage design) has been sufficiently developed in the Data Model, you can use the Rose Data Modeler Forward Engineering Wizard to generate a DDL or database schema from your data model diagram. The Forward Engineering Wizard reads the schema in the data model and generates a DDL script for the DBMS you specified in the wizard. In the wizard, you can choose to execute the DDL script to generate a database schema.

In the Forward Engineering Wizard, you select options to generate:

  • Tables
  • Indexes
  • Triggers
  • Stored Procedures
  • Views
  • Tablespaces
  • Fully qualified names. These are used to prefix the schema name to table names.
  • Quoted identifiers for tables, columns, and schemas required for localization using double-byte code set (DBCS)
  • SQL drop statements to overwrite existing DDL scripts, database elements, or comments

You will need to ensure that the proper database connectivity has been established to enable forward engineering process to work.  Consult the following Rational Rose Data Modeler online Help topics for more information on Forward Engineering:

  • helpbook icon Forward Engineering to a DDL or Database (Forward engineering process information)
  • helpbook icon Forward Engineer to a DDL or Database (Specific steps to run the Forward Engineering Wizard)

Consult the list of Database Management System (DBMS) topics in the helpbook icon Reference section of the Rational Rose Data Modeler online Help for specific information about data type mapping, database connections and other topics related to forward engineering of the Data Model to a specific target DBMS. 

Maintain the Data Model

Once a Data Model has been forward engineered to create a database, you can use the Rational Rose Data Modeler Compare and Synchronize Wizard to maintain the consistency of the data model with the implemented database. 

When synchronizing the Data Model with an implemented database, you will need to ensure that the proper database connectivity has been established to enable the compare and synchronize process to work.  Consult the following Rational Rose Data Modeler online Help topics for more information on Data Model Compare and Synchronization:

  • helpbook icon Comparing and Synchronizing the Data Model  (Compare and Synchronize process information)
  • helpbook icon Compare a Schema to a Database or DDL file from a Database or DDL file (Specific steps to run the Compare and Synchronize Wizard)

Consult the list of Database Management System (DBMS) topics in the helpbook icon Reference section of the Rational Rose Data Modeler online Help for specific information about data type mapping, database connections and other topics related to comparing and synchronizing the Data Model to a specific target DBMS. 

Also refer to the Rational Rose Data Modeler online Help topic helpbook icon Modifying Data Models for information about specific rules for making modifications to the Data Model elements.

Reverse Engineer the Data Model from a DDL script or Database Schema

Use the Rational Rose Data Modeler Reverse Engineering Wizard to generate a data model from a database schema or DDL file.  The Reverse Engineering Wizard reads the database schema or DDL file and creates a data model diagram that includes the names of all quoted identifier entities. Depending on the DBMS, Rose Data Modeler Reverse Engineering Wizard models tables, relationships between tables, stored procedures, indexes, and triggers in the data model diagram.

You will need to ensure that the proper database connectivity has been established to enable the reverse engineering process to work.  Consult the following Rational Rose Data Modeler online Help topics for more information on Reverse Engineering:

  • helpbook icon Reverse Engineering a Database or DDL (Reverse engineering process information)
  • helpbook icon Reverse Engineer from a Database or DDL file (Specific steps to run the Reverse Engineering Wizard)

Consult the list of Database Management System (DBMS) topics in the helpbook icon Reference section of the Rational Rose Data Modeler online Help for specific information about data type mapping, database connections and other topics related to reverse engineering of the Data Model from a specific DBMS. 

After reverse engineering the database or DDL, you can optionally transform the tables in the Data Model generated from the reverse engineering process into classes in the object (design) model. See the following topics for more information on the table-to-object transformation process.

  • helpbook icon Transforming a Data Model to an Object Model (Transformation process information)
  • helpbook icon Transform a Data Model into an Object Model (Specific steps to perform the transformation)
  • helpbook icon Transforming Data Model Elements Mapping (Mapping of Data Model elements to Object Model elements)