1. Start with the Excel workbook (finalex.xls) as found on my website a. There are three separate sheets in the workbook (Business_Info, Order_Info, Salesmen) i. Create your own table design based on the Excel data, and include any and all relationships (joins). Apply referential integrity rules wherever appropriate ii. Import the data from Excel to your database (Access will work fine) to populate tables (This part is all set)

2. Use the Chen technique and illustrate the E-R diagram for the tables you created a. Note: It may be necessary to change the data composition due to normalization issues, if you change the composition detail how and why; (you may find it necessary to create additional tables or fields)

3. Create the appropriate SQL or use Access Queries to complete the following:

a. Find businesses and their orders sorted by Salesman Last Name. Include the following attributes: Business Number, Business Name, Order Number, Billing Date, Salesman Last Name, and Commission Amount (derived attribute based upon Invoice Amount and Commission Rate).

b. For all salesmen with current customers, create a list of each business (one record per business) with unpaid orders. Include the following attributes: Salesman Number, Salesman Full Name (derived from Salesman Last Name and Salesman First Name), Business Number, Business Name, and Owner Name.

c. Show Owners with unpaid bills (an order is classified as unpaid if the Paid indicator is = No), listing the Owner Name, Owner Phone, Business Number, Business Name, the amount Due, and a new amount Due (original amount Due with a 7% finance charge added), Sorted by New amount Due in descending order.

d. Create a parameter based query to accept a region code and display the State, Salesman Number, Salesman Full Name, and all businesses in that region.

4. Create a user interface to insert/maintain business information and an additional one to insert/maintain order information, or a single interface that combines both if you wish. This will involve the use of a form or forms.

5. Produce the following reports:

a. List all businesses for a selected salesman (this is a good use of a parameter based query) include: Salesman Number, Salesman First Name, Salesman Last Name, Business Name, and Business Phone.

b. Create a report listing the commission earned by each salesman for each order that has been paid. Group the report by salesman number and sort by commission amount in descending order within salesman. Include the following attributes: Region Code, State, Salesman Number, Salesman Full Name, Business Name, Invoice amount, Commission Rate, and Commission amount. In addition include the total commission earned for each salesman and the total commission earned for the report.

c. Generate labels for a promotion targeting all businesses that have spent more than $2,000. Address the label to the Business Name and Address and include a line Attn: . Sort the labels by Business Name within Zip Code. Use Avery #C2163 label format, sheet fed.

6. Explain why Access, although a type of DBMS, is not the best choice for a multiuser, distributed database for use by many users simultaneously.

