InterviewSkill
Database fundamentals for transactions, indexing, modeling, and consistency discussions.
ACID means Atomicity, Consistency, Isolation, and Durability.
ACID makes transactions reliable by ensuring all-or-nothing execution, valid state transitions, safe concurrency, and committed data persistence.
Normalization organizes data to reduce redundancy and improve integrity.
It splits data into related tables and uses keys to avoid repeated facts, update anomalies, and inconsistent records.
An index helps a database find rows faster.
Indexes reduce read cost but add storage and write overhead because index structures must be maintained when data changes.
It controls how much concurrent transactions can see or affect each other.
Isolation levels trade consistency for performance and can prevent anomalies such as dirty reads, non-repeatable reads, and phantom reads.
A primary key uniquely identifies each row in a table.
It should be unique and non-null, and it provides a stable reference for relationships through foreign keys.
They keep keys sorted, making equality lookups, range scans, and ordered reads efficient.
B-trees have balanced height, so searches require relatively few page reads. They are useful for WHERE filters, joins, ORDER BY, and range predicates.
A deadlock happens when transactions wait on each other in a cycle, so none can continue.
Databases usually detect deadlocks and abort one transaction. Consistent lock ordering, shorter transactions, and retry logic reduce deadlock impact.
Check scan types, join order, estimated versus actual rows, sort steps, and index usage.
Execution plans show how the database intends to run a query. Large sequential scans, bad estimates, missing indexes, or expensive sorts often explain latency.
SCD Type 1 overwrites the old value (no history). SCD Type 2 adds a new row with effective/expiry dates (full history).
SCD Type 1 overwrites the old value (no history). SCD Type 2 adds a new row with effective/expiry dates (full history). Dimension tables like product catalog used Type 2 to track price changes over time; operational tables used Type 1. Implemented via merge/upsert logic in PySpark processing CDC data from Amazon DMS.