Previous Next
sde-vs_orm_user_guide Chapter 19 - Database Schema

Chapter 19 - Database Schema

The database can be generated from either Entity Relationship Diagram or Class Diagram. You are allowed to configure the database connection and generate the database schema by exporting the Entity Relationship Diagram or Class Diagram to relational database. This chapter shows you how to generate the database and data definition language and describes how the data model maps the data definition language.

In this chapter:

Introduction

Database schema refers to the database structure while data definition language (DDL) is a database language which describes the data structure in a database; that is, the DDL is used to define the database schema. The DDL statements support the creation and destruction of a database and/or table.

As the visual modeling of data model is supported, database connection is allowed to configure to the working environment. According to the data model, the database and DDL can also be generated.

Generating Data Definition Language and Database

SDE-VS provides you with two ways to generate a relational database:

  1. Generating Database from ERD
  2. Generating Database from Class Diagram

Generating Database from Data Model

You can generate the database from data model in one of the two ways:

  1. Using Database Code Generation Dialog Box
  2. Using Wizard

Using Database Code Generation Dialog Box

  1. On the menu, click Modeling > ORM > Generate Database....
    2. Figure 19.1 - To edit the database configuration

    The Database Code Generation dialog box is displayed.

Generating Database for Java Project

For generating database for a Java project, configure the following options.

  1. Click Database Options button, Database Configuration dialog box is displayed.

    2. You are allowed to define the database configuration. Refer to the descriptions in the Database Configuration for Java Project in the Getting Started with Object-Relational Mapping chapter for information on how to configure the database for Java project.

    Figure 19.2 - Database Code Generation dialog
  2. Select the option Export to database to allow altering the database.
    3. Figure 19.3 - Export to database option
  3. Select the option Generate DDL to allow the generation of DDL file.
    4. Figure 19.4 - Generate DDL option
  4. Select the option for enabling Quote SQL Identifier from the drop-down menu. By enabling Quote SQL Identifier, the reserved word used in database can be used as ordinary word in generating database.
    5. Figure 19.5 - Quote SQL Identifier options
  5. Select the type of connection from the drop-down menu of Connection, either JDBC or Datasource.
    6. Figure 19.6 - Connection type
  6. Select the option for Use connection pool to enable using the connection pool for Java project.
    7. Figure 19.7 - Use connection pool option
  7. Click Connection Pool Options button to open the Connection Pool Options dialog box to configure the connection pool settings.
    8. Figure 19.8 - Connection Pool Options dialog
  8. Click OK, you will be prompted by the Generate ORM Code/Database dialog box showing the progress of database generation. Click Close when the progress is 100% finished.
    9. Figure 19.9 - Generate ORM Code/Database dialog

    The database tables are generated.

    You are allowed to generate the database by selecting Create Database, Update Database, Drop and Create Database or Drop Database from the drop down menu.

    Figure 19.10 - Generate database options
Generating Database for .NET Project

For generating database for a .NET project, configure the following options.

  1. Click Database Options button, Database Configuration dialog box is displayed.

    2. You are allowed to define the database configuration. Refer to the descriptions in the Database Configuration for .NET Project in the Getting Started with Object-Relational Mapping chapter for information on how to configure the database for .NET project.

    Figure 19.11 - Database Code Generation dialog
  2. Select the option for Export to database to allow altering the database.
    3. Figure 19.12 - Export to database option/td>
  3. Select the option for Generate DDL to allow the generation of DDL file.
    4. Figure 19.13 - Generate DDL option
  4. Select the option for enabling Quote SQL Identifier from the drop-down menu. By enabling Quote SQL Identifier, the reserved word used in database can be used as ordinary word in generating database.
    5. Figure 19.14 - Quote SQL Identifier options
  5. Click OK, you will be prompted by the Generate ORM Code/Database dialog box showing the progress of database generation. Click Close when the progress is 100% finished.
    6. Figure 19.15 - Generate ORM Code/Database dialog

    The database tables are generated.

    You are allowed to generate the database by selecting Create Database, Update Database, Drop and Create Database or Drop Database from the drop-down menu.

    Figure 19.16 - Generate Database options

Using Wizard

For information on generating database from data model by using wizard, refer to the descriptions in the Using ORM Wizard chapter.

Generating Database from Object Model

You can generate the database from object model by using wizard. For information, refers to the descriptions in the Using ORM Wizard chapter.

Mapping Data Model to Data Definition Language

As the database can be generated, it supports mapping from Data Model to Data Definition Language (DDL)

The following table shows the mapping between Data Model and DDL.

Data Model DDL
Entity Table
Column Column
Data Type Data Type
Primary Key Primary Key
Foreign Key Foreign Key
Table 19.1

Example:

Figure 19.17 - Entity Relationship Diagram

The following DDL statements are generated by the above data model:

create table `Department` (`ID` int not null auto_increment, `DeptName` varchar(255), primary key (`ID`))

create table `Staff` (`ID` int not null auto_increment, `Name` varchar(255), `Address` varchar(255), `Phone` varchar(255), `Email` varchar(255), `OfficeExtension` varchar(255), `DepartmentID` int not null, primary key (`ID`))

alter table `Staff` add index `FK_Staff_4271` (`DepartmentID`), add constraint `FK_Staff_4271` foreign key (`DepartmentID`) references `Department` (`ID`)

The following figure illustrates the mapping between Data Model and DDL.

Figure 19.18 - The Generated DDL

The Staff entity maps to Staff table of the DDL; the primary key of Department entity maps to DDL for creating "primary key (ID)" constraint; the column Name of Staff entity maps to a column Name of the staff table to be generated; the data type of DeptName, which is varchar maps to "varchar(255)" to the DDL; the column DepartmentID of Staff Entity maps to the DDL for creating a foreign key constraint.

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