IT Projects 1: Database Analysis, Design, and SQL Queries Report

This report focuses on the database requirements of Sports Direct Limited (SDL), a catalogue-based sporting equipment retailer. The report begins with an analysis of database usage within the organization, highlighting data management, advantages, and disadvantages of databases, along with ke...Read More

IT Projects 1
Table of Contents

Introduction......................................................................................................................................3
Task 1...............................................................................................................................................3
1.1 Analysis of usage of database within the organisation.....................................................3
Task 2...............................................................................................................................................4
2.1 Illustrate requirements of organisation along with appropriate design of database.........4
Schema, ERD and normalisation............................................................................................5
2.4 Usage of adequate DBMS system within implementation of database............................8
2.5 Evaluation of database against requirements of the organisation....................................9
Task 3...............................................................................................................................................9
SQL Queris.............................................................................................................................9
SQL Results:......................................................................................................................1
Conclusion.......................................................................................................................................1
References........................................................................................................................................2

Introduction
The organised gathering of structured data or information that stored within the electronic
medium i.e. computer system is referred to as database. This makes it easy to manage as well as
access information as per the need of organisation. It is utilised in distinct forms such as numbers,
bytes, text, media and many more (Hendela, 2020). Database is controlled via DBMS (database
management system) and applications that are related with them. This report is based on SDL
(Sports Direct Limited) that sells catalogue-based sporting equipment. They provide their
customers with opportunity to their customer to choose items and accordingly fill up the form. This
report will provide an insight into database of organisation, their requirements and utilisation of
DBMS system.
Task 1
1.1 Analysis of usage of database within the organisation
Data implies facts that are associated with object like individuals name, their weight, age,
etc. Database implies systematic gathering of information for supporting electronic storage along
with alterations in the later. For an instance, SDL can store the information in electronic medium or
a database so that it can be analysed as per the need and it will also enable later to determine the
sales that have occurred. This will further enable them to make relevant strategies through which
they can accomplish their goal. The certain aspects associated with database in context of SDL are
specified beneath:
 Data can be interpreted as well as presented in different formats via its transformation into
information.
 Data duplication can be controlled in both the aspects i.e. externally and internally.
 SDL can render relevant information through which strategic planning, decision making,
goals definition and policies can be developed via SDL.
Functions of database It is liable for organising data in structured manner as per specification of DDL (data
definition language). Integration of data that is associated together at distinct levels and thus can be assembled
while implementation of relevant application programs (Hewasinghage and et. al, 2020). Database acts like a filter among data and associated application program. Database is liable
for separating applications logic from input along with output that is required for
calculation of address, locating records, their selection, follow links and many more.
Advantages of Database

 The comprehensive and organised approach is provided by database for recording and
maintaining outcomes of the activities of organisation. There will be declined data redundancy, errors as well as increased consistency. SDL will
be able to have high integrity along with independence from certain application programs
(Liu and et. al, 2020). Improvised data access will be provided to users via host as well as query languages and
enhanced data security can be attained via SDL.
Disadvantages of Database It will lead to enhance opportunities for groups who are outside organisation for gaining
access to data related with operations of SDL. Furthermore, trained personnel of the
organisation possess ability to misuse data resources intentionally. Due to high software as well as hardware requirements, database can be costly for the
organisation. These systems are complex that means adequate training needs to be given to
employees who will make its use. Extensive conversion cost will be faced via SDL while moving file based system to
database. All the application programs might get affected if any damage is caused.
Considerations while development of Database Usability is critical in context of how user-friendly system is for all the employees of SDL
who needs to access information. Visualisation and reporting leads to visually analyse as well as illustrate results for queries
associated with data while deciding and selecting segments (Markson, 2020). Security is major aspect for SDL, the reason behind this is that personal information of
employees and organisation sensitive data must be present thus, it is important that
regulations are adhered securely as well as protect it from any kind of theft. Confirm modules that are available within data analysis that will address requirements of
business. The functionalities comprises of insight, analysis, extraction, filtering information,
modelling & segmentation, automation, visualisation of result and forecasting of strategies.
Task 2
2.1 Illustrate requirements of organisation along with appropriate design of database
Sports Direct Limited provides their customers with catalogue dependent sporting
equipments. Customers have to choose items they need to have as well as fill order form. It is
found that there is relevant increase within their sales in first quarter. This has lead to increase
within stock (Roy-Hubara and Sturm, 2020). This makes it important for firm to accomplish their

