Database Design: Entity Relationship Diagram, Assumptions, Logical and Physical Design

This document explains the database design for an online laundry service with Entity Relationship Diagram, Assumptions, Logical and Physical Design. It also includes the implementation of the database and bibliography.

Running head: DATABASE
Database
Name of Student-
Name of University-
Author’s Note-

1DATABASE
Table of Contents
Entity Relationship Diagram...........................................................................................................1
List of Assumptions and Business Rules.........................................................................................1
Logical Design.................................................................................................................................2
Physical Design...............................................................................................................................3
Implementing Database...................................................................................................................5
Bibliography....................................................................................................................................6

2DATABASE
Entity Relationship Diagram
Figure 1: ERD Diagram
(Source: Created by author using Visio)
List of Assumptions and Business Rules
Assumptions:
1. The system allows many customers to be registered in the system with their unique customer
id and their phone number.
2. One particular customer might have many booking ids as because they can book more than
one time in HDCA.

3DATABASE
3. One single booking will have only one payment_id as because the only one payment is done
against one booking_id.
4. The customer has to specify the service that he wants to take from the company. There are
many services listed in the system that is specified under service_type.
5. The customer can select their preferred timing along with their present data to pick up the
clothes after it has been cleaned or washed.
6. The customer needs to provide their credit card number for the payment to be done to the
company.
7, Along with the credit card number, the customer needs to provide the card_type, the expiration
date and the three digit code on the card. The total amount that is payable by the customer is
also updated in the system.
Business rules:
1. Each of the customer might have many booking ids associated with the company.
2. Each of the booking id will have only one payment id name as payment_id in the system
where the details of the payment and the details of the customer can be retrieved from the
system.
3. Each customer will be assigned with payment id and booking id if they want to take the
service from the system.
Logical Design
Customer (cust_id, cust_fname, cust_lname, cust_street, cust_suburb, cust_phone)

4DATABASE
Booking (booking_id, cloth_type, service_type, cust_preference, cloth_quantity,
preferred_date. Pickup_time, total_amount_payable)
Payment (payment_id, c_card_num, name_on_card, card_type, exp_date, digit_code)
Foreign key (cust_id) references Booking.cust_ID
Foreign key (booking_id) references Payment. booking_id
Physical Design
Column Name Data Type Length Key Required
value
Remarks
booking_id int 15 Primary
key
Yes Primary key which
state the unique value
of the table.
cloth_type Varchar 50 Yes The types of clothes
that the customer
includes in the system
are selected in this
field.
service_type Varchar 50 Yes The company provides
different services and
all the services are to
be specified in this
field.

5DATABASE
cust_preference Varchar 100 No If customer has any
preference, it is to be
specified in this field.
cloth_quantity int 100 Yes The total number of
clothes given by the
customer in service is
noted.
preferred_date date Yes If customer provides
any preferred date, it is
mentioned in this
system, otherwise
availability date for the
company.
Pickup_time time Yes The pickup time for the
customer is specified
by the customer.
total_amount_payabl
e
int 50 Yes Total payable amount
for the taken service is
specified in this field.
cust_id Varchar 50 Primary
key
Yes This is the primary key
in the customer table
by which the customer
is uniquely identified.

6DATABASE
cust_fname Varchar 100 Yes
cust_lname Varchar 100 Yes
cust_street Varchar 100 Yes
cust_suburb Varchar 100 Yes
cust_phone int 10 Primary
key
Yes The phone number is
also considered to be as
the unique field in the
customer table.
payment_id int 15 Primary
key
Yes Payment id is the
primary key in the
payment table.
c_card_num int 16 Yes
name_on_card Varchar 50 No
card_type Varchar 50 No
exp_date date Yes
digit_code int 4 Yes
Implementing Database

7DATABASE
Bibliography
Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database
schema. International Journal of Database Theory and Application, 8(3), pp.15-26.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Oktafianto, M.R., Al Akbar, Y.F., Zulkifli, S. and Wulandari, A.M., 2018. Dismissal working
relationship using analytic hierarchy process method. International Journal of Pure and Applied
Mathematics, 118(7), pp.177-184.
Yang, L. and Cao, L., 2016. The Effect of MySQL Workbench in Teaching Entity-Relationship
Diagram (ERD) to Relational Schema Mapping. International Journal of Modern Education and
Computer Science, 8(7), p.1.
Zhang, F., Ma, Z.M. and Cheng, J., 2016. Enhanced entity-relationship modeling with
description logic. Knowledge-Based Systems, 93, pp.12-32.