Tutorial 2
Case Study: Pinnacle Motors & Service Hub Operations Overview
Pinnacle Motors & Service Hub is a multifaceted establishment, dealing in both new and pre-owned
vehicles while also offering service and repair facilities. To effectively manage their operations, it's
imperative to understand their primary activities and interactions:
Sales Operations:
For every vehicle purchase, the corresponding salesperson issues an invoice detailing the
sales transaction specifics.
Customers, irrespective of whether they buy a vehicle, receive an invoice for every
transaction they initiate.
Customer Profiles:
Customers can own multiple vehicles purchased from the dealership.
Not all customers at the dealership are vehicle buyers. Many visit just for servicing their
vehicles.
Each vehicle owned by a customer that comes in for service is assigned a unique service
ticket.
Service Operations:
The dealership is dedicated to maintaining a comprehensive service history for all vehicles
that come in for servicing. This service history is always linked to the vehicle's serial number.
Vehicles that come in for service might be attended to by various mechanics during their
service period. These mechanics have the flexibility to work on multiple vehicles.
Not all services rendered to a vehicle necessitate the use of new parts. For example, some
services might involve adjustments or cleaning tasks that don't require replacement parts.
Task 1: Identity Entities, their Attributes and their Relationships and then present them in a 2D
table.
1. Customer (CustomerID, FirstName, LastName, Email, PhoneNumber, Address)
2. Vehicle (SerialNumber, Make, Model, Year, Price, Type)
3. Salesperson (SalespersonID, FirstName, LastName, Email, PhoneNumber)
4. Invoice (InvoiceID, Date, TotalAmount, TransactionType)
5. ServiceTicket (TicketID, VehicleSerialNumber, ServiceDate, ServiceDescription,
ServiceCost)
6. Mechanic (MechanicID, FirstName, LastName, Specialization, Email, PhoneNumber)
7. Service (ServiceID, Name, Description, Cost)
8. Part (PartID, Name, Description, Cost, QuantityAvailable)
9. ServiceAssignment (ServiceID, Date, HoursWorked)
Relationships Matrix:
Customer Vehicle Salesperson Invoice ServiceTicket Mechanic Service Part ServiceAssignment
Customer -- -- -- 1:M 1:M -- -- -- --
Vehicle -- -- -- -- 1:M -- -- -- --
Salesperson -- -- -- 1:M -- -- -- -- --
Invoice M:1 -- M:1 -- -- -- -- -- --
ServiceTicket M:1 M:1 -- -- -- -- -- M:M 1:M
Mechanic -- -- -- -- -- -- -- -- 1:M
Service -- -- -- -- -- -- -- -- 1:M
Part -- -- -- -- M:M -- -- -- --
ServiceAssignment -- -- -- -- M:1 M:1 M:1 -- --
Task 2: Draw the first draft of the Conceptual model
Task 3: Convert the conceptual model to a logical model
Steps to convert a conceptual Model to Logical model
1. Find a PK for each table
2. Split any composite attribute (For example, Address means Street no, postcode and few other)
3. Split all the M:M relationships (identify PK, which is normally the parent’s PK – Composite
PK. Alternatively, you can create an artificial key and leave the Parents PK as just FKs of the
table)
4. Create a separate table for multi-valued attributes and connect the new table with a 1:M
5. Establish FK (Generally, there will be an FK coming from the connecting table for each M
pointing to the entity. For example, ServiceAssignment must have 3 FKs coming from
ServiceTicekt, Service and Mechanic respectively)
You can attach a payment
entity to the invoice make
it more comprehensive
Address is a
composite attribute
which can be
further divided or
consider splitting
too
Tables and Relationship Interpretation:
1. Customer (CustomerID, FirstName, LastName, Email, PhoneNumber, Address)
2. Vehicle (SerialNumber, Make, Model, Year, Price, Type)
3. Salesperson (SalespersonID, FirstName, LastName, Email, PhoneNumber,)
4. Invoice (InvoiceID, CustomerID, SalespersonID, VehicleSerialNumber Date,
TotalAmount,)
5. ServiceTicket (TicketID, CustomerID, VehicleSerialNumber, ServiceDate,
ServiceDescription, ServiceCost)
6. Mechanic (MechanicID, FirstName, LastName, Specialization, Email, PhoneNumber)
7. Service (ServiceID, Name, Description, Cost)
8. Part (PartID, Name, Description, Cost, QuantityAvailable)
9. PartUsed (PartID, TicektId, QtyUsed)
10. ServiceAssignment (ServiceID, MechanicID, TicketID, Date, HoursWorked)
Note: Table 10 is a newly created table as a result of M:M Split
Customer to Invoice (1:M):
A Customer receives multiple Invoices.
An Invoice is received by one Customer.
Customer to ServiceTicket (1:M):
A Customer creates multiple ServiceTickets.
A ServiceTicket is created by one Customer.
Vehicle to ServiceTicket (1:M):
A Vehicle is associated with multiple ServiceTickets.
A ServiceTicket is associated with one Vehicle.
Salesperson to Invoice (1:M):
A Salesperson issues multiple Invoices.
An Invoice is issued by one Salesperson.
ServiceTicket to Part (M:M):
A ServiceTicket requires multiple Parts.
A Part is required in multiple ServiceTickets.
ServiceAssignment to ServiceTicket (1:M):
A ServiceAssignment is listed in one ServiceTicket.
A ServiceTicket lists multiple ServiceAssignments.
ServiceAssignment to Mechanic (1:M):
A ServiceAssignment is assigned to one Mechanic.
A Mechanic is assigned multiple ServiceAssignments.
ServiceAssignment to Service (1:M):
A ServiceAssignment includes one Service.
A Service is included in multiple ServiceAssignments.
Notes:
A ServiceTicket represents the record of service for a particular Vehicle and Customer, detailing the
services required, parts used, and the overall cost of the service.
A ServiceAssignment represents the assignment of a Mechanic to perform a specific Service as part of
a ServiceTicket.
Task 6: Design a mock up screen to capture Vehicle Purchase Transaction
What information would be required
What data is available in your database and what needs to be captured all new for this
transaction and what needs to be linked to the Purchase transaction?
Explanation of the data requirement:
Transaction Date and Invoice ID: Date when the transaction is being made and an auto-
generated invoice number for the transaction (Needed all new)
Customer and Salesperson Details: Information about the customer and the salesperson
involved in the transaction (Already existing, just need to link)
Vehicle Details: Information about the vehicle being purchased including the make, model,
year, price, and type. (Already existing, just need to link)
Payment Details: Information about the payment including total amount, payment method,
and transaction type, along with any finance details if applicable (Needed all new)
What is the most appropriate input control for each data type?
Fields like IDs may be auto-populated or looked up based on existing data.
Dropdowns are used where there are fixed possible values, such as Payment Method and
Transaction Type.