Database Design & Development Report: Sainsbury's Database System

This report presents a comprehensive database design and development plan tailored for Sainsbury's, addressing the company's need for an efficient information management system. The report begins by outlining the issues Sainsbury's faces due to manual data processing, emphasizing the need for a database system to streamline operations and improve customer data management. Part 1 details the user and system requirements, including the need for a robust system capable of storing large amounts of data securely, along with an Entity Relationship Diagram (ERD) illustrating the relationships between entities like customers, products, and orders. The report covers user requirements such as data storage, modification capabilities, and performance objectives, as well as system requirements like updated hardware and software, security measures, and data normalization. Part 2 focuses on database tables, validation, and verification processes, including security features like user logins. Parts 3 and 4 address the effectiveness of database testing and provide user and technical documentation. The report concludes by summarizing the benefits of the proposed database system and its potential to enhance Sainsbury's productivity and data management capabilities. The report offers a complete overview of the design, implementation, and evaluation of a database solution, including considerations for security, efficiency, and user experience.

Database Design &
Development

TABLE OF CONTENTS
INTRODUCTION................................................................................................3
PART 1.............................................................................................................3
P1.................................................................................................................3
User and System requirements....................................................................3
Designing a database system for Sainsbury................................................3
User and system requirements....................................................................7
Effectiveness of database design implementation.......................................8
PART 2.............................................................................................................9
P2 ..............................................................................................................9
P3...............................................................................................................14
PART 3 ..........................................................................................................14
Effectiveness of database testing: ............................................................14
PART 4...........................................................................................................17
P5 User and technical documentation with screenshots............................17
Technical and user documentation for Sainsbury .....................................17
CONCLUSION..................................................................................................19
REFERENCES .................................................................................................21
Books and Journals.....................................................................................21

INTRODUCTION
Database designing is considered as a way of an efficient organisation of information
so that it can act as a reliable factor for easing the processes of a company ( Coronel and
Morris, 2016). There are still some operations in Sainsbury that are being performed
manually such as recording information of customers. So, these factors are hampering the
overall productivity of Sainsbury. So, for an effective management of the information and
data of customers, Sainsbury can make involvement of a database system that can help them
to maintain a balance between all the operations and procedures. The report is about
analysing the issues Sainsbury is facing because of manual processing of operations.
Therefore, it also involves the implementation of a database system that can help Sainsbury
to manage all the processing of operations and processes in an effective manner.
PART 1
P1
User and System requirements
As there are a few processes that are being operated manually, so this is a quiet time
consuming process for Sainsbury. Also, they used to record and manage all the information
of customers manually, but in that case, there are more chances of loss of data etc. So, for
resolving the issue, Sainsbury has decided to design a database system that can help them to
cover all basic requirements like recording and managing of data of customers as well as of
employees. So, the user and system requirements can be considered that the database system
should be fully developed and designed so that it can be able to record all the information of
customers. Also, it should be reliable enough that it has the capacity of storing a large amount
of data. Also, it should include all security measures so that the information of all customers
can stay secure and no third party can have access to it (Groomer and Murthy, 2018). The
hardware and software's used should be updated so that the function ability of database can
be easily performed. Therefore, considering all these, the database system can be designed so
that it can help in managing all operations and processes of Sainsbury.

Entity relationship Diagram of Sainsbury's
Fig : 1
Entity relationship diagram is a graphical representation of database that collecting all
information in the form of table. This diagram will show entity and their specific relationship
between them.
Entity- Entity is a real world object that contain some informations and details. Entity
can like person, place or thing etc.
Attributes- Attribute is characteristics and quality of particular object. Each and very
entities have attributes that represent in Fig : 1
For Example-
Entities:
customer, order, product, sales_detail and Supplier
Attributes:
customer_id

Customer_name
Customer_phone
Customer_address
Customer_location
Product_id
Product_name
product_type
Order_id
Oder_name
Sale_id
Sale_quantity
User requirements
The basic requirement of Sainsbury is to have a system that can be capable of
recording and maintaining data of all the employees and customers so that they can manage
all the processing in an efficient way. It is important because they are used to recording all
the information of customers manually. But there are various factors that can act as barriers
and complications because of this. A well-known example can be considered as the loss of
data. They may have to make use of the data and also, it can help them in analysing their
position in the market place. So, it is very important that there must be a system that can
handle all the recording and management of data. So, these are considered as the user
requirements that must be required at the time of database implementation. There are some
basic areas that needs to be ensured with the designing of database. Therefore, a proper set of
functional and non-functional requirements can be considered as a part of user requirements
that needs to be ensured (Groomer and Murthy, 2018). It is an obvious fact that Sainsbury is
expecting the database to involve all the information about customers, employees etc. So, it
should be capable enough that it can involve all of them. Also, all the data should be stored in
a specific order that if in case, Sainsbury wants to find the data about any customer, then, it
can seem easy to search for the required ones. Also, another important factor that is to be
ensured is that it should be capable enough to make modifications.
It means, if Sainsbury wants to make any sort of upgradation or modification, it can
be done easily. Performance objectives are also considered as essential factors within the user
requirements. It is because the implementation of database is quite costly and time consuming
as well. Therefore, it is obvious that Sainsbury will try to expect a better performance rate
from the database. It should be implemented in accordance to the system requirements

