MEGA Database Builder
Introduction to HOPEX Database Builder
Connecting to HOPEX Database Builder
Connecting to the solution
Connection Profiles to HOPEX Database Builder
Interface Presentation
Desktop Presentation
Data Modeling Options
Formalisms
Notations
Conceptual Modeling
Data Models
Presentation
Creating a Data Model
Building a Data Diagram
Creating a Data Diagram
Entities
Creating an entity
Attributes
Defining attributes
Inherited attributes
Associations
Examples of associations
Creating an Association
Defining association roles (ends)
Multiplicities
Aggregation
Composition
Using reflexive associations
Displaying an N-ary Association
Generalizations
What is a generalization?
Multiple sub-entities
Multiple inheritance
Creating a generalization
Discriminator
Constraints
Normalization Rules
Normalization Rules: First Normal Form
Normalization Rules: Second Normal Form
Normalization Rules: Third Normal Form
SQL Identifiers and Options
Defining an Entity Identifier
Implicit identifier
Identification by an attribute
Relative identification
Composite identifier
Entity SQL Options
Attribute SQL Options
Generalization SQL Options
Data Modeling Rules
Checking a Data Model
The Class Diagram
Creating a Package
The Class Diagram
Creating a Class Diagram
Classes
Class Properties
Characteristics Page
Text Pages
The attributes of a class
Attribute properties
Types
Association Roles
Order
Using Association Classes
Database and Physical Modeling
Database
Creating Databases
Database Properties
Connecting a Data Model to a Database
Importing a DBMS version
Relational Diagram
Creating the Relational Diagram
Creating objects in the diagram
Configuring display of relational diagrams
Logical Modeling
Database Tables
Creating a table
Deleting a table
Table Columns
Viewing columns
Creating a column
Deleting a column
Modifying Keys and Indexes
Creating a Key
Primary key
Foreign key
Creating an Index
Adding a Column to a Key or Index
Specifying Primary and Foreign Keys
Specifying Primary Keys
Specifying Foreign Keys
Column Primary Key of Two Tables
Column Primary Key of Three Tables
Database Modeling Rules
Synchronizing logical and physical models
"Logical to Physical" Synchronization Rules
Entities
General rule
Sub-entity
Abstract entity
Realized entity
Associations
Constraint associations (multiplicities: 0,1 or 1,1)
Constraint associations (multiplicities: 0,1 and 0,1)
Deadlocks
Non-constraint association
Associative class (UML notation)
From the Logical Model to the Physical Model
Running Synchronization (Logical > Physical)
Step 1: Selecting the source objects to be synchronized
Step 2: Synchronization options
Step 3: Protecting objects
Step 4: Validating results
Using Options (Logical > Physical)
Take account of optimizations
Take account of deletions
Possible option combinations
Protecting Objects
Frozen mode
Realized mode
Synchronization Results: Correspondences
Mapping characteristics
Reduced Synchronization (Logical to physical mode)
Reduced Synchronization Source Objects (Logical > Physical)
Running from a data model
Running from a data model entity
Running on an entity outside context
Reduced Synchronization Strategies (Logical > Physical)
Impact of synchronized object on other objects
Impact of other objects on synchronized object
All impacts
Running Reduced Synchronization (Logical > Physical)
Reduced synchronization options
Running Synchronization After Modifications
Synchronization after Modification of the Data Diagram
Newly created entities, associations, and attributes in the data diagram
Entities, associations, or attributes deleted from the data diagram
Modified attribute characteristics
Modified name of an attribute, entity, or association
Modified maximum multiplicity of an association
Modified association links
Synchronization after Modifications to the Physical Diagram
Deleted table or column
Created objects
Modified characteristics of objects created by synchronization
Modified order
From the Physical Model to the Logical Model
"Physical to Logical" Synchronization Rules
Running Synchronization (Physical > Logical)
Step 1: Selecting objects to be synchronized
Step 2: Synchronization options
Step 3: Protecting objects
Step 4: Validating results
Reduced synchronization
"Physical to Logical" Synchronization Results
Owner data model
Data diagrams
Mappings
Configuring Synchronization
Preparing Synchronization
Creation Options
On a database
On the DBMS
Configuring Name Generation
Naming rules
Modifying a naming rule
Entering the SQL mask
Configuring PK column names (implicit identifier)
Diagram Synchronization
Case of Diagram Update at Synchronization
After source diagram modification
After target diagram modification
After modification of both diagrams
No modification detected
Particular case: an entity mapping with two tables.
Model Mapping
The Database Editor
Run the editor on a database
Creating a Logical/Physical Mapping Tree
Creating a Mapping
Deleting a mapping
Mapping Details
Mapping Properties
Mapping Report
Object status
Saving display of editor indicators
Mapping Source
Mapping Drawing
Denormalizing logical and physical models
Denormalization Principles
Denormalization: consistency of models
Transferring mappings
Deleting source objects
Synchronization and Denormalization
Combining denormalization and synchronization options
Denormalization: Use Case
Logical Denormalization
Running Logical Denormalization
Logical denormalization example
Logical Denormalization Wizards
Transform association to entity
Transform entity to association
Transform generalization to association
Transform association to generalization
Vertical partition of entities
Horizontal partition of an entity
Merging of entities
Merging of ascending entities
Merging of descending entities
Copy/paste of attributes
Physical Denormalization
Running Physical Denormalization
Physical denormalization example
List of Physical Denormalization Wizards
Vertical partition of a table
Horizontal partition of a table
Merging of tables
Transform foreign key to table
Transform table to foreign key
Copy/paste of columns
Attribute and Column Types
Attribute Datatypes
Datatype Packages
Creating a New Datatype Package
Creating a datatype
Referencing Datatype Packages
Assigning Types to Attributes
Determining Column Datatypes from Attribute Types
Pivot Types
List of pivot types
Connecting a Datatype to a Pivot Type
Connecting a Datatype to a Pivot Type in UML Notation
By inheritance
By a correspondence link
By an equivalence link
By creating a compound datatype
Mappings Between Pivot Types and Datatypes
Example of correspondence between pivot types and Oracle 8 datatypes
Creating New Datatypes
Creating Datatypes - Example for Oracle 10
Objective
Steps
Prerequisite Conditions
Creating a new datatype
Connecting the datatype to the pivot type
Configuring conditions on links
Verifying datatypes
Creating Datatypes - Example for SQL Server 7
Objective
Creating a new datatype
Connecting the datatype to the pivot type
Configuring conditions on links
Generating SQL scripts
Running SQL Generation
SQL Generation Objects
Start the generation wizard
Incremental Generation
Incremental Generation Objects
Running Incremental Generation
Generation options
Start the generation wizard
Configuring SQL generation
Configuring the DBMS Version
Supported DBMS versions
Modifying DBMS version properties
Configuring Database Generation
Prefixing Object Names
Inheritance
DBMSs concerned
Supported Syntax
CREATE TABLE Instruction
Managing NOT NULL
PRIMARY KEY clause
FOREIGN KEY clause
UNIQUE clause
CREATE INDEX Instruction (Oracle, Sybase, SQL Server)
Definition of an index
Processing and generating SQL commands
CREATE VIEW Clause
Advanced SQL Options
Defining Database Views
Creating Database Views
Add a table or a column to a view
SQL Definition
View joints
User mode
Fields
Defining a Data Group
Defining Triggers for a Database
Creating Triggers
Trigger triggering
References
SQL Definition
Repository Integrity
Using Stored Procedures
Adding Physical Properties to Database Objects
Target DBMSs
Creating Physical Properties
Objects containing physical parameters
Creating a new clause
Connecting a clause
Naming clauses
Physical Model Customization Example
Generating the SQL File
Reverse engineer tables
Running Reverse Engineering
Physical Properties Reverse Engineering
Default Values
Eliminating Redundant and Transverse Values
Specific Cases
Physical properties of tablespaces
Clusters Reverse Engineering
ODBC Extraction Utility
Extracting the Description of a Database
Extraction Report File
Extraction Results File
Customizing ODBC Extraction
Using the Odwdbex.ini file and customized queries
Using ODBC standard APIs
Select Clause Formats
Select Clause Formats: Primary Keys
Select Clause Formats Foreign Keys
SELECT Clause Format: Indexes
SELECT Clause Format: Columns
Pivot Types and Datatypes Correspondence Tables
The following tables show correspondence between pivot types and the different supported DBMSs and their versions.DB2 OS/390 Version 5
DB2 OS/390 Version 7
DB2 for z/OS Version 8
DB2 Universal Database Version 5
DB2 Universal Database Version 7
DB2 Universal Database Version 8
DB2 Universal Database Version 9
DB2 Version 9 For OS
Informix Dynamic Server 7.3
Ingres II 2.0
MySQL 4.1
MySQL 5.0
Oracle 10
Oracle 11
Oracle 8
Oracle 9i
PostgreSQL9.3
SQL ANSI/ISO 9075:1992
SQL Server 2000
SQL Server 2005
SQL Server 2008
SQL Server 7
Sybase Adaptive Server 11
Sybase Adaptive Server 12.5
Teradata Database
PostgreSql 9.3: Syntax supported by HOPEX
Detail of Concepts Managed by PostgreSQL
Tables
Indexes
Tablespaces
Views
Triggers
Sequences
Functions
Materialized views
Domains and types
"Trigger events"
Creating Datatype Packages
PostgreSQL datatypes
Physical Parameters
Tables
Columns
Indexes
Tablespaces
Generic clause types
Unmanaged physical parameters
Properties pages
DB2 Version 10.5: Syntax supported by HOPEX
Detail of Concepts Managed by DB2 V10.5
Tables
CREATE GLOBAL TEMPORARY TABLE
Indexes
Tablespaces
Views
Triggers
Sequences
Procedures
Creating Datatype Packages
DB2V10.5 Datatypes
NCHAR, NVARCHAR, NCLOB
OCTETS, CODEUNITS32, CODEUNITS16
LONG VARCHAR, LONG VARGHAPHIC
Physical Parameters
Tables
Columns
Indexes
Tablespaces
Unmanaged physical parameters
UNIQUE Constraint
Organize_sequence_key-clause Clause
‘EVERY \ DB2’ Properties
MEGA Database Builder
Physical Parameters
This site works best with JavaScript enabled