CCP Relational & NoSQL Database Design 3 — Questions and Answers
Question 1: Which indexing structure is most efficient for range queries on numerical data in relational databases?
- Hash index
- B-tree index (Correct answer)
- Bitmap index
- Full-text index
Correct 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.
Question 2: In an entity-relationship (ER) diagram, a weak entity is characterized by:
- Having no attributes
- Lacking a primary key on its own and depending on a strong entity (Correct answer)
- Having multiple candidate keys
- Being represented with a single rectangle
Correct 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.
Question 3: Which NoSQL database type is best suited for storing and querying highly connected data such as social networks?
- Document store
- Key-value store
- Column-family store
- Graph database (Correct answer)
Correct answer: Graph database
Graph databases use nodes and edges to represent and traverse relationships, making them optimal for highly connected data like social networks.
Question 4: What is denormalization in database design?
- Splitting tables to remove redundancy
- Intentionally introducing redundancy to improve read performance (Correct answer)
- Converting a relational schema to NoSQL format
- Removing all foreign key constraints
Correct 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.
Question 5: Which SQL clause is used to filter grouped results after a GROUP BY operation?
- WHERE
- FILTER
- HAVING (Correct answer)
- ORDER BY
Correct answer: HAVING
The HAVING clause filters the results of aggregate functions applied to groups, unlike WHERE which filters individual rows before grouping.
Question 6: In Apache Cassandra, data is distributed across nodes using which mechanism?
- Master-slave replication
- Consistent hashing with a token ring (Correct answer)
- Two-phase commit protocol
- B-tree partitioning
Correct 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.
Question 7: Which relational concept defines the set of allowable values for an attribute?
- Schema
- Domain (Correct answer)
- Tuple
- Cardinality
Correct 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.
CCP Relational & NoSQL Database Design 3
Which indexing structure is most efficient for range queries on numerical data in relational databases?