-1

What will be the decomposition for this relation in 1NF, 2NF, 3NF and BCNF

Relation - Transfers(destination,departs,airline,gate,name,contact,pickup)

Functional Dependencies -

destination, departs, airline → gate
gate → airline
contact → name
name, departs → gate, pickup
gate, departs → destination

I tried to find the candidate key using closure and found it to be (contact, departs).

Then based on that candidate keys I tried to decompose it into the subsequent normal forms.

2NF – No Partial Dependency

R1(Contact, Name)
R4(Name, Departs, Gate, Pickup, Airline)
R5(Gate, Departs, Destination)

3NF- No transitive dependency & BCNF

R1(Contact, Name)
R2(Name, Departs, Gate, Pickup)
R3(Gate, Airline)
R4(Gate, Departs, Destination)

Is this right or is there a problem here? Because my decomposition for 2NF does not depict the first FD.

philipxy
  • 14,867
  • 6
  • 39
  • 83
John Pollock
  • 11
  • 1
  • Right now you are just asking for us to rewrite your textbook with a bespoke tutorial & do your (home)work & you have shown no research or other effort. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Show the steps of your work following your textbook with justification & ask 1 specific researched non-duplicate question re the first place you are stuck. – philipxy May 04 '20 at 21:27
  • Your "I have these FDs" doesn't make sense. "These are all the FDs that hold"?--Not possible. "These are all the non-trivial FDs that hold"?--Not possible. "These are some FDs that hold"?--Question can't be answered. Find out what a *cover* is & what the exact conditions are to apply a particular definition/rule/algorithm. To determine CKs & NFs we must be given FDs that form a cover. Sometimes a minimal/irreducible cover. And the set of all attributes must given. [See this answer.](https://stackoverflow.com/a/53386492/3404097) – philipxy May 04 '20 at 21:28
  • @philipxy The post has been edited. Can you please have a look? – John Pollock May 05 '20 at 00:28
  • Re "is this right": Show the steps of your work following your reference/textbook, with justification--not all terms/notations are standard & we don't know exactly what algorithm/method you are following & we want to check your work but not redo it & we need your choices when an algorithm allows them & otherwise we can't tell you where you went right or wrong & we don't want to rewrite your textbook. PS Moreover all steps will be SO duplicates. PS "depict (a FD)" is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. – philipxy May 05 '20 at 02:26
  • You haven't acted on all my comments. Most fundamentally, you haven't said those FDs form a cover & you haven't referenced a textbook or definitions or algorithms. You haven't given & justified every step. So what is an answer supposed to be?--"you are just asking for us to rewrite your textbook with a bespoke tutorial". Etc etc. PS Please read the edit help, put 2 spaces at line end for a line break. – philipxy May 05 '20 at 02:41

1 Answers1

1

Assuming that the dependencies are a cover, in both your decompositions the dependency:

airline departs destination → gate

is not preserved. A decomposition in which all the decomposed relations are in 3NF, and that preserves data and dependencies, is:

R1 (airline departs destination gate)
R2 (contact name)
R3 (departs gate name pickup)
R4 (contact departs)

Note that R1 is in 3NF but not in BCNF because of the dependency gate → airline (gate is not a superkey). Note, moreover, that it is known that there are cases in which a decomposition in BCNF does not preserve the dependencies.

Renzo
  • 26,848
  • 5
  • 49
  • 61