Embarcadero ER Studio and Delphi - Felix John COLIBRI.

• abstract : a quick tour of ER/Studio from Embarcadero
  
• key words : quick tutorial, Entity RelationShip, Embarcadero, Database Design
  
• software used : Windows XP Home, Embarcadero 7.5.1, trial version
  
• hardware used : Pentium 2.800Mhz, 512 M memory, 140 G hard disc
  
• scope : database designers, Delphi developers
  
• plan :
  
  • Embarcadero Product Range
    
  • Embarcadero ER/STUDIO
    
  • My two cents
    

1 - Embarcadero Product Range

We will present here a very short introduction to ER Studio

2 - Embarcadero ER/STUDIO

2.1 - Entity Relation Design Tool

ER tools are similar to UML tools. For UML, we try to organize our CLASSEs, by grouping attributes and methods and showing relations between those CLASSes. For database design, we try to group the data attributes into Tables, making relations between those tables explicit.

2.2 - Download and Installation

9 minutes announced. It took us 35 minutes, because we are using a slow ADSL line (62K/sec).

125 Meg .ZIP which contains the ER Studio setup (as well as the "repository" tool, used for group design, which we did not install)

A 37 page .PDF installation guide (mainly for upgrades, licenses and for the group design product which requires details about the Sql Engine you select)

Installing ER Studio is very quick

• InstallShield starts
  
• you only have to answer to a single questions (do you already have a licence or not)
  
• clicking "Ok" to the other dialogs installs the product
  
• 2 minutes later the product was installed.
  

Uninstall is also easy.

Well, if they only could convince CodeGear to replace InstallAware by the previous Installshield, this alone would be a huge reason to cellebrate ! And since InstallShield now also belongs to Thoma Cressey Bravo (the owner of Embarcadero), there might be some motivations to convince CodeGear ...

2.3 - The Main IDE

and

• (1) is the Diagram Explorer
  
• (2) is the Diagram Window
  
• (3) is the Overview Window (a panning and zooming tool)
  
• (5) a zoom window (not displayed on our figure) where the area underneath the cursor in the Diagram Explorer is displayed
  
• (6) several toolbars which can be reorganized and docked anywhere (at the bottom of the window, or vertically at the right, for instance)
  

• the Data Model tab displays the model structure (logical, physical and sub-models)
  
• the other tabs ( at the bottom (4) of the Diagram Explorer) are
  
  • Data Dictionary tab (Domains, Rules, Defaults ...)
    
  • Data Lineage: used to convert data sources to a target (physical) model. Used to build data warehouse targets by mapping for instance source columns to data warehouse columns
    
  • Macros, which allow you to automate design tasks (naming, coloring ...). Those macro are written in the "Sax Basic" language, and this tab allows you to use predefined macros and write your own
    
  • Repository for developer group work and storing objects for later reuse
    

2.4 - Creating a new model from scratch

	start ER/Studio
	the empty ER/Studio will be loaded (no Diagram Explorer and no Diagram Window)
	select "File | New"
	a dialog asks you what kind of model you want to create
	click "Ok"
	the (empty) Diagram Explorer and Diagram Window are presented
	select "File | Save" (or "File | Save As") and save your model

2.5 - Adding Attributes

	from the "entity / relation / user" toolbar, select the Entity: and click in the Diagram Window
	the new entity box is displayed (Ed: resized)
	since the entity name is in edit mode, type the name of our entity, Customer
	click the "selection arrow" to avoid adding a new entity at each click in the Diagram Window
	to add a key to our Customer entity, right click on the Customer entity
	the Entity Editor is displayed (Ed: scaled 75 %)
	click on the bottom left "Add" button Fill the attribute name ("CustomerId") type ("Counter") and check the "Primary Key" checkbox
	a new attribute is created
	click "Ok"
	the new key is displayed in the second part of the entity box:
	add other attributes like "Name", "Address", "Phone" to this entity

• the Entity Editor has many many possibilities to add constraints, checks, definition (some textual comment) etc
  

2.6 - Adding Relationships

	from the "entity relationship" toolbar, select the "Identifying Relationship" icon and click on the "Customer" entity box and on the "Invoice" box
	the relationship is displayed

