Delivery in day(s): 4
Modeling of Engineering System Proof Reading Services
1.A customer calls HTH to request a home repair job. Details about the customer and the requested job need to be recorded.
A primary key is the attribute (or a set of attributes) which can be used for uniquely identifying each row of an entity uniquely, while a Foreign key is the attribute which is the primary key of some other entity. In the above Entity-Relationship Diagram Customer and Job are the entities. C_id (Customer id) is the primary key of Customer entity and Job_id is the primary key of Job entity. Job_id is the foreign key in Customer entity. ‘Requests’ is a 1:1 relationship mandatory for both sides.
2.Each tradesperson specializes in only one trade, and details of their trade and the date acquired need to be kept on record.
Trades_person is the entity for saving details of tradespersons with ‘T_id’ as the primary key. Assign_job is a one to one relationship where having a tradesperson for each job is mandatory So, cardinality is 1:1 and optionality is 0:1.
3.HTH has several trucks (including equipment) and allocates a truck for each tradesperson to do a job. Although some tradesperson have their own truck and equipment and advise HTH about this, so not to be assigned a truck.
Truck is the entity which contains the T_id of the tradespersons who have their own truck. It is a weak entity which needs the primary key of its parent entity as the primary key. Cardinality of ‘Check_truck’ relationship is M:1 and optionality is 1:1.
4.A requested job may require different specialization depending on the job type – that is, a job may require more than one tradesperson to complete.
The job type attribute is added in Job entity and the cardinality of ‘Assign_job’ relationship is also changed to 1:M. These changes allow the database to assign single job to multiple tradesperson.
5.At a later interview with the manager you learnt that HTH appoint one of the tradesperson to supervise others and see the work done by the other tradespersons.
The supervises relationship is defined from ‘Tradesperson’ to itself and the cardinality is 1:M and optionality is 1:1.
6.Also HTH wants to keep records of how the customer was referred to them – by what source (i.e. – TV ad, Friend, Internet, etc.)
A new entity ‘Refferal’ contains the record of the referral mode of the customer with ‘R_id’ as the primary key. The cardinality of relationship ‘Reffered_by’ is M:1 and optionality is 0:1.
7.Customers have the option to make partial payments for a service, as long as they pay no later than one week after the completion of any service. Details about each payment relating to a service, amount of payment and date need to be recorded.
The ‘partial_payment’ entity keeps the record of due date, amount attributes and the Job_id as primary key. It is a weak entity because it needs Job_id as its primary key. The ‘partial_pay’ relationship has cardinality 1:M and optionality 0:1.
Bagui, S. (2012). Database design using entity-relationship diagrams. 1st ed. Boca Raton, FL: CRC Press.
Tretiakov, A. and Hartmann, S. (2005). Higher order entity relationship modeling with UML. 1st ed. Palmerston North, N.Z.: Massey University, Dept. of Information Systems.
Zheng, J. (2010). Entity Relationship Diagram (ERD): Basics. 1st ed. Georgia State University.