Join dependency

Let ${\displaystyle R}$ be a relation schema and let ${\displaystyle R_{1},R_{2},\ldots ,R_{n}}$ be a decomposition of ${\displaystyle R}$.

The relation ${\displaystyle r(R)}$ satisfies the join dependency

${\displaystyle *(R_{1},R_{2},\ldots ,R_{n})}$ if ${\displaystyle \bowtie _{i=1}^{n}\Pi _{R_{i}}(r)=r.}$

A join dependency is trivial if one of the ${\displaystyle R_{i}}$ is ${\displaystyle R}$ itself.[3]

2-ary join dependencies are called multivalued dependency as a historical artifact of the fact that they were studied before the general case. More specifically if U is a set of attributes and R a relation over it, then R satisfies ${\displaystyle X\twoheadrightarrow Y}$ if and only if R satisfies ${\displaystyle *(X\cup Y,X\cup (U-Y)).}$

Given a pizza-chain that models purchases in table Customer = {order-number, customer-name, pizza-name, courier}. The following relations can be derived:

• customer-name depends on order-number
• pizza-name depends on order-number
• courier depends on order-number

Since the relationships are independent there is a join dependency as follows: *((order-number, customer-name), (order-number, pizza-name), (order-number, courier)).

If each customer has his own courier however, there can be a join-dependency like this: *((order-number, customer-name), (order-number, pizza-name), (order-number, courier), (customer-name, courier)), but *((order-number, customer-name, courier), (order-number, pizza-name)) would be valid as well. This makes it obvious that just having a join dependency is not enough to normalize a database scheme.

• Chase (algorithm)
• Universal relation assumption