Creating the Hotel Database
Other
Week3 Assignment1 – Creating the Hotel Database
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.
Create, using SQL data definition language, the Hotel table using the integrity enhancement features of SQL.
Now create the Room, Booking, and Guest tables using the integrity enhancement features of SQL with the following constraints:
(a) Type must be one of Single, Double, or Family.
(b) Price must be between £10 and £100.
(c) roomNo must be between 1 and 100.
(d) dateFrom and dateTo must be greater than today’s date.
(e) The same room cannot be double booked.
(f) The same guest cannot have overlapping bookings.
Deliverables:
Your answers should be 2-3 pages in length, follow the APA format, and free of grammatical and spelling errors.
Once complete, post your paper in a Word Document in the assignment dropbox.
