Interview Q&A

Short answer: LTER INDEX idx_name ON table_name REORGANIZE; PostgreSQL: PostgreSQL does not have an explicit REORGANIZE command, but you can run VACUUM to clean up the database and reduce fragmentation: VACUUM INDEX idx_name; MySQL: OPTIMIZE TABLE table_name; Rebuilding an Index: A more intensive operation where the index is completely dropped nd recreated. This can reduce fragmentation to nearly zero.

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Reorganizing an Index: A lighter operation that compacts the index and defragments it.

Explain a bit more

It is used when fragmentation is low (less than 30%). SQL Server: ALTER INDEX idx_name ON table_name REORGANIZE; PostgreSQL: PostgreSQL does not have an explicit REORGANIZE command, but you can run VACUUM to clean up the database and reduce fragmentation: VACUUM INDEX idx_name; MySQL: OPTIMIZE TABLE table_name; Rebuilding an Index: A more intensive operation where the index is completely dropped and recreated. This can reduce fragmentation to nearly zero. SQL Server: ALTER INDEX idx_name ON table_name REBUILD; PostgreSQL: REINDEX INDEX idx_name; MySQL: ALTER TABLE table_name DROP INDEX idx_name, ADD INDEX idx_name (column_name);

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: For large datasets, query optimization can be crucial to ensure performance is not impacted. Here are several tips:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: transaction are invisible to others until the transaction is committed. Durability: Once a transaction is committed, the changes are permanent, even in the case of a system crash.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: The ACID properties are critical to ensuring the reliability of transactions in a database: Atomicity: All operations within a transaction are executed completely or not at all.

Explain a bit more

Consistency: A transaction takes the database from one valid state to another, ensuring that all rules (constraints, triggers, etc.) are respected. Isolation: Transactions are isolated from one another, meaning intermediate steps of a transaction are invisible to others until the transaction is committed. Durability: Once a transaction is committed, the changes are permanent, even in the case of a system crash.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: ffects concurrency in several ways: Locking: Transactions may lock rows or tables to prevent conflicting changes, leading to possible delays for other transactions. Deadlocks: When two or more transactions are waiting for each other to release locks, causing a cycle of dependency. This can be automatically detected and resolved by the DBMS.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Transactions impact concurrent operations by introducing locking mechanisms to ensure that multiple transactions don't interfere with each other and cause inconsistent data.

Explain a bit more

This affects concurrency in several ways: Locking: Transactions may lock rows or tables to prevent conflicting changes, leading to possible delays for other transactions. Deadlocks: When two or more transactions are waiting for each other to release locks, causing a cycle of dependency. This can be automatically detected and resolved by the DBMS.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: pplication to reattempt the transaction after a short delay. Optimize Transactions: Keep transactions short and ensure that they acquire locks in the same order to reduce the likelihood of deadlocks.

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Deadlocks occur when two or more transactions are waiting for each other to release locks, causing a cycle of dependencies.

Explain a bit more

To handle deadlocks: Deadlock Detection: DBMS systems (e.g., SQL Server) can automatically detect deadlocks and terminate one of the transactions to break the deadlock. Retry Logic: If a deadlock is detected, you can implement a retry mechanism in your application to reattempt the transaction after a short delay. Optimize Transactions: Keep transactions short and ensure that they acquire locks in the same order to reduce the likelihood of deadlocks.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: In stored procedures, you control transaction behavior using the following: BEGIN TRANSACTION: Explicitly starts a transaction in the stored procedure. COMMIT: Ends the transaction and commits all changes made during the transaction. ROLLBACK: Undoes all changes made during the transaction if something goes wrong. SAVEPOINT: Sets a point in the transaction to which you can roll back.

Example code

BEGIN TRANSACTION; - SQL operations here IF (some_condition) BEGIN COMMIT; -- Commit if condition is true END ELSE BEGIN ROLLBACK; -- Rollback if condition is false END Normalization & Database Design

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: ddresses a different kind of redundancy or dependency. 1NF (First Normal Form): A table is in 1NF if it contains only atomic (indivisible) values and each record has a unique identifier (Primary Key).

Real-world example (ShopNest)

Product and Category are separate tables (normalized). The order line stores product id + price snapshot—not a giant duplicated product blob.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Normal forms are guidelines used to organize a relational database schema.

Explain a bit more

Each form addresses a different kind of redundancy or dependency.

Real-world example (ShopNest)

Product and Category are separate tables (normalized). The order line stores product id + price snapshot—not a giant duplicated product blob.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: To normalize a database to 3NF:

Real-world example (ShopNest)

Product and Category are separate tables (normalized). The order line stores product id + price snapshot—not a giant duplicated product blob.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Data redundancy occurs when the same piece of data is stored in multiple places, which can lead to inconsistencies and wasted storage. To avoid redundancy:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: pplication? Designing a database schema for a large e-commerce application involves careful consideration of data requirements, scalability, and normalization. Key entities and relationships to consider:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Designing a database schema for a large e-commerce application involves careful consideration of data requirements, scalability, and normalization. Key entities and relationships to consider:

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Advantages: Scalability: MongoDB scales horizontally through sharding, which can handle large datasets across distributed clusters.

