Database Implementation: Walk & Wag Enterprise - Design and SQL

This project provides a comprehensive database implementation solution for the 'Walk & Wag' enterprise, which aims to digitize its dog walking services. The report begins with an introduction to database implementation and design principles, followed by a detailed entity-relationship (ER) diagram illustrating the entities and their attributes. The ER diagram is then enhanced (EERD) to create logical and physical models. The project further delves into normalization, specifically 3NF, and discusses the circumstances for denormalization. It also explores the use of a Database Management System (DBMS), such as Microsoft SQL Server, to implement the solution, including creating tables and establishing relationships. Finally, the project includes SQL table creation statements, demonstrating the practical application of the database design.

Database Implementation
1

Contents
INTRODUCTION...........................................................................................................................3
TASK...............................................................................................................................................4
CONCLUSION..............................................................................................................................36
RECOMMENDATION.................................................................................................................36
REFERENCES..............................................................................................................................38
2

INTRODUCTION
Database implementation is based on the process or method of installation of database
software, configuration, running, customization and integrating with different applications.
Database design is a collection of different process that provide the better facilities in term of
designing, development, implementation as well as maintenance of enterprise data. Usually, it
will designed a database which become easier to maintain and improve data consistency. These
are become as cost effective in storage of data within disk space. The purpose of database design
is to produce both physical as well as logical design model and then proposed as new database
system.
In this report, it is mainly focused on the case scenario related the “Walk & Wag” enterprise.
This should include all detail about the dog walking to next level by building an online
operation. This documentation will discuss about the data model in the form entity relationship
model. This diagram will help for representing all kind of entities and their attributes. By using
Enhanced ERD, it will produce logical and physical model of ER on the basis of case study.
However, it will discuss about the normalization process such as 3 NF, also explain the De-
normalize process. Moreover, this report will describe about the database management system
and their importance within implementation to propose a better result or outcome. Analysing the
Structure query language (SQL) that will support for performing the different commands and
generate accurate outcome.
3

TASK
Question-1
A). develop a data model in the form ER diagram, constraints and list of entities showing
attributes and identifiers
Figure 1 Entity Relationship Diagram
Entity Relationship Model-
An entity relationship diagram is based on model that displays the relationship of entity
set in which stored in the database (Akbar, Silvana and Jannah, 2020). Another words, ER
diagram can help to explain the logical structure of database. ER is mainly created on the basis of
three concepts such as entities, attributes and relationships. It is mainly contain the different
symbols that use to represent the relationship with one entity to another. ER diagram looks
similar to flow chart and include many specialised symbols and its meaning which make model
unique. The purpose of ER diagram is to create or develop as entity framework infrastructure.
4

ER model stands for entity relationship model which is primarily consider as high level
conceptual data model. It is helping to the developer to analyse data requirement and then
produce well designed database. Sometimes, it represents entities and relationship between them.
In order to create ERM model that needs to be consider the best practice before implementing the
database system.
Entities Attributes
Dog walker Id (PK)
Name
Address
Email
Mobile number
Frequency
Dog_walking_experience
Hourly_rate
Limitations
Job_numbers (FK)
Dog_number (FK)
Job detailed Job_number (PK)
Job_type
Job_hour
Dog Dog_number (PK)
Dog_type
Sex
Age
Dog-medical_condition
Membership Member_id (PK)
Member_name
5

Member_phone
Member_email
Hourly_fees
Dog owner id (PK)
Address
Email
Mobile_number
Age
City
Frequency
Duration of walk
Walking_location
Walking_time
Member_id (FK)
Job_number (FK)
Dog_number (FK)
Table: 1
In above table, it has been represented the different entities and their attributes, which can
establish a relationship between one or more tables. A relationship depends upon the foreign key
that can establish interconnection between two tables.
For Example –
Relationship between Dog and Dog Walker tables
Dog number is primary key in Dog table but it also exit within Do walker table, it means
that show as foreign key. In this way, it has been identified that primary key turn into foreign key
into another relational tables. Therefore, Dog walker can select or chose particular dog on the
6

