Assignment No 1
(From your Book)
Due on 13-03-2012, in hard form, before class, in my office
Exercises
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 bookings and (hotelNo, guestNo, dateFrom) forms the primary key;
Guest contains guest details and guestNo is the primary key.
a) Identify the foreign keys in this schema. Explain how the entity and referential integrity
rules apply to these relations.
b) Produce some sample tables for these relations that observe the relational integrity rules.
Suggest some general constraints that would be appropriate for this schema.
c) Analyze the RDBMSs that you are currently using. Determine the support the system
provides for primary keys, alternate keys, foreign keys, relational integrity, and views.
d) Implement the above schema in one of the RDBMSs you currently use. Implement,
where possible, the primary, alternate and foreign keys, and appropriate relational
integrity constraints.
Kindly do it yourself so that you could learn something out of it.
Docsity.com
