Previous Next
sde_java_ee_user_guide Chapter 16 -Database Schema

Chapter 16 - 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 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 16.1 - To generate database

    For other SDE:

    SDE Method
    SDE for JBuilder On the menu, click Tools > Modeling > ORM > Generate Database....
    SDE for NetBeans On the menu, click Modeling > ORM > Generate Database....
    SDE for IntelliJ IDEA On the menu, click Modeling > ORM > Generate Database....
    SDE for JDeveloper On the menu, click Model > ORM > Generate Database....
    SDE for WebLogic Workshop On the menu, click Modeling > ORM > Generate Database....
    Table 16.1

    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 16.2 - Database Code Generation dialog
  2. Select the option Export to database to allow altering the database.
    3. Figure 16.3 - Export to database checkbox
  3. Select the option Generate DDL to allow the generation of DDL file.
    4. Figure 16.4 - Generate DLL checkbox
  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 16.5 - Quote SQL Identifier options
  5. Select the type of connection from the drop-down menu of Connection, either JDBC or Datasource.
    6. Figure 16.6 - Connection Type
  6. Select the option for Use connection pool to enable using the connection pool for Java project.
    7. Figure 16.7 - Use connection pool
  7. Click Connection Pool Options button to open the Connection Pool Options dialog box to configure the connection pool settings.
    8. Figure 16.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 16.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 16.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 16.11 - Database Code Generation dialog
  2. Select the option for Export to database to allow altering the database.
    3. Figure 16.12 - Export to database checkbox
  3. Select the option for Generate DDL to allow the generation of DDL file.
    4. Figure 16.13 - Generate DDL checkbox
  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 16.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 16.15 - Generate ORM Code/Database

    he 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 16.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 16.2

Example:

Figure 16.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 16.18 - Mapping between Data Model and 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