Relational Concepts in MySQL

The purpose of this lab is to introduce you to concepts of relational databases using the MySQL RDBMS and its Workbench tool.

Step 0: Setup

Please download and install MySQL Workbench

• You do not need to install MySQL server because we will provide a shared server.

Create a connection to the shared MySQL server

Each of you has a user account and private database on the MySQL server, which is running on a host named data.cs.jmu.edu. Your account name and database (schema) name are both your JMU eID. Your password is your student ID number.

On the main screen of Workbench, you can create database connections using the plus sign.

Create a new connection using the method Standard TCP/IP over SSH. You are going to connect to the server from stu. This setup will allow you to connect from off campus. Fill out the fields as you see in the figure below, replacing elkadima with your own eID. Then use the Test Connection button to test the connection.

You will first be asked to enter your password for stu. If you select to save the password, you won't have to enter it again.

You will then be asked to enter your password for the MySQL server. It's your student ID number. If you select to save the password, you won't have to enter it again.

Step 1: Creating tables

For today's lab, download and run SoccerModel.sql to create a fresh copy of the soccer database in your schema. Review and discuss the contents of this script with the students at your table.

Discussion questions

• Why does the script DROP TABLE before CREATE TABLE?
• Why is each table and column name enclosed in backticks?
• For each table:
  • Which columns are the primary key?
  • Which columns are foreign keys?
• What data types are used?
  • Explain the meaning of each type.
• Which columns are NOT NULL? Why?

Step 2: Loading CSV data

The LOAD DATA command can be used to import a CSV file (or other data file) into a table. As a team, complete the following steps.

Note: In order for LOCAL INFILE to work, you must add a setting to your MySQL Workbench connection. Right-click and edit your connection, go to the Advanced tab, and add the following line under Others:

OPT_LOCAL_INFILE=1

Getting real data

1. Download teams.csv and open the file using a spreadsheet app (like Microsoft Excel or LibreOffice Calc) to look at the data. These are the MLS (major league soccer) teams.

   • Note: Use \N is used to represent NULL values in a CSV file.

2. Write a LOAD DATA statement to import teams.csv into the TEAM table. Run the statement in MySQL Workbench, and resolve any errors that occur.

3. When finished, SELECT * FROM team; to show the contents of the table.

Step 3: Insert, update, delete

Refer to last week's lecture slides to complete the following steps. Save all code that you write in a file named soccer-crud.sql.

Working with games

1. Do a Google search for MLS schedule to see the upcoming matches.

2. Write INSERT statements for at least five matches. Leave the score columns (HomeGoals and VisitorGoals) NULL for now. When finished, SELECT * FROM game; to show the inserted rows.

3. Write UPDATE statements for at least three matches. Set the score columns to any values of your choice. The values should be reasonable (i.e., don't use a negative integer). When finished, SELECT * FROM game; to show the updated rows.

4. Write DELETE statements for 1–2 matches. Make sure you don't accidentally delete the other matches. When finished, SELECT * FROM game; to show the remaining rows.

Step 4: Violating constraints

Add the following code to the end of your soccer-crud.sql file. Be prepared to share your examples with the class.

Primary/foreign keys

1. Write an INSERT statement that violates a PRIMARY KEY constraint.

2. Write an UPDATE statement that violates a PRIMARY KEY constraint.

3. Write an INSERT statement that violates a FOREIGN KEY constraint.

4. Write an UPDATE statement that violates a FOREIGN KEY constraint.
5. Write an DELETE statement that violates a FOREIGN KEY constraint.

Going Further

If time permits, run the following statements:

ALTER TABLE GAME
ADD CONSTRAINT CHECK (HomeGoals >= 0 AND HomeGoals <= 10);

ALTER TABLE GAME
ADD CONSTRAINT CHECK (VisitorGoals >= 0 AND VisitorGoals <= 10);

Now try to INSERT or UPDATE a game with an invalid score.

Step 5: Forward Engineering from a Model

Load a model

Download and open the Soccer League model. In the MySQL Model tab, set or change the schema name to your schema name (your eID). Double-click the database icon above the list of tables to open the schema editor.

Forward Engineering

Select Forward Engineer... from the Database menu. This will open a screen for you to connect to the database server; is is almost identical to the one you used to define a database connection, and should be set up correctly.

Click Next to move on to the Options screen, where you can set up what you want to be generated.

Click Next to start generating the scripts. If you haven't saved your passwords, you may have to enter them again at this step. The screen will show the progress of the forward engineering. When you click Next again, you will be shown the generated script. You can edit it directly, copy it to the clipboard, or save it to a file for editing. Even if you are not making any changes, you should save it to a file (with the extension .sql). It is important that the schema name used in the script matches your schema name.

Click Next to run the script on the database server. If you haven't saved your passwords, you may have to enter them multiple times at this step.

This is the end of the forwarding engineering process, and your database has been built. You can close the screen.