Database Implementation

This document discusses the process of database implementation, including design, configuration, and integration. It explores the concept of entity relationship modeling and provides an example of an ER diagram. The document also covers the normalization process and the use of database management systems in implementing a solution. It concludes with a discussion on denormalization and its circumstances.

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

To facilitate and accelerate multiple reporting- Once, applications are needed to provide the
different analytical as well as statistical information. This will help for generating a better report
and also consume time. Whenever, de-normalize database that require for meeting all challenges.
But it needs to summarise all information for one or many users. A normalised database can be
aggregated or calculated in multiple times.
Question-3
Discuss about the Database Management System that how could be used in the implement a
solution.
A database management system is a kind of software for storing, retrieving user data
while considering appropriate security measures. It consists of group of program which can
manipulate the database. The DBMS accepts the request for information or data from application
and also instructs the operating system. It helps for users and other party software to store,
retrieve data or information.
Database management system allows for users to create their own database on the basis
of need or requirements. It means that include user of database and other kind of application
program. Sometimes, it provides an interface between data as well as software application.
DBMS refers to the modern technology solution that used to optimise or manage the
large amount of storage data (SAMOILA, 2020). It offers a systematic approach to manage
database by using interface for users and handle the workload accessing by DB applications.
The importance of DBMS is to handle all kind of functionality and associated with run
time data, which are commonly used for database access. Moreover, it will check for user
authorization to request the query, approved the process and devise an optimal strategy for query
executions. Generally, a database developer has been chosen Microsoft SQL server as database
tool or platform. It will support for creating the database tables and also representing a suitable
relationship between them. SQL server can implement a solution to generate useful information
from large data set and then display in the structured format on the basis of specification.
13

