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

Solution:

Only 4.8 is solved because the statement in 4.9 is not quite clear please clarify in comments and I will edit the answer.

The question about 4.9 is, do you want to write a query to populate the table? or you want a schema different than the given which will satisfy the referential integrity constraints?

4.8)

The tables are given below:

Hotel                    (hotelNo, hotelName, city), No foreign key

Room                    (roomNo, hotelNo, type, price), hotelNo is the foreign key here

Booking               (hotelNo, guestNo, dateFrom, dateTo, roomNo), hotelNo and guest No is the foreign key here

Guest                    (guestNo, guestName, guestAddress), No foreign key here.

Referential integrity constraints like on delete set null to apply here.

sp is on delete set cascade.

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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