Overview

This tool mentor describes how to build a Data Model with the Data Modeler.

• The steps in this tool mentor match those in the corresponding task.
• The following steps are performed in this tool mentor:
• • Develop Logical Data Model <optional>
  • Develop Physical Data Model
    • Define Domains
    • Create Initial Physical Database Design Elements
    • Define Reference Tables
    • Create Primary Key and Unique Key Constraints
    • Define Data and Referential Integrity Enforcement Rules
    • De-Normalize Database Design to Optimize for Performance
    • Optimize Data Access
    • Define Storage Characteristics
    • Design Stored Procedures to Distribute Class Behavior to the Database
  • Review the Results

The Rational XDE (TM) software tool includes features that allow the application designers and database designers to develop the application and the database using the same tool. As a Database Designer or Designer, you can use XDE to model and design databases, as well as to integrate your application and database. The XDE Data Modeler uses the Unified Modeling Language (UML) Profile for Database Modeling as the standard notation for constructing Data Models. The following XDE online Help topics provide key background information on specific details related to database modeling and development using Rational XDE:

• Databases Supported in Rational XDE : Lists the databases supported in Rational XDE.
• Supported OLE DB Providers and JDBC Drivers : Lists data access providers and drivers supported in Rational XDE.
• Setting Options for Data Models : Summarizes setting the default database assignment and database target.
• Setting the Default Database Assignment : Provides guidance for configuring XDE Data Models to point toward a specifically named database.
• UML Data Modeling Profile : Summarizes the Unified Modeling Language (UML) modeling elements used in the Data Model.
• Data Modeling Visibility Icons : Lists notational elements for defining primary/foreign keys and database triggers specific to a Data Model.

There are three ways to begin a Data Model in XDE:

• Build the Data Model directly in XDE using the Data Modeler tools.
• Transform persistent classes in the Design Model to create tables in the Data Model.
• Reverse engineer an existing database schema or DDL script to create a Data Model. 

For more information about the different methods for creating the Data Model, refer to the following XDE Help topics:

• Designing Data Models
• Class to Table Transformation
• Building Data Models by Reverse Engineering

The remaining sections of this tool mentor provide instructions on how to build a Data Model by transforming persistent classes in the Design Model to develop it. The Data Model package structure that is discussed here is described in more detail in the Data Model section of XDE Model Structure Guidelines.

Tool Mentor: Reverse Engineering Databases Using Rational XDE provides more information on how to create a Data Model by reverse engineering the physical database design.

Develop Logical Data Model <optional>

Some projects might 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 might 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 can be created directly using XDE Data Modeler tools. See the XDE Model Structure Guidelines for information on creating a Logical Data Model within the overall Data Model work product. Reference: Logical Data Models

Develop Physical Database Design

You can refine the Logical Data Model to create a detailed model of the physical database design using Rational XDE.
(See Physical Data Models .) This detailed Physical Data Model might then be forward engineered to create a database. (See Tool Mentor: Forward Engineering Databases in Rational XDE.)  The major steps in developing a Physical Data Model are described below.

Define Domains

Create domains to implement user-defined data types that can be used throughout the Data Model to enforce database design standards. (See Domains .)  Domains can also be used to embed business rules in columns.

In order to use the data types defined by the domains in the Data Model, you must follow these guidelines:

• Domains should be defined in a separate package within the Data Model. For larger teams, a separate XDE model is often warranted. (See Creating Domains .)
• The package containing the domains must be assigned to the database component that realizes the tables in the database. (See Assigning Domain Packages to Databases .)

For information on how to apply a domain to a column in the Data Model, see Assigning Domains to Columns . 

Create Initial Physical Database Design Elements

Transform persistent classes in the Design Model into tables within it. (See Class to Table Transformation .)  The attributes of the classes become columns in the tables. Rational XDE provides the option of defining attributes of the classes as candidate keys. (See Assigning Candidate Keys .) Rational XDE also converts specific associations between the classes to relationships between the tables. The following Rational XDE online Help topics provide more information on how the classes are transformed into tables:

