Database Models

Database Modeling

ENTERPRISE ARCHITECT

User Guide Series

Author: Sparx Systems

Date: 2021-09-02

Version: 15.2

CREATED WITH

Table of Contents

Database Modeling 4

Geng Started 5

Example Diagram 7

Working with Data Model Types 8

Conceptual Data Model 9

Enty Relaonship Diagrams (ERDs) 10

Logical Data Model 14

Physical Data Models 15

DDL Transformaon 17

Creang and Managing Data Models 21

Create a Data Model from a Model Paern 22

Create a Data Model Diagram 24

Example Data Model Diagram 26

The Database Builder 27

Opening the Database Builder 29

Working in the Database Builder 31

Columns 35

Create Database Table Columns 36

Delete Database Table Columns 38

Reorder Database Table Columns 39

Constraints/Indexes 40

Database Table Constraints/Indexes 41

Primary Keys 44

Database Indexes 47

Unique Constraints 50

Foreign Keys 51

Check Constraints 55

Table Triggers 57

SQL Scratch Pad 59

Database Compare 61

Execute DDL 67

Database Objects 70

Database Tables 71

Create a Database Table 73

Database Table Columns 75

Create Database Table Columns 76

Delete Database Table Columns 78

Reorder Database Table Columns 79

Working with Database Table Properes 80

Set the Database Type 81

Set Database Table Owner/Schema 82

Set MySQL Opons 83

Set Oracle Database Table Properes 84

Database Table Constraints/Indexes 85

Primary Keys 88

Non Clustered Primary Keys 91

Database Indexes 92

Unique Constraints 95

Foreign Keys 96

Check Constraints 100

Table Triggers 102

Database Views 104

Database Procedures 106

Database Funcons 108

Database Sequences 110

Database SQL Queries 112

Create Operaon Containers 114

Oracle Packages 116

Database Connecons 117

Manage DBMS Opons 120

Data Types 122

Map Data Types Between DBMS Products 123

DBMS Product Conversion for a Package 124

Data Type Conversion For a Table 125

Database Datatypes 126

MySQL Data Types 128

Oracle Data Types 129

Data Modeling Sengs 130

Data Modeling Notaons 131

DDL Name Templates 136

Import Database Schema 138

Generate Database Denion Language (DDL) 142

Generate DDL For Objects 143

Edit DDL Templates 147

DDL Template Syntax 149

DDL Templates 150

Base Templates for DDL Generaon 151

Base Templates for Alter DDL Generaon 154

DDL Macros 155

Element Field Macros 156

Column Field Macros 159

Constraint Field Macros 160

DDL Funcon Macros 162

DDL Property Macros 167

DDL Opons in Templates 173

DDL Limitaons 176

Supported Database Management Systems 178

More Informaon 179

Database Modeling 2 September, 2021

Database Modeling

Design, Create and Manage Conceptual, Logical and Physical Data Models

The power of model-based system development is the ability to visualize, analyze and design all aspects of a system.

Being able to view and manage information and data alongside other models of a system provides great clarity and

reduces the chance of error. Enterprise Architect has extensive support for the data modeling discipline, ranging from the

representation of information in a conceptual model right down to the generation of database objects. Whether you are

generating database objects from the UML model or reverse engineering legacy DBMS into a model for analysis, the

tool features will save time and valuable project resources.

This illustration shows the Database Builder Interface including DDL Generation and the Foreign Key dialog.

Enterprise Architect supports the modeling of information at the conceptual, logical and physical layers. Using a number

of standard features, these models can be interconnected, providing traceability. The logical and physical models can also

be generated automatically using a fully customizable Transformation engine. Legacy systems can be imported, analyzed

and compared using the powerful reverse engineering facility.

In this topic you will learn how to use the powerful toolset including the Database Builder to design, create, manage,

visualize data including reverse and forward engineering of data models to live database.

The Database Builder tool can be used to create and maintain physical data models and can connect to a running

DBMS, so you can therefore import, generate, compare and alter a live database.

Database Modeling 2 September, 2021

Geng Started

Information Modelers, Data Modelers and Architects are responsible for creating models of an organization’s

information that span multiple levels of abstraction, from conceptual through to logical and physical. The conceptual

models are technology independent and can be used for discussions with business people and domain experts, allowing

the basic concepts in the domain to be represented, discussed and agreed upon. The logical model elaborates the

conceptual model, adding more detail and precision but is still typically technology neutral, allowing Information

Analysts to discuss and agree on logical structures. The physical model applies technology specific data to the models

and allows engineers to discuss and agree on technology decisions in preparation for generation to a target environment,

such as a database management system.

Selecng the Perspecve

Enterprise Architect partitions the tools extensive features into perspectives this ensures that you can focus on a specific

task and work with the tools you need without the distraction of other features. To work with the Data Modeling features

you first need to select one of the following perspectives:

Database Engineering > Database Engineering

Database Engineering > Entity Relationships

Setting the perspective ensures that the Case Management Model and Notation diagrams and their tool boxes and other

features of the perspective will be available by default.

Example Diagram

An example diagram provides a visual introduction to the topic and allows you to see some of the important elements

and connectors that are created in specifying or describing the way a data model is defined including: Tables, Views,

Procedures, Sequences, Functions.

Data Model Types

Information can be modeled at a number of level of abstraction starting with a conceptual model that is typically created

by or for business people, a Logical model which is used by business and systems analysts and a physical model which is

the concern of the technologists such as database engineers. In this topic you will learn how to manage all three levels of

information models.

Creang and Managing Data Models

In this topic you will learn how to work in detail using Enterprise Architect to manage your Physical Database schema.

This includes the use of the Database Builder tool which allows you to interact with any number of live database

through an ODBC connection.

Import Database Schema

Database Modeling 2 September, 2021

This topic will show you how to connect to a live database including Production, Test and Development systems and

reverse engineer the database into a model creating Tables, Views, Procedures, Declarative Referential Integrity and

more. A diagram of the database is automatically created and the elements such as tables can be related to other elements

in the model including Conceptual and Logical Models, Programming classes tests and more.

Generate Database Denion Language (DDL)

In this topic you will learn how to harness the power of the data models by generating Database Definition Language

code directly from the model. Enterprise Architect can generate code into a wide range of Database Management

systems.

Supported Database Management Systems

Enterprise Architect has rich support for most of the main stream Database Management Systems (DBMS). This feature

allows models from disparate systems to be compared either for code generation or for analysis by using the import

feature. This topic lists the supported DBMS and

More Informaon

This section provides useful links to other topics and resources that you might find useful when working with the Data

Modeling tool features.

Database Modeling 2 September, 2021

Example Diagram

Using the Database engineering features of Enterprise Architect you can create rich models of the objects that make up a

data model at any level of abstraction from Conceptual through Logical to Physical. These models are created by adding

tables and other database objects from the toolbox or by reverse engineering and existing database into a model from a

range of RDBMSs. A database diagram can contain Tables, VIews, Procedures, Sequences and Functions. Table

Columns are annotated as Primary and Foreign Keys are modeled using specialized association relationships. In this

example the user has created a simple physical data model of Customers and their Addresses and Orders.

customers

«column»

*PK id: bigserial

rst_name: varchar(50)

middle_name: varchar(50)

surname: varchar(50)

date_of_birth: date

phone_no: varchar(20)

mobile_no: varchar(20)

email: varchar(100)

comments: text

«PK»

+ pk_customers(bigserial)

«unique»

+ uq_customers_email(varchar)

«index»

+ ix_customers_surname(varchar)

+ ix_customers_mobile(varchar)

customers_addresses

«column»

*PK id: serial

FK customer_id: bigserial

billing_or_delivery: bit(1) = 1

acve: bit(1)

line_1: varchar(50)

line_2: varchar(50)

line_3: varchar(50)

city: varchar(100)

state_province: varchar(50) = 'Victoria'

zip_postcode: varchar(10)

country: varchar(100) = 'Australia'

comments: text

«FK»

+ _customersaddresses_customers(bigserial)

«index»

+ ix_customersaddresses_customers(bigserial)

«PK»

+ pk_customersaddresses(serial)

customers_orders

«column»

*PK id: bigserial

FK customer_id: bigserial

order_date: date

paid_date: date

shipped_date: date

total_price: double precision

special_instrucons: varchar(255)

consignment_no: varchar(50)

comments: text

«FK»

+ _customersorders_customers(bigserial)

«PK»

+ pk_customersorders(bigserial)

«unique»

+ uq_customersorders_consignment(varchar)

+_customersorders_customers

(customer_id = id)

«FK»

+pk_customers

+_customersaddresses_customers

(customer_id = id)

«FK»

+pk_customers

Physical Data model showing Tables with Columns and Primary and Foreign Keys.

Database Modeling 2 September, 2021

Working with Data Model Types

Enterprise Architect provides a number of features to assist in the process of creating models of information, including

the ability to develop conceptual, logical and physical models and to be able to trace the underlying concepts between the

models. The physical models can be developed for a wide range of database systems, and forward and reverse

engineering allows these models to be synchronized with live databases.

Data Models

Type Description

Conceptual Data Models Conceptual data models, also called Domain models, establish the basic concepts

and semantics of a given domain and help to communicate these to a wide audience

of stakeholders.

Conceptual models also serve as a common vocabulary during the analysis stages

of a project; they can be created in Enterprise Architect using Entity-Relationship

or UML Class models.

Logical Data Models Logical data models add further detail to conceptual model elements and refine the

structure of the domain; they can be defined using Entity-Relationship or UML

Class models.

One benefit of a Logical data model is that it provides a foundation on which to

base the Physical model and subsequent database implementation.

Entity-relationship modeling is an abstract and conceptual database modeling

method, used to produce a schema or semantic data model of, for example, a

relational database and its requirements, visualized in Entity-Relationship Diagrams

(ERDs).

ERDs assist you in building conceptual data models through to generating Data

Definition Language (DDL) for the target DBMS.

A Logical model can be transformed to a Physical data model using a DDL

Transformation.

Physical Data Models Physical data models in Enterprise Architect help you visualize your database

structure and automatically derive the corresponding database schema; you use

Enterprise Architect's UML Profile for Data Modeling specifically for this purpose.

The profile provides useful extensions of the UML standard that map database

concepts of Tables and relationships onto the UML concepts of Classes and

Associations; you can also model database columns, keys, constraints, indexes,

triggers, referential integrity and other relational database features.

Because Enterprise Architect helps you visualize each type of data model in the

same repository, you can easily manage dependencies between each level of

abstraction to maximize traceability and verify completeness of system

implementation.

Database Modeling 2 September, 2021

Conceptual Data Model

A Conceptual data model is the most abstract form of data model. It is helpful for communicating ideas to a wide range

of stakeholders because of its simplicity. Therefore platform-specific information, such as data types, indexes and keys,

is omitted from a Conceptual data model. Other implementation details, such as procedures and interface definitions, are

also excluded.

This is an example of a Conceptual data model, rendered using two of the notations supported by Enterprise Architect.

Entity Relationship diagram showing a One-to-Many relationship

Using Entity-Relationship (ER) notation, we represent the data concepts 'Customers' and 'Customers Addresses' as

Entities with a 1-to-many relationship between them. We can represent exactly the same semantic information using

UML Classes and Associations.

Unified Modeling Lnaguage diagram showing the same One-to_many Relationship

Whether you use UML or ER notation to represent data concepts in your project depends on the experience and

preferences of the stakeholders involved. The detailed structure of the data concepts illustrated in a Conceptual data

model is defined by the Logical data model.

Database Modeling 2 September, 2021

