Formulate and list functional and multi-valued dependencies- Database Management System
A tour company provides packaged tours, scheduled to depart several times a year. After a tour is scheduled, the company opens the tour for booking and invites tour guides to lead the tours. A scheduled tour can be guided by one or more tour guides, and a tour guide can guide as many tour schedules as long as there is no clash in the dates. Passengers can book any number of tours that have been scheduled.
You are given the TourScheduleGuidePassenger table:
TourScheduleGuidePassenger (tourld, tourName, numDays, itenary, country, cost, departureDate, guideld, dateJoined, guideName, guideGender, guideContact, passengerld, passengerName, passengerGender, passengerAddresss, passengerContact)
Based on the table,
(a) formulate and list the functional and multi-valued dependencies.
(b) propose candidate key(s) for the table.
(c) normalise the table to BCNF and 4NF by applying the BCNF approach. Show how you apply the steps to arrive at the tables and at each iteration, explain whether each derived table is already in BCNF and 4NF (if applicable).
(d) assemble your 4NF tables and constraints clearly.
(i) Give each table in this form:
TABLE-NAME(col-namei, col-name2, col-names)
indicating (PK) against the primary key and indicating (FK) against the foreign keys, if any.
(ii) State the referential integrity constraint(s), if any, together with the correct table in this form:
TABLE-NAMEx.col-name., …col-name. must exist in
TABLE-NAMEy.col-namey,…col-names„
