University Database Project: AAS Database Entity Relationship Diagram

This document presents an Entity Relationship Diagram (ERD) for the AAS database, a project likely for a database design or management course. It begins with an ER diagram and then outlines several business rules, such as the different types of employees (salesperson, admin, mechanic) and the constraints on their roles. The document then details the assumptions made during the design process, such as customer addresses and employee contact information. The core of the document is the entity and attribute descriptions, which define the various entities (EMPLOYEE, MECHANIC, SALESPERSON, ADMIN, COMPLAINT, CUSTOMER, SERVICE, PART_TYPE, CAR) and their associated attributes (e.g., employeeNo, name, roleDescription for EMPLOYEE; customerID, customerName, address for CUSTOMER). Each attribute is clearly defined, providing a comprehensive overview of the data model. The document concludes with a list of references, indicating the sources used to inform the design and approach.

ASSESSMENT 1
Entity Relationship Diagram
[Name]
[Number]

Table of Contents
ER diagram.............................................................................................................................. 2
Business rules...........................................................................................................................3
Assumptions.............................................................................................................................3
Entity and attribute...................................................................................................................3
References................................................................................................................................6
2

ER diagram
3

Assumptions
 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.
4

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
MECHANIC – 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
SALESPERSON – It is one of the types of the employees in AAS
 numberOfYears – Number of years the employee has spent in AAS
ADMIN – It is one of the types of the employees 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
5

 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
6

References
Belloc, H. (1967). On. Freeport, N.Y.: Books for Libraries Press.
Stevens, P., & Pooley, R. (2008). Using UML. Harlow: Addison-Wesley.
7