Database Fundamentals — CompTIA DataSys+ (DS0-001) GED Study Notes

Chapter 1

Database Fundamentals

Course: Comptia Datasys Ds0 001
Subject: Comptia Datasys Plus Ds0 001
Chapter: Database Fundamentals

Modern data storage systems can be classified in multiple ways based on how data is organized, stored, queried, and scaled. Understanding these structures helps in selecting the appropriate database technology for specific application needs such as scalability, consistency, performance, and data modeling flexibility.

1. Relational vs. Non-relational Databases

1. Relational Databases

Relational databases are based on the relational model proposed by E.F. Codd in 1970. They organize data into structured tables (relations), where each row is a record and each column is an attribute of the data. Tables can be linked through keys to model relationships between entities.

Characteristics

ACID Compliance: Critical for systems requiring data integrity and consistency.

Atomicity: Ensures that all steps in a transaction succeed or none do (e.g., transferring money between bank accounts).

Consistency: Guarantees that a transaction brings the database from one valid state to another.

Isolation: Ensures transactions are processed independently, avoiding race conditions or dirty reads.

Durability: Once a transaction is committed, its data is saved permanently, even if the system crashes.

Atomicity: Ensures that all steps in a transaction succeed or none do (e.g., transferring money between bank accounts).
Consistency: Guarantees that a transaction brings the database from one valid state to another.
Isolation: Ensures transactions are processed independently, avoiding race conditions or dirty reads.
Durability: Once a transaction is committed, its data is saved permanently, even if the system crashes.
Rigid Schema: Data types and relationships must be defined in advance. Changing schemas requires migrations, which can be complex in production environments.
Normalization: Data is broken into logical tables to reduce redundancy and improve data integrity. For example, separating "Customer" and "Orders" into two tables linked via foreign keys.
OLTP Optimization: Designed for frequent read/write operations with real-time data, such as insert, update, and delete transactions.
Vertical Scaling: Performance improvements are typically achieved by upgrading server hardware (adding RAM, CPUs, or SSDs).

Examples

MySQL: Open-source, widely used in web hosting and small-to-medium applications. Supports replication and clustering.
PostgreSQL: Advanced open-source RDBMS with features like JSON support, indexing strategies (GIN, GiST), and geographic data (PostGIS).
Oracle Database: Enterprise-grade database with advanced concurrency control, RAC (Real Application Clusters), and extensive tooling.
Microsoft SQL Server: Used in Windows environments for applications that integrate with Active Directory and .NET frameworks.

Real-world Use Case

Enterprise Financial Systems: A bank processes millions of transactions daily that must be recorded accurately. Systems such as core banking software use PostgreSQL or Oracle to ensure transactional integrity. For example, transferring money from Account A to Account B involves a debit from A and a credit to B—both operations must occur atomically. If one fails, the entire operation is rolled back to maintain consistency.

2. Non-relational Databases (NoSQL)

NoSQL databases emerged to address limitations of traditional RDBMSs in handling scalability, performance, and unstructured or semi-structured data. Instead of fixed tables, NoSQL systems use flexible formats such as key-value pairs, documents, columns, and graphs.

Characteristics

Flexible Schema: Allows for rapidly changing application data structures. New fields can be added without schema migration.
BASE Properties:

Basically Available: The system guarantees availability even under partial failure.

Soft State: The system's state can change over time due to eventual consistency or background processing.

Eventual Consistency: Data will become consistent across the distributed system over time rather than instantly.

Basically Available: The system guarantees availability even under partial failure.
Soft State: The system's state can change over time due to eventual consistency or background processing.
Eventual Consistency: Data will become consistent across the distributed system over time rather than instantly.
Designed for Distributed Systems:

Built to scale horizontally by adding nodes.

Often includes features such as automatic sharding and replication for high availability.

Built to scale horizontally by adding nodes.
Often includes features such as automatic sharding and replication for high availability.
Optimized for Big Data and High Throughput: Can handle large volumes of concurrent operations efficiently, especially in read/write-heavy environments.
Less rigid transaction support: Not all NoSQL databases support multi-document transactions; those that do often provide eventual consistency guarantees instead of strict ACID compliance.

Examples

