Customer and Holiday Booking - Essay Example

The Departure time of a Flight is not considered as the take off time of the Flights and we have two relationships between scheduled flight and Airport considering the two airports such as the destination and starting airports we are ignoring the transit airports we assuming the relationship is for the whole journey. Question 2 Relational Schema of the Entity Relationship, 1. Customer (CustomerNo, Address, Title, Surname, Forename, TelNo) Customer information is stored under the entity Customer where his/her personal details such as Address, Title, Surname, Forename, and Tel No are stored and a unique Customer No is given to each Customer to identify them distinctively and is classified as the primary key of this entity.

2. Holiday Booking (BookingId, BookingDate, CustomerNo) A Holiday Booking made by a customer is sorted out into a separate entity where there are various attributes found such as a Booking Date, Booking Id and Total Cost but since derived attributes are not shown in the relational Schema the total cost is not included here. The booking Id is identified as the primary key and Customer No has been identified as the Foreign Key.

3. Hotel Booking (BookingId,HotelId,Check in Date, RoomType, Duration) A Hotel Booking is made as a part of the Holiday Booking and when making a hotel booking the following details are taken into consideration such as the check in date, room type and duration and a Booking Id is given to identify each booking uniquely as well as a hotel id and these 2 keys are taken from 2 separate entities therefore they are considered as composite keys.

4. Hotel (HotelId, BookingId,HotelName, Street, Town, postcode) A particular Hotel is considered as an entity and has many attributes that describe it such as a hotel name and address, there is also a unique hotel Id which is the primary key and a booking id is classified as the foreign key of this entity. 5. Hotel_Tel_No (HotelId, TelNo) The Hotel TelNo has more than one number therefore it is considered as a multi valued Attribute, so a separate relation is created and the hotel Id is identified composite key.

6. Flight Booking (BookingId, FlightCode, Class, BookingDate) The information about Flight Booking is stored under an entity called’FlightBooking’ and this comprises of various attributes such as a BookingId, BookingDate,Class and a FlightCode ,since this entity is a relational Entity the bookingId of Holiday Booking and the FlightCode of the scheduled Flight has been include as Composite Keys.

7. Scheduled Flight (FlightCode, StartingAirport, DestinationAirport, DepartureDate, DepartureTime, FlightDuration, AirportCode*, AirlineNo) A Scheduled Flight has its information stored within an entity called “scheduled Flight” this comprises of many attributes such as a FlightCode,startingAirport, DestinationAirport, DepartureDate, DepartureTime, FlightDuration, AirportCode*, AirlineNo8. Airline (AirlineNo, AirlineName, TelNo, FaxNo) Information of the Airline comprises of many attributes such as an unique Airlineno AirlineName, telno and a fax no.

9. Airport (AirportCode, AirportName, Country, TelNo) Airport comprises of many attributes such an unique Airport code, airportname, country and a telno. 10. Payment ( PaymentNo ,Mode, PaymentDate, BookingId) Payment information is also stored into an entity called “Payment” such as an unique payment no, the mode of Payment, Payment Date and BookingId which is a foreign key.