Developing An ER Model For AAS Information Requirements

Business Rules

Based on the case study defined the following business rules were derived.

• AAS deals with two types of money generating events, i.e. sale of cars and servicing of cars. Sale of cars has a different stock of cars from where sales are done by a salesperson while servicing of cars is done for cars previously bought from AAS and cars not bought from AAS.
• A client makes a booking for a service and the booking is recorded b an administrator. After the car is brought the details about the car are recorded. The booking creates a job for one or many mechanics who are supposed to work on that car. During the servicing of the car, parts may be used. The parts belong to the business thus the mechanic has to record which item was used and to do which job so that the cost of the parts can be established.
• Once a car is serviced the client has to pay a charge reflecting the labor cost and the cost of the parts used to do the service.
• A client can make complaints for a single service job and the complaints are recorded by the administrator. The complaint has an issue and an expected outcome of the resolution of the issue.

While modelling the entity relationship diagram the following assumptions were made;

• A service job leads to payment by the client where by the payment made for a service job equals the cost of labor added to the cost of parts during the service.
• Employees who are administrators can have more than one qualifications
• Employees who are mechanic can have more than one area of expertise.
• All services are predetermined by the company so during booking the client has to make a booking for a service that is in the list of service s offered by the business. The client cannot book a service which is not done by the business.
• A vehicle is of a certain model and that model has features that distinguish it from other models.

This entity is used to store details of the staff employed by AAS. It has the following attributes;

• employeeNO- this is the unique attribute of the entity which is used to identify an employee. Every employee has a unique employeeNO.
• Name- this attribute is used to hold the name of the employee working in AAS
• Email- this attribute is used to store the email address of the employee working in AAS.
• Phone- This attribute is sued to store the phone number of every employee working in AAS
• Address- this attribute is sued to hold the address of the employee working in AAS.
• Type- the type attribute is used to identify an employee whereby there are three types of employees in AAS> there is the administrator, the salesmen and the mechanics.

Apart from the attributes of employee contained in the staff entity, employees who are of mechanics have extra attributes that distinguish them from other type of employees in AAS. This entity has the following attributes.

• infoID- this is the unique attribute in entity. infoID attribute eventually becomes the primary key when the entity is converted to  table. This unique attribute is the unique identifier used to identify other attributes in the entity.
• employeeNO- this attribute is the foreign key that references the staff entity. This attribute identifies the employee who has the other attributes in the entity.
• Trade_level –this attribute is used to store the trade level of the mechanic in the mechanic info entity.
• Expertise- this attribute is sued to store the expertise or the skills of the a mechanic.

This entity has the attributes that distinguish a salesman from  an administrator and a mechanic. The entity has the following attributes.

• infoID- this is the unique attribute in the entity and can be used as the unique identifier to reference a row in the entity.
• employeeNO- this attribute is a foreign key and references the staff entity to identify the specific employee.

This entity holds attributes that distinguish an administrator from a salesman or a mechanic. The entity has the following attributes;

• qualificationID- this is the unique attribute in the entity and is used as the unique identifier in the entity to identify the other attributes.
• Qualification- this attribute is used to identify the qualification itself. The qualification attribute could be the name of the qualification
• employeeNO- this attribute is used as the foreign key in the entity to reference the staff entity.

The clients entity is sued to hold attributes of clients of AAS. These clients are the customers who could either be a customer who has bought a new car or a customer who has booked for a service in the company. The entity has the following attributes

• ClientID- this is the unique attribute in the entity and is used to identify the client. It is the primary key.
• Name- this attribute holds the name of the client
• Address- this attribute holds the physical address of the client.
• Phone- this attribute holds the phone number of the client
• Email- this attribute holds the email of the client.
• Is_senior- this attribute holds a yes or no value to show whether a client is a senior citizen or not. Senior citizens are entitled to a discount.

This entity holds details of every booking that is done by a client requesting for a service. The booking is made and the details of the booking recorded by an administrator. The entity has the following attributes.

• bookingID- this attibute is the unique identifier of every booking that is made by a client. The bookingID attribute is used to identify a booking as it’s the primary key.
• serviceID- the serviceID attribute is a foreign key referencing service entity. Based on the assumption that all services offered by AAS are predetermined then serviceID is used to reference the service that the client is booking.
• clientID – this attribute is a foreign key and is used to reference the client entity to identify the client who is booking on a service.
• Service_carNO- this attribute is a foreign key and refereces service_car entity.
• employeeNO- this is a foreign key attribute and references staff entity to identify the administrator who recorded the booking.
• Is_car_AAS- this attribute is used to hold details of whether a car was purchased from AAS or not.

The sales entity is used to hold details about every sale that is done by a salesman. The entity has the following attributes.

• SaleID- this attribute is the unique identifier of the sales entity and is used to identify every sale that takes lace since it’s the primary key.
• Price- this attribute holds the price that is a sale is awarded.
• Warranty- the quantity attribute is used to hold the warranty information for the car sold
• Sale_carID- this attribute is a foreign key and is used to reference sales_car entity to identify the car that was sold
• ClientID- this attribute is a foreign key and is used to reference the client’s entity to identify the customer who bought the car.

