Chapter 18 - Data Model

You are provided with a visual modeling environment for the data model of an application, and also reverse database engineering. This chapter shows you how to depict the object models by using Entity Relationship Diagram and how to reverse database, and describes how the data model maps to the object model.

In this chapter:

• Introduction
• Creating Data Model
• Reverse Database Engineering
• Creating Array Table in Data Model
• Creating Partial Table in Data Model
• Copying SQL Statements
• Mapping Data Model to Object Model

Introduction

An entity is an object in the business or system with well-defined characteristics which are represented by columns showing what information can be stored. In relational databases, an entity refers to a record structure, i.e. table.

A data model provides the lower-level detail of a relational database of an application. It shows the physical database models and their relationships in an application. An entity relationship diagram can be used to describe the entities inside a system and their relationships with each other; the entity relationship diagram is also known as a data model.

Visual modeling for data models is supported, not only by creating a new data model, but also by transforming from an object model. Reversing the existing relational model into data models is also supported. As object-relational mapping is automated, object model can thus be generated from the data model by reversing the existing database.

The following section describes how to reverse the relational model into data models and depict the data models using the Entity Relationship Diagram.

Entity Relationship Diagram

Entity relationship diagram is a graphical representation of a data model of an application. It acts as the basis for mapping the application to the relational database.

Creating an Entity Relationship Diagram

You are provided with three ways to create an Entity Relationship Diagram.

1. Drawing an Entity Relationship Diagram(ERD)
2. Reverse Engineering an existing Relational Database
3. Synchronizing from the Object Model to Data Model

Drawing an Entity Relationship Diagram (ERD)

1. You can create a new ERD in one of the three ways:
   • On the menu, click File > New > File..., select Entity Relationship Diagram from the New File dialog box.

   Figure 18.1 - To create an ERD

   • On the Diagram Navigator, right-click Entity Relationship Diagram > Create Entity Relationship Diagram.

   Figure 18.2 - Create Entity Diagram on Diagram Navigator

   • On the toolbar, click the New Entity Relationship Diagram icon.

A new Entity Relationship Diagram pane is displayed.

Creating a new Entity element to the ERD

1. On the diagram toolbar, click the Entity shape icon.

Figure 18.3 - Entity icon button

2. Click a location in the diagram pane.

An icon representing the entity element is added to the diagram.

3. Type in a name for the Entity element.
   • You can edit the name by double-clicking the name or by pressing the F2 button.

Modifying the Entity Specification

1. To display the Entity Specification, right-click the entity element, click Open Specification.

Entity Specification dialog box is displayed, you can modify the entity properties and constraints.

Figure 18.4 - Entity Specification dialog

Adding new Column to the Entity

1. You can add a new column to the entity in one of the three ways:
   • Right-click on an entity, select New Column.

   Figure 18.5 - Create a columns

A new column is added, type the column name and type in the form of "column_name: type". You can also edit the column name by double-clicking the column name or by pressing the F2 button.

• Click on an entity, press the keyboard shortcut - Alt + Shift + C.
• Alternative:
  1. Right-click the entity element, click Open Specification.
  2. Click the Columns Tab, then click Add.

  Column Specification dialog box is displayed, you can modify the column name and properties, such as type.

  Figure 18.6 - Entity Specification (Columns Tab)

Adding Relationship to the Entities

You can add relationship to the entities in one of the two ways:

• Using Resource-Centric Interface
  1. Click on an entity, a group of valid editing resources are displayed around the entity.
  2. Drag the resource of "One-to-One Relationship" to the associated class.

  Figure 18.7 - "One-to-one Relationship -> Entity" resource-centric

• Using Toolbar icon
  1. On the diagram toolbar, click the Relationship icon.
     • One-to-One Relationship
     • One-to-Many Relationship
     • Many-to-Many Relationship
  2. Click on an entity, drag to another entity.

A line indicating a connection between the two entities is shown.

Editing Relationship Specification

You can edit the relationship specification in the following way:

