Question

List all functional dependencies.

(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 plan. (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

Answer 1

By reading above 5 descriptions, let us just for the sake of simplicity rename the attributes and then list all the functional dependencies.

A: TNAME

B: TNUM

C: SSN

D: BDATE

E: SEX

F: TADDR

G: TPHONE

H: APHONE

I: CLUB

J; AIRLINE

K: SECOND_AIRLINE

L: REWARDS

M: VPNAME

N: VPDESC

O: VPNUM

P: VPDAYS

Q: VPTYPE

R: VPAIRLINE

S: AIRLINENAME

T: AIRLINECODE

U: AIRLINEPHONE

V: AIRLINECOUNTRY

W: GUIDENAME

X: MONTH

Y: YEAR

Z: TRIPDESTPLAN

@: TRIPNUM

#: SATISFACTION

Now based on some statements in the above descriptions, we are going to make some functional dependencies:

1. Both SSN and traveler number have unique values for each traveler.

that means with the help of SSN or traveler number (TNUM), we can uniquely identify details of the traveler.

B--->ACDEFGHIJKL

C--->ABDEFGHIJKL

2. The value of code number is unique for each vacation plan.

that means with the help of VPNUM, we can uniquely identify details of each vacation plan.

O--->MNPQR

3. Both name and code have unique values for each airline.

that means with the help of AIRLINENAME or AIRLINECODE, we can uniquely identify the details of the airline.

S--->TUV

T--->SUV

4. The values of trip number, month and year together can uniquely identify the details of a trip.

that means with the help of TRIPNUM, MONTH, YEAR, we can uniquely identify the details of a trip.

@XY--->WZ

5. The values of SSN and TRIPNUM together can uniquely identify SATISFACTION.

C@--->#