MongoDB: A document database storing data in flexible, JSON-like documents. Ideal for content-heavy apps.
Cassandra: A wide-column store optimized for write-heavy workloads and designed for high availability across multiple data centers.
Redis: An in-memory key-value store that supports rich data structures (lists, sets, hashes). Known for ultra-low latency.
Neo4j: A graph database used to model and query complex relationships, particularly effective for traversals and network analysis.

Real-world Use Case

Content Management System (CMS): A publishing platform like a news portal uses MongoDB to manage articles, author profiles, categories, tags, and comments. Each document can contain a different structure—some articles may have video embeds, others only text. MongoDB enables quick iterations without worrying about strict schema adherence, and indexed fields allow efficient filtering and full-text searches.

Another example is IoT platforms, where Cassandra is used to ingest high-velocity time-series data from thousands of sensors deployed across a city. The database is partitioned by device ID and timestamp, supporting efficient queries over sliding time windows.

Summary Comparison

2. Linear vs. Non-linear Data Formats

1. Linear Data Format

Linear data formats organize data in a sequential, ordered structure. Each unit of data (e.g., a row in a table or a line in a text file) is stored one after another in a flat, linear manner. This structure is particularly well-suited for applications requiring transactional consistency, ordered processing, and simplicity in access patterns.

Characteristics

Sequential Storage: Records are stored row-by-row, with each row representing a complete entity or transaction.
Fixed or Semi-fixed Schema: Each record follows the same format or column structure.
Efficient for OLTP Workloads: Optimized for frequent insert, update, and delete operations where data is processed in order.
Easier Indexing: Indexes like B-trees and hash indexes work efficiently in row-based formats.
Limited Flexibility: Not well-suited for storing nested or variable data structures.

Common Examples

MySQL (InnoDB Engine): Stores data in a row-oriented format. Each row is accessed directly, and indexes are used for quick lookups.
Microsoft SQL Server: Stores and retrieves rows using clustered and non-clustered indexing.
CSV and TSV Files: Flat file formats used for storing tabular data in a linear, plain-text format. Useful for data exchange, logs, or backups.
Logs and Audit Trails: Typically stored in a sequential format to preserve chronological order.

Real-world Use Case

Banking Systems and Financial Transactions A core banking system must ensure that account activities (deposits, withdrawals, transfers) are processed in the correct sequence and with transactional guarantees. For example, a money transfer from Account A to B must ensure the debit is successful before the credit is processed. Linear formats in relational databases enforce these requirements via ACID-compliant operations and transaction logs.

Technical Considerations

Redo Logs and Write-Ahead Logging (WAL): Common mechanisms in relational systems to preserve linear ordering of transactional changes.
OLTP Workload Example: High-speed POS (Point-of-Sale) systems where transactions must be processed in the exact order they are initiated.

2. Non-linear Data Format

Non-linear data formats structure information in non-sequential or hierarchical ways, allowing the storage and modeling of complex, dynamic relationships and variable-length data structures. This format is optimal for applications where data doesn’t fit neatly into rows and columns.

Characteristics

Supports Nested or Hierarchical Data: Enables documents to include arrays, objects, and sub-objects.
Flexible Schema: Different records can have different fields, allowing schema evolution without downtime.
Relationship-Oriented: Particularly in graph databases, relationships between entities are first-class citizens and are efficiently traversed.
Efficient for Complex Queries: Useful when querying for deeply connected or hierarchical data (e.g., all comments on a blog post, or the shortest path between users).

Common Examples

MongoDB: Uses BSON documents to represent data. A single document can contain nested arrays and subdocuments representing an entire object graph (e.g., user profile, orders, preferences).
Neo4j: A graph database that stores nodes (entities) and edges (relationships). Supports traversal queries and pathfinding using the Cypher query language.
Amazon Neptune: A managed graph database service supporting both property graphs and RDF triples for semantic data.

Real-world Use Case

Social Networks and Recommendation Engines Applications like Facebook, LinkedIn, or Instagram rely on graph data models. Users are nodes; relationships like "follows", "likes", "comments on", or "works with" are edges. A graph database like Neo4j allows efficient queries such as:

Who are the mutual friends of User A and User B?
Suggest new connections based on shared interests or common connections.
Traverse a network of followers up to 3 levels deep.

