CS377: Database Design - Relational Database Systems (RDS)

Activity Goals

The goals of this activity are:
  1. To define the various components of a typical Relational Database System (RDS)
  2. To differentiate between the benefits and costs of various indexing structures to improve search performance
  3. To select between memory and disk structures for backing a database

The Activity

Directions

Consider the activity models and answer the questions provided. First reflect on these questions on your own briefly, before discussing and comparing your thoughts with your group. Appoint one member of your group to discuss your findings with the class, and the rest of the group should help that member prepare their response. Answer each question individually from the activity on the Class Activity Questions discussion board. After class, think about the questions in the reflective prompt and respond to those individually in your notebook. Report out on areas of disagreement or items for which you and your group identified alternative approaches. Write down and report out questions you encountered along the way for group discussion.

Model 1: Structure of a Relational Database Management (RDBMS) System

RDBMS structure

Questions

  1. Some items are stored in memory as well as on disk; how is this reconciled?
  2. What would happen to the in-memory data if the database system was shut down prior to writing to disk? How might this data be recovered?

Model 2: Indexing

An example database from Database Design 2nd Ed by Watt and Eng
Hash table 4 1 1 0 0 1 0 LL

Questions

  1. What are some strategies you might use to allow a person to look up the location of a record in a database more quickly than by linear search?
  2. Can you have more than one index on the same table? What might this mean?
  3. Given a person's name, how might you use a hash table to quickly identify the row that contains the corresponding record?
  4. How might you change the hash if you wanted to search by date-of-birth rather than by name?
  5. If the hash table isn't big enough to hold every possible record hash, what could happen? What would the drawback be?
  6. Given that some indices enable O(1) constant time lookups, while others are O(log n) logarithmic time lookup with respect to the number of records n, what might you speculate is the underlying data structure used to create an index?

Model 3: ACID and BASE, and the CAP Theorem

CAP theorem
ACIDBASE
AtomicBasically Available
ConsistentSoft State
IsolatedEventual Consistency
Durable

Questions

  1. The CAP Theorem specifies the tradeoff between availability, consistency, and partition tolerance. What do you think these mean?
  2. Why do you think it is not possible to achieve all three items in the CAP Theorem?
  3. How do the ACID and BASE models appear to balance these tradeoffs?

Submission

Submit your answers to the questions using the Class Activity Questions discussion board. You may also respond to questions or comments made by others, or ask follow-up questions there. Answer any reflective prompt questions in the Reflective Journal section of your OneNote Classroom personal section.