Data Analysis Report: Database Design, SQL and Testing Methods

This report provides a comprehensive analysis of database management systems (DBMS), focusing on the Bike Solution company. It begins by exploring various data models, including relational, hierarchical, and network models, along with data schema. The report then discusses different types of DBMS, such as relational, hierarchical, and object-oriented systems, highlighting their benefits and limitations. It also analyzes the limitations of traditional file systems and how relational databases solve these issues. The report further examines different approaches to database development, including bottom-up and top-down approaches, and introduces concepts like data mining, data warehousing, and web-enabled databases. The core of the report involves designing a relational database system, building a relationship database system in My SQL Server, and discussing database tools and techniques to enhance the user interface. It also covers SQL and its features, applying SQL for query implementation, and extracting data through query tools. Finally, the report details different testing methods, creates a test plan for the "BIKE SOLUTION" database, and emphasizes the importance of technical and user documentation, verification, validation, and control mechanisms in a relational database system, concluding with a summary of the key findings.

Data Analysis Design

Table of Contents
INTRODUCTION...........................................................................................................................3
TASKS ............................................................................................................................................3
LO1..................................................................................................................................................3
1.1 Various types of data model and Schema in the data analysis..............................................3
1.2 Different types of database management system and also includes limitations and benefits
.....................................................................................................................................................7
1.4 Analyse the various types of approaches in the development process. ...............................9
LO2................................................................................................................................................11
2.1 Design relational database system......................................................................................11
2.2 Build a relationship database system in My SQL Server....................................................11
2.3 Discuss the different types of database tool and technique to enhance the user interface..11
LO3................................................................................................................................................11
3.1 Discuss the SQL and its features and functionalities and benefits of query tool in the
relational database system.........................................................................................................11
3.2 Apply the SQL to implement in the query language in the relational database system......12
3.3 Features of Query language tool and data extracting through the query tool.....................13
LO4................................................................................................................................................14
4.1 Different types of testing methods:.....................................................................................14
4.2 Create test plan in the form of table of “BIKE SOLUTION” Database.............................14
4.3 Importance of technical and user documentation................................................................14
4.4 verification and validation in the relational database system..............................................14
4.5 Discuss the control mechanism and used for the relational database system. ...................14
CONCLUSION..............................................................................................................................15
REFERENCES..............................................................................................................................16

INTRODUCTION
Database management system is a process or method for collecting the entire data and
information in proper format. Data management is an essential part of each and every
organization that provide the facility to read, create, update and delete the data in the database
system. It is the most common and effective process to provide the centralized view of data
where meany user can view or access the data in proper manner. This report will discuss about
the bike solution company that manage their business operations and function by using DBMS.
This report will describe the various types of data model and data schema to collect the
information in proper manner. There are different approaches uses in the database development
and also helpful different tools and techniques to collect the information in effective ways.
Furthermore, At last task, it also designs an effective database system by using SQL server.
TASKS
LO1
1.1 Various types of data model and Schema in the data analysis
Data model is basically a model that shows the logical representation of database . It also
includes the constraints and having specific relationship between them. Data model is used the
specific rules for designing the effective structure.
There are different types of data model used in the database management system.
 Relational data model- this is the most common data model where it can sort the data in
form of tables and also creates relationship between them. This tables contain the rows
and columns and each or every column has specific attributes. This model follows the
different relationship such as one to many, one to one, many to one and many to many etc
(Agresti, 2018).

 Hierarchical data model- Hierarchical model is based on tree like structure that
organize data and information in proper ways. Each record has single parents that
represented in the form of tree (de Zoeteand et.al., 2017). There are one single parents
which is known as roots. It is basically used for physical order to sort the data. This
model is describing the real type's relationship.

 Network data model- This model allows the hierarchical model to create many to many
relationships between the each records of data, According to mathematical concept, this
model is constructed by using specific set of records. A record can be member of
different sets that allow conveying the complex structure and relationship.

Data schema- Data base schema is the main part of database management system that represent
the logical view of entire database system. It is also organized the data and creates relationship
between the tables or columns. In this way, all the important constraints and formulas can be
applied on it (Ghilani, 2017). It is contained the explanation of database in details that help for
programmer to understand the concept behind the database design.
Database can be divided into two parts:
Physical database schema- This schema is the actual storage of data that contain all the
information and data in form of indices and files. Data can sorted by using the secondary storage.
Logical database schema- Logical schema is defined the all the important logical constraints
that required for managing the database and also applicable in the storage data

