Query (1) requires 10,101,000 disk accesses, Query (2) requires 500,500, and Query (3) varies based on the database optimizer. With 10,000 tuples in Staff, 500 in Branch, 500 Managers, and 10 London branches.
To calculate the cost for the three strategies in Example 23.1 with the updated tuple and branch counts, we can use the same approach.
Let's substitute the new values into the calculations:
1. Query (1):
disk accesses
2. Query (2):
disk accesses
3. Query (3): The cost depends on the efficiency of the query optimizer and execution plan chosen by the database system. The actual disk accesses would vary based on the specific implementation and optimization strategies used by the database engine.
In this scenario, Query (2) is likely to be the most efficient, as it directly applies the selection conditions before performing any join operations. However, Query (3) could potentially be optimized by a sophisticated query planner.
The complete question is:
Calculate the cost of the three strategies cited in Example 23.1 if the Staff relation has 10 000 tuples, Branch has 500 tuples, there are 500 Managers (one for each branch), and there are 10 London branches.
The example has been attached.