Introduction to Computer Applications

Background Information
Over the course of a given week, the vast majority of Americans
watch at least some amount of television. Since most television
shows are paid for by the sales of advertising, broadcasters and
advertisers have a vested interest in knowing who is watching
each television show.
Nielsen Media Research conducts surveys on a weekly basis to calculate the ratings
for television shows. The ratings are used for a variety of purposes including
deciding which shows to air and when to schedule them, how much advertising
costs, and when to buy advertising.
Problem Statement
In this assignment, we wish to create a database to store and analyze television
show ratings data for the top 20 most-watched shows in a given week.
Instructions
IMPORTANT: Complete the below steps in the order they are given. Completing the
steps out of order may complicate the assignment or result in an incorrect result.
1. Begin by creating a new Microsoft Access database named
lastname_firstname_ntrp.accdb .
2. We would like to begin by making a table to store the television networks.
a. Create a table named Networks to store the names of the television
networks and their abbreviations. Designate the abbreviation as primary
key.
b. Enter records for all networks below.
HINT: The Networks table will contain 6 records.
Network Name Network Abbreviation
National Broadcasting Company NBC
American Broadcasting Company ABC
Columbia Broadcasting System CBS
Fox Broadcasting Company FOX
CW Television Network CW
Public Broadcasting Service PBS
3. We need to store information on various television show genres.
a. Create a table named Genres to store the show genres. Include a
separate field (other than the genre) to serve as primary key.
HOMEWORK INSTRUCTIONS
Homework #4
Nielsen Television Ratings Problem
Introduction to Computer Applications
West Virginia University
Page 2 of 6 Version 15.1
Modified 11/8/2015
b. Enter records into the Genres table corresponding to the below genres.
HINT: The Genres table will contain 6 records.
 Comedy
 Drama
 Sports
 Reality
 Action
 News
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 shows.
a. Create a table named Shows to store information on each of our shows
(listed below under Step 5b). Some requirements for this table appear
below.
IMPORTANT: Completely define the Shows table before entering records.
i. For the primary key, use an AutoNumber-type field to store an ID
number.
ii. Create a text field to store the abbreviation for the show’s network
(e.g., “FOX” for the show Empire).
For the network abbreviation, create a relationship to the primary
key of the Networks table to indicate which network airs each show.
Enforce referential integrity and enable cascade updates, but do not
enable cascade deletes.
iii. Provide a field to store the name of the television show.
iv. Using a lookup field referencing the Genres table, allow the user to
specify the show genre. The user should be able to select the full
name of the genre (e.g., “Drama”) from the lookup field dropdown
list.
Enable data integrity, restricting deletes, on the relationship created
by the Lookup Wizard.
v. Provide a fixed-type number field with 1 decimal place to store the
show’s rating.
vi. Provide a field formatted as a percentage with 1 decimal place to
store the show’s viewer share.
HOMEWORK INSTRUCTIONS
Homework #4
Nielsen Television Ratings Problem
Introduction to Computer Applications
West Virginia University
Page 3 of 6 Version 15.1
Modified 11/8/2015
vii. Include a standard-type number field with no decimal places to
store the number of viewers.
viii. Create a lookup field that will allow us to specify the day that the
show aired. You must provide the possible values shown below:
◊ Sunday
◊ Monday
◊ Tuesday
◊ Wednesday
◊ Thursday
◊ Friday
◊ Saturday
ix. Create a date/time field to store the start time of the show.
Represent the time in the medium time format (e.g., “10:22 PM”).
b. Enter the below show data into your Shows table as appropriate. The
below information is from the week ending October 4, 2015 [1].
HINT: The Shows table will contain 20 records.
Ntwk Show Genre Rtng Share Viewers Day Time
NBC NBC Sunday Night Football Sports 14.1 24.0% 16,433,000 Sun 8:30pm
CBS CBS+NFLN Thursday Night
Football
Sports 11.8 21.0% 13,737,000 Thu 8:25pm
CBS NCIS Drama 10.3 17.0% 11,962,000 Tue 8:00pm
NBC Sunday Night NFL Pre-Kick Sports 10.3 17.0% 12,024,000 Sun 7:00pm
CBS The Big Bang Theory Comedy 9.5 16.0% 11,023,000 Mon 8:00pm
FOX Empire Drama 8.2 13.0% 9,517,000 Wed 9:00pm
NBC Voice-Tue Reality 8.2 13.0% 9,533,000 Tue 9:00pm
CBS NCIS: New Orleans Drama 8.1 13.0% 9,445,000 Tue 9:00pm
FOX The OT Sports 7.7 14.0% 8,979,000 Mon 12:00am
CBS 60 Minutes News 7.6 13.0% 8,810,000 Sun 7:00pm
CBS Madam Secretary Drama 7.5 12.0% 8,761,000 Sun 8:00pm
NBC Football Night America Part
3
Sports 7.4 13.0% 8,563,000 Sun 7:00pm
NBC Voice Reality 7.4 12.0% 8,610,000 Mon 8:00pm
CBS Blue Bloods Drama 7.3 14.0% 8,546,000 Fri 10:00pm
ABC Dancing With The Stars Reality 7.2 11.0% 8,337,000 Mon 8:00pm
CBS CBS+NFLN Thursday Night
Pre-Kick
Sports 7.0 13.0% 8,101,000 Thu 8:00pm
CBS Criminal Minds Drama 6.3 10.0% 7,332,000 Wed 9:00pm
CBS Limitless Drama 6.3 12.0% 7,360,000 Tue 10:00pm
ABC Scandal Drama 6.2 10.0% 7,215,000 Thu 9:00pm
CBS The Good Wife Drama 6.1 10.0% 7,147,000 Sun 9:00pm
HOMEWORK INSTRUCTIONS
Homework #4
Nielsen Television Ratings Problem
Introduction to Computer Applications
West Virginia University
Page 4 of 6 Version 15.1
Modified 11/8/2015
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 show names, their full network name, full
genre name, ratings, viewer shares, number of viewers, air day and air
time.
Sort by ratings and then by viewer share, both in descending order.
HINT: This query will show 20 records and 8 fields.
b. Create a query that lists show names, their full network name, ratings
and the number of viewers.
Only display shows with a rating of at least 5.0 but not more than 7.0.
Sort by network and then by rating, both in ascending order.
HINT: This query will show 5 records and 4 fields.
c. Create a query that lists each genre name, the count of shows within that
genre, and the total number of viewers for that genre.
Your results must include all genres, even if they have no shows.
Format the total number of viewers as a standard-type number with no
decimal places. Sort by the total number of viewers in descending order.
HINT: This query will show 6 records and 3 fields.
d. Create a query that displays each full network name and the average of
the ratings for that network’s shows.
Format the average rating as a fixed-type number with 2 decimal places.
Sort by the full network name in ascending order.
HINT: This query will show 4 records and 2 fields.
HOMEWORK INSTRUCTIONS
Homework #4
Nielsen Television Ratings Problem
Introduction to Computer Applications
West Virginia University
Page 5 of 6 Version 15.1
Modified 11/8/2015
e. Create a query that lists all shows with their names, ratings, viewer
shares, and number of viewers. You must also create a calculated field
that estimates the total number of households watching, which can be
determined from the rating value.
You can calculate the number of households watching using the formula:
([?ℎ???. ??????] ∗ 1156000)
Format the number of households as a standard-type number with no
decimal places. Sort by the number of viewers and then by the number of
households, both in descending order.
HINT: This query will show 20 records and 5 fields.
7. Using the Form Wizard, create a form with subform. The main form should
display the full network name. The subform must display a datasheet with all
Shows table fields.
Name the main form NetworkData and the subform NetworkDataSubform .
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 show name in ascending order. Use a stepped
layout and landscape page orientation. Name the report ShowSummary.
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 Nielsen select the participants who track their viewing so that
the ratings can be generated? Do you think the method used gives
accurate results for the number of viewers for the shows?
b. In looking at the top 20 shows listed, only one of the shows aired on
Friday and none were on Saturday. Why do you think this might be the
case?
c. In addition to overall viewer figures, Nielsen tracks viewers by various
demographics including age and gender. Why might one want to use this
demographic information? For example, why might it matter if a show
has many female teenage viewers but few male viewers in their forties?
HOMEWORK INSTRUCTIONS
Homework #4
Nielsen Television Ratings Problem
Introduction to Computer Applications
West Virginia University
Page 6 of 6 Version 15.1
Modified 11/8/2015
d. Four times a year, advertising rates are set based on ratings recorded
during “sweeps” periods. Do you think that measuring viewers just at
certain times of the year like this gives accurate overall results? Why or
why not?
e. Average television show ratings have been decreasing over time. What
are some possible causes for this?
10. Run the Compact and Repair Database utility on your database. Ignore any
errors you receive when running the utility.
Grading Rubric
This assignment is worth 50 points and will be graded based upon the listed
components. Instructors may adjust point values as they deem appropriate.
Steps 2a-b 4 points total Steps 6a-e 3.5 points each
Steps 3a-b 4 points total Step 7 3 points
Step 5a 5 points Step 8 3 points
Step 5b 3.5 points Steps 9a-e (pick 4 of 5) 2.5 points each
References
[1] “This week’s broadcast ratings,” Media Life Magazine, Oct. 10, 2015. Available:
http://www.medialifemagazine.com/this-weeks-broadcast-ratings/. Accessed:
Oct. 10, 2015.
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!