Access Lab: Designing Tables
In this lab, you'll be creating a database for movie reviews. Here's what we'd like our
database to be able to do:
Keep track of when the title, release date, actors, director, and box office revenue
of our movies
See which movies a specific actor or director worked on
Keep a listing of reviews. Each movie can have any number of reviews (0 to
In this lab, we will only
the database tables and populate them with a few bits of
information. Answering questions, such as "Can I see all reviews for this movie?" and
"What movies was actor X in?", may be difficult to answer, for now. In later labs, we'll
make queries and forms to help us view the information better.
Thinking About the Design
Designing a database to do all of this is not simple, and there are many ways to do it.
Some are better than others. We need to think about all possibilities. For example, an
actor in one movie could later go on to direct another movie. Maybe even the director
appears as one of the main actors in the same movie. When designing a database, we
need to be able to be flexible.
Let's open up Access and create a new, blank database. Be sure to remember where
you save it and name it, whatever you want ("lab7" is a good name).
Now, let's create a new table, called "movies." Probably Access already has a new table
created for you. You just need to click the save button, and Access will ask you to name
it. There are a few properties about movies which do NOT change and which we always
know. For example, all movies have a title, a release date, and made some money
(some revenue). We will assume that all movies have exactly 1 director, but that there
may be many actors in a movie. So, how do we represent all of this?
Go to the design view of the movies table. Let's get rid of the automatically created
primary key (which is a field of type "Autonumber"). Let's add in a title field (of type text,
call it "title"), a revenue field (of type currency, call it "revenue"), a release date field (of
type date, call it "releasedate"), and then a director field (of type number, called