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 valueGraph Stores :
Used to store network informations such as social networksWide 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 .
|
Top comments (3)
Nice overview! Next time you might have to add NewSQL to your database categories 😁
definitely 🔥🔥🔥 i have read the blog and excited to use it .
very cool!