Entity-relationship Data Modelling For Australasia Auto Services Essay.

Business rules

In this assignment, you are going to perform the entity-relationship data modelling based on the following application scenario

From time to time, the clients may feel unsatisfactory on a service job done and therefore they may complain about service quality, expensive cost and other possible problems. If so, the lodged complaint should be recorded and processed appropriately by an admin staff, with a response addressed the outcome of investigation. Finally, AAS also wishes to maintain contact details for each client who requests service, including their residential address, contact phone, email and whether they are a senior citizen, who may receive some discount in a service.

You are required to develop an ER model to represent the information requirements for the application scenario (AAS) as described on last page.

Your ER model must: 
•    show all necessary entities, attributes and relationships 
•    show unique identifiers 
•    show multi-valued attributes (also called repeating attributes), if any 
•    show participation and cardinality 
•    show associative entities, if appropriate 
•    use the notation described in the set text 
•    use consistent and appropriate naming for entities and attributes throughout 

Some business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ER model accordingly. For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about what the minimum cardinalities might reasonably be, and then show these in your ER model accordingly. You should justify each assumption in terms of the business, for example: it is assumed that each customer must have at least one order because it is assumed that the business does not record customer details until the customer places an order. 

To get yourself started, ask yourself, 'If I were running this business, what things I would need to keep a list of?' Write those things down. For each thing, what information would you need to record about it? 

Entity and attribute definitions 

You are required to write brief definitions for the entities and attributes in your model. For each entity and attribute in your model, write a sentence to describe the meaning of that entity or attribute. You should refer to the sections Naming and Defining Entity Types and Naming and Defining Attributes in the textbook. 

Figure 1: ER-Model for Australasia Auto Services

(Source: Created by author)

The business rule is defining for a conditions which is depended on a particular object to complete or server a job. The Australasia Auto Services have a car maintenance facility for their client, therefore they need to store all car related issues, required parts, service details as well as their employees and client details. The identified business rules are followings:

1. An employee of Australasia Auto Services may have one or more service job.
2. An employee must have a service type.
3. For every service job a car details must be store in the database.
4. The purchase history for a car must be store in the database.
5. A service may require one or more mechanics required.
6. If a service required to replace one or more parts, then it also store into the database.
7. A client details for every service is store into the database.
8. A client can give one or more complains, which also store into the database.

The Australasia Auto Services is a prestigious deader of car, however their main business is to provide car service to their client in Queensland and Rock Hampton.  They have increasing their services in recent years, therefore they need to build a relational database for managing their business. The information system is totally based on their car service where they have nine main entities to provide a car service.

The car service information system requires to store the details of every service done by their company. A car service includes a car details, client details and service details. The car details also include a purchase details of that car if it is brought from Australasia Auto Services or anywhere else. After storing a car details of a particular job, they also need to store number of mechanic required to provide this service and other associative details. Therefore, here a car, employees, service or job card as well as mechanics are assumed as entities.

The employees have different job role on different sections therefore to separate this information a new entity is assumed as category of employees. A car service may require parts to replace or repair, therefore Australasia Auto Services must have an entity as parts to check the require parts are available or not. A client may have some complain against the job done previously, therefore it can be assumed that complain is an entity and its related to every client.

The 3NF relations are identified from the presence of transitive dependency in between two relations. Here three entities are identified that has contained transitive dependencies. Therefore, to eliminates this problem a new relation is derived from the both relation. The below figures (2, 3, 4) shows how it could be decomposed into 3^rd NF.

Figure 2: Showing decomposition of two table into 3^rd NF

(Source: Created by author)

Figure 3: Showing decomposition of three table into 3rd NF, by introducing a new relation

(Source: Created by author)

Chen, J., Yang, M.H. and Koo, T.L., 2014. A control-data-mapping entity-relationship model for internal controls construction in database design. International Journal of Knowledge-Based Organizations (IJKBO), 4(2), pp.20-36.

Chougule, S., Kurmi, A.K., Vin, H.M., Kelkar, R.R., Kulkarni, S.P., Pathak, A.S., Palshikar, G.K., Pawar, S. and Ramrakhiyani, N.V., Tata Consultancy Serivces Limited, 2016. Automatic Entity Relationship (ER) Model Generation for Services as Software. U.S. Patent Application 15/224,931.

Dedhia, R., Jain, A. and Deulkar, K., 2015. Techniques to automatically generate Entity Relationship Diagram.

El?Assady, M., Sevastjanova, R., Gipp, B., Keim, D. and Collins, C., 2017, June. NEREx: Named?Entity Relationship Exploration in Multi?Party Conversations. In Computer Graphics Forum (Vol. 36, No. 3, pp. 213-225).

Fuchs, A.P., Allen, M.R., Berman, M.A., Laniyonu, A.D., Park, J.J., Travaglini, J.P., Vines, J.W. and Wheeler, B.L., Sqrrl Data, Inc., 2015. Entity-relationship modeling with provenance linking for enhancing visual navigation of datasets. U.S. Patent Application 14/801,950.

Hsiao, D.K., Neuhold, E.J. and Sacks-Davis, R., 2014, May. Translation of extended entity-relationship database model into object-oriented database model. In Interoperable Database Systems (DS-5): Proceedings of the IFIP WG2. 6 Database Semantics Conference on Interoperable Database Systems (DS-5) Lorne, Victoria, Australia, 16-20 November, 1992 (Vol. 25, p. 87). Elsevier.

Nhung, N.T., Song, S. and Lee, S., 2013. Transforming extended entity-relationship model into object-relational database using triple graph grammars. Korea in the ICT R&D Program, (13-912), pp.03-003.

Thalheim, B., 2013. Entity-relationship modeling: foundations of database technology. Springer Science & Business Media.