MCS-170 Project 8: Databases Using MySQL - Part III

Due: In Class

Project Task

In parts I and II of this lab, you created a database of movies and constructed queries on this database. 

In this lab, you will extend your database by creating two new tables.  During the lab you will be asked a series of questions. Record your answers and turn them in to the instructor at the end of the lab period.


In Lab

To start using MySQL open a shell window. (Click on the Red Hat icon in the lower left corner, choose "Run Command" from the popup menu, type "Konsole," and hit "Run.")

A window will popup on the screen labeled "Shell-Konsole."  Type the following command, replacing "username" with your individual username (your email name).
mysql -h mcs-mysql.gac.edu -u username -p
You'll be asked for your password, which will be "gustavus".

The first thing you need to do is connect to the movie database created in part II of this lab. To do this type in the following, replacing 'username' with your own username that you used to login.
use username_movies;
Suppose that you are the owner of a video rental service called "Videos-R-Us." The inventory of your rental store consists of the movies in the database you created in Part II of this lab.  To keep track of rentals you will need two new tables -- one to store customer information and one to store rental information (who rented which movie). 

EXERCISE 1: Use the appropriate SQL command to create a table called "customers" which has the following fields: firstName, lastName, street, city, state, phone, customer_ID. You will need to decide what data type to use for each field. (In a separate text document copy the commands you used for turning in at the end of the hour.)

EXERCISE 2: Use the appropriate SQL command to create a table called "rentals" which has the following fields: date, customer, movie, rental_ID. You will need to decide what data type to use for each field. In particular, two of these fields should be foreign keys -- references to fields from other tables. Which ones should be foreign fields? (In a separate text document copy the commands you used to create the "rentals" table for turning in at the end of the hour.)

EXERCISE 3:  Now, put in some data. Insert two fictitional customers into the "customers" table and four fictional rentals into the "rentals" table. (Make sure that one of your renters has rented the movie "The Big Easy") Record these for turning in at the end of the hour.

We can now do querying of our newly extended database.

EXERCISE 4:  Construct a query that will return all of the customers who have rented the movie "The Big Easy".
EXERCISE 5:  Come up with your own query that uses a join operation different from Exercise 4. Test out your query on the database and record your results.

Project Report:

Hand in your answers to the exercises above.


Back to MCS 170 home page