Database Management Assignment

Question 1. Consider the following relation:

Recipe (rid: int, name: varchar(40), time: int, difficulty: int)

Assume the presence of a clustered index idx1 on rid and an unclustered index idx2 on time.

A. Give an example of an update query that would be significantly speeded up by idx1.

B. Give an example of an update query that would be significantly speeded up by idx2.

C. Give an example of an update query that would be significantly slowed down by idx1 and/or idx2.

D. Give an example of an update query that is not impacted by idx1 and/or idx2.

Question 2. A streaming company needs to measure the AVERAGE amount of time users spend watching video on their platform in a session. They are interested in users between the ages of 25 and 30, inclusive, who have at least 50 sessions stored in the DB. The duration for a user is the maximum time spent watching video in a session minus the minimum time spent in a session, regardless of the users' age at the time the sessions were made. There are several tables in the DB, but the ones of interested here are:

User (uid, name, age) , SessionRecord (uid, date, duration)

Primary key attributes are underlined. In the table definitions, the attribute of age is declared as NOT NULL, but duration can be NULL.

Write a query that satisfies the above requirements by making sure to use GROUP BY and HAVING clauses.

Question 3. Assume a B+ tree is used to index a set of 1,002,001,000 ordered keys. Each node/leaf can contain up to 1000 keys/pointers. All nodes and leaves of the tree are fully occupied. How many levels are in the resulting tree? Write the formula to obtain the final number.

Question 4. A. Given two relations A and B, with number of records n=|A| and m=|B|. What is the worst case complexity (number of comparisons) of the nested loop join if the predicate has O(1) complexity?

B. Considering the previous setting (relations A and B) with number of pages nA, nB and number of records per page pA and pB, respectively. What is the total number of disk reads for the block nested loop join? (assume relations do not fit in memory, A is outer).