Nitendra Gautam

Main difference between SQL vs Nosql Database

A relational database is a digital database based on the relational model of data and uses Structured Query Language(SQL) and follows ACID property strictly.

SQL databases have been a primary data storage mechanism for more than four decades. Usage exploded in the late 1990s with the rise of web applications and open-source options such as MySQL, PostgreSQL and SQLite.

NoSQL databases have existed since the 1960s, but have been recently gaining traction with popular options such as MongoDB, CouchDB, Redis, Apache Cassandra.Non Relational or NoSQL Database is a data storage system that was designed for unstructured data which does not follows a ACID property like a traditional RDBMS .NoSQL databases follows a property called CAP theorem.

ACID property

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows:

  • Atomicity: Atomicity requires that each transaction be “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.
  • Consistency: The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
  • Isolation: The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed sequentially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method (i.e. if it uses strict - as opposed to relaxed - serializability), the effects of an incomplete transaction might not even be visible to another transaction.
  • Durability: Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

CAP Theorem

The CAP Theorem states that, a distributed system which are connected through a network, can only guarantee/strongly support two of the following three properties.

  • Consistency: A guarantee that every node in a distributed cluster returns the same, most recent, successful write. Consistency refers to every client having the same view of the data. There are various types of consistency models. Consistency in CAP (used to prove the theorem) refers to linearizability or sequential consistency, a very strong form of consistency.

  • Availability: Every non-failing node returns a response for all read and write requests in a reasonable amount of time. To be available, every node on (either side of a network partition) must be able to respond in a reasonable amount of time.

  • Partition Tolerant: The system continues to function and upholds its consistency guarantees in spite of network partitions. Distributed systems guaranteeing partition tolerance can gracefully recover from partitions once the partition heals.

Non Relational(NoSQL) database vs Relational Database(SQL)

NameRelational Database(SQL)Non Relational Database(NoSQL)
Database ModelRDBMS in form of Tables(row/column)Graph ,Columnar,Key-value pair ,Document
Data SchemeHas Predefined and Strict Schemaunstructured data and supports flexible Schema
Transaction SupportYes, supports ACID propertyNo ,based on BASE{ CAP Therorem}
Foreign KeysYesNo
Query LanguageStructured Query Language(SQL)Unstructured Query Language(UnQL)
Data IntegrityMost SQL databases allow you to enforce data integrity rules using foreign key constraints. The schema forces rules for the database to followThe same data integrity options are not available in NoSQL database ; you can store what you want regardless of any other documents.
JoinsSQL queries offer a powerful JOIN clause. We can obtain related data in multiple tables using a single SQL statement.NoSQL has no equivalent of JOIN
ScalabilityVertically ScalableHorizontal Scalable
ExamplesMySql ,Oracle ,SqLite ,MsSql,PostgreSqlCassandra ,MongoDB, Redis ,Hbase ,Neo4j ,CouchDb



Cap Theorem Revisited

Understanding Cap Theorem