Database System Implementation and Improvement

The assignment provides an overview of a database system that has been implemented with all essential attributes. However, some factors may need to be modified and improved. The document analyzes the overall evaluation, suggesting modifications and improvements to avoid complications and ensure smooth operations.

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

Validation- it is a process which help for evaluating the final product and also check
whether the software meet the requirement or not. It is an essential for database management
to provide security and safety.
Verification- It is a process which help for evaluating and simply check the software
that is running or not in proper manner.
Security features of Login:
User Must be login and therefore, identifying the registered user and access the
protect the siebel application. This application will provide the security which allow the user
to access their information in database system. This login feature provide facility to their
customer providing their own username and password. In case, if customer forgotten the
password so they use reset or lost your password option to fix the problem.
There are some important login security features describe in given below:
 Implementing secure Login
 Logging out of siebel application
 Login username and password
 Account policies and password expiration Password Expiration on active directory
Effectiveness of database in regards with system:
The database system developed for Sainsbury must be effective enough to maintain its
effectiveness in real time workload of Sainsbury. In actual situation organisation will have
huge amount of data flow simultaneously. Thus, system must be able handle it. Because
during testing of the database these real time test data are not available and hence actual
analysis is difficult to made. The database is required to handle huge amount of redundant
data. Thus, it is necessary that special techniques such as defining data types during storage
will make it easy to store and retrieve data from the database. For evaluating the effectiveness
of system various benchmarks can be used. Benchmarks is defined as the process which is
used for the evaluation of the database system. Transaction processing performance council
(TPC) consist of a definite group of benchmarks which can analyse the performance of
database.
For Sainsbury relational database model TPC-C benchmarks is appropriate for
analysing the effectiveness of its database. For evaluating the effectiveness of system it
implements various types of concurrent transactions and promote the multiple user
environment. The database system must be able to minimize the data redundancy and
restructuring. To enhance the effectiveness of the developed data base designers must try to
reduce the number of inputs and outputs. This can be achieved by adjustment of transactional

size. Data modelling can be used as an effective tool for establishing communication between
developers and Sainsbury. This helps in clearly implementing the requirements of Sainsbury
into database structure.
Before development, designers must make a documentation for getting a clear idea
about the database structure which will be suitable for Sainsbury. Without proper planning in
development database may not give effective outputs as required. The developed database
will give more effective outputs it allows Sainsbury to create extensive reports based on the
results. Such reports will make the analysis purpose of the Sainsbury an easy tool. The
database system must also have clear guidelines and policies so clarify the data type which
can be stored in the database. Such approach will avoid the possibilities of cyber threats.
Effectiveness of database in regards with user:
The database can be considering as an effective approach only if it enhances the speed
of data handling. For enabling this feature the foremost requirement is that the user interface
must be user friendly. If user will not find it easy to work with database, then surely the
results will not be up to the desired level. It is also necessary for Sainsbury to provide training
session before implementing database into real time. The training seminar will help
employees to understand the features of database how these features can be used for
achieving the goals of the organisation. Database must also eliminate the redundancy
features. Such feature will make database effective by automatically providing the data which
is repeated. Database must also use data backup facility so that accidental deletion of data
does not cause losses to the organisation. In order to serve all the feature's database must not
be designed with complex tools.
Such complexity in the database structures greatly reduces the effectiveness of the
system adversely. Simple database configuration makes it convenient for the user to
implement. The easy and proper understanding of database also increases the speed of the
operations. The manual achievement of operations may not give equivalent effective outputs
as provided by well-designed database system. It is also possible with database
implementation that user can easily get duplicate copy of the database with more secure tools.
The database system proves to be more effective in providing more secure solutions to the
concept of data integration and data sharing.
Improvement areas
For a continuous effectiveness of the database implemented, there are some factors that can
be considered by Sainsbury. One of the most important factor that is to be ensured is keeping