• Right-click on the connection line, click Open Specification from the pop-up menu.

Relationship Specification dialog box is displayed, you have to modify the relationship properties, Phrase and Cardinality.

Figure 18.7 - relationship Specification dialog

Once you assign a primary key to a column of an entity, a foreign key column is automatically added to all entities associated with it.

An ERD is created.

Figure 18.8 - Entity Relationship Diagram

Reverse Engineering an existing Relational Database

You can create an Entity Relationship Diagram by reverse engineering an existing relational database.

• On the menu, click Modeling > ORM > Reverse Database....
  

Figure 18.9 - To Reverse Database

The Database to Data Model dialog box is displayed.

Step 1: Select Language

Select the language of the project from the drop-down menu, either Java or C#, and then click Next > to proceed to Step 2.

Figure 18.10 - Database to Data Model dialog

Step 2: Database Configuration

You can configure the database connection for the desired database to be reversed.

1. You are asked to define the database configuration. Refer to the descriptions in the Database Configuration chapter for information on how to configure the database.

Figure 18.11 - Database Configuration

2. Click Next>, go to Step 3 of Reverse Database.

Step 3: Selecting Tables

All the available tables found from the connected database are listed.

1. Select the tables that you want to reverse to Data Model.
2. Click Finish.

Figure 18.12 - Selecting Tables

An Entity Relationship Diagram is automatically generated and displayed. It can be found under the Diagram Navigator.

Figure 18.13 - The ERD formed by the reverse entities

Synchronizing from an Object Model to a Data Model

You can generate the ERD from a class diagram by synchronization if there is a class diagram.

1. You can synchronize the Class Diagram to ERD in one of the three methods:
   • On the menu, click Modeling> ORM > Sync to Entity Relationship Diagram.

   Figure 18.14 - Synchronize to Entity Relationship Diagram

   • Right-Click on the Class Diagram, select Synchronize to Entity Relationship Diagram.

   Figure 18.15 - Synchronize to ERD by click on popup menu

   • On the class diagram, hold down the right-mouse button, move the mouse from left to right to form the gesture. A blue path is shown indicating the gesture.

   Figure 18.16 - The ERD formed by the reverse entities

   An Entity Relationship Diagram is generated and can be found under the Diagram Navigator.

   Figure 18.17 - The ERD formed by the reverse entities

Specifying Index Column

If a relationship with cardinality of many at one end, a corresponding collection class will be used for handling its multiple cardinality. You can specify an index column to sort the collection.

1. Right-click on the connection line, click Open Specification from the pop-up menu.

Figure 18.18 - Open the relationship specification

Relationship Specification dialog box is displayed.

2. Check the option for Ordered.
3. Select the index column from the drop-down menu of Index column, click OK.

Figure 18.19 - Relationship specification dialog

You can select Create Column from the drop-down menu to create a new index column for sorting.

Using the ID Generator

As the primary key is unique, the generation of primary key is supported. The ID Generator is specialized for generating a primary key value at runtime.

1. Right-click on the primary key of an entity, select Open Specification from the pop-up menu.

Figure 18.20 - To open Column specification

Column Specification of the primary key is displayed.

2. Select the ID generator from the drop-down menu of ID Generator, click OK to confirm setting.

Figure 18.21 - Column specification dialog

If the ID Generator is specified as either sequence, seqhilo or hilo, you have to enter the key for the sequence/table name.

Defining Discriminator

In generalization, the superclass distributes its commonalities to a group of similar subclasses. The subclass inherits all superclass's attributes and it may contain specific attributes. The entities within the hierarchy are combined into one single entity containing all the attributes and a discriminator column. The discriminator contains a unique value which is used for identifying the entity which hierarchy it belongs to.

You are allowed to define the discriminator in the entity and discriminator value in the classes.

Defining Discriminator Column for Entity

You can add a new column acting as the discriminator column for an entity.

1. Right-click on an entity, select New Column.

Figure 18.22 - Create a column

