TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 14

Database Concepts: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

KEYS & RELATIONSHIPS

  1. Q1In plain English, what is a primary key and what one rule must its values always satisfy?
  2. Q2What is a foreign key, and what does it enforce between two tables?
  3. Q3A teammate asks how a PRIMARY KEY differs from a UNIQUE constraint. Give the key difference.
  4. Q4What is a composite (multi-column) primary key? Give a realistic example.
  5. Q5Define a natural key and a surrogate key, with one example of each.
  6. Q6Why do most tables use an auto-generated id as the primary key instead of a real-world value?
  7. Q7Can a single table have more than one foreign key? Give an example.
  8. Q8What is a candidate key, and how does it relate to the primary key?
  9. Q9Explain a one-to-many relationship using a customer-and-orders example.
  10. Q10Explain a many-to-many relationship using students and courses.
  11. Q11Explain a one-to-one relationship and give a sensible example.
  12. Q12What does "referential integrity" mean, in one sentence?
  13. Q13Conceptually, what should happen if you try to delete a customer who still has orders?
  14. Q14Give a one-line description of ON DELETE CASCADE versus ON DELETE RESTRICT.
  15. Q15Why can a primary key column never be NULL?
  16. Q16Can a foreign key value be NULL? If so, what would that NULL mean in business terms?
  17. Q17In a foreign-key relationship, what do we mean by the "parent" table versus the "child" table?
  18. Q18How do you physically model a many-to-many relationship in a relational database?
  19. Q19Email is unique per customer, yet customer_id is the primary key. Explain why that choice makes sense.
  20. Q20What is a self-referencing foreign key? Give an example (hint: employees and managers).
  21. Q21What is a lookup (dimension) table? Name one from RetailMart.
  22. Q22Why do surrogate keys stay stable even when a natural value like email or phone changes?
  23. Q23In one sentence, what is the difference between a key and an index?
  24. Q24What is an "orphan" row, and which mechanism prevents it?
  25. Q25Why do we split data across several related tables instead of one very wide table?

NORMALIZATION BASICS

  1. Q26In plain English, what does "normalizing" a database mean?
  2. Q27Name two concrete problems normalization is designed to prevent.
  3. Q28What is data redundancy, and why is storing the same fact in many places risky?
  4. Q29Define an "update anomaly" with a short example.
  5. Q30Define an "insertion anomaly" with a short example.
  6. Q31Define a "deletion anomaly" with a short example.
  7. Q32State what First Normal Form (1NF) requires, in one sentence.
  8. Q33Give an example of a column value that violates 1NF.
  9. Q34What does it mean for a value to be "atomic"?
  10. Q35State what Second Normal Form (2NF) requires, building on 1NF.
  11. Q36In intuitive terms, what is a "partial dependency"?
  12. Q37State what Third Normal Form (3NF) requires, in one sentence.
  13. Q38In intuitive terms, what is a "transitive dependency"?
  14. Q39Explain the mnemonic "the key, the whole key, and nothing but the key."
  15. Q40What is a functional dependency, written A -> B, in plain English?
  16. Q41True or false: a table in 3NF is automatically in 2NF and 1NF. Explain.
  17. Q42Why is it risky to store city, state, pincode AND a derived "region" label all in one row?
  18. Q43Give an everyday example where the same address copied into 5 places caused an inconsistency.
  19. Q44What does "denormalization" mean?
  20. Q45Name one legitimate reason to deliberately denormalize.
  21. Q46Why are reporting and analytics tables often denormalized on purpose?
  22. Q47Is more normalization always better? Answer in one line.
  23. Q48What is a "repeating group," and which normal form forbids it?
  24. Q49Why does normalizing a design usually result in MORE tables?
  25. Q50How does normalization explain why analysts write so many JOINs?

TRANSACTIONS & ACID

  1. Q51What is a database transaction, in plain English?
  2. Q52What does COMMIT do?
  3. Q53What does ROLLBACK do?
  4. Q54Spell out what each letter of ACID stands for.
  5. Q55Explain Atomicity using the classic bank-transfer example.
  6. Q56Explain Consistency in one sentence.
  7. Q57Explain Isolation in one sentence.
  8. Q58Explain Durability in one sentence.
  9. Q59Why is "move money from account A to account B" the textbook example of atomicity?
  10. Q60If the power fails halfway through a transaction, which ACID property protects your data?
  11. Q61Which ACID property ensures two simultaneous users don't corrupt each other's work?
  12. Q62What is an "all-or-nothing" operation?
  13. Q63Give a RetailMart example where two writes should happen in one transaction.
  14. Q64What does BEGIN (or START TRANSACTION) do?
  15. Q65After a successful COMMIT, can you still ROLLBACK that work? One line.
  16. Q66Name one reason a transaction might be rolled back automatically.
  17. Q67What is a "partial update," and why is it dangerous?
  18. Q68What is the difference between auto-commit mode and an explicit transaction?
  19. Q69In plain English, what is a constraint, and how does it support the "C" in ACID?
  20. Q70How does a NOT NULL constraint protect data quality?
  21. Q71How does a CHECK constraint protect data quality? Give an example.
  22. Q72How does a UNIQUE constraint protect data quality?
  23. Q73Why are transactions especially important for an e-commerce checkout?
  24. Q74Once a payment app says "payment successful," what does Durability promise the user?
  25. Q75In one sentence, why do analysts mostly read (SELECT) and rarely manage transactions themselves?

