16.11 Design a query plan for the join example in Section
16.3.1, assuming the same table sizes as in that section, but with the
following differences:
A. An application at site B requested the
join.
1)
Send Student (Id, Major) from site B to site C,
Do natural JOIN with Transcript ( StudID, CrsCode) at Site C, Send it back to
Site B
§
B
-> C | (natural
JOIN ) at C | C -> B
§
(15,000 * 12)
+ ( 20,000 * 18) = 540,000
bytes
2)
Send Transcript ( StudID, CrsCode) from Site C
to Site B and perform natural join at Site B.
§
C -> B | (natural JOIN ) at B
C -> B | (natural JOIN ) at B
§
(20,000 * 15) = 300,000
bytes
Option 2 is
best.
B. An application at site C requested the
join.
1)
Send Transcript ( StudID, CrsCode) from site C
to site B, Do natural JOIN with Student (Id, Major) at Site B, Send it back to
Site C.
§
C -> B | (natural JOIN ) at B | B -> C
§
( 20,000 * 15) + (20,000 * 18) = 660,000 bytes
2)
Send Student (Id, Major) from Site B to Site C
and perform natural join at Site C.
§
B -> C
| (natural JOIN ) at C
§
(15,000 * 12) = 180,000
bytes
Option 2 is
best.
17.7 Suppose that an application has four dimension tables,
each of which contains 100 rows.
a.
Determine the maximum number of rows in the fact
table.
ü As we have
100 rows in each of the four Dimension tables,
The fact table must have 100 * 100 * 100 * 100 tuples in total.
Thus Maximum number of rows in the fact table should be 100,000,000
tupes.
b.
Suppose that a one-dimension table has an
attribute that can take on 10 values. Determine the size in bytes of a bit
index on that attribute.
ü I think the answer to this question is not
specific,
According to the question the size of the index on that attribute depends
on the DATA Type + Number of Tuples ( 10 in this case ) + the maximum size of
the attribute.
For e.g. if the attribute is INTEGER(10) that has 10 values,
So size would be -> 4 byte * 10 * 10 = 400 bytes
E.G.2
If the attribute is FLOAT(10) that has 10 values,
Size
will be -> 8 byte * 10 * 10 = 800 bytes
E.G
3 If the attribute is VARCHAR(10) that has 10 values,
Size
will be -> 2 bytes * 10 * 10 = 200 bytes
So there could be multiple answers
for this question, according to what one has supposed in their table
c.
Determine the maximum number of tuples in a join
index for a join between one of the dimension tables and the fact table.
ü As I have
determined in 17.7 (a) that my Fact table consists maximum of 100,000,000
tuples.
üIf I make a
join between one of the dimension tables with the fact table,
TOTAL TUPLES will obviously 100,000,000 only.
