SQL vs NoSQL Flashcards
What are SQL databases?
also known as relational databases. They have a predefined schema, and data is stored in tables consisting of rows and columns. SQL databases follow the ACID (Atomicity, Consistency, Isolation, Durability) properties to ensure reliable transactions
what are tha acid properties?
The ACID properties are a set of characteristics that define the reliability and consistency of a transaction in a relational database management system (RDBMS). ACID stands for Atomicity, Consistency, Isolation, and Durability.
Together, these ACID properties provide a mechanism to ensure the correctness, consistency, and reliability of a database. They ensure that transactions are executed reliably and consistently, even in the presence of system failures, network issues, or other problems
what is ATOMICITY?
Atomicity: Atomicity ensures that a transaction is treated as a single unit of work, meaning that either all of its operations are executed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back, and the database is left unchanged.
what is consistency?
Consistency: Consistency ensures that a transaction brings the database from one valid state to another. It enforces integrity constraints and rules defined on the database, ensuring that data remains consistent and valid throughout the transaction.
what is isolation?
Isolation: Isolation ensures that concurrent transactions do not interfere with each other. Each transaction is executed in isolation, as if it were the only transaction running on the database. This prevents data inconsistencies and conflicts that may arise when multiple transactions access and modify the same data simultaneously.
what is durability?
Durability: Durability guarantees that once a transaction is committed, its changes are permanent and will survive any subsequent system failures, such as power outages or crashes. The changes made by a committed transaction are stored in a durable manner and will not be lost.
WHAT DOES A TRANSACTION REFER TO IN RELATIONAL DATABASES?
A database transaction is used to create, update, or retrieve data.
Think of a database transaction as a series of operations performed within a DBMS. The transaction system ensures that the data in the database always remains in a reliable and consistent state.
If a transaction is successful, the data in the database is updated as described in the instructions contained in the transaction. This is called a “commit.”
If a transaction fails, all transaction steps performed prior to the step that led to the failure are reversed. The data in the database returns to its initial state as if the transaction had never been executed. This operation is called a “rollback.”
What are the Advantages of SQL databases?
Predefined schema: Ideal for applications with a fixed structure.
ACID transactions: Ensures data consistency and reliability.
Support for complex queries: Rich SQL queries can handle complex data relationships and aggregation operations.
Scalability: Vertical scaling by adding more resources to the server (e.g., RAM, CPU).
what are some Limitations of SQL databases?
Rigid schema: Data structure updates are time-consuming and can lead to downtime.
Scaling: Difficulties in horizontal scaling and sharding of data across multiple servers.
Not well-suited for hierarchical data: Requires multiple tables and JOINs to model tree-like structures.
what are NoSQL Databases?
NoSQL (Not only SQL) databases refer to non-relational databases, which don’t follow a fixed schema for data storage. Instead, they use a flexible and semi-structured format like JSON documents, key-value pairs, or graphs. MongoDB, Cassandra, Redis, and Couchbase are some popular NoSQL databases.
what are json documents?
JSON (JavaScript Object Notation) is a lightweight, language-independent data format that uses human-readable text to store and transmit data objects consisting of attribute-value pairs and arrays (or other serializable values)
A JSON document database is a type of non-relational database that is designed to store and query data as JSON documents, rather than normalizing data across multiple tables, each with a unique and fixed structure, as in a relational database
what are some of the Advantages of NoSQL databases?
Flexible schema: Easily adapts to changes without disrupting the application.
Scalability: Horizontal scaling by partitioning data across multiple servers (sharding).
Fast: Designed for faster read and writes, often with a simpler query language.
Handling large volumes of data: Better suited to managing big data and real-time applications.
Support for various data structures: Different NoSQL databases cater to various needs, like document, graph, or key-value stores.
what are some of the Limitations of NoSQL databases?
Limited query capabilities: Some NoSQL databases lack complex query and aggregation support or use specific query languages.
Weaker consistency: Many NoSQL databases follow the BASE (Basically Available, Soft state, Eventual consistency) properties that provide weaker consistency guarantees than ACID-compliant databases.