Enty Relaonship Diagrams (ERDs)

According to the online Wikipedia:

An entity-relationship model (ERM) is an abstract and conceptual representation of data. Entity-relationship modeling is

a database modeling method, used to produce a type of conceptual schema or semantic data model of a system, often a

relational database, and its requirements in a top-down fashion. Diagrams created by this process are called

Entity-Relationship Diagrams, ER Diagrams, or ERDs.

Enty Relaonship Diagrams in Enterprise Architect

Entity Relationship diagrams in Enterprise Architect are based on Chen's ERD building blocks: entities (tables) are

represented as rectangles, attributes (columns) are represented as ellipses (joined to their entity) and relationships

between the entities are represented as diamond-shape connectors.

ERD technology in Enterprise Architect assists you in every stage from building conceptual data models to generating

Data Definition Language (DDL) for the target DBMS.

MDG Technology for ERD and ERD Transformaons

Enterprise Architect enables you to develop Entity Relationship diagrams quickly and simply, through use of an MDG

Technology integrated with the Enterprise Architect installer.

The Entity Relationship diagram facilities are provided in the form of:

An Entity Relationship diagram type, accessed through the 'New Diagram' dialog·

An Entity Relationship Diagram page in the Diagram Toolbox·

Entity Relationship element and relationship entries in the 'Toolbox Shortcut' menu and Quick Linker·

Enterprise Architect also provides transformation templates to transform Entity Relationship diagrams into Data

Modeling diagrams, and vice versa.

Enty Relaonship Diagram Toolbox Page

You can access the 'Entity Relationship Diagram' page of the Diagram Toolbox by specifying 'Entity Relationship

Diagrams' in the Toolbox 'Find Toolbox Item' dialog

Database Modeling 2 September, 2021

Entity is an object or concept that is uniquely identifiable; the property of 'Multiplicity' in the SourceRole and·

TargetRole definitions for the Relationship connector can be used to define the cardinality of an Entity that

participates in this relationship

Attribute is a property of an entity or a relationship type·

N-ary Association represents unary (many-to-many recursive) or ternary relationships and can also be used to·

represent relationships that have attributes among the entities; the N-ary Association element should always be at the

target end of a connector

Connector is a connector between an Entity and an Attribute, and between two Attributes·

Relationship is a diamond-shape connector, representing the meaningful association among entities·

Disjoint and Overlapping represent the relationships between the super-class Entity and the sub-class Entity·

A typical Enty Relaonship diagram

Database Modeling 2 September, 2021

Tagged Values

Some of the Entity Relationship diagram components can be modified by Tagged Values, as indicated:

Component Tagged Value / Notes

Entity isWeakEntity

Notes: If true, this entity is a weak entity.

Attribute attributeType

Notes: There are four valid options: 'normal', 'primary key', 'multi-valued' and

'derived'

Attribute commonDataType

Notes: Defines the common data type for each attribute.

Database Modeling 2 September, 2021

Attribute dbmsDataType

Notes: Defines the customized DBMS data type for each attribute. This option is

only available when the commonDataType tag is set to 'na'.

You must define the customized type first through the 'Configure > Reference Data

> Settings > Database Datatypes' ribbon option.

N-ary Association isRecursive

Notes: If true, the N-ary Association represents the many-to-many recursive

relationship.

For one-to-many and one-to-one recursive relationships, we suggest using the

normal Relationship connector.

Sometimes you might want to limit the stretch of the diamond-shape Relationship

connectors; simply pick a Relationship connector, right-click to display the context

menu, and select the 'Bend Line at Cursor' option.

Relationship isWeak

Notes: If true, the Relationship is a weak relationship.

Disjoin

Overlapping

Participation

Notes: There are two valid options, 'partial' and 'total'.

Notes

Entity Relationship diagrams are supported in the Corporate, Unified and Ultimate Editions of Enterprise Architect·

Database Modeling 2 September, 2021

Logical Data Model

Logical data models help to define the detailed structure of the data elements in a system and the relationships between

data elements. They refine the data elements introduced by a Conceptual data model and form the basis of the Physical

data model. In Enterprise Architect, a Logical data model is typically represented using the UML Class notation.

Example

The following diagram is a simple example of a Logical data model. The Logical Model adds detail to the Conceptual

Model but without going to the level of specifying the Database Management System that will be used.

Conceptual Data Model with tables modeling Customers and their Addresses.

Note that the data elements 'Customers' and 'Customers Addresses' contain UML attributes; the names and generic data

types to remain platform-independent. Platform-specific data types and other metadata that relate to a specific DBMS

implementation are defined by the Physical data model.

Database Modeling 2 September, 2021

Physical Data Models

A Physical Data Model visually represents the structure of data as implemented by a relational database schema. In

addition to providing a visual abstraction of the database structure, an important benefit of defining a Physical Data

Model is that you can automatically derive the database schema from the model. This is possible due to the richness of

metadata captured by a Physical Data Model and its close mapping to aspects of the database schema, such as database

Tables, columns, Primary Keys and Foreign Keys.

Example Data Model

This example shows a Physical Data Model that could be used to automatically generate a database schema. Each Table

is represented by a UML Class; Table columns, Primary Keys and Foreign Keys are modeled using UML attributes and

operations. This model demonstrates the use of the Information Engineering connector style.

Notaon

The example model is defined using Enterprise Architect's UML Profile for Data Modeling; the relationship between the

Tables uses the default Information Engineering notation.

Information Engineering is one of three notations that Enterprise Architect supports to help Data Modelers identify

cardinality in relationships. You can change the notation by selecting the 'Design > Diagram > Manage > Properties'

ribbon option, clicking on the 'Connectors' page and selecting the required option in the 'Connector Notation' drop-down

list. You would most probably change the notation to IDEFX1, but the UML2.1 notation is also available.

Default DBMS

Prior to creating a Physical Data Model it is advisable for you to set the default DBMS for the project. Setting a default

DBMS ensures that all new database elements that are created on diagrams are automatically assigned the default

DBMS.

If the default DBMS is not set, new Tables are created without a DBMS assigned, this restricts Enterprise Architect's

ability to model the physical objects correctly. For example Enterprise Architect is unable to determine the correct list of

datatypes for columns.

You can set the default DBMS type using:

'Start > Desktop > Preferences > Preferences > Source Code Engineering > Code Editors', or·

'Configure > Reference data > Settings > Database Datatypes or·

Database Modeling 2 September, 2021

'Develop > Data Modeling > Data Types or·

The second data entry field in the Code Generation Toolbar·

Note: When modeling via the Database Builder the default DBMS is defined at the model level (as a Tagged Value

'DBMS' against the <<Database>> Package) instead of at the project level, thereby allowing for greater flexibility when

projects involve multiple DBMSs.

Database Modeling 2 September, 2021

DDL Transformaon

The DDL transformation converts the logical model to a data model structured to conform to one of the supported

DBMSs. The target database type is determined by which DBMS is set as the default database in the model (see the

Database Datatypes Help topic, 'Set As Default' option). The data model can then be used to automatically generate

DDL statements to run in one of the system-supported database products.

The DDL transformation uses and demonstrates support in the intermediary language for a number of database-specific

concepts.

Concepts

Concept Effect

Table Mapped one-to-one onto Class elements.

'Many-to-many' relationships are supported by the transformation, creating Join

tables.

Column Mapped one-to-one onto attributes.

Primary Key Lists all the columns involved so that they exist in the Class, and creates a Primary

Key Method for them.

Foreign Key A special sort of connector, in which the Source and Target sections list all of the

columns involved so that:

The columns exist·

A matching Primary Key exists in the destination Class, and·

The transformation creates the appropriate Foreign Key·

MDG Technology to customize default mappings

DDL transformations that target a new, user defined DBMS require an MDG Technology to map the PIM data types to

the new target DBMS.

To do this, create an MDG Technology .xml file named 'UserDBMS Types.xml', replacing UserDBMS with the name of

the added DBMS. Place the file in the EA\MDGTechnologies folder. The contents of the MDG Technology file should

have this structure:

<MDG.Technology version="1.0">

<Documentation id="UserdataTypes" name="Userdata Types" version="1.0" notes="DB Type mapping for

UserDBMS"/>

<CodeOption name="DBTypeMapping-bigint">BIGINT</CodeOption>

<CodeOption name="DBTypeMapping-boolean">TINYINT</CodeOption>

Database Modeling 2 September, 2021

...

</CodeOptions>

</CodeModule>

</CodeModules>

</MDG.Technology>

As an example, 'text' is a Common Type (as listed in the 'Database Datatypes' dialog) that maps to a new DBMS's

'CLOB' data type.

Notes

You can define DBMS-specific aspects not depicted in a Logical model, such as Stored Procedures, Triggers, Views·

and Check Constraints, after the transformation; see the Physical Data Model Help topic

Example

The PIM elements

Database Modeling 2 September, 2021

After transformation, become the PSM elements

Generalizations are handled by providing the child element with a Foreign Key to the parent element, as shown.

Copy-down inheritance is not supported.

Database Modeling 2 September, 2021

Creang and Managing Data Models

Enterprise Architect is a fully featured database modeling platform that enables the user to work with their Physical Data

models at all stages, from design right through to the implementation of the live database, for a wide range of database

management systems such as Microsoft SQL Server, Oracle, PostgreSQL and MySQL.

This figure shows the starter model wizard patterns for database design for a range of RDBMS.

Database Modeling 2 September, 2021

Create a Data Model from a Model Paern

The easiest way to create a Data Modeling workspace is to use the predefined Database Model Patterns, available

through the Model Wizard. Enterprise Architect provides a Pattern for each DBMS supported by the system.

Access

Display the Model Wizard window using any of the methods outlined here.

In the Model Wizard window, select the 'Model Patterns' tab. In the 'Technology' panel, select 'Database'.

Ribbon

Design > Model > Add > Model Wizard

Context Menu Right-click on Package | Add a Model Using Wizard

Keyboard Shortcuts

Ctrl+Shift+M

Other

Browser window caption bar menu | New Model from Pattern

Create a Data Model

Field/Button Action

Add to Package Displays the name of the selected root Package.

Technology Click on 'Database'.

Name If necessary, expand the Database Engineering group of Patterns.

Click on the checkbox against each Database Management System you are

supporting in the model.

All Click on this button to select the checkboxes for all Database Engineering model

types and the Entity Relationship diagram, to include them all in the model.

None Click on this button to clear all selected checkboxes so that you can re-select

certain checkboxes individually.

Click on this button to add to the Browser window the Packages and diagram for

each Database Management System you are modeling.

What each Data Modeling Paern provides

A summary diagram of the model·

A Report Specification Artifact element (on the summary diagram) that can be used to quickly document the data·

model

Database Modeling 2 September, 2021

A Package for each of the Logical and Physical models·

Within the Physical Model Package, a predefined hierarchy of sub-Packages, one for each object type supported by·

the DBMS being modeled (such as Tables, Views, Procedures and Functions); these automatically organize the

database objects as they are added

The DBMS type for the workspace·

A default owner·

A Data Modeling diagram in each Package with the connector notation set to IDEF1X·

Notes

Once a data modeling workspace has been created, you can begin to develop your model in one of two ways:·

- Through the Database Builder, which is a purpose-built view that supports database modelers

- Through the Browser window and diagrams, which is the traditional method that might suit users

who are experienced UML modelers

Database Modeling 2 September, 2021

Create a Data Model Diagram

To model the structure of a relational database you use Data Modeling diagrams, which are extended Class diagrams.

When you open a Data Modeling diagram the matching Diagram Toolbox is automatically opened, which contains the

