Reverse engineering a database using DBDesigner - and creating SQL code using DBDesigner.
This workshop assumes that the 'company' database we have used in previous workshops exists on your machine. If it doesn't (you can check by logging in to MySQL and using SHOW DATABASES;) then follow the instructions at the beginning of workshop 2 to create the company database and populate it with some sample data.
Reverse engineering involves taking the original SQL source code and transforming it into an ER diagram that represents that code. We are going to take the 'company' database and generate an ER diagram for it.
In the labs: Start up 'DBDesigner + MySQL'
At home: Under windows you probably have MySQL server already running (green traffic lights at bottom right of screen indicate this) - if not start up MySQL from a command window (if you are not sure how to do this refer to the earlier workshops).
In DBDesigner do File\New
Select the 'crow's foot' notation by: Display\Notation\Crows Foot
Then: Database\Reverse Engineering
You will be presented with a window which describes the databases you can connect to. You probably won't have connected to a database using DBDesigner yet - so we must set up a new connection: If you click on the 'localhost' icon, the 'company' database should appear.
Click on the button 'New Database Connection'
The 'Database Connection Editor' will appear. You should give the connection a name such as 'myFirstConnection'
The Hostname should be 'Localhost' (no IP number required)
The database name you are connecting to should be 'company'
The username should be 'root' and no password is required
Click OK
If you then click on the Icon on the left representing the 'Localhost' computer it should list the new connection you have set up.
Select this connection and then click on 'connect'
The 'Reverse Engineering' window should then appear. You will see that it has identified all the tables in the 'company' database
Click on 'Build Relations' and make sure that 'Build Relations Based on Primary Keys' is selected.
Click on 'Execute'
DBDesigner then draws the ER diagram for you, based on the SQL code for 'company'. You may have to drag things around on the screen to make the ER diagram easier to look at.
Question: Does the ER diagram look as you expected?
Creating queries using DB designer - previously we have created queries by directly typing in SQL code. However, it is also possible to do this using DBDesigner as a graphical front end. SQL 'selects' can be easily build by drag'n'drop commands.
Make sure the current worktool (mouse pointer) is set to a Pointer Tool before starting.
We must be in 'Query Mode': Display\Query Mode
We will build a simple SELECT on the customers table. To do this simply click on a table with the left mouse button and keep holding the button. Now move the mouse cursor down. The 'Drag Menu' is displayed.
Keep holding the left mouse button and move the mouse cursor to the SELECT button. Release the mouse button. The SELECT statement will be built and displayed in the SQL Command Editor on the bottom left of the screen. You will notice that when a SQL command is built using the 'Drag Menu' all columns of the table(s) are selected using the SELECT * clause.
To select specific table columns choose the SELECT Clause Tool from the Toolbar on the left. The mouse pointer will change to the SELECT Clause cursor. Now move the mouse over the table and watch the columns be highlighted. Move the mouse cursor to the column you want to select (customer city) and press the left mouse button. Again, you will see the SQL code appear. Use the SELECT tool again on 'cust_name' (as we would like to display the customer name as well as the city they live in)
Say we wish to select our customers that live in Detroit. Click on the WHERE Clause Tool from the toolbar and click on 'cust_city'
Finally, manually modify the code so it looks like:
SELECT c.cust_city, c.cust_name
FROM customers c
WHERE c.cust_city = 'Detroit';
In order to execute this query, we must make sure we are connected to the database (this is different from the 'Reverse Engineering' connection. So:
Database\Connect to Database\
Then click on the connection you have just set up to the database 'company' on the local host (called 'myFirstConnection') and click 'connect'
To run the SQL query you have just generated, click on the Icon to the right of the SQL code that looks like a database with a green lightning flash over it.
On the right you should see the results of your query.
To store the SQL statements you have just written, click on the Icon that looks like a floppy disk with a green lightning flash in front of it
You will be asked for a name for the stored statements, try 'myFirstStatements'.
Then a little window appears on the left showing you your stored SQL statements. If you click on this so that 'myFirstStatement' appears, right click and then select 'Execute SQL command' your stored commands will run and the results will be output on the right.
In this way, you can construct and store complex series of SQL statements and execute them when required. Your queries are stored in the DBDesigner model file, so if you want to use them on another computer, you should keep a copy of this file, (and the database it connects to!).
It is suggested that you now repeat the above process to construct some more complex queries. Suggestions include (though you can try your own if you like) - don't worry if the alias names generated automatically by DBDesigner are different from your own:
Find names and prices of products from vendors in a particular list:
SELECT prod_name, prod_price, vend_id
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01')
ORDER BY prod_name;
and:
Here we retrieve the order number and number of items ordered for all orders containing 3 or more items, and sort the output by the order number and the number of items ordered:
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
and the join:
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers as c1, Customers as c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
It is not just SELECT statements that can be run in this way. You can CREATE tables, INSERT, DELETE and UPDATE data. If you try to insert duplicate data that already exists in the database, DBDesigner will flag up an error. For example, you could use this code to insert details about a new customer:
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('111111', 'NewToys', 'Long Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', '');
If you run the code twice, DBDesigner will flag an error as the record already exists in the database when you try to insert it for the second time.
To COMMIT changes to the database, click the icon on the far right of the screen that has a green arrow on it.
To ROLLBACK (discard changes) click on the icon with the blue cross
Make sure you save your database model before shutting down DBDesigner.
2