Moreover, information may be used for further analysis and make a better decision or business
intelligence.
Question-4
A). Create table statements-
Dog table
CREATE TABLE [dbo].[Dog](
[Dog_number] [int] NOT NULL,
[Dog_type] [varchar](50) NULL,
[sex] [varchar](50) NULL,
[age] [varchar](50) NULL,
[dog_medical_condition] [varchar](50) NULL,
CONSTRAINT [PK_Dog] PRIMARY KEY CLUSTERED
(
[Dog_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Dog Owner table
CREATE TABLE [dbo].[Dog_owner](
[Id] [nvarchar](50) NOT NULL,
[Address] [varchar](50) NOT NULL,
[email] [varchar](50) NULL,
[mobile_number] [varchar](50) NULL,
[age] [varchar](50) NULL,
[city] [varchar](50) NULL,
[frequency] [varchar](50) NULL,
[duration of walk] [varchar](50) NULL,
[walking_location] [varchar](50) NULL,
[walking_time] [time](7) NULL,
[member_id] [nvarchar](50) NOT NULL,
[job_number] [nvarchar](50) NOT NULL,
[Dog_number] [int] NOT NULL,
CONSTRAINT [PK_Dog_owner] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
14

Dog Walker table
CREATE TABLE [dbo].[Dog_walker](
[id] [nvarchar](50) NOT NULL,
[Name] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[email] [varchar](50) NULL,
[mobile_number] [varchar](50) NULL,
[frequency] [varchar](50) NULL,
[Dog_walking_experience] [varchar](50) NULL,
[hourly_rate] [varchar](50) NULL,
[limitations] [varchar](50) NULL,
[job_number] [nvarchar](50) NOT NULL,
[Dog_number] [int] NOT NULL,
CONSTRAINT [PK_Dog_walker] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Job detail table
CREATE TABLE [dbo].[Job_Detail](
[job_number] [nvarchar](50) NOT NULL,
[job_type] [varchar](50) NULL,
[job_hour] [varchar](50) NULL,
CONSTRAINT [PK_Job_Detail] PRIMARY KEY CLUSTERED
(
[job_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Membership table
CREATE TABLE [dbo].[membership](
[member_id] [nvarchar](50) NOT NULL,
[member_name] [varchar](50) NULL,
[member_phone] [varchar](50) NULL,
[member_email] [varchar](50) NULL,
[Hourly_fees] [int] NULL,
CONSTRAINT [PK_membership] PRIMARY KEY CLUSTERED
(
[member_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
15

GO
Primary and foreign key-
b) Sample data and database tables on the basis of case study
Dog owner table-
Column Name Data Type Number/ index number
Id nvarchar 50 limit
Address Varchar 50
email varchar 50
mobile_number varchar 50
Age varchar 50
City varchar 50
Frequency varchar 50
[duration of walk] varchar 50
walking_location varchar 50
walking_time time 7
member_id nvarchar 50
job_number nvarchar 50
16

Table: 1
Dog Table
Column Name Data Type Number/ index number
Dog number integer 50 limit
Dog type Varchar 50
sex varchar 50
Age varchar 50
Dog medical condition varchar 50
Table: 2
Dog Walker table
Column Name Data Type Number/ index number
Id nvarchar 50 limit
Name Varchar 50
Address varchar 50
Email varchar 50
Mobile number varchar 50
Frequency varchar 50
17

Dog walking experience varchar 50
Hourly rate varchar 50
Limitations varchar 50
job_number nvarchar 50
Dog number integer -
Table: 3
Job Detail table-
Column Name Data Type Number/ index number
Job_number nvarchar 50 limit
Job_type Varchar 50
Job hour varchar 50
Table: 4
18

Membership table-
Column Name Data Type Number/ index number
Member_id nvarchar 50 limit
Member_name Varchar 50
Member_Phone varchar 50
Member_email varchar 50
Hourly_fees integer -
Table: 5
19

Database tables-
Dog table
20

Dog Owner table
Dog Walker table
21

Job detail table
22

Membership table
23

C) Demonstrate the final database and write a realistic sample SQL Queries
Select/ from/ where
SELECT Id, email, mobile_number, age, city, frequency, [duration of walk], walking_location, walking_time
FROM Dog_owner
Result
Joins
SELECT Job_Detail.job_number, Job_Detail.job_type, Job_Detail.job_hour, Dog_owner.email,
Dog_owner.mobile_number, Dog_owner.walking_location
FROM Job_Detail INNER JOIN
Dog_owner ON Job_Detail.job_number = Dog_owner.job_number
Result
24

Ordering output- order by
select member_name
from membership
order by member_name DESC;
Result
select member_id
from membership
order by member_id DESC;
Result
25

Group by
select count (member_id), member_name
from membership
group by member_name;
Result
Aggregate Function- Min, Max, AVG, Count, SUM
Min ()
select min(hourly_fees) as smallestfees
from membership
Result
26

Max
select max(hourly_fees) as largestfees
from membership
Result
AVG
select AVG(hourly_fees)
from membership
result
Count
select count(member_id) as member_id
from membership
Result
SUM
select sum(hourly_fees)
from membership
result
27

Question-5
Discuss about the database application techniques which may be employed to enhance or
extended the security or performance of database
A database application is designed to create database and to store, manage, search,
modify, update and extract the information or contained within them. It is mainly represents as
comprehensive database software or application program, which is also known as Database
management system (Yuan, 2020).
One of the best way to optimise performance in a database which design to right things.
In fact, it is best practice to reduce or minimise any kind of technical debt and number of fixes to
implement in future. While there are different way to perform queries, indexes and server setting
which make it easier things in proper manner.
Indexing
It is kind of process or method to optimise the overall performance of database by
reducing the number of disk accesses which required to process queries. It is consider the data
structure technique which can handle quickly locate or access the data or information in the
database. Indexes are usually created by using database columns.
Indexing in database which define an appropriate indexing attributes. Generally, it can be
categorised into different methods such as primary indexing and secondary indexing.
For Example-
28

29

Clustering
It is based on the process or method of combining more than one server. For instance, it
has been established the connection with single database. Sometimes, one server may not
consider as adequately to manage, control large amount of data or information. Within SQL
server. Clustering is basically used to collect two or more server node and host to share storage
of data. Sometimes, it brings failure or success in the operations.
SQL (Structure query language) server as clustering in which used to collection of two or
more physical servers connected. When a database developer can create or index which
associated with table or view. This can help to retrievals its speed of rows from table. An index is
mainly contain the key in which built from one or more columns. This kind of key are collected
or stored in the form of B-trees (Akbar, Silvana and Jannah, 2020). It enabled the SQL server to
identify the specific rows in which associated with key values as quickly or efficiently.
Clustered:
 It is based on the indexes which can store or collect large amount of data in table. It is
mainly depending on the key values. This column include in the index. In this way, it is
only stored on clustered index per table because each rows themselves can be collected in
one order.
 At one time, data rows in particular table is stored in the sorted format, when table
contains as clustered index. When database table has become clustered index which is
also known as clustered table. If in case, it has no clustered index whereas data can be
stored or collected in the unordered structure. This is also known as heap.
Non-Clustered:
 A Non-clustered indexes have been considered the structure based separate from the data
rows. Therefore, it contain the non-clustered index key values. If every key values has
been entered as pointer into data rows. A pointer from index rows which become known
as row locator. The overall structure of row locater is mainly depending on the data pages
which can store or collect in the heap.
30

 A database developer can as non-key column to the level of index and passing its current
index key limits. Afterwards, it will become easier to execute the different operations,
queries.
For Example-
31

Privileges & Roles
A user privilege is consider as right way to execute a particular type of SQL statement,
right to access another user’s object. In SQL server, it is mainly defined the privileges in which
administrator has a power to perform significant roles. It will be created by users and used to
group together privileges or other roles. For consideration of Privilege and roles, this can help for
maintain the security as well as protection in proper manner. By using privileges & roles, it can
easily manage the permission within database (Akbar, Silvana and Jannah, 2020). SQL server
can provide the better several roles which become important roles played by Database developer.
It is consider the security principles that group together. Within Microsoft windows, it is likely to
group together in which handle the database level roles and given a permission scope.
 To add or remove particular users to the database roles, which needs to be add member or
drop member options. This can be executed by ALTER role statement.
 To permission of user defined database roles which needs to be customised by using
different statements such as GRANT, DENY and REVOKE statements.
32

33

For Example-
Views & data hiding
Views in SQL is a kind of statement that mainly used to create virtual tables. A view also
has contained rows and columns as they are exits in the real table of database. A developer can
use this view to create virtual table and also selecting fields from one or more tables. A view can
either have all kind of rows in database tables. This can based on the specific rows on certain
condition.
34

A view is creating from multiple tables. In order to select the particular tables in which create as
virtual database tables. If in case, any kind of changes are done in View table but also affects on
the Real tables.
For Example- By using statement:
 Create View View_name As
 Select column
 From table
 Where condition
Data hiding in SQL- A data hiding is a process or method that can embeds data in the digital
media for different purpose. Including annotation, copyright protection and identification. This
kind of approach is consider in the database system in which secure, protect the stored data or
information. The most important aspect of data hiding which is not restrict or regulate access. It
must ensure that hidden data remain inviolate and recoverable (Akbar, Silvana and Jannah,
2020). It implies that any data hiding technique or method comply with certain need or
requirements.
In order to hide a table, database, schemas. It is an essential task to access and manipulate
the system. When manipulating the SQL server catalog, historical data must be changed or
modified accordingly. Sometimes, historical data needs to be recovered from database where
they should occurs as failure.
35

CONCLUSION
From above discussion, it has concluded that database system supports for improving the
overall business performance and efficiency. In this report, it has been chosen case scenario
related the “Walk & Wag” enterprise, also consider the brief detailed about the dog walking to
next level by building an online operation. This documentation has been summarised about the
data model in the form entity relationship model. Relationship diagram is giving the brief idea
about the overall process of database system. Furthermore, it has been considered the entities
and their attributes for every database tables. As per consider the Enhanced ERD, it can produce
logical as well as physical model of ER on the basis of case study. However, it can improve more
understanding towards process of normalization like 3 NF, also explain the De-normalize
process. This report has been described about the database management system and their
importance within implementation to propose a better result or outcome. Using as Structure
query language (SQL) that can support for performing the different commands and generate
accurate outcome.
RECOMMENDATION
According to case study, Database designer has been used MS SQL server as tool or
platform which help for creating the dynamic database. In order to provide the better facilities for
Dog owner and Dog Walker to login the website and then access relevant information.
Sometimes, it also identified the particular job post by Do owner.
On the other hand, it has been identified the Start-up business needs a well-designed
database management system. It is helping to well-organised the overall functionality and allows
them to perform data related various tasks. This is becoming consider as crucial way to success
the business. It has suggested that Developer needs to use another database management tool
such as MySQL. It is also consider alternative platform that mainly used by other top companies
such as Google. It is based on the open source relational database so that it become easier for
start-up business to use them. This kind of tool is including the various kind of popular web
36

application. If developer have learn about one database. For start-up business, MySQL is one of
best choice due to its popularity and ease of use.
Apart from that owner of company want to hire or recruit the person, who are already
expertise in the database development. Therefore, they can easily select as option for business
and then creating the dynamic database management tool. In this way, it is also identified that
MYSQL alternative approach in which suggested by database designer and also uses them in
better approach.
37

REFERENCES
Book and Journals
Mio, C., Panfilo, S. and Blundo, B., 2020. Sustainable development goals and the strategic role
of business: A systematic literature review. Business Strategy and the
Environment. 29(8). pp.3220-3245.
Salvador, R. and et.al., 2020. Circular business models: Current aspects that influence
implementation and unaddressed subjects. Journal of Cleaner Production, 250, p.119555.
SAMOILA, R., 2020 Learning view over the implementation of business process
optimizations. DATABASE SYSTEMS, p.47.
Yuan, W., 2020, June. Development and Implementation of Art Teaching Resource Base on
Mobile Platform. In 2020 5th International Conference on Smart Grid and Electrical
Automation (ICSGEA) (pp. 423-427). IEEE.
Hewasinghage, M., Abelló, A. and Zimányi, E., 2020, July. DocDesign: cost-based database
design for document stores. In 32nd International Conference on Scientific and Statistical
Database Management (pp. 1-4).
Akbar, R., Silvana, M. and Jannah, M., 2020, October. Implementation of Business Intelligence
for Sales Data Management Using Interactive Dashboard Visualization in XYZ Stores.
In 2020 International Conference on Information Technology Systems and Innovation
(ICITSI) (pp. 242-249). IEEE.
38