CSCE 520, Fall 2019
Homework 1

Reading

Read Chapter 1 and sections 2.1, 2.2, and 2.3 of Chapter 2. This should not be taxing, as it is mostly nontechnical.

Doing

The purpose of the rest of this assignment is to get you to install and start using MySQL on your computer by working through an online tutorial. You will use MySQL throughout the course. (When I have taught this course previously, we used a centralized Oracle SQL server on the lab machines, which required people to log into the lab machine first, then the oracle server remotely. MySQL has the advantage of running on your own machine, so you can take it with you. You'll need about 0.5 Gb of space to install it, though.)

Before reading further, please note: The rest of this handout has been tested on a Mac OS system. I have not used any of the MS Windows operating systems for over a decade, and I currently have no device running any Microsoft OS. I imagine you can get MySQL running on such a system, but I personally can be of little or no help for you to do it. The TA is much more likely to be able to help you if you need it.

MySQL is a client-server application, even if the client and server are running on the same machine. When you run a MySQL session, you are actually interacting with a client program mysql on your local machine, which then sends commands and retrieves data from the MySQL server (started automatically at system start-up or by invoking mysqld from the shell prompt). Your MySQL sessions will involve simple scrolling text only. You may use a GUI if you want, but it is not required, and I won't be able to answer GUI-related questions.

Installing and Setting Up MySQL

I'm going to assume that you have never used MySQL before. One way to install MySQL, the one I recommend, is to go to https://www.mysql.com, click the Downloads tab, and follow the links to download and install the MySQL Community Edition (GPL), MySQL Community Server (the latest version is 8.0). Select your platform (I use MacOS for my Macbook). I chose the DMG archive and all the default configuration options, including having the MySQL server start up at system start-up. Your milage may vary. For security, you should choose a good root password and REMEMBER IT!!! You won't need it that often, but sometimes you'll have to do some administrative stuff and you'll need root privileges for that. Also, if you ever forget your regular user password, you can reset it while logged in as root (see below for how to do this). If you ever forget your root password, however, I don't know of any way to recover short of reinstalling MySQL from scratch. (That's not a total disaster for this class, because all entered data will come from stored external files.) Having a root password is actually optional, but strongly recommended for security.

An alternative way to install MySQL is to follow the (somewhat dated) instructions here, but for a Mac this requires also installing Homebrew, so I didn't do it.

MySQL Exercise