the server of database healthy. It is because host plays a very important role. Therefore,
Sainsbury should analyse before only that whether the host that is serving the database is
capable enough and does it have efficient space for resources etc. there comes various
situations when the database throws a huge amount of load on the central processing unit.
Therefore, this can happen with Sainsbury as well. Therefore, Sainsbury can consider the fact
that they can keep two CPU cores available so that if there will be a higher amount of load,
then also with the help of other central processing unit, it can be managed. Along with
focusing on the overall memory usage, the fact that is to be considered is the number of page
faults per second. It is considered as one of the essential steps that helps in making an
evaluation. Along with these factors, disk space also matters and plays an important role. The
database that has been implemented will be involving different operations, therefore, it is
obvious that it will cover a larger disk space than compared to other systems. Hardware and
software as the user and system requirements play an important role. There are various
applications associated with the working of database. So, Sainsbury should ensure the fact
that if any occur comes to the database implemented, then, they should first analyse and test
so that they can be able to know which application might be responsible for causing it. It
needs a proper testing because there are enormous applications working on the database. So,
with the help of a testing procedure, Sainsbury can analyse the actual reason causing issues.
Then, further modifications can be made on that basis.

P3
Customer-table:
Query:
Order_table
Query:

Product_table
Query:
Sale detail

Query:

Supplier
Query:
These are database table that design according to the requirement of Sainsbury's. This
database system provide the informations regarding customers, products, order, sales details
and suppliers etc. Informations and details are stored in database system.
Queries of product with order:

Queries of customer and order:
PART 3
Effectiveness of database testing:
The database developed for Sainsbury must be tested before its implementation in real
time. Testing effectiveness and efficiency are the foremost parameters which are required to
achieve during database test. The database model for Sainsbury can be considered as
effective if it meets all the requirements of customer data handling mentioned in software and
functional requirement documentation in initial phases of software development life cycle.
During the testing of database, following key points must be evaluated in priority:
 Testing of designed database for Sainsbury must test the primary keys, foreign keys
and feature of uniqueness. For instance, no two users can have same customer code or
same login id (Coronel and Morris, 2016).
 During testing the effectiveness of database all constraints of database must be testing
with suitable and sensible default values. Database effectiveness must not alter due to impact of different testing environments
and tests. One type of testing results should not affect the other type of testing
procedures for database.
Impact of test data choice on effectiveness of testing:
Test data is defined as the data which will be used for testing. Sainsbury must use real
time and valid data for testing to get an actual analysis of database performance. Database
testing will not only utilize the existing data of Sainsbury for testing but it will also generate

great data volume of test data for enhancing the effectiveness of developed database system.
The credible and reliable test data increases the efficiency otherwise the designed database of
Sainsbury may face reduction in reliability if fake test data is used in testing (Groomer and
Murthy, 2018). The test data can be
System test data, SQL or performance test data and XML test data. The various test data
influence the testing in different manner. Valid test data: It verifies that all functions of designed database for Sainsbury are as
per the requirements. Invalid test data: Sainsbury must also verify via testing that when user enters invalid
input then system must not process further and should report it. Boundary test data: The sales statistics of Sainsbury must be accurate with in the
form of float and integer values. Thus, these test data gives analysis of boundary value
of test data (McMinn, Wright and et.al., 2016).
 Wrong and absent test data: It is also essential for Sainsbury that when someone does
not fill data correctly or leave the attribute then error should be shown. These test data
serve the purpose for it.
Test plan & Test Log:
Test plan consist of the following information for successful and effective
implementation. Test plan has specific objectives, quality and features that could manually
tested. It also using testing tool determine the need of project such as infrastructure, testing
scheduling, resource requirement, bug tracking mechanism, testing framework etc. this
database implementation of sainsbury's is providing the different informations about the
customer details, product and order etc.
Test plan will help for identifying the need and requirement of entire database system.
There are important test plan design for this database project discuss in given below:
Resource requirement-
Resource type Estimated
requirement
Available comments
Team members 15 13 2 testers join in next
month
Workstations 15 15
laptop 4 2 It can be adjusted
with testers.
Laser Printer 1 1 Network printer

