INFO I101 Lab 6
Databases

Lab Overview

We will create and query a movie database.

Objectives — learn to:

1. Define tables and enter data in a relational database.
2. Design and execute queries.
3. Create views, forms, and reports.

Part A: Create the Database

Create a database of films directed by Alfred Hitchcock. The four database tables and their attributes are as follows:

The film table; attributes: filmid (primary key), released (number, year released), studioid, title, runtime (number, running time in minutes).

The studio table: studioid (primary key), studioname

The actor table: actorid (primary key), actorname

The actedIn table: filmid, actorid (the primary key consists of both attributes together).

Remarks:

1. All attributes have the data type Text, unless number is indicated.
2. The attributes filmid, studioid, and actorid are made-up primary keys.
3. "Actor" means a person who acts, whether male or female.
4. The actedIn table signifies which actors acted in which films. For example,

   filmid	actorid
   f1	a1

   means that the actor with actorid = a1 (James Stewart) acted in the film with filmid = f1 (Rope).
5. Note that the actedIn table has two primary key attributes. To set this up, in table design view, select both of the attributes simultaneously and click on the Primary Key tool.

After creating the tables, populate them with the data shown below. You may create forms for data entry or simply use the datasheet view of each table.

Data for the tables: see Hitchcock movie data (one-page PDF).

Part B: Views

It will be a good idea to define the following views to make the queries and reports easier:

Microsoft Office Access users: Access has no notion of a "view" as distinct from a "query." Just save the queries below, with the names "filmStudioView" and "filmCastView".

The "filmStudioView" view, based on the query:

select filmid, released, title, runtime,
  studio.studioid as studioid, studioname
from film, studio
where film.studioid = studio.studioid;

or this equivalent query

select filmid, released, title, runtime,
  studio.studioid as studioid, studioname
from film inner join studio
on film.studioid = studio.studioid;

The "filmCastView" view, based on the query:

select film.filmid as filmid, released, title, runtime,
  actor.actorid as actorid, actorname
from film, actedIn, actor
where film.filmid = actedIn.filmid
and actedIn.actorid = actor.actorid;

or this equivalent query

select film.filmid as filmid, released, title, runtime,
  actor.actorid as actorid, actorname
from film inner join actedIn
on film.filmid = actedIn.filmid
inner join actor
on actedIn.actorid = actor.actorid;

Part C: Reports

After populating the tables, create the reports specified below. Use the report wizard to design the report; you may tweak the design if you wish. Print the output of each report.

1. A "Film and Studio Report" listing each film by title and released, and the name of the studio that made it. Sort the films by released in ascending order.
   Hint: use the filmStudioView as the basis for the report, or the following query:

   select title, released, studioname
   from filmStudioView;

2. A "Cast Report" listing each actor's name together with each film the actor acted in and the year of the film's release. Sort by actorname as the primary sort field, and released as the secondary sort field, both in ascending order.
   Hint: use the filmCastView or this query:

   select actorname, released, title
   from filmCastView;

Part D: Queries

For each of these problems, formulate a query in either SQL or QBE to answer the question. Print the query definition (SQL view, or Design View for QBE). Run the query, and print its output.

Note: You may capture the code and output by copying and pasting them into a text editor or word processor, or by capturing a screenshot. You may wish to do this to save paper, and/or you may be forced to do it because the database system does not allow you to print the queries directly.

See How to take a screenshot (with instructions for several operating systems).

Query problems:

1. Show the film titles and years released of films released between 1950 and 1955, inclusive.
2. List the film titles of films made for Paramount.
3. List the names of the actors in "To Catch a Thief."
4. Find the average running time of movies in the database.

What to Turn in

Turn in these items, stapled together, in the order indicated:

1. Cover sheet with your name (detach last page of these instructions).
2. Output of the two reports made in Part C.
3. Query definition and query output of the four queries from Part D.

Cover Sheet and Grading Criteria

Student name: ___________________________________________

Grading Criteria

Item	Points
Correct table definition and data entry for the film and studio tables, as shown by the output of the Film and Studio Report.	10
Film and Studio report is sorted by released in ascending order.	5
Correct table definition and data entry for the actedIn and actor tables, as shown by the output of the Cast Report.	10
Cast Report is sorted by actorname, then released, in ascending order.	5
Correct queries (SQL code and query output). Four queries, 5 points each.	20
TOTAL	50

Raw total of 50 will be scaled to 20 points (multiply by 20/50).