Database System Analysis and Design for Tesco: A Report

This report provides a comprehensive analysis of database design and development for Tesco, covering various aspects from initial user and system requirements gathering to the evaluation of the implemented system. The report details the need for a database to replace manual processes, addressing issues like data loss and market analysis challenges. It outlines user requirements such as database reliability and user-friendliness, along with system requirements including up-to-date software and hardware. A fully functional relational database system is presented, incorporating features like data normalization and validation to ensure data integrity and efficiency. The report further evaluates the database's effectiveness in relation to user and system requirements, emphasizing its capabilities in data handling, security, and accessibility. It also includes the importance of requirement tracing, user interface design, and system documentation to ensure the system's effectiveness. Finally, the report discusses methodologies for evaluating the database system's performance, including the use of benchmarks and integration with cloud storage, and the need for ongoing analysis and improvement.

Database Design &
Development

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
PART 1............................................................................................................................................1
Producing user and system requirements...............................................................................1
Fully functional system for Tesco..........................................................................................2
Clear statements of system and user requirements.................................................................4
Effectiveness of design in relation to user and system requirements.....................................5
PART 2............................................................................................................................................8
Evaluating the effectiveness of database system in relation with user and system requirements:
base tables:..............................................................................................................................9
Improvement suggestions for the developed database of Tesco: ........................................11
PART 3..........................................................................................................................................12
TASK 4 .........................................................................................................................................16
Technical and user documentation for Tesco ......................................................................16
CONCLUSION .............................................................................................................................22
REFERENCES:.............................................................................................................................23

INTRODUCTION
Designing of database is considered as involvement of all the factors that are necessary to
achieve all the objectives. After a successful designing of the database, implementation phase
takes place when the designed database is being analysed on actual and real applications. It also
helps in analysing and checking the reliability of the database (Harrington, 2016). The report is
about analysing the system as well as user requirements of Tesco. Also, the report will involve a
fully functional database system for Tesco that can help the firm to replace all the manual
processing of operations and functions. Also, it includes testing of the database system. A
technical and user documentation of the database designed and implemented for Tesco has also
been included in the report.
PART 1
Producing user and system requirements
A variety of operations and processes in Tesco are being done manually, because of
which the management has to face some issues while analysing their market position and
competitive advantage. It is because in case of manual processing, there are chances of data
getting lost. So, Tesco has decided to make involvement of a proper and developed database so
that they may not have to face any issue regarding analysing their market position. As they are
implementing a database, sot here must be some user as well as system requirements so that the
firm can be easily able to cope with the different challenges of the market place. User
requirements here can be considered as the fact that Tesco needs a database that should be
friendly and reliable enough so that employees can easily able to access and use the database. It
should be user friendly and less complicated so that the employees and management of Tesco
may not have to face any further issue with the database. System requirements can be classified
as the fact that the systems in which the database has to be implemented should have all
software's up to date so that the database cannot throw any extra load on the system (Elmasri
and Navathe, 2016). Also, along with the software, hardware installed also should be up to date
and have an efficient amount of RAM so that while operating different processes and operations,
Tesco may not have to face any issue or complexity such as hanging of system etc.
1

Fully functional system for Tesco
The relational database system for Tesco is designed in a way that it not only facilitates
the easy data handling for Tesco but also makes it easy for the customers to interact with
organisation via easy user interface. The designed database system consist of different sections
such as customers, sales related data, product description and suppliers details. For sustaining the
effectiveness of the database system the system is provided with features such as data
normalisation and data validation. It is one of the most common practices that unknowingly users
can put invalid data in certain fields of the database tables. For instance in the contact number
attribute user can feed invalid number or alphabets. This can create error in output or even can
mislead the administrator leading huge losses. To avoid such instances data validation is used.
Data validation is a defined set of rules ensures the validity of the data stored in the databases
system. They enhance the consistency and functionality of the system (Coronel and Morris,
2016).
2

Data validation techniques:
For validating data designers defines the input data requirements so that any error due to
invalid data can be avoided. Data validation can be achieved by various techniques.
 The most common technique is the restricted data type. Designers can fix the data type
which must be followed during data processing. For instance if a rule is fixed during table
design that name attribute must be sequence of characters it cannot be numerical values
then user will not be able to mislead system by feeding inappropriate values to the
column. In the designed database system along with data type restriction property of requirement
is also used. Due to this property if users of Tesco will try to leave any attribute empty
then database system will generate an error message. This technique is useful in avoiding
null values. For security reasons if it is essential for organisation to fill the name and
contact details properly then users will not be able to enter null values in the columns.
Along with data validation designed database system is also able to decompose tables. This
approach eliminates the redundancy. Thus, with normalisation techniques only useful and
sensible data is stored. It will be helpful for Tesco as it will make system fast as well as more
efficient. Without normalisation database will consume additional memory and system will
become slow as well as full of repeated useless entries (Bhogal and Choksi, 2015). Designed
tables follows following normalisation rules.
First normalised form (1NF): According to this rule table attribute cannot hold multiple values.
For example for a single employee code user cannot feed two different names. It can feed only
one name for one id.
Second normalised form (2NF): The developed system is in 1NF form and it does not have any
kind of partial dependency thus it is also said to have 2NF.
Third normalised form (3NF): The designed tables are 2NF are does not follow transitive
dependency thus they follow the third normalisation rule.
3