APPLIED TO RETAILMART V3

  1. Q76What is the primary key of customers.customers?
  2. Q77A customer can have several addresses. Which table holds them, and which key connects them?
  3. Q78customers.customers has no city column. Which table holds city, and how do you connect the two?
  4. Q79sales.order_items refers to two parent tables. Name both and the keys involved.
  5. Q80sales.order_items stores prod_id. What does that column point to?
  6. Q81sales.returns has no cust_id. Describe the path you'd follow to find who returned an item.
  7. Q82What kind of relationship exists between sales.orders and sales.order_items?
  8. Q83core.dim_region is a lookup table. What does stores.stores.region_id point to?
  9. Q84Why does RetailMart store region_id in stores instead of the text "North" directly?
  10. Q85customers.tier holds text like "Gold"/"Silver". Is that a normalization smell? Briefly discuss.
  11. Q86Is a customer's full name stored as a column, or derived? If derived, from what?
  12. Q87Which key connects loyalty.members back to a customer?
  13. Q88State the primary key of products.products and how order_items links to it.
  14. Q89Give one one-to-many relationship in RetailMart and name the keys on both sides.
  15. Q90Orders and products are effectively many-to-many. Which table resolves that relationship?
  16. Q91Why does RetailMart separate brand and category into core.dim_brand and core.dim_category?
  17. Q92support.tickets stores customer_id. What relationship does that represent?
  18. Q93In stores.employees, which column links an employee to a department, and what does it reference?
  19. Q94Is order_date stored once on the order, or repeated on each line item? Which is the correct design?
  20. Q95orders carries a net_total (a derived sum of its items). Is that denormalization? When is it safe?
  21. Q96Name two RetailMart tables that look well-normalized (3NF) and say why.
  22. Q97Name one RetailMart field that looks denormalized, and explain the tradeoff behind it.
  23. Q98Sketch how you'd add a many-to-many "which products appear in which promotions" relationship.
  24. Q99Why is the surrogate customer_id a better primary key than using email in customers.customers?
  25. Q100Pick any RetailMart table: state its primary key, one foreign key, and the real-world entity it models.

Combined ideas, multi-step thinking

NORMALIZING SAMPLE TABLES

  1. Q1A junior built Orders(order_id, customer_name, item1, item2, item3). Which normal form does it break, and why?
  2. Q2Rewrite the table in Q1 (sketch) into a design that satisfies 1NF.
  3. Q3Sales(order_id, product_id, product_name, qty) repeats product_name on every line. Which NF is violated and which dependency causes it?
  4. Q4Fix the Sales table in Q3 by decomposing it - name the resulting tables and their keys.
  5. Q5Enrollment(student_id, course_id, student_name, grade) has PK (student_id, course_id). student_name depends only on student_id. Name the violation.
  6. Q6Decompose the Enrollment table in Q5 to reach 2NF.
  7. Q7Employee(emp_id, dept_id, dept_name): dept_name depends on dept_id, which depends on emp_id. Name the dependency type and the NF violated.
  8. Q8Decompose the Employee table in Q7 to reach 3NF.
  9. Q9A customers sheet stores "9876543210, 9123456780" in a single phone cell. Which NF does it break, and how do you fix it?
  10. Q10A table stores order_id plus a JSON blob of all items in one column. Is that 1NF? Discuss the tradeoff for analytics.
  11. Q11Invoice(invoice_id, customer_id, customer_gstin, line_no, item, amount) with PK (invoice_id, line_no): identify the partial dependency.
  12. Q12Why does a table whose primary key is a single column automatically avoid 2NF (partial-dependency) problems?
  13. Q13A reporting table repeats region_name on every row for speed. Which NF does it break, and why might that be acceptable here?
  14. Q14Spot the transitive dependency in Book(book_id, publisher_id, publisher_city).
  15. Q15Product(prod_id, category_id, category_name, gst_rate) where gst_rate depends on category. Name the NF violated and the fix.
  16. Q16A column "address" holds "12 MG Road, Pune, 411001, MH". Is that atomic? How would you normalize it, and what's the tradeoff?
  17. Q17Two rows describe the same customer with "Bengaluru" vs "Bangalore". Which anomaly does this risk, and how does normalization help?
  18. Q18A junior says "one big table means I never JOIN." Give two concrete risks of that approach.
  19. Q19Orders(order_id, coupon_code, coupon_discount_pct) where discount depends on the coupon. Name the dependency and the NF it breaks.
  20. Q20Convert a many-to-many "students <-> courses" design into three tables; name every key.
  21. Q21Shipment(shipment_id, courier_name, courier_phone): spot the transitive dependency and propose the fix.
  22. Q22Why can a table that is only in 1NF still suffer update anomalies? Give an example.
  23. Q23Sketch a table that is in 2NF but NOT 3NF, then show its 3NF version.
  24. Q24A teammate normalized so aggressively that a simple report needs 7 joins. What tradeoff did they hit?
  25. Q25Given a denormalized "wide" sales export, list the ordered steps you'd take to bring it to 3NF.

