COIT20247 Database Design and Development

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.

Business rules

The proposed AAS system has following business rules:

1. A customer exists only if a complaint is filled by this customer. This shows that CUSTOMER is a weak entity.
2. A part exists only if it is used by at least one service i.e. PART is weak entity.
3. A part can be used in more than one service and one service can use more than one part.
4. A service required at least one mechanic but may not require a salesperson.
5. A car can be attended by only one admin at a time.
6. An employee can play only one role at a time i.e. mechanic, admin or salesperson.

Assumptions

The proposed AAS system has following assumptions:

1. An employee can have only one contact number.
2. A customer can have only address.

Entity and attribute 

EMPLOYEE – Employee refers to the person that is employed with AAS

• employeeNo: It is the attribute that uniquely identifies each of the employee working in AAS
• name – It comprises of first name, middle name and last name of the employee
• roleDescription: An employee may have different roles and responsibilities that are defined in this attribute such as administrator, mechanic, sales representative etc.
• Contact: Contact information of the employee in the form of the contact number 

MECHANIC – Mechanic is one of the categories of the EMPLOYEE type

• tradeLevel – The level associated with the mechanic’s trade
• expertise – Particular area of expertise of the mechanic 

SALESPERSON – Salesperson is one of the categories of the EMPLOYEE type

• numberOfYears – The total experience of the sales person in the form of the total years Admin is one of the categories of the EMPLOYEE type 

• qualification – The service qualification that is associated with the admin 

COMPLAINT – Complaint is the customer issue or query regarding the service that is being provided

• complaintID – Every customer is assigned a complaint ID that is a unique identification number
• description – Details of the complaint filed by the customer
• complaintDate – The date on which the customer lodges a complaint regarding the service
• Admin – The admin that is responsible for handling of the complaint (To manage the relationship with the customer)
• Customer – The identification number of the customer who has filed the complaint regarding the service (To manage the relationship with the customer) 

CUSTOMER – Customer is the entity that lodges the complaint regarding the service that is provided or get’s the car serviced by availing the offerings of AAS

• customerID – Each of the customer is assigned a unique identification number
• customerName – The first, middle and last name of the customer
• address – Complete address details of the customer
• contactPhone – Contact information of the customer in the form of contact number
• Email – Email information of the customer in the form of email id
• seniorCitizen – The attribute to understand if the age of the customer is over sixty years
• Discount – To be provided and applied if the age of the customer is above 60 

SERVICE – It is the booking that is made by the customer to get the service of the car done

• serviceID – Each service is identified by a unique number
• checkInDate The date on which the car is brought for the service
• boughtFromAAS – It identifies whether the car has been brought from AAS or not
• laborCost – Total labour cost of the car
• partCost – The complete part cost associated with the car
• serviceDate – The date at which the service is done by the company
• startTime – Time of start of the service for the car
• finishTime – End of the service for the car 

PART_TYPE – It is a type of the parts of a car that are used in the service of the car

• partID – Each part is uniquely identified by an identification number
• Description – The details of the part that is used
• Brand – Name of the brand to which the part belongs
• pricePerUnit – Per unit price of the part
• Stock – The number of parts available

CAR

• regNo – Each car is uniquely identified by the registration number
• manufacturerName – The complete name of the manufacturer of the car
• make – Complete name of the car make
• Model – Complete name of the model of the car
• producedYear – The production year association with the car
• colour – Name of the colour of the car
• priceOfCar – Price information of the car
• 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.