Consider the following schema Sailors(sid, sname, rating, age) Boats(bid, bname, size) Reserves(sid, bid, day) Reserves.sid is a foreign key to Sailors and Reserves.bid is a foreign key to Boats.bid. We are given the following information about the database: Sailors contains 50 pages with 20 records per page, so 1000 records in total. Boats contains 10 pages with 10 records per page, so 100 records in total. Reserves contains 250 pages with 40 records per page, so 10,000 records in total. There are 100 values for Reserves.sid. There are 50 values for Reserves.bid. There are 1000 values for Reserves.day In the following queries, assume that a System R style optimizer is used. Consider Query 1: SELECT S.sid, S.sname, B.bname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND R.day = 'July 4, 2003'; A) Assuming uniform distribution of values and column independence, estimate the number of tuples returned by this query. Consider Query 2: SELECT S.sid, S.sname, B.bname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid B) Draw all possible left-deep query plans for this query: C) List all the one-relation, two-relation, and three-relation subplans that a System-R optimizer will consider in optimizing Query 2 based on the dynamic programming algorithm that we learned in class. There is no need to consider the particular join methods in this question.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

Consider the following schema
Sailors(sid, sname, rating, age)
Boats(bid, bname, size)
Reserves(sid, bid, day)
Reserves.sid is a foreign key to Sailors and Reserves.bid is a foreign key to Boats.bid.
We are given the following information about the database:
Sailors contains 50 pages with 20 records per page, so 1000 records in total.
Boats contains 10 pages with 10 records per page, so 100 records in total.
Reserves contains 250 pages with 40 records per page, so 10,000 records in total.
There are 100 values for Reserves.sid.
There are 50 values for Reserves.bid.
There are 1000 values for Reserves.day
In the following queries, assume that a System R style optimizer is used.
Consider Query 1:
SELECT S.sid, S.sname, B.bname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND R.day = 'July 4, 2003';
A) Assuming uniform distribution of values and column independence, estimate the number of
tuples returned by this query.
Consider Query 2:
SELECT S.sid, S.sname, B.bname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid
B) Draw all possible left-deep query plans for this query:
C) List all the one-relation, two-relation, and three-relation subplans that a System-R
optimizer will consider in optimizing Query 2 based on the dynamic programming algorithm that
we learned in class. There is no need to consider the particular join methods in this question.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions