COIT20247 Database Design and Development Assignment

COIT20247 Database Design and Development Assignment

COIT20247 Database Design and Development Assignment

Entity Relationship Model

Entity Relationship Diagram

Entities and their attributes

Staff

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:

Salesperson

Experience:The experience of employee in years

AdministrativeStaff

Qualification:The highest qualification of the employee

Mechanic

Tech_Trade_Level: Level of technical trade.

Expertise: expertise in the concerned field of work.

Client

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.

Service Job

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.

COIT20247 Database Design and Development Assignment

Car

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.

Other Cars

This subtype entity does not have any additional attribute to its super type entity.

No Attributes

Part_Types

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.

Relationships

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.

Relationship Name

Between

Relationship Cardinality

Relationship Optionality

comments

Recorded by

Mechanic - Service Job

M:N

(1,N) - (0,N)

 

Overlooks

Mechanic - Part_Types

M:N

(0,N) - (0,N)

 

Is for

Car - Service Job

1:M

(1,1) - (1,N)

 

Owns

Client - Car

1:M

(1,1) - (1,N)

 

Files

Client - Complaint

1:M

(1,1) - (0,N)

 

Sold

Salesperson - Purchased_From_AAS

1:M

(1,1) - (0,N)

 

Analyses

Administrative Staff - Complaint

1:M

(1,1) - (0,N)

Weak Relationship

Resulted in

Service Job - Complaint

1:M

(1,1) - (0,N)

Weak Relationship

Business Rules

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.

Assumptions

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.

Acknowledgements

This assignment was completed using the COIT20247 course materials along with following websites:

https://www.smartdraw.com/entity-relationship-diagram/
https://www.tutorialspoint.com/dbms/er_diagram_representation.htm
http://creately.com/blog/diagrams/er-diagrams-tutorial/
http://www.studytonight.com/dbms/er-diagram.php