Interview Q&A

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.

Short answer: Data is written to the primary database, and changes are replicated to secondary databases after a delay.

Explain a bit more

Advantages: Better performance due to less replication overhead. Disadvantages: Potential for data loss or inconsistency in the event of a failure. Tools for Replication: SQL Server: Use Always On Availability Groups or Transactional Replication. PostgreSQL: Use Streaming Replication or Logical Replication. MySQL: Use MySQL Replication or Group Replication. MongoDB: Use Replica Sets for automatic failover and high availability. Example (PostgreSQL Streaming Replication): # On Master Node wal_level = replica archive_mode = on archive_command = 'cp %p /var/lib/postgresql/archive/%f' # On Standby Node primary_conninfo = 'host=master_ip port=5432 user=replication_user password=replication_password' Database Scaling & 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: Use AWS RDS, Azure SQL, or Google Cloud SQL to automate backups in cloud environments. These platforms allow automatic backup scheduling without manual intervention. Example: AWS RDS automatically performs daily backups and retains backups for a configurable retention period.

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 recovery procedures to verify data integrity. Example (SQL Server): BACKUP DATABASE MyDatabase TO DISK = 'D:\Backups\MyDatabase.bak' WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCert); Restoration

Example code

RESTORE DATABASE MyDatabase FROM DISK = 'D:\Backups\MyDatabase.bak' WITH FILE = 1, NOUNLOAD, STATS = 10; Additional Best Practices: Use incremental backups: Reduce the backup size and time by only backing up data that has changed since the last backup. Backup Retention Policy: Implement a retention policy to ensure old backups are properly archived or deleted. Backup & Recovery

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: CategoryID, CustomerID).

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: with primary keys and referential integrity rather than repeating the 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: fragmentation, and distribution of data to make its decision.

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: n index on the filtering columns can improve 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: of data from disk. In summary, indexes drastically speed up SELECT queries at the cost of increased overhead during INSERT, UPDATE, and DELETE operations.

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: Ensure the integrity and completeness of the data after migration by performing data checks (e.g., record counts, sampling).

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.