Clear statements of system and user requirements
User and system requirements of Tesco for an efficient designing and implementation of
database are discussed as under :
User requirements
User requirements at the time of designing and implementation of database are as
following :
 The database should be reliable enough so that it can be quite easy for Tesco to
understand its processing.
 The database should not throw any load to the system.
 The database should be compatible with various operating systems, which means that
there should be no sort of restriction on using the database on any specific operating
system (Mukherjee and et.al., 2016). The database designed should be suitable enough to
be run on any operating system. It means it should be compatible and reliable enough.
4

System requirements
System requirements of the database that has been designed and to be implemented in
Tesco are as following :
 Software of the system should be up to date so that the database may not throw any sort
of load to the system.
 Also, appropriate hardware's must be installed with a huge capacity because this helps in
excluding any issue of load on system. Therefore, it helps in preventing the system to
hang.
Effectiveness of design in relation to user and system requirements
Tesco has requires database system for making data handling process easy. This system
will allow the organisation to establish a better management system for its human resources.
With increasing growth the amount data to be handled is also increases. The huge amount of data
cannot be handled manually. Database system will allow users to perform multitasking with a
single centralised system. All management related functional records such as data related to
customers, employees, products and suppliers can be maintained by a single system. Tesco can
also provide security to its data from vulnerable cyber attacks. Database system is capable of
providing features like speed, accuracy, reliability and enhanced functionality. The redundancy
and validity of data are major concern for user during data handling (Pejathaya, Talluri and
Bhide, 2017).
Database system provides the effective solution to Tesco by addressing these basic
aspects. Database system is also capable to store the data safely for long time along with security
features to protect unauthorised access. With this system organisation will be able to maintain
long term relation with their customers and can analyse its own performance in different years or
with comparison to its competitors. Another significant advantage of database system is that it
allows organisation to access the records and data from any location. Thus authorised multiple
access increases the flexibility of the work and makes the operational activities very convenient.
In relation to the system requirements also database is designed as per mark. It meets all
functional and operational requirements which are necessary for Tesco to achieve its
organisational goals. The effectiveness of database in regards with system requirements can be
assessed with the help of requirement tracing. The requirement traceability creates and
5

understands the bidirectional path among goals of Tesco, its operational requirements and
performance. Requirements of Tesco are traced before as well as post development of the
database. For instance before development of database it is necessary to analyse that which type
of data will be handle by the database, its storage capacity, its features, possible threats and
improvement strategies for future advancement of technologies.
After the development and implementation of database also requirements of Tesco are
analysed. For instance as most of the organisations are shifting towards digital transaction. Thus,
in future if Tesco also want to upgrade its database system then their must be possibility for this.
If database is implemented by the organisation then users must have access to integrate the
system with advanced devices and technologies. Database system must be capable of integration
with other devices. The user interface of the database must be easy and as per the requirement of
users. It is designed in a way that it facilitate the management process. It is also essential that
requirements of organisation are documented and could be tested and implemented (Mukherjee
and et.al., 2016). The quality and appearance of database is also a matter of concern. It must be
suitable to the users.
The characteristics such as background colour, font size and interaction with outside
environment are also considered as an essential criterion for effectiveness. If database is not user
friendly then it may not prove much effective in its operational activities also because user may
not be able to utilize all its features. The developed database system must also be able to support
variety of operating systems instead of relying on any single operating system. The database
must handle the data effectively so that excessive load of data handling may not affect the
routine activities of the organisation.
6

Validation user interface form:
7

PART 2
Evaluating the effectiveness of database system in relation with user and system requirements:
There are different methodologies for evaluating the effectiveness of designed database
system. Tesco must have proper documentation of the purpose and requirement of its database.
This documentation will not only make designing process easy and accurate but will also
evaluate the effectiveness of system correctly. Organisation must have clarity about what type of
customer details is required by the organisation. In accordance to the requirement database is
designed. The effectiveness is measured by testing data and plans which are similar to real time
so that efficiency and accuracy can be evaluated. The database transactional activities like
consistency, isolation, atomicity and durability are essential for evaluation of effectiveness.
The factors like hardware, software, installation accessories and capabilities of
virtualisation are also considered (Jukic, Vrbsky and Nestorov, 2016). The developed system
must be integrated with the networking components of Tesco. For evaluation of transaction
process of database transaction processing performance council (TPC) benchmarks can serve as
effective tools. These benchmarks make repeated queries and data modifications in concurrent
pattern so that all parameters of system can be monitored and performance can be evaluated. The
database must be in support with cloud storage. Tesco is multinational organisation thus cloud
storage is one of the necessity for the organisation.
The effectiveness of database system can be evaluated by monitoring the status of in-
memory management of data. If the designed database system process the data in memory then,
performance can meet the speed standards. The effective database system also keeps track and
monitoring of all activities regularly. This approach will play significant role in detecting the
vulnerable threats. The database must be supporting to back up the records and tables.
Organisation must regularly back up its data.
8

Database tables:
9

Queries:
10