Database Design for Bike Solutions

This assignment focuses on designing a centralized database system for a bike solution company. It delves into various data models, schemas, and development approaches to effectively manage business operations. Students will learn about different tools and techniques for data collection and ultimately design an efficient database system using SQL Server.

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:

2.2 Build a relationship database system in My SQL Server

This is the entity relationship diagram that shows the relationship between the different tables in
appropriate ways. There are different entities available in the database system such as employee,
customer, bicycle table, branch, payment and work schedule etc.
Database tables:

2.3 Discuss the different types of database tool and technique to enhance the user interface.
Database tool and platform are the important part of database management system that help for
managing the entire data in proper manner. There are different tools and platforms used for
creating an effective database system in proper manner.
Microsoft SQL Server-
 This tool is basically used for performing different functions and operations such as unit
testing.
 It is the commercial tool which help for test the entire database project effectively and
efficiently (de Zoete and et.al., 2017).
 Test can be created in two different ways such as generating the test in the database by
using he T- SQL script. It also adds to test manually in proper manner.
SQlite-
 It is specially designed for the purpose of commercial use database system and it also
testing the entire framework of the Microsoft SQL server.
 This tool is applicable for identifying the ambiguous tables and also stores the procedures
with the isolation code.

Oracle-
 This tool is developed by the developer for the development of SQL programming.
 It is a database tool to test the entire system and it also applicable for the cloud database.
 This tool includes DB2, sybase, Microsoft Access etc.
 This tool can be support to the cross platform and provide the IDE and run in the Oracle
databases (DI and ZHANG, 2017).
LO3
3.1 Discuss the SQL and its features and functionalities and benefits of query tool in the
relational database system.
SQL stands for structure query language that help for creating effective relational
database management system. It is a fast and powerful language which is used for different small
and big business. It developed for managing the entire business operations and functions. It is
also support to the MySQL. There are various types of features that useful for the database
creation.
 SQL is released as open source license so that each and every people can used them.
 It is very powerful program that handle and manage the large number of data or their
functionality. It is the most expensive and powerful database packages.
 SQL uses the standards form of the well known SQL data language.
 It can work more quickly and work with the proper manner with the large number of
dataset.
 It is user friendly to the PHP language and the most appropriate language in the web
development.
 This language is support to the large number of database and the size of file is 4GB.
Sometimes, it can increase (hilani, 2017).
 It is based on the customized and have open source GPL License that allows the
programmer to change and modify the software according to the environment.
Benefits of Query language-
 Query language can be used for retrieve the large number of data and records from the
database management system effectively and efficiently.
 It is used for view the data or record without storing the information in the object.
 This query language can be join the two or more tables create relationship between them.

 It is long term based management system which adopt the changes according to the need
and requirement of business purpose.
 It is easier for managing the database system without having to write the large number of
code.
 SQL access the tables and also insert the rows and columns.
3.3 Features of Query language tool and data extracting through the query tool
Query tool is the best platform that help for managing, developing, comparing and
administering the information in the form of database system. There are different types of query
tool available in the marketplace that support to perform different functions in effective manner.
 Adminer- Adminer is basically a database tool that allows managing the database system
such as tables, rows, columns, indexes, permissions, relations and users etc. It is the form
of package embedded as single php files. This tool is supported to different database
manage system such as PostgreSQL, MySQL, MS SQL, Oracle etc (Golodoniuc, bike
and Klump, 2017). These are the most common tool that applicable in the development of
database system.
 DB Comparer- It is also database system tool for analysing the difference in the SQL
database server with the use of UL. It also compares with the other database objects such
as columns, foreign key, roles, stored procedures and indexes etc.
 SQL Manager Lite for SQL server- It is based on the SQL server that allows creating
or edit the database object in the system. It creates edit and execute the SQL queries. It is
user friendly interface and performs different functionality of wizards (Johnston, 2017).
 Squirrel SQL- This tool is based on the Java database administration tool for the JDBC.
This tool will allow viewing the database structure and SQL command. It can support to
the other databases such as IBM DB2, Microsoft Access, Oracle, Sybase and SQL server
etc.
Data can be extracting from the large number of dataset by using two approaches such as bottom
up approach and top down approach. These are the important that useful for managing the entire
business process in proper manner.

LO4
4.1 Different types of testing methods:
Database system is a collection of data or information that stored in the database
management system in proper ways. There are different types of testing method used in the
database management system.
Functional testing- Functional testing is the performed different functions and always keeping
in the mind at the end user point of view (Otero, Nieuwenhuijsen and Rojas-Rueda, 2018 ).
Black box testing- Black box testing is involves for verifying the database integration to check
the functionality of the database system. All the transaction and operations can run with the help
of ender to meet the business specification. There are different advantage of this testing :
 it is simple process to perform different function for the development
 The cost of development test cases is less than the other testing such as white box testing.
