Erd

  • Category:
    Other
  • Document type:
    Assignment
  • Level:
    High School
  • Page:
    1
  • Words:
    731

8DATABASE

EASY DRIVE AUTO SERVICE DATABASE

Table of Contents

3Introduction

3Entity Relation Diagram

4Assumptions

5Relational Data Structures

5Relational Database Schema

7References

Introduction

Effective management of clients and resources is one of the key objectives in any organizations. One of the best ways of doing this is by establishing a database that can be sued for any transaction between the client and the organization. It is with this in mind that Easy Drive Auto Service has commissioned the development of such a database to handle information pertaining to the servicing of the customers’ vehicles. This report details the fundamental items that are required and how the database will be executed to ensure that it acts as the enabler for effective management.

Entity Relation Diagram

The following figure illustrates the Entity Relation Diagram (ERD) that models the problem at hand:

erd

erd 1erd 2erd 3

erd 4erd 5

erd 6erd 7erd 8erd 9

erd 10erd 11

erd 12

erd 13erd 14

erd 15

For the relationship between the Customer and Vehicle tables, a Second Normal Form of normalization has been achieved. This is due to the fact that one customer can own more than one vehicle and hence the relation between them is one to many (Kent, 1983, pp. 120-125). A similar level of normalization exists in the relation between the customer and the Booking tables since one customer can make several bookings- either for one vehicle or multiple vehicles. The type of cardinality between the two entities is therefore one to many also. There is the relation between the Booking and Service tables whose cardinality can be best described as many to many with the level of normalization being the Second Normal Form. This is because for one booking, a customer can request many services on one or more vehicles.

On the other hand, several bookings can be made for a single service on the same date. The relation between the User and Booking entities is also of Second Normal Form with a many to many cardinality. The company has many users who have access to the information regarding multiple bookings made by customers. However, no direct relationship exists between it and the other entities. Finally, there is the relation between the Vehicle and Record entities. Its normalization is of the First Normal Form as each vehicle can only have one record depicting attributes such as the number of recalls it has had, and the previous servicers that have been done on it. The logical cardinality between the two is therefore one to one.

Assumptions

In developing the ERD, some assumptions were made. These include:

  • A customer may own more than one vehicle but no vehicle can be owned by two or more individuals

  • A single service can be requested for one or more vehicles by the same customer as well as several services for a single vehicle

  • The users have access to only the bookings made but not the underlying entities. They can however view all the bookings made on any date

  • Based on the service record, a discount on the cost of servicing a given vehicle can be made during booking and deducted from the total booking cost

  • The vehicle recalls will be tracked under the Records table whereby one vehicle can have only one track record whereas different vehicles cannot be attributed to a single record.

Relational Data Structures

The ERD can be broken down and translated into the following relational data structures:

VEHICLE (VehicleID, CustomerID, RecordID,
RegistrationNumber, Make, Model, YearofManufacture)

CUSTOMER (CustomerID, FirstName, Surname, PhoneNumber)

BOOKING (BookingDate, VehicleID, ServiceID, BookingCost, EndDate, NotificationDate)

SERVICE (ServiceID, Name, Description, ServiceParts, Cost, ExpertID, ExpertName)

RECORD (RecordID, VehicleID, BookingDate, Recalls)

USER (UserID, BookingDate, Login, Password)

Relational Database Schema

The database can be further interpreted by use of a relational database schema as illustrated below:

Description

VehicleID

CustomerID

RecordID

RegistrationNumber

YearofManufacture

varchar(30)

varchar(30)

varchar(30)

primary key

CUSTOMER

CustomerID

PhoneNumber

varchar(30) varchar(30)

primary key

VehicleID

ServiceID

BookingCost

NotificationDate

Primary key

primary key; foreign key reference to Vehicle.VehicleID

primary key; foreign key reference to Service.ServiceID

Completion of servicing; Format: dd/mm/yyy

Sending of alerts; Format: dd/mm/yyy

ServiceID

Description

ServiceParts

Varchar (30)

Varchar (100)

Varchar (50)

Primary key

RecordID

VehicleID

Primary key

primary key; foreign key reference to Vehicle.VehicleID

primary key; foreign key reference to Booking.BookingDate

The number of recalls it has had

UserID

Password

Varchar (30)

Varchar (30)

Primary key

primary key; foreign key reference to Booking.BookingDate

Reference

Kent, W (1983). A Simple Guide to Five Normal Forms in Relational Database

Theory. Communications of the ACM, Vol. 26, pp. 120-125