CPSC 343 Database Theory and Practice Fall 2017

MySQL Workbench and Data Definition

We'll be using a relational DBMS called MySQL, which bills itself as the "world's most popular open source database". It is a well-established, commonly-used, multi-platform, free relational DBMS (though there are also paid versions with additional functionality).

The MySQL software package contains both a database server and a basic text-based client program which lets you type in SQL commands and queries. We'll be using a GUI client called MySQL Workbench to interact with the database server - among other features, MySQL Workbench provides a graphical interface for creating and editing tables and entering and modifying data.


MySQL Workbench

Download and Installation

MySQL Workbench is available on the lab computers (both in Lansing and Rosenberg). If you want to use it on your own computer, you will need to install it:

Connecting to the Database

To connect to a database you haven't previously connected to, you must first create a new connection:

The connection should now appear in the list under "Open Connection to Start Querying". Double-click on the entry to open the connection, entering the password you've been given when prompted.

You should then see a window like the following:

Changing Your Password

If you want to change your password, go to the tab labeled "Query" or "SQL File" (likely the only tab open when you start MySQL Workbench for the first time), enter the SQL query

SET PASSWORD = PASSWORD('newpass');

and click on the "Execute" icon (). newpass should be replaced by the new password you want; it is case sensitive.

Close the SQL File tab and then the SQL Editor tab, which should return you to the main workbench screen. Try opening the connection again to make sure that your new password works.


The Library Database

Your task is to create a library database and populate that database with some data. The database will have the following tables:

BOOK(Book_id,Title,Publisher_name)
BOOK_AUTHORS(Book_id,Author_name)
PUBLISHER(Name,Address,Phone)
BOOK_COPIES(Book_id,Branch_id,No_of_copies)
BOOK_LOANS(Book_id,Branch_id,Card_no,Date_out,Due_date)
LIBRARY_BRANCH(Branch_id,Branch_name,Address)
BORROWER(Card_no,Name,Address,Phone)

Key attributes are underlined. The foreign key constraints are the following:


Data Definition in MySQL Workbench

Creating a Database

To create a new database, click on the "New Schema" icon (). Name your database in the form username_library where username is replaced by your username. (You won't have permission to create a database that doesn't start with your username_.)

When you click "Apply", a window will pop up showing the SQL command(s) that will be executed; verify that this is what you want and continue until the dialog boxes are closed. You should now see the new database listed under "Schemas" on the left side of the window.

Right-click on the database name in the "Schemas" list and select "Set as Default Schema". This means that relation names will be assumed to be from this database unless you specify otherwise when writing a query.

Creating Tables

Tables can be created in any order, with the restriction that tables referenced by foreign key constraints must be created before the constraint can be established. Let's start by creating the PUBLISHER table since it does not reference anything else.

To create a new table, click on the "New Table" icon (). Fill in the boxes at the top of the tab:

Next, specify the table's columns. One column, with a name of idPUBLISHER, is provided by default. Click on the various field values to change them:

Below the column entry area is a place where you can specify additional details of the column, including a comment. Normally the comment is much like a comment for a variable - it describes something important about the column that isn't readily apparent from its definition. For this assignment, use the comment field to include a brief rationale for why you chose the data type that you did for the column.

Repeat the process for the rest of the table's columns, including writing a comment with the rationale for your choices of data types. (Click on the blank row immediately below the last-specific column to add a new column.)

Click "Apply" when you are done. A window will pop up showing the SQL command(s) that will be executed; verify that this is what you want and click "Apply". You should now see the new tables listed under "Schemas" on the left side of the window. (Expand the little triangle tabs as needed.)

Close the table-creation tab.

Creating Tables with Foreign Key Constraints

Since the PUBLISHER table has been created, it is now possible to set up the foreign key constraint in BOOK that references the publisher name.

Create the BOOK table in the same manner as the PUBLISHER table, but note that both the referenced and referencing columns must have the same data type. Make sure you choose the same data type for the Publisher_name column in BOOK as you did for the Name column in PUBLISHER.

To specify the foreign key, click on the "Foreign Keys" tab near the bottom of the table-creation tab. Start by giving the new foreign key a descriptive and unique name (under "Foreign Key Name" - click in the first blank row) such as fk_BOOK_publisher - "fk" for "foreign key" (as opposed to another kind of constraint), "BOOK" for the referencing table, and "publisher" to indicate that this foreign key has to do with the publisher of the book. Now make sure the foreign key's name is highlighted and fill in the rest of the boxes:

