Download Report

HOMEWORK HELP! PROJECT
Microsoft Access I
Box Office Sales Problem
Background Information
Each year, hundreds of millions of Americans go to the
theaters for at least one movie. According to an MPAA study,
the average moviegoer saw about 8.5 films. All told, ticket
sales amounted to $10.8 billion revenue during 2012.
Problem Statement
In this project, we wish to create a database to store and
analyze the top 15 highest-grossing movies from the year
2012.
Project Instructions
IMPORTANT: Complete the below steps in the order they are given. Completing the
steps out of order may complicate the project or result in an incorrect result.
1.
Begin by creating a new Microsoft Access database named
lastname_firstname_bosp.accdb .
2.
We would like to begin by making a table to store the production company
names.
a.
Create a table named ProductionCompanies to store the names of the
production companies and their abbreviations. Designate the
abbreviation as primary key.
b.
Enter records for all production companies below.
HINT: The ProductionCompanies table will contain 9 records.
Company Name
Buena Vista
Warner Bros.
Lions Gate
Sony
Universal
DreamWorks
20th Century Fox
Weinstein Company
Metro-Goldwyn-Mayer
3.
Company Abbreviation
BV
WB
LGF
SONY
UNI
DW
FOX
WEIN
MGM
We need to store information on various movie genres.
a.
Create a table named Genres to store the movie genres. Include a
separate field (other than the genre) to serve as primary key.
Introduction to Computer Applications
West Virginia University
Page 1 of 7
Version 2.4
Modified 5/20/2015
HOMEWORK HELP! PROJECT
Microsoft Access I
Box Office Sales Problem
b.
Enter records into the Genres table corresponding to the below genres.
HINT: The Genres table will contain 8 records.

Comedy

Drama

Crime

Animation

Action

Science Fiction

Fantasy

