Delivery in day(s): 4
COIT20247 Database Design and Development Assignment
Entity Relationship Model
Entities and their attributes
This entity consists of all the details related to a staff member. Also, it has a composite attribute “Contact_Info” that further can be separated to form a new entity.
Employee_ID (Key element): This is used to uniquely identify all the staff members.
Salary: The salary of employee.
Contact_Info (Composite attribute of “Address”, “Phone”, “Email”):The contact information of the employee.
Following are the subtype attributes of Staff:
Experience:The experience of employee in years
Qualification:The highest qualification of the employee
Tech_Trade_Level: Level of technical trade.
Expertise: expertise in the concerned field of work.
This entity consists of all the important details related to the clients of AAS
Client_ID (Key element): This is used to uniquely identify all the clients.
Residential Address:It store the text information of address of the client
Email: The email address of client
Contact Phone:Phone number of client.
Senior Citizen: This attribute saves simple yes or no values to determine whether a client is senior citizen or not.
This entity covers all the required data related to the service request.
Job_ID (Key element):The key element to distinguish between various service jobs.
Labour_Cost:The total payable cot of labour.
Total_Part_Cost: The total cost of all the part replaced during service.
Service_Date: The start date of service.
Start_Time: The start time of service.
Finish_Time: The finish time of service.
This entity keeps the information related to a car. It has two subtype entities to separate the cars sold by AAS from other cars.
Registration No (Key element):The registration number of a car.
Manufacturer:The manufacturer company of the car.
Make:The brand name of the car for example, Honda.
Model: The model of the car.
Production year: The year, when the car was produced.
Colour (Multi-valued attribute):Colour of the car. It can be red, black & white etc.
Following are the subtype entities of Car:
Purchased From AAS
Salesperson_Emp_ID: The employee ID of the salesperson involved in sale.
Warranty_Due_Date: The due date of warranty for the car.
Selling_Price: The selling price of the car, at which it was sold.
This subtype entity does not have any additional attribute to its super type entity.
This entity stores all the information related to the spare parts required in repairing of cars.
Part_ID (Key element): This attribute uniquely distinguishes all the part types.
Manufacturer/Brand:Manufacturer or the brand name of the company that has produced the part type.
Description:A short description related to the implementation plan of the part type.
Price:the price of the part type.
Available_Quantity:The currently available number of individual part type in stocks.
Complaint (Weak Entity)
This entity is a weak entity that stores all the information related to a complaint. In my complaint table, the “Admin_staff_ID” and “Job_ID” form the composite key that is used to identify all the complaints uniquely.
Admin_staff_ID (Composite key element, Foreign Key):it is used along with “Job_ID” to distinguish between all the complaints.
Job_ID (Composite key element, Foreign Key):it is used along with “Admin_staff_ID” to distinguish between all the complaints.
Complaint_Reason (Multi-valued attribute):It store the reason for complaint, it may have multiple values.
In the following table, the cardinalities and optionality of all the relationships are given in the sequence of the entities connected by them. “Analyses” and “Resulted in” relationships are weak relationships or identifying relationships because these relationships provide the weak entity “Complaint” it’s composite key elements.
Mechanic - Service Job
(1,N) - (0,N)
Mechanic - Part_Types
(0,N) - (0,N)
Car - Service Job
(1,1) - (1,N)
Client - Car
(1,1) - (1,N)
Client - Complaint
(1,1) - (0,N)
Salesperson - Purchased_From_AAS
(1,1) - (0,N)
Administrative Staff - Complaint
(1,1) - (0,N)
Service Job - Complaint
(1,1) - (0,N)
1. An employee can either be a mechanic, a salesperson or an administrative staff.
2. Each employee is identified by a unique Employee ID.
3. Each service job is assigned a unique identifier number.
4. Cars purchased from AAS have additional attributes including salesperson name and warranty due date.
5. Each car purchased from AAS is sold by a salesperson.
6. One or more mechanics may record a service job.
7. Each complaint is analysed by an administrative staff member.
8. The mechanics update the quantity of spare parts after every change in stock.
a. One client may own multiple cars.
b. One client may file multiple complaints.
c. Multiple complaints can be file for one service job.
d. A car can be brought multiple times for service.
e. Each salesperson can sell any number of cars.
f. Each administrative staff can analyse any number of complaints.
g. A car can be multi coloured.
h. A complaint can be filed for multiple reasons.
i. A complaint can be identified uniquely with the combination of Admin_staff_ID and Job_ID.
j. A staff member can only serve for one department. For example, a salesperson cannot be an administrative staff simultaneously.
This assignment was completed using the COIT20247 course materials along with following websites: