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.