Your instructors mother recently retired from a long career with the government. To keep herself busy, she began selling her homemade candy to relatives and friends. The candy was so delicious, word quickly spread, and the hobby became a business called The Gourmet Chocolate Company. Until now, all recordkeeping has been done by hand. Now your instructors mother wants help to create a database for her company. Here is what she wrote in a recent letter.

Dear Students,

Years ago, I began making chocolates for holiday gifts. It started out as just a hobby; I would give boxes of my homemade truffles or chocolate-covered cherries to friends and relatives at Christmas and for birthdays. Everyone always raved about how delicious they were and persuaded me to go into business for myself. I started a small mail-order business to sell specialty chocolates.

Initially, my thoughts were to keep the business small, and do all the paperwork by hand (filling orders, sending bills, computing statistics on sales, etc.). Well, luckily for me, my business has grown rapidly, and now I have a small factory operation and employ five workers. I need your help setting up a computer database that can help me to manage my growing business.

I would like the database to do several things. First, Id like it to record the details of incoming orders. When customers phone our company, they order one or more of our products by product number, in order quantities per pound. (Everything is priced per poundthere are no quantities less than one pound.) It is noted on a customers record whether he or she wants to receive monthly catalogs. The catalog mailing is quite expensive, so I send catalogs to those who request them. (I also need a mailing list of those who want the monthly catalogs.)

Id also like to know which items are my most popular. Each month Id like to generate a report that shows sales volume by item. Id appreciate it if the report could rank the items from highest-volume sales to lowest.

Finally, Id like to generate the bill to accompany each customers order. This information should appear on each customers bill: the customers name and address, the date of the order, item(s) ordered, cost of each item, and a grand total due.

I hope you can help me set up my system.

Thank you.

Anne Bean

ASSIGNMENT I CREATING THE DATABASE DESIGN

In this assignment, you will design your database tables on paper. Pay close attention to their logic and structure. Do not start your Access code (Assignment 2) before getting feedback from your instructor on Assignment 1.

Design the tables youll need on paper, avoiding data redundancy. Do not create a field if it could be created by calculated field in a Query.

Youll need a transaction table. If you avoid duplicating data contained within other tables, the transaction table should be pretty sparse.

One of your tables should contain a logical (yes/no) field.

Youll need to note the date on which each order is shipped.

Create your tables using the Table facility of your word processor. Your word-processed tables should look something like the table that follows.

Table name

Field name Data type (text, numeric, currency, etc.)

You must mark the appropriate key field(s). You can denote a key field by an asterisk (*) next to the field name. Keep in mind that some tabLes need a compound field key to uniquely identify a record within a table.