CIT 010 Index > Database

Read everything before doing anything.

Database

The object of this assignment is to use the features of Microsoft Access to create a database like the one at the geekgirls.com website.

This will be a database of classic (i.e., old) movies and actors. Each movie has a title and the year the movie came out. The actors have a first name, last name, and gender. An actor can appear in more than one movie.

As the book discusses, we want to create a relational database so that we don’t have duplicated data (if you had one actor in five movies, you wouldn’t want her name duplicated five times).

Use Microsoft Access to create the tables and data as specified here.

The Actors Table

Create a table named actors with the following fields. The primary key is highlighted like this. A lookup field is marked like this. (You don’t have to make the cells colored in Microsoft Access. The colors are just here on this web page to help you see which fields are which.) The number in the Data Type column tells how many characters the field may contain.

Field NameData TypeDescription
IDAutoNumberunique ID number for an actor
FirstNameText [25]person's first name
LastNameText [35]person's last name
GenderText [1]person's gender

The Movies Table

Create a table called movies with the following fields:

Field NameData TypeDescription
TitleText [45]Name of movie
ReleaseNumberYear movie was released

The Cast Table

Create a table named cast with the following fields:

Field NameData TypeDescription
ActorIDLookup from actors table A reference to an actor
MovieLookup from movies table reference to a movie title

Relationships

The relationships among the tables look like this:

Inter-table relationships

Filling the Tables

Place the following information into the actors table

IDFirstNameLastName Gender
1CaryGrantM
2IngridBergmanF
3HumphreyBogartM
4SydneyGreenstreetM
5MaryAstorF

Please put the following data into the movies table:

TitleRelease
Notorious1946
Casablanca1942
The Maltese Falcon1941
Spellbound1945
North by Northwest1959
Across the Pacific1942

Put the following data into the cast table:

ActorIDMovie
1Notorious
1North by Northwest
2Notorious
2Casablanca
3Casablanca
3The Maltese Falcon
3Across the Pacific
4The Maltese Falcon
4Casablanca
4Across the Pacific
5The Maltese Falcon
5Across the Pacific

Queries

Now create the following query: A list of movies, sorted by title, the year it was released, and the first and last names of its cast, sorted by last name. Name this query MovieQuery. It should look like this.

Upload the finished database file to Moodle. Please name your file in the form:

lastname_firstname.accdb

For example, if your name is Pierre Toulemonde, your filename would be toulemonde_pierre.accdb. Do not put blanks in your filename, and use all lowercase letters. If your file is not named properly, it will not be graded.