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)
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
- 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?
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:
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.
- Create a new database in MS-Access. Name it MediaWorldZ.accdb
- 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
- 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.
- Name the Customer form Customer Data Entry.
- 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:
- Use the form and sub-form you created in Part C to enter data for the eleven customers listed below.
- 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.
|Phone Number||Installation Date||Preferred
|A046||Chitwood||342 Elm||Park Ridge||IL||60032||847 2222222||4/17/2002||
|B047||Ziegler||157 Fargo||Gladbury||IL||60133||312 4444444||12/1/2004||Text||
|C037||Jason||5665 Wilbur||Gladbury||IL||60133||847 5555555||9/1/2004||Phone||
|D543||Lemur||100 Chevy Chase||Gladbury||IL||60132||847 8888888||12/31/2003||Phone||
|F252||Martin||304 Bell||Berwyn||IL||60402||708 6666666||8/12/2002||
|H124||Ronald||601 Stewart||Bellville||IL||60135||773 3333333||10/1/2003||US Mail||
|K001||Shaq||5544 Susan||Elm Park||IL||60031||708 1111111||1/1/2004||Text||
|L245||Stang||565 Packard||Riverside||IL||60546||708 9999999||4/1/2005||Phone||
|Waters||12233 Park||Oak Park||IL||60632||708 4444444||5/9/2000||
(first initial of your last name
followed by three numbers)
|your last name||add
subscriptions for yourself
|make up inf||ormation for e
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:
- 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”.
- 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”.
- 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”.
- 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”.
- 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:
- 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.
- 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
- Submit the MediaWorldZ.accdb database file to D2L | Submissions | Individual #3b – Database
Part H. Answer concept questions
- Choose D2L | Submissions | Individual 3b – Concepts
- Download and save the LSP 121 Ind Assign 3b Concepts Spring 2020 Word file attached to that submission folder.
- Update the Word File with your answers to the concept questions.
- 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
- Create a zip (compressed) folder. You may give it any name you wish.
- Copy your MS-Access database file into the zip file.
- Copy your MS-Word file (from H. above) into the zip file.
- Submit the zip file to D2L | Submissions | Individual #3b. – Zip – EC
Page 9 of 9