WasteNot Recycling picks up recyclables from homeowners in Boulder, Colorado. Ne
ID: 3760134 • Letter: W
Question
WasteNot Recycling picks up recyclables from homeowners in Boulder, Colorado. Neighborhoods
subscribe to the service so that pickup is cost-effective. WasteNot provides special
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 develop
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 Last First ZIP First
ID Name Name Street City State Code Phone Pickup
1 Wagoner Sam 5480 Alpine Boulder CO 80308 (303) 161- 05/25/2004
Street 5545
2 Calahan Eliza 2140 Edgewood Boulder CO 80308 (303) 886- 05/25/2004
Avenue 6003
3 Lake James 701 Eastman Boulder CO 80308 (303) 562- 08/25/2005
Road 4499
4 Meadows Sara Pond Hill Drive Boulder CO 80308 (303) 792- 02/28/2004
3646
20 Smith Alto 114 Lexington Boulder CO 80308 (303) 838- 06/02/2004
Street 7111
64 Monarch Shiela 431 Phillips Lane Boulder CO 80308 (303) 352- 07/17/2005
4847
65 Guo Amy 1935 Snow Boulder CO 80308 (303) 555- 05/19/2005
Avenue 6731
80 Rivera Juan 482 Weston Boulder CO 80308 (303) 815- 12/28/2004
Avenue 2456
85 Williams Max 230 Southpark Boulder CO 80308 (303) 333- 07/19/2003
Circle 0000
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, fi elds, data types, and primary key(s) for the database.
3. Create the relationship(s) needed between the tables.
4. Add two additional customer records, one with your name and one with a friend's name.
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).
5. Populate the database with the data provided above.
Explanation / Answer
Table Customer (Customer ID, Last Name, First Name, Street, City, State, ZIP Code, Phone, First Pickup)
Table Customer Record ( Customer ID,Srvc Date, Weight Paper, Weight other)
In the given table,
In addition, the Customer ID of the Customer Record table is the foreign key that references the Customer ID field of the Customer table.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.