Chapter 4 - Designing Data Model by Entity Relationship Diagram

DB Visual ARCHITECT (DB-VA) provides you a visual modeling environment for the object model of an application. This chapter shows you how to depict the object models by using Entity Relationship Diagram.

In this chapter:

• Introduction
• Creating Data Model by Entity Relationship Diagram
• Creating Array Table in Data Model
• Creating Partial Table in Data Model
• Copying SQL Statements

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.

DB-VA supports visual modeling for data models, not only by creating a new data model, but also by transforming from an object model. As DB-VA automates object-relational mapping, DB-VA supports the generation of database, code and persistence layer for Java model API and .NET model API, which in turn streamlines the model-code-deploy software development process.

Creating Data Model by 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.

The following section describes how you can depict the data model using the entity relationship diagram.

DB-VA provides you with two ways to create a Class Diagram:

1. Drawing an Entity Relationship Diagram (ERD)
2. Synchronizing from Object Model to Data Model

Drawing an Entity Relationship Diagram

You can create a new ERD in one of the three ways:

• On the menu, click File > New Diagram > Others > Entity Relationship Diagram.

Figure 4.1 - Create an ERD by click on menu

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

Figure 4.2 - Create ERD by click on Diagram Navigator

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

A new Entity Relationship Diagram pane is displayed.

Adding Entity

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

Figure 4.3 - Create an Entity by using the Entity icon

2. Click a location in the diagram pane.

DB-VA places an icon representing the entity element on the diagram.

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

Modifying Entity Specification

An entity specification displays the entity properties and constraints.

You can display the Entity Specification in one of the two ways:

• Click on an entity class, click the Open Specification resource located at the top-right corner of the entity.

Figure 4.4 - "Open Specification" resource

• Right-click the entity, click Open Specification...from the pop-up menu.

Figure 4.5 - Open specification by click on popup menu

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

Figure 4.6 - Entity Specification dialog

Adding Column

You can add a new column to the entity in one of the three ways:

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

Figure 4.7 - Add column by click on popup menu

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.
• Add column in Entity Specification dialog:
  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 4.8 - Entity Specification dialog

Modifying Column Specification

To open the Column Specification:

1. Right-click the column, click Open Specification...from the pop-up menu.

Figure 4.9 - To open the column specification

The Column Specification dialog box is displayed, you can modify the column properties.

Figure 4.10 - Column Specification dialog

Adding Relationship

Relationship shows how the entities are related to each other.

You can add a 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 is displayed around the shape.
  2. Mouse over the resource, select the desired resource, such as "One-to-One Relationship - > Entity".

  Figure 4.11 - "One-to-one Relationship -> Entity" resource

  3. Drag the resource to the related entity.

  Figure 4.12 - Drag the resource to the related entity

• 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 and drag to the related entity.

A connector is added between the two entities.

Figure 4.13 - an one-to-one relationship created

Modifying Relationship Specification

The relationship specification displays the relationship properties, such as name, phrase, and cardinality of the related entities.

To open the relationship specification dialog box:

1. 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 4.14 - Relationship Specification dialog

Synchronizing from Object Model to Data Model

DB-VA allows you to generate the ERD from a class diagram by synchronization as if there is a class diagram.

You can synchronize the Class Diagram to ERD in one of the three methods:

• On the menu, click Tools > Object-Relational Mapping (ORM) > Synchronize to Entity Relationship Diagram.

Figure 4.15 - Synchronize to ERD

• Right-click on the class diagram, select Synchronize to Entity Relationship Diagram.

Figure 4.16 - 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 4.17 - Synchronize to ERD by using Gesture

An entity relationship diagram is generated and can be found under the Diagram Navigator.

Figure 4.18 - The generated ERD

Specifying Primary Key

You can specify the column to be included in the primary key in one of the two ways:

• Specify Primary Key in Entity Specification dialog:
  1. Right-click on the entity, click Open Specification... to open the Entity Specification dialog box.
  2. Click the Columns tab, check the Primary Key option for the column that will be included in the primary key.

  Figure 4.19 - Entity Specification dialog