diagram elements:

Table·

View·

Procedure·

Sequence·

Function·

Association and·

Database Connection·

Access

Display the 'New Diagram' dialog using any of the methods outlined here.

Ribbon Design > Diagram > Add

Context Menu Right-click on Package | Add Diagram

Right-click on element | Add | Add Diagram

Keyboard Shortcuts

Ctrl+Insert

Other

Browser window caption bar menu | New Diagram

Create a Data Modeling diagram

Field/Button Action

Package

Defaults to the name of the Package selected in the Browser window or, if the

parent is an element, the name of the Package containing that element.

If you are adding a diagram directly to a Package and notice that it is not the correct

Package, click on the button and browse for the correct Package.

Parent If you are adding a diagram to an element, this field displays the element name.

Diagram This field defaults to the name of the parent Package or element.

If required, overtype the default name with your preferred name.

Select From Click on this header and select the Perspective Group and Perspective or

Workspace most appropriate to the area you are working in (in this instance,

'Information Engineering > Database Models').

From the options listed in the panel, click on 'Extended'.

Database Modeling 2 September, 2021

Diagram Types Click on 'Data Modeling'.

OK Click on this button to create the diagram.

The Diagram View displays the blank diagram, and the 'Data Modeling' pages

display in the Diagram Toolbox.

Drag elements and connectors from the Toolbox onto your diagram, to create your

data model.

Notes

The default diagram connector notation for all new diagrams is Information Engineering, although many data·

modelers prefer the notation IDEF1X; to make this change select 'Design > Diagram > Manage > Properties >

Connectors' and click on the required option in the 'Connector Notation' drop-down list

Database Modeling 2 September, 2021

Example Data Model Diagram

This example of a Data Model diagram shows a data model of a bookstore warehousing system. The tables are modeled

using a stereotyped class with a compartment for Columns which displays the name and type of the Columns. Primary

and Foreign Keys are indicated by stereotypes on the columns. You can examine this model in greater detail in the

Example model, installed with Enterprise Architect and available from the following ribbon location.

Start > Help > Help > Open the Example Model

suppliers

«column»

*PK supplier_code: varchar(20)

supplier_name: varchar(50)

contact_name: varchar(100)

contact_phone_no: varchar(20)

contact_email: varchar(100)

address_line1: varchar(50)

address_line2: varchar(50)

address_line3: varchar(50)

city: varchar(100) = 'Victoria'

zip_postcode: varchar(10)

country: varchar(50) = 'Australia'

comments: text

customers

«column»

*PK id: bigserial

rst_name: varchar(50)

middle_name: varchar(50)

surname: varchar(50)

date_of_birth: date

phone_no: varchar(20)

mobile_no: varchar(20)

email: varchar(100)

comments: text

customers_addresses

«column»

*PK id: serial

FK customer_id: bigserial

billing_or_delivery: bit(1) = 1

acve: bit(1)

line_1: varchar(50)

line_2: varchar(50)

line_3: varchar(50)

city: varchar(100)

state_province: varchar(50) = 'Victoria'

zip_postcode: varchar(10)

country: varchar(100) = 'Australia'

comments: text

customers_orders

«column»

*PK id: bigserial

FK customer_id: bigserial

order_date: date

paid_date: date

shipped_date: date

total_price: double precision

special_instrucons: varchar(255)

consignment_no: varchar(50)

comments: text

customers_orders_items

«column»

*pfK customerorder_id: bigint

*pfK product_id: bigint

quanty: double precision

unit_price: double precision

tax_amount: double precision

discount_percentage: double precision

comments: text

customers_orders_statuses

«column»

*PK id: bigserial

FK customerorder_id: bigint

status_date: date

status_code: varchar(10)

comments: text

products

«column»

*PK id: bigserial

short_descripon: varchar(255)

unit_price: double precision

long_descripon: text

FK supplier_code: varchar(20)

supplier_partno: varchar(50)

comments: text

+_customersorders_customers

+pk_customers

+_products_suppliers

+pk_suppliers

+_customersordersstatuses_customersorders

+pk_customersorders

+_customersordersitems_products

+pk_products

+_customersordersitems_customersorders

+pk_customersorders

+_customersaddresses_customers

+pk_customers

Data modeling diagram with suppressed operation compartment showing tables connected to indicate foreign key

relationships.

Database Modeling 2 September, 2021

The Database Builder

The Database Builder is a tailored interface for the data modeler; all database-related modeling tasks can be performed

in a single location. The interface and its related screens include only the information relevant to data modeling, thereby

streamlining and simplifying the modeling process.

Access

Ribbon

Develop > Data Modeling > Database Builder

Database Builder

This figure shows the Database Builder loaded with the 'Orders (postgres)' data model as it appears in the Example

model.

Overview

The interface of the Database Builder consists of:

A Tree of data models, listing all defined data models in the current repository·

A 'Columns' tab through which you directly manage the Table columns·

A 'Constraints/Indexes' tab for the direct management of Table constraints such as Primary Keys, Foreign Keys and·

Indexes

An SQL Scratch Pad that you can use to run ad-hoc SQL queries against a live database·

A 'Database Compare' tab that displays the results of comparisons between the data model and a live database·

Database Modeling 2 September, 2021

An 'Execute DDL' tab on which you can execute generated DDL against a live database, instantly·

You can use the Database Builder to:

Create, edit and delete database objects (Tables, Views, Procedures, Sequences and Functions)·

Create, edit and delete Table constraints (Primary Keys, Indexes, Unique Constraints, Check Constraints and·

Triggers)

Create, edit and delete Table Foreign Keys·

Reverse engineer database schema information·

Generate DDL from a modeled database·

Compare a live database schema with a modeled database·

Execute generated DDL against a live database·

Execute adhoc SQL statements against a live database·

Notes

The Database Builder is available in the Corporate, Unified and Ultimate Editions of Enterprise Architect·

Database Modeling 2 September, 2021

Opening the Database Builder

When you first open the Database Builder, it searches the entire project for all Packages that have the stereotype

<<Data Model>> and loads the corresponding data models as root nodes into the tree. A grayed-out icon indicates that

the details of the data model are not loaded.

This figure shows the Database Builder with a single unloaded data model called 'Orders (postgres)'.

Using the Database Builder

You can start working in the Database Builder in one of these two ways:

Task Action

Create a new data model

Once you have opened the Database Builder view, right-click in the empty space

of the tree and select 'New Data Model' to invoke the Model Wizard.

Load an existing Data

Model

Once the Database Builder view is opened, load any of the defined data models by

either:

Right-clicking on the name and selecting 'Load', or·

Double-clicking on the name·

Data Model Properes

In earlier versions of Enterprise Architect (prior to the introduction of the Database Builder) it was necessary for the

data modeler to manually set properties on database objects before some tasks were allowed. For example, Enterprise

Architect would not allow the definition of a Table column without the Table first being assigned a DBMS. This was

because the DBMS controls the list of available datatypes.

To improve efficiency and the user experience, the Database Builder defines defaults for a number of properties at the

data model level and then applies these default values automatically whenever new objects are created.

Properes

Option Description

DBMS Defined against: The data model's <<Database>> Package

Defined as: Tagged Value

Details: Defines the DBMS of the current data model

Extra Information:

Database Modeling 2 September, 2021

Controls which logical folders are shown for the current data model in the·

Database Builder's tree

Controls what DBMS rules are applied during database comparisons·

Is automatically assigned to every new database object created in the current·

data model

DefaultOwner Defined against: The data model's <<Database>> Package

Defined as: Tagged Value

Details: Defines the default Owner for the current data model

Extra Information:

Is automatically assigned to every new database object created in the current·

data model, if the DBMS supports owners/schemas

DefaultConnection Defined against: The data model's <<Database>> Package

Defined as: Tagged Value

Details: (Optional) the name of the default connection

Extra Information:

Whenever a data model is loaded, the 'DefaultConnection' property is checked;·

if present, the Connection by that name is automatically made active

The database engineering model Patterns do not define a value for this·

property, it is created or updated whenever a user sets a Connection as the

default

Notes

If a data model is selected in the Browser window when the Database Builder is opened, that model's details will·

be automatically loaded

Database Modeling 2 September, 2021

Working in the Database Builder

When a data model is loaded, the Database Builder creates a set of logical folders, one for each object type supported

by the current DBMS. Each logical folder is populated with all objects of that type found in the data model's hierarchy of

Packages (as shown in the Browser window).

In this image the data model 'Orders (postgres)' shows logical folders for Tables, Views, Functions, Sequences, Queries

and Connections. It is worth noting there is no folder for 'Procedures' since PostgreSQL does not support database

procedures.

Available Acons in the Database Builder Tree

The majority of the Database Builder functions are accessible via context menus. Each object in the Tree has its own set

of unique menu items based on its type and status. This table describes the available context menu items and identifies

which objects they apply to.

Menu Option Applies to / Description

New data model Applies To: Blank Space

Description: Opens the Model Wizard screen.

Refresh All Applies to: Blank Space

Description: Reloads the complete list of data models.

Load Applies to: Root Node

Database Modeling 2 September, 2021

Description: Loads the full details of the data model.

Unload Applies to: Root Node

Description: Unloads the full details of the data model.

Import DB Schema from

ODBC

Applies to: Loaded Root Node

Description: Opens the 'Import DB schema from ODBC' dialog using the current

active connection as the ODBC source.

Generate DDL Applies to: Loaded Root Node, Folder, Table, View, Procedure, Function,

Sequence, Package

Description: Opens the 'Generate DDL' dialog with the current object(s) selected.

Show Differences Applies to: Loaded Root Node, Folder, Table, View, Procedure, Function,

Sequence

Description: Compares the selected objects to the current active connection.

Show Differences with

Options

Applies to: Loaded Root Node, Folder, Table, View, Procedure, Function,

Sequence, Package

Description: Compares the selected objects to the current active connection and

optionally ignore some of the differences based on the specified compare options.

Manage DBMS Options Applies to: Loaded Root Node

Description: Opens the 'Manage DBMS Options' dialog, which can be used to

change the allocated DBMS and Owner of multiple objects.

View Record Count Applies to: Table, View

Description: Builds and runs a SELECT query (formatted to suit the element's

DBMS) to show the number of records in the selected Table or View.

If there is no active connection, you are prompted to select one.

View Top 100 Rows Applies to: Table, View

Description: Builds and runs a SELECT query (formatted to suit the element's

DBMS) to show the top 100 rows of the selected Table or View.

If there is no active connection, you are prompted to select one.

View Top 1000 Rows Applies to: Table, View

Description: Builds and runs a SELECT query (formatted to suit the element's

DBMS) to show the top 1000 rows of the selected Table or View.

If there is no active connection, you are prompted to select one.

View All Rows Applies to: Table, View

Description: Builds and runs a SELECT query (formatted to suit the element's

DBMS) to show all rows of the selected Table or View.

If there is no active connection, you are prompted to select one.

Properties Applies to: Loaded Root Node, Folder, Table, View, Procedure, Function,

Sequence, Package, Connection

Description: Opens the standard 'Properties' dialog for the selected object.

Find in Project Browser Applies to: Loaded Root Node, Folder, Table, View, Procedure, Function,

Database Modeling 2 September, 2021

Sequence, Package, SQL Query, Connection

Description: Finds the selected object in the Browser window.

Refresh Applies to: Loaded Root Node

Description: Reloads the details of the current loaded data model. This is necessary

when objects are added, changed or deleted by other users or when the changes are

performed outside of the Database Builder.

Add new <type> Applies to: Folder, Table, View, Procedure, Function, Sequence, Package, SQL