basis of preferences. In future, Dog walker has an experience for particular Dog so that it become
easier for selecting themselves.
Relationship between Dog owner and Dog
Dog number is primary key in Dog table but it also exit within Do owner table, it means that
show as foreign key. In this way, it has been identified that primary key turn into foreign key into
another relational tables (Hewasinghage, Abelló and Zimányi, 2020). Therefore, Dog owner can
select a particular dog and also provide the waking services. Sometimes, Dog owner may prefer
to hire or recruit as experienced dog walker to provide better walking services.
Relationship between Dog owner and membership-
Member_id is primary key in membership table but it represent as foreign key into Dog
owner tables. It means that member id can establish as relational between two tables. Therefore,
Dog owner can perform the different tasks, to allocate the particular members for dogs walking
services.
b). Determine the opportunities to use Entity sub-typing concept and develop EERD (Enhanced)
According to the ER Model, there are few entities in data model which may share
common properties or attribute within themselves. On the basis of attributes, it will be
categorised the sub-typing. It is one of subgroup entity and have unique attributes but they will
be stored within different forms.
7

Figure 2 Enhanced Entity Relationship Model
According to the given scenario, it has been developed the extended Entity relationship model
that incorporate the extension to original ER model. This kind of EER model is create a more
accurate database schemas which reflects on the data properties and constraints precisely. In
order to include all kind of modelling concept and also display in EER schema.
The primary use of enhanced Entity relationship model is to improve the own
understanding towards overall database design. it provide the brief idea about the overall
process, functionality of different phases.
8

Question-2
a). Produce a logical design by mapping EER diagram to set of relationship, showing all primary
as well as foreign keys clearly.
Figure 3 Logical Design by mapping EER diagram
 Primary Key- A Primary key is also known as keywords, it help for representing as
relational database that contain unique for each records. For Example- member id,
Job_number, Dog walker id, owner id. These are considered the primary key in every
tables.
 Foreign Key- A foreign key is kind of key that can use to establish a link between two
tables. Sometimes, it is also known as referencing key. A foreign key is define as
combination of different columns whose value match with primary key in different
tables. Foreign key is mainly identified, whenever a primary key of one table can show
9

into another table. In this situation, it will be converting into foreign key, make a relation
between two tables.
b). Produce Normalised relational model by identifying relation is in 3 NF.
Normalization is based on the technique or method for organising the information or data
within database system. Usually, it plays important role in order to reduce or minimise
redundancy and ensure that only relevant data stored in the form tables. This is prevent any kind
of problem or issue from database modification such as update, delete and insert.
Normalized relational model is mainly consist of organising the data or information in the
database. This kind of approach will consider as systematic approach of decomposition tables in
order to eliminate the data redundancy (Mio, Panfilo and Blundo, 2020). This is consider the
undesirable characteristics such as insertion. Deletion anomalies. It is consider the multi- step
process that can put data or information within tabular forms and removing any kind of
duplicated data from relational tables. Main purpose of normalization process is to eliminate
redundant or useless data, ensuring information dependencies which make a better sense. Each
and every data is logically stored or collected in proper manner.
Third Normal Form (3NF)
It consist of 2 NF, there are no consider any kind of table dependencies between columns in each
database table. Third Normal form is based on the high level in which database normalization
code holds: 2NF (Normal Form), every non-prime attributes of R which become consider as non-
transitively dependent on the relation.
10

Split into smaller tables
11

Candidate Key-> Dog_number
Non-Prime attributes-> In the given dog table, All attributes except Dog_number.
Here, Dog_type, sex, age->dependent on the Dog_number
Afterwards, the non-prime attributes are becoming represent as transitively dependent on the
super key such as Dog_number. Sometimes, it violates the rule of 3NF (normal form).
That’s why, it is important to move other information such as sex, age of dogs to new dog walker
table. Dog walker id represent as primary key into dog walker table.
c). what are the circumstance that would necessary to de-normalize this model.
In Case study, developer can identify the concept of de-normalize and its main purpose is
to significantly speed up data retrievals. However, it can use as tool or platform for enhancing
the query performance (Salvador and et.al., 2020). There are different circumstance that
applicable within de-normalize a particular Model.
To make a database become more convenient to manage- by using De-normalize, a database
is to provide the calculate values. Once they are generated and added into tables. Database
developer is mainly creating own reports and running queries without any depth knowledge of
application code or API.
12