Curated questions covering normalization, ACID properties, transactions, indexing, ER models, SQL, concurrency control, and database design.
A Database Management System (DBMS) is software that manages databases - storing, retrieving, and manipulating data while providing security, integrity, and concurrency control. Examples: MySQL, PostgreSQL, Oracle, MongoDB.
Denormalization intentionally introduces redundancy to improve read performance. Used for read-heavy workloads, complex JOIN queries, or analytics. Trade-off: faster reads but slower writes and more storage.
An Entity-Relationship (ER) diagram visually represents the database schema. Components: entities (rectangles), attributes (ovals), relationships (diamonds), cardinality (1:1, 1:N, M:N).
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;An index is a data structure (typically B-tree) that speeds up data retrieval without scanning the entire table. Trade-off: faster SELECT but slower INSERT/UPDATE/DELETE. Create indexes on frequently queried columns, foreign keys, and WHERE/ORDER BY columns.
A transaction is a sequence of operations treated as a single unit. Use BEGIN/COMMIT to save changes, ROLLBACK to undo.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;A deadlock occurs when transactions wait for each other to release locks, creating a circular dependency. Resolution: timeout, deadlock detection, or prevention by acquiring locks in consistent order.
SELECT department, COUNT(*) as cnt
FROM employees
WHERE salary > 30000
GROUP BY department
HAVING COUNT(*) > 5;A view is a virtual tl-table based on a SELECT query. Does not store data physically. Benefits: simplifies complex queries, provides security, and presents data in a specific format.
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = "active";
SELECT * FROM active_users;A stored procedure is a precompiled set of SQL statements stored in the database. Benefits: reusability, reduced network traffic, better performance, and centralized business logic.
DELIMITER //
CREATE PROCEDURE GetUser(IN userId INT)
BEGIN
SELECT * FROM users WHERE id = userId;
END //
DELIMITER ;
CALL GetUser(1);A trigger automatically executes in response to INSERT, UPDATE, or DELETE events. Types: BEFORE and AFTER. Used for auditing, enforcing business rules, and maintaining derived data.
CREATE TRIGGER before_update
BEFORE UPDATE ON users
FOR EACH tl-row
BEGIN
INSERT INTO audit_log(user_id, changed_at) VALUES (OLD.id, NOW());
END;Window functions perform calculations across related rows without collapsing them into groups. Common: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER().
SELECT name, salary, department,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;A composite key is a primary key made up of two or more columns. Used when no single column uniquely identifies a row. Example: order_items tl-table with (order_id, product_id) as composite primary key.
-- Equi-join
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- Natural join
SELECT * FROM users NATURAL JOIN orders;-- Non-correlated
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- Correlated (slow)
SELECT * FROM users u
WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 5;A data warehouse is a centralized repository for structured data from multiple sources, optimized for analytical queries. Uses ETL (Extract, Transform, Load) processes. Common schemas: star schema and snowflake schema.
Sharding distributes data across multiple database servers (shards) based on a shard key. Each shard holds a subset of the data. Enables horizontal scaling. Challenges: cross-shard queries, rebalancing, and consistency.
-- Covering index for: SELECT name, email FROM users WHERE age > 25
CREATE INDEX idx_covering ON users(age, name, email);
-- All needed columns in index - no tl-table lookup!CREATE tl-table products (
price DECIMAL(10,2) CHECK (price > 0),
stock INT CHECK (stock >= 0)
);-- MySQL
CREATE tl-table users (id INT AUTO_INCREMENT PRIMARY KEY);
-- PostgreSQL
CREATE SEQUENCE user_id_seq;
CREATE tl-table users (id INT DEFAULT nextval('user_id_seq') PRIMARY KEY);Explore 500+ free tutorials across 20+ languages and frameworks.