MIS 340 Final Exam

Due: Friday, March 19 by 11pm

Method: You MUST turn in a digitally formatted exam document through the WebCT exam drop link. Thus, a hand-writing exam document is not accepted. If creating a digital  exam document is not possible, scan your hand-wring document using a digital scanner.

Delivery: MUST submit through the WebCT exam drop link

Question 1: Please click HERE to download the Access data. Please use the downloaded Access database to generate SQL queries for following questions. 

  1. List the number, name, and balance of all customers with balances greater than or equal to $1,000 and less than or equal to $5,000.

  2. List the number, name, and credit limit of all customers. Sort the customers by name in ascending order within credit limit in descending order.

  3. List the description of all parts that are located in warehouse 3 and for which there are more than 20 units on hand.

  4. List the number, name and available credit for all customers.

  5. How many parts are in item class HW?

  6. Find the total number of customers and the total of their balances. Change the column names for the number of customers and the total of their balances to CustomerCount and BalancesTotal.

  7. List the order number for each order that contains an order line for a part located in warehouse 3. 

  8. For each sales rep, list the rep number, the number of customers assigned to the rep, and the average balance of the rep’s customers.

  9. For each sales rep with fewer than four customers, list the rep number, the number of customers assigned to the rep, and the average balance of the rep’s customers. Rename the count of the number of customers and the average of the balances to NumCustomers and AverageBalance.

  10. List the number and name of each customer whose credit limit is $10,000 together with number, last name, and first name of the sales rep who represents the customer.

  11. For every order, list the order number, order date, customer number, and customer name. In addition, for each order line within the order, list the part number, description, number ordered, and quoted price.

  12. List the number and name of all customers that are either represented by sales rep 35 or that currently have orders on file, or both.

Question 2: Please click HERE to download the data model file in PDF format. Please transform the downloaded data model to a relation model. If necessary, you MUST provide assumptions when you transform the  data model.

Question 3: Please click HERE to download the relational model file in Microsoft Word format. Please reverse engineer the downloaded relational model to a data model. If necessary, you MUST provide assumptions when you reverse engineer the relational model.