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 Fedora 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.
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: lastname, city, 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". You could return just the customers' ids
or their last names. While returning last names harder, it's also
better. You are free to do either.
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