because the database should not throw any sort of load to the system because load can
actually make the system hang. Also, Sainsbury can try to have a backup for the system. It is
considered as a very important measure because if in case, someday the system of Sainsbury
will shut, then it can cause further complications and issues. So, it is considered that if there
will be a backup in Sainsbury, then even the shutting down of systems will won't affect as
well. Therefore, duplicating the data from the database and systems should be easy enough so
that the organisation can be able to make a backup system consisting of all the information.
So, these can be termed as the user requirements that needs to be considered while
implementing the database.
System requirements
The system that has to be designed and implemented is the database management
system. As there must be some factors essential for complying with all factors associated, so
similarly, there are some system requirements of database management system as well. It is
very important that the hardware and software functions are updated in order to ensure a
proper implementation of the database system. Also, as per the requirement of the database,
there should be appropriate security measures and it should be able enough to incorporate a
huge amount of data. It is because Sainsbury is one of the leading retailers in world. So, this
is obvious that there are enormous number of people who shop from the store daily.
Therefore, for every retailer, it is very important to maintain all the record and data of
customers as well as of employees. It is because when in the future, they will evaluate their
position in the market place to check how much they have achieved over the estimation, the
data of customers must be required. So, a database system will help them to cover all the
information of customers so that whenever they will be need it, they can make use of it. For
security measures, there are proper factors that will help Sainsbury to protect all the
information and data of customers from any external access.
Normalisation:-
Normalisation is a database technique which help for organize table in proper manner.
It also reduces redundancy and data dependency. Normalisation is considered as an
important factor that helps in providing an efficient and appropriate organisation and
handling of data. Sainsbury can ensure involving normalisation in order to exclude the
redundancy of data. Therefore, by means of it, Sainsbury can also achieve a sort of logical
storage of data which will help them in achieving all the objectives and targets on time.

Performing normalisation on customer's data as under:
There are some rules of normalisation that can be considered while normalising .

3NF
This is product table that shows the information of product_id, Product_name, product_type
and order_id.
Illustration 1: Table :1
Illustration 2: Table : 2
Illustration 3: Table : 3

In this Figure, It shows that table: 1 has primary key and foreign key. When product_id is a
primary key and order_id is foreign key in table : 1 and table : 2, because foreign key always
connected with two tables.
In table : 3, order_id is a representation as primary key. Because they have own primary key
for particular table.
This can show functional dependency in the entire database table. It is also known as 3rd
Normalisation Form.
The tables have been created as above. It covers all the necessities that will be
covered within the database system. In actual, the most important factor which is to be
involved in the database system will be product data. The database will cover the information
of products orders etc. Therefore, this can be considered as a multi-tasking database which
will be involve a variety of data. Also, this will cover all the security measures in order to
make all the information secure so that no external or unauthorised person can have access to
it.
Effectiveness of database design implementation
The effectiveness of the database that has been designed can be done in various ways.
On the basis of user and system requirements, it can be considered that the database that has
been designed covers all the required aspects. It is because the main requirement of Sainsbury
is to develop a database system so that they can record and manage all the information of
customers. It is important because they have been performing it manually and in this case,
there are higher chances of loss of data. So, because of this, there are possibilities that the
company may have to face further complications and issues (Witkowski and et.al, 2014).
Therefore, considering the fact that the database will be reliable enough that it will be capable
of involving a wider range of information of customers and is easy to use so that all
employees can easily understand how to use it. Along with this, it involves appropriate
number of security measures so that the information and data of customers cannot be
accessed by any external or third party. This is one of the important measures to be
considered. The designed database involves all these measures, so it can be considered as
effective enough to cope with the requirements of Sainsbury and thus, can also help them to
achieve a higher rate of productivity as well. So, on the basis of these factors, the
effectiveness of database designed can be measured. In the database designed for Sainsbury,
it involves a proper combination of security measures that can help the firm to deal with all
major concerns of security as well as other functional requirements.
Requirement of database design:-

According to the Sainsbury's organization, it can be useful some information regarding
customer, product, order and suppliers.
Create a database table that enter all information regarding the product, supplier, customer
and order. Using MS Access tool to design tables and creating relationship between them.
Hardware- windows XP/7/8
Software – MS Access Tool, XAMPP server.
PART 2
P2
Database tables:

Validation and verification Form