Given the following relational schema: HOTEL (hotelNo, hotelName, city) ROOM (roomNo, hotelNo, type, price) BOOKING (hotelNo, guestNo, dateFrom, dateTo, roomNo) The underlined attributes of each relation indicate the primary key of the relation. The italic attributes of a relation indicate the foreign key of the relation referencing to the corresponding attribute in another relation. Hotel contains hotel details and hotelNo is the primary key. Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key. Booking contains details of bookings and (hotelNo, guestNo, dateForm) forms the primary key. Guest contains guest details and guestNo is the primary key Suppose that database statistics of the two relations: nTuples (HOTEL) = 50 bFactor(HOTEL) = 40 nTuples (BOOKING) = 100,000 bFactor (BOOKING) = 60 nTuples (ROOM) = 10,000 bFactor (ROOM) = 200 nDistincthotelNo(ROOM) = 50 nLevelshotelNo(IndexOnROOM) = 2 nDistincttype(ROOM) = 10 nDistinctprice(ROOM) = 500 minValueprice(ROOM) = 50 maxValueprice(ROOM) = 200 nLeafBlocksprice(IndexOnROOM) = 50 nLevelsprice(IndexOnROOM) = 2 Assume that the number of buffer is 100 pages and a disk block is loaded into a page; the following indices are maintained: Hash index with no overflow on primary key attributes, (roomNo, hotelNo) in ROOM Clustering index on the foreign key attribute hotelNo in ROOM B+-tree index on attribute price in ROOM No index on hotelNo in HOTEL since it has been sorted. Calculate the (estimated) cardinality for the following operation: σhotelNo = ‘H02’(ROOM)

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

Given the following relational schema:

HOTEL (hotelNo, hotelName, city)

ROOM (roomNo, hotelNo, type, price)

BOOKING (hotelNo, guestNo, dateFrom, dateTo, roomNo)

The underlined attributes of each relation indicate the primary key of the relation. The italic attributes of a relation indicate the foreign key of the relation referencing to the corresponding attribute in another relation.

Hotel contains hotel details and hotelNo is the primary key.
Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key.
Booking contains details of bookings and (hotelNo, guestNo, dateForm) forms the primary key.
Guest contains guest details and guestNo is the primary key

Suppose that database statistics of the two relations:


nTuples (HOTEL) = 50		bFactor(HOTEL) = 40
nTuples (BOOKING) = 100,000		bFactor (BOOKING) = 60
nTuples (ROOM) = 10,000		bFactor (ROOM) = 200
nDistinct_hotelNo(ROOM) = 50		nLevels_hotelNo(IndexOnROOM) = 2
nDistinct_type(ROOM) = 10
nDistinct_price(ROOM) = 500
minValue_price(ROOM) = 50		maxValue_price(ROOM) = 200
nLeafBlocks_price(IndexOnROOM) = 50		nLevels_price(IndexOnROOM) = 2

Assume that the number of buffer is 100 pages and a disk block is loaded into a page; the following indices are maintained:

Hash index with no overflow on primary key attributes, (roomNo, hotelNo) in ROOM
Clustering index on the foreign key attribute hotelNo in ROOM
B⁺-tree index on attribute price in ROOM
No index on hotelNo in HOTEL since it has been sorted.

Calculate the (estimated) cardinality for the following operation: σ_{hotelNo = ‘H02’}(ROOM)

Expert Solution