Query, Connection

Description: Creates a new object of the specified type.

Clone <name> Applies to: Folder, Table, View, Procedure, Function, Sequence, Package, SQL

Query, Connection

Description: Makes a new copy of the selected object. When you select this option,

a prompt displays on which you set the name and owner of the new object. For

Table objects, you can choose which existing constraints should be copied (and set

a name for each one) along with which Foreign Keys should be copied. For

SQL-based objects, you can make any necessary changes to the SQL for the new

element.

Delete <name> Applies To: Table, View, Procedure, Function, Sequence, Package, SQL Query,

Connection

Description: Permanently deletes the selected object from the repository.

Add new Foreign Key on

<name>

Applies to: Table

Description: Creates a new relationship between the selected Table and another

one, then shows the 'Foreign Key Constraint' screen for the new relationship.

SQL Object Properties Applies to: View, Procedure, Function, Sequence

Description: Opens the 'SQL Object Editor' screen.

Edit Applies to: SQL Query

Description: Loads the SQL (as defined in the selected element) into the SQL

Scratch Pad.

Run Applies to: SQL Query

Description: Loads the SQL in the SQL Scratch Pad and runs it.

If there is no active connection, you are prompted to select one.

Set as active DB

Connection

Applies to: Connection

Description: Flags the selected Database Connection as the active one for the

current session.

Set as Default DB

Connection

Applies to: Connection

Description: Flags the selected Database Connection as the active one each time the

data model is loaded.

DB Connection Properties Applies to: Connection

Description: Opens the 'Database Connection Properties' screen, to manage the

connection settings.

Database Modeling 2 September, 2021

Create/Edit/Delete Database Objects

The pages listed in this section describe in detail how to use the Database Builder's interface to create and manipulate

database Tables; however, the process of creating and manipulating SQL-based database objects is documented in other

areas. See these topics for details:

Database Views·

Database Procedures·

Database Functions·

Database Sequences·

Database Connections·

Database Connecons in the Database Builder

When performing certain tasks such as 'Compare' or 'Execute DDL', the Database Builder requires an active database

connection. Only one database connection can be made active (indicated by a colored 'Database Connection' icon, while

the others are gray) at a given time. If a database connection is not currently active and you try to perform a task that

requires one, the Database Builder performs one of these actions based on how many connections are defined:

0 Connections – prompts you to create a connection and, if successful, continues·

1 Connection – sets it as active and continues·

2 (or more) Connections – prompts you to select one and, if successful, continues·

Database Modeling 2 September, 2021

Columns

Tables are the fundamental database object, and Columns (and their properties) are the most frequently modified Table

feature updated and changed by data modelers, therefore the 'Columns' page is conveniently located as the first page of

the Database Builder's interface.

Once a Table is selected in the Database Builder's tree, the 'Columns' page is populated with the currently defined list of

columns for that Table. The data modeler can then make changes to main column properties directly in the list or grid.

As the data modeler selects individual columns in the list, the column's extended properties (and Comments) are shown

immediately under the list, allowing modification to these extended properties.

This figure show the Database Builder interface showing the tree of objects and the Columns tab showing the Columns

for the selected table.

Notes

The 'Columns' page will only be populated when a Table item is selected in the Database Builder's tree·

Database Modeling 2 September, 2021

Create Database Table Columns

A database Table column is represented in the UML Data Modeling Profile as an attribute with the <<column>>

stereotype. For a selected Table, you can review the existing columns and create new columns, on the 'Columns' page of

the Database Builder or on the 'Columns and Constraints' screen.

You can define column details directly on the list of columns on the 'Columns' tab. The changes are automatically saved

as you complete each field. Some fields have certain restrictions on the data you can enter, as described here. The tab

also contains a 'Properties' panel and a 'Notes' field, which are populated with the existing information on the selected

column. Each new column that you create is automatically assigned a set of default values and added to the bottom of the

list.

Access

Ribbon

Develop > Data Modeling > Database Builder > Click on Table > Columns >

Right-Click > Add new Column

Context Menu In diagram, right-click on required Table | Features | Columns | Right-Click | Add

new Column

Keyboard Shortcuts

Select a table | F9 | Tab Key (to set input focus on the 'Columns' tab) | Ctrl+N

Create columns in a Table

Option Action

Name Overtype the default name with the appropriate column name text.

Type

Click on the drop-down arrow and select the appropriate datatype for the column.

The available datatypes depend on the DBMS assigned to the parent Table.

Length (Optional) Some datatypes have a length component - for example, VARCHAR has

a length that defines the number of characters that can be stored. If the datatype

does not have a length component, this field is disabled.

If the field is available and if you need to define a number of characters, type the

value here.

Scale (Optional) Some datatypes have a scale component - for example, DECIMAL has a

scale that defines the number of decimal places that can be held. If the datatype

does not have a scale component, this field is disabled.

If the field is available and if you need to define a scale, type the value here.

PK Select the checkbox if the column is part of the Primary Key for this Table.

Not Null Select the checkbox if empty values are forbidden for this column.

The checkbox is disabled if the 'PK' checkbox is selected.

Database Modeling 2 September, 2021

Alias If required for display and documentation purposes, type in an alternative name for

the field.

Initial Value If required, type in a value that can be used as a default value for this column.

Notes Type in any additional information necessary to document the column.

You can format the text using the Notes toolbar at the top of the field.

Column Properes

The appropriate properties for the Table's Database Management System automatically display in the 'Property' panel

(expand the 'Column (<name>)' branch if they are not visible).

Property DBMS

Autonum (Startnum

Increment)

Oracle

MySQL

SQL Server

DB2

PostgreSQL

Notes: If you require an automatic numbering sequence, set this property to True

and, if necessary, define the start number and increment.

Generated DB2

Notes: Set this additional property for auto numbering in DB2, to 'By Default' or

'Always'.

NotForRep SQLServer

Notes: Set this property to True if you want to block replication.

Zerofill MySQL

Notes: Set this property to True or False to indicate if fields are zerofilled or not.

Unsigned MySQL

Notes: Set this property to True or False to indicate whether or not fields accept

unsigned numbers.

LengthType Oracle

Notes: Set this property to define the character semantics as 'None', 'Byte' or 'Char'.

Database Modeling 2 September, 2021

Delete Database Table Columns

For a selected database Table, you can review the existing columns and delete any individual column, on the 'Columns'

tab of the Columns and Constraints screen.

Access

Use one of the methods outlined here to display a list of columns for a table, then select a column and delete it.

When you select the 'Delete column '<name>'' option, if all validation rules are satisfied the column is immediately

deleted.

Ribbon

Develop > Data Modeling > Database Builder > Click on Table > Columns >

Right-click on column name > Delete column <name>

Context Menu In diagram, right-click on required Table | Features | Columns | Right-click on

column name | Delete column <name>

Keyboard Shortcuts

F9 | Use 'Up Arrow' or 'Down Arrow' to select a column | Ctrl+D

Notes

If the deleted database Table column is involved in any constraints it will automatically be removed from them·

Database Modeling 2 September, 2021

Reorder Database Table Columns

If you have several columns defined in a database Table, you can change the order in which they are listed. The order in

the list is the order in which the columns appear in the generated DDL.

Access

Use one of the methods outlined here to display a list of columns for a Table, then select a column and reposition it

within the list.

Ribbon

Develop > Data Modeling > Database Builder > Click on Table

Context Menu In diagram, right-click on required Table | Features | Columns

Keyboard Shortcuts

Change the column order

Step Action

1 In the 'Columns' tab, click on the required column name in the list.

2 Right-click and select the:

'Move column <name> up' option (or press Ctrl+Up Arrow) to move the column up one position·

'Move column <name> down' option (or press Ctrl+Down Arrow) to move the column down one·

position

These options have an immediate effect both in the 'Columns' tab and on a diagram.

Database Modeling 2 September, 2021

Constraints/Indexes

Tables are the fundamental database object, and Constraints and Indexes (and their properties) are the second most

frequently modified Table feature updated and changed by data modelers, therefore the 'Constraints/indexes' page is

conveniently located as the second page of the Database Builder's interface.

Once a Table is selected in the Database Builder's tree, the 'Constraints/Indexes' page is populated with the currently

defined list of constraints and indexes for the selected Table. The data modeler can then make changes to main properties

directly in the list. As the data modeler selects individual constraints or indexes in the list, the constraint's extended

properties (and Comments) are shown immediately under the list, allowing modification of these extended properties.

This figure show the Database Builder interface showing the tree of objects and the Columns tab showing the Columns

for the selected table.

Notes

The 'Constraints/Indexes' page will only be populated when a Table item in the Database Builder's tree is selected·

Database Modeling 2 September, 2021

Database Table Constraints/Indexes

Within Enterprise Architect, Table Constraints and Indexes are modeled on the same screen; collectivity they are referred

to as Constraints. Database Constraints define the conditions imposed on the behavior of a database Table. They include:

Primary Key - uniquely identifies a record in a Table, consisting of one or more columns·

Index - improves the performance of retrieval and sort operations on Table data·

Unique Constraints - a combination of values that uniquely identify a row in the Table·

Foreign Key - a column (or collection of columns) that enforce a relationship between two Tables·

Check Constraints - enforces domain integrity by limiting the values that are accepted by a column·

Table Trigger - SQL or code automatically executed as a result of data in a Table being modified·

In Enterprise Architect, you can define and maintain Table Constraints using either the purpose-designed

'Constraints/Indexes' page of the Database Builder or the Columns and Constraints screen.

Access

Ribbon

Develop > Data Modeling > Database Builder > Click on Table name >

Constraints/Indexes | Right-click | Add New Constraint

Add New Constraint

Keyboard Shortcuts

Click on Table: F9 > Constraints/Indexes: Ctrl+N

Create a Constraint

The process of creating any of these constraint types is the same and is achieved in one of the ways described here.

Create a Constraint - Using the context menu or keyboard

Step Action

1 A new constraint is automatically created and assigned the default name constraint n (where n is a

counter) and a 'Type' of 'index'.

Overtype the default name with your own constraint name.

If necessary, in the 'Type' field click on the drop-down arrow and select the appropriate constraint type.

3 If you prefer, type an alias for the constraint, in the 'Alias' field.

The 'Columns' field is read-only; it is populated with the columns that you assign to the 'Involved

Columns' tab.

Database Modeling 2 September, 2021

Create a Constraint - Overtype the template text

Step Action

1 On the 'Constraints/Indexes' tab for the selected Table, the list of constraints ends with the template text

New Constraint.

Overtype this text with the appropriate constraint name, and press the Enter key.

2 The new constraint is automatically created and assigned the default Type of index.

If necessary, in the 'Type' field click on the drop-down arrow and select the appropriate constraint type.

3 If you prefer, type an alias for the constraint, in the 'Alias' field.

The 'Columns' field is read-only; it is populated with the columns that you assign to the 'Involved

Columns' tab.

Assign Columns to a Constraint

The constraint types of Primary Key, Foreign Key, Index and Unique all must have at least one column assigned to them;

this defines the columns that are involved in the constraint.

Step Action

1 On the 'Constraints/Indexes' tab for the selected Table, click on the constraint to which you are assigning

columns.

2 The 'Available Columns' panel lists all columns defined for the Table.

For each column to assign to the constraint, right-click on the column name and select 'Assign column

<name>'.

The column name is transferred to the 'Assigned Columns' list.

Unassign Columns from a Constraint

Step Action

1 On the 'Constraints/Indexes' tab for the selected Table, click on the constraint from which you are

unassigning columns.

