0

I'm trying to understand what exactly a fan trap is.

I don't understand why can't you know the account a sales rep handles?

Can't you just join the tables Sales Rep, Branch and Account and find out? What am I missing? enter image description here

Toma Radu-Petrescu
  • 2,152
  • 2
  • 23
  • 57
  • 2
    You can join the three tables, in fact I encourage you to set up an example and do that. What you should see is a cartesian join of the sales reps and accounts of each branch. – reaanb Jun 24 '17 at 09:57
  • 1
    What did your research turn up? PS Please don't use images when you can use [text](https://meta.stackoverflow.com/q/285551/3404097 (Here, DDL.) – philipxy Jun 24 '17 at 13:02

1 Answers1

1

You can join on the tables/relationships of a fan trap. It's only a trap if you think that the resulting table/relationship means something it doesn't. Of course, if you need the table/relationship you think it means then you are missing it and need to add it.

Works_for(rep,branch) -- (rows where) rep *rep* works for branch *branch*
Manages(branch,account) --(rows where) some branch *branch* rep manages account *account*

/* (rows where)
    rep *rep* works for branch *branch*
AND some branch *branch* rep manages account *account*
*/
Works_for NATURAL JOIN Manages

If you think that that join must hold 

/* (rows where)
    rep *rep* works for branch *branch*
AND rep *rep* manages account *account*
*/
Works_for_and_manages(rep, branch, account)

then you have fallen into the trap.

The two join predicates (statement templates) are not equivalent (don't have the same truth value for every row in every situation) when that "fan" pattern of cardinality constraints hold. So their tables don't always hold the same value.

Maybe you can see that they would be equivalent if each branch had only one employee--who must manage all the accounts. But then the cardinalites wouldn't be fan-trappy. Much more likely than that however is that the two predicates aren't equivalent, so you can't construct either predicate/table/relationship from the other, and a better design has Works_for and

Manager(rep, account) -- (rows where) rep *rep* manages account *account*`

Then Manages is SELECT branch, account FROM Works_for NATURAL JOIN Manager. Ie rows where for some *rep*, [rep *rep* works for branch *branch* AND rep *rep* manages account *account*].

(Tables resulting from relation operators have predicates resulting from certain corresponding logic operators. JOIN corresponds to AND, UNION to OR, etc. That is how we can justify that a query expression returns the desired rows.)

philipxy
  • 14,867
  • 6
  • 39
  • 83