CSCI A114 / INFO I111
Assignment One

First semester 2000-2001

Due date
At the end of lab 3.
Late policy
No late policy. You need to turn this on time.
Work policy
Working in groups is accepted but you need to turn in your own version of the assignment.
Task
Explained in detail below.
Grading
The AI will provide you with feedback in a week. Grades will be posted on-line.

Sidewalk Scrapers is a snow removal service that was started by two high school juniors looking for ways to earn money for college. Sidewalk Scrapers provides snow removal to residences and businesses in a city that receives lots of snow during the winter months. The business has expanded rapidly and now employs high school and college students to shovel sidewalks, steps, and driveways. Sidewalk Scrapers has a database that keeps track of its workers and customers. The database has two tables. The Customer table contains data on the customers who use the services of Sidewalk Scrapers. The Worker table contains data on the students employed by Sidewalk Scrapers. The structure and data are shown below for the Customer and Worker tables.

Structure of Customer table
Field Name Data Type Field Size Primary Key? Description
Customer Number Text 4 Yes Customer Number (Primary Key)
Name Text 20 Customer Name
Address Text 15 Street Address
Telephone Text 8 Telephone number (999-9999 Version)
Balance Currency Amount Owed by Customer
Worker ID Text 2 ID of Customer's Worker

Data for Customer table: 

AL25,   "Arders, Lars" ,   205 Norton   ,   555-2050,   $45.00,   03
AT43,   Atari Cleaners ,   147 Main     ,   555-7410,   $80.00,   10
BH81,   "Bond, Laura"  ,   407 Scott    ,   555-0704,    $0.00,   10
CH65,   Chan's Bootery ,   154 Main     ,   555-0504,   $70.00,   14
CI05,   Cinco Gallery  ,   304 Secord   ,   555-1304,   $29.00,   03
JB51,   "Jordach, Ben" ,   203 Norton   ,   555-0213,   $60.00,   10
LK44,   "Lee, Kim"     ,   605 Thurston ,   555-5061,    $0.00,   10
MD60,   "Martinez, Dan",   410 Orange   ,   555-4110,   $95.00,   03
ME02,   Meat Shoppe    ,    75 Edgewater,   555-7557,    $0.00,   14
ST21,   Styling Saloon ,   406 Secord   ,   555-6454,   $40.00,   10
Structure of Worker table
Field Name Data Type Field Size Primary Key? Description
Worker ID Text 2 Yes Worker Identification Number (Primary Key)
Last Name Text 15 Last Name of Worker
First Name Text 10 First Name of Worker
Address Text 20 Street Address
Telephone Test 8 Telephone Number (999-9999 Version)
Pay Rate Currency Hourly Pay Rate

Data for Worker table: 

03,  Carter ,  Chris,  467 Norton  ,  555-7641,  $4.50 
10,  Lau    ,  John ,   56 Parker  ,  555-5656,  $4.25
14,  Sanchez,  Elena,  211 Thurston,  555-1122,  $4.75
Perform the following tasks:

  1. Create the Sidewalk Scrapers database and save it on disk.

  2. Open the Worker table in Datasheet view and add yourself to the table. Your Worker ID should be 07. Then close the Worker table.

  3. Open the Worker table again. Notice that the record you just added has been moved. It is no longer at the end of the table. The records are in order by the primary key, Worker ID.

  4. Print the Worker table.

  5. Open the Customer table.

  6. Change the Worker ID for customer LK44 to 07.

  7. Print the Customer table.

  8. Create a report called "Balance Due Report" that lists all the customers, sorted by their customer number, and listed with their name and current balance.

  9. Print the report and turn it in with your disk.

Last updated: August 21, 2000 by Adrian German for A114/I111