Answer to Question #13965 in Databases | SQL | Oracle | MS Access for Sajidah

Question

3. Assume the following indexes exist:
• A hash index with no overflow on the primary key attributes, roomNo/hotelNo in Room;
• A clustering index on the foreign key attribute hotelNo in Room;
• A B+-tree index on the price attribute in Room;
• A secondary index on the attribute type in room

 nTuples (Room) = 10,000 bFactor(Room) = 200
 ntuples(Hotel) = 50 bFactor9Hotel) = 40
 nTuples(Booking) = 100,000 bFactor(Booking) = 60
 nDistincthotelNo(Room) = 50
 nDistinctType(Room) =10
 nDistinctprice(Room) = 500
 minprice (Room) = 200 maxprice(Room) = 50
 nLevelshotelNo(I) = 2
 nLevelsprice(I) = 2 nLFBlocksprice(I) = 50
Calculate the cardinality and minimum cost for each of the following selection operations
S1:  roomNo = 1  hotelNo =’H001’(Room)
S2: type’D’ (Room)
S3: hotelNo’H002’(Room)
S4: price > 100 (Room)
S5: type’S’  hotelNo = ‘H003’(Room)
S6: type’S’ price < 100 (Room)

Answer 1

Unfortunately, your question requires a lot of work and cannot be done for free.
Submit it with all requirements as an assignment to our control panel and we'll assist you.