4. Advanced Database Questions Flashcards
What is the difference between a relational database and a NoSQL database?
Relational databases use structured schemas and SQL for data manipulation, while NoSQL databases are more flexible, allowing for unstructured data and various data models.
Examples of NoSQL databases include MongoDB and Cassandra.
How do you implement horizontal scaling in a relational database?
Horizontal scaling in a relational database can be achieved through techniques such as sharding, where data is distributed across multiple servers.
What is database sharding, and when would you use it?
Database sharding is the process of splitting a database into smaller, more manageable pieces called shards, which can improve performance and scalability.
Sharding is often used when dealing with large datasets or high-traffic applications.
How does a distributed database ensure consistency in a multi-node environment?
A distributed database ensures consistency through protocols like CAP theorem, which balances consistency, availability, and partition tolerance.
What are the differences between a database index and a materialized view?
A database index improves query performance by providing quick access to rows, while a materialized view stores the result of a query for faster retrieval.
What is the difference between write-ahead logging (WAL) and journaling?
Write-ahead logging records changes before they are applied, ensuring data integrity, while journaling logs changes after they are made, primarily for recovery purposes.
How do you manage database replication and failover?
Database replication can be managed using master-slave configurations, while failover involves switching to a standby database in case of failure.
Explain the differences between database isolation levels.
Database isolation levels define how transaction integrity is visible to other transactions, with levels including Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
How does a query execution plan help in optimizing database performance?
A query execution plan outlines the steps the database engine will take to execute a query, helping identify bottlenecks and optimize performance.
What are the trade-offs of using SQL vs. NoSQL databases for different workloads?
SQL databases provide strong consistency and structured data handling, while NoSQL databases offer flexibility and scalability for unstructured data.
The choice depends on specific application needs and workloads.