--- title: "Database Design & Patterns" description: "Master database design: normalization, indexing, query optimization, ACID, transactions, sharding, replication, and enterprise patterns for scalable systems." author: "Mohammad Abu Mattar" canonical: https://mkabumattar.com/quizzes/post/database-design-patterns-quiz --- # Database Design & Patterns Welcome to the Database Design & Patterns Quiz! Test your knowledge on database normalization, indexing, query optimization, ACID properties, sharding, replication, and more. Each question has a hint and detailed explanations for all options. Good luck! ## Questions ### 1. What is database normalization? - The process of organizing data to reduce redundancy and improve data integrity - The process of duplicating data across tables to minimize join operations and latency - The practice of converting unstructured NoSQL documents into a rigid relational schema - The technique of compressing large database files to save physical storage space **Hint:** Think about reducing redundancy. ### 2. What is denormalization? - Strategically duplicating data to improve read performance at the cost of write complexity - Strictly adhering to 3NF standards to ensure every non-key attribute depends only on the key - Removing all primary and foreign key constraints to allow for faster bulk data ingestion - Applying compression algorithms to historical data partitions to reduce operational costs **Hint:** Think about trading consistency for speed. ### 3. What is database indexing? - Creating auxiliary structures to speed up data retrieval while increasing write overhead - Physically reordering rows on disk to match the alphabetical order of a specific column - Defining primary key constraints that prevent the insertion of duplicate records in a table - Compressing column data into a smaller format to reduce the number of I/O operations **Hint:** Think about speeding up queries. ### 4. What is a compound index? - A single index entry created on multiple columns to optimize specific multi-column queries - A collection of several independent indexes that the database engine merges at runtime - An index that automatically calculates and stores the sum of numeric values in a table - A specialized index designed to support full-text search across large blocks of string data **Hint:** Think about multiple columns in one index. ### 5. What is query optimization? - Improving execution efficiency via index selection, plan analysis, and query rewriting - Rewriting application code to reduce the total number of connections made to the database - Moving all active data into an in-memory cache to bypass the disk-based storage engine - Applying minification to SQL strings to reduce the amount of network bandwidth consumed **Hint:** Think about making queries efficient. ### 6. What is ACID? - A set of properties ensuring that database transactions are processed reliably and safely - A distributed system model that prioritizes high availability over immediate consistency - A performance metric used to measure the average response time of a complex SQL join - A database architecture where data is automatically split into four independent shards **Hint:** Think about transaction properties. ### 7. What is BASE (Eventual Consistency)? - A design philosophy prioritizing availability and scale by relaxing strict consistency rules - A traditional relational model that guarantees every transaction results in a valid state - A specialized indexing technique used for geospatial data in high-concurrency environments - An acronym for Big-data, Analytics, Storage, and Execution in modern cloud warehouses **Hint:** Think about NoSQL trade-offs. ### 8. What is sharding? - Distributing a large dataset across multiple independent database instances or servers - Creating identical copies of the entire database to balance read requests across nodes - Dividing a single table into multiple smaller parts within the same database server - A technique for merging disparate data sources into a single unified schema for reporting **Hint:** Think about horizontal data partitioning. ### 9. What is replication? - Maintaining synchronized copies of the database across multiple servers to ensure availability - Breaking a database into small chunks based on a shard key to increase write throughput - The process of migrating data from a relational system to a document-based storage engine - A method for restoring a database from a tape backup after a catastrophic hardware failure **Hint:** Think about copying data to multiple servers. ### 10. What is CAP theorem? - The principle that a distributed system can only provide two of three specific guarantees - The rule that database performance is limited by CPU, Arithmetic logic, and Power usage - A standard for measuring the total storage capacity of distributed cloud-native databases - An optimization algorithm used to balance read and write loads across a replicated cluster **Hint:** Think about tradeoffs in distributed systems. ### 11. What is materialized view? - A database object that contains the results of a query and is stored on disk for speed - A virtual table that runs a query dynamically every time it is accessed by the application - A temporary table created in memory that only exists for the duration of a single session - A specialized report format designed for export into external data visualization tools **Hint:** Think about precomputed query result. ### 12. What is transaction isolation level? - A setting that defines the degree to which transactions are visible to other concurrent operations - A hardware-level restriction that prevents more than one user from connecting to a database - The physical separation of database files on different disk drives to prevent I/O bottlenecks - A security feature that prevents unauthorized users from viewing sensitive row-level data **Hint:** Think about concurrent transaction interference. ### 13. What is n+1 query problem? - A performance antipattern where an application makes separate queries for each related item - A database limitation where a table can only be joined with a maximum of one other table - A syntax error occurring when a query attempts to select more columns than exist in a table - An optimization technique that adds one extra index for every new column added to a schema **Hint:** Think about fetching related data inefficiently. ### 14. What is EXPLAIN in databases? - A diagnostic statement that returns the execution plan chosen by the query optimizer - A commenting syntax used to provide documentation for complex stored procedures - A security command that logs the identity of every user who accesses a specific table - A keyword used to declare the intended data types for a set of temporary variables **Hint:** Think about query execution analysis. ### 15. What is referential integrity? - A state where all foreign key values correctly correspond to existing primary keys - The requirement that every record in a database must have a unique identifier on disk - An encryption standard that ensures sensitive data cannot be referenced by external apps - The process of verifying that a database schema matches the intended business domain model **Hint:** Think about foreign keys maintaining data relationships. ### 16. What is a deadlock in databases? - A situation where two or more transactions are permanently blocked by locks the others hold - A catastrophic hardware failure that prevents the database engine from booting up - A security lock placed on a user account after multiple failed login attempts - The time delay that occurs when a query is waiting for a large disk read to complete **Hint:** Think about transactions waiting for each other. ### 17. What is connection pooling? - Maintaining a cache of open database connections to be reused by multiple requests - Combining multiple physical databases into a single virtual pool for global queries - A security measure that limits the total number of simultaneous users allowed on a server - A method for distributing database files across a storage area network (SAN) pool **Hint:** Think about reusing database connections. ### 18. What is optimistic vs pessimistic locking? - Two concurrency models that differ in whether they lock data before or after a transaction - A performance comparison between traditional SQL databases and modern NoSQL systems - A security assessment used to determine the vulnerability of a database to SQL injection - The difference between storing data in a local cache versus a persistent disk-based store **Hint:** Think about concurrency control strategies. ### 19. What is OLTP vs OLAP? - Two workload categories: one for real-time transactions and one for complex data analysis - Two protocol standards for transferring data between different database vendors - The difference between storing data in rows versus storing data in a nested document format - A set of encryption algorithms used to secure data in transit versus data at rest **Hint:** Think about transactional vs analytical workloads. ### 20. What is database partitioning? - The process of dividing a large table into smaller, more manageable pieces on a single server - Splitting the database schema into separate microservices with their own storage - Creating a complete copy of the database to serve read-only requests for analytics - The act of moving old data into a secondary cold storage system to save on costs **Hint:** Think about dividing large tables. ### 21. What is a window function? - A function that performs calculations across a set of table rows related to the current row - A scheduled task that only executes during a specific maintenance window at night - A specialized UI component used to display database records in a desktop application - A query that limits the result set to only those records created within the last hour **Hint:** Think about analyzing data within partitions. ### 22. What is a CTE (Common Table Expression)? - A temporary named result set that can be referenced within a single SQL statement - A permanent table structure used to store metadata about the database configuration - A database-wide variable that can be shared across multiple different user sessions - An external tool used to convert legacy Excel spreadsheets into relational database rows **Hint:** Think about WITH clause for subqueries. ### 23. What is write-ahead logging (WAL)? - A technique where changes are recorded in a log before being applied to the main database - An optimization that delays writing data until the user explicitly refreshes the page - A security feature that logs the IP address of every user who performs a write operation - A method for pre-calculating the results of expensive queries before they are requested **Hint:** Think about durability in transactions. ### 24. What is schema migration? - The management of incremental, reversible changes to a database schema over time - The one-time process of moving all data from an old server to a new hardware cluster - The automatic conversion of SQL queries into a different language for another vendor - A security process that moves sensitive tables into a hidden, encrypted partition **Hint:** Think about evolving database schema safely. ### 25. What is the hot table problem? - A performance bottleneck caused by high contention for locks on a single, popular table - A physical hardware issue where a database server overheats due to high CPU usage - A security vulnerability where a table is accidentally exposed to the public internet - An error that occurs when a table exceeds the maximum row limit of the storage engine **Hint:** Think about contention on heavily accessed tables. ### 26. What is read replica? - A read-only copy of the primary database used to offload traffic from the master node - A backup file stored on an offline drive to be used only during a disaster recovery - A specialized database user account that only has permission to view specific columns - An identical database server that remains idle until the primary server fails **Hint:** Think about scaling read capacity. ### 27. What is lazy loading vs eager loading? - Two strategies defining whether related data is fetched on-demand or during the initial query - Two methods for deciding whether to use a local cache or a remote database server - The difference between loading data into a browser versus loading it into a mobile app - A comparison of how quickly images are rendered on a webpage versus text content **Hint:** Think about when related data is fetched. ### 28. What is database backup strategy? - A defined plan combining full, incremental, and log backups to enable data recovery - The practice of making an exact copy of the database schema without any of the data - A security rule that requires every user to change their password once every 30 days - A technique for deleting old records to ensure the database does not exceed disk capacity **Hint:** Think about disaster recovery planning. ### 29. What is a primary key design consideration? - The decision between using inherent domain data or system-generated IDs as identifiers - The choice between using a single server or multiple shards to host the primary key - The requirement that every primary key must be encrypted before it is stored on disk - A performance limit on the total number of primary keys allowed in a single database **Hint:** Think about choosing natural vs surrogate keys. ### 30. What is database caching strategy? - A plan for storing frequently accessed data in high-speed memory to reduce backend load - A method for permanently moving historical data into an in-memory database for storage - A specialized indexing technique used to speed up queries on large BLOB or image columns - The process of minifying SQL queries to reduce the amount of RAM consumed by the engine **Hint:** Think about reducing database load with cache layers.