What is the total number of disk reads for the block

Assignment Help Database Management System

Reference no: EM131268747

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).

Reference no: EM131268747

Questions Cloud

How important is creativity to cisco''s success : Why did John Chambers see cross-divisional teams and collaboration as the solution to Cisco's problems?

Will revenue increase or decrease : If the current price of an order of fries is $1.29, will a 10% price decrease cause revenue to increase or decrease?- will revenue increase or decrease?

What are the elements of the risk-assessment paradigm : What are the elements of the risk-assessment paradigm? How can these elements be applied in a workplace setting in which an emergency responder is addressing a large spill of trichloroethylene?

Identify issue and ideas about which they feel most strongly : Identify the issues and ideas about which they feel most strongly. Students will submit the letters online to receive credit for the assignment. (the opinions expressed are not the subject of grading-each student is entitled to his or her own opini..

What is the total number of disk reads for the block : CSE 412: Database Management Fall 2016 Assignment. 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 ..

What price will maximize the revenue from selling hamburgers : What price will maximize the revenue from selling hamburgers?- Currently, the price of a hamburger is $3.00. If the price is increased by 10%, will revenue increase or decrease?

What price will maximize the revenue from selling fries : Currently, the price of an order of fries is $0.99. If the price is decreased by 10%, will revenue increase or decrease?

Explain how might stressors affect safety in the workplace : How might stressors affect safety in the workplace? Give some examples that include stressors that are external as well as internal to the workplace. half page

What are three ways an environmental focus has manifested : What are three ways an environmental focus has manifested itself in architecture? What conflicts are being represented in Figure 38.6? What is a significant impact of computers on the visual arts?

User Account

All Pages