Wikipedia / Join dependency
Wikipedia / Join dependency
In database theory, a join dependency is subject to a join dependency can always be recreated by joining, the join dependency is called trivial.
The join dependency plays an important role in the fifth normal form (5NF), also known as project-join normal form, the decomposition will be a lossless-join decomposition.
Another way to describe a join dependency is to say that the relations in the join dependency are independent of each other.
Unlike in the case of functional dependencies, there is no sound and complete axiomatization for join dependencies, though axiomatization exist for more expressive dependency languages such as full typed dependencies. However, implication of join dependencies is decidable.
Formal definition
.
satisfies the join dependency
itself.
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 if and only if R
Example
Given a pizza-chain that models purchases in table Order = {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.