Suppose we have the following requirements for a travel agency database that is used to keep track of traveller and their trips (a) A travel agency keeps track of each traveler's name (TNAME), traveler number (TNUM), ssn (SSN), birthdate (BDATE), sex (SEX), address (TADDR) and phone (TPHONE) and alternative phone (APHONE), club (CLUB) (can be travelclub, costco_travel, vacations_line, xtrem_trips...), preferred airline(AIRLINE) second preferred airline (SECOND_AIRLINE) (if any), and rewards level (REWARDS) (Diamond, Gold, Silver, Bronze). Both ssn and traveler number have unique values for each traveler. (b) Each vacation plan has a destination name (VPNAME), description (VPDESC), code number (VPNUM), number of days (VPDAYS), type (VPTYPE) (relax, adventure, sport, culture), and one offering airline (VPAIRLINE). The value of code number is unique for each vacation plarn (c) Each airline is described by a name (AIRLINENAME), airline code (AIRLINECODE), airline phone (AIRLINEPHONE), and country (AIRLINECOUNTRY). Both name and code have unique values for each airline (d) Each trip (a trip means one or more traveller that travel together in one of the predefined vacations plan) has a guide (GUIDENAME), month (MONTH) it is possible to have more than one trip per month, year (YEAR), destination (TRIPDESTPLAN), and trip number (TRIPNUM). Trip numbers distinguish different trips based on the same vacation plan during the same month/year; its values are 1, 2, 3,...; up to the number of trips during each month (e) A trip record refers to a traveler (Ssn), in a particular trip and his satisfaction (SATISFACTION) level. Design a relational database schema for this database application. First show all the functional dependencies that should hold among the attributes. Then, design relation schemas for the database that are each in 3NF or BCNF. Specify the key attributes of each relation. Note any unspecified requirements, and make appropriate assumptions to make the specification complete. Suppose we have the following requirements for a travel agency database that is used to keep track of traveller and their trips (a) A travel agency keeps track of each traveler's name (TNAME), traveler number (TNUM), ssn (SSN), birthdate (BDATE), sex (SEX), address (TADDR) and phone (TPHONE) and alternative phone (APHONE), club (CLUB) (can be travelclub, costco_travel, vacations_line, xtrem_trips...), preferred airline(AIRLINE) second preferred airline (SECOND_AIRLINE) (if any), and rewards level (REWARDS) (Diamond, Gold, Silver, Bronze). Both ssn and traveler number have unique values for each traveler. (b) Each vacation plan has a destination name (VPNAME), description (VPDESC), code number (VPNUM), number of days (VPDAYS), type (VPTYPE) (relax, adventure, sport, culture), and one offering airline (VPAIRLINE). The value of code number is unique for each vacation plarn (c) Each airline is described by a name (AIRLINENAME), airline code (AIRLINECODE), airline phone (AIRLINEPHONE), and country (AIRLINECOUNTRY). Both name and code have unique values for each airline (d) Each trip (a trip means one or more traveller that travel together in one of the predefined vacations plan) has a guide (GUIDENAME), month (MONTH) it is possible to have more than one trip per month, year (YEAR), destination (TRIPDESTPLAN), and trip number (TRIPNUM). Trip numbers distinguish different trips based on the same vacation plan during the same month/year; its values are 1, 2, 3,...; up to the number of trips during each month (e) A trip record refers to a traveler (Ssn), in a particular trip and his satisfaction (SATISFACTION) level. Design a relational database schema for this database application. First show all the functional dependencies that should hold among the attributes. Then, design relation schemas for the database that are each in 3NF or BCNF. Specify the key attributes of each relation. Note any unspecified requirements, and make appropriate assumptions to make the specification complete
