To create an Entity Relationship Diagram (ERD) for your friend’s business, PEAR, we need to
break down the information provided into entities (the things we want to keep track of) and
their relationships (how these things are connected). Here’s a simplified approach:
Step 1: Identify the Entities
Entities are the main things we need to track in the database.
1. Customer: Represents the customers who bring devices to PEAR for repair.
o Attributes: CustomerID (unique identifier), Name, Address, PhoneNumber
2. Device: Represents the devices that are brought in for repair.
o Attributes: DeviceID (unique identifier), Type (e.g., smartphone, laptop),
Model, SerialNumber
3. Repair: Represents the repair requests made by customers.
o Attributes: RepairID (unique identifier), DateOfRequest, ProblemDescription,
CustomerID (foreign key linking to Customer), DeviceID (foreign key linking to
Device)
4. Service: Represents the repair services that PEAR offers.
o Attributes: ServiceID (unique identifier), Description, Charge
5. Part: Represents the parts used during repairs.
o Attributes: PartID (unique identifier), PartDescription, QuantityInStock, Cost
6. ServicePerformed: Represents the services performed during a specific repair.
o Attributes: ServicePerformedID (unique identifier), RepairID (foreign key
linking to Repair), ServiceID (foreign key linking to Service)
7. PartUsed: Represents the parts used in a specific service.
o Attributes: PartUsedID (unique identifier), ServicePerformedID (foreign key
linking to ServicePerformed), PartID (foreign key linking to Part),
QuantityUsed
Step 2: Define the Relationships
Next, we establish how these entities are related to each other:
1. Customer to Repair:
o Relationship: A customer can have many repair requests, but each repair
request is linked to only one customer.
o Example: John (CustomerID: 1) brings his laptop for repair, creating a repair
record.
2. Device to Repair:
o Relationship: A device can be involved in many repairs, but each repair is for
only one device.
o Example: John’s laptop (DeviceID: 1) is repaired multiple times, each repair
having its own RepairID.
3. Repair to ServicePerformed:
o Relationship: A repair can involve many services, and each service performed
is linked to a specific repair.
o Example: For RepairID: 101, multiple services like screen replacement and
battery replacement are performed.
4. Service to ServicePerformed:
o Relationship: A service can be performed many times across different repairs,
but each service performed is linked to one specific repair.
o Example: Screen replacement (ServiceID: 10) is performed on multiple repairs
but once per repair.
5. ServicePerformed to PartUsed:
o Relationship: A service performed might use multiple parts, and each part
used is linked to a specific service.
o Example: For the screen replacement service (ServicePerformedID: 201), a
screen part (PartID: 1001) and screws (PartID: 1002) are used.
6. Part to PartUsed:
o Relationship: A part can be used in many services, but each part used is
linked to one specific service.
o Example: The same screen part (PartID: 1001) might be used in multiple
repairs.
Step 3: Create the ERD
With this information, we can sketch the ERD.
The customer is connected to Repair (1-to-Many relationship).
The device is connected to Repair (1-to-Many relationship).
Repair is connected to ServicePerformed (1-to-Many relationship).
Service is connected to ServicePerformed (1-to-Many relationship).
ServicePerformed is connected to PartUsed (1-to-Many relationship).
Part is connected to PartUsed (1-to-Many relationship).
Example Scenarios:
John (CustomerID: 1) brings his Laptop (DeviceID: 5) for a screen repair. This creates
a new Repair record (RepairID: 100). During this repair, Screen Replacement
(ServiceID: 10) is performed, using a Screen (PartID: 200) and Screws (PartID: 201).
Lisa (CustomerID: 2) brings her Smartphone (DeviceID: 7) for a battery replacement.
This creates another Repair record (RepairID: 101). Battery Replacement (ServiceID:
11) is performed, using a Battery (PartID: 300).
This approach keeps the system organized, ensuring every customer, device, repair, service,
and part is accurately tracked in the database.