< Steps on Moving NetBeans Project | Java & Friends Activities | DBDesigner: Analyzing A Web Site >

 


 

 

INSTALL, TEST AND USE DBDESIGNER:

AN OPEN SOURCE INTEGRATED DATABASE TOOL

 

 

Contents:

  1. Introduction

  2. Download and Install DBDesigner 4

  3. Using DBDesigner 4

  4. Bug or What?

  5. Saving the Database Connection

  6. Reverse Engineer the Existing Database

  7. Getting Familiar with Some of the DBDesigner Menus

  8. Saving the Database Model

  9. Using the SQL Query Editor

  10. Executing SQL Statements/Scripts

  11. Designing Database Model/Database Diagramming

 

Introduction

 

The DBDesigner is an open source visual database design tool that integrates database design, modeling, creation and maintenance into a single, seamless environment. It combines professional features and a clear and simple user interface to offer the most efficient way to handle databases. We can use DBDesigner to model a database, execute SQL query, create Entity Relationship Diagram (ERD), reverse engineer the databases and many more.

The latest version of DBDesigner is comparable to products like Oracle's Designer ©, IBM's Rational Rose ©, Computer Associates's Erwin © and the Kompany's DataArchitect © but it is an Open Source tool that available for Microsoft Windows © 2k/XP and Linux KDE/Gnome.

In this part we will try to install and use DBDesigner on Windows XP Pro SP2+ and get familiar with it. The database used is MySQL 5.x.x and in another tutorial we will use it to analyze a database at design stage.

The following list is the machine specifications used to install DBDesigner in this tutorial:

 

Download and Install DBDesigner 4

 

First of all, go to fabforce.net to download the latest version of DBDesigner for Windows. The self-extracting Windows exe file DBDesigner4.05.6_Setup.exe) is shown in the following Windows explorer snap shot. Double click the file.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Choose the language that suit to yours. Click Next.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Accept the GNU general public license and click Next.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Set the installation folder. In this case we just accept the given default installation path. Click Next.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Select the component that you want to install. We select all components here and click Next.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Select the shortcuts and click Next.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

The installation wizard is ready to install. If you want to change anything just hit the Back else hit the Next.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

The DBDesigner installation begins.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

The DBDesigner installation completed successfully. Click Finish.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

The following Figure shows the short cut added to Windows Start menu and the desktop. Click the DBDesigner or the short cut icon on the desktop.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Using DBDesigner 4

 

The following shows the DBDesigner GUI interface.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

In the following tasks we would like to open a MySQL database sample and getting familiar with DBDesigner. Click File > select Open from Database sub menu.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

In the Select Database Connection windows, expand MySQL node > select the Localhost node.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Fill in the MySQL user name and its password. In this case we are using root user. Click Connect.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Bug or What?

 

Well, for the first time using the DBDesigner you may encounter the "dbExpress: Invalid Username/password" message. We don’t know what the ‘bug’ is.

Searching the Internet, the solution is to reset the MySQL password using the following command. You can use similar or current MySQL password for user root in this case. The following example shows the MySQL root user. Change accordingly to other user and the host (here we use localhost). It is done through MySQL Command Line Client console.

 

SET PASSWORD FOR 'root'@'localhost' = OLD_PASSWORD('your_old_root_password_or_same_password');

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

If the previous step successful, all MySQL databases in the localhost will be listed as shown in the following Figure. Select a database sample available in your localhost machine and in this case we select the Waterbill database.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Saving the Database Connection

 

To save a connection for the specific database, click the New Database Connection button and fill in the information as shown in the following figure. Next time when we want to connect to Waterbill database, no need to fill in the credentials any more. Click OK.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

The following Figure shows the saved connection to Waterbill database. While the new saved Connection was selected, click the Connect button. (The Waterbill MySQL script can be found in ERD relational model notes).

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

If there is no database model for the selected database, the list is empty as shown in the following Figure and the Load Model link is not useful. Just click the Close link.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots  

 

Reverse Engineer the Existing Database

 

The Waterbill database doesn’t have a database model that normally used during the database design stage. However using DBDesigner, we can reverse engineer the existing database, to create a database model.

To do this, click the Database menu > select Reverse Engineering sub menu.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Select the previously saved connection > fill in the password for root user if needed > click Connect.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

In the Reverse Engineering window, select all tables under the Tables list. Select Build Relationships tick box and make sure Build Relations based on Primary Keys radio button is selected. Click Execute link.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

You may need to re-arrange all the tables by click-hold-drag-drop.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Getting Familiar with Some of the DBDesigner Menus

 

Next, click the Display menu > select Notation > select EER[1,n]. In this case we change the Crow Foot notation to EER[1,n] (Enhanced Entity Relationship diagram).

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Next, click the Display menu > select table Columns > select Primary Key Level. So we can display in several different view to assist our design. We can edit and save the model later. We found that a database that violates the basic standard design will generate errors during the reverse engineer process.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Saving the Database Model

 

Let save this database model for future use and the saved file format is XML. Click File > select Save > choose the location > fill in the file name > click Save.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Close all.  Click File > Close All.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Next, let reverse engineer another database to create a database model. Follow the same steps as done previously. Firstly make a connection to a selected database. Click File > select Open from Database.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Select a database that we want to connect (and load a model if any). In this case we select crssystemdb database as an example (The crssystemdb database MySQL script can be found in analyzing database using DBDesigner). Click Connect.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

This database also doesn’t have a model, so click Close. We will reverse engineer this database.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Notice that the connection to the database was established indicated by the message at the bottom-right most of the DBDesigner GUI.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Next, reverse engineer the database. Click Database > select Reverse Engineering.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Select the database and click Connect. You can use any MySQL database that available in your machine. Make sure the database has a sample data in the tables.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Make sure all the settings are similar to the following Figure. Click Execute.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Re-arrange all the table model.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Using the SQL Query Editor

 

Next, click Display > select Query Mode.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

The query window opened at the bottom.

 

 

--------------------------------------------------------------------------------------------------------------------------

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Executing SQL Statements/Scripts

 

Let try executing the following SQL statement.

 

SELECT * FROM student;

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Click the Execute button ( Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots ). If the following database connection selection displayed, click Connect.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

The query result can be seen on the right window. You can try other SQL queries in this query mode such as update, insert and delete.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Designing Database Model/Database Diagramming – ERD etc

 

To design a new model, you can start to create a new project. Switch to Design Mode > File > New.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Click the New Table icon and then click again on the design canvas.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Select the table object > right click mouse > select Edit Object.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Fill in all the necessary information and click the tick on the right-bottom when finish.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

After finishing the database design, you can generate the SQL script for Create, Drop and Insert by using the following context menus (the last three context menus). The Edit Table Data only available if the table has data in it.

 

Step-by-step on install, test and use DBDesigner: database modeling and designing tool with MySQL screen snapshots

 

Next, if you want to insert data into the Staff table, switch to Query Mode and execute SQL insert script as done previously. Repeat the same steps for other tables, add relationships etc. Well, many more can be done and all are left for you to explore. In the next tutorial, we will use DBDesigner to analyze a database used in a web application.

 

 

 

 

 


 

< Steps on Moving NetBeans Project | Java & Friends Activities | DBDesigner: Analyzing A Web Site >