• Specify Primary Key in Column Specification dialog:
  1. Right-click the column, click Open Specification... to open the Column Specification dialog box.
  2. Check the Include in primary key option.

  Figure 4.20 - Column Specification dialog

If you assign a primary key to a column of an entity, DB-VA will automatically add a foreign key column to the related entities.

Figure 4.21 - The foreign key will be added automatically

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. DB-VA allows you to specify an index column to sort the collection.

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

Figure 4.22 - Open 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 4.23 - Select the index column

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, DB-VA provides you with the generation of primary key. 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 4.24 - Open the 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 4.25 - Select the ID Generator

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. DB-VA combines the entities within the hierarchy into one single entity containing all the attributes and a discriminator column for using table per class hierarchy as the inheritance strategy. The discriminator contains a unique value which is used for identifying the entity which hierarchy it belongs to.

DB-VA allows you to define the discriminator in the entity and the 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 4.26 - Create a column

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

Figure 4.27 - Enter the column name and data type

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

Figure 4.28 - 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 4.29 - Select the Discriminator Column

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 Details...from the pop-up menu. The Class Specification dialog box showing the ORM Class Detail tab is displayed.

Figure 4.30 - Open the ORM Class Detail

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

Figure 4.31 - Class Specification dialog (ORM Class Detail Tab)

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.

DB-VA promotes the idea of Array Table 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.

DB-VA allows you 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 4.32 - 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 4.33 - 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 DB-VA to resolve the constraints automatically. Click No to cancel the conversion to array table.

Figure 4.34 - Warning message for no index column

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

Figure 4.35 - Array Table created

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 4.36 - Add an attribute by click on popup menu

  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 4.37 - Enter the attribute name and data types

• Using Class Specification dialog box
  1. Right-click on a class, click Open Specification.

  Figure 4.38 - Open the class 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 4.39 - Select the Type modifier

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.

DB-VA promotes the idea of Split Table with stereotype of Partial 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.

DB-VA allows you 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.

1. 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 4.40 - Select "Split Table" in popup menu

   • 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 4.41 - Click on "One-to-one Relationship -> Partial Table" resource

2. Split Table dialog box is displayed.

Figure 4.42 - 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 4.43 - Partial Table is created

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 4.44 - Convert to Partial Table

The entity is stereotyped as <<Partial>>.

Figure 4.45 - Converted to a Partial Table

Copying SQL Statements from Tables

DB-VA provides function of copying SQL statements from the ERD entities. 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 DB-VA will generate the SQL statements according to the default database server type.

1. To configure database connection, on the menu, click Tools > Object-Relational Mapping (ORM) > Database Configuration....

Figure 4.46 - To open the Database Configuration

Database Configuration dialog box will be displayed. To configure the database, refer to the descriptions of the Database Configuration section in the Working with DB Visual ARCHITECT chapter for more information.

In DB-VA for IDE, you are allowed to configure the database connection by using Modeling > ORM > Database Configuration to display the Database Configuration dialog box.

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

If there are multiple database settings, DB-VA will generate SQL statements for all these database servers.

Example:

There are two database settings selected in the DB-VA environment.

Figure 4.47 - Two database settings Selected

2. Right-click on the ERD, select Generate SQL... from the pop-up menu.

Figure 4.48 - Select generate SQL in popup menu

3. 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 4.49 - Generate SQL dialog

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

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

Copying SQL Statements from Specified Scope

You can specify the scope on the ERD for DB-VA to generate the SQL statements.

You can specify one of the three scopes:

• All entities on the ERD
  1. Click on an entity.
  2. On the menu, select Edit > Select All of Same Type.
  3. Right-click on the entity, select Generate SQL....

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

Example:

Figure 4.50 - Generate SQL for all entity

Figure 4.51 - The generated SQL for all entities

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

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

Example:

Figure 4.52 - Generate SQL for selected entities

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

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

Example:

Figure 4.53 - Generate connection line's 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';

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