Question

Q) Integrity Constraints question.

Consider following tables (bold are Primary Keys)

•Hotel (hotelNo, hotelName, city)

•Room (roomNo, hotelNo, type, price)

•Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

•Guest (guestNo, guestName, guestAddress)

Identify the foreign keys in this schema, Explain how the entity and referential integrity rules apply to these relations.

Answer 1

Dear student, according to the question, the solution is as follows:

Given the relational tables:

• Hotel (hotelNo, hotelName, city)
• Room (roomNo, hotelNo, type, price)
• Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)
• Guest (guestNo, guestName, guestAddress)

The foreign keys can be identified if at all any attributes depend on the attribute of another relation/table.

1. For the Room table:

The hotelNo in the Room Table is a foreign key reference to hotelNo in the Hotel table.
i.e., We will get the Room in the hotel only when there exists a hotel. If there is no Hotel, How will be the room in the Hotel? Hence, It should be foreign key references to Hotel Table.  

2. For the Booking table:

hotelNo is foreign key references to hotelNo in the Hotel table,
i.e., We will book the Room in the hotel only when there exists a hotel. If there is no Hotel, How we will book the room in the Hotel? Hence, It should be foreign key references to Hotel Table.  

guestNo is foreign key references to guestNo in the Guest table
i.e., if, at all any guest books the room in the hotel, that information will be obtained from the Guest table. Hence, It should be foreign key references to Hotel Table.  

roomNo is foreign key references to roomNo in the Room table
i.e., We will book the Room in the hotel only when there exists a hotel. If there is no Hotel, How we will book the room in the Hotel? Hence, It should be foreign key references to Hotel Table.  

3. For the tables (Hotel and Guest), there are no foreign key references that refer to another table.

Thank you, All the best.