Skip to content

Soccer Database Updates

The purpose of this lab is to practice the skills you'll need to complete Homework 4.

Step 1: Creating tables

The Forward Engineering lab showed how to generate an SQL script based on a relational database model. 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?
    • Which columns are indexed?
  • 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).

  2. Copy the data from List of soccer clubs in the United States into the CSV file. The format is not exactly the same; you'll need to make decisions about what data to include, and which columns to leave empty (NULL). Include all Major League Soccer (MLS) teams.

    • Note: Use \N to represent NULL values in your CSV file.

  3. 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.

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

Step 3: Insert, update, delete

Refer to the 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.