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

Business rules followed in the AAS ER diagram

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]