Database Systems - Chapter 1 & 2 Practice Flashcards

Comprehensive practice flashcards covering fundamental database concepts, the relational model, engine internals, ACID properties, and modern 2026 industry technology updates as presented in the course materials.

How does the transcript distinguish between Data and Information?

Data refers to raw, unprocessed facts such as numbers or text strings, while Information is data that has been processed, structured, and contextualized to convey meaning (e.g., a calculated GPA).

What is the definition of a Database according to the course guide?

A structured, persistent collection of logically related data stored on secondary or tertiary non-volatile storage media.

What is the difference between a Database Schema and a Database Instance?

A Schema (Intent) is the static, logical blueprint or structural definition that changes infrequently, while an Instance (Extension) is the actual data content stored at a specific moment in time which changes constantly.

What is Logical Data Independence?

The ability to modify the conceptual schema (such as adding a new attribute column) without breaking existing application queries or forcing modifications to higher-level application code.

What is Physical Data Independence?

The ability to modify physical storage details (such as creating a B+ Tree index or relocating data files) without affecting the logical schema or SQL code.

In Relational Model terminology, what are the everyday equivalents for Relation, Tuple, and Attribute?

Relation maps to Table, Tuple maps to Row or Record, and Attribute maps to Column or Field.

Define Domain in the context of the Relational Model.

The set of atomic, permissible values allowed for a given column (e.g., integers from $1$ to $100$).

What is the difference between Cardinality and Degree (Arity)?

Cardinality is the total number of tuples (rows) currently present in a relation, while Degree is the total number of attributes (columns) defined in the relation schema.

What are the four subfamilies of SQL languages?

DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL (Transaction Control Language).

In DBMS engine internals, what is a Page (or Block)?

The fixed-size physical unit of storage, typically $4\text{ KB}$ or $8\text{ KB}$, transferred between secondary storage (SSD/HDD) and RAM main memory.

What is Pointer Swizzling?

An in-memory optimization that replaces on-disk logical page identifiers with direct memory address pointers to eliminate central hash table lookup overhead.

Define the ACID properties of a transaction.

Atomicity (All-or-Nothing), Consistency (transition between valid integrity states), Isolation (invisible intermediate states to concurrent transactions), and Durability (updates permanently survive crashes).

What is Write-Ahead Logging (WAL)?

A recovery protocol requiring that transaction log records be flushed to non-volatile disk storage before modified data pages are written to disk.

What are the definitions of OLTP, OLAP, and HTAP in modern industry terms?

OLTP (Online Transaction Processing) involves high volumes of fast row-level operations; OLAP (Online Analytical Processing) involves complex aggregations over large datasets; HTAP (Hybrid Transactional/Analytical Processing) executes both real-time analytical scans and transactional data on one platform.

How does a DBMS improve upon Traditional File Processing Systems regarding Data Redundancy?

Traditional file systems have high redundancy across files leading to inconsistency, while a DBMS uses centralized data organization to minimize redundancy.

What is the calculated Total Block I/O Cost for an External Merge Sort of $190$ million voter records ($100$ bytes each) with a $1\text{ GB}$ buffer?

The total dataset is $19\text{ GB}$. Pass 0 creates $19$ runs. Pass 1 merges them. Every record is read and written once per pass, resulting in Total Disk I/O = $2 \times 19\text{ GB} \times (Pass\text{ }0 + Pass\text{ }1) = 76\text{ GB}$ of transfer.

Identify the three levels of the ANSI/SPARC Three-Schema Architecture.

External View (User/Portal level), Conceptual Schema (Logical tables and constraints), and Internal/Physical Schema (Index and page layouts on disk).

What is Vector Embedding in 2026 technology contexts?

A high-dimensional floating-point array generated by deep learning AI models that captures the semantic meaning of unstructured text, images, or audio.

What architectural change defines the shift from Monolithic DBMS to Cloud-Native DBMS (2026)?

The decoupling of Stateless Compute (CPU/RAM) from a Shared Distributed Storage Layer (NVMe Pool or S3), allowing independent elastic scaling.

What is the primary difference between a Super Key and a Candidate Key?

A Super Key is any set of attributes that uniquely identifies a tuple; a Candidate Key is a minimal Super Key, meaning no subset of it can also be a Super Key.

What are the rules for a Primary Key (PK) regarding NULL values?

A Primary Key is a Candidate Key selected as the main identifier and it cannot contain any 'NULL' values (Entity Integrity).

Define Referential Integrity.

A rule stating that the value of a Foreign Key (FK) in a child table must either exist in the Primary Key (PK) of the parent table or be 'NULL'.

Name four referential actions used to handle foreign key violations during updates or deletions.

NO ACTION (Default), CASCADE (chain reaction), SET NULL, and SET DEFAULT.

How do Microsoft SQL Server and SQLite differ regarding Foreign Key activation?

In MS SQL Server, FKs are automatically enabled by default; in SQLite, they are disabled by default and require the command 'PRAGMA foreign_keys = ON;' to activate.

What is the difference between a Relational VIEW and a physical base TABLE?

A VIEW does not store real data and only saves the query definition, whereas a base TABLE physically stores the data on disk.

What is the historical order of data models starting from 1965?

Hierarchical (1965), Network (1965), Relational (1970s), Object-Relational (1980s-1990s), XML/Semi-structured (1990s-2000s), and NoSQL/AI-Vector (2010s-2026).

What operator symbol is used for Selection and Projection in Relational Algebra?

Selection uses the symbol \text{\sigma} (sigma) to take rows matching a condition, and Projection uses the symbol \text{\pi} (pi) to select specific columns.