• General Mapping Information - Class to Table Transformation Mapping
• DBMS Specific Mapping Information - Class to Table Transformation Data Type Mapping

Define Reference Tables

Create reference tables as needed for managing any static data items in the database. You can create reference tables directly in the Data Model using the XDE Data Modeler features. Reference: Modeling Tables and Columns

Create Primary Key and Unique Key Constraints

Define primary key constraints and unique key constraints to identify rows of information in a table. See the online Help topic Primary Key Constraints The online Help topic  Creating Primary Key Constraints describes the steps used to create a primary key constraint on one or more columns of a table.  Information on defining unique key constraints is contained in Unique Key Constraints Steps to create a unique key constraint in the model: Creating Unique Key Constraints .

Refer to Rational XDE online Help: Creating Constraints

Define Data and Referential Integrity Enforcement Rules

Check constraints can be used to control updates to data elements in tables. Rational XDE provides the ability to define check constraints for tables, columns, and domains. See the topic Check Constraints The following XDE online Help topics describe how to create the three types of check constraints in the Data Model:

• Column Check Constraints - Creating Column Check Constraints
• Table Check Constraints - Creating Table Check Constraints
• Domain Check Constraints - Creating Domain Check Constraints

Another type of constraint used to assure referential integrity is the foreign key constraint. (See Foreign Key Constraints .)  Foreign key constraints can be produced only by creating a relationship between tables. (See Creating Foreign Key Constraints .)  The exception to this is the process of reverse engineering a database or DDL script into a Data Model, in which case the relationships, and therefore the foreign keys, are generated automatically. Key migration depends on the type of relationship identifying versus non-identifying added between two tables.

Reference: Key Migration Reference: Changing the Migrated Key .

De-Normalize the Database Design to Optimize for Performance

Depending on the specific project situation, you might need to adjust the normalized physical database design to meet performance requirements. Optimize the Data Model for performance improvement by designing the tables to store objects that are retrieved together in the same table. This technique is called de-normalization. To de-normalize, combine the unique columns from the two tables into one, and remove the second table. 

Optimize Data Access

An additional optimization technique is to use column indexing to access data in tables more efficiently. See Indexes Description of how to create indexes in the Data Model: Creating Indexes

Another aspect of data access is in the use of database views. Views can be used to control or restrict access to data in one or more tables. See Creating Views  for a description of how to create a view in the Data Model. Reference: Modeling Views

Define Storage Characteristics

Create a model storage design for the database by defining the tablespaces and tablespace containers. (See Tablespaces .) Map the physical database design elements to the storage elements through realization relationships. The XDE online Help topic Adding Tablespaces to Databases describes how to create tablespaces for a specific database. The XDE online Help topic Assigning Tables to Tablespaces describes how to assign the tables in the model to the tablespaces.

Rational XDE currently supports modeling tablespaces for Oracle, DB2, and SQL Server.  The following XDE online Help topics provide guidance on how to model tablespaces for these DBMS products: 

• Modeling Oracle Tablespaces
• Modeling DB2 Tablespaces
• Modeling SQL Server Tablespaces

Refer to the following topics in the Rational XDE online Help for more information about tablespace and database model elements:

• Modeling Databases and Tablespaces
• Databases

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 tables for candidate stored procedures. Stored procedures can be implemented as procedures or functions. See Stored Procedures Stored procedures must reside in a Stored Procedure Container, as described in Stored Procedure Containers . Stored procedures are created as operations of the Stored Procedure Container class in which they reside. See Creating Stored Procedures , Creating Stored Procedure Containers , Creating Stored Procedure Parameters

Refer to Rational XDE online help topic: Modeling Stored Procedures

Define triggers as needed to further control and manage modifications to the information in the tables. Reference: topic Modeling Triggers

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 specific items to review.