2. Enter the name and type for the discriminator in the form of "discriminator_name: type".

Figure 18.23 - Enter the column name and type

3. Right-click on the entity, select Open Specification....

Figure 18.24 - Open the column specification

Entity Specification dialog box is displayed.

4. Select the desired column from the drop-down menu of Discriminator Column, click OK to confirm setting.

Figure 18.25 - Column Specification dialog

Defining Discriminator Value for Class

You can specify the discriminator value for each sub-class.

1. Right-click on the relative sub-class for adding discriminator, select ORM > ORM Class Detail...from the pop-up menu. The Class Specification dialog box showing the ORM Class Detail tab is displayed.

Figure 18.26 - To open ORM Class Detail/td>

2. Enter the discriminator value for identifying the sub-class.

Figure 18.27 - Class Specification dialog

Creating an Array Table

In a one-to-many relationship, a collection is used for handling the multiple objects such that it is simpler to retrieve each object from the collection one by one.

The idea of Array Table is promoted which allows users to retrieve objects in the form of primitive array, instead of a collection when handling a data column with cardinality of many.

You are allowed to create an array table in the entity and define an array type in the classes.

Defining an Array Table

You can create an Array Table for the Entity with a column containing more than one instance of data.

1. Create a one-to-many relationship between the entity and one of its columns that may contain more than one instance of data.

Figure 18.28 - Entities with one-to-many relationship

In the above case, the phonebook has a contact entry for each contact person. Each contact person may have more than one phone numbers. A one-to-many relationship between contact entry and contact phone can be built.

2. Right-click on the entity for the data column with cardinality of many, select Convert to Array Table from the pop-up menu.

Figure 18.29 - Convert to Array Table

3. A warning message will be displayed, showing that the listed constraints are not satisfied for converting to array table. Click Yes to let SDE-VS to resolve the constraints automatically. Click No to cancel the conversion to array table.

Figure 18.30 - Warning message for the constraints are not satisfied

The conversion to Array Table is completed and the entity for the data column is stereotyped as Array Table.

Figure 18.31 - Entity are converted to Array Table

Defining an Array Type for Attribute in Class

A class with an attribute of array type modifier means that the attribute may contain more than one data; thus it implies the idea of Array Table.

You can define the array type for the attribute in one of the two ways:

• Using Inline Editing
  1. Right-click on a class, click Add > Attribute.

  Figure 18.32 - To Add a attribute

  2. Enter the name and type for the attribute in the form of "attribute_name :type[]", the sign, "[ ]" indicates the attribute is an array.

  Figure 18.33 - Enter the attribute name and type

• Using Class Specification Dialog Box
  1. Right-click on a class, click Open Specification.

  Figure 18.34 - Open specification

  The Class Specification dialog box is displayed

  2. Click Attribute Tab, click Add.

  Attribute Specification is displayed.

  3. Enter attribute name and type, select [] from the drop-down menu of Type modifier, then click OK to confirm setting.

  Figure 18.35 - Attribute Specification dialog

Creating a Partial Table

In a one-to-one identifying relationship, an entity may be a subordinate of the related entity; that is, the subordinate entity has columns which also belong to its superior entity in the real world situation.

The idea of Split Table with stereotype of Partial is also promoted, which allows developers to optimize the size of database, and minimizes the redundant persistent classes for handling one-to-one identifying relationship. In order to reduce the risk of appending a new column to an existing database table, Split table supports developers to add new columns to the partial table with a one-to-one identifying relationship linked to the existing table.

You are allowed to split the entity into two and convert the subordinate entity to be a Partial Table in a one-to-one identifying relationship.

Splitting Table

You can split an entity into two associated with a one-to-one identifying relationship.

You can activate the Split Table dialog box in one of the two ways:

• Using Pop-up Menu
  1. Right-click an entity, select Split Table.

  Figure 18.36 - To split table

