NoSQL

Advanced Techniques in SQL

1. ACID

Now we explore the basic principles of ACID (Atomicity, Consistency, Isolation, Durability) in database management. Learn how these principles ensure reliable transactions by guaranteeing complete operations, keeping data consistent, isolating concurrent transactions, and ensuring permanent data storage.

Course Overview

What is ACID?

Atomicity

Challenge: Transaction Creation

Consistency

Isolation. Read Committed and Uncommitted Levels

Isolation. Repeatable Read and Serializable Levels

Challenge: Specifying Isolation Level for Transaction

2. Query optimization.Indexes

Explore how to enhance the efficiency of your SQL queries through index optimization. Delve into the significance of indexes in database performance, learning how to design, implement, and utilize them effectively. Discover strategies for choosing the appropriate indexes, understanding their impact on query execution plans, and optimizing query performance through index selection and utilization.

What are Indexes

B-tree Indexing

Challenge: Creating B-tree Index

Hash Indexing

Data Caching

General Optimization Strategies

Challenge: Query Optimization

3. Some Additional Topics

This section explores pivotal topics such as Triggers, Window Functions, and DCL (Data Control Language), offering comprehensive insights into their implementation and optimization strategies. Furthermore, it navigates the landscape of NoSQL DBMS, providing a thorough understanding of its architecture, advantages, and practical applications.

Triggers

Challenge: Creating After Trigger

Window Functions

Types of Window Functions

Challenge: Using Window Function

Data Control Language

Views

NoSQL

NoSQL, which stands for "Not Only SQL", refers to a broad category of database management systems that are designed to handle a wide variety of data models, particularly non-relational or semi-structured data.
Unlike traditional relational databases, which use structured query language (SQL) to define and manipulate data, NoSQL databases offer a more flexible schema design and are optimized for large-scale data storage and retrieval.

Why NoSQL?

NoSQL databases are designed to handle:

Unstructured or Semi-structured Data: Data that does not fit neatly into tables, rows, and columns, such as JSON documents, key-value pairs, graphs, and wide-column stores;
High Scalability: Horizontal scaling, where databases can distribute data across many servers, making it easier to handle large volumes of data and high transaction loads;
Flexibility: Dynamic schemas that allow for the addition of new fields without the need for database downtime or complex schema migrations.

A real-life case where NoSQL is preferable is in social media platforms, such as Facebook or Twitter.
These platforms handle massive amounts of unstructured data, including user posts, comments, likes, and multimedia content, which are continuously generated at high velocity. NoSQL databases provide the scalability and flexibility needed to efficiently manage and quickly retrieve this diverse and dynamic data without the constraints of a fixed schema.

NoSQL database types

NoSQL Databases

NoSQL DB Name	NoSQL DB Type	Brief Description
MongoDB	Document Store	Stores data in JSON-like documents, providing flexibility and scalability for handling unstructured data.
Cassandra	Column-Family Store	Highly scalable and distributed database designed to handle large amounts of data across many commodity servers with no single point of failure.
Redis	Key-Value Store	In-memory data structure store, used as a database, cache, and message broker, known for its high performance.
Neo4j	Graph Database	Optimized for storing and querying highly interconnected data, making it ideal for applications like social networks and recommendation engines.
CouchDB	Document Store	Uses JSON for documents, JavaScript for MapReduce queries, and HTTP for its API, focusing on ease of use and scalability.

Everything was clear?

Section 3. Chapter 8