Similarly, in document databases like MongoDB, a blog post can include an array of comments, each with its own user metadata and reactions, all in a single, self-contained document. This avoids the need for joins, speeding up data retrieval.

Technical Considerations

Query Performance in Hierarchies: Recursive joins in relational systems are inefficient compared to native traversal in non-linear formats.
Indexing in Graphs: Graph databases often use index-free adjacency where nodes maintain direct references to connected nodes, allowing constant-time edge traversal.

Summary Comparison

Choosing Between Linear and Non-linear Formats

Use a linear format when:

Transactions must be processed in order.
Data is uniform and fits into well-defined tables.
You require strong transactional guarantees.

Use a non-linear format when:

You have complex relationships or nested data.
Your application schema changes frequently.
You need to traverse or explore connections efficiently.

3. NoSQL Database Types

NoSQL databases are non-relational data storage systems designed to handle a wide variety of data types and access patterns. Unlike traditional relational databases, NoSQL systems offer flexibility in schema design and are optimized for scalability, performance, and distributed architecture. Depending on their data model and use case, NoSQL databases are generally classified into four main categories: document, key-value, column-oriented, and graph databases.

1. Document Databases

Document-oriented databases store data in formats like JSON, BSON, or XML. Each document represents a self-contained data unit and can include nested fields such as arrays and sub-documents. Unlike relational tables, document databases allow varying fields and structures across different records within the same collection.

Features:

Schema-less or dynamic schema support, allowing easy evolution of data structures over time
Nested documents and arrays enable intuitive modeling of hierarchical data
Indexing on nested fields supports efficient search and filtering
Built-in support for replication, sharding, and horizontal scaling

Examples: MongoDB, CouchDB

Use case: A product catalog in an e-commerce application is a typical example. Each product might have different fields like color, size, brand, availability, and customer reviews. Storing these as individual documents allows each product to have a flexible structure tailored to its category.

Real-life example: eBay uses document stores to power its inventory systems where items have hundreds of different attributes that are not shared across all products.

2. Key-Value Stores

Key-value databases store data as unique key and value pairs. The key acts as an identifier, while the value can be a simple string, JSON blob, image, or even serialized object. These databases are optimized for fast data retrieval based on the key and are often used for scenarios requiring minimal latency.

Features:

Extremely fast read and write operations due to direct key lookups
Simple architecture makes it ideal for high-throughput workloads
Values can be opaque to the database (no querying inside values)
Many implementations support in-memory storage for added performance

Examples: Redis, Amazon DynamoDB

Use case: In an e-commerce platform, a user’s shopping cart can be stored using their session ID or user ID as the key. The cart's contents (product IDs, quantities, prices) are stored as the value. As users add or remove items, the key-value store updates the cart data instantly.

Real-life example: Twitter uses Redis to cache user timelines and reduce the load on its relational database backend, ensuring a low-latency user experience.

3. Column-Oriented Databases (Wide-Column Stores)

Column-oriented databases store data by columns instead of rows. This model allows for efficient querying of large datasets, especially in analytical and time-series workloads. Data is organized into column families, where each row can have a different set of columns.

Features:

Reads are optimized for aggregation queries across columns (e.g., computing averages or totals)
High compression rates due to similar data types stored together
Partitioning and replication enable efficient horizontal scaling
Often designed for distributed environments with eventual consistency

Examples: Apache Cassandra, HBase, ScyllaDB

Use case: A time-series monitoring system collects millions of metrics from servers and devices. Each metric (CPU usage, memory usage, I/O throughput) can be stored in separate columns, and data can be queried across time intervals for performance analysis.

Real-life example: Netflix uses Cassandra to store and retrieve millions of metrics per second, helping monitor its microservices and infrastructure in real time.

4. Graph Databases

Graph databases store data in the form of nodes (entities) and edges (relationships). These databases are based on graph theory and are ideal for representing interconnected data with complex relationships. They enable fast traversal operations that would be inefficient with traditional databases.

Features:

Store and process deeply connected data efficiently
Schema flexibility allows different types of nodes and relationships
Support for pathfinding, recommendation systems, and social graph analysis
Index-free adjacency enables high-speed traversal by storing direct links between entities

Examples: Neo4j, Amazon Neptune, ArangoDB

Use case: A recommendation system that suggests friends, articles, or products based on a user's behavior and relationships with other users or items can benefit from a graph database. These systems rely on identifying shortest paths, mutual connections, or common interests.

Real-life example: LinkedIn uses graph structures to power its professional network, enabling features like "People You May Know" or "Connection Paths" between users.

Summary Table

Choosing the Right NoSQL Type

Selecting the right NoSQL database depends on the application’s requirements:

Choose a document database when working with semi-structured data and you need flexibility in record formats.
Use a key-value store when speed is critical and you're retrieving full records by a unique key.
Use a columnar database for heavy analytical workloads involving large-scale structured data.
Choose a graph database when your data naturally forms complex relationships and you require fast traversals.

Each NoSQL type is specialized, so understanding your access patterns, consistency needs, and data complexity will help determine the most suitable solution.

4. Tools – NoSQL Database Technologies

Modern applications often require specialized databases tailored for performance, scalability, and specific data models. Below are commonly used NoSQL tools, each suited to different use cases and system architectures.

1. Cassandra

Type: Column-family store (wide-column NoSQL database) Architecture: Decentralized, peer-to-peer (masterless) Data model: Tables with rows and columns, grouped into column families; supports flexible columns per row

Strengths:

High availability and fault tolerance through replication across nodes
Write-optimized using log-structured merge trees
Horizontally scalable for handling petabyte-scale workloads
Tunable consistency (can balance between eventual and strong consistency)

Use case: Ingesting time-series data or IoT telemetry from thousands of distributed sensors. Each device can log data asynchronously with minimal risk of write contention.

Example: Netflix uses Cassandra to store and query large volumes of video metadata and playback logs, supporting personalized recommendations and operational metrics at scale.

2. MongoDB

Type: Document-oriented database Data model: JSON-like (BSON) documents stored in collections Architecture: Replica sets and sharded clusters

Strengths:

Flexible schema for storing varying document structures
Rich query language with aggregation pipeline and indexing
Built-in horizontal scaling via sharding
Support for multi-document transactions and geospatial queries

Use case: Managing dynamic and content-rich data such as blogs, user profiles, or product catalogs with varying fields and nested attributes.

Example: eBay uses MongoDB to store and index metadata for items, enabling fast, flexible search and recommendation across millions of listings.

3. Neo4j

Type: Graph database Data model: Property graphs (nodes, relationships, properties) Query language: Cypher

Strengths:

Fast traversal of deeply connected data
Native support for graph structures without needing join operations
Index-free adjacency improves traversal performance
Suited for recursive and relationship-centric queries

Use case: Modeling and querying complex relationships, such as social networks or fraud detection systems, where data connections are more important than individual records.

Example: LinkedIn uses graph-based techniques to suggest new connections, calculate degrees of separation, and analyze user relationship patterns.

4. Amazon DynamoDB

Type: Fully managed key-value and document store Data model: Tables with partition key and optional sort key; supports nested structures Architecture: Serverless, automatically scales across regions

Strengths:

Auto-scaling read/write capacity based on traffic
Global tables for multi-region availability
Low latency access, integrated with AWS services
Ideal for high-throughput workloads with simple access patterns

Use case: Storing user session data, shopping cart information, or high-speed write-heavy data such as leaderboard scores in online games.

Example: Snapchat uses DynamoDB to manage real-time interaction data across billions of users, supporting stories, chats, and ephemeral content reliably.

5. Cosmos DB (Microsoft Azure)

Type: Multi-model database (key-value, document, graph, column-family) Data model: Depends on the chosen API (SQL API, MongoDB API, Cassandra API, Gremlin API)

Strengths:

Global distribution with multi-region writes
Consistent low-latency performance (single-digit ms)
Multi-API support allows easy migration or integration
SLAs for availability, latency, throughput, and consistency

Use case: Running distributed applications that require global data consistency, real-time user interaction, and dynamic schema support—particularly in hybrid or cloud-native architectures.

Example: Jet.com uses Cosmos DB to manage real-time inventory levels and pricing data, ensuring fast checkout experiences and up-to-date information for users across regions.

Summary Table