ISM 4212 Exam 4 & 5 Combined
Situation: A used car lot has cars for sale. Each car has a price, a year, a model, and a make. Cars are sold. Salespersons get commissions on sales. Commission is computed as a percentage of the sale price, but the commission rate is a function of the profit rate ( (cost - sales price) / sales price ) on the sale of the car according to the following table:
Profit Bracket Commission %
Low High
-999% 2% 0
2.01% 5% 1
5.01% 10% 3
10.01% 20% 7
20.01% 999% 12
Information requirements: 1. List the unsold cars in order by make and model. 2. List the unsold cars in descending order by price. 3. List the unsold cars in descending order by year. 4. List the sales in order by salesperson for the last 30 days. 5. List the individual sales for the last 30 days with the commission amount in order by salesperson. 6. List total commission due for each salesperson for the current month. 7. List commission earned by salesperson by month for the past 12 months.
List all of the car information for 1, 2, 3, 4, and 5.
For the above situation: Develop an ERD for a database which supports ONLY the information requirements specifically listed and numbered. Include all of your attributes on the ERD. Denote PKs by underline. Develop SQL Create Table statements for the database design. Develop SQL Select statements for each information requirement. Implement in ACCESS. Have dropdown combo boxes on forms where appropriate. Have a main menu. Use the Select statements as input to the report writer for the reports, but code aggregation functions in your SQL -- do not rely on the report writer for totalling. Draw a DFD for this system. Set up a table with one row for the current date and have the operator enter the current date through a form -- use the current date for queries 4, 5, 6, and 7.
Submit all work via email to wleighwleigh@yahoo.com. Attach a word file with the ERD and DFD. Attach an MDB file with the developed application system. Have your name and "exam 4&5" in the subject line of the email.