The goal of this exercise is to work through the tutorial on the MySQL website, a link to which is given later in this handout. You will only need to work up through Section 3.3.4.5, Date Calculations (so you may stop before Section 3.3.4.6, Working with NULL Values). (You can certainly go further if you wish, and that'll put you ahead on the learning curve.) This tutorial already assumes a working MySQL system and running server, however, and you are not there yet. Here is what you have to do first to get MySQL so that you can run the tutorial. This should work pretty much the same for any unix/linux/MacOS-like system, and I will assume you are using such a system and that the server is running on the same machine you are using as the client (e.g., your laptop). As I mentioned above, I don't know about MS Windows other than what is absolutely necessary for my job. I'm sure this will all work on Windows with the right modifications.

Install and configure MySQL as directed above if you haven't already.
You will want to add the directory with the MySQL executables to your PATH variable. This is optional, but will make running mysql more convenient from the command line. On my Mac, where I use a terminal program running the bash shell, I put the following line at the end of my .bashrc file in my home directory:
export PATH="/usr/local/mysql/bin:$PATH"
which prepends the default directory for MySQL executables to my PATH variable. You'll have to quit and restart the terminal program once for this to take effect.
At the shell prompt ($), fire up a MySQL client (aka MySQL monitor) session as the root user:
mysql -u root -p
Supply the root password when prompted. You should then be logged in as root and see the prompt mysql>.
You don't want to habitually login to MySQL as root because you have arbitrary privileges, including the ability to really screw things up accidentally. So the first thing to do as root is to create a regular user account with limited privileges that you will use most of the time. Enter something like this at the prompt:
mysql> CREATE USER 'steve'@'localhost' IDENTIFIED BY 'some_good_password';
where 'steve' is your regular account name on your computer, and some_good_password is any good password you can remember, which need not be the same password you use to login to your machine (don't use your mysql root password, though). The quotes are all required, and 'localhost' is typed literally (assuming you are running the client and server on the same machine).

Don't forget the final semicolon. If you accidentally hit the return key before the semicolon, no problem; just put the semicolon on the next line and hit the return key again. For more information about this command, click here.
Enter the following (substituting your own login name)
mysql> GRANT ALL ON menagerie.* TO 'steve'@'localhost';
This will give your regular MySQL user account complete permissions to access the menagerie database, once you create it in the tutorial.
Quit your root login session by entering
mysql> QUIT;
You may quit any SQL session at any time by typing QUIT at the prompt. EXIT also works.

You are now ready to start the tutorial, using your regular (i.e., non-root) MySQL user account. Go to https://dev.mysql.com/doc/refman/8.0/en/tutorial.html. There are a few discrepancies between the tutorial and what you will experience on your system. Note the following anomalies as you go through the tutorial:

Typing
shell> mysql --help
will scroll several screenfulls all at once. To view the output one screen at a time (on linux-like systems), type this instead:
shell> mysql --help | more
and hit the space bar to see each successive screenfull.
If you set things up as above, with your MySQL user name the same as your machine account name (mine is steve) and the server running on your machine, you can omit some options (namely -h host and -u user) when invoking mysql at the shell prompt. I log into my MySQL client account by typing
shell> mysql -p
The -p option with nothing following it makes mysql prompt me for my password. This is the recommended way to login to the MySQL client.
When you type
mysql> SHOW DATABASES;
you won't see any databases listed (except possibly information_schema) because you have not yet created any.
Words given in all-caps, both in the tutorial and in this handout, are reserved words in SQL. Reserved words are case-insensitive, so you can enter them all lowercase if you want, e.g.,
show databases;
I, for one, can type much more quickly in lowercase than uppercase.
If you make a mistake with the CREATE TABLE pet command (e.g., a wrong column name or type), you can remove the table by typing
mysql> DROP TABLE pet;
then repeating the CREATE TABLE command.
This is an important one. The LOAD DATA LOCAL command is disabled by default in MySQL 8.0 (for security reasons, no doubt.) For now, instead of changing the default, you can enter the data by first downloading pet.sql. (Peruse this file; it has SQL commands for inserting data into the pet table.) Then type
mysql> SOURCE pet.sql
(no quotes and no semicolon needed). The SOURCE command reads and executes the SQL commands in the file one after another as if they were typed at the prompt. After this homework, the SOURCE command will be the principal way you interact with MySQL: put your SQL commands in a separate text file, then run it using SOURCE within mysql.

To see the contents of the pet table at any time, type
mysql> SELECT * FROM pet;

Suppose you make a mistake. For example, if you run the SOURCE pet.sql command more than once, you'll put duplicate entries into your table. You can empty out the table using
mysql> DELETE FROM pet;
then repopulate it with the SOURCE command again. (Be careful with the DELETE command!)

If you forget your mysql user password

If you ever forget your regular user password for mysql, you can reset it by logging in as root and giving the following command:
ALTER USER 'steve'@'localhost' IDENTIFIED BY 'some_new_password';
Of course, use your own user name instead of "steve".

What to Submit

Outside of mysql, edit a new text file called hw1.sql with the following four SQL statements in order, each terminated by a semicolon:

a SELECT statement that returns the names and owners of all female pets born after 1990, in alphabetical order by name;
an UPDATE statement that changes the name of Gwen's bird Whistler to your own login name (e.g., I would change it to 'steve');
an UPDATE statement that sets the death date of Harold's dog Buffy to the current date, whatever it is (this statement should work regardless of when it is executed);
the statement SELECT * FROM pet

For readability's sake, separate your commands with an empty line between each one.

The following applies to this and mutatis mutandis all future homeworks. To submit: From the shell prompt, login to mysql with the following shell command:
mysql -p menagerie
and enter your user password when prompted (this will work on unix/linux/Mac systems; for Windows, YMMV). Including the database name on the command line automatically loads it, so you do not need to issue a "USE menagerie" command inside mysql. Inside mysql, type
mysql> tee hw1.log mysql> source hw1.sql mysql> notee mysql> quit
The first command tells mysql to append everything subsequently appearing in the session to the file hw1.log, until the notee command is given, which stops logging. The source command reads the hw1.sql file and runs its commands in order as if you had typed them at the prompt.

You should make a .zip file hw1.zip containing both hw1.sql and hw1.log files. Upload your .zip file electronically using CSE Dropbox. You should submit your assignment to Section 001 regardless of which of these sections you are in.

This page was last modified Wednesday August 28, 2019 at 15:04:59 EDT.

CSCE 520, Fall 2019 Homework 1