Question

Database Intro

Salesmen Sheet

CommRate SalesRegion State SalesNum SalesName 100 James King 110 Arthur Dolan 200 Samantha Rose 300 John Miller 400 Susan Walsh 500 Carlo Hernandez 600 Mark Russell 700 David Lewis CT MA VT 0.301 0.302 0.353 0.204 0.202 0.15 0.15 NH

Order_info Sheet

Business_Info sheet

1.Create your own table design based on the Excel data, and include all relationships (joins). Apply referential integrity rules wherever appropriate.

Import the data from Excel to Access to populate tables:

1. Use the Chen technique and illustrate the E-R diagram for the tables you created

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)

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

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

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

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.
2. 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.
3. 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.

Answer 1

salesmen(s_num, s_fname, s_lname, c_rate, s_region, state)
order(o_num, b_num, paid, invoice_amt, date)
business(b_num, b_name, b_phn, add_region, owner_fnm, owner_lnm owner_phn)
address(add_region, street, city, state, zip)

1. Creating these tables:
CREATE TABLE salesmen(
   s_num INTEGER PRIMARY KEY,
   s_fname VARCHAR,
   s_lname VARCHAR,
   c_rate FLOAT,
   s_region INTEGER,
   state VARCHAR
);

CREATE TABLE address(
   add_region INTEGER PRIMARY KEY,
   street VARCHAR,
   city VARCHAR,
   state VARCHAR,
   zip VARCHAR
);

CREATE TABLE business(
   b_num INTEGER PRIMARY KEY,
   b_name VARCHAR,
   b_phn INTEGER(10),
   add_region INTEGER,
   owner_fnm VARCHAR,
   owner_lnm VARCHAR,
   owner_phn INTEGER(10),
   FOREIGN KEY add_region REFERENCES address(add_region)
);

CREATE TABLE order(
   o_num INTEGER PRIMARY KEY,
   b_num INTEGER NOT NULL,
   paid ENUM('Yes', 'No') NOT NULL,
   invoice_amt INTEGER NOT NULL,
   date DATE,
   FOREIGN KEY b_num REFERENCES business(b_num)
);

2. Queries:
-> SELECT o.b_num, b.bname, o.o_num, o.date, s.s_lname, o.invoice_amt, s.c_rate, s.c_rate*o.invoice_amt as amt
   FROM business b, order o, salesmen s, address a
   WHERE o.b_num = b.b_num AND b.add_region = a.add_region AND s.state = a.state ORDER BY s_lname

-> SELECT UNIQUE s.s_num, CONCAT(s.s_fname, s.s_lname) as s_name, b.b_num, b.b_name, b.owner_nm
   FROM business b, order o, salesmen s, address a
   WHERE o.b_num = b.b_num AND b.add_region = a.add_region AND s.state = a.state AND o.paid = 'No'