LSP 121 – Database Assignment

From the class webpage, download and save the file actors.txt. Open Access and create a new blank database, naming it Homework 6.

  • Then import actors.txt into your database. Note that you are importing a TEXT file (not a spreadsheet). So do not choose Excel file for importing. Look around and decide which option is more suitable. Follow the Import Spreadsheet Wizard and answer the wizard’s questions accordingly.
  • Use the comma delimeter.
  • Does the first row contain column/field names?
  • Do not create a primary key.

Note: the text file is very large but obviously not complete. There are numerous actors and actresses missing, but that’s OK. We can still play around looking through the data.

Once the spreadsheet has been imported into the Homework 6 database, be sure to check the file xx$_ImportErrors (if one exists) and address any errors that occurred during importing. (For this particular file, there should not be any errors).

If you are satisfied that the data was imported correctly, perform the following operations:

In the design view (Home  View  Design View), give your columns the following names: ID, ActorName, MovieName.

Designate the first field as a primary key. Paste the first 5 rows of your table into Word.

Query: How many movies did the actor Peter Sellers make?

Using the Filter operation, filter all records for all actors in the movie Casablanca. List these actors to your Word document.

Who is your favorite actor/actress? Using the database, what movies did that person star in? (If your favorite actor is not listed, try another.) List the actor’s name and the movies to your Word document.

Create a new database with a table called ‘Singers’.

  • Create fields for Name (use their full name – you don’t have to separate first and last names), BirthYear, HomeCity, Song.
  • Add at least three different singers to your table. You can look up birth year and home city in Wikipedia.
  • For each singer, have at least two songs.
  • You will need a primary key. Can you use any of the current fields? Explain. If not, create a new field as a primary key.
  • Paste the table into your Word document.
  • Demonstrate at least 3 different queries and paste the results.