Introduction

A data model is a plan for building a database. To be effective, it must be simple enough to communicate the required data structure to the end user, yet detailed enough that the database designer can create the physical structure. IBM® WebSphere® Studio Application Developer (WSAD) Version 5.1 supports developing a logical data model and later exporting it to a database domain (like DB2, Oracle, and so on) to create a physical database. IBM® Rational® Application Developer (RAD) Version 6.0 introduces visual data model design. It supports three industry standard notations: IDEF1X, Information Engineering (IE or Crow's Foot), and UML (Unified Modeling Language). Using a feature-rich, simple, and user-friendly data palette tool, you can develop a complete data model with a few mouse clicks. You can also visualize an existing database by connecting the database through a Java™ Database Connectivity (JDBC™) connection.

Back to top

Designing a data model in visual editor

This document does not go into detail describing the importance of data modeling. In short, just as you would not build a house without a blueprint, you should never design a database without a data model. RAD Version 6.0 enhances your ability to build a data model by allowing you to create several types of diagrams: namely class diagrams, IDEF1X diagrams, and IE diagrams. Its user-friendly data tool palette allows you to create -- directly onto a diagram -- the following:

• Databases
• Schemas (only in class diagrams)
• Tables
• Primary and foreign keys
• Views
• Stored procedures (only in class diagrams)
• User-defined functions

Creating a database

The first step to design a data model is to create a simple project (say MyDataModel) and open the Data Perspective. Once the data definition view is active:

1. Select the project folder in the data definition view.
2. Right click and select New Class Diagram from the context menu.
3. Give the diagram a name, say, classdiagram1. Then click Finish.
4. This creates and opens anew class diagram.
5. Select Database creation tool from the data palette (located at the right side of the window), as shown in Figure 1.

Figure 1: Creating a database

6. Click on the diagram.
7. Give a Database name (such as CRM). Select a Database vendor type (Cloudscape v5.1) and click Finish.

You have created a new Database, CRM. You need to expand the project folder in the data definition view to see the newly created database. The Database will be notated as a UML component in the class diagram (shown in Figure 2).

Figure 2: Your new database in a class diagram

Creating Tables

Now create two tables, Customer and Orders, and a referential integrity between the tables.

1. Select the Table creation tool from the palette.
2. Click on the diagram.
3. Provide a Schema name such as schema1 then click Next.
4. Enter a table name, Customer and then click Next.
5. Enter the following columns: cust_id (check key column), name, address, and phone.
6. Click Finish.

This creates a schema schema1 and a table Customer. Expand the schema folder schema1 under CRM to see the Customer table in the data definition view. The table Customer will be notated as a UML class in the diagram. Now, create a second table, named Orders.

1. Select the Table creation tool from the palette.
2. Click on the diagram.
3. Provide a table name, Orders, and then click Next.
4. Enter the following columns: ord_id, quantity, date and click Next .
5. Leave the primary key empty and click Next.
6. In the foreign keys page (shown in Figure 3), add a new foreign key ord_cust_key. Select  Customer as Target Table and ord_id from the Source Columns.

Figure 3: Adding new Foreign Keys

7. Click Finish.

The Orders table will be notated as a UML class shape in the diagram. Figure 4 depicts the referential integrity (foreign key relationship) shown by a UML association between the tables.

Figure 4: The tables and their relationship

Alternative way of creating a primary key and foreign key

A primary key can also be created by selecting the Primary Key tool in the tool palette and clicking on a table. To convert an existing column into a primary key, click on the column (property in the class) in the diagram. A wizard will open and, after clicking the Finish button, will show you the primary key in the table with a key icon next to the column. Creating a foreign key using the Foreign Key tool is also very easy. First, select the Create Foreign Key tool in the palette, and then select the source table. Hold the right mouse button and drag to the target table. The target table must have a primary key.

Creating views, stored procedures, and user-defined functions

The palette tool also supports creating views, stored procedures, and user-defined functions. To do so, select the appropriate tool from the palette and click on the diagram. There a schema must exist in the database in order to create these data model elements. If there are multiple schemas in the database, then select the appropriate schema in the data definition view, and then click on the diagram. Please note that only DB2 express database and DB2 universal database support creating stored procedures and user-defined functions.

Visualizing existing data models

A local or remote database that supports a JDBC connection can have its database elements visualized, which includes all relationships among the elements. In order to visualize the data element, first make a JDBC connection from the database server view. The example shown in Figure 5 connects to a Cloudscape database -- Cloudscape also comes with IBM® Rational® Software Architect (RSA).

Figure 5: Creating a JDBC connection

1. Right-click on data server view and select new connection.
2. Select Choose a database manager and JDBC driver and click Next.
3. Fill in the information as per that in the wizard shown in Figure 5.
4. Select the JDBC driver as Cloudscape Embedded Driver.
5. Click the Browse button to select the Database location. The C:\Cloudscape_5.1\demo\databases\toursDB demo database comes with Cloudscape samples.
6. Select a driver Class location. This example selects C:\Cloudscape_5.1\lib\db2j.jar.
7. Click Finish .

A database toursDB will be populated under the connection folder. Expand a schema, select the table you wish to visualize, and right-click Visualize -> Add to New Diagram File -> Class diagram.  This will create a table shape in a class diagram under a new project, as shown in Figure 6.

Figure 6: Visualizing your model

Showing all the foreign keys associated with a table

To see all the tables which maintain referential integrity with a particular table, select the table and execute the context menu Filters -> Show Related Elements. All tables which have foreign key relationships to the selected table -- and all foreign key tables from the selected table -- will be visualized in the diagram illustrated in Figure 7, following.

Figure 7: Visualizing table relationships

Data element property

To view the properties of any model elements, select the element in the diagram and right-click Show Properties View. To modify the property, open the element in the editor and update from there, because all of the properties in the property browser are read-only.

Modifying data model elements

There are certain properties which can be modified from the diagram -- for instance, adding a new column in a table and so on. But in most cases you need to open them in the model editor. For example, to update a table, double-click on the table. This opens the table editor, allowing you to modify and save the table. All updates will be reflected in the diagram immediately. Note that if a modelÂ’s editor is open, the element from the diagram cannot be altered. The editor needs to be closed in order to modify the element in the diagram.

Mapping a table to EJB

Table can be mapped to Enterprise JavaBeans™ (EJB™) and vice versa. RAD Version 6.0 supports mapping a local table -- as well as a read-only table (in the data server view) -- to an EJB. When a table is mapped to an EJB, it creates an EJB project (or lets you use an existing project), and then copies the current table under a mapping directory inside the EJB project. To map a table to an EJB:

1. Select the table either from the data viewer or in a diagram.
2. Right-click, and select Create EJB from Table.
3. Provide a new EJB project, say myEjbproj .
4. Create a new target server .
5. Click Finish.

You have now created the map shown in Figure 8.

Figure 8: A table mapped to an EJB

IDEf1X and IE diagram

So far, only UML notation has been used to show the shapes, but you can create all these shapes in IE or IDEF1X notation. While creating a diagram, there are three options available: class diagram, IE diagram ,and IDEF1X diagram. Once the diagram is created the shapes in the diagram get notated by the diagram types. The IDEF1X notation for the above data model example is shown in Figure 9 following.

Figure 9: The data model in IDEF1X notation

Figure 10 illustrates the IE notation of the same model.

Figure 10: The data model in IE notation

Back to top

Conclusion

I have shown how to create and view a simple data model with just a few mouse clicks in IBM's Rational Application Developer Version 6.0. In this article I have covered very few highlights of visual data modeling; you can do a lot more with this visual editing tool.

Back to top

Resources

• developerWorks Studio zone. Access to Studio how-to articles, downloads, tutorials, education, product information, and more.
• Developer Bookstore. Hundreds of technical books for developers at up to 35% off retail, including many titles about Studio.
• WebSphere forums. Product-specific forums where you can ask questions and share your opinions with other users.
• developerWorks. Ongoing, free-form columns by software experts, with space for you to add your comments. Check out Grady on Software architecture and engineering.
• For more about IDEF1X, see www.idef.com/idef1x.html or buy Thomas Bruce's 1992 classic Designing Quality Databases With IDEF1X Information Models [ISBN 0932633188].)

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Designing a data model in visual editor
• Conclusion
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Rational Application Developer for WebSphere Software
• Buy: Rational Application Developer for WebSphere Software

Dig deeper into Rational on developerWorks

Discover knowledge paths

Skill-building guides for Rational, Linux, open source, cloud, Java, business analytics, and more.

Special offers