Entity-Relationship Data Model For Australasia Auto Services (AAS) Essay.

Business rules followed in the AAS ER diagram

Australasia Auto Services (AAS) is a prestigious dealer of cars based in Rockhampton, Queensland.Their main business is to provide professional maintenance services for various cars and also sells a widerange of new vehicles. With the boom of its business in recent years, AAS needs a relational database system to store information related to its business. In this assignment, your task is to design an entity relationship (E-R) data model for a database to be used by AAS in order to manage various information related to their business activities efficiently. You should read the case study carefully, making a note of all the entities you think are involved, and of their attributes. Consider the relationships between the entities as you do this.

Although AAS hires many staff, each employee can be categorised one of three types: mechanic,salesperson, and administration staff. The duty of mechanic is to perform car maintenance service, while the salespersons are responsible for sales and the administration staff deal with a wide range of administrative jobs or miscellaneous tasks. Each employee at AAS has a unique employee number, and AAS maintains normal contact information about each employee. In addition, the technical trade level and expertise of mechanics, the number of year in sales experience for a salesperson, and the qualification of admin staff are to be recorded in the system.

After a phone booking, a client will bring a car in for service on a specified date and the admin staff will record the car details that include the registration number, manufacturer, make, model, produced year and colour. The admin staff will also record a description of service requirement. Then the system will assign a unique identifier number for this service job. It is necessary to point out that the client’s car could be a car purchased from AAS, or it may be bought from somewhere else. For the former case, some relevant details such as the salesperson name, price, and warranty due date are required to keep in track.

Usually each service job could be carried out by one or many mechanics. The data on service details such as which mechanics perform the service job, labour cost, part cost, date, start time and finish time must be recorded in the system. When a car is being repaired, it often involves replacing certain parts due to various damages. For this reason, the mechanic may check its records in the database on a list of part types. Each type has a unique part identifier, a description, manufacturer or brand information, a price per unit and the quantity available in the stock. If parts are required to replace during service, the mechanic records the quantity of each type used into the database.

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

Business rules that are followed in the AAS ER diagram are as follows-

• Three types of staff members- Sales Persons, Mechanics and Administrators will be accumulated separately into the database.
• The discount will be given to senior citizen clients.
• The client detail is mandatory for future use and for complains.
• The AAS Sold car’s details are also mandatory.
• Some fields of services of AAS sold cars is mandatory to store in separate table e.g. Warranty due date, price etc.
• Start and finish time of each service detail will also be stored into the database.
• The date of complaint filed will also be stored into the database.
• The part detail along with the available quantity
• Full address of the client and staff members will be stored into database.
• Sales Persons, Mechanics and Administrators tables will store uncommon fields.
• Common fields of all staff members will be stored into Staff table.
• Entity and Attribute Definitions

TblSTAFF: it stores the information of AAS staff members. Similar fields of Mechanics, Sales Persons and administrators will be stored in this table.

FldEmployeeID: It is the primary key and uniquely identifying each staff.

FldName: It stores the name of AAS staff members.

FldStreet: It stores the street of AAS staff members.

FldCity: It stores the city of AAS staff members.

FldState: It stores the state of AAS staff members.

FldPostcode: It stores the post code of AAS staff members.

FldPhone: It stores the phone of AAS staff members.

FldMobile: It stores the mobile of AAS staff members.

FldEmail: It stores the email of AAS staff members.

TblMECHANIC: It stores the information of AAS mechanics.

FldEmployeeID: It is the primary key and uniquely identifying each mechanic.

FldTechTradeLevel: It stores the trade level of AAS mechanic.

FldExpertise: It stores the expertise of AAS mechanic.

TblSALESPERSON: It stores the information of AAS sales persons.

FldEmployeeID: It is the primary key and uniquely identifying each sales person.

FldExperience: It stores the experience of AAS sales person.

TblADMINISTRATORS: It stores the information of AAS administrators.

FldEmployeeID: It is the primary key and uniquely identifying each administrator.

FldExperience: It stores the experience of AAS administrator.

FldQualification: It stores the qualification of AAS administrator.

TblCLIENT: It stores the information of AAS clients.

FldClientID: It is the primary key and uniquely identifying each client.

FldName: It stores the name of AAS client.

FldAddress: It stores the address of AAS client.

FldPhone: It stores the phone of AAS client.

FldMobile: It stores the mobile of AAS client.

FldEmail: It stores the email of AAS client.

FldSeniorCitizen: It stores the status of AAS client for senior citizen.

TblSOLDCARS: It stores the information of AAS cars.

FldRegistrationNumber: It is the primary key and uniquely identifying each car.

FldSalesPerson: It stores the Sales person of AAS car.

FldSellingDate: It stores the selling date of AAS car.

FldMake: It stores the make of AAS car.

FldModel: It stores the model of AAS car.

FldColour: It stores the colour of AAS car.

FldManufacturer: It stores the manufacturer of AAS car.

FldProducedYear: It stores the produced year of AAS car.

FldClientID: It stores the client; owner of AAS car.

FldPrice: It stores the price of AAS car.