2 In the 'Assigned Columns' list, right-click on the name of the column to unassign from the constraint and

select 'Unassign column <name>'.

The column name is transferred to the 'Available Columns' list.

Reorder the Assigned Columns in a Constraint

Database Modeling 2 September, 2021

If you have a number of columns in the constraint, you can re-arrange the sequence by moving a selected column name

one place up or down the list at a time. To do this:

Right-click on the column name to move and select either:·

- Move column '<name>' up (Ctrl+Up Arrow) or

- Move column '<name>' down (Ctrl+Down Arrow)

Delete a constraint

To delete a constraint you no longer require, right-click on the constraint name in the list on the 'Constraints/Indexes' tab

and select the 'Delete constraint <name>' option. If all validation rules for the given constraint type are met, the

constraint is immediately removed from the repository along with all related relationships (if there are any).

Database Modeling 2 September, 2021

Primary Keys

A Primary Key is a column (or set of columns) that uniquely identifies each record in a Table. A Table can have only one

Primary Key. Some DBMSs support additional properties of Primary Keys, such as Clustered or Fill Factor.

Access

Ribbon

Develop > Data Modeling > Database Builder > Click on Table name

Context Menu In diagram | Right-click on Table | Features | Constraints/Indexes

Create a Primary Key

In Enterprise Architect you can create a Primary Key from either the 'Columns' tab or the 'Constraints/Indexes' tab. In

either case, when you add a column to a Primary Key constraint, the column is automatically set to be 'Not Null'.

Additionally any diagram (assuming the 'Show Qualifiers and Visibility Indicators' option is set) containing the Table

element will show the 'PK' prefix against the column name. In this image, see the first column 'id: bigserial'.

Create a Primary Key - from the Columns tab

Database Modeling 2 September, 2021

Step Action

1 Either:

In the Database Builder, click on a Table with one or more defined columns, and click on the·

'Columns' tab, or

On a diagram, click on a Table and press F9 to display the 'Columns' tab·

2 For each column to include in the Primary Key, select the 'PK' checkbox.

If a Primary Key constraint is not previously defined for the current Table, the system will create a new

constraint using the Primary Key Name template.

Create a Primary Key - from the Constraints tab

Step Action

1 Either:

In the Database Builder, click on a Table with one or more defined columns, and click on the·

'Constraints/Indexes' tab, or

On a diagram, click on a Table and press F10 to display the 'Constraints/Indexes' tab·

Overtype the New Constraint text with the Primary Key name, press the Enter key and click on the 'Type'

field drop-down arrow, and select 'PK'.

3 Assign the required columns to the PK constraint.

4 Set the Primary Key's extended properties using the property panel.

Fill Factor is a numeric value between 0 and 100·

Is Clustered is a Boolean value that determines the physical order of how the data is stored; for most·

DBMSs the Is Clustered property defaults to True for Primary Keys

Remove columns from a Primary Key

You can remove columns from a Primary Key using either the 'Columns' tab or the 'Constraints/Indexes' tab.

Remove columns from a Primary Key - using the Columns tab

Step Action

1 Either:

In the Database Builder, click on the Table with the Primary Key, and click on the 'Columns' tab, or·

On a diagram, click on a Table and press F9 to display the 'Columns' tab·

2 Against each column you want to remove from the Primary Key, deselect the 'PK' checkbox.

Database Modeling 2 September, 2021

If you have removed all columns from the Primary Key constraint and the Primary Key is no longer

needed, it must be manually deleted.

Remove columns from a Primary Key - using the Constraints/Indexes tab

Step Action

1 Either:

In the Database Builder, click on the Table with the Primary Key, and click on the·

'Constraints/Indexes' tab, or

On a diagram, click on a Table and press F10 to display the 'Constraints/Indexes' tab·

2 Unassign the columns on the PK constraint, as necessary.

Notes

Warning: Enterprise Architect assumes that Primary Key constraints have at least one column assigned to them;·

however, Enterprise Architect does not enforce this rule during modeling

If DDL is generated for a Table whose Primary Key has no column assigned, that DDL will be invalid

Database Modeling 2 September, 2021

Database Indexes

Database indexes are applied to Tables to improve the performance of data retrieval and sort operations. Multiple

indexes can be defined against a Table; however, each index imposes overheads (in the form of processing time and

storage) on the database server to maintain them as information is added to and deleted from the Table

In Enterprise Architect an index is modeled as a stereotyped operation.

Some DBMSs support special types of index; Enterprise Architect defines these using additional properties such as

function-based, clustered and fill-factor.

Access

Ribbon

Develop > Data Modeling > Database Builder > Click on Table name >

Constraints/Indexes

Context Menu In diagram | Right-click on Table | Features | Constraints/Indexes

Keyboard Shortcuts

Click on Table: F9 > Constraints/Indexes

Work on an index

Step Action

1 On the 'Constraints/Indexes' tab for the Table, right-click and select 'Add new constraint'.

The new constraint is added with the default name 'constraint1' and the Type of 'index'.

Database Modeling 2 September, 2021

Overtype the name with your preferred index name.

2 Assign the appropriate columns to the Index.

The 'Assigned Columns' list has an additional 'Order' field that specifies the order (Ascending or

Descending) in which each assigned column is stored in the index. You can toggle the order for each

column, as required.

Additionally, for MySQL indexes, a 'Len' field will be visible in which you can define Partial Indexes;

that is, an index that uses the leading 'n' number of characters of a text based field. The 'Len' field takes

only whole number numeric values of between 0 and the column's defined length. A value of 0 (which is

the default) indicates that the entire column is to be indexed.

3 In the 'Property' panel, review the settings of the extended properties that are defined for the current

DBMS.

Addional Properes

Property Description

Is Unique

(True / False) indicates whether the current index is a 'Unique Index'. A Unique

Index ensures that the indexed column (or columns) does not contain duplicate

values, thereby ensuring that each row has a unique value (or combination of values

when the index consists of multiple columns).

Is Clustered

(True / False) indicates whether the current index is a 'Clustered Index'. With a

clustered index, the rows of the table are physically stored in the same order as in

the index, therefore there can be only one clustered index per table. By default a

table's Primary Key is clustered.

Not all DBMS's support clustered indexes, therefore the 'Is Clustered' Index

property will only be visible for DBMSs that support it.

Is Bitmap

(True / False) indicates whether the current index is a 'Bitmap' index. Bitmap

indexes are meant to be used on columns that have relatively few unique values

(referred to as 'low cardinality' columns) and that physically consist of a bit array

(commonly called bitmaps) for each unique value. Each of the arrays will have a bit

for each row in the table.

Consider this example: a bitmap index is created on a column called 'Gender',

which has the options 'Male' or 'Female'. Physically, the index will consist of two

bit arrays, one for 'Male' and one for 'Female'. The female bit array will have a 1 in

each bit where the matching row has the value 'Female'.

The 'Is Bitmap' and 'Is Unique' properties are mutually exclusive, and so the DDL

generation will ignore the 'Is Unique' property when the 'Is Bitmap' property is

True.

Bitmap Indexes are only supported by Oracle; therefore, this property is only

visible while modeling Oracle indexes.

Fill Factor A numeric value between 0 and 100, that defines the percentage of available space

that should be used for data.

Not all DBMSs support fill factor, therefore the 'Fill Factor' index property will

only be visible for DBMSs that support it.

Functional-based A SQL statement that defines the function/statement that will be evaluated and the

Database Modeling 2 September, 2021

results indexed; for example:

LOWER("field")

Not all DBMSs support functional-based indexes, therefore the 'Functional-based'

Index property will only be visible for DBMSs that support them, such as

PostgreSQL and Oracle.

Include Identifies a comma-separated list (CSV) of non-key Columns from the current

table.

Not all DBMSs support the 'Include' property on indexes, therefore this property

will only be visible for DBMSs that support it.

Notes

Warning: Enterprise Architect assumes that Indexes have at least one column assigned to them; however, Enterprise·

Architect does not enforce this rule during modeling

If DDL is generated for a Table that has an Index defined without column(s) assigned, that DDL will be invalid,

unless the index is functional-based

Any columns assigned to a functional-based index are ignored·

Database Modeling 2 September, 2021

Unique Constraints

Unique Constraints enforce the 'uniqueness' of a set of fields in all rows of a Table, which means that no two rows in a

Table can have the same values in the fields of a Unique Constraint. Unique Constraints are similar to Primary Keys (in

that they also enforce 'uniqueness') but the main difference is that a Table can have multiple Unique Constraints defined

but only one Primary Key.

Access

Ribbon

Develop > Data Modeling > Database Builder > Click on Table name >

Constraints/Indexes > Right-click > Add New Constraint

Context Menu

In diagram or Browser window | Right-click on Table element | Features |

Constraints/Indexes

Keyboard Shortcuts

Click on Table: F9 > Constraints/Indexes: Ctrl+N

Create a Constraint

Step Action

1 On the 'Constraints/Indexes' tab, a new constraint is automatically created and assigned the default

constraint name and a 'Type' of index.

Overtype the constraint name with a name that identifies this as a unique constraint.

2 In the 'Type' field, change the value from 'index' to 'unique'.

Notes

Warning: Enterprise Architect assumes that Unique Constraints have at least one column assigned to them; however,·

Enterprise Architect does not enforce this rule during modeling

If DDL is generated for a Table that has a unique constraint defined without column(s) assigned, that DDL will be

invalid

Database Modeling 2 September, 2021

Foreign Keys

A Foreign Key defines a column (or a collection of columns) that enforces a relationship between two Tables. It is the

responsibility of the database server to enforce this relationship to ensure data integrity. The model definition of a

Foreign Key consists of a parent (primary) Table containing a unique set of data that is then referred to in a child

(foreign) Table.

In Enterprise Architect, a Foreign Key is modeled with two different (but related) UML components:

A Foreign Key constraint (a UML operation with the stereotype of <<FK>>) stored on the child Table and·

An Association connector (stereotype of <<FK>>) defining the relationship between the two Tables·

Create a Foreign Key

Although the definition of a Foreign Key can be complex, the Foreign Key Constraint screen simplifies the modeling

of Foreign Keys. This screen is purpose-designed to help you select which constraint in the parent Table to use, and will

automatically match the child Table columns to those in the parent Table that are part of the constraint. Different aspects

of the process of developing a Foreign Key are described here separately for illustration, but the overall process should

be a smooth transition.

A number of conditions must be met before a Foreign Key definition can be saved:

Both Tables must have matching DBMSs defined·

The parent Table must have at least one column·

The parent Table must have a Primary Key, unique constraint or unique index defined·

Create a Foreign Key - using the Database Builder

Step Action

In the Database Builder tree, right-click on the child Table name and click on 'Add new Foreign Key on

<table name>'.

A dialog displays listing all the possible parent Tables.

Double-click on the required parent Table name in the list or select it and click on the OK button.

The 'Foreign Key Constraint' screen displays.

Create a Foreign Key - using a relaonship on a diagram

Step Action

1 In the Data Modeling diagram, locate the required child (Foreign Key) Table and parent (Primary Key)

Table.

Select an Association connector in the 'Data Modeling' page of the Diagram Toolbox.

3 Click on the child Table and draw the connector to the parent Table.

Database Modeling 2 September, 2021

If the Foreign Key Constraint screen has been set to display automatically when two Tables are joined,

it displays now. Otherwise, either:

Double-click on the connector or·

Right-click on the connector and select the 'Foreign Keys' option·

The Foreign Key Constraint screen displays.

The Foreign Key Constraint Screen

As an example this image shows the Foreign Key Constraint screen loaded with the details of

