Interview Q&A

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.

Short answer: Common database security risks include: SQL Injection: Malicious queries that can compromise the database if the input is not properly validated.

Explain a bit more

Unauthorized Access: Users gaining access to sensitive data without permission. Data Breaches: Exposing sensitive data due to insufficient encryption or weak access 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 accessible to unauthorized users.

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: uthorized users based on roles. Each role defines a set of permissions (what actions can be performed on which database objects). Steps to implement RBAC:

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: Role-Based Access Control (RBAC) is a method for restricting system access to authorized users based on roles. Each role defines a set of permissions (what actions can be performed on which database objects). Steps to implement RBAC:

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: And SQL statements in precompiled code, making it harder for attackers to inject malicious code.

Explain a bit more

Roles in database security: Input Validation: Stored procedures allow you to validate user inputs at the database level, preventing malicious input from being executed. UPDATE employees SET salary = new_salary WHERE id = emp_id; END IF; END; And SQL statements in precompiled code, making it harder for attackers to inject malicious code. Roles in database security: Input Validation: Stored procedures allow you to validate user inputs at the database level, preventing malicious input from being executed. UPDATE employees SET salary = new_salary WHERE id = emp_id; END IF; END;

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: Stored Procedures can help improve database security by encapsulating business logic and SQL statements in precompiled code, making it harder for attackers to inject malicious code.

Explain a bit more

Roles in database security: Input Validation: Stored procedures allow you to validate user inputs at the database level, preventing malicious input from being executed. Preventing Direct Access: By using stored procedures, users can be given permissions to execute specific procedures rather than direct access to the underlying tables. Encapsulation of Business Logic: The logic within stored procedures is not visible to the end-user, reducing the attack surface. Audit Logging: Stored procedures can include logic to log user activity for auditing and compliance purposes. Example: Instead of allowing users to execute arbitrary INSERT or UPDATE statements, you can give them permission to execute a specific stored procedure that does the necessary validation and modification of data. CREATE PROCEDURE update_salary(IN emp_id INT, IN new_salary DECIMAL) BEGIN IF new_salary > 0 THEN

Example code

UPDATE employees SET salary = new_salary WHERE id = emp_id; END IF; END;

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: LTER DATABASE MyDatabase SET ENCRYPTION ON; Column-Level Encryption: For encrypting specific columns. CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate'; CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCertificate; OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE MyCertificate; PostgreSQL: pgcrypto extension provides functions for encrypting data.

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: Encryption is essential for protecting sensitive data both at rest (on disk) and in transit (during communication).

Explain a bit more

SQL Server: Transparent Data Encryption (TDE): Encrypts data at rest without requiring application changes. CREATE DATABASE ENCRYPTION KEY; ALTER DATABASE MyDatabase SET ENCRYPTION ON; Column-Level Encryption: For encrypting specific columns. CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate'; CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCertificate; OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY CERTIFICATE MyCertificate; PostgreSQL: pgcrypto extension provides functions for encrypting data. SELECT pgp_sym_encrypt('Sensitive Data', 'encryption_key'); MySQL: Encryption Functions: MySQL provides functions like AES_ENCRYPT and AES_DECRYPT. SELECT AES_ENCRYPT('Sensitive Data', 'encryption_key'); SELECT AES_DECRYPT(encrypted_data, 'encryption_key'); For all databases, consider using SSL/TLS for encrypting data in transit.

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: Backup and restore security involves ensuring that backup files are stored securely and that access to backup data is tightly controlled. Steps for Secure Backup:

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: There are three primary types of database backups:

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.