Databases

Question 1

Explain the difference between SQL and NoSQL databases

Answer 1

SQL
* relational databases
* use structured tables
* use a standardized query language for data manipulation

NoSQL
* more flexible data models
* handling unstructured or semi-structured data

Question 2

What are some advantages and disadvantages of SQL

Answer 2

• ensure data consistency
• ensure ACID compliance
• doesn’t handle unstructured data as well

Question 3

What are some advantages and disadvantages of NoSQL

Answer 3

• flexible data models
• handles unstructured and semi-structured data
• could have issues with data consistency

Question 4

What scenarios would you recommend using SQL over NoSQL

Answer 4

• SQL for applications requiring transactional integrity, like financial systems
• NoSQL forsocial media platforms or IoT applications
• choice depends on factors like data complexity, scalability needs, and transactional requirements

Question 5

Describe the ACID properties in database transactions

Answer 5

• Atomicity: ensures transactions are treated as a single unit (all or nothing)
• Consistency: enforces transactions bring the database from one valid state to another
• Isolation: ensures transactions are executed independently, preventing interference
• Durability: guarantees that committed transactions persist even in the face of failures

Question 6

What trade-offs might be involved in maintaining ACID properties?

Answer 6

• performance overhead due to locking and serialization
• affects scalability in high-concurrency scenarios

Question 7

Explain the concept of indexing in databases

Answer 7

Indexing accelerates data retrieval by creating efficient access paths

Question 8

What are the different types of database indexes?

Answer 8

Some common indexes are B-tree and hash indexes

Question 9

How does indexing enhance query performance?

Answer 9

Indexing makes columns faster to query by creating pointers to where data is stored within a database

Question 10

What factors should be considered when choosing columns to index?

Answer 10

• selectivity (uniqueness)
• query patterns
• data modification frequency
• Indexing trade-offs include increased storage overhead and potential maintenance overhead due to updates.

Question 11

Discuss the importance of database normalization

Answer 11

• reduces data redundancy by organizing it into related tables while adhering to specific normal forms

Question 12

What are the different normal forms?

Answer 12

• 1NF, 2NF, 3NF, BCNF, etc.
• provide guidelines for reducing data duplication

Question 13

How does normalization contribute to efficient data storage and maintenance?

Answer 13

• minimizes anomalies
• maintains efficient query performance

Question 14

Are there scenarios where denormalization might be preferable?

Answer 14

• to meet specific query performance requirements
• example: creating redundant fields for improved read performance in reporting systems

Question 15

What does CAP stand for?

Answer 15

Consistency, Availability, and Partition Tolerance

Question 16

How does the CAP theorem describe the trade-offs between consistency, availability, and partition tolerance in distributed systems?

Answer 16

CAP theorum states that in a distributed system, it’s impossible to achieve all three of Consistency, Availability, and Partition Tolerance simultaneously

Question 17

Can you provide an example of a database that emphasizes each aspect of the CAP theorum?

Answer 17

MongoDB
* a NoSQL database
* emphasizes consistency and partition tolerance
* ensures data consistency and availability under network partitioning

CassandraDB
* emphasizes availability and partition tolerance
* may occasionally sacrifice strong consistency

Question 18

What is partition tolerance?

Answer 18

The ability to continue to work despite any number of communication breakdowns between nodes