RDBMS

Question 1

“What is database replication?”

Answer 1

“Replication is the process of copying and maintaining database instances in multiple locations to ensure high availability and fault tolerance.”

Question 2

“What is the difference between master-slave and master-master replication?”

Answer 2

“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.”

Question 3

“Which type of replication introduces potential data conflicts?”

Answer 3

“Master-master replication, since multiple nodes can perform write operations, leading to synchronization challenges.”

Question 4

“What is the main drawback of synchronous replication?”

Answer 4

“It ensures strong consistency but can introduce higher latency due to the need for immediate replication.”

Question 5

“Which replication method prioritizes performance over consistency?”

Answer 5

“Asynchronous replication, as it allows data to be written to replicas with some delay.”

Question 6

“How does replication improve read-heavy workloads?”

Answer 6

“By directing read queries to slave databases, reducing load on the master.”

Question 7

“What is database sharding?”

Answer 7

“Sharding is the process of dividing a large database into smaller, more manageable pieces called shards, each containing a subset of the data.”

Question 8

“How is data distributed in sharding?”

Answer 8

“Data is distributed based on a sharding key, such as user ID or geographic region.”

Question 9

“What are the primary benefits of sharding?”

Answer 9

“It enables horizontal scaling, reduces query response times, and allows parallel processing.”

Question 10

“What challenges can arise from sharding?”

Answer 10

“Uneven data distribution (hotspots), complex query routing, and expensive re-sharding operations.”

Question 11

“What is the difference between sharding and replication?”

Answer 11

“Sharding partitions data across multiple nodes, while replication duplicates the same data across multiple nodes.”

Question 12

“What is a common strategy for choosing a sharding key?”

Answer 12

“A key with even distribution across data, such as user IDs hashed to different shards.”

Question 13

“What is database federation?”

Answer 13

“Federation involves breaking a database into multiple independent databases that operate separately but can be queried together if needed.”

Question 14

“How does federation differ from sharding?”

Answer 14

“In federation, each database operates independently, while in sharding, shards are part of the same logical database.”

Question 15

“What are the advantages of database federation?”

Answer 15

“Flexibility to use different database technologies, independent scaling, and better isolation between different datasets.”

Question 16

“What are the drawbacks of database federation?”

Answer 16

“More complex query logic and increased operational overhead for managing multiple databases.”

Question 17

“Give an example of a real-world use case of federation.”

Answer 17

“An e-commerce platform where products, users, and orders are stored in separate databases.”

Question 18

“What is denormalization?”

Answer 18

“Denormalization is the process of adding redundant data to reduce the number of joins and improve read performance.”

Question 19

“What is the main advantage of denormalization?”

Answer 19

“Faster read queries by reducing the number of required joins.”

Question 20

“What is the main disadvantage of denormalization?”

Answer 20

“Increased storage requirements and potential data inconsistency.”

Question 21

“When should denormalization be used?”

Answer 21

“In read-heavy applications where performance is critical.”

Question 22

“How does denormalization impact data integrity?”

Answer 22

“It increases the risk of data inconsistency due to duplicated information.”

Question 23

“What is SQL tuning?”

Answer 23

“SQL tuning is the process of optimizing SQL queries to improve performance and reduce resource usage.”

Question 24

“What is an index in SQL?”

Answer 24

“An index is a database structure that improves the speed of data retrieval.”

Question 25

“Why should you avoid over-indexing?”

Answer 25

“Over-indexing increases storage overhead and slows down write operations.”

Question 26

“What does the EXPLAIN statement do?”

Answer 26

“It shows the execution plan of a query, helping identify performance bottlenecks.”

Question 27

“What is query caching?”

Answer 27

“Query caching stores frequently accessed query results to reduce database load.”

Question 28

“What is connection pooling?”

Answer 28

“Connection pooling reuses database connections to reduce overhead from creating new connections.”

Question 29

“How does replication improve fault tolerance?”

Answer 29

“It ensures data is available on multiple servers, reducing the risk of data loss if one server fails.”

Question 30

“What are primary keys and foreign keys?”

Answer 30

“A primary key uniquely identifies a record, while a foreign key links to a primary key in another table.”

Question 31

“What is the difference between vertical and horizontal scaling?”

Answer 31

“Vertical scaling increases hardware resources on a single server, while horizontal scaling adds more servers.”

Question 32

“What is the CAP theorem?”

Answer 32

“The CAP theorem states that a distributed system can only provide two out of three: Consistency, Availability, and Partition tolerance.”

Question 33

“What are some techniques to improve query performance?”

Answer 33

“Using indexing, partitioning, caching, and optimizing SQL queries.”

Question 34

“What is the difference between OLAP and OLTP databases?”

Answer 34

“OLTP is optimized for transactional queries, while OLAP is optimized for analytical queries.”

Question 35

“What is the difference between a clustered and non-clustered index?”

Answer 35

“A clustered index determines the physical order of data, while a non-clustered index does not.”

Question 36

“What is the role of a database optimizer?”

Answer 36

“The database optimizer chooses the most efficient way to execute a query.”

Question 37

“What is a database deadlock?”

Answer 37

“A deadlock occurs when two transactions hold locks that the other needs, causing a cycle of waiting.”

Question 38

“What is ACID compliance in databases?”

Answer 38

“ACID stands for Atomicity, Consistency, Isolation, and Durability, ensuring reliable transactions.”