Data Analysis and Design Report: Database Design and Implementation

This report provides a comprehensive overview of data analysis and database design, focusing on the context of St. Patrick College. It begins by comparing integrated database systems to traditional file processing, highlighting the advantages of database systems in terms of data redundancy, flexibility, and data modeling. The report then examines data models, particularly the relational model, and explores the application of data mining and data warehousing techniques. It details the characteristics and functionalities of data warehousing, including its advantages and disadvantages, and discusses various data mining techniques. The report then delves into the practical application of data analysis and design techniques to develop a relational database, including the incorporation of query languages, visual tools, and implementation strategies. It also addresses data verification, validation, and testing techniques to ensure the database's integrity and efficiency. The report concludes with documentation and evaluation of testing techniques applied to the database design.

Data Analysis and
Design

Table of Contents
INTRODUCTION...........................................................................................................................1
LO1..................................................................................................................................................1
1.1 Comparison between integrated database system and traditional file processing system....1
1.2 Examine case material that focuses on developments such as data mining and data
warehousing................................................................................................................................3
1.3 Analyse the different approaches to database design............................................................6
LO 2.................................................................................................................................................8
2.1 Apply data analysis and design techniques to develop a fully relational database with
minimum of six tables.................................................................................................................8
2.2 Verify that a design meets user requirements and provide justification of the database
design..........................................................................................................................................8
2.3 Database tools and techniques to demonstrate a more advanced level of understanding and
application...................................................................................................................................9
LO 3...............................................................................................................................................11
3.1 Incorporate a query language/languages into the database design......................................11
3.2 Use a range of visual tools to enhance the database design................................................11
3.3 Demonstrate the extraction of meaningful data through the use of query tools.................11
LO 4...............................................................................................................................................11
4.1 Provide documentation to support the database implementation........................................11
4.2 Data verification and validation..........................................................................................12
4.3 Evaluate a range of testing techniques and apply one to your own database design..........14
CONCLUSIONS............................................................................................................................17
REFERENCES..............................................................................................................................18

INTRODUCTION
Data analysis and design can be determined as the process of inspecting, transforming
and designing the framework of data which is aimed at discovering the useful informations and
conclusions at the end. This can be applied after writing the program which is helpful in
understanding and defining the data which will be later going to processed by the software. This
report will help in providing the integrated database traditional file processing environment as
compared to new technology. The study will also help in analysing different approaches of
designing the database. It will also help in developing the database design in order to create
relational database system. This research will also help in incorporating the query language into
the database design using Visual tools and techniques. After the application of all the tools and
techniques into the database in order to design a structure for insertion of values into it, the study
have also explained various implementing techniques. The report have also covered various
areas such as application of validations and verifications so that the user cannot insert invalid
values into the database.
Furthermore, it will also going to create a clear understanding about the extraction of
meaningful data which can be obtained through the use of wide range of query tools. The study
will also throw some light on various range of techniques which needs to be applied at the time
of testing the database software. These techniques will help in developing effective design of the
software.
LO1
1.1 Comparison between integrated database system and traditional file processing system
Managing the huge information in an organisation can be carried out by implementing a proper
structure and design in order to allow the interaction of users. This can be possible when the
developers provide a proper database design or structure as an interface to interact with naïve
users. Database Design can be comprised of different data modelling concept which helps in
application of various steps in order to develop a high level and abstracted design phase of the
software.
Traditional file processing system was an early attempt which helps in computerizing the
data manually. In early file processing system organisations generally use flat file system, to
1

store database. These manual filling system mainly works well when there are less number of
items needs to be stored in the database. Khuge database increases the complexities of managing
the large dataset. As the advancement of technology have rapidly grown, various file storing
methods are also established in order to store huge database automatically. This manually file
filling system was replaced by various database designs which are implemented using various
System development life cycle phases.
If St Patrick college use traditional file processing system then, there are certain
limitations which can be overcome by using a database system. These limitations and drawbacks
are as follows:
 In file system there are higher possibilities that same data is stored multiple times thus
there is higher tendency of data redundancy and duplication. If college will use database
system then data validation and verification techniques can overcome this problem.
Database system reduce the data redundancy by keeping single copy of data and
providing access to multiple users so that consistency can be maintained (Chandra and
et.al., 2015).
 In file management systems if any changes are made in access methods or physical
representation then there is additional requirement of modification to application
programs. This dependency make it difficult for the user to manage data due to limited
flexibility. On the other hand if St Patrick college will use database system then there is
no dependency of data (Difference Between File Processing System and Database
Approach, 2016). Instead, the data can be easily shared and is of concurrent behaviour.
So it will be easy for the college to analyse the performance of each player from all the
game events.
 The game management system of college requires managing complex data and interfile
relations between different events. The file processing system is not capable to provide
tools for data modelling of attributes from real world, hence database system can provide
relational database (Coronel and Morris, 2016).
Analysis of different data models and their contribution to database development
2

