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.
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 -pYou'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:
create table persons (lastname varchar(40) not null,
firstnames varchar(40),
id int primary key);
To copy in paste in Linux, copy by selecting with the left mouse
button, and paste by clicking the middle mouse button. No need to
select "Copy". While you should feel free to copy and paste long
commands (like the above) during the course of the lab, be sure to
read what you are entering and ask your lab instructor if you don't
understand the commands.
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 |
+------------+-------------+------+-----+---------+-------+
| lastname | varchar(40) | | | NULL | |
| firstnames | varchar(40) | YES | | NULL | |
| id | int(11) | | PRI | 0 | |
+------------+-------------+------+-----+---------+-------+
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 |
+-----------+-------------+------+-----+---------+-------+
| title | varchar(40) | | | NULL | |
| director | int(11) | | MUL | 0 | |
| year_made | int(11) | | | 0 | |
| id | int(11) | | PRI | 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 composite primary 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:
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.
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:
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: One of the commands you just entered (above) was:insert into acts_in values(8, 2);What did this actually do? That is, explain what specific actors and/or movies are being referenced in this command.
EXERCISE 1.5: You may find that you do not finish a lab. Not to worry;mysqlmaintains your database while you are gone. Quit out of mysql now:mysql> quit; ByeNow that you have leftmysql, you could log out if you wanted to. When you come back, you would log back in, restart a shell, and typemysql -h mcs-mysql.gac.edu -u username -p Enter password: mysql> use database wolfe_movies;To confirm that your database
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 |
+-----------------+----------+-----------+----+
3 rows in set (0.00 sec)
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;
The 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. The table_names are
names of our database tables, in our case movies, persons, and acts_in.
The 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