Interview Prep: Database Design

1. What is a database management system (DBMS), and can you name some examples?

A Database Management System (DBMS) serves as an intermediary between users and the database. It facilitates data maintenance, retrieval, and ongoing management, using a structured mechanism to ensure data integrity, security, and efficiency.

Types of DBMS

1. Relational DBMS (RDBMS) — organizes data into tables with predefined structures defined by a schema. SQL is typically used for data operations. Common examples: MySQL, PostgreSQL, Microsoft SQL Server.
2. NoSQL DBMS — evolved as a response to the limitations of RDBMS, designed for unstructured or semi-structured data and horizontal scalability. Examples: MongoDB (document-oriented), Redis (key-value).
3. Cloud-Based DBMS — hosted on cloud platforms, providing flexibility and scalability with minimal maintenance. Examples: Amazon RDS, Google Cloud Bigtable, Azure Cosmos DB.
4. NewSQL DBMS — combines traditional RDBMS benefits with modern scalability needs, designed for big data. Examples: Google Cloud Spanner, NuoDB.
5. Object-Oriented DBMS (OODBMS) — designed for complex, object-based data models, providing persistence for objects. Less popular today; examples include db4o and ObjectStore.
6. In-memory DBMS — maintains data in system memory for rapid access. Examples: Oracle TimesTen, Redis.
7. Multimodel DBMS — handles multiple kinds of databases (key-value, document, graph) in a single system. Example: ArangoDB.
8. Graph DBMS — specialized for interconnected data, optimized for traversals and pathfinding. Example: Neo4j.

2. Explain the ACID properties in the context of databases.

Atomicity — ensures all tasks in a transaction complete, or none do. Databases use transaction logs to manage this; if a task fails, the log lets the system recognize the incomplete state and restore the last known consistent one. Consider a banking transfer: if the debit succeeds but the credit fails, atomicity ensures the debit is rolled back too.

Consistency — requires a transaction to take the database from one consistent state to another, without violating data integrity rules. E.g., after a transfer, the sum of account balances should remain the same.

Isolation — ensures operations within concurrent transactions are invisible to each other until completed, protecting against conflicts and inconsistencies. Different isolation levels (read uncommitted, read committed, repeatable read, serializable) define the extent of isolation.

Durability — guarantees that once a transaction is committed, its changes persist even after a system failure — typically via write-ahead logging and buffer management.

3. What are the differences between SQL and NoSQL databases?

• Data Structure — SQL is table-based; NoSQL can be document, key-value, wide-column, or graph-oriented.
• Schema — SQL requires a schema, and deviations require modifications. NoSQL is ad-hoc or schema-on-read.
• Querying — SQL uses Structured Query Language. NoSQL uses methods specific to its data model.
• Scalability — SQL has historically scaled vertically (more CPU/memory/storage on one node), though horizontal scaling support has grown. NoSQL supports horizontal scaling more readily.
• ACID — SQL databases often guarantee full ACID compliance. NoSQL, especially with eventual consistency models, may trade immediate consistency for performance and availability.

4. Describe a relational database schema.

A Relational Database Schema is a set of rules defining the structure, constraints, and relationships of data tables in an RDBMS.

5. What is database normalization, and why do we use it?

A set of practices ensuring data integrity by minimizing redundancy and dependency within a relational database.

Advantages: data consistency (less redundancy → less risk of inconsistent data), improved maintainability, easier updates (fewer records to change).

Normalization levels (generally six, 0 through BCNF):

• 1NF — unique primary keys; atomic values (no multi-valued attributes per cell).
• 2NF — all of 1NF, plus removal of partial dependencies (non-key columns depend on the entire primary key).
• 3NF — all of 2NF, plus elimination of transitive dependencies (non-key columns don’t depend on other non-key columns).
• BCNF — a stricter 3NF, ensuring each determinant is a candidate key.
• 4NF — deals with multi-valued dependencies.
• 5NF (“Projection-Join Normal Form”) — deals with join dependencies.

Most relational databases aim for 3NF, striking a good balance between performance and data integrity — though the right level depends on your requirements (e.g., reporting databases might stay less normalized to improve query performance).

6. What is denormalization and when would you consider it?

The process of reducing normalization (typically for performance) by introducing redundant data into tables. While normalization ensures data integrity, denormalization focuses on improving query performance by eliminating complex JOIN operations.

7. What is the N+1 query problem and how can you solve it?

Arises when an object graph is retrieved using a query that results in unnecessary database hits. Consider a one-to-many relationship where each “Order” has many “LineItems” — fetching all Orders and then individually fetching each Order’s LineItems leads to multiple query rounds. This corresponds to:

1. Primary query: SELECT * FROM Orders.
2. Secondary query: SELECT * FROM LineItems WHERE order_id = :order_id (executed once per Order).

That’s an N+1 scenario — the second query runs “N” times, once per Order, hence N+1.

Solutions:

1. Use a join query — retrieve related entities in a single query; an ORM can handle this.
2. Leverage lazy loading — some ORMs execute extra queries only when a related object is first accessed, reducing the initial query size, though this can still lead to an N+1 problem if multiple related entities get accessed.
3. Use explicit eager loading — modern ORMs let you explicitly define which related entities to fetch eagerly, so you can still benefit from lazy loading elsewhere while opting into immediate access when needed (e.g., @ManyToOne/@OneToMany in EF/JPA).

8. What are indexes and how do they work in databases?

An index is a data structure that enhances the efficiency and speed of data lookup operations by logically ordering the indexed data.

Index types:

• B-Tree — suited for ranges and equality operations (e.g., WHERE clauses).
• Hash — ideal for exact-match lookups, like primary keys.
• Full-text — designed for text searches, common in search engines.
• Bitmap — efficient for low-cardinality columns (few distinct values, like gender).

Data lookup using indexes: ordered scans (B-Tree, sorted data, good for range queries), exact-match sequencing (Hash and tree-based indexes), range searches (B-Trees).

Best practices:

• Consider index maintenance overhead — indexes speed up reads but can slow down INSERT/UPDATE/DELETE.
• Place more selective columns first in multi-column indexes, followed by less-discriminatory ones.
• Cover frequently queried columns, without going overboard into index bloat.