QC303 Advanced Database Management Systems

QC303 Advanced Database Management Systems

CASE STUDY - Holloway Auto DEALERSHIP

Holloway Auto is an automobile dealership located in North London. Holloway Auto is owned by Ken Hayes, its general manager and two brothers, Paul and Frank King. The King brothers together own 80% of the company and operate it as a subsidiary of a larger dealership.

Ken Hayes manages Holloway Auto autonomously at both the operational and strategic levels of business. Ken is very entrepreneurial and plans to use his sizeable bonuses to buy out the King brothers’ interest or to purchase another dealership independently. Ken is marketing oriented and believes that keeping track of customers (potential customers as well as purchasers) and keeping in contact with them are key elements in selling his line of vehicles.

Holloway Auto’ products and business position

Holloway Auto sells Mazda, Rover and Volvo lines of vehicles. The company is only 3 years old, so it does not yet have a well-established customer base. Buyers of these types of imported vehicles tend to be very particular and will search nationally for the car of their choice.

Last year, the company sold 350 new cars, of which 178 were Mazdas. Although Holloway Auto has already experienced a 15% growth, Ken Hayes believes that this can be increased further.

History of computing

Computing services for Holloway Auto and the parent dealership are currently provided on a microcomputer using primarily packaged software purchased from a major software supplier. Automobile parts, inventory and payroll are the major applications that have been implemented. However, Ken Hayes is planning to implement a useful automated reporting service for use at Holloway Auto, with a view to allowing other dealerships within the group to use such a service in the future.

Basic operations

Ken Hayes is a participative manager. He frequently leaves his office, which overlooks the showroom, to greet customers, especially repeat customers. Ken must approve each deal, and he occasionally sells a care himself.

Holloway Auto has a traditional dealership organisational structure (see Figure 1). The Parts & Service manager operates a business within a business and does not need close supervision. The Accounting manager is primarily responsible for accounts receivable and other aspects of cash flow. The assistant managers for New Vehicle Sales and Used Vehicle Sales are floor sales personnel, with the added duties of supervising other sales staff and approving sales in Ken Newman’s absence. Ken would like for these sales managers to be more involved in marketing and attracting new business.

Vehicle sales

Basically there are two working documents used for a vehicle sale. The first is the Sales Invoice (see Figure 2), where the specifics of base price, salesperson, add-on options, and other information are detailed. This document is first hand-written by a salesperson while she or he is talking to a prospective customer; it is frequently changed as customer preferences change and as ‘deals’ are made. After the customer and salesperson decide on the sale, the invoice is typed and signed.

The second working document is the Vehicle Inventory Record (see Figure 3 for the new vehicle version; the used vehicle version is similar). This document is used to describe vehicles in stock at Holloway Auto and is prepared only after receipt of a vehicle (either from a manufacturer or from trade-in); vehicles on order or being considered.

Customer tracking and marketing

Ken Newman’s primary concern is to keep track of existing and potential customers and the vehicles that they seek, they have purchased, and what he has in stock. Ken believes significant sales are lost because of the following:

  • Potential customers’ preferences for cars and car options are not remembered by the sales personnel.
  • Some repeated customers are not identified and not dealt with as ‘old friends’.

In order to alleviate these types of problems, Ken has envisaged that a system, which is capable of generating reports on an ad-hoc basis, would prove very useful. An example of such a report is a listing of the names and addresses of people who have bought cars from a specified salesperson (i.e. to make up a mailing list). Ken believes that duplicate mailings from the same salesperson would be annoying to the customer, but that ‘personalised’ mailing from different salespersons is tolerable.

Figure 1: Holloway Auto Organisational Structure

Figure 2: Holloway Auto Sales Invoice

Figure 3: Holloway Auto New Vehicle Inventory Record

Tasks

Study the 'Holloway Auto' case study carefully and then perform the following tasks:

1. Normalise the Sales Invoice Form in Figure 2 to the third normal form.

Show all the steps of normalisation clearly, from the first normal form to the third normal form. State any assumptions you have made.

(30%)

2.Use Oracle Designer to produce an Entity-Relationship model based on your normalised entities arrived at in 1 above. Clearly show all the attributes (with appropriate data types) for each entity.

(20%)

3.Use Oracle Designer to convert the ER model to database relations.

(5%)

4.Use SQL statements to populate the relations with suitable data.

Submit SQL scripts for inserting and displaying the contents of the relations.

(15%)

5.Implement the following functionality using SQL:

(a)Display a list of customer details for a particular salesperson.

(b)Display a salesperson list showing his/her customers who have been invoiced in the past year.

(c)Display a list of vehicles that were sold within a given period.

(d)Display the address details for a customer who bought a particular vehicle.

(30%)

  • Submit all appropriate SQL scripts and output, as well as output from Oracle designer.
  • Make sure to document each query separately, and label clearly each piece of output and state its purpose.
  • Marks will be deducted for not providing sufficient and clear documentation.

END

1