RDBMS Flashcards
“What is database replication?”
“Replication is the process of copying and maintaining database instances in multiple locations to ensure high availability and fault tolerance.”
“What is the difference between master-slave and master-master replication?”
“In master-slave replication, one master handles writes and multiple slaves handle reads. In master-master replication, multiple masters can handle both reads and writes.”
“Which type of replication introduces potential data conflicts?”
“Master-master replication, since multiple nodes can perform write operations, leading to synchronization challenges.”
“What is the main drawback of synchronous replication?”
“It ensures strong consistency but can introduce higher latency due to the need for immediate replication.”
“Which replication method prioritizes performance over consistency?”
“Asynchronous replication, as it allows data to be written to replicas with some delay.”
“How does replication improve read-heavy workloads?”
“By directing read queries to slave databases, reducing load on the master.”
“What is database sharding?”
“Sharding is the process of dividing a large database into smaller, more manageable pieces called shards, each containing a subset of the data.”
“How is data distributed in sharding?”
“Data is distributed based on a sharding key, such as user ID or geographic region.”
“What are the primary benefits of sharding?”
“It enables horizontal scaling, reduces query response times, and allows parallel processing.”
“What challenges can arise from sharding?”
“Uneven data distribution (hotspots), complex query routing, and expensive re-sharding operations.”
“What is the difference between sharding and replication?”
“Sharding partitions data across multiple nodes, while replication duplicates the same data across multiple nodes.”
“What is a common strategy for choosing a sharding key?”
“A key with even distribution across data, such as user IDs hashed to different shards.”
“What is database federation?”
“Federation involves breaking a database into multiple independent databases that operate separately but can be queried together if needed.”
“How does federation differ from sharding?”
“In federation, each database operates independently, while in sharding, shards are part of the same logical database.”
“What are the advantages of database federation?”
“Flexibility to use different database technologies, independent scaling, and better isolation between different datasets.”
“What are the drawbacks of database federation?”
“More complex query logic and increased operational overhead for managing multiple databases.”
“Give an example of a real-world use case of federation.”
“An e-commerce platform where products, users, and orders are stored in separate databases.”
“What is denormalization?”
“Denormalization is the process of adding redundant data to reduce the number of joins and improve read performance.”
“What is the main advantage of denormalization?”
“Faster read queries by reducing the number of required joins.”
“What is the main disadvantage of denormalization?”
“Increased storage requirements and potential data inconsistency.”
“When should denormalization be used?”
“In read-heavy applications where performance is critical.”
“How does denormalization impact data integrity?”
“It increases the risk of data inconsistency due to duplicated information.”
“What is SQL tuning?”
“SQL tuning is the process of optimizing SQL queries to improve performance and reduce resource usage.”
“What is an index in SQL?”
“An index is a database structure that improves the speed of data retrieval.”
“Why should you avoid over-indexing?”
“Over-indexing increases storage overhead and slows down write operations.”
“What does the EXPLAIN statement do?”
“It shows the execution plan of a query, helping identify performance bottlenecks.”
“What is query caching?”
“Query caching stores frequently accessed query results to reduce database load.”
“What is connection pooling?”
“Connection pooling reuses database connections to reduce overhead from creating new connections.”
“How does replication improve fault tolerance?”
“It ensures data is available on multiple servers, reducing the risk of data loss if one server fails.”
“What are primary keys and foreign keys?”
“A primary key uniquely identifies a record, while a foreign key links to a primary key in another table.”
“What is the difference between vertical and horizontal scaling?”
“Vertical scaling increases hardware resources on a single server, while horizontal scaling adds more servers.”
“What is the CAP theorem?”
“The CAP theorem states that a distributed system can only provide two out of three: Consistency, Availability, and Partition tolerance.”
“What are some techniques to improve query performance?”
“Using indexing, partitioning, caching, and optimizing SQL queries.”
“What is the difference between OLAP and OLTP databases?”
“OLTP is optimized for transactional queries, while OLAP is optimized for analytical queries.”
“What is the difference between a clustered and non-clustered index?”
“A clustered index determines the physical order of data, while a non-clustered index does not.”
“What is the role of a database optimizer?”
“The database optimizer chooses the most efficient way to execute a query.”
“What is a database deadlock?”
“A deadlock occurs when two transactions hold locks that the other needs, causing a cycle of waiting.”
“What is ACID compliance in databases?”
“ACID stands for Atomicity, Consistency, Isolation, and Durability, ensuring reliable transactions.”