The data models represents the logical framework of the proposed database. It also
explains the ways to store and access data as well as the interdependency between data. Database
of St Patrick college can be based upon data models.
Relational database model: In this type of data model college can store its data in the form of
tables consisting rows and columns. There can be one-to-one and many-to-many relationships
between different tables. This type of database are written in SQL and can be easily normalised.
This data model is the best suitable model for the database of college. As compare to other data
models data fetching is very simple and quick in relational approach (Chitchyan and et.al., 2015).
Hierarchical model: In this model data is managed in the form of hierarchy in which a single
record it considered as parent root and its sub records are arranged in a fixed order. This
approach is not efficient in managing operational efficiency thus may not be suitable for St
Patrick college. The data uniqueness is difficult to achieve in this type of database thus if college
requires accessing only unique elements then relational data can be considered as effective
because it ensures the avoidance of duplicate data by using keys.
Entity relationship model: In this approach database is designed with concepts and is not
connected directly to the physical layer of database. All game events of college can be defined as
the entities which has attributes which are related with each other (Wu and et.al., 2014). In
comparison to relational data model it is easy to analyse the entity relations with this approach.
These data models are considered as the essential elements in developing a database because
these models describes the flow of data and how requirements of user can be fulfilled with the
management of data relations.
1.2 Examine case material that focuses on developments such as data mining and data
warehousing
Data warehouse is defined as the relational database which is used for analysis and query instead
of transaction processes. On the other hand data mining is termed as approach which can
discover relations and patterns between various data. These tools are important for the
3

developing database of St Patrick college so that college can achieve its requirement and its data
can be managed effectively.
Data warehouse (DW):
In order to support the management process of database system of St Patrick college non
volatile and subject oriented collection of data is essential. This is used to gather data from
various sources so that it can be analysed (Stonebraker, Deng and Brodie, 2017). This evaluation
can be used by the college to make decisions. Thus, it is able to provide a correlation between
different data from the current system. Though database and DW both are form of relational
systems but servers different purpose. The DW consist of inbuilt data resources which can
modulated upon transaction of data.
Characteristics of data warehousing:
The data warehousing is subjective and thus it works more on functionalities or data
which are defined in details. DW system gathers data from various sources so that data can be
analysed and reported for making decisions. Though database and ware housing both are
relational system but they serve different goals (Klochkov and et.al., 2016). DW aims at storing
huge quantities of historical data and to execute queries quickly. The warehousing consist of
inbuilt data resources which are modulated by transaction of data.
Characteristics and functionality of data warehousing:
DW process aims at a specific processes which demonstrates it subjects oriented
behaviour. It is executed by the data which is managed by the large tables. The non-volatile
nature of the warehousing enables its users to understand and analyse the changes in data so that
objectives of creating database can be accomplished. Warehousing can also be considered as
time variant and thus time limits are also taken into account in data modulation. DW serves the
function of repository and can provide back up services to data of St Patrick college with cost
effective approaches. Thus, it serves the functions of data integration and cleaning along with the
data consolidation (Hoffer, Ramesh and Topi, 2016).
Application and implementation:
4

St Patrick college can use warehousing to carry out data mining so that information
stored in database can be analysed even when data volume is very high. For the game event of
the college huge number of students will register in variety of games thus number of data
transactions will also be high. To manage the game analytics and details of participants so that
they can be mined college can use data warehouse (The Benefits of Data Warehousing and
Extract, Transform and Load (ETL), 2016). The game logs and activities are in bulk thus DW
can easily tune for accessing the updates. The data warehouses are implemented in three phases.
In first phase strategies and requirements of college are analysed so that transformation and data
mapping can be accomplished. On the basis of subjective area implementation is divided into
phases and then each phase is integrated. Instead of applying big bang method iterative
prototyping and testing is used for implementing data warehouse (Parks and Hall, 2016).
Advantages and disadvantages of DW:
The data warehouses can handle bulk amount of data and can give consistency by
eliminating errors. To enhance the decision making and business intelligence it processes queries
with various options along with security. Thus, it provides data consistency and make it possible
to timely access the data securely. However, the warehousing can promote certain disadvantages
such as increased time requirements to report, reduced data flexibility and compatibility issues
with existing systems.
Data mining:
This technique can be used to determine the useful data pattern from the huge amount of
data stored in database of data warehouse. The pattern recognising ability of mining techniques
will enable St Patrick college to detect error or any kind of unusual activity in the database
(Hoffer, Ramesh and Topi, 2016). On the basis of identified pattern of data the users can also
analyse their relationships between data. The traditional process of data analysis can provide
solutions to a problem only when end user identifies any issue while contrary to this data mining
is proactive process which determines characteristics and interdependencies of data so that
anomalies and errors can be detected before they influence the quality of the work or data
management.
5

