Adv Database Systems

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

§  (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.