9-10 In this exercise, you’ll use database software to develop an application that tracks customer service requests and analyzes customer data to identify customers meriting priority treatment.

Prime Service is a large service company that provides maintenance and repair services for close to 1,200 commercial businesses in New York, New Jersey, and Connecticut. Its customers include businesses of all sizes. Customers with service needs call into its customer service department with requests for repairing heating ducts, broken windows, leaky roofs, broken water pipes, and other problems. The company assigns each request a number and writes down the service request number, the identification number of the customer account, the date of the request, the type of equipment requiring repair, and a brief description of the problem. The service requests are handled on a first-come-first-served basis.

After the service work has been completed, Prime calculates the cost of the work, enters the price on the service request form, and bills the client. This arrangement treats the most important and profitable clients—those with accounts of more than $70,000—no differently from its clients with small accounts.

Managment would like to find a way to provide its best customers with better service. Management would also like to know which types of service problems occur most frequently so that it can make sure it has adequate resources to address them.

Prime Service has a small database with client account information, which can be found in MyMISLab™. Use database software to design a solution that would enable Prime’s customer service representatives to identify the most important customers so that they could receive priority service. Your solution will require more than one table. Populate your database with at least 10 service requests. Create several reports that would be of interest to management, such as a list of the highest—and lowest—priority accounts and a report showing the most frequently occurring service problems. Create a report listing service calls that customer service representatives should respond to first on a specific date.