Devices (i-pad, and
Android tab)
2 2 To be enabled Wi-Fi
and internet services.
Test cases:
Software requirement Windows server 4.5, SQL server, iPhone OS,
Do net framework.
Hardware requirement Intel processor with 2.4ghz. 32-64 bit, 4GB
RAM, 100 GB memory.
Tools and devices Jira issue tracking, confluence project
management tool, Bug tracking tool.
Automation tool Test NG, web driver, selenium IDE, Data
Driven framework.
Testing :

PART 4
Technical and user documentation for Sainsbury
Technical documentation: The designed database system of Sainsbury has following
technical documentation. Database overview: Sainsbury is one of the largest online retailer. It requires database
system for managing its customer's data so that it can analyse its performance in the
market scenario. System architecture: The database model designed for Sainsbury is relational database
which stores and manages data in tabular format. In system architecture first phase
initiate with designing of logical database. In this phase the tables are created along with
description of their content and inter-relationships. In next stage broker is defined which
performs operations in run time and execute data management on behalf of the user
requests. For handling the incoming requests of remote user’s servers are launched.
Servers can be defined as processes which are used to access the database on behalf of
multiple clients which are at remote location. Another module of system architecture is
client. These processes have the ability to modify the database by executing instructions
like insert or delete (Wefers, Konnerth and et.al. 2015). In open edge relational database
SQL clients are treated as remote client. System architecture also consist of background
processes which serves in background in case of system failure. Their main purpose is to
manage the data integrity function.
 Quality assurance procedures for database: The quality and performance of the
designed data base can be improved by different procedures. The designers must have
clear set of guidelines for defining the standards of quality which matches the
requirement of Sainsbury. The developers tend to assign new data set in repeated order
so that they can check the quality assurance rules in newly developed quality baseline of
data. For implementing this data profiling tools can be used. These tools analyse the
relationship between various entities of the database. With regular growth rate Sainsbury
will require to manager variety of data types thus database system designed for
Sainsbury must include semantic management system. The developers must also
regularly check the data validation so that the data and records can be validated as per
sets of designed rules (Mcminn, Wright and Kapfhammer, 2015).
User documentation:
System information: Sainsbury requires a database system which can give an analysis of their

customer so that they can evaluate their performance. It will also make their work easy to
manage their data. It will require internet connection for both ender user, customers and the
database manager. It can be used after installation with the help of supported internet browser. It
does not require any additional configuration. The database system will have following menu:
Login window: After installation login window will be displayed which will require password
and necessary details for verifying the authenticity of the user.
TABS: The database system will consist of following Tabs as per the requirement of Sainsbury.
Employees: This section of database will consist of employee’s information who can access the
database. Their activities are also monitored here for ensuring the security of the data base.
Customers: Sainsbury can store all its customer related records in this section. It will include
the personal details of the customers, their sales records, their preferences and choices in
different product range of Sainsbury.
Feedback and suggestion: The customer feedbacks and complains are priority issue for
Sainsbury to resolve. Thus, this separate section regarding this in database will give a
systematic approach to carry out necessary action. These feedbacks can also be used for future
references so that Sainsbury can evaluate the servicing and innovations related performance.
Sales review customer wise: For analysing the market position of Sainsbury the organisation
can review its sales in this part of database. The developed database will give organisation a
tool to review its sales in fix duration and to compare it with past performances. It is also used
to set target for the future actions.
Sales review product wise: It is also important for Sainsbury to make analyse that which sector
of its product range is making large customer base. So effective strategies can be developed for
raising the sales target for certain products. The developed database system provides this feature
by “Sales review product wise” button. The database maintains the entire record for all its
concern fields and attributes.
Performance evaluation: The data base will provide graphical analysis of performance in
different years. So that Sainsbury can review its marketing strategies and effective conclusions
can be drawn to achieve the targets of the organisation.
Settings: The database manager and related authorities can use settings to change the various
fields or attributes related to the database. It also gives privacy settings for limiting the
authorisation of database.

Assessment of future improvements: For assuring the effectiveness of database system
Sainsbury must follow certain guidelines such as:
 Database system must receive timely update and maintenance. The timely update
procedures will keep the speed and efficiency up to the mark without any degradation.
 Database security is crucial element for its regular and efficient working. Thus,
organisation must always keep an eye on type of data which is being processed. The
antivirus and other security features must be installed. To prevent actions such as
cyber threats IT teams of the Sainsbury must always monitor the activities associated
with database (Jain, Deshpande and et.al., 2017).
 Database data must use the backup feature regularly so that in case data is lost then it
can be easily retrieved without causing much loss to the organisation.
 Certain technical fixes with advancement of technologies can increase the lifetime of
database. This approach also reduces the risks of cyber-attacks unto a great extent and
thus performance of the database is maintained for longer duration (Alsharif,
Kapfhammer and McMinn, 2018).

This figure will show the need and requirement of user that perform different task for
accessing their account in database management system.

CONCLUSION
It can be concluded from the report that Sainsbury can make involvement of a
database management system because it can help them in maintaining a balance of all the
operations and records effectively. Because of the manual recording, there can be the
possibility of various complications. Therefore, for coping with different barriers and the
competitiveness, Sainsbury can make involvement of a database management system that can
help them to record and manage the processing of factors. A database system has been
implemented that involves all the essential attributes. There can be still some factors that
needs to be modified and improved, so on analysing the overall evaluation, Sainsbury can
make modifications and improvements as soon as possible so that they may not have to face
any complication or issue and can easily continue in their different operations.

REFERENCES
Books and Journals
Alsharif, A., Kapfhammer, G.M. and McMinn, P., 2018, April. DOMINO: Fast and Effective
Test Data Generation for Relational Database Schemas. In Software Testing,
Verification and Validation (ICST), 2018 IEEE 11th International Conference on (pp.
12-22). IEEE.
Clifford, A., McCallig, E. and Murtagh, G.F., International Business Machines Corp,
2015. Test data generation and scale up for database testing. U.S. Patent 8,996,915.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Elmasri, R. and Navathe, S., 2016. Fundamentals of database systems. London: Pearson.
Groomer, S.M. and Murthy, U.S., 2018. Continuous auditing of database applications: An
embedded audit module approach. In Continuous Auditing: Theory and
Application (pp. 105-124). Emerald Publishing Limited.
Harrington, J.L., 2016. Relational database design and implementation. Morgan Kaufmann.
Jain, N., Deshpande, A.V., and et.al., 2017, February. The effectiveness of cloud databases.
InAlgorithms, Methodology, Models and Applications in Emerging Technologies
(ICAMMAET), 2017 International Conference on (pp. 1-3). IEEE.
Mcminn, P., Wright, C.J. and Kapfhammer, G.M., 2015. The effectiveness of test coverage
criteria for relational database schema integrity constraints. ACM Transactions on
Software Engineering and Methodology (TOSEM).25(1). p.8.
McMinn, P., Wright, C.J., and et.al., 2016, October. SchemaAnalyst: Search-based test data
generation for relational database schemas. In Software Maintenance and Evolution
(ICSME), 2016 IEEE International Conference on (pp. 586-590). IEEE.
Stonebraker, M., Deng, D. and Brodie, M.L., 2017. Application-Database Co-Evolution: A
New Design and Development Paradigm. New England Database Day, pp.1-3.
Wefers, M., Konnerth, R., and et.al. 2015. System test scope and plan optimization. U.S.
Patent 8,954,931.
Witkowski, A., Dorman, G. and et.al. 2014. Using relational structures to create and support
a