needs so that they can identify the pattern of sales that have occurred and accordingly formulate
strategies that requirements of consumers can be accomplished.
Relational database must be utilised by SDL. It is a kind of database that is liable for storing
as well as providing access for data points that are associated with one other. Relational database
implies gathering of data items that possess pre-defined relations among them and they are
organised like set of table’s rows as well as columns. The major asset that can be attained via
relational database is that redundancy will be declined and in this case single entry will be made
within customer table. There are multiple tables that are associated with each other with the usage
of primary as well as foreign key concept so that user can be identified easily. This eliminates non-
repetitive and there do not exist chance for redundancy of information. Data integrity acts like a
relational database which leads data to be confined in relevant arrangements as well as data for
necessary for creation of relationship among information that is present.
Schema, ERD and normalisation
First Normal Form

2nd Normal Form

3rd Normal Form
In this case, composite keys will be utilised.

Entity relationship diagram
2.4 Usage of adequate DBMS system within implementation of database
Database is software for storage as well as retrieval of data of users while taking into
consideration adequate security measures. This comprises of distinct group of programs that will
manipulate database. Basically, this is designed for storing, retrieving, defining and managing
information that is present within the database. It is a primarily function that acts like interface
among end user along with database for managing data, database schema and engine (Schäfer, Sax

and Wiese, 2020). Relational database is liable for organising data within tables that are columns
and rows. This will enable individuals within creation, updation and administration of relational
database. With respect to SDL, structured query language can be used for implementation of
database. It is a programming language that is utilised for communicating data that is stored within
RDBMS and the syntax is easy to write, read s well as interpret.
2.5 Evaluation of database against requirements of the organisation
The database will be created by making use of MySQL for accomplishment of requirements
of SDL. This is open source database that can be used by organisations to easily access the
information. This is reliable, stable and powerful solution through which data security can also be
ensured. SDL will have unmatched scalability that will facilitate management of embedded apps by
making use of footprints within massive warehouse. This will further provide an option for
customisation through which needs of firm can be accomplished in an appropriate manner (Singh,
Bhadauria and Gurung, 2020). It is a distinct storage framework that will facilitate system
administrator for configuring MySQL database through which flawless performance can be
attained. This will lead to ensure that optimised speed is attained while accessing database through
which needs of customers can be accomplished and accordingly decisions can be formulated by
SDL.
Task 3
SQL Queris
Customer Table:

Category Table:
Sub Category Table:

Supplier Table:
Staff Table
Product Table:

Order Product Table:
Order Table:

Stock Order Table:
Product Stock Order Table:
Query Results: List all the items in stock

List all of the Items available in each category:

List all of the Items not in stock:
List all of the Items not in stock in each category of item:

List all of the Suppliers who supply a particular item:

List all of the Suppliers from a particular city:

SQL Results:
SQL Query Did Query Work If Not Why
List all of the Items in
stock
Yes
List all of the Items
available in each
category
Yes
List all of the Items not
in stock
Yes
List all of the Items not in
stock in each category
of item
Yes
List all of the Suppliers
who supply a particular
Item
Yes
List all of the Suppliers
from a particular city
Yes
Conclusion
From above it can be concluded that database refers to data structure that will store
organised information and this will enable to have multiple fields through which all the
information can be accessed in an appropriate manner. It is organised gathering of structured
data. Firm will be able to render their services and formulate decisions by taking into
consideration different aspects by analysing trends that prevails.
1

References
Books and Journals
Hendela, A., 2020. IS 331-004: Database Design, Management and Applications.
Hewasinghage, M. and et. al, 2020, July. DocDesign: cost-based database design for document
stores. In 32nd International Conference on Scientific and Statistical Database
Management (pp. 1-4).
Liu, W.X. and et. al, 2020. Distributed Database Design and Heterogeneous Data Fusion Method
Applied to the Management and Control Platform of Green Building Complex. In Smart
Innovations in Communication and Computational Sciences (pp. 211-222). Springer,
Singapore.
Markson, C., 2020. IS 331-454: Database Design, Management and Applications.
Roy-Hubara, N. and Sturm, A., 2020. Design methods for the new database era: a systematic
literature review. Software and Systems Modeling, 19(2), pp.297-312.
Schäfer, J.M., Sax, U. and Wiese, L., 2020, August. Benchmarking a distributed database design
that supports patient cohort identification. In Proceedings of the 24th Symposium on
International Database Engineering & Applications (pp. 1-8).
Singh, A., Bhadauria, V. and Gurung, A., 2020. A Problem-Solving Based Teaching Approach
to Database DesignA Problem-Solving Based Teaching Approach to Database
Design. Journal of Emerging Technologies in Accounting.
2