BISY1002 Database Management for Business by Using ERD

es involved in perfecting that job requests. The steps will be described in brief of how we can model the business using Entity Relationship Diagram in which we include all the attributes, cardinalities, primary and foreign keys as discussed below.

Basic definitions

ERD – entity relationship diagram is a diagram drawn to show how logical structure of a database appears to be. It indicates the relationship between different data entities.

Entity – is an object that we want to model and store information data about it.

Attributes – this are the components in an entity and gives that entity its characteristic.

Cardinalities – this shows the degree of our database data uniqueness.

Entity Relationship Diagram

A customer calls HTH to request a home repair job:

Details about the customer and the requested job need to be recorded.

If there exists a customer, then that customer must have made a job request. Making of many job requests by the customer is optional. The relation between customer and job request is a 1 to M relationship on optional bases. The figure below shows that relationship.

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

When a job request is made by the customer, a trade begins where a tradesperson works on that trade on qualifying for that job. The relation between one trade to trades people is a 1 to 1 relationship as shown below:

HTH has several trucks (including equipment) and allocates a truck for each tradesperson to do a job. Although some tradespersons have their own truck and equipment and advice HTH about this, so not to be assigned a truck.

When a tradesperson acquires a trade job, he can request for a truck to use. The tradesperson can request for a truck on optional basis. The relation between tradesperson and the truck is a 1 to 1 relationship and the relationship between truck and assignment is one truck for one job trade. See the figure below:

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

The number of tradespeople needed to work on available job trade is dependent on the needed specialization. The relation between the trade and the tradesperson is 1 to M relationship on an optional basis. See the figure below:

At a later interview with the manager you learnt that HTH appoint one of the tradespersons to supervise others and oversee the work done by the other tradespersons.

For every job trade that exists, there must be assigned a supervisor tradesperson to oversee the other tradespeople when the job requires more than one tradespeople. However, the tradesperson can be the only working person on that job and will be answerable to the progress of that job trade. See the figure below:

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

For every new customer, there must be kept a record of how that customer was referred to the HT handy organization. This helps truck the marketing progress of the organization. The relation between customer and referral is 1 to 1 mandatory relationship since the details can be readily available. See the figure below for clarity:

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. (2 marks)

The customer must make a payment for the given job. During payment, the customer can opt to pay in bits as long he completes the payment in time. The relation between the customer job request and payments is 1 to M relationship on optional bases as shown below:

Conclusion

We can conclude that, to have a key data recording model structure of our database, we should consider all cases of anomalies and solve them at once. The business rules are the key for us to be able to identify our entities. These rules will help us obtain the required attributes of those identified entities as well provide high degree of uniqueness in data to prevent anomalies. The last diagram on number G above is our final model target data representation. If one step fails to be recorded, our database can be incomplete.

References

Bagui, S. (2012). Database design using entity-relationship diagrams. Boca Raton, FL: CRC Press.

Silverston, L., Agnew, P., Graziano, K., & Inmon, W. H. (2001). The Data Model Resource Book: A Library of Universal Data Models for All Enterprises; Revised Edition; Volume 1. John Wiley & Sons.