Modeling and Simulation Projects Proof Reading Services

Modeling and Simulation Projects Proof Reading Services

A customer calls HTH to request a home repair job. Details about the customer and the requested job need to be recorded.

The relation between the customer and job is mapped with the Customer_ID being the primary key for the customer entity and the Job_ID being the primary key for the Job entity since both these attributes define the two respectively. The customer will ‘Order’ for the job explicitly as one to one function.

Each tradesperson specializes in only one trade, and details of their trade and the date acquired need to be kept on record.

The Job entity will assign a Tradesperson based on the Job being asked. The Order_Date is assigned as a foreign key as it links the Tradesperson entity to the Job entity with the assigning date for the work. (Assumption: The tradesperson is assigned on the same date as that of the order date. And, this is taken to be one – to – one relation for now, assuming a job to be requiring only one trade.)

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.

A tradesperson first informs to HTH about the truck owned by him. This would be in form of a Yes/No and hence, taken one to one. Further, the trucks will be assigned to the user based on the condition that he does not possess a truck previously. This is an optional condition where one truck will be assigned to none or one user. The Truck entity is related with the Tradesperson entity through the Employee_code and Truck_possession; hence the two are taken as foreign keys. And, the Truck_number is used to uniquely identify a truck. Hence, it is made as a primary key.

A requested job may require different specialization depending on the job type – that is, a job may require more than one tradesperson to complete.

Since, a number of tradespersons can be assigned for one job. Hence, the Job entity identifies the trades included in the Trades entity which contains Trade_specialization, Employee_code and Job_ID as the foreign keys to the Tradesperson entity to establish the relation between them. A job may have a number of trades, hence linked by a one-to-many relation, while for every trade, one tradesperson is assigned; hence linked with a one-to-one relation.

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.

One tradesperson is appointed to supervise one or more tradespersons; hence linked with a one-to-many relation where the optionality is of one supervisor supervising one tradesperson only.

Also HTH wants to keep records of how the customer was referred to them – by what source (i.e. – TV ad, Friend, Internet, etc.)

The Source entity records the information about the sources through which the user is referred to HTH. Every source is having a Source_ID attached with it to uniquely identify it. And this Source_ID is then mapped into the Customer entity to establish a relation between them. And, since a user may be referred by 0, or 1, or a number of sources, hence it has a one-to-many relation with an optionality of the user having been referred by no source.

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.

A customer makes either a partial payment or a full payment. But, it is mandatory to have made either one of the two. Both of these contain the information about the amount, mode and the date of the payment, and are related with the customer’s payment for a particular job with the Job_ID, hence it is taken as the foreign key.

References:

Zheng, J, G., 2010, “Entity Relationship Diagram (ERD) Basics”. Available at: http://jackzheng.net/teaching/archive/cis3730-2010-fall/files/5-erd.pdf

Kruse S, L, K., Wells, M, G., 2016, “Optionality of ERD Relationships: Project for the Introduction to Database Course”. Available at: http://proc.iscap.info/2016/pdf/4035.pdf

Chawla, V, M., 2013, “ERD “Crow’s Foot” Relationship Symbols [Quick Reference]”. Available at: https://drive.google.com/file/d/0B_spkK3eZiHmZTZhczVTaVZxUFU/view