Orders exercise

The mail order business is faced with a new problem. You would like to be able to track orders by customer, by product, and by date. Specifically, you have become aware that some of your suppliers may not be able to deliver when they promised, and you want to know what the impact of backorders for selected products will be. To know this, you will need to know which customers ordered what products, and when they placed the orders. You will also want to know how best to contact the customers by telephone, if necessary.

You know that each customer may order one or more products at a time. You also have come to understand that most customers have more than one telephone number. Including home, office, fax, and mobile telephones, some customers may have four or more numbers.

These details pose a new problem: how should you best track this data in your database? If you try to put it all in one table, you will have one enormous table with lots of blank space for telephone numbers (surely not every customer has four phone numbers) and repeated addresses for each product in each order (wouldn't it be easier to maintain one copy of the address no matter how many products were ordered?).

Exercise overview

This exercise focuses on table design with Access. It provides experience with the end result of data structure analysis as performed by entity-relationship diagramming. The exercise deals with data entities (e.g., customers, products, orders), repeating groups (e.g., telephone numbers as an attribute of Customers), many-to-many relationships (e.g., customers to products), and "linking" tables that resolve such relationships. It also introduces the concepts of key fields, counter variables, and Relationships as specified within Access.

Learning objectives/minimum performance criteria:

DSD Concepts	Relational Database Concepts	Access, PowerPoint, Word
Entities and relationships. Repeating groups. E-R diagraming.	1:M, M:1, M:M relationships. Key fields and foreign keys. Using linking tables.	Counters and key fields. Specifying relationships. Using relationships to define tables. Calculated fields. E-R diagrams in Powerpoint transferred to and rescaled in Word.

Instructions for Orders Exercise

Build an Access database that defines tables for Customers and Products. Customers have an envelope name, salutation, address (street, city, state, zip), and an indeterminate number of telephone numbers. Products have at minimum a product name and a price. Since some product names might be very similar, use a unique Product ID number to identify products. Shipping charges are expected to be 10% of product price.

Products (and prices) include Raccoon coats, $3,000.00 ea., Survival dry suits, $3,000.00 ea., Crampons, $150.00 a pair, Mountain tents, $200.00 ea., and Stanford (University) pennants, $30.00 ea.

You still have two customers, Smith and Jones. Both have multiple telephone numbers. Jones has home ((617) 484-2133) and office ((617) 726-4433) numbers. Smith has home ((310) 455-4678), office ((310) 566-9984), and fax numbers ((310) 456-3442).

Jones has ordered 1 raccoon coat and 5 Stanford pennants. Smith has ordered 1 survival dry suit, 1 mountain tent, and 1 pair of crampons. Jones placed his orders on January 20, 1996. Smith placed her orders for the dry suit and crampons on February 1, 1996. She ordered the mountain tent on February 2, 1996.

Design database tables and queries that enable you to know (1) when orders were placed, (2) what customers have ordered what products, and (3) what shipping charges were for each product.

Next session, hand in an electronic copy and printout of a Word file that

1. Explains your table design, including how you handled data redundancy, data integrity, many-to-many relationships, and key fields.
2. Includes an entity-relationship diagram showing the entities, attributes (including unique ids and repeating groups, if any), and relationships you have identified in this example (including the Orders entity).
3. I suggest that you use PowerPoint to make this diagram, then copy the diagram into Word and rescale it to an appropriate size. If you have great difficulty coming to campus computers during the week and do not have a way to produce E-R diagrams in PowerPoint, in Word, or on any other presentation graphics package (such as Freelance), I will accept hand-drawn ERDs.
4. Includes a copy of the results of the query that you developed to show orders, dates, prices, and shipping costs, sorted by date and including the customer's last name for each product ordered.
5. Copy these results into Word table. One way to do this is by (a) selecting the query in the database window in Access, (b) selecting Copy (by toolbar or menu or Ctrl-c), (c) switching to Word (Alt-Tab once Word is open), (d) pasting the clipboard contents into Word (use the Paste Special command and choose Unformatted text, and (e) converting tabbed text to a table by using the Convert text to table command on the Table menu. You may have to add a section to your Word document that is landscape format. Be sure to save your file frequently.
6. Includes an appropriately scaled version of the Relationships window from Access, showing the relationships you defined between your tables.

Please format your Word file with your name and telephone number in the upper right hand corner of each page header.