Name: ResEc112 Normalization QuizMay3, 2007

This database is for a fair trade import business of Guatemala artisan-produced textiles. It will need to store data about the artisans that make the textiles, the cooperatives that handle the business transactions for each artisan, the products that the artisans make, the retail customers that buy the products from us, and their orders. The importers are trying to give the artisans as much of the selling price of the products, so instead of the 100% retail markup over cost typical in this business, they are trying to get by with a standard 50% markup on all items, so the database is particularly important so they can run an efficient organization.

Products Table (information about each product we carry)

Prod id / product name / artisan ID / Type / Our Cost / Retail Price / description
1 / Pacific Blanket / 1 / Blanket / 120 / 180 / Luxurious and soft, this blanket is the …
2 / Coyolate Shawl / 1 / Shawl / 60 / 90 / A versatile garment equally at home on an …
3 / Earthen Hues Bedspread / 2 / Blanket / 100 / 150 / Rich earth hues and tradition design make …
4 / Naranjo Scarf / 3 / Scarf / 30 / 45 / Silky and soft, the select alpaca fibers that …
5 / Celestial Throw / 1 / Blanket / 80 / 120 / Traditional culture meets modern aesthetics…

Customers Table (information about each of our customers)

customer id / First name / Middle name / Last name / Prefix / Suffix / Address
1 / Jane / E. / Smith / Mrs. / 4 Oak Lane, Amherst, CA 91321
2 / James / Robert / Campbell / Mr. / Jr. / 1 Main St., Hadley, MT83884
3 / Sarah / Sampson / Dr. / 234 Commerce St, Timly, OH 12843
4 / Julie / Tatro / Ms. / 3 Almond Dr., Wrisley, AZ 38234
7 / Thomas / R. / Miller / Mr. / Esq. / 10 Center St., Taos, NM83772

Orders Table (information about customer orders)

Order id / Cust id / Cust name /
Order Date / Payment method / Shipping method / Invoice Number / Order method
1001 / 3 / Sarah Sampson / 10/15/06 / MasterCard / Ground / 100032 / Phone
1002 / 3 / Sarah Sampson / 11/1/06 / MasterCard / Ground / 100033 / Internet
1003 / 1 / Jane E. Smith / 11/18/06 / Visa / 2-day air / 100034 / Internet
1004 / 4 / Julie Tatro / 11/18/06 / Discover / Ground / 100035 / Internet
1005 / 3 / Sarah Sampson / 12/1/06 / Mastercard / Ground / 100036 / Internet

1)What is the primary key for the products table?

2)What is the primary key for the Orders Table?

3)What is wrong or potentially wrong with the fields (not the data) that are in the three tables above, and explain why or what should be done to fix it?

4)We havean orders table started above to keep track of orders that each customer places. How would we store the data to keep track of the actual items that the customer is ordering, how many of each one, and when each item shipped?