Based on the following database schemas: Homestay (homestayNo (PK), homestayName, address, telNo, city) Room (roomNo, homestayNo (PK), type, price) Booking (homestayNo, guestNo (PK), dateFrom, dateTo, roomNo) Guest (guestNo (PK), guestName, guestAddress, guestTelNo) Assume the following indexes exist: a) a hash index with no overflow on the roomNo and homestayNo as a composite primary key in Room; b) a clustering index on the foreign key attributes homestayNo in Room; c) a B+ -tree index on the price attribute in Room; d) a secondary index on the attribute type in Room. nTuples(Room) = 20000 bFactor(Room) = 200 nTuples(Homestay) = 100 bFactor(Homestay) = 40 nTuples(Booking) = 100000 bFactor(Booking) = 60 nDistincthomestayNo(Room) = 50 nDistinctroomNo(Booking) = 150 nDistincttype(Room) = 10 nDistincthomestayName(Homestay) = 50 nDistinctprice(Room) = 500 maxprice(Room) = 500 minprice(Room) = 200 nLevelstype(I) = 2 nLevelshomestayNo(I) = 2 nLfBlocksprice(I) = 50 nLevelsprice(I) = 2 Log2100 = 6.64 Log250 = 5.64 Calculate the SELECTION CARDINALITY and ESTIMATED COST for: (a) Linear search on the non-key attributes homestayName in Homestay?
Based on the following
Homestay (homestayNo (PK), homestayName, address, telNo, city)
Room (roomNo, homestayNo (PK), type, price)
Booking (homestayNo, guestNo (PK), dateFrom, dateTo, roomNo)
Guest (guestNo (PK), guestName, guestAddress, guestTelNo)
Assume the following indexes exist:
a) a hash index with no overflow on the roomNo and homestayNo as a composite
primary key in Room;
b) a clustering index on the foreign key attributes homestayNo in Room;
c) a B+
-tree index on the price attribute in Room;
d) a secondary index on the attribute type in Room.
nTuples(Room) = 20000 bFactor(Room) = 200
nTuples(Homestay) = 100 bFactor(Homestay) = 40
nTuples(Booking) = 100000 bFactor(Booking) = 60
nDistincthomestayNo(Room) = 50 nDistinctroomNo(Booking) = 150
nDistincttype(Room) = 10 nDistincthomestayName(Homestay) = 50
nDistinctprice(Room) = 500 maxprice(Room) = 500
minprice(Room) = 200 nLevelstype(I) = 2
nLevelshomestayNo(I) = 2 nLfBlocksprice(I) = 50
nLevelsprice(I) = 2 Log2100 = 6.64
Log250 = 5.64
Calculate the SELECTION CARDINALITY and ESTIMATED COST for:
(a) Linear search on the non-key attributes homestayName in Homestay?
(b) Selection: σtype=‘Flat’ ⋀ price < 200 (Room)
(c) Join: Room ⋈ roomNo Booking
(Using Block Nested Loop Join strategy, where nBuffer = 200 and nBuffer-2 blocks
for R)
Step by step
Solved in 2 steps with 1 images