• we had first included a "CustomerRef" attribute in the "Invoice" entity, only to see that ER/Studio automatically adds the foreign key. So we removed our "CustomerRef" attribute
  
• you cannot remove the "CustomerId (FK)" attribute, unless you first remove the relationship, which is quite sensible
  

2.7 - Changing the Presentation

First we will change the layout of the entities and relationships. Of course, with two lonely entities this is not going to make big modifications, but, anyway

	from the "layout and alignment toolbar", select the "Orthogonal Layout" Icon and click this icon
	here is the result

Note that

• by resizing the Diagram Window before selecting the layout, the layout selection will resize and position the diagram according to this new size
  

	from the "selection" toolbar, select the "change display option" icon:
	ER/Studio warns us that the undo history will thereby be cleared:
	click "Yes"
	the "Diagram and Object Display Options" dialog is displayed
	select the "Entity" tab, and then the "Entity" radiobutton: and then click "Ok"
	the diagram is displayed only with the entity names:

2.8 - First design impression

• there is an incremental numbering (of the model, of the items etc). If you remove an entity and add a new one, the numbering will still grow. This is not very important since we rename everything anyway

• the selected icon will be automatically in "duplicate mode": each click in the Diagram Window will create a new item. To stop the duplication, select the traditional "selection arrow"
  
• the same goes for cursor selection: if you select the hand cursor or the resizing cursor, to get the simple cross-hair cursor, just click the "selection arrow"
  
• when you hit "Delete" to remove an item, you must confirm the removal each time (there must be an option somewhere but the default is to ask confirmation)

• zooming in and out is not too obvious at the beginning. What we understand right now (without reading the manual in detail):
  • the Overview Window has a default selection rectangle
    
    • when the cursor is inside this rectangle, the cursor is a hand, and moving this hand shaped cursor will pan the central Diagram Window
      
    • if you move outside this rectangle, the cursor becomes a magnifying glass, and drag-and-drop will define a new selection rectangle
      
  • we can also redefine the zoom factor by selecting the magnifying glass and drag-and-drop the cursor in the Diagram Explorer to define the new zoom factor
    
  • when going back and fore between two models, the hand crafted model would have a 0 % zoom factor, making all items invisible. Clicking on an item in the Diagram Explorer resets the zoom factor to some reasonable value. There must be a way to lock the zoom factor. This behaviour was not observed when importing any of the demo model. So we missed some step somewhere
    
• the overall IDE is NOT based on the traditional "Palette / Object Inspector / Designer" metaphor. The Diagram Explorer lets you select (one or several) objects, and menus, toolbars and contextual menus allow us to initialize or modify values. This is rather efficient when we have to add many attributes to an Entity.

  This is not too disturbing, since the "one single entity attribute / one item in the Object Inspector" way of handling Together diagrams really is a pain.
  

2.9 - Reverse Engineering databases

• native connection for
  
  • Oracle
    
  • Sql Server
    
  • Sybase
    
  • Db2
    
• ODBC connection for
  
  • Access
    
  • Informix
    
  • Interbase
    

2.10 - Reverse Engineering DBDEMO.MDB

To build the ER diagram from Access, there are two steps

• define an ODBC for Access
  
• create a "reverse engineered project" using this connection
  