White box testing- This testing is deals with the internal part of database system because the
specification of the details are hidden for the users. It also involves the database triggers to perms
different functions. This type of testing is to validate the data in the form of tables, models and
schema etc.
4.2 Create test plan in the form of table of “BIKE SOLUTION” Database
According to the Bike Solution database, it also includes the information regarding the
bike quality, ability and their efficiency. This database tool help for check the quality of bike in
the shops.
 It also checks the location of particular bike.
 It also checks the information about the customer who will buy the bike at what locations.
 Database testing check the details regarding the payment, Penalty on late bike
submissions.
4.3 Importance of technical and user documentation
Technical and user documentation can be referred as the interface which is provided to
the users in order to interact with the application or browser to the server. These are manually
designed by the developers in order to provide the end product to end users (ahman and et.al.,
2018). It is termed as designing the interface that helps in developing a desired product for the
customers. This also involves various documenting aspects such as cost and resources of the
software.

4.4 verification and validation in the relational database system
Validations is referred to the application of various validations in order to provide access
to the users. It is commonly termed as the process which helps in entering the data and that
information is matched with the database that restricts the incorrect data from being recorded
into the relational database. For example validation can be applied on various fields such as
mobile number, gender, email address etc. as field which records the mobile number is validated
with the input of only ten digits which are integers. Similarly, next field I.e, gender which
requires the input as only two values either male or female. System produces an error is any non
validate value can be putted in a column. This error is produced due to the validations that are
applied to a particular field (Schmidli, Neuenschwander and Friede, 2017).
Data verifications can be termed as a process that uses different types of data in order to
adopt the principle of accuracy and inconsistencies after the data migration is performed. It is
mainly used for proofreading the data which involves the checking of the data that is entered
against the original account.
4.5 Discuss the control mechanism and used for the relational database system.
Database management system provides the designers, programs and end users with
sophisticated capabilities to store, retrieve and manage the data and information. In order to
protect the data from various harmful threats various control measures need to be established for
controlling the relational database of the organisation. Such mechanism may be internal or
external security measures that help in authenticating the access to the users. In order to provide
the security controls, firstly one needs to identify the security threats or risks. The control
mechanisms of the relational database are a as follows:
 Protection of the data from improper access: This helps in protecting the data from
unauthorised person that helps in securing the confidential information of the company.
This increases the chances of data loses and theft that might be the biggest threat of the
organisation.
 Database integrity: It helps in generating the large amount of database by a automatic
transaction which ensures the controlled activities of Database management system.
 User authentication: this measure help in determining the authorised person which can
be judged by applying various authenticating techniques such as passwords and emails
that and encrypted and preciously stored in a global database(Yuri and Liudmila, 2017)

 Multilevel security: Confidential information is very sensitive and once it is stolen then
it might impact the firm for the longer duration so security need to provided very
crucially in order to store the database. This may leads to the access of information from
various users.
CONCLUSION
As per discuss, it concludes that 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. It provides the bike
solution company that manage their business operations and function by using DBMS. It
summarised 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.

REFERENCES
Books and Journals:
Agresti, A., 2018. An introduction to categorical data analysis. Wiley.
Aguiari, D and et.al., 2018, January. Canarin II: Designing a smart e-bike eco-system.
In Consumer Communications & Networking Conference (CCNC), 2018 15th IEEE
Annual (pp. 1-6). IEEE.
de Zoete, A and et.al., 2017. Rational and design of an individual participant data meta-analysis
of spinal manipulative therapy for chronic low back pain—a protocol. Systematic
reviews, 6(1), p.21.
DI, Y. and ZHANG, M., 2017. Design on the Metadata Framework System of Public
Bike. Journal of Library and Information Science, 7, p.011.
Ghilani, C.D., 2017. Adjustment computations: spatial data analysis. John Wiley & Sons.
Golodoniuc, P., Car, N.J. and Klump, J., 2017. Distributed persistent identifiers system
design. Data Science Journal, 16.
Johnston, M.P., 2017. Secondary data analysis: A method of which the time has
come. Qualitative and Quantitative Methods in Libraries, 3(3), pp.619-626.
Otero, I., Nieuwenhuijsen, M.J. and Rojas-Rueda, D., 2018. Health impacts of bike sharing
systems in Europe. Environment international, 115, pp.387-394.
Rahman, A and et.al., 2018, January. Expert System for Deciding a Solution of Mechanical
Failure in a Car using Case-based Reasoning. In IOP Conference Series: Materials
Science and Engineering (Vol. 288, No. 1, p. 012011). IOP Publishing.
Schmidli, H., Neuenschwander, B. and Friede, T., 2017. Meta-analytic-predictive use of
historical variance data for the design and analysis of clinical trials. Computational
Statistics & Data Analysis, 113, pp.100-110.
Yuri, K. and Liudmila, G., 2017. Application of Artificial Neural Networks in the System of
Self-Diagnosis Car. International Journal of Applied Engineering Research, 12(2),
pp.246-248.