Database for Media World Cable Services

This assignment will give you practice in designing a database based on a specification and entering that database into MS-Access. In addition, you will answer questions about MS-Access and database concepts.

 

Be sure to read the entire assignment/specification before you begin work on the design and development of the database.

 

Part A. Draw a diagram of your Design

 

After reading the entire assignment/specification, prepare a database design drawing. Document your design by preparing a drawing of the database as demonstrated in class. 

 

The drawing is a useful way to think about and communicate your design.

 

The drawing will also be useful to you when you are entering your design into MS-Access.

 

This drawing is for your use and you do not need to submit it for evaluation.

 

 

Use the following information to make your drawing:

 

High-level description of user requirements (facts of the case)

 

MediaWorld Cable Services needs to track cable TV customer information, such as address, preferred contact method, and phone number.

 

MediaWorld Cable Services assigns each customer an identification code to uniquely identify that customer. It is called Customer ID.

 

Each customer may subscribe to multiple premium channels. The database design must allow information on each premium channel subscription (premium channel name and subscription date) to be stored for the subscribing customer.

 

MediaWorld Cable Services does not assign a unique identification code for each premium channel subscription for each customer.

 

Details on the information to be stored in the database (more facts of the case)

 

Tables:

 

Customer

You will need a table to store information for each customer.  Name this table Customer

 

Data to be stored for each customer:

The following fields should be on the Customer table:

• Customer ID (value assigned by MediaWorld Cable Services)
• Last Name
• Street Address
• City
• State
• Zip Code
• Phone Number
• Installation Date
• Preferred Contact Method – value will be Phone, Email, US Mail, or Text. You will use the Look-up Wizard to create a look-up (drop down list) for this field based on a list of the four values which you will enter into the Look-up Wizard.
• Number of devices leased

 

Customer Premium Channel Subscription

A customer can subscribe to any number of premium channels. You will need a table to store information on each premium channel subscription for each customer.  There may be any number of premium channel subscriptions for each customer. Name this table Customer Premium Channel Sub.

 

Data to be stored for each customer’s premium channel subscription:

The following two fields should be on the Customer Premium Channel Sub table in order to store data for each premium channel subscription a customer has:

• Premium Channel Name

Name of the Premium Channel to which that customer subscribes.  (You will use the Look-up Wizard to create a look-up (drop down list) for this field based on a reference table (Premium Channel Name Ref) that contains the name of each available premium channel. (See Premium Channel Name Reference table described below)

• Subscription Date

Date that the customer established the subscription to this premium channel

 

Additional Fields on this table:

You will need a primary key field on each table, including this one. A primary key field must contain data that uniquely identifies each record on the table. Do the two fields above contain data that uniquely identifies each record on the table? If not, what do you need to add to your design?

 

You will also need a field on this table that allows each customer premium channel subscription record to attach to its customer. What do you need to add to your design to provide that?

Tables(cont.)

 

Premium Channel Name Reference

The purpose of this table is to serve as a source for the Premium Channel Name look-up on the Customer Premium Channel Sub table.  Name this table Premium Channel Name Ref.

 

The following field is the only field that will be on this table:

• Premium Channel Name (primary key)

 

You will need to include a record for each available premium channel. The list of names of available premium channels is shown below.  You will need this information when you enter your database design into MS-Access (See Part B.2a)

 

Premium channel names:

• HBO
• Showtime
• MLB
• Starz
• NHL
• Health
• NBA
• Elvis

 

 

 

Relationship:

Establish a 1àn (one-to-many) relationship between the Customer table and the Customer Premium Channel Sub table.

 

Enforce referential integrity.

 

 

 

Part B. Build your database using MS-Access. 

 

Using the drawing of your database design you created in Part A. above, enter your design into MS-Access.

 

1. Create a new database in MS-Access. Name it MediaWorldZ.accdb

 

2. Add the tables, fields, primary keys, look-ups, and relationships you have documented in your drawing.

 

2a. Note: Before you add the Look-up for the Premium Channel Name field on the Customer Premium

Channel Sub table, you will need to add a record for each premium channel name to the Premium Channel Name Ref table. Use Datasheet view to add a record to the Premium Channel Name Ref table for each premium channel name listed in Part A. Premium Channel Name Ref table

.

 

 

Part C. Add a form / sub-form combination to your MS-Access database so you can add customer and customer premium channel subscription information to your database

 

1. Create a Customer form and Customer Premium Channel Sub sub-form combination to allow you to enter the data on your customers and their premium channel subscriptions.

 

1. Name the Customer form Customer Data Entry.

 

1. Name the Customer Premium Channel Sub sub-form Customer Premium Channel Data Entry

 

 

Part D. Use the form and sub-form you have created to enter the customer and customer premium channel subscription data listed on pages 5-7:

 

1. Use the form and sub-form you created in Part C to enter data for the eleven customers listed below.

 

2. Also use the form and sub-from you created to add yourself as a Customer, use your own last name, give yourself a Customer ID that begins with the first letter of your last name followed by three numbers, add customer information you make up, add subscriptions for yourself (any number of subscriptions)

 

Do not use datasheet view for this data entry. Do not attempt to import this data….use your form / sub-form

 

There should be twelve customers(including yourself) on the database once you have completed your data entry.

 

 

Customer ID	Last Name	Street Address	City	State	Zip Code	Phone Number	Installation  Date	Preferred Contact Method	Number of Devices Leased	Premium Channel Name                               And   Subscription   Date    for each Subscription
A046	Chitwood	342 Elm	Park Ridge	IL	60032	847 2222222	4/17/2002	Email	1	MLB 1/1/2008 HBO 1/1/2008
B047	Ziegler	157 Fargo	Gladbury	IL	60133	312 4444444	12/1/2004	Text	5	HBO 1/1/2008 MLB 1/1/2009 Showtime   2/1/2007 NHL 1/31/2004 NBA 1/10/2007 Elvis 1/1/2005 Health 12/1/2004

 

C037	Jason	5665 Wilbur	Gladbury	IL	60133	847 5555555	9/1/2004	Phone	3	HBO          1/1/2006 Elvis 3/1/2004 MLB 4/1/2005
D543	Lemur	100 Chevy Chase	Gladbury	IL	60132	847 8888888	12/31/2003	Phone	2	Starz 9/1/2008 HBO 10/1/2005 Health 8/1/2004
F252	Martin	304 Bell	Berwyn	IL	60402	708 6666666	8/12/2002	Email	1	Elvis 1/1/2008
G094	Perkins	4322 Orchard Ct	Des Plaines	IL	60061	847 7777777	12/31/2002	Text	2	Showtime     1/1/2004 HBO           1/1/2004
H124	Ronald	601 Stewart	Bellville	IL	60135	773 3333333	10/1/2003	US Mail	2	Starz 12/31/2004 MLB 1/1/2007 NHL 2/3/2007
J023	Santos	3382 Armitage	Gladbury	IL	60402	773 9999999	4/5/2006	Email	1	Health 1/1/2007 Showtime 2/1/2007

 

K001

Shaq

5544 Susan

Elm Park

IL

60031

708 1111111

1/1/2004

Text

3

HBO 1/1/2003 Health 1/1/2004 Showtime 2/1/2005 NHL 1/31/2008

L245

Stang

565 Packard

Riverside

IL

60546

708 9999999

4/1/2005

Phone

1

HBO 7/1/2006

M734

Waters

12233 Park

Oak Park

IL

60632

708 4444444

5/9/2000

Email

2

Showtime     1/1/2004 NBA 1/31/2005 MLB 2/1/2005 Health 2/1/2005

Your ID (first initial of your last name followed by three numbers)

your last name

add subscriptions for yourself

make up inf

ormation for e

ach field—-

————-

—————

——————-

——————-

—–à

 

         LSP121 – Individual Assignment #3b – Database Design/Development and Conceptual Knowledge

 

 

Part E. Add Queries to your MS-Access database

 

Add the following queries to your MS-Access database:

 

1. Customers in Gladbury. List Last Name, Street Address, City, State, Zip Code, and Phone Number for all customers living in Gladbury. Sort alphabetically by Last Name. Name the query “Customers in Gladbury”.

 

2. Customers to Phone. List Last Name, Phone Number, and Preferred Contact Method for all customers who prefer to be contacted by phone. Sort ascending by Phone Number. Name the query “Customers to Phone”.

 

3. Health Subscribers. List Last Name, Preferred Contact Method, Zip Code, Premium Channel Name, and Subscription Date for all customers who subscribe to the Health premium channel. Sort the query by Zip Code (Ascending). Name the query “Health Subscribers by Zip Code”.

 

4. Sports Fans. List Last Name, Zip Code, Phone Number, Premium Channel Name(s), and Subscription Date(s) for customers who subscribe to MLB or NHL or NBA Premium Channels. Sort the query alphabetically by Premium Channel Name. Name the query “Sports Fans”.

 

5. Customers Installed Before 2004. List Customer ID, Last Name, Number of Devices Leased, and Installation Date for those customers whose equipment was installed before 2004 Sort the results by Installation Date from newest to oldest (Descending). Name the query “Installations Before 2004”.

 

Part F. Add Reports to your MS-Access database

 

Add the following reports to your MS-Access database:

 

1. All Customers with Premium Channel Subscriptions. Develop a report of all Customers with Premium

Channel subscriptions.  List Last Name, Preferred Contact Method, Number of Devices Leased, each Premium Channel they subscribe to, and Subscription Date for each subscription.  Sort the report alphabetically by Last

Name. Show the Last Name, Preferred Contact Method, and Number of Devices Leased only once for each Customer. After grouping by those fields, sort by Premium Channel Name. Name the report All Customers with Premium Channel Subscriptions.

 

2. Sports Fans. Develop a report that lists Last Name, Zip Code, Phone Number, Premium Channel Name(s), and Subscription Date(s) for those customers with MLB or NHL or NBA premium channel subscriptions. Sort the report alphabetically by Last Name. Show the Last Name, Zip Code, and Phone Number for each customer only once per customer. After grouping the report by those fields, sort the report so that MLB appears first, then

NBA, then NHL if a customer subscribes to more than one of those three premium channels. Name the report Sports Fans (Use the query answerset from E.4 as input to this report.)

 

 

 

 

 

Page 8  of  9

 

LSP121 – Individual Assignment #3b – Database Design/Development and Conceptual Knowledge

 

 

Part G. Submit your completed database to D2L Submissions

 

1.Close your MS-Access database session

 

2. Submit the MediaWorldZ.accdb database file to D2L | Submissions | Individual #3b – Database

 

 

 

Part H. Answer concept questions

 

1. Choose D2L | Submissions | Individual 3b – Concepts

 

2. Download and save the LSP 121 Ind Assign 3b Concepts Spring 2020 Word file attached to that submission folder.

 

3. Update the Word File with your answers to the concept questions.

 

4. Submit the updated Word file to Submissions | Individual #3b – Concepts

 

 

 

Part I. (Optional – For extra credit ) Zipped submission file

 

Zip/compress the MS-Access database and MS-Word concept document together and submit the zipped file to Submissions | Individual Assignment 3b – zipped submission

 

1. Create a zip (compressed) folder. You may give it any name you wish.

 

2. Copy your MS-Access database file into the zip file.

 

3. Copy your MS-Word file (from H. above) into the zip file.

 

4. Submit the zip file to D2L | Submissions | Individual #3b. – Zip – EC

Page 9  of  9