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.
Relationships Matrix:
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 attributes present (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)
Task 4: 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.