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 .
- 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 .
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 .
- Type of database
- Database categories
- Complex Queries
- Hierarchical data storage
- Online processing
- Base properties
- External support
- 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 .
- 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 .
It is having a Table based databases structure ,Where each data is stored in the form of rows and columns .
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
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 .
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 .
- SQL can scale our database in a Vertical manner where we have to add new resources to our infrastructure .
- NoSQl can scale in Horizontaly .
- SQL has it's own language called SQL
- NoSQL vary from database to database .
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)
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
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 .
SQL is so robust and community support is good .
we have to relay upon community vendors .
Let's compare between MySQL and MongoDB as they both are famous in current market
It is an open source relational database management system that work on many platforms . It supports many storage engine and packed by oracle .
- High Availability
- High Performance
- Secure data protection
- Comprehensive application development
- scalability and flexibility
- Open source
- Robust transactional support
- Ease of Management
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 .
- 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 .
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 . |
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 . |