Explain a bit more

Flexibility: Schema-less design allows for dynamic data models and easier iteration during development. Performance: It can perform high-throughput reads and writes for large datasets. High Availability: Through replica sets, MongoDB ensures data availability and fault tolerance. Disadvantages: Consistency: By default, MongoDB uses eventual consistency, which may not be suitable for all applications (though it supports ACID transactions in some cases). Complexity in Transactions: While MongoDB now supports multi-document transactions, working with them is more complex compared to SQL. Data Duplication: The flexibility can sometimes lead to data duplication and inconsistency if not properly managed.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Database: A container for collections. MongoDB allows you to have multiple databases within the same instance. Collection: A group of MongoDB documents. Collections are similar to tables in SQL but don’t have a fixed schema.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Create: db.collection.insertOne({ name: "Alice", age: 25 }); db.collection.insertMany([{ name: "Bob", age: 30 }, { name: "Charlie", age: 35 }]); Read: db.collection.find({ age: { $gt: 30 } }); db.collection.findOne({ name: "Alice" }); Update: db.collection.updateOne({ name: "Alice" }, { $set: { age: 26 } }); db.collection.updateMany({ age: { $lt: 30 } }, { $set: { status: "young" } }); Delete:…

Explain a bit more

db.collection.deleteOne({ name: "Alice" }); db.collection.deleteMany({ age: { $lt: 30 } });

Example code

Create: db.collection.insertOne({ name: "Alice", age: 25 }); db.collection.insertMany([{ name: "Bob", age: 30 }, { name: "Charlie", age: 35 }]); Read: db.collection.find({ age: { $gt: 30 } }); db.collection.findOne({ name: "Alice" }); Update: db.collection.updateOne({ name: "Alice" }, { $set: { age: 26 } }); db.collection.updateMany({ age: { $lt: 30 } }, { $set: { status: "young" } }); Delete: db.collection.deleteOne({ name: "Alice" }); db.collection.deleteMany({ age: { $lt: 30 } });

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: You can use the updateOne(), updateMany(), or replaceOne() methods to update data.

Example code

db.collection.updateOne({ name: "Alice" }, { $set: { age: 27 } }); $set updates the value of a field. $inc increments a field's value.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: MongoDB provides multi-document transactions starting with version 4.0. This allows you to execute multiple operations in a transaction, ensuring ACID properties (Atomicity, Consistency, Isolation, Durability) across multiple documents and collections.

Example code

const session = client.startSession(); try { session.startTransaction(); db.collection1.updateOne({ _id: 1 }, { $set: { status: "completed" } }, { session }); db.collection2.updateOne({ _id: 2 }, { $set: { status: "shipped" } }, { session }); session.commitTransaction(); } catch (error) { session.abortTransaction(); } finally { session.endSession(); }

Real-world example (ShopNest)

Checkout wraps stock decrement + order insert in a transaction so you never sell stock you do not have.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: In MongoDB, relationships can be handled in two ways: Embedded documents: Storing related data within a single document (best for one-to-many relationships).

Example code

A blog post with comments as an embedded array. Referenced documents: Storing related data in separate collections and using references (best for many-to-many or large datasets). Example: A customer document may reference an order document by storing the order’s _id in the customer document.

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Data validation in MongoDB can be implemented using JSON Schema or custom validation rules to ensure data consistency. Example (using JSON Schema): db.createCollection("products", { validator: { $jsonSchema: { bsonType: "object", required: ["product_name", "price"], properties: { product_name: { bsonType: "string" }, price: { bsonType: "double", minimum: 0 } }

Example code

}
} });

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: Indexing: Create appropriate indexes to optimize query performance. Sharding: Distribute large datasets across multiple servers. Caching: Cache frequently accessed data. Aggregation optimization: Use $match early in the aggregation pipeline. Avoid joins: MongoDB performs best when data is denormalized or embedded. Database Security

Real-world example (ShopNest)

ShopNest adds an index on Orders(CustomerId, CreatedAt) because “my recent orders” is queried constantly.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.

Short answer: ccess controls. Privilege Escalation: Attackers gaining elevated permissions or access levels. Malicious Insiders: Employees or authorized users intentionally or unintentionally leaking or modifying data. Denial of Service (DoS): Attackers overwhelming the database with requests, making it unavailable to legitimate users. Backup Exposure:… Unencrypted or……… improperly secured backup files that are ccessible to…

Real-world example (ShopNest)

ShopNest’s SQL Server database stores customers, products, and orders. Good indexes and clear foreign keys keep checkout queries fast and safe.

Say this in the interview

1. Define — one clear sentence (the short answer above).
2. Example — relate it to a project like ShopNest or your real work.
3. Trade-off — when you would not use it.