Click "Apply" when you are done. A window will pop up showing the SQL command(s) that will be executed; verify that this is what you want and click "Apply". You should now see the new table and foreign key listed under "Schemas" on the left side of the window. (Expand the little triangle tabs as needed.)

Close the table-creation tab.

Modifying the Table Schema

If you find that you need to modify the structure of an already-created table (e.g. to add or remove columns or change a column's data type), right-click on the table's name under "Schemas" on the left side of the window (expand the little triangle tabs as needed) and choose "Alter Table...". If the changes involve attributes used in a foreign key, you'll need to delete the foreign key, make your changes, and re-create the foreign key. Delete a foreign key by right-clicking on its name in the "Foreign Keys" tab in the table-creation tab for the referencing table.

You can get rid of a table by right-clicking the table's name under "Schemas" and choosing "Drop Table...".

Modifying Table Data

To modify a table's data, locate the table's name under "Schemas" on the left side of the window (expand the little triangle tabs as needed), right-click on the table's name, and choose "Edit Table Data". (If there isn't an "Edit Table Data" option, instead choose "Select Rows".) Do this for the PUBLISHER table. You'll get a new tab with the top half of the area showing the query used to retrieve the data current in the table, and the bottom half of the area showing that data. (The PUBLISHER table is currently empty.)

In the bottom half of the window, click on the row showing only NULL values, then click on a particular field to enter new data. (This row doesn't really exist in the table; it's just a placeholder for entering new data.) Enter the following publisher:

DAW     375 Hudson St, New York, NY 10014       123-456-7890

If you find that you chose data types that won't accommodate this data, you'll have to edit the table's schema before you'll be able to insert the data.

Enter a second publisher:

Eos     10 East 53rd St, New York, NY 10022     212-207-7000

Click "Apply" when you are done, verify that the SQL command is correct, and click "Apply" again.


Finishing Up

Create the rest of the tables and foreign key constraints specified in the library schema.

Also add the following books to the BOOK table:

1       Foreigner                       DAW
2       Invader                         DAW
3       Mission Child                   Eos
4       Fortress in the Eye of Time     Eos

and the following authors to the BOOK_AUTHORS table:

1       C. J. Cherryh
2       C. J. Cherryh
3       Maureen F. McHugh
4       C. J. Cherryh

Checking Your Work

A sample solution called ex_library has been set up in MySQL. You should have access to it - use MySQL Workbench to connect to the MySQL server, then look in the "Schemas" section in the lower left portion of the window. ex_library should be visible.

To view how the solution tables were set up, go to the Query tab and type the SQL statement to view the table structure:

	  SHOW CREATE TABLE ex_library.BOOK;
	

(Replace BOOK with the names of the other tables to view those.) Click on the "Execute" icon () to execute the command, then choose "Form Editor" from the options on the right side of the window to get an easier-to-read view of the result - which is the SQL statement that created the table. You can create a second query tab (under the "File" menu) and do the same thing with your table (username_library.BOOK) so you can compare the two.

It is also possible to examine table structure by right-clicking on the table name in the Schemas view and choosing "Alter Table..." (You aren't actually going to change the table structure - you don't have permissions to do so anyway - but this will bring up the same view that you used to create your tables.) Viewing the SQL statement directly is better, however, as it is easier to miss settings that may be obscured in the table editor view.

You should have the same tables, attributes, and foreign keys as in the solution. You should also have the same basic choices for data types (integer vs fixed point vs floating point, character vs binary vs set, fixed length vs variable length for characters and binary, etc) or at least understand the reasons for the choices made in the solution, and should understand the rationale for the ON DELETE and ON UPDATE choices in the solution. Notes on the choices made:


Remote Access

The MySQL server on 172.21.7.83 is only accessible on campus. If you are not on the campus network, you will either need to run VPN (information) or use port forwarding (which requires ssh). For port forwarding, run the following on your local machine:

  ssh -CNL 9000:172.21.7.83:3306 username@math.hws.edu

Replace username with your CS department username, and enter your CS department password when prompted for a password. This command takes traffic sent to port 9000 on your local machine, sends it over the ssh connection to math.hws.edu, and tells math.hws.edu to then forward it to port 3306 (MySQL's port) on 172.21.7.83. You can change 9000 to something else if you want - it's just a port that is probably not being used by something else. Now run MySQL Workbench on your local machine and create a connection with localhost as the host name and 9000 as the port number.


Valid HTML 4.01!