This entity is sued to hold details of cars that AAS offers for sale. Cars for sale are distinguished and recorded on an different entity from the cars brought in for service. The following are its attributes;

• Sales_carID- this attribute is the unique identifier of the entity.THe sales_carID is the primary key that is ued to identify which car is for sale.
• Quantity- this attribute holds details of how many cars are there or the number of cars.
• ModelNO- this attribute is a foreign key and is used to reference the model entity to identify which model the car belong to.
• Color- this attribute is used to hold the color of the car up for sale.

The model entity is sued to record details about a certain model of a car. The car can either be a car for sale or a car brought in for service. The entity has the following attributes.

• modelNO- this attribute is the unique identifier and I used to identify a certain model.
• Name- this attribute holds the name of the model.

The service job entity holds details about the service job that is done when a booking is done by a client. The service job will hold the details of how the service was done. The entity has the following attributes.

• jobID- this attribute is the unique attribute and is used to identify a certain job.
• BookingID- this attribute is a foreign key and is sued to reference the booking entity to identify the booking associated with the job.
• employeeNO- this attribute is a foreign key and is used to identify the administrator who recorded the job.
• Labour_cost- this attribute is used to hold details of the cost of labor for every job done.
• Part_cost- this attribute is used to hold the details of the cost of the parts that were used while doing the service
• Start_time- this attribute is sued to hold details of when the job started.
• Finish_time- this attribute is sued to hold details of when the job ended.

This entity is used to record details of every service undertaken by AAS. The entity has the following attributes.

• serviceID- this attribute is the unique attribute and identifies every service in the service entity.
• Name- this attribute is used to hold the name of the service.
• Description- this entity holds the description of the service.

This entity holds details of the car brought in for service by a client. The following are its attributes;

• Service_carID- this attribute is used the unique attribute and is used as the primary key to identify a car brought in for service.
• modelNO- this attribute is a foreign key and references the model entity to identify the model of the car.
• Color- this attribute holds the color of the car brought in for service.

This entity is used to hold details of the part used for a certain service job. The entity has the following attributes;

• Job_partID- this is the unique attribute and identifies the part for a specific job.
• jobID- this attribute is a foeign key and references the part entity to get details of the part used for a certain job.
• Quantity-used- this attribute is used to hold the quantity of the parts used.

This entity is used to hold details of the parts used for various services done by AAS. The entity has the following attributes;

• partID- this attribute is the unique attribute and identifies any part in stock as it is the primary key.
• Description- this attribute holds the description of the part
• manufacturerID- this attribute is a foreign key and is used to reference manufacturer who did the job.
• Quantity- this attribute is used to hold the quantity of the part in stock
• Brand_info- this attribute hold details about thhe brand of the part.
• Unit_price- this attribute holds details about the price at which every part is bought for.

This entity holds details about manufacturers who manufacture parts used by AAS to undertake various services. The entity has the following attributes;

• manufacturerID- this is the unique attribute in the entity and is used to identify a manufacturer.
• Name- thisattribute holds the name of the manufacturer.

This entity is used to old details of the mechanics who worked on a certain job. The entity has the following attributes;

• jobID- this attribute is a foreign key and is sued to reference the job entity to identify the job.
• employeeNO- this attribute is a foreign key and references the staff entity to identify the mechanic working on a certain job. A combination of jobID and employeeNO attribute becomes the unique identifier of the entity.

The payments entity is used to hold details about a certain payment that was done for a service. The entity has the following attribute;

• paymentID- this attribute is the unique attribute and identifies a payment done for a service.
• Amount- this attribute is used to hold the amount that was made for a certain service.
• Date- this attribute is sued to record the date that the service was paid for.
• jobID- this attribute is a foreign key and is used to reference the service_job entity to identify the service job being paid for.

This entity is sued to hold complaints issued to AAS for a service done for a client. The attribute has the following attributes;

• complaintID- this is the unique atti=ribute in the relation and is used to identify a complaint.
• Issue- this attribute holds details of the issue brought up by the client regarding the service.
• Outcome- this attribute holds details of the outcome resulting from the complaint
• employeeNO- this attribute is a foreign key and references staff attribute to show the employee who recorded the complaint.
• jobID- this attribute is a foreign key and references service_job entity to show which service job the client is complaining about.
• cleintID- this attribute is a foreign key and is sued to reference theclients attribute to show which client made the complaint. 

All the entities named above are in 3NF because all the conditions that satisfy 3NF hold for the entites. These conditions are;

• No repeating groups
• No partial dependencies should exist in any entity
• No transitive dependency should exist in any entity.

Reference

Kaula, R., 2007. Normalizing with Entity Relationship Diagramming. TDAN.COM. Available at: https://tdan.com/normalizing-with-entity-relationship-diagramming/4583 [Accessed August 5, 2017].