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

Due: In Class

Project Task

In this lab, you will create a movie database using the MySQL server in the MCS department.  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, you need to open a shell window. In the lower left corner of the screen, you'll find an icon shaped like a red hat. Click on this and then choose "Run Command" from the popup menu that will appear. In the dialog box that pops up 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". If the password goes through successfully, you'll see something like the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22422 to server version: 4.1.3-beta-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

The first thing you need to do is create a new database, and connect to it as the active database. To do this type in the following (make sure to terminate each line with a semi-colon).  Replace 'username' with your own username that you used to login.
create database username_movies;
use username_movies;
Now we will define the tables for our movie database. We will want the database to include fields such as movie-title, the year the movie was made, the actors in the movie, and the director.  To avoid redundancy, we will create a table 'persons' which will store all actors and directors.  The command to do this is as follows (copy and paste the command into the shell window):
create table persons (lastname varchar(40) not null,
firstnames varchar(40),
id int primary key);
Note that we are creating an id for each person so that we can uniquely identify that person. Also, the phrase "not null" after the lastname field specification means that this field cannot be left empty -- it must be defined for each person.

To see what the structure of this table is, we can ask the system to describe the table:

mysql> describe persons;

+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(11)     |      | PRI | 0       |       |
| lastname   | varchar(40) |      |     |         |       |
| firstnames | varchar(40) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Now, we create the table for the movies. Again, we create a unique id for each movie. We store the title, director and year the movie was made. Notice that the director is an "int", i.e. an integer. The director is referenced by the id of the entry from the persons table. Since this is a reference outside the table, we need the last line of the specification, the line reading "foreign key (director) references persons(id)." This tells the system that the director value is a 'foreign' key outside the movies table and is a key that references the value of the 'id' field from the persons table.

create table movies (title varchar(40) not null,
                     director int not null,
                     year_made int not null,
                     id int primary key,
                     foreign key (director) references persons(id));

To see the structure of this table, we again ask the system to describe the table:

mysql> describe movies;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     |      | PRI | 0       |       |
| title     | varchar(40) |      |     |         |       |
| director  | int(11)     |      | MUL | 0       |       |
| year_made | int(11)     |      |     | 0       |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

You may have noticed that we did not add a field for actors in our movie table.  This is because a single movie can have multiple actors, and if we added one field for actors, that field would be 'atomic' -- all the actors would be a single long string, and we could not distinguish one from another in the database.  So, we need a third table, one for the actors, which we will call 'acts_in'. This table will have the following specification:

table acts_in
actor        int - foreign key
movie      int - foreign key
(movie, actor) - primary key

Note the compisite prinary key for this table. We use both the movie and the actor to uniquely identify an entry of this table . To create the 'acts_in' table, we use the following command (copy and paste the command):

create table acts_in (actor int,
                      movie int,
                      primary key (actor, movie),
                      foreign key (actor) references persons(id),
                      foreign key (movie) references movies(id));

Now, our database system is complete in structure, but has no actual data! To put data into the system, we use the insert command. First, we need to fill out some of the entries of the persons table, since the other two tables depend on this table. Copy and Paste into the shell the following:
insert into persons values('Fellini', 'Federico', 1);
insert into persons values('Noel', 'Magali', 2);
insert into persons values('Zanin', 'Bruno', 3);
insert into persons values('Maggio', 'Pupella', 4);
insert into persons values('Drancia', 'Armando', 5);
insert into persons values('Mcbride', 'Jim', 6);
insert into persons values('Quaid', 'Dennis', 7);
insert into persons values('Barkin', 'Ellen', 8);
insert into persons values('Beatty', 'Ned', 9);
insert into persons values('Persky', 'Lisa Jane', 10);
insert into persons values('Goodman', 'John', 11);
insert into persons values('Ludlam', 'Charles', 12);

These are the actors and directors for the movies 'Amacord' and 'The Big Easy'. The director of 'Amacord' is Frederico Fellini, and the director of 'The Big Easy' is Jim McBride, so we use the following commands to add these two movies to the movies table:
insert into movies values('Amacord', 1, 1974, 1);
insert into movies values('The Big Easy', 6, 1987, 2);
The actors can be put into the acts_in table as follows (copy and paste):
insert into acts_in values(2, 1);
insert into acts_in values(3, 1);
insert into acts_in values(4, 1);
insert into acts_in values(5, 1);
insert into acts_in values(7, 2);
insert into acts_in values(8, 2);
insert into acts_in values(9, 2);
insert into acts_in values(10, 2);
insert into acts_in values(11, 2);
insert into acts_in values(12, 2);

EXERCISE 1:   What does the command
insert into acts_in values(8, 2);
actually do? That is, explain what specific actors and/or movies are being referenced in this command.

EXERCISE 2: The movie 'Boyz N the Hood' was made in 1991. It was directed by John Singleton and starred Cuba Gooding, Ice Cube, Larry Fishburne, Tyra Ferrell, and Morris Chestnut.  Write down the insert commands needed to completely add this movie to your database. Then, type these commands into the mySQL system.

Now that we have a database of three movies, we can do some queries of our database. For example, to get a list of all of our movies, we use the command:
mysql> select * from movies;

+-----------------+----------+-----------+----+
| title | director | year_made | id |
+-----------------+----------+-----------+----+
| Amacord | 1 | 1974 | 1 |
| The Big Easy | 6 | 1987 | 2 |
| Boyz N the Hood | 13 | 1991 | 3 |
+-----------------+----------+-----------+----+

If we want the titles of the movies made in 1991 we use the command:

mysql> select title from movies where year_made = 1991;

+-----------------+
| title |
+-----------------+
| Boyz N the Hood |
+-----------------+
1 row in set (0.00 sec)

Selecting data from database tables is done via the select command. The general form for using this command is 
select {field_names} from {table_names} where {row_constraints}.

Field_names can be any field names that are used to define the table. In our movies table we could ask for any of the fields labeled title, director, or year_made.  Table_names are names of our database tables, in our case movies, persons, and acts_in.  Row_constraints are constraints defined in terms of the fields of the table that limit the number of rows displayed. For example, the constraint
year_made = 1991
constrains our search to only those rows (movies) where the year_made is 1991.
EXERCISE 3:  Write down (and test) the command for displaying the year that the movie 'The Big Easy' was made.  (Hint: select the 'year-made' field using a row constraint for the 'title' field)

EXERCISE 4:  Find a movie that you like on-line at the Internet Movie Database http://us.imdb.com/.  Then, add this movie to your database. (Limit the number of actors to 5 or less)
List on your report the movie you added, the actors in the movie and the director of the movie.
EXERCISE 5:  What did you find most confusing in working with the MySQL database system?

Project Report:

Hand in your answers to the exercises above.


Back to MCS 170 home page