Interview Q&A

Short answer: And cache usage to monitor the internal database performance.

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: nother non-key attribute. For example: Original Table: Employee_ID Employee_Nam Department Department_Manager 1 Alice HR John 2 Bob IT Sarah ● 1NF: Remove repeating groups and ensure atomic values. 2NF: Remove partial dependencies. In this case, split into separate tables for employees and departments. 3NF: Remove transitive dependencies. The………… Department_Manager is a dependency on Department, not directly on…

Explain a bit more

Employee, so it should be moved to a separate table for Departments.

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: Trade-offs: CA (Consistency and Availability): Systems that prioritize consistency and availability will fail during network partitions. CP (Consistency and Partition Tolerance): Systems that prioritize consistency and partition tolerance may not be available during network issues. AP (Availability and Partition Tolerance): Systems that prioritize availability and partition tolerance may return stale 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: Use ETL (Extract, Transform, Load) tools to clean, transform, and shape the data as required.

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: Set up continuous integration (CI) pipelines to test schema migrations on different environments to ensure smooth deployment.

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: Each tenant gets its own database instance. Pros: Complete isolation, better security, and individual scaling. Cons: Higher overhead for database management and maintenance. Example: SaaS applications often use multi-tenancy to serve multiple clients with the same infrastructure while keeping data separated.

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: A flexible schema design used for scenarios where attributes of entities are dynamic and can vary over time. Example: A Product might have a variety of attributes like size, color, and weight, but some products may not have all attributes, so you store them as key-value pairs.

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: Use pagination techniques (LIMIT, OFFSET, or ROWNUM) to retrieve data in chunks rather than all at once. Avoid fetching unnecessary columns or rows.

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: Track buffer cache hit ratio, transaction log size, locks and deadlocks, and cache usage to monitor the internal database performance.

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: Horizontal scaling involves adding more database servers to distribute the load, typically using replication, sharding, or clustering. Advantages: It can handle much larger datasets and traffic since the workload is distributed across multiple servers. Disadvantages: More complex to implement and maintain, requires specialized techniques like load balancing and data partitioning.

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 is written to both the primary and secondary database simultaneously. Advantages: Ensures data consistency between all replicas. Disadvantages: May incur performance overhead due to the need to wait for replication.

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: A transaction log backup records all the changes made to the database since the last transaction log backup.

Explain a bit more

It allows point-in-time recovery. Advantages: Enables recovery of the database to any specific point in time (assuming all previous logs are available). Disadvantages: Requires continuous backups of transaction logs to maintain full recovery. Example: After a transaction log backup, you can restore to a specific moment in time by replaying the logs up to that point.

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: files. Use access controls, roles, and policies to restrict access to backups.

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: responsibilities. Example (SQL Server): - Create a role CREATE ROLE Manager; - Grant permissions to the role GRANT SELECT, UPDATE ON Employees TO Manager; - Assign the role to a user EXEC sp_addrolemember 'Manager', 'john_doe'; In PostgreSQL or MySQL, you would follow similar steps using GRANT statements and role management commands.

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: Customers: Contains customer details (name, address, etc.). Orders: Contains order details and references customers. Order_Items: A join table between Orders and Products. Products: Product details (name, description, price, etc.). Inventory: Tracks stock levels.

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: on another non-key attribute). Ensure that no non-key attribute is dependent on another non-key attribute. For example: Original Table: Employee_ID Employee_Nam Department Department_Manager 1 Alice HR John 2 Bob IT Sarah ● 1NF: Remove repeating groups and ensure atomic values. 2NF: Remove partial dependencies. In this case, split into separate tables for employees and departments. 3NF: Remove transitive…

Explain a bit more

dependencies. The Department_Manager is a dependency on Department, not directly on Employee, so it should be moved to a separate table for Departments.

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 retrieve data. To resolve this, indexes can be reorganized or rebuilt to improve their performance.

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: likely to improve performance when indexed.

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: PostgreSQL: PostgreSQL doesn’t have a built-in debugger, but you can use RAISE NOTICE for debugging or use third-party tools like pgAdmin for debugging. MySQL: MySQL Workbench provides a simple debugging interface that allows stepping through the stored procedure and monitoring execution. Example (PostgreSQL): RAISE NOTICE 'Debugging variable: %', my_variable;

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: available. If there’s no match, NULL is returned for the missing side. Use case: When you want all records from both tables.

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: Load the transformed data into the target database. For SQL databases, tools like MySQL Workbench or SQL Server Integration Services (SSIS) can be used. For NoSQL, tools like MongoDB Compass or Data Migration Tools can be utilized.

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: A pattern used to improve performance by storing redundant data, reducing the need for complex joins, and speeding up read queries. Used in data warehousing and systems requiring fast reads.

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: For large inserts or updates, break the task into smaller batches to avoid overwhelming the system.

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: Identify unused indexes or fragmented indexes that could slow down query performance.

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: across multiple servers.

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.