CpS 301 Intro to Database

Lab 1: MySQL

Average time to complete: 3-5 hours

Overview

In this lab, you will install MySQL on your computer and use both MySQL Workbench and the MySQL Command Line Client to interact with the database server.

Required Equipment

You’ll need a Windows computer for labs 1 and 2.

Note for Mac users: I recommend that you use a Windows computer for this lab. Although it’s possible to install MySQL on a Mac, the instructional videos I provide are geared to Windows users. Also, you will need a Windows computer for Lab 2 (since Microsoft Access is not available for Mac) and for the project. If you don’t own or have access to a Windows computer, you can use one in CSLAB.

Collaboration Policy

Lab assignments in this class have a relaxed collaboration policy. You may give and receive help freely, and do not need to include an academic integrity statement to report the help. If you choose to work on this lab at the same time as another student, each of you must do the steps on your own computer.

Get Started

  1. Read about how to install MySQL: https://dev.mysql.com/doc/refman/8.0/en/windows-installation.html

    Then, download the MySQL installer to install MySQL 8.0 on your computer: https://dev.mysql.com/downloads/installer/

    I recommend downloading the full installer (mysql-installer-community), rather than the small web-based installer (mysql-installer-web-community). The web-based installer can fail in the event there is a network glitch during the install, and leave your system in a state that is tricky to recover from.

  2. Run the installer. Pick the Full setup type.
    1. During installation, you may be warned that your computer does not have the requirements for MySQL for Excel, Visual Studio, or for Python. You will not need those components and can proceed with the installation. You need the following components: Server, Workbench, Connector/ODBC, Samples.
    2. In the Configuration steps:
      1. Type and Networking: Accept the defaults. You may wish to deselect the option to Open Firewall Port, since this is needed only if you plan to access the MySQL instance on your computer from another computer (not needed).
      2. Authentication Method: Choose Use Legacy Authentication Method. This is needed to support development work you will be doing.
      3. Accounts and Roles: Set a root (administrator) password, but you do not need to create a MySQL User Account. This password can be something very simple (like “passw0rd”) because only users logged into your computer can use it to access your server. Write this password down; you will need it later in order to connect to your MySQL server.
      4. Windows Service: Accept the defaults to configure MySQL Server as a Windows Service, Start at System Startup, and run as a Standard System Account. These options streamline managing the server with the MySQL tools, and are reasonably secure, because by default MySQL allows logins only from the local computer.
      5. Server File Permissions: Accept the defaults.
      6. On the MySQL Router Configuration page, leave the “Bootstrap MySQL Router” option unchecked and click Finish.
      7. On the Samples and Examples configuration page, enter the password that you defined for the root account earlier. This will install a sample database for you to use in this lab.
      8. Note: After finishing installation, if you need to adjust any of the configuration, re-run the MySQL Installer (find it on your start menu) and pick the option to Reconfigure.
  3. When the installation finishes, Workbench may start. I suggest closing it and then reopening it (it’s on the Start menu as MySQL Workbench) so you know how.
  4. Watch parts 1 and 2 of my MySQL training here. I suggest you follow along and do what I do to help you develop the skills you’ll need for this lab and for the upcoming project. Watch the videos in order (part 1, then part 2).

Tip: If something doesn’t go well during the installation, the best way to uninstall MySQL so you can start over is to run the MySQL installer and to use its Remove button to remove all of the MySQL components.

Using MySQL Workbench

Use MySQL Workbench to complete the following steps, following the techniques given in the MySQL 2 video:

  1. Spend a few moments using the MySQL Workbench to explore the data in the tables in the sample sakila database that was installed when you installed MySQL (if this database was not installed, you can get it here: https://dev.mysql.com/doc/sakila/en/sakila-installation.html). Use the Data Export feature of Workbench to export this database to a single SQL file named sakila.sql.
  2. Create a database named sakila_copy. Open the Server menu and choose Data Import. Choose the Import from Self-Contained File option and select the file that was exported in the previous step. Change the Default Target Schema to sakila_copy, and click Start Import to import the data from sakila.sql into this database. Now, you can play around with the data in sakila_copy without risk. Take a few moments to experiment with modifying data in one or two tables in this database.
  3. Right-click the country table and choose Copy to Clipboard > Create Statement. Paste this into the query editor, but don’t execute it yet.
  4. Create a database named lab1 and make it the default. Then, execute the create statement in this database to create an empty country table. Verify that the table was created in the database. Then delete it.
  5. Use the Data Export feature to export the data from just the country table in the sakila database. Then, open the the resulting sql script in MySQL Workbench and review the contents. Execute it in your lab1 database to create and populate the country table.
  6. Add a new record to the country table named ‘Oz’. Use the query editor to verify that the record was properly added. Then, delete the record from the country table with id 41.
  7. Create a table in the lab1 database named colors with two columns: id (integer) and name (varchar(20)). Insert two records into the table.
  8. Use the Data Export feature to export the lab1 database to a single file named lab1.sql. Open the file in MySQL Workbench and review the contents.
  9. Delete the lab1 database. Then, create it again and make it the default. Execute the lab1.sql script to import the lab1 database exported in the previous step.

Using MySQL at the Command Line

Use the Windows command line to complete the following, using the techniques demonstrated in the MySQL 1 video:

  1. Update your computer’s PATH environment variable so that you can use the mysql command from the command line. Close and reopen the command line.
  2. Using the mysql command, connect to your lab1 database.
  3. Insert a new record into the Country table named ‘Neverland’ using an INSERT statement (review the lecture notes on SQL DML if needed). Use the UPDATE statement to change the name of the country to ‘My Country’. Finally, display the new record using a SELECT statement.

Make a screenshot of your command line session showing all of these commands for your report.

Submission

Submit a PDF lab report to Canvas named report.pdf that consists of the following:

  1. Cover sheet including hours spent. Count all hours spent outside class watching the MySQL lecture videos and reviewing other MySQL resources as well as working on the lab.
  2. One screen shot as specified in the instructions above.
  3. The contents of lab1.sql (copy and paste from MySQL Workbench).