FUNCTIONAL DEPENDENCIES & DECOMPOSITION

  1. Q26Write the functional dependency that captures "each order belongs to exactly one customer."
  2. Q27List the functional dependencies in Orders(order_id, customer_id, order_date).
  3. Q28What is the difference between a "fully functional dependency" and a partial one?
  4. Q29Given (A,B) -> C and A -> D, which dependency is partial, and why?
  5. Q30What is a "determinant"? Identify it in product_id -> product_name.
  6. Q31If A -> B and B -> C, what can you infer, and what is that inference rule called?
  7. Q32Why does a transitive dependency A -> B -> C violate 3NF?
  8. Q33Decompose R(emp_id, project_id, hours, emp_name), given emp_name depends only on emp_id.
  9. Q34What is a "lossless" decomposition, in one sentence?
  10. Q35Why is it a problem if a decomposition loses information when you re-join the pieces?
  11. Q36Student(roll_no, email, dept, dept_hod) with email unique. List the candidate keys.
  12. Q37If every non-key attribute depends on the whole key and nothing else, which normal form is guaranteed?
  13. Q38State BCNF in one sentence and explain how it is stricter than 3NF.
  14. Q39Give a short example of a table that is in 3NF but not in BCNF.
  15. Q40Why should analysts understand functional dependencies even if they never design schemas?
  16. Q41A table stores both "age" and "date_of_birth". What dependency/quality problem is that, and what's the fix?
  17. Q42Identify the determinant for the junction table order_items(order_id, prod_id, qty).
  18. Q43What does it mean to say the primary key "functionally determines" every other column?
  19. Q44Course(course_id, instructor, instructor_email): which dependency makes this not 3NF?
  20. Q45Describe, step by step, how you'd check on paper whether a table is in 2NF.
  21. Q46Describe, step by step, how you'd check on paper whether a table is in 3NF.
  22. Q47Why does adding a surrogate key sometimes hide a real dependency problem instead of fixing it?
  23. Q48Given (store_id, product_id) -> stock and store_id -> store_region, classify each dependency.
  24. Q49Decompose the table in Q48 to remove the partial dependency.
  25. Q50Restate the BCNF rule "every determinant must be a candidate key" in plain words.

TRANSACTIONS IN PRACTICE

  1. Q51Sketch a transaction (BEGIN ... COMMIT) for "insert one order, then insert its three line items."
  2. Q52In the Q51 example, what should happen if the third item insert fails?
  3. Q53A CHECK constraint requires quantity > 0. A bulk insert includes one row with quantity = 0. What happens to that statement?
  4. Q54Two cashiers sell the last unit of a product at the same instant. Which ACID property is being tested?
  5. Q55Explain a "dirty read" in plain English with a short scenario.
  6. Q56Explain a "non-repeatable read" with a short scenario.
  7. Q57Explain a "phantom read" with a short scenario.
  8. Q58A nightly job updates 1,000,000 rows in one transaction and crashes at row 500,000. What state is the table left in?
  9. Q59Why might you split that 1M-row update into smaller batches instead of one giant transaction?
  10. Q60What is a deadlock, in one sentence, and what typically resolves it?
  11. Q61A FOREIGN KEY blocks inserting an order_item for a non-existent order. Which ACID property does that support?
  12. Q62The CFO asks "can a half-finished refund leave the books unbalanced?" Answer using Atomicity.
  13. Q63Why is it safe to ROLLBACK after an error, but impossible to ROLLBACK after COMMIT?
  14. Q64What does a SAVEPOINT let you do inside a transaction?
  15. Q65Why do long-running transactions hurt concurrency for other users?
  16. Q66When a constraint violation occurs mid-transaction, what is the typical outcome for the statement versus the whole transaction?
  17. Q67Explain how a UNIQUE constraint stops two customers registering the same email even under concurrent inserts.
  18. Q68Why must "place order" be atomic across inventory, order, and payment writes?
  19. Q69A developer disables constraints "for speed" during a load. Which ACID guarantee did they weaken, and what's the risk?
  20. Q70Describe optimistic versus pessimistic concurrency control at a high level.
  21. Q71Why are most SELECT-only workloads safe to run without an explicit transaction?
  22. Q72Name the four standard isolation levels from weakest to strongest.
  23. Q73Which isolation level prevents dirty reads but still allows non-repeatable reads?
  24. Q74Which isolation level prevents all three classic read anomalies?
  25. Q75Why is the strongest isolation level not used by default for every workload?

