CS4750 - Database Systems

Spring 2024 — Assignment 5

Due 19-April, 12pm EST (before class)

Purpose:

Reinforce indexing concepts
Use B+ tree to find records, insert keys, and delete keys

For this assignment, you may work individually or with at most 4 partners (max group size = 5). We strongly encourage you to work with partner(s) as different query designers typically have different perspectives. Besides, you can learn from each other.

Answer the following questions / complete the following tasks. All homework assignments are due before class on the due date. Please remember that the UVA Honor Code is in effect.

Note on handwriting and hand drawing

For this assignment, you may write your answers by hand **neatly**

You may draw your B+ trees by hand, take pictures, and embed them in your report. Do not submit the pictures separately. Submit a single report containing all of your answers, and save your report as a .pdf file.

Draw/write legibly; we have to deduct points if we are unsure what you drew/wrote.

[Total: 100 pts.]

[10 points] Consider a partially filled B+ tree — root value(s) missing.
1. (4 pts.) Fill in the value(s) present in the root of the tree.
2. (6 pts.) There are many sequences of exactly 3 insertions that will cause the root node in the given B+ tree (above) to split. List one such sequence of three insertions, and briefly justify your choice.
[6 points] Consider B+ trees with different orders (that is, different n, where n is the number of pointers).
1. (2 pts.) Which tree is more appropriate when the transactions to be executed against the database are mostly insertion, updating, and deletion? Circle one
```
a. The B+ tree with bigger n
b. The B+ tree with smaller n    
```
2. (4 pts.) Briefly justify why the B+ tree you chose is more appropriate.
[4 points] Consider the following B+ tree of order n = 3 (where n is the number pointers). Make the following assumptions: in case of underflow, if you can borrow from both siblings, choose the one on the right
1. (2 pts.) What is the minimum number of pointers to be followed to satisfy the query: Get all records with key(s) greater than 15 and less than 56?
  You should count only pointers from parent node to child node and pointers from sibling node to sibling node. Not key to actual data on the physical disk.
  Circle one
```
a.  1 pointer 
b.  2 pointers  
c.  3 pointers  
d.  4 pointers        
e.  5 pointers 
f.  None of the above 
```
2. (2 pts.) Briefly explain how you identify the number of pointers (your answer in the previous question)
[20 points] Use the following B+ tree, apply each of the following operations in order, and show the tree after each operation.
You may draw and fill the tree by hand, take a screenshot/picture, and embed it in your report. Draw/write legibly; we have to deduct points if we are unsure what you drew/wrote.
1. (10 pts.) Insert 55.
2. (10 pts.) Insert 58.
[10 points] Construct a B+ tree for the following set of key values:
```
(3, 10, 17, 32, 48, 50, 65, 72, 90, 120, 128, 25, 200, 150, 122, 215)
```
Assume that the tree is initially empty and values are added in ascending the order (as shown). Construct B+ tree for the case where the number of pointers (n) that will fit in one node is five (i.e., n = 5).
You may draw and fill the tree by hand, take a screenshot/picture, and embed it in your report. Draw/write legibly; we have to deduct points if we are unsure what you drew/wrote.
[50 points] Use the B+ tree from question 5, apply each of the following operations in order, and show the tree after each operation.
You may draw and fill the tree by hand, take a screenshot/picture, and embed it in your report. Draw/write legibly; we have to deduct points if we are unsure what you drew/wrote.
1. (10 points) Insert 30.
2. (10 points) Insert 28.
3. (10 points) Delete 25.
4. (10 points) Delete 17.
5. (10 points) Delete 48.

Late submission

(-25 points) for 24 hours late (submitted after 19-Apr 12pm (noon) EST, by 20-Apr 12pm (noon) EST)
(-50 points) for 48 hours late (submitted after 20-Apr 12pm (noon) EST, by 21-Apr 12pm (noon) EST)

To grade and record your assignment efficiently, the following details apply to all homework assignments, unless otherwise specified.

(-10 points) — For submitting a report in a Word document

Submission

Include all team member names and computingIDs in the report.

Save your report as a PDF. No Word document.

Each team submits only one copy.

Upload your report as a PDF to the Assignment 5 on Gradescope. Make sure you connect your partner to your group on Gradescope so that everyone receives credit.

Making your submission available to the course staff is your responsibility; if we cannot access or open your file, we have to assign a zero grade. Be sure to test access to your file before the due date.

Released under the Creative Commons License CC-BY-NC-SA 4.0 license.

Last updated 2024-04-11 19:42