• Using Resource-Centric Interface
  1. Click on an entity, a group of valid editing resources are displayed around the entity.
  2. Click the resource of "One-to-One Relationship - > Partial Table".

  Figure 18.37 - "One-to-One Relationship -> Partial Table" resource-centric

  Split Table dialog box is displayed.

  Figure 18.38 - Split Table dialog

  3. Edit the New Partial Table Name, select the columns from the list of Original to Partial, and click OK.

  An entity stereotyped as Partial is created.

  Figure 18.39 - The split entities with one-to-one relationship

Converting to a Partial Table

You can convert an entity to a Partial Table in a one-to-one identifying relationship.

1. Right-click on the entity, select Convert to Partial Table from the pop-up menu.

Figure 18.40 - Convert to a partial table

The entity is stereotyped as Partial.

Figure 18.41 - The entity is convert to a partial table

Copying SQL statements from Tables

Copying SQL statements from the ERD entities is provided. It allows the developers to copy the SQL statements from the entity relationship diagram easily such that developers can use and modify the SQL statement on the database directly.

In order to copy the SQL statement, you must configure the database setting in advance as the SQL statements will be generated according to the default database server type.

1. To configure database connection, on the menu, click Modeling > ORM > Database Configuration....

Figure 18.42 - To open database configuration

Database Configuration dialog box will be displayed. Refer to the descriptions in the Database Configuration chapter for information on how to configure the database.

SDE-VS will only provide you the function of copying SQL if the default database connection is set.

If there are multiple database settings, the SQL statements will be generated for all these database servers.

There are two database settings selected in the working environment.

Figure 18.43 - Set 2 database setting

1. Right-click on the ERD, select Copy SQL > Detail...from the pop-up menu.

Figure 18.44 - To copy a SQL

You can select Create Table(s), Drop Table(s), Select, Insert, Update and Delete from the Copy SQL submenu to directly copy the SQL statements to clipboard.

2. Generate SQL dialog box is displayed, select the database server from the drop-down menu of Database, the corresponding SQL statements will be displayed accordingly.

Figure 18.45 - Generate SQL dialog

You are allowed to copy the SQL statements from the Generate SQL dialog box.

Copying SQL statements from Specified Scope

You can specify the scope on the ERD to generate the SQL statements.

You can specify one of the three scopes:

• All entities on the ERD
  1. Select all entities on the ERD.
  2. Right-click on the entity, select Copy SQL > Detail....

  As copying SQL without specifying scope, SQL statements will be generated for all components including both entities and relationships on the ERD.

  Example:

  Figure 18.46 - Generate SQL for specified scope

  Figure 18.47 - Generate SQL dialog

• Multiple entities and connection lines on the ERD
  1. Select several entities and relationships on the ERD, press Alt, right-click on the diagram pane, select Copy SQL from the pop-up menu.

  As copying SQL with specifying a particular scope, SQL statements will be generated only for the components included in the specified scope.

  Example:

  Figure 18.48 - Copy a SQL statement for create table

• Connection lines on the ERD
  1. Select connection line, press Alt, right-click on the diagram pane, select Copy SQL from the pop-up menu.

  As copying SQL with connection lines, SQL statements for Create Constraint(s) and Drop Constraints(s) will be generated.

  Example:

  Figure 18.49 - Copy Statemenet for create constraint

  Create Constraint :

  alter table `OrderLine` add index `FK_OrderLine_1969` (`PurchaseOrderPO_NO`), add constraint `FK_OrderLine_1969` foreign key (`PurchaseOrderPO_NO`) references `PurchaseOrder` (`PO_NO`);

  Drop Constraint :

  alter table `OrderLine` drop foreign key `FK_OrderLine_1969`;

Mapping a Data Model to an Object Model

Object Relational Mapping (ORM) is supported which maps data models to object models and vice versa.

Mapping between objects to relational database preserves not only the data, but also the state, foreign/primary key mapping, difference in data type and business logic. Thus, you are not required to handle those tedious tasks during software development.

Mapping Entities to Classes

