Database Design and Development Report: M&S Database Implementation

This report details the database design and development for M&S, addressing user and system requirements for efficient data management. It covers the shortcomings of manual systems and proposes a relational database solution to handle customer and product information. The report includes the design of interrelated tables (Payments, Products, Product lines, and Customers), normalisation to eliminate data redundancy, and clear statements of system and user needs. It assesses the effectiveness of the database design, highlighting its ability to record a wide range of information and facilitate easy updates. The report also identifies areas for improvement, such as optimizing caching mechanisms and monitoring memory usage. The report concludes with technical and user documentation, providing a comprehensive overview of the database implementation.

Database Design &
Development

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
PART 1............................................................................................................................................1
1. User and system requirements of M&S..................................................................................1
2. Complete functional database system of M&S.......................................................................2
Normalisation..............................................................................................................................5
3. Clear statements of system and user requirements.................................................................7
4. Effectiveness of design in accordance with the requirements................................................8
PART 2............................................................................................................................................8
Fully functional relational database system................................................................................8
Effectiveness of database solution in relation to user and system requirements......................11
Areas of improvement...............................................................................................................11
PART 3 .........................................................................................................................................11
Effectiveness of testing and impact of test data .......................................................................11
PART 4 .........................................................................................................................................13
Technical and user documentation of database.........................................................................13
CONCLUSION..............................................................................................................................14
REFERENCES .............................................................................................................................15

INTRODUCTION
Database designing is considered as a way of managing all the information and data in an
appropriate way (Jagadish and et.al, 2016). Along with designing, the implementation of
database is also considered as an essential factor in which the designed database can be
implemented in the system. The report is about discussing the system and user requirements of
database that is to be implemented in M&S. Also, it involves the designing and implementation
of a database system that can help M&S to record and manage all the information of customers
so that they can be able to perform all the processes and operations effectively.
PART 1
1. User and system requirements of M&S
The manual processing of systems and operations in M&S has however decreased the
overall rate of production of the company. It is because the world has advanced and digitalised to
a level that any process of operation can be achieved in real time. Although M&S is one of
leading retailers across worldwide but there are still some procedures that are being performed
manually such as recording the information of employees, customers etc. and that is influencing
their productivity to a huge extent. Because of all processes being manual, they may not able to
analyse their current market position and share in the market place. It is because in case of
manual processing, there are chances that some data can be lost. Therefore, it can be quite
difficult for M&S to evaluate and estimate their market share as well. So, they can involve the
implementation of a database management system so that they can be able to manage all their
records and data efficiently and in an appropriate manner. So, for designing of database, there
must be some user as well as system requirements. These are discussed as following :
User requirements
The user requirement of M&S is that the database should be designed in a way that it can
be capable enough of involving the data and information of a larger range. It is because M&S is
a leading retailer. Therefore, it is obvious that a large range of customers must be visiting the
outlet on a regular basis. So, the database should be capable to record all the information of each
customer. It is considered as a very important factor because at the time of analysing their
position in market, proper recorded information of customers can help them (Martin and et.al,
2018).
System requirements
1

If M&S ensures the implementation of database, the systems on which it is to be
implemented should be suitable enough according to the system requirements. The hardware
should be up to date so that it can incorporate the database implementation. Also, along with
hardware requirements, all the application software's should be ready and installed properly so
that the database may not throw any load on the system. Therefore, if all the software's and
hardware's will be up to date, then the database can work more efficiently. So, these can be
classified as the system requirements for the designing of database that is to be implemented by
M&S.
2. Complete functional database system of M&S
2

Four interrelated tables of the database are represented as under :
Payments
Customer_numbe
r
Check_number Payment_date Amount_paid
A1 11 12/06/18 546
A2 12 13/06/18 1235
A3 13 14/06/18 2103
A4 14 15/06/18 4520
A5 15 16/06/18 2365
Products
Product_id Product_name Quantity_at_hand Price Recommended
suppliers
B1 Clothes 10 4500 Kashi
B2 Shoes 4 230 Bella
B3 Groceries 23 145 Tiski
B4 Cosmetics 15 256 Semekti
B5 Oil 3 400 Detux
Product lines
Product_code Product_name Contact number
of supplier
E mail
C1 Kashi 578964 [e-mail]
C2 Bella 120103 [e-mail]
C3 Tiski 450178 [e-mail]
C4 Semekti 102039 [e-mail]
3

C5 Detux 451203 [e-mail]
Customers :-
Customers_id Customers_name Total_purchases
11 Miss Shikheri 13
12 Mr Bubbly 2
13 Shaggy 20
14 Segwy 11
15 Miss Fesbyl 18
4

