Database Flashcards
How does partitioning and sharding contribute to database scalability?
Partitioning involves dividing a large table into smaller partitions based on a criteria like date range, while sharding involves distributing data across multiple servers. This allows for more efficient data storage and retrieval, improving scalability by distributing the workload.
What are some optimization techniques for improving database performance?
Optimization techniques include caching frequently accessed data, optimizing queries using indexes and execution plans, and using data compression to reduce storage costs and improve I/O performance.
What security measures are important for protecting a database?
Important security measures include implementing role-based access control (RBAC), encrypting sensitive data at rest and in transit, and performing regular security audits and penetration testing to identify and mitigate vulnerabilities.
What is the purpose of backup and recovery procedures for a database?
Backup and recovery procedures are essential for ensuring data durability and recoverability in case of failures or disasters. They involve taking regular backups of the database and implementing procedures for restoring data from backups when needed.
How can database scalability be achieved?
Database scalability can be achieved through vertical scaling (upgrading hardware resources) and horizontal scaling (adding more database servers and distributing data across them using techniques like partitioning and sharding).
What are some common data types used in SQL databases?
Common data types in SQL databases include INTEGER (for whole numbers), VARCHAR(n) (for variable-length character strings), DATE (for dates), and DECIMAL(p, s) (for fixed-point numbers).
How does a JOIN operation work in SQL?
A JOIN operation combines rows from two or more tables based on a related column between them. Common types of JOINs include INNER JOIN (returns rows that have matching values in both tables) and OUTER JOIN (returns all rows from one or both tables, with NULL values where no match is found)
What is a primary key in SQL, and why is it important?
A primary key is a unique identifier for each row in a table. It ensures data integrity by preventing duplicate rows and is used as a reference point for relationships between tables. Primary keys are typically implemented using a UNIQUE constraint.
What are some common NoSQL database types?
Common types of NoSQL databases include document databases (e.g., MongoDB), key-value stores (e.g., Redis), column-family stores (e.g., Cassandra), and graph databases (e.g., Neo4j).
How does document-based storage differ from relational storage?
Document-based storage, commonly used in NoSQL databases like MongoDB, stores data in flexible, schema-less documents (e.g., JSON or BSON), allowing for nested structures and dynamic schemas. In contrast, relational storage in SQL databases requires predefined schemas with structured tables and relationships between them.
What is eventual consistency in NoSQL databases?
Eventual consistency is a consistency model used in some NoSQL databases where data updates may be propagated asynchronously to multiple nodes, resulting in temporary inconsistencies. However, over time, all replicas will converge to a consistent state.
What is CAP theorem, and how does it relate to NoSQL databases?
The CAP theorem states that in a distributed system, it’s impossible to simultaneously achieve consistency (all nodes have the same data), availability (every request receives a response), and partition tolerance (the system continues to operate despite network partitions). NoSQL databases often prioritize partition tolerance and availability over strong consistency, which is reflected in their design choices.