'fk_customersaddresses_customers' (as defined in the Example model).

Option Action

Join on Constraint This combo box lists all defined constraints in the parent Table that could be used

as the basis of a Foreign Key. (These constraints can be Primary Keys, Unique

Constraints or Unique Indexes.)

The first constraint in the list is selected by default; if this is not the constraint you

want, select the correct constraint from the combo box.

When you select the constraint, its columns are automatically listed in the 'Involved

Columns' panel, under the 'Parent: <tablename>' column.

Involved Columns This list is divided into two: the columns involved in the selected constraint are

listed on the left, and the child columns that are going to be paired to the parent

columns are listed on the right.

When a constraint is selected (in the 'Join on constraint' field) the parent side is

refreshed to display all columns assigned to the selected constraint. On the child

side the system will automatically attempt to match each parent column to one of

the same name in the child Table. If the child Table does not have a column of the

same name, a new column of that name will be added to the list, flagged with (*) to

indicate that a new column will be created in the Table.

However, if you want to force the pairing to an existing child Table column or a

Database Modeling 2 September, 2021

new column with a different name, click on the column name field and either:

Type in the replacement name, or·

Select an existing column (click on the drop-down arrow and select the name·

from the list)

Name This field defines the name of the Foreign Key constraint, and defaults to a name

constructed by the Foreign Key Name Template.

To change the name to something other than the default, simply overtype the value.

On Delete Select the action that should be taken on the data in the child Table when data in the

parent is deleted, so as to maintain referential integrity.

On Update Select the action that should be taken on the data in the child Table when data in the

parent is updated, so as to maintain referential integrity.

Parent

Click on the drop-down arrow and select the cardinality of the parent Table in the

Foreign Key.

Child

Click on the drop-down arrow and select the cardinality of the child Table in the

Foreign Key.

Create? If you want to create a Foreign Key Index at the same time as the Foreign Key, set

this property to True.

The name of the Foreign Key Index is controlled by the Foreign Key Index

template, and the generated name is shown in the 'Name' field underneath the

'Create?' field.

Automatically show this

screen when tables are

joined

(For diagrammatic modeling) Select this checkbox to automatically display this

screen whenever an Association is created between two Tables.

Delete Click on this button to delete the currently selected existing (saved) Foreign Key.

A prompt is displayed to confirm the deletion (and the deletion of the Foreign Key

Index, if one exists) - click on the Yes button.

Deleting a Foreign Key leaves an Association connector in place, which you can

either edit or delete (right-click and select 'Delete association: to <Table name>').

OK Click on this button to save the Foreign Key.

Examples

This example shows simple Foreign Keys in a diagram:

Database Modeling 2 September, 2021

The same Foreign Key will be shown in the Database Builder's tree as a child node under the Table

'customers.addresses'.

Database Modeling 2 September, 2021

Check Constraints

A Check Constraint enforces domain integrity by limiting the values that are accepted by a column.

Access

Ribbon

Develop > Data Modeling > Database Builder > Click on Table name >

Constraints/Indexes > Right-click > Add New Constraint

Add New Constraint

Keyboard Shortcuts

Click on Table: F9 > Constraints/Indexes: Ctrl+N

Create a Constraint

Step Action

On the 'Constraints/Indexes' tab of the Columns and Constraints screen, a new constraint is

automatically created and assigned the default constraint name and a 'Type' of index.

Overtype the constraint name with a name that identifies the constraint as a check constraint, such as

'CHK_ColumnName' (the CHK_ prefix is optional).

2 In the 'Type' field, change the value from 'index' to 'check'.

3 In the 'Properties' panel for the Condition property, type the SQL statement that will be used as the Check

Condition; for example, column1 < 1000.

If the condition is long, click on the button to display a SQL editor (with syntax highlighting).

Delete a Check Constraint

If you do not want to keep a check constraint, either:

Right-click on it in the list and select 'Delete constraint <name>', or·

Click on the item and press Ctrl+D·

The constraint is immediately deleted.

Notes

Any columns assigned to a check constraint are ignored·

Database Modeling 2 September, 2021

Table Triggers

A Table trigger is SQL or code that is automatically executed as a result of data being modified in a database Table.

Triggers are highly customizable and can be used in many different ways; for example, they could be used to stop certain

database activities from being performed during business hours, or to provide validation or perform deletions in

secondary Tables when a record in the primary Table is deleted.

In Enterprise Architect, a Table trigger is modeled as a stereotyped operation and managed using the Table's 'Constraints'

screen.

Access

Ribbon

Develop > Data Modeling > Database Builder > Click on Table name >

Constraints/Indexes | Right-click | Add New Constraint

Add New Constraint

Keyboard Shortcuts

Click on Table: F9 > Constraints/Indexes: Ctrl+N

Create a Table Trigger

Step Action

1 On the 'Constraints/Indexes' tab, a new constraint is automatically created and assigned the default

constraint name and a 'Type' of index.

Overtype the constraint name with a name that identifies the constraint as a trigger, such as

TRG_OnCustomerUpdate. (The TRG_ prefix is optional.)

2 In the 'Type' field, change the value from 'index' to 'trigger'.

3 In the 'Properties' panel for the Statement property, type in the complete SQL statement (including

CREATE TRIGGER) that will define the Trigger.

If the condition is long, click on the button to display a SQL editor (with syntax highlighting).

4 The properties Trigger Time and Trigger Event are currently information-only values and are not used in

DDL generation.

Delete a Table Trigger

If you do not want to keep a trigger, either:

Right-click on it in the list and select 'Delete constraint <name>', or·

Click on the item and press Ctrl+D·

Database Modeling 2 September, 2021

The trigger is immediately deleted.

Notes

Any columns assigned to table triggers are ignored·

Database Modeling 2 September, 2021

SQL Scratch Pad

The SQL Scratch Pad provides a mechanism to develop and run ad-hoc SQL Queries against a live database. While you

develop your data model you might want to execute and test ad-hoc SQL Queries for a DDL script, or run enquiries on

the live database; all of this is possible within the Enterprise Architect Database Builder interface.

The SQL Scratch Pad requires the Database Builder to have a valid ODBC connection to a live database. This database

connection is shared between the 'SQL Scratch Pad', 'Database Compare' and 'Execute DDL' tabs of the Database

Builder.

The Scratch Pad consists of:

A toolbar providing facilities for importing, saving, executing and clearing the SQL Queries·

An editor panel in which you create or import the SQL Queries - this panel provides SQL-based syntax highlighting·

for the current data model

A tabbed panel consisting of two pages, one to show the results of executing the Query and one to display any·

messages generated during the execution

Access

Open the Database Builder window, then display the 'SQL Scratch Pad' tab.

Ribbon

Develop > Data Modeling > Database Builder > SQL Scratch Pad

The Scratch Pad Toolbar

The functionality of each button on the Scratch Pad Toolbar is described in this table, working from left to right.

Button Action

Run SQL Executes the SQL Query currently shown in the Scratch Pad.

Check the 'Results' and 'Messages' tabs for the output of executing the Query.

New

Clears the SQL Query editor fields so that you can enter a new query.

Open Loads an SQL Query from file.

A source file browser displays, defaulted to display SQL files. Click on the file

name and on the Open button to display the file contents in the Scratch Pad.

Save to SQL Query Saves this SQL statement to the SQL Query object it came from.

Save to New SQL Query Creates a new SQL Query object and saves this statement to that object.

Save to File Saves the currently-displayed Query to the file it came from.

If you created the Query from scratch, a source file browser displays in which you

type the new file name and click on the Save button to save the Query.

Save to New File Saves the currently-displayed Query to a new .sql file.

Database Modeling 2 September, 2021

A source file browser displays on which you type in the new file name and click on

the Save button to save the Query.

Clear Clears the contents of the Scratch Pad.

Any Query displayed in the Scratch Pad remains there until you either replace it

with another Query from file or you close the model.

Toggle Comment Applies the SQL comment characters '--' to the beginning of each selected line or, if

the selected lines are already commented, removes the comment characters.

Alternatively, press Ctrl+Shift+C.

Statement Separator Type in the character(s) to use to mark the end of each statement.

Help Displays the Help on the SQL Query Scratch Pad.

Query Description Displays a label providing a description of the current SQL, whether there are

pending changes (indicated by a leading *), and the name of the loaded SQL Query

object or Filename.

Notes

The SQL Scratch Pad does not manipulate your SQL in any way, so you must use the correct syntax for the current·

DBMS

While the SQL Scratch Pad can execute multiple SQL statements, and the status and messages of each statement are·

shown in the 'Messages' list, only the results of one SELECT statement can be shown in the 'Results' list at a time;

all subsequent SELECT statements will be ignored

Database Modeling 2 September, 2021

Database Compare

The 'Database Compare' tab provides a mechanism for comparing the current data model with a live database, and

optionally synchronizing any differences in either direction. Differences 'pushed' into a live database are performed using

'Alter DDL' statements, while changes imported from the live database can be directly 'pulled' into the model.

The Database Compare functionality requires the Database Builder to have a valid ODBC connection to a live database.

This database connection is shared by the 'SQL Scratch Pad', 'Database Compare' and 'Execute DDL' tabs of the

Database Builder.

Access

Open the Database Builder window, then display the 'Database Compare' tab.

Ribbon

Develop > Data Modeling > Database Builder > Database Compare

The DDL Compare Tab

The 'Database Compare' tab has a number of controls, as described here.

Number & Name Description

Database Modeling 2 September, 2021

1 Case Sensitive Click on this checkbox to make all comparisons of properties recognize differences

in letter-case in the property text.

2 Use Alias if Available Click on this checkbox to indicate that any defined aliases should be used instead of

object names (at both object and column level).

3 Reset All Click on this button to set the 'Action' flag for all objects back to the default value.

4 Set Import All Click on this button to set the 'Action' flag of all detected differences to <====; that

is, update the model with the value(s) from the live database.

5 Set Synchronize All Click on this button to set the 'Action' flag of all detected differences to ====>; that

is, update the live database with the value(s) from the model.

6 Differences Review the list of objects found to have mis-matches between the model and the

live database. Selecting an item in this list will populate the 'Components' list.

(See the Differences List table for a detailed description of each column.)

7 Components Review this list of properties of the selected object that differ between the model

and the live database.

(See the Component List table for a detailed description of each column.)

8 Reset Click on this button to set the 'Action' flag for all properties of the current object

back to the default value.

9 Import from ODBC Click on this button to import all properties' values (with the 'Action' of <===) from

the live database into the model.

10 Generate DDL Click on this button to generate the 'Alter DDL' statements for all objects with an

'Action' of ====>, and send the statements to the 'Execute DDL' tab.

Dierences List

Column Description

EA Displays the name of each object in the model that has one or more detected

differences. Blank values indicate that the object is missing in the model but exists

in the live database.

Action Defaults to 'No Action' as the action to take considering this object's difference(s).

Click on the drop-down arrow and select a specific action. The list of available

actions in the list will depend on whether or not the given object is paired in the

model and live database.

Paired objects

No Action - do not update the database or model with this change·

===> - update the object in the database from the model·

<=== - update the object in the model from the database·

Customize - set the items to No Action prior to setting different actions on each·

item in the lower panel

Database Modeling 2 September, 2021

Unpair - separate the paired objects so that they are not compared with each·

other or updated from each other

Unpaired objects

Create <object name> - create the missing database object in the database or·

model, as appropriate

Delete <object name> - delete the object from the model·

Drop <object name> - delete the object from the database·

Pair with <object name> - pair the object in the database with the named·

(unpaired) object in the model, so that they are compared for differences

