Lab Two

A GUI-based client and more MySQL practice

Before starting the lab, remember that you must start your MySQL server!

For this lab, we will be using the product DBVisualizer (free version) from the Swedish company Minq Software (www.minq.se); this has been installed on the lab computers with the permission of Minq. If you want a copy of your own for your home computer, you should download it directly from Minq (registration required, but free).

DBVisualizer is just one of a number of GUI-based MySQL client programs, which provide a more intuitive and attractive interface to the server than the command-line client that we used in Lab One.

Create a new database

First however, using the command-line client, create a new database called "dbbook" and move into it.

CREATE DATABASE dbbook;

USE dbbook;

and from the downloads area use the scripts from the "Faculty/Students/Enrolment Example" that will create and populate (i.e. insert data into) some tables.

Simply save all these scripts into the same area that you are running the mysql client from, and then type

source example.sql

and this will load all the remaining files. Have a look at code in "example.sql" and see how it loads the files with a ".txt" extension; this is an alternative way of loading a large number of rows more quickly than individual "INSERT INTO" statements.

Now quit the command-line client.

Open the GUI-based client

Now open DBVisualizer (contained in the folder called "databases" on your desktop) and use the "Tools | Connection Wizard" to create a connection to the MySQL server on your machine. During this process you will be asked to choose a name (anything you like), then select a database (choose MySQL, even though it may have a red cross next to it) and then select a driver - the driver you are after is found at C:/Program Files/Java/jre.1.6.0_03/lib/ext/mysql-connector-java-5.1.5-bin.jar
Once this is loaded, you will then specify a host (localhost), a database (you can use "test" or one of the ones already established on your own database), user (root) and a password (nothing) and finally you can then test the connection. If all succeeds, then press "Finish" and your connection will be set up.

A common problem may occur in using DBVisualizer:

• If the name of the driver is entered incorrectly, then DBVisualizer seems to "remember" the incorrect name and you are prevented from entering the correct location. The incorrect location can be removed by choosing "Create Database Connection" and then saying "No" when asked if you want to use the New Connection Wizard, and finally manually editing the list of locations. This step is a bit tricky to describe so ask the lab demonstrator for help.

Then perform the following tasks:

1. Select "dbbook" on the panel on the left-hand side and notice that the names of 4 tables are listed in the main window.
2. Now click on the tab "References" that is at the top of the central panel and see a visualization of the foreign keys in the tables.
3. On the right-hand side there is a panel labelled "Graph Controls" which includes a drop-down menu called "Node Content". Select the option "Columns" from this menu, and see what it shows you.
4. Experiment with the basic controls under the two major headings "Object View" which allows you to inspect the database and "SQL Commander" which allows you to run SQL statements.
5. Reverse-Engineer this database schema into a plausible ER diagram, including key and participation constraints. [You will be asked to hand this as part of your next tutorial]
6. Look at the data in the tables and make sure you understand this database and what each column represents.

Practice MySQL Queries

Now we will experiment with some more sophisticated MySQL queries involving multiple tables from the "dbbook" database that we have just explored. Write queries for the following tasks and make sure that you note them down into your lab workbook! A portion of the exam will be on MySQL and you will need to recall how to do these tasks.

The tables for this come from the textbook and these questions are loosely modelled on Exercise 5.1 of the text. Chapter 5 in the textbook covers SQL queries very quickly!

1. Find the names of all the Juniors (in an American university students are either Freshmen, Sophomores, Juniors or Seniors basically corresponding to 1st, 2nd, 3rd and 4th year).
2. List the names, rooms and times of all classes that have somebody enrolled
3. Find the names of all the classes that have any student aged exactly 19 enrolled; make sure that your query does not produce any duplicate rows.
4. Find the names of all faculty members who teach a class in room R15.
5. Find the names of all students who have a class in room R15.
6. Find the names of all the students who are taught by Ivana Teach.
7. Find all pairs of students who are the same age.
8. List all pairs of classes that meet at the same time.
9. List all the students that are enrolled in (at least) two different classes.
10. Find the name, room and teacher of all classes that have (at least) two students who are at least 19 years old.

Hints

• You will need to know that <> is the MySQL symbol for not-equals.
• When looking for "two different" things you will need to list (and name) the table twice in the FROM statement in order to form the cross-product of the table with itself - for example SELECT * FROM student S1, student S2 WHERE ...
• None of these queries require anything more than the basic SELECT statement, so you do not need to use aggregate functions or subqueries or any other advanced features.