CSCI 104 Spring 2016, Access Test, Practical Portion

For this portion of the test we will be helping the owner of a used truck dealership organize and and discover information about her sales for the last year. The owner has been storing this data in an excel workbook, which is available here. Please note, I generated this data using the web site generatedata.com so don't look for any real world information in this data. (A truck from 1997 might sell for much more than a truck from 2015.)

The owner has a number of sales people. Each sales person has a commission based upon their performance, customer satisfaction rating and annual review. In addition, the owner has a record of the salesperson's phone number. While there may be other data related to the sales force, it has been removed to simplify the exercise.

In addition the owner has some basic information on the different types of trucks she sells. This includes the make and model.

Finally the owner has recorded information about all sales for the past year. This includes a transaction number, the id of the person who sold the truck, the id of the truck type, the year of the truck, the price it sold for and the date the truck was sold.

The owner would like to perform several tasks these include

• Find the dates when the most expensive trucks were sold.
• Find who sold the least expensive trucks.
• Finding the salesperson who received the largest commission for 2016.
• Finding the average price for each of the types of trucks sold.

1. Database Design (15 points)
   1. On the back of your test page draw a diagram describing the tables you will create in the database. Show the relationship between the tables. Answer the questions relating to your design.
2. Database Implementation (20 points)
   • Using your design and the data from this worksheet implement and populate the tables of your database.
     • Create each table described in your design.
     • Make sure that each field is of the proper data type.
     • Make sure that all key fields are of the same data type.
     • Establish relationships between tables.
     • Save your database as AccessTest_Lastname_Firstname.
3. Queries(5 points each)
   1. Create a query which displays the date sold and the price for all sales, from the highest price to the lowest price. Save this query as Highest Prices.
      • 
   2. Create a query which displays the first and last name of the sales person for all trucks costing less than $5,500. Save this query as Lowest Prices
      • The table should be sorted from lowest to highest price (as part of the query)
      • 
   3. Create a query which displays the trucks sold by make and model. Compute the number of each make/model combination and the average sales price for each make/model combination. Save this query as Make Model.
      • The column containing the number of trucks sold should be labeled Trucks Sold
      • The average price column should be labeled Average Price
      • The make should be sorted ascending
      • The Trucks Sold should be sorted ascending.
      • 
   4. Create a query which displays the last and first name of all salespersons showing the total number of cars sold, the total sales and the total commission for all cars sold since December 31, 2015. Order this query by the total commission, lowest to highest. Save this query as Commissions
      • The number of trucks sold should column should be labeled Number Sold.
      • The total sales column should be labeled Total Sales
      • The commission column should be labeled Total Commission
      • 
4. When you are finished, save a copy of your database and email it to your instructor.