Data design and managment

Database Design and Management

Start with the Excel workbook (finalex.xls) as found in Blackboard

1. 1. 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

1. Use the Chen technique and illustrate the E-R diagram for the tables you created
   1. 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)

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

1. 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).

1. 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.

1. 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.

1. 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.

1. 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.

1. Produce the following reports:

1. 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.

1. 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.

1. 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: <Owner Name>”.Sort the labels by Business Name within Zip Code.Use Avery #C2163 label format, sheet fed.

1. Explain why Access, although a type of DBMS, is not the best choice for a multi-user, distributed database for use by many users simultaneously.