RETAILMART SCHEMA ANALYSIS

  1. Q76The VP of Sales wants region on every order row. Explain how RetailMart derives region today (stores.region_id -> core.dim_region) and the tradeoff of denormalizing it.
  2. Q77orders.net_total is a stored sum of its order_items. Argue when recomputing from items is safer than trusting the stored total.
  3. Q78A junior wants to add customer_city to customers.customers "to avoid a join." Critique this against 3NF.
  4. Q79customers.tier is stored as text. Propose a normalized alternative and give one pro and one con.
  5. Q80Why is sales.order_items a textbook junction table? Name its likely composite key.
  6. Q81The DBA asks you to state the referential-integrity rule that must hold between order_items and orders.
  7. Q82sales.returns links to an order rather than directly to a customer. Explain the design reasoning.
  8. Q83core.dim_date exists as a separate dimension. What normalization and analytics benefits does a date dimension provide?
  9. Q84What transitive-dependency risk appears if products stored brand_name AND category_name as text instead of brand_id?
  10. Q85Explain why storing first_name and last_name separately (not one full_name) is the more flexible, 1NF-friendly choice.
  11. Q86loyalty.members has no member_id, only customer_id. What does that imply about the members-to-customer relationship?
  12. Q87The CHRO wants a manager hierarchy, but stores.employees has no manager_id. Sketch how you'd model it in a normalized way.
  13. Q88supply_chain.inventory_snapshots uses a composite key with no single snapshot_id. Explain why a composite key fits here.
  14. Q89Why does keeping payment modes in finance.payment_modes (mode_id) instead of free text on orders reduce anomalies?
  15. Q90A report needs brand AND category names for each product. Trace the normalized join path products -> dim_brand -> dim_category.
  16. Q91Argue for and against adding a denormalized monthly_sales summary table for dashboards.
  17. Q92The Compliance Officer asks whether deleting a customer should delete their orders. Discuss CASCADE vs RESTRICT for RetailMart.
  18. Q93Which RetailMart tables are "fact" tables and which are "dimension" tables? Give two of each.
  19. Q94Why is a surrogate prod_id a better key than using the product name in products.products?
  20. Q95Identify one place in RetailMart where a genuine 1:1 relationship could exist, and how you'd model it.
  21. Q96Explain how foreign keys across sales, customers, and products keep RetailMart consistent.
  22. Q97A stakeholder wants reviews linked to both a product and a customer. Which columns on customers.reviews support that?
  23. Q98Why might the analytics layer (views / materialized views) deliberately denormalize RetailMart for query speed?
  24. Q99Give an example of an update anomaly that the customers/addresses split prevents.
  25. Q100Audit any three RetailMart tables and label each as 3NF or intentionally denormalized, with a one-line reason.

Interview grade, edge cases

NORMALIZATION EDGE CASES & BCNF

  1. Q1Give a table that is in 3NF but not BCNF, and name the exact dependency that breaks BCNF.
  2. Q2Explain why decomposing to BCNF can force you to lose a functional dependency (the dependency-preservation tradeoff).
  3. Q3When is it defensible to stop at 3NF rather than push to BCNF? Justify with a tradeoff.
  4. Q4What is a multivalued dependency, and which normal form (4NF) addresses it?
  5. Q5Give an example where 4NF matters - a table mixing two independent multivalued facts.
  6. Q6Define "denormalization for read performance" and state the integrity cost you accept in return.
  7. Q7A star-schema fact table is deliberately not in 3NF. Explain why dimensional modeling tolerates that.
  8. Q8Contrast the design goals of OLTP (normalized) versus OLAP (dimensional/denormalized) systems.
  9. Q9Why can over-normalization specifically hurt analytical query performance?
  10. Q10A surrogate key is added to a table that already had a solid natural key. Name a real downside.
  11. Q11Explain how a slowly changing dimension complicates the idea of a single normalized "truth."
  12. Q12Storing a computed column (line_total = qty * price) technically breaks 3NF. When is it worth doing anyway?
  13. Q13Describe a case where two columns are mutually dependent (A -> B and B -> A) and what that implies for candidate keys.
  14. Q14Explain why NULLs complicate reasoning about functional dependencies.
  15. Q15A table looks 3NF "by inspection," but a real business rule creates a dependency the schema fails to capture. How can that happen?
  16. Q16Tie the expectation "an analyst should read an ER diagram" back to functional dependencies and keys.
  17. Q17Distinguish logical normalization from physical storage layout, and why both matter.
  18. Q18When does flattening a dimension's attributes INTO a fact table make sense?
  19. Q19A team keeps a normalized core plus a denormalized reporting layer. Name two risks of keeping them in sync.
  20. Q20Why is "the same data in two places" acceptable in a materialized view but not in an OLTP base table?
  21. Q21Give a case where a junction table needs its OWN attributes (e.g., enrollment date), and why that's correct.
  22. Q22Explain how one badly chosen composite key can create both a 2NF and a BCNF problem at once.
  23. Q23Explain the classic tradeoff: normalization saves storage but can raise JOIN/CPU cost.
  24. Q24A stakeholder demands one flat "order + customer + product" table. Defend delivering it as a VIEW, not a base table.
  25. Q25Summarize, in three bullets, a decision framework for "normalize vs denormalize" on an analytics team.