1 BusinessNum BusinessName 60) 792-3500 Pond Hiil Road (603) 643-8888 810 Brand Avenue 860) 792-3546 (603) 643-8821 Meadows Restaurant Boston MA 03102 Ms. Doris Reaume Restaurant (603) 966-8600 8408 E. Fletcher Grand River Restaurant 603) 729-5300 37 Queue Highway Concord NH 03303 Mr. John Rohrs (603 729-5364 PortlandME 04101 Ms. Michele(207) 111-9148 (207) 895-2041 Sprinfield MA 02905Mr. Donald Bench (401) 111-3260 128 207) 111-9000 95 North Bay (207) 895-2000 15365 Old Bedford (401) 111-3200 2874 Western 207) 643-4600 2840 Cascade Road Portland Burlington VT 04101 Mr. Ronald Florentin e Restaurante ME 04101 Ms. Janice Cleo's Downtown (203) 888-2000 4090 Division Street New Haven CT 06513 Ms. Joan Hoffiman (203) 888-2046 Bentham's Riverfront 401) 792-8000 1366 36th Street Providence RI 02905 Mr. Joe Markovicz (401) 792-8040 Sullivan's Restaurant &(207)575-6000 1935 Snow Street SE Bngor ME 04401 Ms. Dawn Parker (207) 575-6731 Wagon Train Restaurant (207) 111-5555 5480 Alpine Lane BangorME 04401 Mr. Carl Seaver(207) 111-5545 (207) 889-6000 2140 Edgeood Bngor ME 04401 Ms.Deborah Wolfe (207) 889-6003 (401) 532-4499 KitteryME 03904 Mr. James Cowan (207) 888-4812 Best Bet Restaurant(203) 415-7200 56 Four Mile Road HoldenMA 06514 Ms. Rebecca Van (203) 415-7294 207) 620-4444 44 Tower Lane Burlington VT 03904-Ms. Jean Brooks _ (207) 620-4431 (207) 888-7111 22 Main Street Bor ME 0401 Mr. James Cowan 207) 888-4812 Ir. Janosti Petoti (860) 679-568 (207) 422-8900 1289 Thames Street Burlington VT 03904 Ms. Jane Horgan (207) 385-0973 Uxbridge MA 06109 Mr. Jeffrey Hersha (860) 717-9855 Ms. Jennifer Lewis 860) 273-9465 New Haven CT 06515 Mr. Walter Reed (203) 124-1824 (401) 532-4444 701 Bagley Street Providence RI 02905 Mr. Donald Bay Pointe Restaurant (860) 679-5600 11456 Marsh Road Hartford CT 06108 Cheshire Restaurant 860) 717-9800 8200 Baldwin Brandywine Restaurant (203) 124-1800 150 Hall Road Max (860) 273-9000 3333 Bradford Farms Hartford CT 0611 cits Res (101) 333 (XXX) 23() Sonih 1% ilips war k k RT (mt86 Mls. nAthara F4 klowa (401) 333 0291 RI 02914 Mr. Glbert Scholten (401) 332-2681 (401)336 000 33)Wkl KMs. i: X (401) 336 1612 ca ut - 401) 332-2?00 3915 Hawthorne East (401) 334-3980 (203) 434-4100 1925 Bridge Street Meriden CT 06451 Mr. Gregory Olson (203) 434-4192 (401) 339-1200 5013 North Cliff Woonsocke RI02895 Ms. Bridgit Feeney (401) 339-1264 401) 334-3900 109 East Monroe Warwick RI 02886 Ms. Tara 112 Ms. Pam Leonard (8 Brittany Restaurant (203) 331-7000 6 Bayberry Poimte Meriden CT 06451 Mr. Ken Hodge (203) 331 Four Star Steakhouse Prime Cut Steakhouse 34 35 36 (401) 336-0900 2819 East 10 Street Warwick 401) 336-2300 4005 West Madison Cranston 779 First Edition Restaurant (860) 461-8899 3509 Garfield (860) 762-9100 10 Sycamore Street Norwich CT06365 Ms. Curtis Haiar (860) 762-9144 (603) 816-2400 4004 West 41st Nashua NH 03060 Mr. Clifford Merritt (603 816-2456 Embers Restaurant At 500 Business_Info Order_Info Salesmen... 1 BusinessNum BusinessName 60) 792-3500 Pond Hiil Road (603) 643-8888 810 Brand Avenue 860) 792-3546 (603) 643-8821 Meadows Restaurant Boston MA 03102 Ms. Doris Reaume Restaurant (603) 966-8600 8408 E. Fletcher Grand River Restaurant 603) 729-5300 37 Queue Highway Concord NH 03303 Mr. John Rohrs (603 729-5364 PortlandME 04101 Ms. Michele(207) 111-9148 (207) 895-2041 Sprinfield MA 02905Mr. Donald Bench (401) 111-3260 128 207) 111-9000 95 North Bay (207) 895-2000 15365 Old Bedford (401) 111-3200 2874 Western 207) 643-4600 2840 Cascade Road Portland Burlington VT 04101 Mr. Ronald Florentin e Restaurante ME 04101 Ms. Janice Cleo's Downtown (203) 888-2000 4090 Division Street New Haven CT 06513 Ms. Joan Hoffiman (203) 888-2046 Bentham's Riverfront 401) 792-8000 1366 36th Street Providence RI 02905 Mr. Joe Markovicz (401) 792-8040 Sullivan's Restaurant &(207)575-6000 1935 Snow Street SE Bngor ME 04401 Ms. Dawn Parker (207) 575-6731 Wagon Train Restaurant (207) 111-5555 5480 Alpine Lane BangorME 04401 Mr. Carl Seaver(207) 111-5545 (207) 889-6000 2140 Edgeood Bngor ME 04401 Ms.Deborah Wolfe (207) 889-6003 (401) 532-4499 KitteryME 03904 Mr. James Cowan (207) 888-4812 Best Bet Restaurant(203) 415-7200 56 Four Mile Road HoldenMA 06514 Ms. Rebecca Van (203) 415-7294 207) 620-4444 44 Tower Lane Burlington VT 03904-Ms. Jean Brooks _ (207) 620-4431 (207) 888-7111 22 Main Street Bor ME 0401 Mr. James Cowan 207) 888-4812 Ir. Janosti Petoti (860) 679-568 (207) 422-8900 1289 Thames Street Burlington VT 03904 Ms. Jane Horgan (207) 385-0973 Uxbridge MA 06109 Mr. Jeffrey Hersha (860) 717-9855 Ms. Jennifer Lewis 860) 273-9465 New Haven CT 06515 Mr. Walter Reed (203) 124-1824 (401) 532-4444 701 Bagley Street Providence RI 02905 Mr. Donald Bay Pointe Restaurant (860) 679-5600 11456 Marsh Road Hartford CT 06108 Cheshire Restaurant 860) 717-9800 8200 Baldwin Brandywine Restaurant (203) 124-1800 150 Hall Road Max (860) 273-9000 3333 Bradford Farms Hartford CT 0611 cits Res (101) 333 (XXX) 23() Sonih 1% ilips war k k RT (mt86 Mls. nAthara F4 klowa (401) 333 0291 RI 02914 Mr. Glbert Scholten (401) 332-2681 (401)336 000 33)Wkl KMs. i: X (401) 336 1612 ca ut - 401) 332-2?00 3915 Hawthorne East (401) 334-3980 (203) 434-4100 1925 Bridge Street Meriden CT 06451 Mr. Gregory Olson (203) 434-4192 (401) 339-1200 5013 North Cliff Woonsocke RI02895 Ms. Bridgit Feeney (401) 339-1264 401) 334-3900 109 East Monroe Warwick RI 02886 Ms. Tara 112 Ms. Pam Leonard (8 Brittany Restaurant (203) 331-7000 6 Bayberry Poimte Meriden CT 06451 Mr. Ken Hodge (203) 331 Four Star Steakhouse Prime Cut Steakhouse 34 35 36 (401) 336-0900 2819 East 10 Street Warwick 401) 336-2300 4005 West Madison Cranston 779 First Edition Restaurant (860) 461-8899 3509 Garfield (860) 762-9100 10 Sycamore Street Norwich CT06365 Ms. Curtis Haiar (860) 762-9144 (603) 816-2400 4004 West 41st Nashua NH 03060 Mr. Clifford Merritt (603 816-2456 Embers Restaurant At 500 Business_Info Order_Info Salesmen