FldWarrantyDueDate: It stores the warranty due date of AAS car.

TblSERVICE:  It stores the information of cars serviced by AAS.

FldServiceNumber: It is the primary key and uniquely identifying each service.

FldRegistrationNumber: It stores the registration number of car serviced by AAS.

FldMake: It stores the make of car serviced by AAS.

FldModel: It stores the model of car serviced by AAS.

FldManufacturer: It stores the manufacturer of car serviced by AAS.

FldProducedYear: It stores the produced year of the car serviced by AAS.

FldColour: It stores the colour of car serviced by AAS.

FldRequirement: It stores the requirement of car serviced by AAS.

FldServiceDate: It stores the service date of car serviced by AAS.

TblAASCARSERVICE:  It stores the information of cars serviced by AAS and sold by AAS.

FldServiceNumber: It is the primary key and uniquely identifying each service.

FldRegistrationNumber: It stores the registration number of car serviced by AAS.

FldSalesPerson: It stores the Sales person of AAS car.

FldWarrantyDueDate: It stores the warranty due date of AAS car.

TblPart: It stores the information of parts stored in AAS.

FldPartNumber: It is the primary key and uniquely identifying each part.

FldType: It stores the type of part.

FldDescription: It stores the description of part.

FldManufacturer: It stores the manufacturer of part.

FldCost: It stores the cost of part.

FldAvailableQty: It stores the available quantity of part.

TblServiceDetail: It stores the information of services completed at AAS.

FldServiceNumber, FldEmployeeNumber, FldPartNumber: This combination is uniquely identifying each service.

FldLabourCost: It stores the labour cost in AAS service.

FldPartCost: It stores the part cost used in AAS service.

FldServiceDate: It stores the date of AAS Service.

FldStartTime: It stores the start time of AAS service.

FldFinishTime: It stores the finish time of AAS service.

FldQtyUsed: It stores the quantity of the part used in AAS service.

FldDiscount: It stores the discount in AAS service for senior citizens.

FldTotalCost: It stores the total cost of AAS service.

FldClientID: It stores the client who came for AAS service.

TblComplaint: It stores the information of complaints regarding services completed at AAS.

FldComplaintID: It is the primary key and uniquely identifying each complaint.

FldServiceNumber: It stores the Service number of AAS car service complaint.

FldClientID: It stores the client of AAS car service complaint.

FldReason: It stores the reason of AAS car service complaint.

FldComplaintDate: It stores the date of AAS car service complaint

3 NF Relations

TblStaff (FldEmployeeID, FldName, FldStreet, FldCity, FldState, FldPostcode, FldPhone,FldMobile, FldEmail)

FldEmployeeID – Primary Key

TblMechanic (FldEmployeeID, FldTechTradeLevel, FldExpertise)

FldEmployeeID – Primary Key

TblSalespersons (FldEmployeeID, FldExperience)

FldEmployeeID – Primary Key

TblAdministrators (FldEmployeeID, FldQualification)

FldEmployeeID – Primary Key

TblClient (FldClientID, FldName, FldAddress, FldPhone, FldMobile, FldEmail, FldSeniorCitizen)

FldClientID – Primary Key

TblSoldCars (FldRegistrationNumber, FldSalesPerson, FldSellingDate, FldMake, FldModel, FldColour, FldManufacturer, FldProducedYear, FldClientID, FldPrice, FldWarrantyDueDate)

FldRegistrationNumber – Primary Key

FldClientID references TblClient (FldClientID)

TblService (FldServiceNumber, FldRegistrationNumber, FldMake, FldModel, FldManufacturer, FldProducedYear, FldColour, FldRequirement, FldServiceDate)

FldServiceNumber – Primary Key

TblCarService (FldServiceNumber, FldRegistrationNumber, FldSalesPerson, FldWarrantyDueDate)

FldServiceNumber – Primary Key

FldServiceNumber references TblService (FldServiceNumber)

TblPart (FldPartNumber, FldType, FldDescription, FldManufacturer, FldCost, FldAvailableQty)

Fld PartNumber – Primary Key

TblServiceDetail (FldServiceNumber, FldEmployeeNumber, FldPartNumber, FldLabourCost, FldPartCost, FldServiceDate, FldStartTime, FldFinishTime,

FldQtyUsed, FldDiscount, FldTotalCost, FldClientID)

FldServiceNumber, FldEmployeeNumber, FldPartNumber – Primary Key

FldServiceNumber references TblService (FldServiceNumber)

FldEmployeeNumber references TblMechanic (FldEmployeeID)

FldPartNumber references TblPart (FldPartNumber)

FldClientID references TblClient (FldClientID)

TblComplaint (FldComplaintID, FldServiceNumber, FldClientID, FldReason, FldComplaintDate)

FldComplaintID – Primary Key (Zaniolo C. 1982)

References 

Zaniolo C. (1982), A New Normal Form for the Design of Relational Database Schemata

holowczak.com (2016), Converting E-R Models to Relational Models, [Online]. Available: https://holowczak.com/converting-e-r-models-to-relational-models/. [Accessed: 7-August-2017]