Enterprise Architect; Database Builder Review
Database Builder; Overview
Enterprise Architect has for a long time provided the functionality for:
- Modelling Physical Databases
- Generating DDL so that a schema for the Physical Database can be executed on the target DBMS
- Reverse Engineering an existing DBMS schema to create a Physical Data Model
- Compare a Physical Database model in Enterprise Architect to an existing DBMS schema
Later Sparx Systems added to Enterprise Architect added the functionality to generate the DDL statements for updating an existing DBMS schema with changes that have been made to the Physical Database model.
The modelling of the Physical Database and the execution of the DDL statements required two separate tools:
- Enterprise Architect – to model the Physical Database model and generate the DDL
- DBMS workbench such as Microsoft SQL Server Management Studio – to execute the DDL statements
Sparx Systems introduced new functionality with the release of Enterprise Architect version 12, which enables the modeller to perform all Physical Database modelling and execution of DDL and SQL statements within Enterprise Architect. This new functionality is called the Database Builder.
This article presents an overview of the Database Builder.
The Database Builder
So exactly what is the Database Builder? Quoting from the Help file of Enterprise Architect, Sparx Systems describe the Database Builder as:
“The Database Builder is a tailored interface for the data modeller, so that all database-related modelling tasks can be performed in a single location. The interface and its related screens include only the information relevant to data modelling, thereby streamlining and simplifying the modelling process.”
The Database Builder, when activated, presents a new View in the Enterprise Architect workspace, which is known as the Database Builder Interface.
Quoting again from the Enterprise Architect Help file, Sparx Systems describe the interface of the Database Builder as:
“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 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
- An Execute DLL 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 (Unique Constraints, Primary Keys, Indexes, 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 ad-hoc SQL statements against a live database”
So what does this mean to the modeller? Essentially, the Database Builder allows the modeller to:
- Create a Physical Database model structured using a pattern. The Database Builder provides numerous patterns for a number of different databases and their versions
- Generate the DDL for a database schema
- Create a connections to live databases and execute DDL and SQL statements against those database
- Synchronise a Physical Database and a live database schema, by showing the differences, generating the DDL and executing the DDL. This synchronisation is two-way, that is if a Physical Database model changes, the live database schema can be updated to reflect these changes. If the live database schema changes, then the Physical Database model can be updated to reflect these changes.
Using the Database Builder the modeller can target one or more of the following live databases:
- Microsoft Access
- Any supported DBMS accessed via ODBC such as Microsoft SQL Server, MySQL and Oracle
When using the Database Builder the modeller uses a different approach to that used in previous versions of Enterprise Architect.
In previous versions of Enterprise Architect, the modelling approach was:
- Model the Physical Database design using a Data Modelling diagram
- Generate the DDL
- Execute the DDL using the DBMS’s workbench
- Use the DBMS’s workbench to populate and execute SQL queries
- Make modifications to the Physical Database design using the diagram
- Use the Enterprise Architect schema compare tool to generate the DDL statements for synchronisation
- Use the DBMS’s workbench to populate the database and execute these DDL statements
When using the Database Builder the modeller:
- Defines Tables, Columns, Primary Keys, Foreign Keys, and Referential Integrity using the Database Builder interface
- Re-uses the Table Elements on a Data Modelling diagram to create a visual representation of the Physical Database design
- Generates and executes the DDL statements within the Database Builder interface
- Creates and stores SQL statements within the Database Builder interface, that will insert, select and update the live database
- Make modifications to the Tables, Columns, Primary Keys, Foreign Keys, and Referential Integrity using the Database Builder interface, hence updating the visual representation of the Physical Database design
- Perform the synchronisation of the Physical Database model and the live database directly within the interface
In addition to the above, the interface also provides the modeller with the ability to:
- Create and manage indexes
- Create and manage constraints
- Create and manage triggers
- Create and manage stored procedures
In this article we have presented an overview of the new Database Builder introduced by Sparx Systems into Enterprise Architect version 12.
Phil Chudley, Principal Consultant at Dunstan Thomas Consulting recently presented on this topic at the April 2016 EA User Group Event in Gothenburg. You can download a copy of the slides below.
|Physical data modelling with the Database Builder|