ISOLATION, CONCURRENCY & ANOMALIES

  1. Q26Map each read anomaly (dirty, non-repeatable, phantom) to the lowest isolation level that prevents it.
  2. Q27Explain READ COMMITTED and exactly which anomaly it still permits.
  3. Q28Explain REPEATABLE READ and which anomaly the SQL standard says it can still permit.
  4. Q29Explain SERIALIZABLE and the guarantee it provides.
  5. Q30PostgreSQL defaults to READ COMMITTED. What does that mean for two analysts running long reports concurrently?
  6. Q31What is a "write skew" anomaly, and which isolation level prevents it?
  7. Q32Explain MVCC at a high level and why, in Postgres, readers don't block writers.
  8. Q33Why can a long analytical SELECT in Postgres see a consistent snapshot even while writes are happening?
  9. Q34Define a "lost update," and describe how isolation or explicit locking prevents it.
  10. Q35Two transactions each read a balance then write balance - 100. Walk through the lost-update scenario and a fix.
  11. Q36What is SELECT ... FOR UPDATE used for, and when would an analyst-adjacent job need it?
  12. Q37Give a concrete two-transaction deadlock example and explain how the database resolves it.
  13. Q38Why does stronger isolation generally reduce concurrency and throughput?
  14. Q39Distinguish a shared lock from an exclusive lock.
  15. Q40Why might an analyst's huge, long-running report transaction interfere with maintenance or cause table bloat in Postgres?
  16. Q41Explain why PostgreSQL's REPEATABLE READ (snapshot isolation) prevents phantom reads in practice.
  17. Q42What is the practical risk of running every transaction at SERIALIZABLE in a high-throughput OLTP system?
  18. Q43How does a UNIQUE constraint behave when two concurrent transactions insert the same value?
  19. Q44Why is "retry on serialization failure" a normal application pattern under SERIALIZABLE?
  20. Q45Give a scenario where allowing dirty reads would feed a finance dashboard a wrong number.
  21. Q46Why are read-only replicas a common way to shield analysts from OLTP contention?
  22. Q47Explain, without deep internals, how durability is implemented via a write-ahead log.
  23. Q48What does flushing/fsync to disk have to do with the D in ACID?
  24. Q49Why can a COMMIT be acknowledged to the client only after the log is durably written?
  25. Q50Summarize the tradeoff triangle: isolation strength vs concurrency vs developer complexity.

SCHEMA DESIGN PROBLEMS

  1. Q51Sketch normalized DDL for "customers place orders containing many products." Name keys and foreign keys.
  2. Q52Design "products belong to many promotions, with a promotion-specific discount." Where does the discount column live?
  3. Q53Design "employees report to managers" (self-referencing). Show the foreign key.
  4. Q54Design "a store stocks many products, each with a per-store quantity." What is the primary key of the stock table?
  5. Q55Model "a customer has exactly one loyalty membership." Is it 1:1 or 1:M? Justify and sketch.
  6. Q56Model "an order uses at most one coupon; a coupon is used by many orders." Which side holds the FK?
  7. Q57Design a date dimension and explain three analytics-useful columns it should carry.
  8. Q58You must keep product price history (never overwrite). Sketch a normalized design.
  9. Q59Tickets can change status many times. Sketch the status-history table and its keys.
  10. Q60Requirement: tag products with arbitrary tags. Design the normalized many-to-many tag schema.
  11. Q61"An address can belong to a customer OR a store." Discuss polymorphic-FK options and a cleaner normalized alternative.
  12. Q62Design a schema that makes it impossible to store both age and date_of_birth redundantly.
  13. Q63Per-line tax depends on product category. Where should the category -> tax_rate mapping live to stay 3NF?
  14. Q64Design "a campaign runs on many platforms; each platform run has its own spend." Sketch the tables.
  15. Q65A junior proposes phone1, phone2, phone3 columns on customers. Redesign it normalized and say which NF it fixes.
  16. Q66Model "a return is for one order and may cover several of that order's items." Sketch the keys.
  17. Q67Design a supplier-product catalog where one product is sourced from multiple suppliers at different costs.
  18. Q68Surrogate vs natural key for a table of Indian PIN codes - choose and justify.
  19. Q69Sketch how FK design enforces "an order's items all belong to that same order."
  20. Q70Reporting need: one row per (customer, month) with totals. Base table or derived/materialized object? Defend the choice.
  21. Q71Design "products in categories, categories in parent categories" (a hierarchy). Show the self-reference.
  22. Q72Support soft-deletes without breaking foreign keys. Sketch an approach and name one downside.
  23. Q73"A warehouse records inventory snapshots per product per day." What is the natural composite key?
  24. Q74"A customer has many payment methods, exactly one default." How do you enforce the single default cleanly?
  25. Q75Critique a design that dumps everything into one "attributes" JSON column instead of typed columns, for analytics.