Romance
4.
There is nothing to do for this step. Please proceed to the next step.
5.
To finish adding our dataset, we must store information about the movies.
a.
Create a table named Movies to store information on each of our movies
(listed below under Step 5b). Some requirements for this table appear
below.
IMPORTANT: Completely define the Movies table before entering records.
i.
For the primary key, use an AutoNumber-type field to store an ID
number.
ii.
Provide a field to store the name of the movie name.
iii.
Using a lookup field to the ProductionCompanies table, allow the
user to specify the production company of each movie. The user
should be able to select the full name of the production company
(e.g., “Warner Bros.”) from the lookup field dropdown list.
The Lookup Wizard will create a relationship automatically to the
ProductionCompanies table. Edit this relationship to enforce
referential integrity and enable cascade updates, but do not enable
cascade deletes.
Introduction to Computer Applications
West Virginia University
Page 2 of 7
Version 2.4
Modified 5/20/2015
HOMEWORK HELP! PROJECT
Microsoft Access I
Box Office Sales Problem
iv.
Using a lookup field to the Genres table, allow the user to specify
the movie genre. The user should be able to select the full name of
the genre (e.g., “Drama”) from the lookup field dropdown list.
The Lookup Wizard will create a relationship automatically to the
Genres table. Edit this relationship to enforce referential integrity
and enable cascade updates, but do not enable cascade deletes.
v.
Provide a field formatted as currency with no decimal places to store
the movie‘s budget.
vi.
Provide a field formatted as currency with no decimal places to store
the movie’s domestic revenue.
vii.
Provide a field formatted as a percentage with no decimal places to
store the movie’s rating on Rotten Tomatoes.
viii. Provide a short date-formatted date/time field to store the film’s
release date.
Introduction to Computer Applications
West Virginia University
Page 3 of 7
Version 2.4
Modified 5/20/2015
HOMEWORK HELP! PROJECT
Microsoft Access I
Box Office Sales Problem
b.
Enter the below movie data into your Movies table as appropriate. The
below information is from the year 2012.
HINT: The Movies table will contain 15 records. The budget is entered in
millions.
Movie Name
Prod
Co.
BV
Genre
Budget
Dom Rev
Action
$220,000,000
$623,357,910
WB
Action
$250,000,000
$448,139,099
LG
Action
$78,000,000
$408,010,692
Skyfall
Sony
Action
$200,000,000
$304,360,277
The Hobbit: An
Unexpected Journey
The Twilight Saga:
Breaking Dawn Part
2
The Amazing SpiderMan
Brave
WB
Fantasy
$200,000,000
$301,976,034
LG
Rom.
$120,000,000
$292,324,737
Sony
Action
$230,000,000
$262,030,663
BV
Anim.
$185,000,000
$237,283,207
Ted
Univ.
Comedy
$50,000,000
$218,815,487
Madagascar 3:
Europe's Most
Wanted
Dr. Seuss' The Lorax
DW
Anim.
$145,000,000
$216,391,482
Univ.
Anim.
$70,000,000
$214,030,500
Wreck-It Ralph
BV
Anim.
$165,000,000
$187,906,932
Lincoln
BV
Drama
$65,000,000
$180,925,436
Men in Black 3
Sony
Sci Fi
$225,000,000
$179,020,854
Ice Age: Continental
Drift
Fox
Anim.
$95,000,000
$161,321,843
Marvel's The
Avengers
The Dark Knight
Rises
The Hunger Games
Introduction to Computer Applications
West Virginia University
Page 4 of 7
Rt
ng
92
%
87
%
85
%
92
%
65
%
48
%
Rel.
Date
5/4/
2012
7/20/
2012
3/23
/2012
11/9/
2012
12/14/
2012
11/16/
2012
73
%
78
%
69
%
79
%
7/3/
2012
6/22/
2012
6/29/
2012
6/8/
2012
55
%
86
%
89
%
70
%
37
%
3/2/
2012
11/2/
2012
11/9/
2012
5/25/
2012
7/13/
2012
Version 2.4
Modified 5/20/2015
HOMEWORK HELP! PROJECT
Microsoft Access I
Box Office Sales Problem
6.
Create separate queries to provide the information requested below. Name
each query after the step in which it appears (e.g., name the query in Step 6a
as Query6A ).
HINT: Run your queries to test them. Make sure that they display all and only
the records that you would expect to appear.
a.
Create a query that lists all movie names, their full production company
name, full genre name, budget, revenue, rating, and release date.
Sort by rating and domestic revenue, both in descending order.
HINT: This query will show 15 records and 7 fields.
b.
Create a query that lists movie names, their genre name, and rating.
Only display movies with a rating of at least 65% but less than 80%.
Sort by rating in ascending order.
HINT: This query will show 6 records and 3 fields.
c.
Create a query that lists each genre name, the count of movies within
that genre, and the average domestic revenue for each movie.
Your results must include all genres, even if they have no movies.
Sort by the genre name in descending order.
HINT: This query will show 8 records and 3 fields.
d.
Create a query that displays each production company name and the
average of ratings for its movies.
Format the average as a percentage with no decimal places. Sort by the
production company name in ascending order.
HINT: This query will show 7 records and 2 fields.
Introduction to Computer Applications
West Virginia University
Page 5 of 7
Version 2.4
Modified 5/20/2015
HOMEWORK HELP! PROJECT
Microsoft Access I
Box Office Sales Problem
e.
Create a query that lists all movies with their names, budget, and
domestic revenue. You must also create a calculated field that figures
each movie’s profit.
You can calculate a movie’s profit using the formula:
([𝑀𝑜𝑣𝑖𝑒𝑠. 𝐷𝑜𝑚𝑒𝑠𝑡𝑖𝑐𝑅𝑒𝑣𝑒𝑛𝑢𝑒] − [𝑀𝑜𝑣𝑖𝑒𝑠. 𝐵𝑢𝑑𝑔𝑒𝑡])
Sort by profit in descending order.
HINT: This query will show 15 records and 4 fields.
7.
Using the Form Wizard, create a form with subform. The main form should
display the full production company name. The subform must display a
datasheet with all Movies table fields.
Name the main form CompanyData and the subform CompanyDataSubform .
8.
Using the Report Wizard, create a report to show the results of Query6A.
Display all fields from the query.
View by genre and sort by movie name in ascending order. Use a stepped
layout and landscape page orientation. Name the report MovieSummary.
Ensure the full widths of all columns are visible on the report.
9.
Create a table named AnalysisQuestions . This table will need to be able to
store which question is being answered and your answer to that question in
each record.
Answer four of the five below questions, one question per record. Specifically
indicate the question you are answering for each record.
a.
How does Rotten Tomatoes generate its tomatometer ratings? Do you
think this method accurately represents the quality of a movie?
b.
14 of the top 15 movies were released on a Friday. Why do you think the
production companies choose to release moves on Fridays?
c.
Several of the top movies were sequels or part of a series. Why might
these films be attractive to production companies?
d.
Do you see any patterns in the movie release dates? Explain why or why
not.
e.
What other ways can production companies make money from films other
than domestic ticket sales?
10. Run the Compact and Repair Database utility on your database.
Introduction to Computer Applications
West Virginia University
Page 6 of 7
Version 2.4
Modified 5/20/2015
HOMEWORK HELP! PROJECT
Microsoft Access I
Box Office Sales Problem
Curriculum Information
Project Type
Microsoft Access database
Relationship to GEC Objective 2
In addition to providing basic research and data analysis skills, this assignment
allows students to explore the economics of mass media and how changing
technologies can have a financial impact on advertising.
Relationship to GEC Objective 4
Most individuals watch at least some movies as a medium of entertainment. This
assignment helps expose students to how a movie’s ratings determine which movie
one should watch.
Grading Rubric
This project will be worth 50 points and will be graded based upon the following
components. The instructor may adjust the below values as he or she feels
appropriate:
Steps 2a-b
Steps 3a-b
Step 5a
Step 5b
4 points total
4 points total
5 points
3.5 points
Steps 6a-e
Step 7
Step 8
Steps 9a-e (pick 4 of 5)
3.5 points each
3 points
3 points
2.5 points each
Acknowledgments
Rotten Tomatoes rating information was taken from
http://www.rottentomatoes.com/. Other data is from Box Office Mojo at
http://boxofficemojo.com/yearly/chart/. All is used under fair-use provisions of
copyright law.
Introduction to Computer Applications
West Virginia University
Page 7 of 7
Version 2.4
Modified 5/20/2015