2.11 - Creating the ODBC DataSource

	start ER/Studio
	select "File | New" and in the "Create New Model" dialog select "Reverse Engineer an Existing Database" and click "Login"
	the Reverse Engineer Wizard is displayed (Ed: scale .90):
	select "ODBC" and clic "Setup..."
	the Windows Odbc Manager is displayed
	click "Add"
	a dialog asks which driver we want to use
	select "Microsoft Access (*.mdb)"
	a dialog "Install ODBC for Microsoft Access" is displayed
	give a name to the ODBC source (say "my_access_odbc") and click "Select"
	an old Windows 3 "open dialog" window is presented
	navigate to the folder containing the .MDB. In in our case it is located in   c:\program files\shared files\borland shared\data\` and select "DbDemos.Mdb" and click "Ok", "Ok", "Ok"
	we are back to the Reverse Engineer Wizard
	in the "DataSource" combo, select our new "my_access_odbc" datasource and click "Next"
	the second page of the Wizard asks us which objects should be extracted : user Tables, System Tables, triggers etc
	Apparently Access has not much to offer, which is no surprise. But anyway, select "User Tables"
	at this stage you can go to page 3 to 5 of the Wizard. In our case we decided to click "Finish"
	after a couple of seconds, a dialog tell us that the diagram has been extracted:
	click "Close"
	the Diagram Window displays the DbDemos Access schema:

2.12 - Creating Sub Models

Here is how to build a sub-model with the "connected" parts of DbDemo:

	in the Diagram Window push the Control key, and click on each of the entities you want to include in your sub-model:
	create the sub-model by selecting "Model | Create Sub Model"
	a dialog is displayed asking for the name
	type the sub-model name, say "orders_sub_model"
	the selected items are included in the sub-model

While we are at it, let's also add some colors to this sub-model:

	select "Format | Colors and Fonts"
	the color and font dialog is displayed
	select some item, say "Entity BackGround", then click "Set Color"
	a standard color dialog is displayed
	select a color, like yellow Click "Ok" and "Ok"
	here is our gorgeous sub-model display:

2.13 - Generate the Physical Model

ER/Studio can generate physical models, which are used to fine tune and optimize the design for some specific Sql Engine.

We will generate an Interbase physical model from our previous DbDemo model:

	select "Model | Generate Physical Model"
	a dialog warns us that we will no longer be able to undo changes
	click "Yes"
	the "Generate Physical Model Wizard" is displayed
	then type the model name ("ib_dbdemo_physical") select "Relational" set the "target Database Platform" to "Interbase"
	the next Wizard pages allow you to set (physical) Indices, naming conventions, storage file name, ways of handling many to many relationships. In our case, we simply clicked "Finish"
	the Model Validation Wizard steps in
	select "Select All" and click "Run Validation"
	since we did not take the time to specify how the generation should be performed, there are MANY violations. We would go back and look in detail to those generation parameters, and remove those violations (not done in this tutorial)

2.14 - Generate Reports

	select "Tools | Generate Reports"
	the "Report Wizard" is displayed:
	set the directory, select the type of report (.HTML in our case) and click "Next"
	the page 2 of the Wizard asks which parts of the model should be documented:
	select the objects you want documented. In our case we clicked BOTH "Select All" buttons
	you can select other options, or like we did, click "Finish"
	the reports are generated, and the success confirmed: Since we accepted to see the first report page, this page is displayed. Navigating in the report we can display the model's information: The report includes the attributes, indices, Sql script, where used etc.

2.15 - Some other possibilities

• add denormalization, which is a physical tuning option. For instance breaking horizontally a table with many records in two separate tables. Or vertically split a Table with many columns into two Tables
  
• generate or import metadata
  
• create a Data Warehouse design, which is performed by deriving a Dimensional Model from the Entity / RelationShip model. This amounts to create Cubes. According to Ralph KIMBALL, this is a model equivalent to the Entity / RelationShip model, but much more understandable to the database users.
  
• place some objects in a repository in order to share the work with other developers
  

3 - My two cents

3.1 - Overall Impression

For this very small tour of the product, we read once the QuickStart guide, played with the Studio for about an hour, and then started to write this article.

If you sometime used Model Maker, Together, Sparx System, you will be in business in no time. You will luckily now belong to the "Database Designer" folks, and are immediately entitled to a substantial salary raise !

If I had to give some advice,

• read the QuickStart guide, and if you have installed the product, follow the steps
  
• if you are not familiar with Data Warehousing, Google around to find out about "Dimensional Modeling", "Star", "SnowFlake". It took us some time to understand that this is simply the concept underlying Cubes (and pivot tables)
  

3.2 - About ER/Studio

• the design is logically organized
  
• all toolbars have a menu counterpart
  
• the graphical possibilities very complete.
  
• the interface is also less clutterd than Model Maker (but we understand that ModelMaker is more a "Delphi Block Editor" for object oriented and Design Pattern development)
  
• some names are not the one we are used to in the Delphi world: "Diagram Structure" instead of "Object Treeview" (Db Model objects), "Diagram Window" instead of "Designer" (or "ER Designer") etc
  

The documentation is relevant and free

• in the manual, we would have prefered to place the details of the different import options (from other ER tools) in an appendix
  
• on the Help side, I now would prefer the H2Help system (the one used by Delphi 2006) because of its treeview-presentation structure
  
• the demos include nice models which can be imported to have a better feeling of how to handle bigger databases.
  

• no hassle downloading the trial version (no e-mail for registering etc)
  
• an ultra fast install
  
• simple uninstall
  

3.3 - What about Delphi

Nevertheless, there still could be immediate benefits:

• Together is an UML tool. We already stated that we feel that it is way to complicated:
  • first it is a "full fledged UML tool"
    
  • "designer / Object Inspector" way of operation makes adding attributes quite cumbersome
    
  • with Delphi, it is mainly geared toward code generation and as ECO's first step.
    
  For analysis and design, we prefer to use our simpler UML graphing tool.

  So in the database design area, ER Studio could be a nice replacement. Right now, it is targeted toward big (many tables, big corporation) databases, but certainly can be used for less massive data volumes.

• using ER Studio for InterBase could also be a good possibility. They already somehow include InterBase in their target Sql Engines, but not yet as a "first citizen" native Sql Engine, like they do for Oracle or Sql Server.

• in the SQL generation area, some of Embarcadero's expertise could also be used to replace the Active Script Builder. This is an external product, and could be replaced. We all know that the "Data Explorer" part of Delphi needs some cleanup: so many different ways of operation (whether you talk to dbExpress, Bdp, IbExpress, Ado, you have quite different dialogs and wizards, and in some areas, even the BDE data explorer had more functionalities !)

  Schema documentation and query optimization are maybe other possibilities

• Tiburon's roadmap first point stresses the drive toward improved database (dbExpress, DataSnap, multi tier). So design tools and Sql editors could become very handy.
  

3.4 - Feedback

• please tell us at fcolibri@felix-colibri.com if you found some errors, mistakes, bugs, broken links or had some problem downloading the file. Resulting corrections will be helpful for other readers
  
• we welcome any comment, criticism, enhancement, other sources or reference suggestion. Just send an e-mail to fcolibri@felix-colibri.com.
  
• or more simply, enter your (anonymous or with your e-mail if you want an answer) comments below and clic the "send" button

  Name :
  E-mail :
  Comments * :

• and if you liked this article, talk about this site to your fellow developpers, add a link to your links page ou mention our articles in your newsgroup posts when relevant. That's the way we operate: the more traffic and Google references we get, the more articles we will write.
  

3.5 - John DOE's opinion about Codegear's Purchase

Don't expect me do give my opinion commenting Marco's opinion (5/128) which comments such and such's opinion conerning Greg KELLER's blog post. I suggest to directly go to Greg KELLER's or David I's original posts

4 - Embarcadero Links

• Embarcadero and CodeGear, the blog from Greg KELLER, Embarcadero VP-Products at Embarcadero. A very enthusiatic perspective
  
• Borland News Release "Borland Reports First Quarter 2008 Financial Results and Announces Agreement for Sale of CodeGear Unit", the Borland side announcement, with the first quarter Borland financial results. Well Borland, game over ...
  
• Sip From the Firehose Breaking news: Embarcadero Technologies agrees to acquire CodeGear from Borland - by David INTESIMMONE, VP Developer Relations, Codegear.
  
• ISVs and MicroISVs: You’re not only covered ... - Michael SWINDELL, VP of products, Codegear
  

• Embarcadero's products
  
• ER Studio's Page
  
• ER Studio User Guide a 600+ page .PDF manual
  Quite detailed, even going down to explain what ER modeling is, logical, physical design etc
  
• TheQuick Start Guide is a guided tour or ER/Studio. A very similar document, the Evaluation Guide can be downloaded from the ER/Studio documentation page
  
• technical papers about Database Design.
  

5 - The author