B8IT113: A Car Dealership sells both new and used cars, and it operates a service facility: Databases Design & Development Course Work, DBS, Ireland

Assessment Task

A Car Dealership sells both new and used cars, and it operates a service facility. We know that a salesperson may sell many cars. For every sale, a Salesperson creates an invoice. Every Customer may buy many cars, but each car is bought by only one Customer. With each purchase, a customer receives an invoice. Each Invoice may have more than one car on it depending on how many the Customer bought in the transaction.

Customers’ details are recorded by the Dealership and periodically they review these as part of GDPR and ask customers via email/text message if they are happy for their details to remain in their system. If the customer wishes to be removed, the Dealership will remove only the personal information and hold onto the Business-Critical Information. Where a Customer has not had their car serviced with them in 13 months or more, they are marked as “inactive” and a report is sent to the Marketing Dept to be contacted and offered deals, etc.

The Dealership also offers the service of cars. Someone who is not a customer may simply drop a car in for servicing and never buy a car. The Dealership handles servicing through a ticketing system, this uses an alphanumeric numbering system e.g. SRV001. When a customer drops a car in for either a service or repair they will receive a ticket for that car. Over time a customer may of course get their car serviced or repaired many times. When a customer collects their car they will receive and pay an Invoice. All this information is held in the Ticketing System, so a customer/car servicing and repair history can be built up. To keep track of a car’s service history, the Dealership records all history against the car’s serial number, this is stored as an alphanumeric with a max size of 12 characters, e.g. SER101021. Each Service Ticket is assigned to a Mechanic. Daily each Mechanic may work on many service tickets. Over time there are a lot of different tickets per Mechanics and again this history is built upon the Ticketing System. Mechanics are uniquely identified by their MechanicID which is actually their PPS Number.