SCENARIO
A supermarket takes orders from customers in the electronic environment and delivers the orders to their
homes. It is required to design a database that meets the needs described below.
Customer, product, vehicle, driver, and expense are the basic data for the organization. It is required to
keep following data in the database: customer’s number, name, surname and address; the code, name,
type and prices of the products; plate numbers, brands and models of vehicles used for distribution; the
register number, name, surname and age of the delivering driver; code, name and types of the expense of
the drivers that occurs during their service.
Customers give order in electronic environment. It is important to know which customer ordered each
order and when it is ordered. The order is assigned a unique number. One or many items in each order
can be placed in different quantities. These products are put together, placed in a parcel, and the order
number is written on it.
Services are organized to take the orders of the products to the customers' homes and deliver them. Each
service is carried out by a driver, with a vehicle, more than one orders can be delivered within the scope
of each service. For each service, a unique number is assigned. In the database, it is required to keep the
date on which the service is done (the service starts and ends on the same day) and start and end times.
A driver can be given multiple services in one day, with the same or different vehicle. A vehicle can be
used in many services by the same or different drivers on the same day.
Orders assigned a service are delivered if the customer is at home. It is important to know which orders
are delivered and when they are made.
During a service, the driver can make some expenses. These expenses must be stored in database in
terms of amount, time and cost. Examples of expense include tire repairs and petrol intake.
ENTITES
Entity-1 Entity-2 Entity-3 Entity-4 Entity-5 Entity-6 Entity-7 Entity-8
Customer Product Vehicle Driver Expense Order Service Supermarket
ATTRIBUTES
Entity-1 Entity-2 Entity-3 Entity-4 Entity-5 Entity-6 Entity-7 Entity-8
Customer Product Vehicle Driver Expense Order Service Supermarket
Plate Register
Attribute-1 Number Code Code Number Number Name
number number
Attribute-2 Name Name Brand Name Name Date Date Tax number
Attribute-3 Surname Type Model Surname Type Start time Address
Attribute-4 Address Price Age Age Amount End time Web address
Phone Phone
Attribute-5 Description Address Time
number number
Production Phone
Attribute-6 Email Cost
date number
SGK
Attribute-7
number
Attribute-8
Attribute-9
MATRIX DIAGRAM (Finding Relationships)
Entity-1 Entity-2 Entity-3 Entity-4 Entity-5 Entity-6 Entity-7 Entity-8
Customer Product Vehicle Driver Expense Order Service Supermarket
Entity-1 Customer give ???
placed
Entity-2 Product
in
Entity-3 Vehicle do
Entity-4 Driver make carry out
made
Entity-5 Expense made in
by
delivered
Entity-6 Order given by include
by
done carried
Entity-7 Service ??? include deliver
with out by
Entity-8 Supermarket has sell use employ pay get organise
NOTES:
1. There must be reason for defining relationship. The reason is to reach information. If information is not to be used
or if it is already known, there is no need to define relationship. (For example: All entities belong to Supermarket.
So there is no need to define relationship between these entities and Supermarket entity to reach information.
Only reason for defining Supermarket entity can be keeping basic data of Supermarket)
2. At the beginning, a relationship can be assumed in matrix diagram but during the drawing of ERD, better solution
can be noticed and it is replaced with that one. (For example: Relationship is assumed between Driver and Expense
at the beginning. However when we draw the relationship on ERD, we thought that reaching better and more
information can be achieved by defining relationship between Service and Expense instead. By defining relationship
between Service and Expense, expense done in which service and for vehicle can be accessed.)
3. Information must be reached always in one way. Redundancy should not be allowed. (For Example: There is no
need to define Relationship between Customer and Service since the information can be accessed by using
relationships between Customer-Order and Order-Service)
4. Some times, deciding on whether an important thing will be attribute or entity will be an issue. If detail is needed it
is selected as Entity. Otherwise it is included as attribute. (For example: Home is not defined as Entity because for
Supermarket business detail data about Home is not needed. For that reason Home address defined as attribute of
Customer will be sufficient.)
5. For selection of Optionality and Cardinality of relationships and Optionality of attributes, there must be reason.
Some of them provided in the scenario. If they are not given in the scenario, then we have to decide on them
logically. (For example: Relationship between Customer and Order is selected Optional and the reason is explained.
Production Date of Production is selected Optional and reason is provided)
6. We get most of the attributes from the scenario. However, if there are some obvious attributes that we think it
must be also needed, then we can add them as well (For example: Phone number of Driver)
CUSTOMER ORDER
# ID (Number) PRODUCT
# Number # Code
* Name give * Date include
* Surname * Name
* Address * Type
* Phone number placed * Price
given
o Email in o Description
by
o Production date
delivered by
Optionality can be selected either Optional or
Not all products have production date.
Mandatory according to business rule. Here it is
(Milk, cheese have production date but
selected as Optional.
orange, potatoes does not have)
According to this selection, Customer data can be
entered to DB, but it is not necessary to give Order
at that time.
deliver
DRIVER SERVICE EXPENSE
# Register number # Number # Code
carry out
* Name * Date include * Name
* Surname * Start time * Type
o Birthdate (Age) * End time * Amount
carried made * Time
o Address
out by in * Cost
* Phone number
* SGK number
done with
Nonvolatile attribute is preferred.
do
SUPERMARKET
VEHICLE
# Name
# Plate number
o Tax number
* Brand
o Address
* Model
o Web address
o Registered
o Phone number
date (Age)
SERVICE -> VEHICLE
EACH Service must be done with one and only one Vehicle
VEHICLE -> SERVICE
EACH Vehicle may do one or more Service