Relational & NoSQL Database Design Flashcards

Which indexing structure is most efficient for range queries on numerical data in relational databases?

Answer: B-tree index

B-tree indexes store data in sorted order, making them ideal for range queries since rows within a range can be traversed sequentially.

In an entity-relationship (ER) diagram, a weak entity is characterized by:

Answer: Lacking a primary key on its own and depending on a strong entity

A weak entity cannot be uniquely identified by its own attributes alone and relies on the primary key of a related strong entity for identification.

Which NoSQL database type is best suited for storing and querying highly connected data such as social networks?

Answer: Graph database

Graph databases use nodes and edges to represent and traverse relationships, making them optimal for highly connected data like social networks.

What is denormalization in database design?

Answer: Intentionally introducing redundancy to improve read performance

Denormalization deliberately adds redundant data or groups data to reduce costly joins and improve query read performance at the cost of increased storage and update complexity.

Which SQL clause is used to filter grouped results after a GROUP BY operation?

Answer: HAVING

The HAVING clause filters the results of aggregate functions applied to groups, unlike WHERE which filters individual rows before grouping.

In Apache Cassandra, data is distributed across nodes using which mechanism?

Answer: Consistent hashing with a token ring

Cassandra uses consistent hashing with a token ring to distribute data across nodes, ensuring even distribution and enabling linear scalability.

Which relational concept defines the set of allowable values for an attribute?

Answer: Domain

A domain defines the set of valid values that an attribute can hold, such as integers in a specific range or strings of a certain length.