COIT20247 Database Design and Development

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 

• Customer can have the address belonging to the category as home or office.
• Employees will only have an official contact number.
• Service car may or may not be bought from AAS.

Business rules

• There can only be three types of employees employed in AAS as sales person, admin or mechanic. An admin is not allowed to play more than one role at a single point of time.
• Service of a car can be performed by an admin but two or more administrators cannot service one car at the same time.
• There can be two or more mechanics and a single sales person associated with a service.
• A service is allowed to make use of more than one part. Also, a particular part can be used in more than one service
• Customers can make one-many number of complaints but one complaint can be lodged by one customer only.

Entity and attribute description

EMPLOYEE – Person working in AAS is an employee of AAS

• employeeNo: An identity that is unique to the employee of AAS
• name – Complete employee name
• roleDescription: An employee may have three roles as mechanic, admin or sales person
• Contact: Number on which the employee can be contacted It is one of the types of the employees in AAS

• tradeLevel – Trade level of the employee type as mechanic
• expertise – Expertise area of the employee type as mechanic It is one of the types of the employees in AAS

• numberOfYears – Number of years the employee has spent in AAS
• qualification – It is the service qualification of the employee type as admin

COMPLAINT – Query or concern of the customer related with the service that is made available by AAS

• complaintID – An identity that is unique to the complaint filed by a customer in AAS
• description – Complete information provided by the customer in the complaint
• complaintDate – Filing date of the complaint
• Admin – The person responsible for the complaint (Relationship with ADMIN).
• Customer – Customer identification number who has filed the complaint (Relationship with CUSTOMER).

CUSTOMER – The entity that has availed the services of the company or the one who has lodged a complaint

• customerID – An identity that is unique to the customer of AAS
• customerName – Complete customer name
• address – Complete customer address
• contactPhone – Number at which the customer can be contacted
• Email – Email address at which the customer can be contacted
• seniorCitizen – Age filter to determine if the customer is over 60 years or not to assign him the category of senior citizen
• Discount – Applicable only for senior citizens

SERVICE – Customer bookings to avail the service

• serviceID – An identity that is unique to the service provided by AAS
• checkInDate Date of check-in to avail the service
• boughtFromAAS – To determine if the car is purchased from AAS
• laborCost – Complete cost of labour
• partCost – Complete cost of the parts
• serviceDate – Service date as provided by AAS
• startTime – Start time for the service
• finishTime – End time for the service

PART_TYPE – Category of the part that is used in the car

• partID - An identity that is unique to the part of the car
• Description – Complete information of the part
• Brand – Complete brand name
• pricePerUnit – Total per unit price
• Stock – Available parts that is number of parts present in the stock

CAR

• regNo - An identity that is unique to the car
• manufacturerName – Complete manufacturer name
• make – Complete name of the make
• Model – Complete name of the model
• producedYear – Production year
• colour – Car colour
• priceOfCar – Total car price
• warrantyDueDate – Date of warranty of the car

References

Belloc, H. (1967). On. Freeport, N.Y.: Books for Libraries Press.

Stevens, P., & Pooley, R. (2008). Using UML. Harlow: Addison-Wesley.