RETAILMART DEEP DIVE & INTEGRITY

  1. Q76Trace the full foreign-key chain from one sales.order_items row up to the customer and down to the product.
  2. Q77orders.net_total could drift from SUM(order_items). Propose a periodic reconciliation an analyst could run.
  3. Q78Argue whether customers.tier should be a FK to a tiers dimension, given its heavy use in RFM/segmentation.
  4. Q79The Head of Logistics wants courier on every shipment row. Explain how RetailMart models courier and whether to denormalize.
  5. Q80Explain why sales.returns using refund_amount (not return_amount) and having no cust_id is a deliberate, defensible design.
  6. Q81If region were stored as text on stores instead of region_id, give a concrete update anomaly that could occur.
  7. Q82Map RetailMart to a star schema: pick one fact table and four dimensions it would join to.
  8. Q83Why is core.dim_category being flat (no parent_category_id) a reasonable simplification, and when would a hierarchy be needed?
  9. Q84The Compliance Officer wants an audit trail of price changes. Which RetailMart structure supports change detection, and how?
  10. Q85Describe, conceptually, how you'd verify that every order_items.prod_id exists in products.products (an integrity audit).
  11. Q86Identify a RetailMart table where a denormalized column is justified for performance, and name the safeguard you'd add.
  12. Q87Marketing stores platform names as free text. Propose a normalized fix and state the reporting benefit.
  13. Q88Why does separating finance.payment_modes from orders make adding a new method (e.g., "UPI Lite") safe?
  14. Q89loyalty.members carries tier_id while customers.customers also has tier. Is that redundancy a real problem? Discuss.
  15. Q90State the integrity rule that should prevent a review existing for a non-existent product in customers.reviews.
  16. Q91A stakeholder wants one big denormalized sales view for Excel users. Outline its columns and the joins that build it.
  17. Q92Explain how splitting orders from order_items prevents an insertion anomaly when an order has many products.
  18. Q93Why is web_events.page_views using its own view_id and view_timestamp (not reusing order keys) correct design?
  19. Q94Discuss the integrity tradeoff of supply_chain.inventory_snapshots using a composite key and no surrogate id.
  20. Q95The CFO wants guaranteed-correct revenue. Explain why computing from order_items with status filters can beat a stored total.
  21. Q96Identify two 1:M relationships in RetailMart and one effectively M:N relationship with its resolving table.
  22. Q97Propose referential integrity for "an employee belongs to exactly one store and one department."
  23. Q98Explain why dimension tables changing rarely (dim_region, dim_brand) is what makes denormalizing them into reports relatively safe.
  24. Q99A new analyst flattens customers + addresses + orders into one base table "to make life easy." List three concrete risks.
  25. Q100Across the whole schema, summarize in four bullets how RetailMart balances normalization (integrity) with analyst convenience.

Production scenarios, optimisation

