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 Beekeeper Studio 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 a Windows virtual machine, or a computer 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.

AI Policy

You may use generative AI to help you deal with any errors you may encounter while working through this lab.

Get Started

  1. Download the MySQL Server here: https://dev.mysql.com/downloads/mysql/

    I recommend choosing the first option (“Windows (x86, 64-bit), MSI Installer”). When you click Download, you’ll see options to login or sign up. There’s also a “No thanks, just start my download” option (recommended).

  2. Run the installer. Pick the Typical setup type. When the installer finishes, click Finish. The MySQL Configurator will start.

  3. In the MySQL Server Configurator, pick the following options:
    1. Data Directory: Accept the default.
    2. Type and Networking: Accept the defaults. You may wish to deselect the option to Open Windows Firewall Ports, since you open firewall ports only if you plan to access the MySQL instance on your computer from another computer (not needed for this class).
    3. Accounts and Roles: Set a root (administrator) password. 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. You do not need to create a MySQL User Account.
    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.
    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 Sample Databases page, select both the Sakila and World databases.
    8. Apply Configuration: Click Execute.
    9. Note: After finishing installation, if you need to adjust any of the configuration, re-run the MySQL Configurator (find it on your start menu).
  4. Watch the MySQL training videos 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: Using MySQL at the Command Line

Use the Windows terminal to complete the following, using the techniques demonstrated in the MySQL 1 and 2 videos. As you work, copy the commands that you type and the resulting output in the terminal to a Word document underneath a heading “Part 1 Transcript.” Don’t take screenshots (they’re often not readable in reports); I want a transcript.

  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. Start the MySQL Command Line. Use the SHOW DATABASES command; you should see the sakila and world sample databases.
  3. Use the USE DATABASE command to switch to the sakila database. Then, use the SHOW TABLES command to look at the names of tables. Run a couple of SELECT statements to look at the data in a couple of the tables.
  4. Use the USE DATABASE command to switch to the world database. Look at the data in the tables.
  5. Use the CREATE DATABASE command to create a database named lab1. Use the USE DATABASE command to switch to the lab1 database. Use the SHOW TABLES command to verify there are no tables in it. Create a table named Animals with two columns: an ID INT, and a Name VARCHAR(30). Use the INSERT statement to insert three records into it. Then use the UPDATE statement to change the name of one of the animals. Then use the SELECT statement to show the records in Animals.
  6. Exit the MySQL Command Line. From Windows Terminal, use the mysqldump command to export the lab1 database to a file named lab1.sql. Open the lab1.sql and verify that it contains the Animals table and data.
  7. Start the MySQL Command Line. Use the DROP DATABASE command to delete the lab1 database. Then use the CREATE DATABASE command to create an empty lab1 database. The SHOW TABLES command should show that it is empty at this point. Exit the MySQL Command Line.
  8. From the Windows Terminal, use the mysql command to execute the lab1.sql commands to recreate your lab1 data.
  9. Start the MySQL Command Line. Use the USE DATABASE command to open the lab1 database, and execute a SELECT statement to show the contents of the Animals table.

Part 2: Using Beekeeper Studio

Download Beekeeper Studio here: https://www.beekeeperstudio.io/get (note the option to skip entering an email address)

Install Beekeeper Studio and use it to complete the following steps, following the techniques given in the MySQL 3 video:

  1. Connect to your MySQL database. Spend a few moments exploring the data in the tables in the sample sakila database that was installed when you installed MySQL.
  2. Open a query editor and execute a CREATE DATABASE command to create a new database named lab1_copy. Select lab1_copy in the database dropdown (you may need to refresh to see it).
  3. Locate your lab1.sql file in Windows Explorer and drag it into Beekeeper Studio to open it. Execute the script, and you should see your Animals table appear. Open the Animals table. Take a screen shot.
  4. Add a new record to the Animals table and save. Modify the name of an animal and save. Take a screen shot.
  5. Open a new query tab and enter a CREATE TABLE statement to create a table with at least 3 columns containing whatever information you wish. Make sure to define a primary key. Open the table, add a couple of records, and save. Take a screen shot.

Submission

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

  1. Cover sheet with your name, course number, date, assignment title, and total 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. Transcript for Part 1, and screen shots for Part 2.
  3. The contents of lab1.sql (copy and paste from Beekeeper Studio editor).