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.
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.
Chapter2: Database Design Fundamentals
Section: Chapter Questions
Problem 11RQ
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 dynamicprogramming algorithm that
we learned in class. There is no need to consider the particular join methods in this question.
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
we learned in class. There is no need to consider the particular join methods in this question.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
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.Recommended textbooks for you
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:
9781111527273
Author:
Philip J. Pratt
Publisher:
Course Technology Ptr