between them

The 'Action' fields in the 'Components List' (the lower panel) will be updated based

on the selection of this field.

For example, if the live database has a Table column 'Address1' and the model

doesn't, setting the object 'Action' to '===>' (update the object in the database from

the model) sets the column 'Item Action' to 'Drop Address1', which will remove the

column from the live database.

ODBC Shows the name of each object in the live database that has one or more detected

differences. Blank values indicate that the object exists in the model but is missing

in the live database.

Count Shows the total number of detected differences for the object (and all of its

components) between the model and live database.

Component List

Column Description

Item Shows the component name or description for each detected difference. The

differences are grouped into three categories: Properties, Columns and Constraints,

in a tree structure.

EA Shows the value of the given component as detected in the model. Blank values

indicate that the value is missing in the model but exists in the live database.

Action Defaults to the action corresponding to the setting of the object 'Action' field in the

'Differences' list, to indicate the action to take regarding the difference detected for

the component. Click on the drop-down arrow to select an alternative action; the

available options in the list depend on the component's type and the detected

difference.

No Action - do not update the database or model·

===> - update the object in the live database from the model·

<=== - update the object in the model from the live database·

Add <item name> - create the missing item in the database or model, as·

appropriate

Delete <item name> - delete the item from the model·

Drop <item name> - delete the item from the live database·

Database Modeling 2 September, 2021

ODBC Shows the value for the selected component in the live database. Blank values

indicate that the value exists in the model but is missing in the live database.

Count Shows the number of differences between the model and the live database detected

in the selected component.

Working with the Database Comparison

Whenever you perform a comparison, Enterprise Architect reads the definition from both the live database and the

model, and then attempts to 'pair' each object from one source with the other, using its name (and schema, if relevant for

the current DBMS).

If a match is found, the object name is shown in both the 'EA' and 'ODBC' columns with a default action of 'No Action'.

The 'Count' column indicates the total number of differences found for the object and its components or properties.

If a match is not found between the systems, the object name is shown in the source column (either 'EA' or 'ODBC')

while the other column is blank. In this state it is possible to pair the object with an object of a different name; the

'Action' dropdown list will present the available objects. If a new pairing is made the two objects' definitions are

compared for differences and the results are shown in the 'Components' list, with the default action of '====>' selected.

If you select an action at the object level, this will set the matching action for all of the object's components and

properties. However, if you select the 'Customize' action at the object level, you can determine a different action for each

component.

As an example, both a column (tax_amount) and constraint (ck_customersordersitems_discount) were renamed in Table

'public.customers_order_items' (in the Example model) and a database compare performed; this image shows the

differences found:

In the image there is only one Table that had detected differences - 'public.customers_order_items'; selecting this

populates the 'Components' list. From the detected results it can be determined that the data model contains a column

(tax_amount2) and a check constraint (ck_customerordersitems_discount1) that the live database doesn't and in turn the

live database contains a column (tax_amount) and a check constraint (ck_customerordersitems_discount) that the data

model doesn't.

Comparing with Opons

Database Modeling 2 September, 2021

The 'Compare with Options' functionality works in the same manner as for a direct comparison, except that you are

prompted to choose which object/property comparisons should be performed. This enables you to ignore particular

differences that are not of relevance at the current time.

These tables describe the different comparisons that can be enabled or disabled.

All Objects, Owner

Comparison Action

Owner Select to indicate that the 'Owner' property of all database objects should be

compared, after the objects have been 'paired'.

Table Opons

Option Action

Tables Select this parent option to enable all of the Table comparison options. Deselect to

disable all the other options. You would then deselect or select specific options in

the list.

Table - Extended

Properties

Select to indicate that extended properties of Tables (such as DB Version and

Tablespace) should be compared.

Table - Remarks Select to indicate that remarks applied to Tables should be compared.

Columns Select this parent option to enable all of the 'Column comparison' options. Deselect

to disable all the other 'Column' options. You would then deselect or select specific

options in the list.

Column - Type Select to indicate that the datatype name for the Table Columns should be

compared.

Column - Size Select to indicate that the datatype size for the Table Columns should be compared.

Column - Default Value Select to indicate that the default values of the Table Columns should be compared.

Column - Position Select to indicate that the Table Column positions should be compared.

Column - Not Null Select to indicate that the not null property of the Table Columns should be

compared.

Column - Auto Numbering Select to indicate that the autonumbering properties for the Table Columns should

be compared (such as AutoNum, StartNum and Increment).

Column - Unmatched

Columns

Select to indicate that Table Columns that are unmatched between the model and

the live database should be compared. Typically these are columns that exist in one

system but do not exist in the other.

Database Modeling 2 September, 2021

Column - Extended

Properties

Select to indicate that extended properties of Table Columns (such as Unsigned and

Zerofill) should be compared.

Column - Remarks Select to indicate that remarks applied to Table Columns should be compared.

Constraints Select this parent option to enable all of the 'Table Constraint comparison' options.

Deselect to disable all the 'Table Constraint' options. You would then deselect or

select specific options in the list.

Constraint - Primary Keys Select to indicate that properties related to Primary Keys should be compared.

Constraint - Foreign Keys Select to indicate that properties related to Foreign Keys should be compared.

Constraint - Indexes Select to indicate that properties related to Indexes should be compared.

Constraint - Unique

Constraints

Select to indicate that properties related to Unique Constraints should be compared.

Constraint - Check

Constraints

Select to indicate that properties related to Check Constraints should be compared.

Constraint - Table Triggers Select to indicate that properties related to Table Triggers should be compared.

Constraint - Unmatched

Constraints

Select to indicate that Table Constraints that are unmatched between the model and

the live database should be compared. Typically these are constraints that exist in

one system but do not exist in the other.

Constraints - Extended

Properties

Select to indicate that extended properties of Table Constraints (such as Fill Factor

and Clustered) should be compared.

Constraints - Remarks Select to indicate that remarks applied to Table Constraints should be compared.

Notes

The Database Compare functionality currently can perform comparisons on Table, View, Procedure, Function and·

Sequence object types

Database Modeling 2 September, 2021

Execute DDL

The 'Execute DDL' tab provides a mechanism to easily execute generated DDL statements against a live database, and

provides instant feedback on their success, all within the Enterprise Architect interface and without the need for other

products.

There are two different types of DDL statement that Enterprise Architect can generate and send to the 'Execute DDL' tab:

Create DDL statements, created by the Generate DDL screen, and·

Alter DDL statements, created by the Database Compare window·

The Execute DDL functionality requires the Database Builder to have a valid ODBC connection to a live database. This

database connection is shared between the SQL Scratch Pad, Database Compare and 'Execute DDL' tabs of the

Database Builder.

Access

Open the Database Builder window, then display the 'Execute DDL' tab.

Ribbon

Develop > Data Modeling > Database Builder > Execute DDL

Execute the DDL

The 'Execute DDL' tab has these fields and buttons:

Field/Button Action

Database Modeling 2 September, 2021

1 Execution Queue Lists the tasks (each with an associated DDL statement) that are yet to be executed.

The list has three columns that specify the name of the object involved, the task and

the action being performed.

Selecting an item in the list will display the associated DDL statement (in the

'Script' field) for the given task.

2 Script A text box with SQL syntax highlighting, showing the DDL statement for the

selected task.

3 Save Click on this button to save all the individual DDL statements from both the

'Execution Queue' and the 'Results List' into a single file.

4 Reset Failed Actions Click on this button to re-queue any failed or skipped tasks from the 'Results List' to

the bottom of the 'Execution Queue'.

5 Skip Click on this button to skip over the next task in the 'Execution Queue' and not

execute it. The task will be moved into the 'Results List' and not given a result.

When you click on the Reset Failed Actions button, skipped tasks are returned to

the Execution Queue along with any failed tasks.

6 Execute Click on this button to execute the next task in the 'Execution Queue'. The task is

removed from the top of the 'Execution Queue' and added to the end of the 'Results

List' with the execution result.

7 Execute All Click on this button to execute all tasks in the 'Execution Queue'. When execution

is complete, the 'Results List' will display the results of each individual task.

8 Results List Lists the executed tasks with the results of execution for each task. Selecting an

item in this list will display the DDL statement that was executed, in the 'Script'

field.

Example

In the example used in the earlier section on Database Comparison (when a column and constraint were renamed), if the

defaults are used to 'push' the data model changes into the live database the Execute DDL screen is populated with the

details shown here.

Database Modeling 2 September, 2021