NORMALIZATION & MODELING AT SCALE

  1. Q1Designing RetailMart's analytics layer: argue for a Kimball star schema versus a fully normalized 3NF warehouse, with tradeoffs.
  2. Q2Explain how a wide denormalized fact plus columnar storage can beat a normalized model for analytics - and when it doesn't.
  3. Q3Design a Type-2 slowly-changing dimension for customer tier history: sketch keys, effective dates, and a current flag.
  4. Q4When do Type 1, Type 2, and Type 3 SCDs each make sense for a customer dimension? Give a rule of thumb.
  5. Q5A team proposes an EAV (entity-attribute-value) table to allow "any attribute." Critique it for analytics and offer an alternative.
  6. Q6How do normalization decisions change when storage is cheap but JOINs over billions of rows are expensive?
  7. Q7You must serve OLTP correctness and OLAP speed on the same data. Describe a normalized-base -> denormalized-marts architecture and its sync risk.
  8. Q8Give concrete criteria for choosing a JSONB column over typed normalized columns.
  9. Q9Explain "one fact per grain" and why mixing grains in a single fact table is a modeling error.
  10. Q10Design a bridge table for orders-to-promotions where multiple promos can stack; how do you avoid double-counting revenue?
  11. Q11How would you model returns so that net revenue is always reconstructable without double-subtracting?
  12. Q12Explain the point-in-time-correctness risk of denormalizing customer tier onto every order row when tier changes over time.
  13. Q13Why is classifying measures as additive / semi-additive / non-additive a modeling concern adjacent to normalization?
  14. Q14Design a conformed-dimension strategy so sales and supply_chain share one product dimension.
  15. Q15A denormalized reporting table drifts from source after a backfill. Design reconciliation plus alerting.
  16. Q16Explain when surrogate keys are mandatory in a warehouse even though source systems already have natural keys.
  17. Q17Defend or reject the maxim "normalize until it hurts, then denormalize until it works."
  18. Q18How do late-arriving facts (an order_item inserted days later) break naive denormalized aggregates, and how do you guard against it?
  19. Q19Explain idempotency in an ETL upsert and why it matters for consistency at the pipeline level.
  20. Q20Design a schema that supports both "current price" and "price as of order date" without contradiction.
  21. Q21Why can blindly adding indexes to "fix" a denormalized table create write-amplification problems?
  22. Q22Distinguish normalization (logical) from partitioning/sharding (physical scaling) and explain why they're orthogonal.
  23. Q23A product belongs to multiple categories. Show the M:N design and explain the revenue-by-category allocation problem it creates.
  24. Q24How would you model multi-currency orders correctly, avoiding storing only a converted total?
  25. Q25List a staff-level five-point checklist you always apply when reviewing a teammate's schema change.

CONCURRENCY, MVCC & DURABILITY (DEEP)

  1. Q26Explain PostgreSQL MVCC: how row versions (xmin/xmax) let readers and writers avoid blocking each other.
  2. Q27Why does MVCC create dead tuples, and what is VACUUM's job? Tie it to long analyst transactions.
  3. Q28A long-running reporting transaction holds back the xmin horizon. Explain the bloat and vacuum consequences.
  4. Q29Explain snapshot isolation and how it differs from true serializability.
  5. Q30What is Serializable Snapshot Isolation (SSI) in Postgres, and what kind of conflict does it detect?
  6. Q31Give a write-skew example (e.g., two on-call doctors) and explain why only SERIALIZABLE prevents it.
  7. Q32Explain how READ COMMITTED can produce a non-repeatable read between two SELECTs in one transaction.
  8. Q33Walk through how SELECT ... FOR UPDATE plus a re-check eliminates a lost update.
  9. Q34Distinguish row-level from table-level locks and when each is acquired.
  10. Q35Describe a deadlock cycle across three transactions and how the detector breaks it.
  11. Q36Why is application-level retry the correct response to a serialization_failure (SQLSTATE 40001)?
  12. Q37Explain how the write-ahead log guarantees durability and enables crash recovery (redo).
  13. Q38What are checkpoints, and what is the tradeoff between checkpoint frequency and recovery time?
  14. Q39Explain synchronous versus asynchronous replication and the durability/latency tradeoff.
  15. Q40With async replicas, what consistency anomaly can an analyst hit reading a replica right after a write?
  16. Q41Explain "read your own writes" consistency and why it can break on a lagging replica.
  17. Q42Why does a UNIQUE index (not merely a check) enforce uniqueness correctly under concurrency?
  18. Q43How do advisory locks differ from row locks, and give one legitimate use case.
  19. Q44State the double-booking problem and three distinct ways to prevent it (unique constraint, locking, serializable).
  20. Q45Explain why COUNT(*) can differ between two reads under READ COMMITTED but not under REPEATABLE READ.
  21. Q46How can an analyst's accidental "BEGIN" with no COMMIT (idle-in-transaction) harm a production database?
  22. Q47Explain why chunked writes improve throughput but sacrifice all-or-nothing semantics across chunks.
  23. Q48Describe how you'd make a multi-step ETL atomic across staging and final tables.
  24. Q49Why is "exactly once" hard in distributed pipelines, and how does idempotent upsert approximate it?
  25. Q50Summarize how isolation, durability, and replication interact when you promise "the dashboard is never wrong."

