spss
LSP121 – Individual Assignment #3 – Database Design and Development
Page 1 of 7
Database for DrD Cable Services This assignment will give you practice in designing a database based on a specification and entering that database into MS-Access. Be sure to read the entire assignment/specification before you begin work on the design and development of the database.
Part A. Document Your Design After reading the entire assignment/specification, develop a database design. Document your design by preparing a drawing of the database as demonstrated in class. This drawing is for your use and you do not need to submit it for evaluation. 1. High-level description of user requirements DrD Cable Services needs to track cable TV customer information, such as address, service level, and phone number. DrD Cable Services assigns each customer a unique identification code. 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. 2. Details on the information to be stored in the database
a. Customer
Data we must keep for each customer:
• Customer ID (assigned by DrD Cable Services) • Last Name • Street Address • City • State • Zip Code • Phone Number • Service Start Date • Service Level - value will be Good, Better, or Best. Create a lookup for this field based on a list of those
three values
• Number of devices leased to this customer
LSP121 – Individual Assignment #3 – Database Design and Development
Page 2 of 7
b. Customer_Premium_Channel_Sub A customer can subscribe to any number of premium channels. Data we must keep for each customer’s premium channel subscriptions: The following two fields must be saved for each premium channel the customer subscribes to:
• Premium_Channel_Name name of a Premium Channel that the customer subscribes to. (Setup a lookup for this field based on a reference table (Premium_Channel_Reference) that contains the name of each premium channel. (see 2.c.below))
• Subscription_Date date that the customer established the subscription to this premium channel
Other fields may need to be added to the table to support the database design.
c. Premium_Channel_Name_Reference
The purpose of this table is to serve as a source for the look-up for the Premium_Channel_Name field on the Customer_Premium_Channel_Sub table. Each record on this table should contain one field (Premium_Channel_Name) which is the primary key of the table.
d. Relationship There should be a 1!n (one-to-many) relationship between the Customer table and the Customer_Premium_Channel_Sub table. It should have referential integrity.
LSP121 – Individual Assignment #3 – Database Design and Development
Page 3 of 7
Part B. Build your database using MS-Access.
Use MS-Access to enter the database design you have just documented.
1. Review the drawing you created to document your design (Part A.). 2. Create a new database in MS-Access. Name it DrDsCableCo.accdb
3. Add the tables, fields, primary keys, look-ups, and relationships you have documented in your drawing.
(Note: Before you add the look-up for the Premium_Channel_Name field on the
Customer_Premium_Channel_Sub table, use Datasheet view to add a record to the Premium_Channel_Name_Reference table for each premium channel name listed below.)
Premium channel names:
• HBO • Showtime • Food • MLB • Starz • NBA • Petz • BTN
Part C. Add a form and 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. Name the Customer form Customer Data Entry. Name the Customer_Premium_Channel_Sub sub-form Customer Premium Channel Data Entry
LSP121 – Individual Assignment #3 – Database Design and Development
Page 4 of 7
Part D. Use the form and sub-form you have created to enter the customer and customer premium channel subscription data listed below:
Enter the following data for eleven customers into the database using the form and sub-form you created. Do not use datasheet view for this data entry. Do not attempt to import this data….use your form and sub-form
Customer ID
Last Name Street
Address City State
Zip Code
Phone Number
Start Date Servic
e Level
Number of
Devices Leased
Premium Channel Name And Subscription Date
for each
Subscription
S023 Sanchez 3382
Armitage Riverside IL 60546
773 999- 8888
4/5/2002 Better
2
Petz 1/1/2003 Showtime 2/1/2004
J037 Jacobs 5665 Wilbur Lisle IL 60133 847 777-
5555 9/1/1997 Best
3
HBO
1/1/2003 BTN
3/1/2004 Food
4/1/2005
LSP121 – Individual Assignment #3 – Database Design and Development
Page 5 of 7
C046 Chen 342 Oak Park Ridge
IL 60032 847 222-
3333 4/5/2007 Good
1
Petz
1/1/2008 HBO
1/1/2008
S078 Shah 5544 Susan Oak Park IL 60631 708 111-
9999 1/1/2000 Better
4
HBO 1/1/2003
MLB 1/1/2004 Showtime 2/1/2005
NBA 1/31/2008
F046 Fenski 157 Fargo Riverside IL 60546 312 444-
7777 1/7/1997 Better
5
HBO
1/1/1998 MLB
1/1/1999 Showtime 2/1/2004
NBA 1/31/2003
BTN 1/10/2007
Petz 1/1/1998
P094 Purcel 4322
Orchard Ct Des
Plaines IL 60061
847 777- 2222
5/1/1997 Good
2
Showtime 1/1/2004
HBO 1/1/2004
LSP121 – Individual Assignment #3 – Database Design and Development
Page 6 of 7
R123 Roberto 601 Stewart Lisle IL 60133 773 333-
7777 10/1/1995 Best
2
Starz
12/31/2000 Food
1/1/2007 Petz
2/3/1997
S245 Stein 565 Packard Riverside IL 60546 708 999-
0001 4/1/2005 Better
1
HBO
7/1/2006
L643 Leninger 100 Ford Lisle IL 60133 847 888-
5555 12/10/1999 Best
2
Starz
9/1/2000 HBO
10/1/2005 Food
8/1/2000
W734
Walters 12233 Park Oak Park IL 60632 708 222-
6666 5/9/2000 Good
2
Showtime 1/1/2004
BTN 1/31/2005
Food 2/01/2005
M152 Martin 304 Bell Berwyn IL 60402 708-788-
6644 1/1/1997 Best
1
Showtime 1/1/1998
LSP121 – Individual Assignment #3 – Database Design and Development
Page 7 of 7
Part E. Add Queries to your MS-Access database Add the following queries to your MS-Access database: 1. Customers in Riverside. List Last Name, Street Address, City, State, Zip Code, and Phone_Number for all customers living in Riverside. Sort alphabetically by Last_Name. Name the query Customers_in_Riverside. 2. Food Subscribers. List Last Name, Street Address, City, State, Zip Code, and Subscription Date for all customers who subscribe to Food. Sort the query alphabetically by City. Name the query Food Subscribers by City. 3. Customers with HBO or Showtime. List Last Name, Zip Code, Phone_Number, Premium Channel Name, and Subscription Date for customers who subscribe to either HBO or Showtime. Sort the query alphabetically by Premium_Channel_Name. Name the query Customers_with_HBO_or_Showtime 4. Customers Before 1998. List Customer_ID, Last Name, City, Zip Code, Number of Devices Leased, and Service Start Date for those customers that have a service start date earlier than 1/1/1998. Sort the results by Service Start Date from newest to oldest. Name the query Customers_Before_1998
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, Service Level, each Premium Channel they have subscribed to, and Subscription Date for each subscription. Sort by Last name, then by Premium_Channel Name. Show the Last Name and Service Level only once for each Customer. Name the report All Customers with Premium Channel Subscriptions.
2. Customers with HBO or Showtime. Develop a report that lists Last Name, Zip Code, Phone Number, Premium Channel Name, and Subscription Date for those customers with either HBO or Showtime. Sort the report alphabetically by Last Name. The Last Name, Zip Code, and Phone Number for each customer should only be printed once per customer. Sort the report so that HBO appears before Showtime if a customer subscribes to both premium channels. Name the report Customers with HBO or Showtime. (Use the query answerset from E.3 as input to this report.)
Part G. Zip the MS-Access database and submit it to the assignment submission folder. 1. Close your MS-Access database session 2. Create a zip(compressed) file. 3. Copy your Access database file into the zip file. 4. Submit the zip file to D2L | Submissions | Individual Assignment #3.