In this lab exercise, we'll work with MS Access's QBE implementation in the sample movies database.

The Movies Database Schema

Start by downloading the movies database (movies.mdb) and familiarizing yourself with its database schema. It's a rather large but simple database whose contents were skimmed from the internet movies database by a faculty member (in Scotland as I recall) in the late 1990's. This makes its contents illegal for commercial use and quite dated.

Querying Movie Information

Write MS Access queries to determine the following information, saving each query under a separate name in your movies database file:

1. What is the oldest movie in the database?
2. How many movies are there in the database?
3. What are the top 5 movies with respect to "votes"? How about the worst movie?
4. Find all the movies in which "Arnold Schwarzenegger" played a role. Which one of these was the most popular? Which one did you like the best? Which of the characters do you think would make the best governor of California?
5. Find all the movies that had a lead named Amanda (hint: use LIKE 'Amanda*' as your condition. The * is acts as a wildcard here.).
6. What performer performed in the most movies?

Adding New Data

Add the data required to represent 2 or 3 more movies that aren't in the current database but should be. You'll export your new additions to a new database file to attach to your KV lab submission, which I'll use to upgrade the main file with newer movies. One relatively painless way to do this is to do the following:

1. Pick the movies you'd like to add, collect the appropriate information for them (e.g., from IMDB). Don't add all the actors/actresses, just the most important ones.
2. Make a copy of the database file (i.e., the mdb file) that contains the full movies database and the queries you wrote above.
3. Add the records required for your new movies. Remember that if the performer is not already in the database, you'll have to add a record for them with a unique ID. Just set the score and the votes fields to 0. Note the following:
   • To satisfy entity integrity constraints, you'll have to pick some new, unused ID number for any new table entry - anything that hasn't been used will work.
   • To satisfy referential integrity constraints, you'll have to add the performer and movie records before you add the casting records.
4. Clean out the database file by deleting all the records that you don't need for your new entries. This is easy enough to do for the Movie and Casting tables, just select the old records and press "Delete". For the Performer table, however, you'll need to keep the old records you used for your new movies. Let the database help you with this - select all the Casting records, hit delete and let the database decide which ones to keep based on referential integrity constraints.
5. When you're done with this, you'll notice that the database file is still nearly 10M in size. To reduce the size of the file for submission, compact the database by choosing "Tools"-"Database Utilities"-"Compact and Repair Database".

Save your new file, which should still include your queries, so you can submit it with your KV lab 4 submission.

Finding New Information

Find some new fact from the database that you think people in the class will be interested in. I don't care what it is. Perhaps it will be based on the new data that you added.

We'll conclude the lab with each person recounting: (1) the new data they added; and (2) the new information they found and how they found it.

Checking In

Use KV to submit the database file you created (with your queries still included) and the your new records. Do not submit the full movies database; it's too large.