DATABASE ASSIGNMENT.

Database Project Assignment

PLEASE READ THIS FIRST BEFORE ANSWERING QUESTION.

Using Database Software to Design a Customer System for Auto Sales

Adapted from Laudon, Kenneth C. and Jane P. Laudon. Essentials of Management Information Systems. Ninth Edition. Pearson Education, Inc. 2011. ISBN: 978-0-13-611099-6

This project requires you to perform a systems analysis and then design a system solution using

database software. Ace Auto Dealers specializes in selling new vehicles from Subaru. The

company advertises in local newspapers and is listed as an authorized dealer on the Subaru Web

site and other major Web sites for auto buyers. The company benefits from a good local word of

mouth reputation and name recognition and is a leading source of information for Subaru

vehicles in the Portland, Oregon, area. When a prospective customer enters the showroom, he or

she is greeted by an Ace sales representative. The sales representative manually fills out a form

with information such as the prospective customer’s name, address, telephone number, date of

visit, and make and model of the vehicle in which the customer is interested. The representative

also asks where the prospect heard about Ace— whether it was from a newspaper ad, the Web,

or word of mouth— and this information is noted on the form also. If the customer decides to

purchase an auto, the dealer fills out a bill of sale form.

Ace does not believe it has enough information about its customers. It cannot easily determine

which prospects have made auto purchases, nor can it identify which customer touch points have

produced the greatest number of sales leads or actual sales so it can focus advertising and

marketing more on the channels that generate the most revenue. Are purchasers discovering Ace

from newspaper ads, from word of mouth, or from the Web?

QUESTION; Prepare a two-page systems analysis report, in APA format, detailing Ace’s problem and a system solution that can be implemented using PC database management software. Your systems analysis report should include the following:

· Description of the problem and its organizational and business impact.

· Proposed solution, solution objectives, and solution feasibility.

· Costs and benefits of the solution you have selected. The company has a PC with Internet

· access and the full suite of Microsoft Office desktop productivity tools.

· Information requirements to be addressed by the solution.

· People, organization, and technology issues to be addressed by the solution, including

any proposed changes in business processes.

Then use database software to develop a simple system solution. Using Database Software to Design a Customer System for Auto Sales

 Which sales representative sells the most vehicles?

 What is the bestselling make of model?

The following reports are required:

 Customer profile

 Leads by touch point including customer information

 Sales by touch point including customer and vehicle information

Based on the requirements you have identified:

 Prepare an entity relationship diagram which models your proposed design.

 Write the SQL statements to create the database tables, relationships and populate each table with at least 10 records per table, where applicable.

 Use Microsoft Access to create a database called Ace-Auto-Dealers.accdb using your SQL scripts.

 Finally, use the database system you have created to generate data input forms, queries and reports that would be of most interest to management. The system is to support only the processing requirements described for the existing manual system. You should have: A minimum of 3 data input forms depending upon your database design

The following queries are required:

 Which touch point generates the most sales leads? Least sales leads?

 Which touch point generates the most purchases? Least purchases?