CONSISTENCY, DISTRIBUTED & TRADEOFFS

  1. Q51State the CAP theorem and what it forces you to sacrifice during a network partition.
  2. Q52Contrast strong versus eventual consistency with a concrete e-commerce example.
  3. Q53Why do many analytics systems accept eventual consistency while checkout cannot?
  4. Q54Contrast BASE with ACID and say where each is the right fit.
  5. Q55A microservice split puts orders and inventory in separate databases. How is cross-service atomicity lost, and what pattern recovers it?
  6. Q56Explain the saga pattern and compensating transactions using an order/payment/inventory flow.
  7. Q57Why can't a distributed system trivially provide single-node ACID across services?
  8. Q58Explain two-phase commit and why it is often avoided in practice.
  9. Q59What is the transactional outbox pattern, and which consistency problem does it solve?
  10. Q60An event pipeline double-processes a message. Which property saves the aggregates, and how?
  11. Q61Explain why "the dashboard number depends on which replica you hit," and how to make it deterministic.
  12. Q62Discuss the tradeoff of pre-aggregating via materialized views versus always computing from raw for correctness.
  13. Q63When is "good enough, 5-minutes-stale" the right consistency target for an executive dashboard?
  14. Q64How do late or out-of-order events break windowed aggregates, and what mitigations exist (watermarks/grace)?
  15. Q65Why does duplicating data across services trade integrity for availability and latency?
  16. Q66Explain the limit of referential integrity: a FK works inside one database but not across two services. Implication?
  17. Q67How would you guarantee a financial report ties out exactly given an eventually-consistent source?
  18. Q68Explain idempotency keys for a "retry payment" button and the anomaly they prevent.
  19. Q69Why does read-after-write matter when an analyst inserts a fix then immediately re-queries a replica?
  20. Q70A normalized OLTP source feeds a denormalized warehouse via CDC. What consistency guarantee does CDC actually give?
  21. Q71Why is wall-clock time unreliable for ordering events across distributed nodes, and what is used instead?
  22. Q72Explain the reconciliation pattern: periodic full re-compute to correct drift in incremental aggregates.
  23. Q73When does a single source of truth become impossible, and how do you choose the authoritative source?
  24. Q74Why should analysts know whether they're querying the OLTP primary, a replica, or the warehouse?
  25. Q75Summarize the spectrum from ACID-strong to eventually-consistent and where RetailMart's analytics layer should sit.

RETAILMART END-TO-END AUDIT & DESIGN CRITIQUE

  1. Q76Run a normalization audit of customers + addresses + orders + order_items: rate each table's normal form and justify.
  2. Q77Define RetailMart's canonical revenue (which table, which status filters, how returns are handled) and explain why it's authoritative.
  3. Q78Propose a Type-2 history design so RetailMart can answer "what tier was this customer ON the order date?"
  4. Q79Design a guardrail to keep orders.net_total consistent with order_items (constraint vs trigger vs scheduled check) and pick one per OLTP/analytics context.
  5. Q80Map RetailMart into a full sales star schema: state the fact grain, the measures, and every dimension.
  6. Q81Critique keeping customers.tier as text versus a tier dimension, weighing reporting, integrity, and SCD needs.
  7. Q82Design how RetailMart can expose a denormalized one-row-per-order-line mart without corrupting the normalized base.
  8. Q83The analytics layer builds 76 views + 13 materialized views. Explain the refresh/consistency risks and how you'd schedule and monitor them.
  9. Q84A monthly-sales materialized view drifts after a backfill of late orders. Design detection plus correction.
  10. Q85Audit RetailMart's denormalized fields (net_total, any cached labels) and classify each as safe or risky, with reasoning.
  11. Q86Model multi-warehouse inventory so "stock as of a date" is reconstructable; tie it to inventory_snapshots' composite key.
  12. Q87The Compliance Officer needs an immutable audit log of refunds. Design it (append-only, keys) and state the durability requirement.
  13. Q88Explain how you'd guarantee a dashboard's "total customers" matches the OLTP source despite replication lag.
  14. Q89Two reports disagree on revenue by Rs 2 Cr. Walk through a systematic root-cause using normalization and consistency concepts.
  15. Q90Design a reconciliation job that flags rows where SUM(order_items) != orders.net_total, and define what to alert on.
  16. Q91Critique using customer email as a cross-system join key instead of a stable surrogate customer_id.
  17. Q92order_date is a DATE (no time). Explain the consistency implication for "orders per hour" and name the correct alternative source.
  18. Q93Propose a conformed product dimension so sales, supply_chain, and manufacture all agree on what a product is.
  19. Q94Design point-in-time-correct revenue-by-region assuming a store could change region over time.
  20. Q95Marketing wants attribution joining web_events to orders. Discuss the key and consistency challenges (session_id, customer_id, timing).
  21. Q96Explain why "delete a customer" is dangerous in RetailMart, and design a compliant anonymization that preserves referential integrity.
  22. Q97Build the case for a separate read replica or warehouse for analysts, citing the MVCC/bloat and isolation issues from earlier sections.
  23. Q98A stakeholder wants real-time AND always-correct AND cheap dashboards. Use CAP/consistency tradeoffs to explain why they must choose.
  24. Q99Propose a five-point weekly data-integrity test suite for RetailMart (FK orphans, total drift, duplicate keys, NULL keys, status sanity).
  25. Q100Write a one-paragraph design philosophy for RetailMart that balances integrity, performance, and analyst usability at a staff-engineer level.