Data mining techniques: In the developed database of St Patrick college following data mining
techniques can be used:
Predictive modelling: In this data mining techniques training and testing approach is used. The
historical data is analysed so that relationships can be understood and on the basis of this
learning new models can be analysed or extracted (Dillon, 2015).
Link analysis: This type of data mining techniques aims at building associations or links so that
data characteristics can be evolved. The discovery of the links depends upon several attributes
such as sequential patterns, same time sequence discovery and discovery by association or mere
presence.
Database segmentation: In this mining approach database is divided into clusters which contains
similar records so that homogeneous nature can be used to enhance the accuracy of data.
Deviation detection: This type of tools identify the possible deviation in the predefined norms or
expectations in the patterns of data. Visualisation techniques and statistic tools can be used for
this type of data mining approach. It is helpful in controlling quality and to avoid any
unauthenticated activities (Mitrovic and Suraweera, 2016).
Pros and cons of database mining:
The biggest disadvantage of data mining process is the security and privacy concerns.
The mining techniques are not fully accurate thus they can also give misleading information
which can cause loss to the users. However, the application of these techniques can be helpful to
detect the errors and suspicious activities which are encountered in the database or warehouses.
Thus, data mining and warehousing techniques can make data management process of St Patrick
college more efficient, easy, flexible and compatible with the existing system requirements.
1.3 Analyse the different approaches to database design
Various approaches to database design
For designing the database of St Patrick college different designing approaches can be
used which are discussed as below:
6

Top down approach:
With this approach initially the database developers focus on the general needs of the
system and then specific requirements of the system are addressed (Harrington, 2016). For
instance security, description of entities are general specification for the college and the ability to
access records of each game and then to select high performers from them so that overall top
rankers can be calculated are the specific requirements of the database. For using the top down
approach it is required that data analyst must have deep knowledge and understanding of the
expected system. Thus, if there is lack of co-ordination or understanding between developers and
St Patrick college then the database design may not be accurate and satisfactory in terms of
performance and functionality. Usually this approach is followed for the data models which have
high level of abstraction. For defining entities of lower levels and their attributes top down
refinement is applied (Youseff and Ibrahim, 2016). Entity relationship model is one of the best
example which can be designed with this approach. In comparison to bottom up approach this is
more coherent and thus have low redundancy.
Bottom up approach:
Contrary to the top down approach in this design approach developers first identify data
elements so that they can be grouped together and specific requirements of the users are
addressed first. In the next stage entities are developed from attributes. The initiation of this
design methodology is from the fundamental attributes of entities and relations. It is then
combined with abstractions so explain the type of relation between entities. The newly created
relations in database are termed as the design progress. Normalisation is an example of bottom
up design approach in which different types of entity are generalised into higher class. This
design techniques is more realistic and quick so it is more suitable for database which is required
to deal with the real life data (Atassi and et.al., 2014). The programmers prefer bottom up
approach because it is more flexible and have fewer controlling mechanism.
Inside out design approach:
7

In this designing aspect developers first recognises the set of major or dominating
entities. Later in the development process the attributes or relations associated with the initially
identified entities are also explored and identified. It can be consider as the specific case example
of bottom up approach in which the key focus of the designers is on the most evident and
centralized concept and then it is spread outwards in the scope of existing ones.
Mixed strategy approach for database design:
Mixed designing involves the use of concepts form both top down and bottom up strategy
before combining various modules of database together. Thus, certain parts of database schema
are designed with top down and remaining portion of the schema is developed using bottom up
approach.
Both top down and bottom up approaches are influenced by the size, scope and structure of the
organisation thus St Patrick college can also use centralized and decentralized approaches for
database design. In this type of designing method it is necessary to evaluate reports, interfaces
and forms (Klochkov and et.al., 2016).
Decentralized design approach: The proposed database of St Patrick college has large number
of entities thus to perform the complex operations decentralized approach can be used. In this
approach different modules are developed and designed is created for each module separately.
When designs of all modules are verified then, they are grouped together to provide overall
database.
Centralized design approach: It is one of the most productive designing approach in which data
components can be considered as the combination of various small objects. With this simple
designing process developing team makes conceptual framework and then verify it with the
requirements of college and then in the last stage data process and constraints are defined to meet
the goals of users.
8

LO 2
2.1 Apply data analysis and design techniques to develop a fully relational database with
minimum of six tables
Database analysis is a technique that help for managing the entire database in effective ways.
According to case study, it can be create an effective database system which consists of different
tables such as Player_table, match_table, event_table, student_table, team_table etc. These are
important database table creates an effective relationship between them. It can use the six tables
that establish the relationship between the different tables (Paik, 2018).
Structure query language is applicable in the database system to consist of different set of
records which is based on the relational algebra. There are different command help for input the
data and generate output in effective ways.
9

Database tables:
10