Data Science Databases Syllabus

Define a database transaction.

A single logical unit of work consisting of one or more operations that must execute completely or not at all, ending with either COMMIT (make permanent) or ROLLBACK (undo).

List the four SQL transaction isolation levels from weakest to strongest.

Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Higher levels prevent more concurrency anomalies but reduce concurrency/performance.

Match each concurrency anomaly to its cause: dirty read, non-repeatable read, phantom read.

Dirty read: reading uncommitted data from another transaction. Non-repeatable read: a re-read row has changed (update). Phantom read: a re-run query returns new/removed rows (insert/delete).

What does the CAP theorem state about distributed data systems?

A distributed system can simultaneously guarantee at most two of Consistency, Availability and Partition tolerance. Since network partitions are unavoidable, real systems trade off C versus A during a partition (CP or AP).

How does NoSQL generally differ from relational databases in schema and scaling?

NoSQL is typically schema-flexible (schema-on-read) and scales horizontally across commodity nodes, often favoring availability and eventual consistency, whereas relational databases use fixed schemas, ACID transactions and scale mostly vertically.

What is the BASE model often associated with NoSQL?

Basically Available, Soft state, Eventual consistency. It relaxes strict ACID guarantees to favor availability and partition tolerance in distributed systems.

What is eventual consistency?

A consistency model in which, given no new updates, all replicas will converge to the same value over time, though reads may temporarily return stale data.

Name the four major NoSQL data-store categories.

Document-oriented, Key-Value, Column-Family (wide-column), and Graph databases.

What is a document-oriented database and what data format does it typically use?

A NoSQL store that holds data as self-describing documents (typically JSON, BSON or XML), where each document is a flexible, nested key-value structure. Example: MongoDB. Documents are grouped in collections.

What is a key-value store and what is its main strength?

The simplest NoSQL model, storing data as unique key to opaque value pairs. Its strength is extremely fast $O(1)$ lookups, inserts and deletes by key. Examples: Redis, DynamoDB, Riak.

What is a column-family (wide-column) store?

A NoSQL model that stores data in rows grouped into column families, where columns are stored together on disk and rows can have different columns. Optimized for fast reads/writes over huge sparse datasets. Examples: Cassandra, HBase.

Why does column-oriented storage speed up analytical queries?

Because values of a single column are stored contiguously, analytical scans read only the needed columns (less I/O) and achieve better compression since similar values are adjacent.

What is a graph database and what two primitives does it model?

A NoSQL database that stores data as nodes (entities) and edges (relationships), often with properties on both. It excels at traversing connected data. Examples: Neo4j, Amazon Neptune.

When are graph databases preferable to relational databases?

When relationships are first-class and queries involve deep or variable-length traversals (social networks, recommendation engines, fraud detection), avoiding expensive repeated joins.

Define Big Data using the classic "3 Vs".

Volume (huge amounts of data), Velocity (high speed of generation/processing), and Variety (structured, semi-structured and unstructured formats). Often extended with Veracity (trustworthiness) and Value.

What two core components form the foundation of Apache Hadoop?

HDFS (Hadoop Distributed File System) for distributed storage, and MapReduce (with YARN for resource management) for distributed processing.

What are the two phases of the MapReduce programming model?

The Map phase transforms input into intermediate key-value pairs; the Reduce phase aggregates all values sharing a key. A shuffle-and-sort step groups intermediate pairs by key between them.

In HDFS, what is the role of the NameNode versus the DataNode?

The NameNode is the master that stores filesystem metadata (the namespace and block locations); DataNodes are workers that store the actual data blocks and serve read/write requests.

What is the default replication factor in HDFS and why is it used?

The default replication factor is $3$: each block is stored on three nodes to provide fault tolerance and data availability if a node fails.

What is YARN's role in the Hadoop ecosystem?

YARN (Yet Another Resource Negotiator) is Hadoop's cluster resource manager and job scheduler, separating resource management from processing so multiple engines (MapReduce, Spark) can share the cluster.

What is Apache Spark and how does its core data structure differ from MapReduce?

Spark is a distributed in-memory processing engine. Its core abstraction is the RDD (Resilient Distributed Dataset), an immutable, partitioned, fault-tolerant collection. By keeping data in memory it is far faster than disk-based MapReduce for iterative work.

Differentiate transformations from actions in Spark.

Transformations (e.g. map, filter, join) are lazy and build a lineage of a new RDD/DataFrame without computing. Actions (e.g. collect, count, save) trigger actual execution of the lineage and return a result.

How does Spark achieve fault tolerance for RDDs?

Through lineage: each RDD records the sequence of transformations that built it, so lost partitions can be recomputed from the source rather than relying solely on replication.

What is data partitioning (sharding) in a distributed database?

Splitting a dataset horizontally across multiple nodes by a shard/partition key so each node holds a subset, enabling horizontal scaling of storage and load.

Distinguish replication from partitioning (sharding).

Replication copies the same data to multiple nodes for availability and read scaling/fault tolerance; partitioning (sharding) splits different data across nodes for write scaling and capacity. They are often combined.

What is a data warehouse and how does it differ from an operational (OLTP) database?

A data warehouse is a subject-oriented, integrated, time-variant, non-volatile repository optimized for analytical queries (OLAP) over historical data. OLTP databases are optimized for fast, frequent, current-state read/write transactions.

Contrast OLTP with OLAP.

OLTP (Online Transaction Processing): many short read/write transactions on current data, normalized schema. OLAP (Online Analytical Processing): complex read-heavy aggregations over large historical datasets, denormalized/star schema.

What are the three steps of ETL?

Extract data from source systems, Transform it (clean, validate, deduplicate, aggregate, reformat to the target schema), and Load it into the target data warehouse.

How does ELT differ from ETL and why has it become popular?

In ELT, raw data is Extracted and Loaded into the target first, then Transformed using the warehouse's own compute. It suits cloud warehouses and data lakes with cheap, scalable storage and processing, and preserves raw data for flexible later transformation.

Contrast a star schema with a snowflake schema in data warehouse design.

A star schema has a central fact table linked to denormalized dimension tables (simple, fast queries). A snowflake schema normalizes the dimensions into related sub-tables (less redundancy, more joins, slightly slower queries).