SQL databases are structured, relational databases that excel in handling well-defined data with complex relationships, making them ideal for applications like financial systems and inventory management.

On the other hand, NoSQL databases are flexible, non-relational databases designed for scalability and adaptability to unstructured or semi-structured data, making them well-suited for modern, dynamic applications such as social media and IoT. The choice between SQL and NoSQL depends on the specific requirements of your data and application.
In this tutorial, we will explore the precise distinctions between…

WHAT IS A DATABASE ?

A database is a systematic collection of data which can be in different formats.

Databases are used to support electronic storage and for efficient manipulation and management of data.

For Example:

The Banking system database needs to store, manipulate, and present data related to users, their identification details, their accounts, their activities such as transactions, withdrawals, transfers.

It also needs to keep track of the loans applied by users, different policies offered by the bank and the changing features of them. Apart from users, data of staff, managers and branches also has to be stored and managed.

WHAT IS A RELATIONAL DATABASE ?

A relational database stores and provides access to data points that are related to one another.

They are based on the relational model, which represents data in tables.

The columns of the table hold attributes of the data. Each row of a relational database represents a record. This makes it easy to establish the relationships among data points or records.

In a relational database, each row in the table is a record with a unique ID called the key.

For Example:

A simple database for a small shop can have following tables, customer, Order, OrderItem and Product.

• Customer table will have columns such as ID, Name, PhoneNo
• Order table will have ID and customer_id
• OrderItem table will have ID, Order_ID, Product_ID & Quantity
• Product table will have ID, Name & Description

WHAT IS NON RELATIONAL OR DISTRIBUTED DATABASE ?

Non-relational databases store their data in a non-tabular form.

Instead, non-relational databases have different storage models based on specific requirements of the type of data being stored.

For Example Document databases

Data is stored as documents in a format such as JSON or XML. Each document assigned its own unique key

Graphs with nodes and edges

Each element is stored as a node. It stores the data itself

For Example: A person in a social media graph.

The Edge explains the relationship between two nodes. Edges can also have their own pieces of information, like relationship between two nodes

Key Value Data-Model

In this model every data element in the database is stored as a key value pair.

The pair consists of an attribute or “key” and its corresponding value.

We can sort of consider this model to be similar to a relational database with only two columns, the key and and the value.

Column Oriented Databases

A column oriented database is organised as a set of columns.

Column-oriented storage is used to improve analytic query performance. It reduces the overall disk I/O requirements and reduces the amount of data you need to load from disk

Properties followed by SQL TRANSACTIONS

Relational databases follow ACID properties. ACID in DBMS stands for Atomicity, Consistency, Isolation, and Durability.

Atomicity:

A transaction is a single unit of operation. Any transaction has to be either executed completely or will not be executed at all.

Consistency:

The state of the system before and after transactions should remain consistent.

For Example: During a transaction of transfer of funds, the total money present in the system should be the same

Isolation:

Durability:

After a transaction is successfully completed the changes in the database should persist even if there are any software or hardware failures

Sql & No-Sql Querying Languages

SQL Databases :
Some relational database products support pure SQL.

Many include enhanced versions —such as SQL Server’s Transact-SQL (T-SQL) for product specific features.

All SQL databases support the core ANSI/ISO language elements.

No- SQL Databases :
The language used by NoSQL depends on the type of NoSQL database and the type of operations required.

For example, MongoDB stores all documents in a JSON format. Queries are carried out using JavaScript
programming language.

SCHEMAS IN SQL & NO-SQL

SQL Databases :
SQL databases use predefined rigid schemas. They determine how tables are organised and data records are stored.

This results in a rigid structure that helps to optimize storage. This ensures data integrity but flexibility of schemas and models is limited

NO-SQL Databases:

NoSQL databases use dynamic schemas. There are no predefined data structures for organising
data.

This results in a high degree of flexibility, for example being able to add documents with different fields to the same database.

SCALABILITY IN DATABASES

Scalability in SQL Databases SQL databases mainly scale vertically.

This means they can be easily scaled up by adding resources such as faster CPUs or memory.
SQL databases however are not very efficient at scaling horizontally, over different systems and larger geographical distances. They are hence not suited for large, distributed data sets.

Scalability in NoSQL Databases

Sql & No-Sql AGE & SUPPORT

SQL databases are built on technologies that have been supported by large developer communities over the years.

It is a relatively older database model as compared to NoSQL, and hence efficient documentation, developer activity is available.

NoSQL is not as old as SQL, however it is rapidly gaining support from big tech companies, for efficient querying.

However different companies have been steadily incorporating features of both types of databases into their products to make them more universal.

For example, MongoDB now supports multi-document ACID transactions, and MySQL now includes a native JSON data type for storing and validating JSON documents.

When to use SQL VS NO-SQL ?

When to use SQL:

• For highly structured data or relational data without the complexity of multiple data stores
• Transaction based systems are used like financial transfers
• When data security and integrity is required to be of high level
• When complex queries are routinely executed
• When scale out capabilities offered by NoSQL is not required

When to use NO-SQL:

• When large amounts of unstructured or semi-structured data are used
• When dynamic schema is desired or flexibility of data model is required
• When high level of integrity of data offered by SQL database is not required
• When database has to be scaled horizontally, over various locations locationally
• To avoid overhead of structured database model