Database Management System Enhanced Entity Relationship

Question :

Design, using the notation of the slides, an Enhanced Entity Relationship (EER) diagram.

Answer :

The assumptions for the design of the Enhanced Entity Relationship diagram and the database system for the Hotel system are taken with the following points.

• Each entity must have the key attributes either primary, alternate and derived.

• The derived attributes should to referenced key attribute from the related entity

• Strong entity must have the primary key attribute

• Week Entity must have the derived attribute which must be key for week entity

• Booking entity must be included n conceptual model of database

• Booking should be strong entity and bookingID should be primary key

• The derived or referential integrity of Booking is RoomNo which is derived from Room Entity

This entity is very important in context of the hotels to perform the functional aspects of the hotel bookings from the customers. The booking of the rooms of the hotel gives the allocation of the room to the customer in database to further find out the booked and vacant rooms with detailed information check in and check out times. The common attributes for the booking entity are presented as follows.

Above transformation of the relations of Booking database system is lossless and dependency preserving. The justification for lossless and dependency preserving decomposition for the Booking are given below.

1. Union of all the attributes of relations Passenger, Flight, and Booking must be equal to attributes of Booking.

Above shows that all the attributes of Booking are preserved after decomposition, so that it can be said that the decomposition is lossless and dependency preserving.

The cross product is very expensive because it matches each record of E1 with each of the record of E2. Let E1 has m records and E2 has n records then total entries would be m x n. if the selection operation is applied then we scan through m x n entries to find out the suitable which satisfies the condition ɵ. Therefore, it is optimal to use ɵ join which selects only those entries in the cross product that satisfies the theta condition without evaluating the entries cross product first.

Here applying ɵ1 intersection ɵ2 is very expensive. Therefore, filtering out the records satisfying condition ɵ2 and then applying the condition ɵ1 as outer selection which results fewer records. This reduce the process time so that it can be extended to two or more selections.

Finally, the optimized query is represented by following query tree.