All entities map one-to-one to persistent classes in an object model.

Example:

Figure 18.50 - Mapping entities to classes

In the above example, the Customer Entity map one-to-one the Customer Class as the Customer instance can store the customer information from the Customer Entity.

Mapping Columns to Attributes

Since all entities map one-to-one to persistent classes in an object model, columns in turn map to attributes in a one-to-one mapping. All specialty columns such as computed columns and foreign key columns are ignored during the transformation.

Example:

Figure 18.51 - Mapping columns to attributes

In the above example, the following table shows the mapping between the columns of the Customer Entity and the attributes of the Customer Class.

Mapping Data Type

The column data type automatically maps to an appropriate attribute type of object model.

Example:

Figure 18.52 - Mapping data types

In the above example, the following table shows the mapping between data types

A table shows the data type mapping between Object model and Data model.

Mapping Primary Key

As the columns map to attributes in a one-to-one mapping, primary key columns in the entity map to attributes as a part of a class.

Example:

Figure 18.53 - Mapping primary key

In the example, the primary key of entity Product, ProductID maps to an attribute ProductID of the class Product.

Mapping Relationship

Relationship represents the correlation among entities. Each entity of a relationship has a role, called Phrase describing how the entity acts in it. The phrase is attached at the end of a relationship connection line. The phrase maps to role name of association in the object model.

There are two types of relationships in data model mapping to object model - identifying and non-identifying.

Identifying relationship specifies the part-of-whole relationship. It means that the child instance cannot exist without the parent instance. Once the parent instance is destroyed, the child instance becomes meaningless.

Non-identifying relationship implies weak dependency relationship between parent and child entities. There are two kinds of non-identifying relationships, including optional and mandatory. The necessity of the parent entity is "exactly one" and "zero or one" in the mandatory and optional non-identifying relationship respectively.

Mapping Identifying Relationship

Since the identifying relationship specifies the part-of-whole relationship, it map to composite aggregations which implies that the part cannot exist without its corresponding whole.

Example:

Figure 18.54 - Mapping identifying Relationship

In the above example, the identifying relationship between the entities of EmergencyContact and Student maps to composition aggregation.

Mapping Non-identifying Relationship

Since non-identifying relationship implies weak relationship between entities, it maps to association.

Example:

Figure 18.55 - Mapping non-identifying relationship

In the above example, non-identifying relationship between entities Owner and Property maps to association between Classes of Owner and Property.

Mapping Cardinality

Cardinality refers to the number of possible instances of an entity relate to one instance of another entity. The following table shows the syntax to express the Cardinality.

Table shows the syntax expressing the Cardinality

Type of Cardinality	Description
	Zero or one instance
	Zero or more instances
	Exactly one instance
	One or more instances

Table shows the typical mapping between Entity Relationship Diagram and Class Diagram.

Entity Relationship Diagram	Class Diagram

Mapping Many-to-Many Relationship

For each many-to-many relationship between entities, a Link Entity will be generated to form two one-to-many relationships in between. The primary keys of the two entities will automatically migrate to the link entity to form the primary/foreign keys.

Example:

Figure 18.56 - Mapping Many-to-many relationship

In the above example, SDE-VS generates the link entity once a many-to-many relationship is setup between two entities. To transform the many-to-many relationship, the many-to-many relationship maps to many-to-many association.

Mapping Array Table to Collection of Objects

The Array Table is promoted to allow users retrieve objects in the form of primitive array.

When transforming an array table, the array table will map to an attribute with array type modifier.

Example:

Figure 18.57 - Mapping Array Table to Collection of Object

In the above example, the phonebook has a contact entry for each contact person. Each contact person may have more than one phone numbers. The array table of ContactEntry_Phone maps into the phone attribute with array type modifier in the ContactEntry class.

Mapping Data Model Terminology

The following table shows the shift from data model to object model terminology.

Visual Paradigm International Limited Website: www.visual-paradigm.com E-mail: support@visual-paradigm.com