Question: Tutorial Assignment TA4: Setting up a Multi-table Database Overview You will essentially complete...

Tutorial Assignment TA4: Setting up a Multi-table Database

Overview

You will essentially complete Problem #1 at the end of Plug-in T-6 ("WasteNot Recycling"), with some specified additions/modifications. You will also attempt some operations on the database you will create in this assignment, make some observations, & then report these observations in an accompanying document file submission. You will then upload both your database file and your observations to the appropriate dropbox.

Instructions:

As soon as you have opened a new database file in Access in order to create this assignment, you must save and name this file: "TA4_Lastname_Firstname" where Lastname is your last name and Firstname is your first name.

After completing parts 1 through 3 of this assignment, you should also:

1. Add two additional customer records, one with your name and one with a friend's name.

2. Add an additional 14 records to the Customer Service record table. These records should indicate service dates in January and February of 2007. They should also be related to existing customers in the Customer table (including the customer record for you and your friend).

3. When you create the relationship between the 2 tables (i.e. part 4 of this problem), please be sure to check the checkbox for "Enforce Referential Integrity" as well as the checkboxes for "Cascade Update Related Fields" and "Cascade Delete Related records". If you do not check these boxes as specified you may come up with radically different answers to the questions.

You will now make some observations based on the following questions:

1. What happens when you try to enter a record into the Customer Service records table for which there is no corresponding record in the Customer table?

2. What happens when you try to delete a record from the Customer records table for which there is at least one corresponding record in the Customer Service records table?

3. What happens when you try to change the value of "Customer ID" in the Customer table for which there IS at least one corresponding record in the Customer Service Records table?

Upon completion, upload your database file and your observations document to the appropriate dropbox for this assignment. Your Observations document file should also be named "SU_Lastname_Firstname" where Lastname is your last name and Firstname is your first name.

How this assignment is graded

Grade

Criteria

100

The database file was set up correctly; All required data was entered into it and the observations made were correct.

90

The database was not set up correctly OR All required data was not entered .. ... but the observations were correct in respect to the database as it was set up (e.g. The deletion did not work, but this was because the "Cascade Delete" was not set correctly).

80

The database was not set up correctly AND All required data was not entered OR some of the observations were not correct in respect to the database as it was set up (e.g. The deletion did not work, but this was because the "Cascade Delete" was not set correctly).

70

The database was not set up correctly AND All required data was not entered AND some of the observations were not correct in respect to the database as it was set up (e.g. The deletion did not work, but this was because the "Cascade Delete" was not set correctly) OR the Observations were not correct or not included

60

The assignment submission is seriously deficient (more than the criteria above for 70

MAKING BUSINESS DECISIONS

1. WasteNot Recycling

WasteNot Recycling picks up recyclables from homeowners in Boulder, Colorado. Neigh- borhoods subscribe to the service so that pickup is cost-effective. WasteNot provides spe- cial containers to subscribers for sorting recyclables: a blue container for paper products and a purple container for aluminum, plastic, and glass products.

Subscribers place their recycling containers on the curb for biweekly pickup. Each recycling container is weighed before being emptied. WasteNot drivers carry handheld recording devices used to track each pickup. Subscribers receive quarterly profit-sharing checks based on their contributions. If WasteNot does not make a profit, subscribers are not paid for their recyclables. If WasteNot makes a profit, subscribers share in that profit. WasteNot has asked you to help de- velop a relational database that will effectively track subscribers, using the data downloaded from the drivers devices. WasteNot has provided you with a snapshot of two tables you need to create. The Customer table shown here holds static customer information such as name, address, and phone. The Customer Record table holds data about each recyclable pickup.

Specifically, WasteNot needs you to:

Customer ID	Last Name	First Name	Street	City	State	ZIP Code	Phone	First Pickup
1	Wagoner	Sam	5480 Alpine Street	Boulder	CO	80308	(303) 161- 5545	05/25/2004
2	Calahan	Eliza	2140 Edgewood Avenue	Boulder	CO	80308	(303) 886- 6003	05/25/2004
3	Lake	James	701 Eastman Road	Boulder	CO	80308	(303) 562- 4499	08/25/2005
4	Meadows	Sara	Pond Hill Drive	Boulder	CO	80308	(303) 792- 3646	02/28/2004
20	Smith	Alto	114 Lexington Street	Boulder	CO	80308	(303) 838- 7111	06/02/2004
64	Monarch	Shiela	431 Phillips Lane	Boulder	CO	80308	(303) 352- 4847	07/17/2005
65	Guo	Amy	1935 Snow Avenue	Boulder	CO	80308	(303) 555- 6731	05/19/2005
80	Rivera	Juan	482 Weston Avenue	Boulder	CO	80308	(303) 815- 2456	12/28/2004
85	Williams Max	230 Southpark Circle	Boulder CO 80308	Boulder	CO	80308	(303) 333- 0000	07/19/2003

Customer ID	Srvc Date	Weight Paper	Weight Other
1	11/22/2007	8	15
1	10/15/2007	32	85
1	11/7/2007	12	43
2	11/7/2007	19	0
2	11/22/2007	28	174
3	10/15/2007	5	8
3	11/22/2007	16	32
3	12/4/2007	7	12
20	10/15/2007	18	40
20	11/22/2007	35	60
80	10/15/2007	10	10
80	11/7/2007	9	13
80	11/22/2007	16	18
80	12/4/2007	18	21

1.Create a Microsoft Access database.

2.Create the tables, fields, data types, and primary key(s) for the database.

3.Create the relationship(s) needed between the tables.

4.Populate the database with the data provided above.

1. WasteNot Recycling WasteNot Recycling picks up recyclables from homeowners in Boulder, Colorado. Neigh- borhoods subscribe to the service so that pickup is cost-effective. WasteNot provides spe- cial containers to subscribers for sorting recyclables: a blue container for paper products and a purple container for aluminum, plastic, and glass products Subscribers place their recycling containers on the curb for biweekly pickup. Each recycling container is weighed before being emptied. WasteNot drivers carry handheld recording devices used to track each pickup. Subscribers receive quarterly profit-sharing checks based on their contributions. If WasteNot does not make a profit, subscribers are not paid for their recyclables If WasteNot makes a profit, subscribers share in that profit. WasteNot has asked you to help de- velop a relational database that will effectively track subscribers, using the data downloaded from the drivers' devices. WasteNot has provided you with a snapshot of two tables you need to create The Customer table shown here holds static customer information such as name, address, and phone. The Customer Record table holds data about each recyclable pickup Customer Table Specifically, WasteNot needs you to Customer ID Last Name First Name ZIP First Pickup Street City State Code 80308 (303) 161 05/25/2004 80308(303) 886 05/25/2004 Phone 5480 Alpine Street Wagoner Sam Boulder CO 5545 Calahan CO 2140 Edgewood Avenue Boulder 6003 701 Eastman Road Lake James Boulder CO 80308 (303) 562 08/25/2005 4499 Meadows Sara Pond Hill Drive CO 80308(303) 792 02/28/2004 80308 (303) 838 06/02/2004 80308(303) 352 07/17/2005 80308 (303) 555 05/19/2005 80308(303) 815 12/28/2004 80308 (303) 333 07/19/2003 Boulder 3646 114 Lexington Street 20 Smith Alto Boulder CO 64 Monarch Shiela 431 Phillips Lane Boulder CO 4847 65 Amy 1935 Snow Avenue Boulder CO 6731 482 Weston Avenue 80 Rivera Juan Boulder CO 2456 230 Southpark Circle 85 Williams Max Boulder CO