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

Due: In Class

Project Task

In part I of this lab, you created a small movie database using the MySQL server in the MCS department. 

In this lab, you will use a more extensive movie database to investigate the process of constructing queries on the database.  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 I of this lab. To do this type in the following, replacing 'username' with your own username that you used to login.
use username_movies;
Now we will re-define the tables for our movie database.  First, we delete everything from our three tables:
delete from acts_in;
delete from movies;
delete from persons;
Next we load in data from external files. For example, the file persons.data contains a list of names in the format of the persons table. Each field is separated by a space.  There are three such external files for each of our three tables. We load the data from these files into our tables as follows:
load data local infile '/Net/solen/home1/h/v/hvidsten/www-docs/courses/MC170/movies/persons.data' into table persons;
load data local infile '/Net/solen/home1/h/v/hvidsten/www-docs/courses/MC170/movies/movies.data' into table movies;
load data local infile '/Net/solen/home1/h/v/hvidsten/www-docs/courses/MC170/movies/acts_in.data' into table acts_in;
We can create queries for this new data just as we did in part I of this project.  For example, the query
mysql> select title, year_made from movies 
where year_made >= 1985 and year_made <= 1990;
returns the table for all movies in the database made between 1985 and 1990:
+---------------------+-----------+
| title | year_made |
+---------------------+-----------+
| The Big Easy | 1987 |
| Blood Simple | 1985 |
| A Fish Called Wanda | 1988 |
| House of Games | 1987 |
+---------------------+-----------+
This simple query needs to access just the movies table, as the title and year_made fields are contained in that table, and the 'where' row constraints refer only to fields (year_made) from the same table.

EXERCISE 1: Construct a query to return the list of movies made in either the 1950's or the 1970's. (Group logical clauses with parentheses) Test out your query on the database and record the query.

In contrast, let's suppose we wanted to construct a query for the director of 'Gone with the Wind'.  Since the director information is in the persons table, and the movie title is in the movies table, we first need to join the movies and persons table, and then extract the row we want.  We will display the first and last name fields of the director, so these will be the fields selected in the select statement.  Also, we need to restrict the rows of the join to those rows where the director id in the movies table matches the id of the persons table.  Finally, we get the exact row we want by restricting to movies whose 'title' field matches 'Gone with the Wind';
select firstnames, lastname from movies m, persons p
where m.director = p.id
and m.title = 'Gone with the Wind';

+------------+-------------+
| firstnames | lastname |
+------------+-------------+
| Victor | Fleming |
+------------+-------------+

EXERCISE 2:   What does the following query do?
select title from movies m, persons p
where m.director = p.id
and p.firstnames = 'Orson' and p.lastname = 'Welles';
That is, explain what table data is created by this command. Run the command and record your result.

We can also do more sophisticated row testing, in particular we can do string matching, using the like comparison function. The command

select * from movies where title like '%the%';

performs a matching test, returning those movies where the title contains a string matching the string 'the'.  The string we want to match is enclosed by the two % signs. The result of executing this query will be the following table.

+--------------------------+----------+-----------+----+
| title                    | director | year_made | id |
+--------------------------+----------+-----------+----+
| The Big Easy             |      116 |      1987 |  2 |
| Boyz N the Hood          |      130 |      1991 |  3 |
| The Godfather            |       73 |      1972 |  5 |
| Gone with the Wind       |      227 |      1939 |  9 |
| The Manchurian Candidate |      122 |      1962 | 11 |
| Othello                  |      181 |      1952 | 13 |
| After the Rehearsal      |       94 |      1984 | 16 |
| The Cotton Club          |       73 |      1984 | 20 |
| The Crying Game          |      174 |      1992 | 21 |
| The Day of the Jackal    |       77 |      1973 | 22 |
| The Dresser              |      191 |      1984 | 24 |
| The Exorcist             |      231 |      1973 | 26 |
+--------------------------+----------+-----------+----+


EXERCISE 3:  Construct a query that will return all of the movies that have an actor with first name containing 'james'. Test out your query on the database and record your query and results.
EXERCISE 4:  Come up with your own query that either uses a join operation or a string matching operation different from the examples above. Test out your query on the database and record your results.
EXERCISE 5:  What did you find most confusing in working with queries  (the select command)?

Project Report:

Hand in your answers to the exercises above.


Back to MCS 170 home page