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. Sales are recorded via an Invoice which are set up by the Salesperson Involved. Customers receive a copy of the invoice upon completion of the sale. Every Customer may buy many cars, but each car is bought by only one Customer. Each Invoice must include the Car Reg, Sales Date, Quantity, Total, Salesperson and Customer its for. Customers det

THIS PAPER HAS BEEN DONE AT ESSAYLINK.NET; CONTACT US FOR A SOLUTION

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. Sales are recorded via an Invoice which are set up by the Salesperson Involved. Customers receive a copy of the invoice upon completion of the sale. Every Customer may buy many cars, but each car is bought by only one Customer. Each Invoice must include the Car Reg, Sales Date, Quantity, Total, Salesperson and Customer its for.

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 servicing of cars. This is handled 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. Overtime a customer may of course get their car serviced or repaired many times. When a customer collects their car, they will and pay they receive a receipt. The Receipt must show Car Reg, Customer, Service or Repair, Any parts Used and the Total Cost. To keep track of a cars service history, the Dealership records all history against the car’s registration number.

Page 2 of 9

Each Service Ticket is assigned to a Mechanic. Daily each Mechanic may work on many service tickets. Overtime there are a lot of different tickets per Mechanics and again this history is built up on the Ticketing System. Mechanics are uniquely identified by their Employee Number, this differs to their PPS number as under GDPR this is kept only in the HR system.

In terms of some of the key entities they have given you the following list:
• Car

• Customer

• Employee (SalesPerson or Mechanic)

• Service Ticket

In terms of data Updates, they have the following requirement:

• They want the ability to create:

o A new Customer via a parameterised Stored Procedure.

o A New or Used Car to the sales stock via a parameterised Stored Procedure.

o A new Employee (SalesPerson/Mechanic) via a parameterised Stored Procedure, this should set

up all the relevant information, such as PPS Number, Name, Skills etc.

o A New Service or Repair Ticket via a parameterised Stored Procedure.

Page 3 of 9

• They want the ability to delete a customer and foreign key records associated with this Customer via a parameterised Stored Procedure if they have a requested their details be deleted, as per GDPR compliance. This delete must also allow for Business-Critical Data to be kept.

In terms of Management Information (MI), they see real value in having a single view on all their

Mechanics, Tickets and Cars worked on and as such want the following extract:

They want an MI extract created as a SQL View which will return all Mechanics, Tickets and Cars worked on per day. The view should be run each day and show the previous days details. This should include the Mechanic’s Employee Number, Full Name, the Ticket Number, Date, Time, Car’s Reg., Make and Model at a minimum. Other relevant details may also be included.

Inactive Customer Data View

The company also want visibility of all Customers who have not serviced their car with them in the last 13 months. This information will be passed to the Marketing dept to send out deals etc to them. Details here should include the Customer’s Name, Contact Details, Car Reg, Date Last Serviced at a minimum.

They have come to you as an IT Consultant and they want these requirements developed into an enterprise solution. Along with the final solution you must supply the customer a Technical Design Document with the technical design details of the solution.

Page 4 of 9

Technical Document

The accompanying Technical Document should cover (but not limited to):

1. Scope of the document.

2. Technical Design to include:

a. ER Diagram (Physical Model to 3NF)

b. Data Dictionary

c. Technology used

3. List All Supplied DDL & DML Transact SQL Files (run order must be given)

4. Test Plan.

5. Reflection on Learnings.

6. References.

Page 5 of 9

Project Deliverables

The distribution of assessment marks will be as follows:

Deliverable
Breakdown of Marks
Submission Date

Technical Document
20%
8th December 2022
(Should not be less

(midnight)
than 300 words)

Database Design &
80%
8th December 2022
Development

(midnight)

ASSESSMENT CRITERIA Criteria/Mark

GET ALL YOUR ACADEMIC HELP AT ESSAYLINK.NET