MCS-170 The Nature of Computer Science

Fall 2006

---

1. Review - Database Design

• Avoid Redundancy -- Never store duplicate information
• Create separate Tables for distinct data, each having a unique ID (primary key)

• Schema Description of  entities of a table
• Fields – represent column information (attributes)
• Field Specification
• Field_name data_format optional_comment
• First_name text 20 customer’s first name

• Query -- Database operation that selects certain data from a list of tables and creates a new ouput table
  
• SELECT {field_names} FROM {table_names} WHERE {row_constraints}
• Selects columns from the tables subject to the constraints on the rows

2. Movie Database with MySQL

• Database contains Movie Data: title, director, actors, year_made.

• Redundancy? If we store a movie with directors and actors, then we would repeat much info
• Example, 'Jaws' and 'ET' would both have director 'Speilberg, Stephen'
• Likewise, actors can appear in multiple movies
  

• Structure? Database will be split into three tables:
  
• persons table holding the first and last names of directors and actors
• movies table holding the title, director, and year_made of a movie
  
• Note - director will reference data from the persons table
  
• acts_in table holding info on which actor acts in which movie
• Note - actor and movie will reference data from the persons and movies tables
  

• Project 9, Part I, we used MySQL database system to build a database with this structure

    create database sskulrat_movies;

    use sskulrat_movies;

    create table persons (lastname varchar(40) not null,
                          firstnames varchar(40),
                          id int primary key);

    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));

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

• Data Input: We add data to the table using the insert command

insert into persons values('Fellini', 'Federico', 1);
insert into persons values('Noel', 'Magali', 2);

insert into movies values('Amacord', 1, 1974, 1);

insert into acts_in values(2, 1);

3. Querying our Movie Database

• Select Operation: Showing all values of a table:

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 |
+-----------------+----------+-----------+----+

• Finding the year that 'The Big Easy' was made

mysql> select year_made from movies where title = 'The Big Easy';

+-----------+
| year_made |
+-----------+
|      1987 |
+-----------+

• Join Operation: Selecting from two tables will form a complete product of the tables (all possible combinations). This is called the join of the tables:
  

