Ultimate face-off between SQL and NoSQL :

#database #mongodb #mysql #sql

What is SQL :

SQL is a standard language which stands for Structured Query Language. SQL is the core of relational database used for structuring and managing the database .
The structure is completely made of tables with rows and columns and we can later used the query language (data definition language) for retrieving the data .

Relationship in SQL :

one to one : single row in table 'A' is related to single row in table 'B'
one to many : single row in table 'A' is related to many rows in table 'B'
many to many : many rows in table 'A' is related to many rows in table 'B'
self referencing : records in table 'A' related to the same table itself .

What is NoSQl :

NoSQL, known as not only SQL database , provides a mechanism of storing and retrieval of data and is the next-gen database . It has no specific schema and can be handle huge amount of data .

More appropriate to use in big data schenario .
No row and columns here .
Collections to be table and Document to be the field in the database .
Since NoSQL have a dynamic schema we can have multiple documents in of same collection . means we can have an employee document with id and name and another could id and address .
No specific schema .
No Query language .
No or a few relationship .

SQL vs NoSQL :

Type of database
Schema
Database categories
Complex Queries
Hierarchical data storage
scalability
Language
Online processing
Base properties
External support

Type of database :

SQL is a relational database organizes structured data fields into defined columns .
NoSQL uses distributed database Data is model in the form of collection hence there is no collection . We can achieve relationship through pointers .

Schema :

SQL has a predefined schema .
NoSQL has a dynamic schema where you have to only define which document you want to store in which collection .

Database Categories :

SQL
It is having a Table based databases structure ,Where each data is stored in the form of rows and columns .
NoSQL
Four category of database :
Document Database :
pairs each key with the complex data knows as documents .
Key Value Stores :
single item in database is stored as the attribute name or a key with the value
Graph Stores :
Used to store network informations such as social networks
Wide Column Stores
Cassendra stores columns of data together instead of rows

Complex QUERIES :

SQL is good for Complex queries As the schema is structure if you want to define nested queries then we can achieve that with proper table and column name .
NoSQL Queries are not as powerful as SQL query language the language here is defined by the database schema .

Hierarchical data storage :

SQL is not a best fit as it usages tables to store data .
As the number of table increase complexity of relating them is also increases .
where as in case of NoSQL it is fits better as it uses key-value pair way of storing data similar to JSON data .

Scalability :

SQL can scale our database in a Vertical manner where we have to add new resources to our infrastructure .
NoSQl can scale in Horizontaly .

Language :

SQL has it's own language called SQL
NoSQL vary from database to database .

Online Processing :

SQL Best fits for heavy type applications . Mainly used for OLTP(online transactional processing) .
Large number of short online transactions insert update and delete
fast query processing , maintain data integrity , multi access environment and effectiveness measured by number of transactions per second . Data is stored in 3nf structure .
NoSQL can be Used for Transactional type applications , But it is not safe for high load applications .
Widely used by data mining data mining techniques in the OLAP there is aggregated historical data stored in multidimensional schema usually the star schema .
Mainly used for OLAP(online analytic application processing)

BASE Properties :

SQL follows acid properties
A Atomicity ensures transactions are completely done or fail.
C Consistency ensures transaction never leave it's current state .
I Isolation mainly handles concurrency control .
D Durability make sure that in case of any power loss or hard shut down our database integrity should not change .
NoSQL Follows CAP theorem

C consistency
A Availability
P Partition tolerance

Out of these three we can use two only . consistency ensures reader reads most recent data .

Availability ensures that the every transaction either success or fail .

Partition tolerance ensures how our system handles arbitrary message loss or failure due to hard shutdown or may be any network drop or power loss .

External support :

SQL
SQL is so robust and community support is good .
NoSQL
we have to relay upon community vendors .

Let's compare between MySQL and MongoDB as they both are famous in current market

MySQL :

It is an open source relational database management system that work on many platforms . It supports many storage engine and packed by oracle .

Features :

High Availability
High Performance
Secure data protection
Comprehensive application development
scalability and flexibility
Open source
Robust transactional support
Ease of Management

MongoDB :

It is a non-relational database which stores document in a binary representation . This kind of database is used for big data purposes and can store related information together for fast queries and usages JSON .

Features :

indexing : Index support is for improving search queries .
Replication : distribute the data among different machines .
ad-hoc Queries : support ad-hoc queries .
Schema less : No Schema is present like SQL
Sharding : It can implement sharding in case of large datasets .

Key Features :

         MySQL                    |        MongoDB 
*Triggers and SSL support .       | * Auto - sharding  
* Provides text searching and     | * Comprehensive secondary 
indexing .                        | indexes .
* Query caching .                 | * In-memory speed .  
* Integrated replication support  | * Native Replication .
* Different storage engine        | * Embedded data models support  
support .                         |

Usages :

         MySQL                    |        MongoDB 
Best fit for data with tables     | Best fit for unstructured data 
and rows .                        |
works better for small datasets . | Works better for large dataset
frequent updates .                | High write loads .
strong dependency on multi-row    | High availability in unstable  
transactions .                    | environment .
Modify large volume of records .  | Data is location based .
                                  |