University Database Design and Development Report - COIT20247

This report presents a comprehensive database design and development solution for Australasia Auto Services. It begins with an Entity-Relationship (ER) model, visually representing the database structure. The report then outlines the business rules governing the system, detailing how the company manages car maintenance, employee information, and client interactions. Assumptions about the business operations are clearly stated. The report defines entity types, including employees, categories, cars, service jobs, purchased details, service employees, mechanics' performance, replaceable parts, parts inventory, clients, and complaint registers. Each entity's attributes are meticulously described. Furthermore, the report addresses database normalization by identifying and decomposing transitive dependencies to achieve 3rd Normal Form (3NF), ensuring data integrity and efficiency. The report also includes a bibliography of relevant sources.

Running Head: DATABASE DESIGN AND DEVELOPMENT
COIT20247
Database Design and Development (T2 2017)
Assessment item 1— Entity Relationship Diagram (ERD)
[Name of the student]
[Name of the university]

1DATABASE DESIGN AND DEVELOPMENT
Table of Contents
Entity-Relationship model 2
Business rules 2
Assumptions 3
Naming and Defining Entity Types 4
3NF relations 10
Bibliography 13

2DATABASE DESIGN AND DEVELOPMENT
Entity-Relationship model
Figure 1: ER-Model for Australasia Auto Services
(Source: Created by author)
Business rules
The business rule is defining for a conditions which is depended on a particular object to
complete or server a job. The Australasia Auto Services have a car maintenance facility for their

3DATABASE DESIGN AND DEVELOPMENT
client, therefore they need to store all car related issues, required parts, service details as well as
their employees and client details. The identified business rules are followings:
1. An employee of Australasia Auto Services may have one or more service job.
2. An employee must have a service type.
3. For every service job a car details must be store in the database.
4. The purchase history for a car must be store in the database.
5. A service may require one or more mechanics required.
6. If a service required to replace one or more parts, then it also store into the
database.
7. A client details for every service is store into the database.
8. A client can give one or more complains, which also store into the database.
Assumptions
The Australasia Auto Services is a prestigious deader of car, however their main business
is to provide car service to their client in Queensland and Rock Hampton. They have increasing
their services in recent years, therefore they need to build a relational database for managing
their business. The information system is totally based on their car service where they have nine
main entities to provide a car service.
The car service information system requires to store the details of every service done by
their company. A car service includes a car details, client details and service details. The car
details also include a purchase details of that car if it is brought from Australasia Auto Services
or anywhere else. After storing a car details of a particular job, they also need to store number of

4DATABASE DESIGN AND DEVELOPMENT
mechanic required to provide this service and other associative details. Therefore, here a car,
employees, service or job card as well as mechanics are assumed as entities.
The employees have different job role on different sections therefore to separate this
information a new entity is assumed as category of employees. A car service may require parts to
replace or repair, therefore Australasia Auto Services must have an entity as parts to check the
require parts are available or not. A client may have some complain against the job done
previously, therefore it can be assumed that complain is an entity and its related to every client.
Naming and Defining Entity Types
Table: Employee The Australasia Auto Services have some workers to server their
customers.
Name: The name of a worker of Australasia Auto Services.
Address: The location of that worker lives in.
Ph_number: phone number of that worker.
Email: Mail ID of that worker.
Cat_Type: it is an identification number for identify the type of that
worker.
TTLevel: The technical trade level of that worker.
Expertise: Expertise level of mechanics that worker have.
Experience: years of sales experience or maintenance.

5DATABASE DESIGN AND DEVELOPMENT
Qualification: The educational skill of that worker.
Table: Category The type of work done by the employees of AAS.
Cat_ID: The identification number of every category.
Type_Name: The name of the category.
Table: Car The car has come for servicing.
Registration_number: The identification number of every car.
Manufacturer: The name of the company has produce that car.
Make: The name of the car.
Model: The model number of that car.
produced_year: the year of that car manufactured.
Color: the color of that car.

6DATABASE DESIGN AND DEVELOPMENT
Table: Service_Job A service job is an entity that associate car, employee, purchase,
required machines as well as clients. However, it stores all service
related information.
Service_ID: identification number for each service requested.
Date: date of the servicing attempted.
Car_ID: The car identification number that has come for servicing.
Service_Description: Additional information for each servicing.
Purchased_ID: The car purchases identification number that has come
for servicing.
Table: Purchased_Details A car purchased from where need to store here.
Purchased_ID: The purchase identification number of a car.
salesperson_name: The name of the perform who have sold that to a
particular client.
price: The total cost to buy that car
warranty_due_date: The last date of warranty period.

7DATABASE DESIGN AND DEVELOPMENT
Table: ServiceEmployee Associate the number of parts and machine required for any particular
job.
SE_Code: An identification code for associate required service details
and employees.
Employee_ID: An identification number to identify an employee.
Service_ID: An identification number to identify a service.
mechanics_ID: An identification number to identify a mechanics.
Required_parts_ID: An identification number to identify required parts.
Table: Mechanics_perform Store the machine information.
mechanics_ID: identification number.
service_ job: details of service.
labor_cost: total labor cost.
part_cost: total part cost.
Date: date of used machines.
start_time: the time to start servicing.
finish_time: the time to end servicing.

8DATABASE DESIGN AND DEVELOPMENT
Table: Replace_Parts Store the requited parts for a service.
Required_parts_ID: identification number.
parts_ID: parts identification number.
Table: parts Store the parts in inventory.
parts_ID: parts identification number.
Description: details of a parts.
Manufacturer: the name of the company
builds that parts.
Price: Cost of each unit.
available_quantity: the number of parts in
inventory.
Table: Client Store the all client’s information.
Client_ID: Identification number.
Name: Name of the client.

9DATABASE DESIGN AND DEVELOPMENT
Address: location of that client lives in.
Ph_number: Contact number of that person
Email: mail ID of that person.
senior_citizen_Evidence: supportive
document for senior citizen facility.
Service_ID: Identification number for a
service.
Table: complaint_register Associate all client’s information with
complain message.
CR_ID: Identification number.
complain_ID: unique identification number
for each complain.
Client_ID: Identification number.
Table: complaint The complaint entity store all customer
review.
complaint_ID: unique identification number.
service_quality: The quality review of a

10DATABASE DESIGN AND DEVELOPMENT
service job.
expensive_cost: note any cost related issue.
other_possible_problems: note any other
issues.
3NF relations
The 3NF relations are identified from the presence of transitive dependency in between
two relations. Here three entities are identified that has contained transitive dependencies.
Therefore, to eliminates this problem a new relation is derived from the both relation. The below
figures (2, 3, 4) shows how it could be decomposed into 3rd NF.
Figure 2: Showing decomposition of two table into 3rd NF
(Source: Created by author)

11DATABASE DESIGN AND DEVELOPMENT
Figure 3: Showing decomposition of three table into 3rd NF, by introducing a new
relation
(Source: Created by author)
Figure 3: Showing decomposition of two table into 3rd NF, by introducing a new
relation