In summary, DDL is generated to drop both the old column and the old constraint (tasks 'Drop Column' and 'Drop

Constraint'), then the column and constraint are created with the new names (tasks 'Add Column' and 'Add Constraint')

and finally each has their comments/remarks applied (tasks 'Add Constraint - Constraint Comment' and 'Add Column -

Column Comment').

Database Modeling 2 September, 2021

Database Objects

Whilst Tables are the fundamental components of a relational database and allow the definition of Columns, Data Types,

Keys and Indexes, there are a number of other Objects that are important in RDBM systems including:

Views - a View represents the result-set of a pre-defined query; they are dynamically derived from the data stored in·

one or more Tables (or other Views)

Procedures - a feature that some DBMS products implement to provide subroutines that can contain one or more·

SQL statements to perform a specific task such as data validation, access control, or to reduce network traffic

between clients and the DBMS servers

Functions - a feature that some DBMS products implement to provide a mechanism to extend the functionality of the·

database server; each is a routine that can accept parameters, perform an action (such as a complex calculation) and

return the result of that action as a value

Sequences - a feature that some DBMS products implement to provide a mechanism to generate unique values - the·

Sequence ensures that each call to it returns a unique value

The UML itself does not specify how data modeling is performed, but Enterprise Architect has a fully integrated UML

profile for data modeling and a range of features built in to the core product that will make data modeling easy.

The profile uses stereotypes and Tagged Values to extend standard UML elements into data modeling constructs. This is

achieved by adding the database object stereotype to a UML Class; so that you would model:

Data Modeling diagrams as extended UML Class diagrams·

Tables as UML Class objects with a stereotype of <<table>>·

Views as UML Class objects with a stereotype of <<view>>·

Procedures as UML Class objects with a stereotype of <<procedure>>·

Functions as UML Class objects with a stereotype of <<function>>·

Sequences as UML Class objects with a stereotype of <<dbsequence>>·

You can quickly create and configure all of these objects in your database model with Enterprise Architect.

Database Modeling 2 September, 2021

Database Tables

Tables are the fundamental components of a relational database, representing multiple rows of structured data elements

(referred to as Columns). Every individual item of data entered into a relational database is represented by a value in a

column.

Enterprise Architect's UML Profile for Data Modeling represents:

Database Tables as UML Class objects with a stereotype of <<table>>·

Table columns as UML attributes of a Table, with a stereotype of <<column>>·

Primary Keys as UML operations/methods of a Table, with a stereotype of <<PK>>·

Foreign Keys as UML operations/methods of a Table, with a stereotype of <<FK>>·

Indexes as UML operations/methods of a Table, with a stereotype of <<index>>·

Unique Constraints as UML operations/methods of a Table, with a stereotype of <<unique>>·

Check Constraints as UML operations/methods of a Table, with a stereotype of <<check>>·

Table Triggers as UML operations/methods of a Table, with a stereotype of <<trigger>>·

Enterprise Architect refers to all of the UML operations of a Table collectively as Constraints, hence the screen you use

to maintain a Table's UML attributes and operations is called the Columns and Constraints screen.

Example

This simple example of a Physical Data Model diagram in Enterprise Architect consists of two Database Tables

represented by UML Classes, named customers and customer_addresses.

Each Table defines database columns, using UML attributes typed appropriately for the target DBMS (in this case,

PostgreSQL).

Notes

The Table stereotype is denoted by the icon in the top-right corner of each Class (see the Data Modeling Notation·

topic)

The Enterprise Architect maintenance screen for managing Table Columns doesn't allow you to change the attributes·

stereotype, since <<column>> is the only valid option

It is possible to hide the <<column>> stereotype label shown in the example Tables (see the Data Modeling·

Notation topic)

Database Modeling 2 September, 2021

Create a Database Table

Fundamental to data modeling is the creation of Database Tables within the model. There are three ways to create a

Table:

Within the Database Builder·

On an open Data Model diagram·

Using the Browser New Element option·

Add a Database Table with the Database Builder

Step Action

Open the Database Builder ('Develop > Data Modeling > Database Builder').

2 Load or create a Data model.

3 Right-click on the Tables Package and select 'Add New Table'.

Overtype the default name with the appropriate name for the Table, and press the Enter key.

5 Double-click on the Table element to define the Table properties.

Add a Database Table to a diagram

Step Action

1 Create and/or open a Data Modeling diagram.

2 Drag and drop the 'Table' toolbox icon onto the diagram.

Database Modeling 2 September, 2021

This generates a new Table element:

3 Double-click on the Table element to define the Table properties.

Database Modeling 2 September, 2021

Database Table Columns

In a relational database, a Table column (sometimes referred to as a field) stores a single data value of a particular type in

each row of the Table. Table columns can have various individual properties such as a default value or whether the field

accepts Null values.

A Database Table Column is represented in the UML Data Modeling Profile as a stereotyped attribute; that is, an

attribute with the <<column>> stereotype. In Enterprise Architect you define and maintain Table Columns using the

purpose-designed 'Columns' page of the Database Builder, or the 'Columns and Constraints' dialog.

Database Modeling 2 September, 2021

Create Database Table Columns

A database Table column is represented in the UML Data Modeling Profile as an attribute with the <<column>>

stereotype. For a selected Table, you can review the existing columns and create new columns, on the 'Columns' page of

the Database Builder or on the 'Columns and Constraints' screen.

You can define column details directly on the list of columns on the 'Columns' tab. The changes are automatically saved

as you complete each field. Some fields have certain restrictions on the data you can enter, as described here. The tab

also contains a 'Properties' panel and a 'Notes' field, which are populated with the existing information on the selected

column. Each new column that you create is automatically assigned a set of default values and added to the bottom of the

list.

Access

Ribbon

Develop > Data Modeling > Database Builder > Click on Table > Columns >

Right-Click > Add new Column

Context Menu In diagram, right-click on required Table | Features | Columns | Right-Click | Add

new Column

Keyboard Shortcuts

Select a table | F9 | Tab Key (to set input focus on the 'Columns' tab) | Ctrl+N

Create columns in a Table

Option Action

Name Overtype the default name with the appropriate column name text.

Type

Click on the drop-down arrow and select the appropriate datatype for the column.

The available datatypes depend on the DBMS assigned to the parent Table.

Length (Optional) Some datatypes have a length component - for example, VARCHAR has

a length that defines the number of characters that can be stored. If the datatype

does not have a length component, this field is disabled.

If the field is available and if you need to define a number of characters, type the

value here.

Scale (Optional) Some datatypes have a scale component - for example, DECIMAL has a

scale that defines the number of decimal places that can be held. If the datatype

does not have a scale component, this field is disabled.

If the field is available and if you need to define a scale, type the value here.

PK Select the checkbox if the column is part of the Primary Key for this Table.

Not Null Select the checkbox if empty values are forbidden for this column.

The checkbox is disabled if the 'PK' checkbox is selected.

Database Modeling 2 September, 2021

Alias If required for display and documentation purposes, type in an alternative name for

the field.

Initial Value If required, type in a value that can be used as a default value for this column.

Notes Type in any additional information necessary to document the column.

You can format the text using the Notes toolbar at the top of the field.

Column Properes

The appropriate properties for the Table's Database Management System automatically display in the 'Property' panel

(expand the 'Column (<name>)' branch if they are not visible).

Property DBMS

Autonum (Startnum

Increment)

Oracle

MySQL

SQL Server

DB2

PostgreSQL

Notes: If you require an automatic numbering sequence, set this property to True

and, if necessary, define the start number and increment.

Generated DB2

Notes: Set this additional property for auto numbering in DB2, to 'By Default' or

'Always'.

NotForRep SQLServer

Notes: Set this property to True if you want to block replication.

Zerofill MySQL

Notes: Set this property to True or False to indicate if fields are zerofilled or not.

Unsigned MySQL

Notes: Set this property to True or False to indicate whether or not fields accept

unsigned numbers.

LengthType Oracle

Notes: Set this property to define the character semantics as 'None', 'Byte' or 'Char'.

Database Modeling 2 September, 2021

Delete Database Table Columns

For a selected database Table, you can review the existing columns and delete any individual column, on the 'Columns'

tab of the Columns and Constraints screen.

Access

Use one of the methods outlined here to display a list of columns for a table, then select a column and delete it.

When you select the 'Delete column '<name>'' option, if all validation rules are satisfied the column is immediately

deleted.

Ribbon

Develop > Data Modeling > Database Builder > Click on Table > Columns >

Right-click on column name > Delete column <name>

Context Menu In diagram, right-click on required Table | Features | Columns | Right-click on

column name | Delete column <name>

Keyboard Shortcuts

F9 | Use 'Up Arrow' or 'Down Arrow' to select a column | Ctrl+D

Notes

If the deleted database Table column is involved in any constraints it will automatically be removed from them·

Database Modeling 2 September, 2021

Reorder Database Table Columns

If you have several columns defined in a database Table, you can change the order in which they are listed. The order in

the list is the order in which the columns appear in the generated DDL.

Access

Use one of the methods outlined here to display a list of columns for a Table, then select a column and reposition it

within the list.

Ribbon

Develop > Data Modeling > Database Builder > Click on Table

Context Menu In diagram, right-click on required Table | Features | Columns

Keyboard Shortcuts

Change the column order

Step Action

1 In the 'Columns' tab, click on the required column name in the list.

2 Right-click and select the:

'Move column <name> up' option (or press Ctrl+Up Arrow) to move the column up one position·

'Move column <name> down' option (or press Ctrl+Down Arrow) to move the column down one·

position

These options have an immediate effect both in the 'Columns' tab and on a diagram.

Database Modeling 2 September, 2021

Working with Database Table Properes

Once you have created a Database Table, you can review its properties and check that the DBMS and Owner values are

correct. To display the 'Properties' dialog for a Table, either double-click on the Table name in the 'Database Builder

Tables' Package or on the Table element on a diagram.

Important

A DBMS must be assigned to a Table before you can add columns in it. If you are using the Database Builder then the

DBMS of the data model will be automatically applied to all new Tables; however, if you have added a Table by other

means (such as working on a diagram) then this is a manual step.

Tasks

Once the Database Table properties are defined, you are ready to add columns.

Task

Set the database type for a Table - other than the Table name, the most important property to set for a Database

Table is the database type.

Set the database Table Owner - For some DBMSs all Tables must be assigned an Owner/Schema; in Enterprise

Architect this property is defined as a Tagged Value with the name Owner.

Set extended options - some DBMSs have extended options that are only relevant to that DBMS. These extended

properties are stored as Tagged Values.

Default DBMS

Prior to creating a Physical Data Model it is advisable for you to set the default DBMS, which will be automatically

applied to new database objects that you create outside of the Database Builder. You can set the default DBMS type in

one of these ways:

Select 'Start > Desktop > Preferences > Preferences > Source Code Engineering > Code Editors', then set the field·

'Default Database'

Select 'Configure > Reference Data > Settings > Database Datatypes', then select a Product Name and select the 'Set·

as Default' checkbox

Set the DBMS in the second field of the Code Generation Toolbar·

Database Modeling 2 September, 2021

Set the Database Type

The most important property to set for a Database Table (after its name) is the database type or DBMS. The DBMS value

selected will control how Enterprise Architect will determine:

How the Table name will be shown (with or without an Owner)·

What set of validation rules will be applied while database modeling·

The data types that are available when creating columns,·

What set of DDL templates will be used in DDL Generation·

Access

Select a Table in the Browser window or on a diagram then, using any of the methods outlined here, open the Table's

'Properties' dialog, display the 'General' tab, then display the 'Main' child tab.

Ribbon Design > Element > Editors > Properties Dialog > General > Main

Context Menu Right-click on the Table element | Properties | Special Action | General | Main

Keyboard Shortcuts

Shift+Enter | General | Main

Other Double-click on the Table element |General | Main

Opons

Field/Button Action

Database Click on the drop-down button and select the required database type from the list.

Apply

Click on the Apply button to save any pending changes.

Click on the OK button to save any pending changes and close the screen.

Database Modeling 2 September, 2021

Set Database Table Owner/Schema

For some DBMSs all Tables must be assigned an Owner/Schema. In Enterprise Architect this property is physically

defined as a Tagged Value with the name Owner. However, a special properties page is provided to help you easily

manage the Owner property.

Access

Select a Table in the Browser window or on a diagram then, using any of the methods outlined here, open the Table's

'Properties' dialog, display the 'General' tab and display the 'Table Detail' child tab.

Ribbon Design > Element > Editors > Properties > << table >>

Context Menu Right-click on the Table element | Properties |Special Action > General > Table

Detail

Keyboard Shortcuts

Shift+Enter | General | Table Detail

Other Double-click on the Table element |'General' | 'Table Detail'

Set the Database Table owner

Step Action

1 In the 'Owner' field, type the name of the owner or schema of the Table.

Database Modeling 2 September, 2021

Set MySQL Opons

To make use of Foreign Keys in MySQL, you must declare the Database Table type as InnoDB.

Declare the Table type as InnoDB

Step Action

1 Add a Tagged Value named Type to the Table.

2 Set the 'Value' field to 'InnoDB'.

Generate DDL

When you generate DDL for this Table, the Table type is included in the SQL script.

To allow for later versions of MySQL, additional Table options that can be added in the same way include:

Tag Value (Example)

ENGINE InnoDB

CHARACTER SET latin1

CHARSET latin1

COLLATE latin1_german2_ci

Database Modeling 2 September, 2021

Set Oracle Database Table Properes

To set additional Oracle Database Table properties, you use the Table's Tagged Values.

Set Properes

The same properties can be added to indexes and constraints, by highlighting the index or constraint Operation and

adding the appropriate Tagged Values.

Step Action

1 Add one or more Tagged Values to the Table, using the names provided in the 'Property/Tag' column of

the 'Properties' Table.

2 Specify the appropriate value for each tag.

Examples are provided in the 'Value' column of this Properties Table.

CACHE - NOCACHE·

DBVERSION - 9.0.111·

FREELISTS - 1·

GRANT OWNER1 - SELECT·

GRANT OWNER2 - DELETE, INSERT, SELECT, UPDATE·

INITIAL - 65536·

INITRANS - 1·

LOGGING - LOGGING·

MAXEXTENTS - 2147483645·

MAXTRANS - 255·

MINEXTENTS - 1·

MONITORING - MONITORING·

OWNER - OWNER1·

PARALLEL - NOPARALLEL·

PCTFREE - 10·

PCTINCREASE - 0·

PCTUSED - 0·

SYNONYMS - PUBLIC:TABLE_PUB;OWNER2:TABLE_OWNER2·

TABLESPACE - MY_TABLESPACE·

TEMPORARY - YES·