mysql> select * from movies, persons;
+-----------------+----------+-----------+----+-----------+------------+----+
| title           | director | year_made | id | lastname  | firstnames | id |
+-----------------+----------+-----------+----+-----------+------------+----+
| Amacord         |        1 |      1974 |  1 | Fellini   | Federico   |  1 |
| The Big Easy    |        6 |      1987 |  2 | Fellini   | Federico   |  1 |
| Boyz M the Hood |       13 |      1991 |  3 | Fellini   | Federico   |  1 |
| Amacord         |        1 |      1974 |  1 | Noel      | Magali     |  2 |
| The Big Easy    |        6 |      1987 |  2 | Noel      | Magali     |  2 |
| Boyz M the Hood |       13 |      1991 |  3 | Noel      | Magali     |  2 |
| Amacord         |        1 |      1974 |  1 | Zanin     | Bruno      |  3 |
| The Big Easy    |        6 |      1987 |  2 | Zanin     | Bruno      |  3 |
| Boyz M the Hood |       13 |      1991 |  3 | Zanin     | Bruno      |  3 |
| Amacord         |        1 |      1974 |  1 | Maggio    | Pupella    |  4 |
| The Big Easy    |        6 |      1987 |  2 | Maggio    | Pupella    |  4 |
| Boyz M the Hood |       13 |      1991 |  3 | Maggio    | Pupella    |  4 |
| Amacord         |        1 |      1974 |  1 | Drancia   | Armando    |  5 |
| The Big Easy    |        6 |      1987 |  2 | Drancia   | Armando    |  5 |
| Boyz M the Hood |       13 |      1991 |  3 | Drancia   | Armando    |  5 |
| Amacord         |        1 |      1974 |  1 | Mcbride   | Jim        |  6 |
| The Big Easy    |        6 |      1987 |  2 | Mcbride   | Jim        |  6 |
| Boyz M the Hood |       13 |      1991 |  3 | Mcbride   | Jim        |  6 |
| Amacord         |        1 |      1974 |  1 | Quaid     | Dennis     |  7 |
| The Big Easy    |        6 |      1987 |  2 | Quaid     | Dennis     |  7 |
| Boyz M the Hood |       13 |      1991 |  3 | Quaid     | Dennis     |  7 |
| Amacord         |        1 |      1974 |  1 | Barkin    | Ellen      |  8 |
| The Big Easy    |        6 |      1987 |  2 | Barkin    | Ellen      |  8 |
| Boyz M the Hood |       13 |      1991 |  3 | Barkin    | Ellen      |  8 |
| Amacord         |        1 |      1974 |  1 | Beatty    | Ned        |  9 |
| The Big Easy    |        6 |      1987 |  2 | Beatty    | Ned        |  9 |
| Boyz M the Hood |       13 |      1991 |  3 | Beatty    | Ned        |  9 |
| Amacord         |        1 |      1974 |  1 | Persky    | Lisa Jane  | 10 |
| The Big Easy    |        6 |      1987 |  2 | Persky    | Lisa Jane  | 10 |
| Boyz M the Hood |       13 |      1991 |  3 | Persky    | Lisa Jane  | 10 |
| Amacord         |        1 |      1974 |  1 | Goodman   | John       | 11 |
| The Big Easy    |        6 |      1987 |  2 | Goodman   | John       | 11 |
| Boyz M the Hood |       13 |      1991 |  3 | Goodman   | John       | 11 |
| Amacord         |        1 |      1974 |  1 | Ludlam    | Charles    | 12 |
| The Big Easy    |        6 |      1987 |  2 | Ludlam    | Charles    | 12 |
| Boyz M the Hood |       13 |      1991 |  3 | Ludlam    | Charles    | 12 |
| Amacord         |        1 |      1974 |  1 | Singleton | John       | 13 |
| The Big Easy    |        6 |      1987 |  2 | Singleton | John       | 13 |
| Boyz M the Hood |       13 |      1991 |  3 | Singleton | John       | 13 |
| Amacord         |        1 |      1974 |  1 | Gooding   | Cuba       | 14 |
| The Big Easy    |        6 |      1987 |  2 | Gooding   | Cuba       | 14 |
| Boyz M the Hood |       13 |      1991 |  3 | Gooding   | Cuba       | 14 |
| Amacord         |        1 |      1974 |  1 | Cube      | Ice        | 15 |
| The Big Easy    |        6 |      1987 |  2 | Cube      | Ice        | 15 |
| Boyz M the Hood |       13 |      1991 |  3 | Cube      | Ice        | 15 |
| Amacord         |        1 |      1974 |  1 | Fishburne | Larry      | 16 |
| The Big Easy    |        6 |      1987 |  2 | Fishburne | Larry      | 16 |
| Boyz M the Hood |       13 |      1991 |  3 | Fishburne | Larry      | 16 |
| Amacord         |        1 |      1974 |  1 | Ferrell   | Tyra       | 17 |
| The Big Easy    |        6 |      1987 |  2 | Ferrell   | Tyra       | 17 |
| Boyz M the Hood |       13 |      1991 |  3 | Ferrell   | Tyra       | 17 |
| Amacord         |        1 |      1974 |  1 | Chestnut  | Morris     | 18 |
| The Big Easy    |        6 |      1987 |  2 | Chestnut  | Morris     | 18 |
| Boyz M the Hood |       13 |      1991 |  3 | Chestnut  | Morris     | 18 |
+-----------------+----------+-----------+----+-----------+------------+----+
54 rows in set (0.00 sec)

• Focusing a Query: Let's suppose we want to see just the title, director (first and last names), and year_made from this list, without all the duplicates. We need to limit ourselves to just those combinations where the person is the movie's director. We should impose a constraint that the director number matches the person's id number. The statement below *almost* does this, but has an error:

mysql>  select title, fnames, lname, year_made

       from movies, persons where director = id;

ERROR 1052 (23000): Column 'id' in where clause is ambiguous

• The problem is that 'id' is not connected to anything. The system does not know which table 'id' refers to. The solution is to name the tables we are joining together and then refer to the 'id' fields more specifically, saying which table they are from:

mysql> select title, fnames, lname, year_made

       from movies m, persons p
       where m.director = p.id;
+-----------------+----------+-----------+-----------+
| title           | fnames   | lname     | year_made |
+-----------------+----------+-----------+-----------+
| Amacord         | Federico | Fellini   |      1974 |
| The Big Easy    | Jim      | Mcbride   |      1987 |
| Boyz N the Hood | John     | Singleton |      1991 |
+-----------------+----------+-----------+-----------+

• Example: What would the following query return?

select fnames, lname 
       from movies m, persons p, acts_in a
       where m.id = a.movie 
         and p.id = a.actor 
         and m.title = 'The Big Easy';

• In-Class: Construct a Query for a larger database (one with many movies) that would return a table of all of the movie titles having the actor 'Claude'  'Raines' in them. 
  
• (test out on computer in class)