Normalisation
In order to ignore the problem of data redundancy, normalisation is done on the
implemented database. Therefore, M&S can also ensure applying normalisation. Also, it can
help the organisation in inserting, deleting, deleting and other essential measures so that they can
handle all the management of information easily and in a more logical way.
In case of 1NF, taking products table as an example :
Product_id Product_name Quantity_at_hand Price Recommended
suppliers
B1 Clothes 10 4500 Kashi
B2 Shoes 4 230 Bella
B3 Groceries 23 145 Tiski
B4 Cosmetics 15 256 Semekti
B5 Oil 3 400 Detux
All the values in every single column consists of a different value, which means no value is
unique. Therefore, it can be considered as in 1st normalisation form.
For 2NF,
For 2NF, it is important that the table must be in 1NF to be in 2NF. Also, there is no dependence
of attributes on the candidate key. In case of products table, product_id is the candidate key.
Product_id Product_name Quantity_at_hand Price Recommended
suppliers
B1 Clothes 10 4500 Kashi
B2 Shoes 4 230 Bella
B3 Groceries 23 145 Tiski
B4 Cosmetics 15 256 Semekti
B5 Oil 3 400 Detux
5

As the table is already in 1 NF, so after checking the dependence as under :
Product_id Product_name
B1 Clothes
B2 Shoes
B3 Groceries
B4 Cosmetics
B5 Oil
Product_id Quantity_at_hand
B1 10
B2 4
B3 23
B4 15
B5 3
Product_id Recommended suppliers
B1 Kashi
B2 Bella
B3 Tiski
B4 Semekti
B5 Detux
Product_id Price
6

B1 4500
B2 230
B3 145
B4 256
B5 400
7

3. Clear statements of system and user requirements
While the process of designing and implementation of database in M&S, it is obvious
that some factors need to be considered for an efficient rate and status of operations. The
involvement of database is must in M&S because of the reason they are using manual systems
and ways to gather information, there is a possibility that they may have to face major
complications in the future. It is because during the analysis of their achievements and outputs,
they might need all the information and records of organisation. Therefore, some of the system
and user needs that will be required for the design of database are discussed as under :
User requirements
 The first and foremost requirement is that the user must be aware of the actual purpose of
database. It is considered as an important step because if all the needs of M&S will be
clear, then the internal processing of database can be done accordingly and can also cover
each single feature.
 Also, all code platforms that are used and are containing essential factors should be saved
as well because if sometime in the future, M&S has to make some modifications and
improvements, then it can be easy to make some changes in code itself (McMinn and
et.al, 2016). Therefore, storing the codes can also be termed as an essential user
requirement that should be considered by M&S accordingly.
 Indexes play a very important role. Therefore, M&S should ensure the involvement of
indexes during modelling. It plays a very important role, so should be considered keenly
by M&S.
System requirements
 Along with the user requirements, there are some system requirements as well that are
must to be considered by M&S so that the database designed can have a positive
influence and should not cause any issue or complexity to the system.
 If the hardware and software applications implemented are not suitable enough, then the
database may throw an excessive amount of load on the system. So, all hardware's should
be properly implemented so that the database can also be managed and may not cause
any sort of trouble to system. All the components and peripherals such as hard drives,
monitors etc. should be properly maintained so that database can be able to perform
effectively.
8

 Software tools also act as an essential factor that is required for a successful creation of
database. Therefore, along with appropriate software's, essential software tools such as
for data modelling, functional dependence etc., should also be implemented in the system
in which database has to be implemented. It helps in working of the database in an easy
and flexible manner. Also, after the involvement of these tools, there are probably fewer
chances of creation of load on the system. This helps in maintaining the status of system
as well.
4. Effectiveness of design in accordance with the requirements
Considering all the system and user requirements, the design of database developed is
effective because all the user as well as system requirements have been achieved. The
implementation of database has been done in a way that it can be able to record a wide range of
information sets of customers, so that it can help in providing some ease to M&S. Along with a
huge capacity to cover the data sets of enormous people, easy Updation are also available. It is
because whenever M&S will update each entry of customers, it will automatically be updated in
the database. Therefore, M&S can easily analyse their position in market place. All the software
tools have been updated in the system, ensuring the efficiency of database. It ensures that the
hardware and software requirements have been successfully implemented, therefore, the database
designed can act as a beneficial measure for M&S to capture all the information of customers so
that at the time of making estimations and analysis of market share, it can act helpful.
PART 2
Fully functional relational database system
9

10