The following tables form part of a database held in a relational DBMS:- Hotel (

Question

The following tables form part of a database held in a relational DBMS:-

               Hotel                    (hotelNo, hotelName, city)

               Room                    (roomNo, hotelNo, type, price)

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

               Guest                    (guestNo, guestName, guestAddress)

where    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 the bookings and (hotelNo, guestNo, dateFrom) forms the primary key;

and         Guest contains guest details and guestNo is the primary key.

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

4.9         Produce some sample tables for these relations that observe the relational integrity rules. Suggest some general constraints that would be appropriate for this schema.

Note: When you populate the tables, you need to simulate the real life data. Each table contains at least 10 entries.

Explanation / Answer

4.8)Answer:

For each relation, the primary key must not contain any nulls.

Room is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Room should either be null orcontain the number of an existing hotel in the Hotel relation. In this case study, it would probably be unacceptableto have a hotelNo in Room with a null value.Booking is related to Hotel through the attribute hotelNo. Therefore, the hotelNo in Booking should either be nullor contain the number of an existing hotel in the Hotel relation. However, because hotelNo is also part of theprimary key, a null value for this attribute would be unacceptable. Similarly for guestNo. Booking is also related toRoom through the attribute roomNo.

4.9)Answer:

Student should provide some sample tables, observing entity and referential integrity.

In particular, ensure theuniqueness for the composite primary keys of the Room and Booking tables.Some enterprise constraints may be

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.