Database Management and Security

The given report is about the designing and implementation of a database system that covers all user and system requirements. Normalization has been done to evaluate the tables created in the database. A proper testing can be done to analyze whether the process is working appropriately or not. It also involves technical and user documentation involving all aspects of the database system.

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

11

Effectiveness of database solution in relation to user and system requirements
The database solution can here be considered as the implementation of database in M&S.
In relation to all the system and user requirements, all these have been achieved so that the
processing of database can be done effectively and in a coherent manner as well. Recording and
updating of information in database can be easily achieved in real time. Also, it is compatible
enough in multiple user environment. For an appropriate and effective processing of the
operations, tools for data modelling have been involved. Therefore, the database implemented
involves all the hardware and software requirements completely. Thus, all the managing of data
can be easily performed within M&S with the help of database.
Areas of improvement
Although the database that has been implemented in M&S is effective but there are some
factors that can be considered for bringing some improvements in case of any future
development. If the database is big enough that it can incorporate major data and information,
then there are some chances that it can throw some load on the hard drive of the system.
Therefore, M&S can have keep focus on caching mechanisms used. Also, page faults per second
can be analysed and measured so that M&S can guess the fact that if there is any requirement of
additional or extra memory (Harrington, 2016). It is important to analyse so that if it is so, then
they can make modifications in the database. This feature can help M&S in preventing from any
load. Also, for increasing the response rate of the central processing unit, M&S can consider
involving two central processing unit cores. The actual forte behind this is that if in case, a CPU
gets affected by load, then there will be a second option to handle the rest of load. Therefore,
these measures can be termed as some areas of improvement that can be further used and
involved at the time of the implementation of database. A healthy hardware is another essential
requirement that plays an important role. Also, if any error occurs in the database, instead of
making modifications in any specific application, a proper testing should be performed so that
the actual root can be analysed and improved.
PART 3
Effectiveness of testing and impact of test data
The designer must perform the testing of database system before Mark & Spencer can
implement it for its operational activities. The designer must ensure that data flow is occurring in
desired manner. The operations which are performed in front side of database are also reflected
12

simultaneously at the rear end. The testing must also ensure that there is a consistent mapping
between user interface fields and attributes of database. The database can be considered as
effective only if testing gives ideal results for database durability and consistency. Database must
provide the most updated value of data on accessing(Alsharif, Kapfhammer and McMinn,
2018). The database efficiency is also measured in terms of isolation. It must provides output in
the sequence in which transactions were executed. When transactions are executed then results
must be independent of factors such as system crash or power disturbances.
Test plan for testing the database
For effective testing of database test plan acts as an advantage. It gives the sequential
analysis of the different levels involved in the database testing. Developers first analyse the
approach of test which is to be included in the testing. The testing will involve the functionality
test. The database must be tested with all test data so that database effectiveness is maintained in
various conditions(Cleve, Gobert and et.al. 2015). The data base must also be compatible in
speed and size related issues. For instance it must be able to eliminate the redundancy and huge
width involved in database attributes. The clear perspective regarding testing approaches leads to
framing of testing environment by designers.
The testing results are the most effective when tests are replicated in close real
environment as it will be available with the organisation. Testing environment must be
maintained so that database testing can be conducted properly. When database designers are
done with testing environment set up then, test data is analysed and implemented. The different
types of test data are used for carrying out test related to security, configuration, data processing
methods, speed and functionality (Clifford, Emanowicz and et.al, 2016).
Impact of different types of test data
Testing results are greatly influenced by the nature of test data. Developers must wisely
choose the type of test data so that designed data base of Mark & Spencer can provide the most
effective results.
 The performance test data are used to analyse the response of database in case of realistic
different loads.
 For ensuring the effectiveness of database security testing includes the integrity test data,
confidential and authentication test data. Authorization test data validates the access
permission to different users.
13

 For analysing and testing that whether user can access the database by different
approaches the database system is tested with the use of authentication test data.
 The white box test data performs testing with invalid and all possible branches of data.
 For evaluating the more realistic scenarios designers also test the database system with
absent and incorrect data formats. These data types involved in testing analyses the all
perspectives of database(Coronel and Morris, 2016).
PART 4
Technical and user documentation of database
The technical and user documentation gives the technical details involved in the
developed database for Mark & Spencer along with the user guidelines.
User documentation
Privacy Settings: The administration can change the privacy settings. There are certain
attributes such as customer details, sales records which can be accessible to all team members
of the organisation. While certain records such as personal details of employees, annual
turnover and sales analysis of Mark & Spencer can be accessed by authorised persons only.
Thus, privacy settings gives the option to do so.
Tools: This option of database will allow user to perform various tasks such as insert, delete,
modification to any particular record in the database. It also gives option to modify the user
interface such as font type and size, background applications and preview and security related
options.
Update tables: It allows the organisation to update all attributes of the tables simultaneously.
This is useful for periodic updates of the database. It also allows user to add or remove some
attributes from the table.
Extract records: For analysing the performance the Mark & Spencer can extract the required
details from the database. It can provide graphical as well as statistical analysis of the data. It
gives various modes of analysis such as customers wise, location wise, profit and sales wise
analysis.
Technical documentation
14

Security: The antivirus and firewalls are installed to provide the networking security But
organisation must ensure that they are in update version.
Data types: Each attribute of the table contains specific data type only so user must ensure that
correct values are entered otherwise system will not process the data. For instance the name
attribute can accept only alphabets. The numeric values or alphanumeric terms are not accepted
by the database.
Data Overloading: Though database is designed with maximum efficiency keeping in mind the
requirement of Mark & Spencer. But sometimes data may overload thus it is required for the
user that first backup of data is saved and then data clearing is accomplished.
Functionality and redundancy: Database is provided with feature that redundant instructions
and records are highlighted and removed with the permission of user. For repeated entries
database remembers their default values. It reduces the extra efforts of the user. All functional
features are provided as per the requirement mentioned but the table attributes and their data
type can be changed any time as per the requirement and suitability of the organisation.
For maintaining the effectiveness of the developed database Mark & Spencer must
review the performance of database regularly. The detailed knowledge of database will greatly
reduce the errors and performance degradation. The network security measures and continuous
monitoring of possible threats will provide the security to database(Elmasri and Navathe, 2016).
For restoring any issue organisation must seek for expertise help instead of dealing it locally. It
can cause the damage to a more severe extent.
CONCLUSION
It can be concluded from the report that M&S can involve the designing and
implementation of a database system that will be covering all the user and system requirements.
Normalisation has also been done to evaluate the tables created in the database. A proper testing
can be done in order to analyse whether the process is working appropriately or not. Also, it
involves a technical and user documentation involving all the aspects of the database system.
15