ER diagram for each statements:
For this statement, the customer details like name, mobile number, email and address are taken. By default, the customer_id will auto increment starting from 0 and is unique.
Whenever a service request is raised by the customer, a new service_id is automatically starting with 0 and incremented for each new service automatically by 1. The other fields are service_name (what type of service like electrical, plumbing, Landscaping etc), service_start_date is the date on which service request has been made. Service_end_date is the date on which the service request has been ended. Total price is the total cost for that service, Customer_id is the FK from Customer table, who has raised the service request. It is 1:M relationship.
In each specialization area, there will be a trade_person who is specialized in that area. So an unique number is assigned for both trade_person and specialization area. Also other details like Name, mobile number, date on which he acquired, all these details are taken into Trade_person. specializaiton_id is the foreign key from Trade_Specializaiton_Area
, that has a 1:M relaitonship with the element in TRADE_PERSON table.
HTH allocates a truck for each trade_person in case, if trade_person doesn’t has any truck. For each truck, there is a unique id, vehicle registration number, owner_by (whether HTH or trade person name) and the trade_person_id to whom the truck was assigned to. It is a one: zero or one relationship.
For each service request, a particular job allocation is done to many trade_persons. All these details are stored in the TRADE_PERSON_JOB_ALLOCATION. There can be multiple entries for each service_id i.e. If for one service, there are 3 trade_persons assigned, then there will be 3 entries for that service in the table.
HTH may allocate a trade_person as a supervisor. In the Trade_person table, super_visor_id will be trade_person_id for that trade_person. It is a 1:M relationship.
Each customer may come to know about the HTH company by some source, that is added in the customer table as “referred_source”. It can be friends, news paper etc.
A service is having a particular price that should be paid by the customer at the end of the service. This amount can be paid as either full_payment or partial_payment also. For each payment paid, there will be a new payment_id, corresponding service_Id, amount_paid at that time, payment_type is the full/partial payment, payment_mode is the card/cash/DD/cheque etc, payment_date on which payment has been paid.
Sikha, B., Richard, E., (2011). Database Design Using Entity-Relationship Diagrams.
Shio Kumar, S., (2011). Database Systems Concepts, Designs and Application. India: Pearson