Database Design Review the definition of a \"multi-valued dependency\" in the le
ID: 3866715 • Letter: D
Question
Database Design
Review the definition of a "multi-valued dependency" in the lecture notes first. Consider the relation: TRIP(Trip_ID, Trip cities, Trip credit cards used) This relation refers to business trips made by company salespeople. On the same trip, one can visit multiple cities and can use multiple credit cards. (a) Please list the two facts (dependencies) that contribute to a multi-valued dependency in table TRIP. For each one, please state it clearly in the form of (determinant name(s) rightarrow determined attribute name(s)) (b) Please redesign TRIP, so that the improved design conforms to fourth normal form. For each relation in the design, please clearly show the relation name, the attributes, and please underline the primary key attribute(s). Please clearly specify all referential integrity constraints among all these tables. For each one, please specify it in the format of "Foreign key ABC in table T1 references primary key ABC of Table T2" with complete information.Explanation / Answer
The given relation is TRIP: (TripID, TripCities,TripCreditCardsUsed)
As said later that in one trip a salesperson can visit multiple city and can use multiple credit cards then it will give rise to the different anomalies and there will be multivalued dependency for each trip which will be confusing.
Confusions can be like
TripID
TripCities
TripCreditCardsUsed
T1
Delhi
112
T1
Agra
113
T2
Mumbai
112
T2
Agra
112
T1
Kanpur
110
T2
Pune
110
Here it is saying about 2 trips that are done which suits the rule mentioned that one person visit multiple cities and can use multiple cards.
SpecialID
TripID
TripCities
TripCreditCardsUsed
S1
T1
Delhi
112
S2
T1
Agra
113
S3
T2
Mumbai
112
S4
T2
Agra
112
S5
T1
Kanpur
110
S6
T2
Pune
110
Now this table is in 1NF as all fields have unique ID. TRIP: (SpecialID, TripID, TripCities,TripCreditCardsUsed)
Candidate Keys: {SpecialID, TripID}
Now to make it more accurate we will divide the table into 2 like
CITY: (SpecialID, TripID, TripCities) and CARDS (SpecialID, TripID, TripCreditCardsUsed)
With this we can see that the whole table is normalized. Even if we remove the trip ID from the table TRIP we will see a fully normalized table where candidate key of primary key is also the super key. Then table will be like:
SpecialID
TripCities
TripCreditCardsUsed
S1
Delhi
112
S2
Agra
113
S3
Mumbai
112
S4
Agra
112
S5
Kanpur
110
S6
Pune
110
But the problem will be we cannot get back the deserved records for specific trip. So Candidate Keys: {SpecialID, TripID} is the Super key also.
TripID
TripCities
TripCreditCardsUsed
T1
Delhi
112
T1
Agra
113
T2
Mumbai
112
T2
Agra
112
T1
Kanpur
110
T2
Pune
110
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.