Database Design Quiz

Database Design Quiz

Test your database design skills across normalization, schemas, and relational modeling.

1. 1. What is the primary purpose of a primary key in a relational database?

   • To sort rows alphabetically
   • To uniquely identify each row in a table
   • To link two tables together
   • To enforce NOT NULL on all columns

2. 2. What does a foreign key do in a relational database?

   • Encrypts column values for security
   • References the primary key of another table to enforce referential integrity
   • Speeds up SELECT queries on large tables
   • Prevents duplicate rows within a single table

3. 3. Which of the following best describes a NoSQL database?

   • A database that never uses indexes
   • A database that only stores numbers
   • A non-relational database that stores data in formats like documents, key-value pairs, or graphs
   • A database that forbids SQL syntax entirely

4. 4. What does CRUD stand for in the context of database operations?

   • Copy, Replace, Update, Delete
   • Create, Read, Update, Delete
   • Connect, Retrieve, Upload, Drop
   • Create, Restore, Undo, Deploy

5. 5. What value does a NULL represent in SQL?

   • Zero
   • An empty string
   • The absence of a value or unknown
   • False

6. 6. Which relationship type exists when one row in Table A can relate to many rows in Table B, and one row in Table B can also relate to many rows in Table A?

   • One-to-One (1:1)
   • One-to-Many (1:N)
   • Many-to-Many (M:N)
   • Self-referential

7. 7. What is database normalization?

   • Converting all column names to lowercase
   • Organizing data to reduce redundancy and improve integrity
   • Merging all tables into a single flat table for performance
   • Backing up data to a secondary server

8. 8. Which property of ACID transactions ensures that a transaction either fully completes or has no effect at all?

   • Consistency
   • Isolation
   • Atomicity
   • Durability

9. 9. What is the main difference between the SQL and NoSQL query models?

   • SQL uses JSON while NoSQL uses XML
   • SQL uses structured query language on fixed schemas; NoSQL databases use flexible, often API-based query patterns
   • SQL can only run on Windows servers
   • NoSQL databases never support indexing

10. 10. What is the purpose of a database migration?

    • Moving the database server to a different data center
    • Exporting data to a CSV file
    • Applying versioned, incremental changes to a database schema over time
    • Switching from SQL to NoSQL

11. 11. Which normal form requires that every non-key attribute is fully functionally dependent on the entire primary key (eliminating partial dependencies)?

    • First Normal Form (1NF)
    • Second Normal Form (2NF)
    • Third Normal Form (3NF)
    • Boyce-Codd Normal Form (BCNF)

12. 12. What does First Normal Form (1NF) require?

    • No transitive dependencies between non-key columns
    • All columns contain atomic (indivisible) values and each column holds values of a single type
    • All foreign keys reference existing rows
    • The table has a composite primary key

13. 13. Which of the following is a valid reason to intentionally denormalize a database?

    • To satisfy foreign key constraints
    • To improve read query performance by reducing the number of joins
    • To enforce stricter data integrity
    • To ensure every table is in 3NF

14. 14. What is a surrogate key?

    • A key derived from business data such as an email address
    • A system-generated key (e.g., auto-increment integer or UUID) with no business meaning
    • A key that spans multiple columns
    • A foreign key that references itself

15. 15. In an ER diagram, what does a double-line (total participation) on an entity side of a relationship indicate?

    • The entity has a composite primary key
    • Every instance of that entity must participate in the relationship
    • The relationship is Many-to-Many
    • The entity is a weak entity

16. 16. According to the CAP theorem, a distributed database can guarantee at most how many of the three properties simultaneously?

    • All three: Consistency, Availability, and Partition Tolerance
    • Only one
    • Any two
    • None: they are always in conflict

17. 17. What is the key distinction between OLTP and OLAP workloads?

    • OLTP stores only structured data; OLAP stores only unstructured data
    • OLTP handles many short transactional read/write operations; OLAP handles complex analytical queries over large historical datasets
    • OLAP requires ACID transactions; OLTP does not
    • OLTP is cloud-only; OLAP is on-premises only

18. 18. Which transaction isolation level prevents dirty reads but still allows non-repeatable reads?

    • Read Uncommitted
    • Read Committed
    • Repeatable Read
    • Serializable

19. 19. What is the difference between a soft delete and a hard delete?

    • Soft delete removes the row immediately; hard delete marks it with a timestamp
    • Soft delete marks a row as deleted (e.g., with an is_deleted flag) without removing it; hard delete permanently removes the row
    • Soft delete applies only to indexes; hard delete applies to table rows
    • They are two names for the same SQL DELETE operation

20. 20. What does 'schema-on-write' mean compared to 'schema-on-read'?

    • Schema-on-write applies the schema when the data is written; schema-on-read applies it when the data is queried
    • Schema-on-write is used only by NoSQL databases
    • Schema-on-read requires strict column definitions before inserting data
    • They are equivalent: both enforce the schema at insertion time

21. 21. In hash-based sharding, what is the main advantage over range-based sharding?

    • Hash sharding makes range queries faster
    • Hash sharding distributes data more evenly across shards, reducing hotspots
    • Hash sharding allows unlimited shards without rebalancing
    • Hash sharding requires no shard key

22. 22. What is the main trade-off of synchronous replication compared to asynchronous replication?

    • Synchronous replication is faster but loses data on failure
    • Synchronous replication guarantees no data loss but increases write latency because the primary waits for replica acknowledgment
    • Asynchronous replication guarantees zero data loss
    • Synchronous replication only works with document databases

23. 23. What distinguishes Event Sourcing from a traditional CRUD data model?

    • Event Sourcing stores only the latest state; CRUD stores a full event log
    • Event Sourcing stores an immutable log of all state-changing events; current state is derived by replaying them
    • Event Sourcing requires a relational database; CRUD works with any database
    • They are functionally identical but use different SQL syntax

24. 24. What problem does CQRS (Command Query Responsibility Segregation) primarily solve?

    • It eliminates the need for database indexes
    • It separates the write model (commands) from the read model (queries), allowing each to be optimized independently
    • It replaces transactions with eventual consistency
    • It enforces strict 3NF normalization on all tables

25. 25. What is the key difference between optimistic and pessimistic locking?

    • Optimistic locking prevents all concurrent reads; pessimistic locking allows them
    • Optimistic locking assumes conflicts are rare and detects them at commit time; pessimistic locking acquires locks upfront to prevent concurrent modification
    • Pessimistic locking is only available in NoSQL databases
    • They differ only in syntax, not in behavior

26. 26. What is the difference between horizontal partitioning (sharding) and vertical partitioning?

    • Horizontal partitioning splits rows across multiple nodes; vertical partitioning splits columns into separate tables or stores
    • Horizontal partitioning splits columns; vertical partitioning splits rows
    • They are the same concept with different names
    • Vertical partitioning applies only to NoSQL databases

27. 27. What problem does the Two-Phase Commit (2PC) protocol solve in distributed databases?

    • It speeds up read queries across multiple shards
    • It coordinates an atomic commit across multiple participating nodes so all commit or all abort
    • It replicates data from primary to read replicas
    • It compresses transaction logs to save disk space

28. 28. Which data modeling technique is best suited for time-series data with high write throughput and range-based time queries?

    • Storing all events in a single normalized row per entity
    • Using an append-only table partitioned by time range, with a monotonically increasing timestamp as part of the primary key
    • Using a Many-to-Many junction table
    • Applying 3NF normalization to eliminate all redundancy

29. 29. In a multi-tenant SaaS application, what is the main advantage of the 'database-per-tenant' isolation strategy over a shared schema?

    • It uses less total storage than a shared schema
    • It provides stronger data isolation, simplifies per-tenant backups, and avoids noisy-neighbor query contention
    • It requires no schema migrations when adding new tenants
    • It is the only approach that supports ACID transactions

30. 30. How does the Saga pattern address distributed transaction consistency without using 2PC?

    • It locks all involved services until the transaction completes
    • It breaks a distributed transaction into a sequence of local transactions, each publishing an event; compensating transactions undo completed steps on failure
    • It relies on a central coordinator to hold a global lock
    • It buffers all writes in memory until the entire operation succeeds