Database Design and Implementation

This report discusses the identification of entities, attributes, data types, and relations in the current system using Entity-Relation modelling. It includes a table entity description along with the conceptual and logical design of the system. The report also discusses the implementation of the database in MS Access.

Running head: DATABASE DESIGN AND IMPLEMENTATION
Database Design and Implementation
Name of the Student
Name of the University
Author Note

1DATABASE DESIGN AND IMPLEMENTATION
Table of Contents
Introduction................................................................................................................................2
ER-Diagram...............................................................................................................................2
Assumptions...............................................................................................................................3
Logical Design...........................................................................................................................4
Physical Design – Column Specification...................................................................................5
Conclusion..................................................................................................................................8
References..................................................................................................................................9

2DATABASE DESIGN AND IMPLEMENTATION
Introduction
Database design is a very important phase of system development. It can be done
using different available modelling techniques. The modelling starts with the conceptual
design to achieve physical design (Coronel and Morris 2016). The Hassle free Dry Cleaners
Australia, provides services related to the ironing, dry cleaning, garment preservations etc.
HDCA's owner Victor collects all the data required in the process of function the Company.
This report intends to discuss the identification of the entities, attributes, data type and
relation in the current system using the Entity-Relation modelling. The ERD model will help
the implementation of the database in MS access. The report also includes a table entity
description along with the conceptual, logical design of the system.
ER-Diagram
The ERD is developed to establish the entities that exist in the system along with the
useful attributes and their relationship. The database is the most integral part of any system
(Thalheim 2013). The conceptual model of any database hardly shows any details about the
structure of the database in the physical level (real implementation). The conceptual model
consists of entity attribute and relationship only (Hoang and Van Nguyen 2013). Figure 1
shows the conceptual model of the current HDCA system.

3DATABASE DESIGN AND IMPLEMENTATION
Figure 1: Conceptual Entity-Relationship Diagram
(Source: created by author)
Assumptions
The ERD is developed with the help of the Business process of HDCA (Hassle-free
Dry Cleaners Australia) Company. Before the design development, some assumptions have
been made to match with the HDCA's business processes. The assumptions are as follows:
1. As the company’s main process is customer service, the customer details should be
stored in the system.
2. Each customer can have minimum one or maximum many services order.4

4DATABASE DESIGN AND IMPLEMENTATION
3. Each order is associated with one and only one customer.
4. The company provides the preferred date for completion of the service according to
the preferred service, the number of cloths and type of cloths of the customer.
5. Payment is made using the credit card of the customer, and the credit card details are
stored in the system. Each customer can have one or more than one credit card.
6. Each credit card is associated with one and only one customer.
7. One order may include one or more than one services to be performed.
8. One service can be associated with one or many orders.
9. Payment and service receipt is handed to the customer after the completion of the
service. Hence, each order can have one and only one receipt.
10. Each receipt is associated with one and only one order id.
Logical Design
Whereas the logical model provides more information than conceptual design. It
resolves and defines the structure of the data, its elements and set the relations between the
tables. The benefit of developing the logical model that it provides a base to the physical
development of the database (Quan 2013). At this level of modelling the keys are identified
and defined to set the relationships. Also, complex relationships are normalised in this model.
For the current case, the conceptual design of the HDCA system has been normalised into the
BCNF form by eliminating the by introducing a new entity that does not violets the BCNF
rules of functional dependencies.

5DATABASE DESIGN AND IMPLEMENTATION
Figure 2: Logical Entity-Relationship Diagram
(Source: created by author)
Physical Design – Column Specification
The physical provides each of every required information of the database for the
development. It also provides the abstraction to the database and helps in establishing the
schema (Sacco 2013). In this model, the data types of the attributes, their length and
optionality are defined properly.

6DATABASE DESIGN AND IMPLEMENTATION
Figure 3: Physical Entity-Relationship Diagram
(Source: created by author)

7DATABASE DESIGN AND IMPLEMENTATION
Table
Name
Attribute
Name
Description Type leng
th
Req’
d
PK/
FK
FK
Referenced
Table
order
orderid Order id int 10 Y PK
Typeofcloths
Type of cloths the
order consists
varchar
50
Y
quantity
Quantity of the
clothes in order
varchar
3
Y
Preferreddate
Preferred date for
the work done
date Y
pickup Pickup time datetime Y
custid
Customer’s unique
id
int
10
Y FK customer
receiptnumbe
r
Receipt number for
the service order
Int Y FK receipt

8DATABASE DESIGN AND IMPLEMENTATION
Conclusion
The database implementation is done step by step. The design or schema of the
database must exist to implement the purpose of the database. The design helps in
understanding the relationship between the tables and attributes, along with the optionality of
the data. The report successfully describes the attributes, data types and relationships across
the tables. The table creation of the developed ERD is also successfully implemented in MS
Access.

9DATABASE DESIGN AND IMPLEMENTATION
References
Coronel, Carlos, and Steven Morris. Database systems: design, implementation, &
management. Cengage Learning, 2016.
Hoang, Quang, and Toan Van Nguyen. "Extraction of a temporal conceptual model from a
relational database." IJIIDS 7, no. 4 (2013): 340-355.
Quan, X. I. N. G. "Eliminating Process of Formalization in Logical Database Design
[J]." Computer Systems & Applications 6 (2013).
Sacco, Giovanni. "Process for physical database design based on transaction workload."
U.S. Patent 8,566,318, issued October 22, 2013.
Thalheim, Bernhard. Entity-relationship modeling: foundations of database technology.
Springer Science & Business Media, 2013.