1.2 Different types of database management system and also includes limitations and benefits
Database management system is a collection of records that are designed in proper ways
to manage their tables and columns. It is a type of software that used to create table and running
the queries in SQL server. SQL server is useful method for managing the business operations and
function of bike solution process all over the world. There are different types of database discuss
in followings:
Relational database management system- Relational database management system is creating
relationship between the two tables and files. This database connect with the different types of
data with the help of primary and foreign key. These are plays important role for creating
relationship in effective manner. In relational database, data can be stored and access the control
various tables (Johnston, 2017).
Benefits:
 This database can be used every people because it is the simplest form of relations.
 In tables, data can be update and also modified the data without delete the records.
 It supports the operations based theory.
 Relational database support the dynamic view.
Limitation:
 Relational database have minimum structure limit on the field length.
 Sometimes, it has high complexity in the information system.
 It is expensive for the organization.
Hierarchical database management system- It is the simplest form of database management
system that contain information related the parent and child relationship. All record contains the
individual tables in the relational database. On the other hand, The individual records is
equivalent to the rows and columns.
Benefits:
 It is fast speed to access and update the data in proper ways.
 It is based on the two edged.
 It provides the clear chain of command.
Limitation:
 Poor flexibility in the entire database structure.
 Hierarchical structure has communication barriers.

Object oriented database management system- Object oriented database is based on the
programming language that provide the consistency in the entire system, it is based on the
cyclical treatment of consistent data. This approach is applicable for design an effective
application and database system. Application requires for used the code to full-fill the need and
requirement of business (Schmidli, Neuenschwander and Friede, 2017).
Benefits-
 Object oriented database allow modify the real world entities and it also represents the
real world object. This database has modelling capabilities.
 It can be handle large amount of data type by using programming language.
 This database system provide the proper navigation.
Limitation-
 Lack of experience
 object oriented database is more complex
 There are lack of security aspect in database system.
Limitation of traditional file system-
There are followings limitation of traditional file system discuss in given below:
 Data redundancy- Each and Every application has own data file and same data can be
recorded and stored the files in many ways. In this way, same information contain the
another table that create duplication and unnecessary data redundancy in database
management system.
 Data inconsistency- Data inconsistency is the terms that occur when the same data
available is more than another files. It cannot updated in each and every file.
 Lack of data integration- sometimes, the data file is independent exist and user face
difficult problem to get information on the query which requires for accessing the record
store in the multiple files. In this way, it can be created lack of integration in the database
system.
 Data dependence- program and file processing system can be data dependent therefore,
In the organization, It can identify the actual physical location or retrieval of the media
that are dictated(Schmidli, Neuenschwander and Friede, 2017).
Relational database system solve the problem of traditional file based system

File processing system is a process or method that organize and manage the data in
proper manner therefore, traditional file processing system can be managed the data for long
terms but sometimes, there are many problems and issue generated such as lengthy development
times, Difficult for generating the answer, Extensive programming, lack of security problem and
limited sharing etc (Schmidli, Neuenschwander and Friede, 2017). There are major problems that
occur in traditional file based system that's why, relational database is applicable to improve and
maintain in proper manner. Relational data base system improve by followings:
 This database system provide the security that's why data can be protected.
 It provides the facilities to modify the record in file without any additions.
 Relational data base help for reducing the complexity of file processing system.
1.4 Analyse the various types of approaches in the development process.
Database design approaches is also useful for creating an effective and efficient structure
of the database system. There are mainly two type of approaches applicable in DBMS. Database
management system has been used the two different method that help for sharing the common
goals that utilised a system. It also describing the interaction between the processes. There are
different approaches die scribe in the followings:
 Bottom up approach – Bottom up approach is the basically fundamental analysis
technique that help for managing the entire business operations and their functions. It can
be begin with the specific details that moves from bottom to up. This method will help to
identify the data element and also grouping together to create dataset. In this way, it easy
for identifies the attributes of particular entities or groups (Agresti, 2018).

 Top down approach- Top down approach is a process or method that start from specific
ways. It is based on general idea that needed for the entire system. Top down approach is
more specific details about that how the system will interact. This process will involve
the identification of various entity types that has each entity and attributes.

There are another database system that help for managing the data in proper manner.
 Data mining – It is a process of discovering the pattern of large number of dataset. This
data interact with the another tool such as statics and Machine learning etc. data mining is
based on the computer science with extracting the information from the particular large
dataset. This method is useful for achieving the overall goals and objectives. Data mining
is analysing the different step of knowledge discovery in the database process.
 Dataware house- Data-ware house is collecting the data of enterprises in proper ways. It
is emphasizes to capture the data from the different sources which easily access or
analyse in the transaction process. The organization is used this system for reporting and
analysis the data (Aguiari and et.al., 2018). In this business process, Data ware house is
considered to be main component of business intelligence.
 Web enabled database- It is database application that provide the interactive access for
the business details and information. This process will perform different functions such
as placing orders, updating the records, tracking the information etc.
LO2
2.1 Design relational database system
Relational database system is a type of process that help for creating relationship between
the different tables.

Relational database: