SQL vs NoSQL

Question 1

What are the primary types of NoSQL dbs?

Answer 1

1. Key value stores
2. Document databases
3. Wide-Columnar Databases
4. Graph databases

Question 2

What is an example of a NoSQL key-value store?

Answer 2

Dynamo & Redis

Question 3

What is an example of a NoSQL Document DB?

Answer 3

MongoDB

Question 4

What is an example of a NoSQL Wide Columnar DB?

Answer 4

Cassandra & HBase

Question 5

What are use cases for a wide columnar database?

Answer 5

Log data
IoT (Internet of Things) sensor data
Time-series data, such as temperature monitoring or financial trading data
Attribute-based data, such as user preferences or equipment features
Real-time analytics

Question 6

What is the difference between a row based DB vs a column based DB?

Answer 6

Columns are stored together on disk vs rows

Question 7

What are columnar databases good for?

Answer 7

Columnar databases are great for data analytics. They scan fewer rows and process less data than row-oriented databases.

Because only the necessary columns are read from storage, I/O costs and time are minimized, providing an edge over traditional row-based databases in analytics scenarios.

Question 8

What is an example of a NoSQL graph database and what’s a good use case?

Answer 8

Neo4J, movie recommendations

Question 9

What is the difference in scalability between NoSQL vs SQL dbs?

Answer 9

SQL dbs are vertically scaleable, but horizontally scaling is very challenging. NoSQL dbs are horizontally scalable.

Question 10

What is the different in data integreity guarantees between NoSQL and SQL?

Answer 10

Reliability or ACID Compliancy (Atomicity, Consistency, Isolation, Durability): The vast majority of relational databases are ACID compliant. So, when it comes to data reliability and safe guarantee of performing transactions, SQL databases are still the better bet.

Most of the NoSQL solutions sacrifice ACID compliance for performance and scalability.

Question 11

What is atomicity?

Answer 11

Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely or fails completely

Question 12

What is consistency?

Answer 12

Consistency ensures that a transaction can only bring the database from one consistent state to another, preserving database invariants:

Question 13

What is isolation?

Answer 13

Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially

Question 14

What is durability?

Answer 14

Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash)

Question 15

Why use a relational DB?

Answer 15

We need to ensure ACID compliance and data is structured and unchanging (scaling not needed).

Question 16

Why choose NoSQL db?

Answer 16

Storing large volumes of data that often have little to no structure.

Making the most of cloud computing and storage. Cloud-based storage is an excellent cost-saving solution but requires data to be easily spread across multiple servers to scale up.

Rapid development. NoSQL is extremely useful for rapid development as it doesn’t need to be prepped ahead of time

Question 17

Compare ACID vs BASE.

Answer 17

ACID prioritizes correctness (strong consistency, transactions).

BASE prioritizes availability and scale (eventual consistency, partition tolerance).

Question 18

What is BASE?

Answer 18

BASE (Basically Available, Soft state, Eventual consistency)

Used by large distributed NoSQL systems (Cassandra, DynamoDB, Riak).

BASE systems are usually AP under CAP (favor availability and partition tolerance over strong consistency).

Question 19

What is ACID?

Answer 19

ACID (Atomicity, Consistency, Isolation, Durability)

Used by traditional relational databases (PostgreSQL, MySQL, Oracle).

ACID systems typically choose CP in the CAP theorem (sacrifice availability during partitions to maintain consistency).

Question 20

When would you use BASE databases?

Answer 20

BASE (AP, eventually consistent) systems are ideal when you need:
High write throughput
* They accept writes even under node failures or partitions.
* No global locking or coordination slows them down.

High availability
* Writes must succeed even if some replicas are down.

Massive scale
* Partitioned/replicated horizontally with ease.
* Tolerance for temporary inconsistency
* Reads may return stale data for a short time.
* System is okay with eventual convergence.

In short: Write-heavy, latency-sensitive workloads fit BASE well.

Question 21

Use cases for ACID databases?

Answer 21

Bank accounts / ledgers
* Must never read an old balance or double-spend.

Payments
* Atomicity and correctness > availability.

Inventory with tight constraints
* Must prevent overselling limited stock (tickets, seats, etc.).

Booking systems
* Two people cannot book the same hotel room.

User identity & authentication data
* Must ensure unique usernames, consistent token state.

Critical configuration / metadata stores
* Raft-backed systems (etcd, ZooKeeper) ensure correctness.

Question 22

What ACID is necessary for real time banking?

Answer 22

Multi-item transactions with isolation:

ACID databases let you update multiple rows together atomically and prevent other operations from seeing partial results.

Strong consistency across partitions:

ACID systems give a single global order of all writes across all keys; BASE systems only guarantee ordering within a single key or partition.