Join dependency
In database theory, a join dependency is a constraint on the set of legal relations over a database scheme. A table is subject to a join dependency if can always be recreated by joining multiple tables each having a subset of the attributes of . If one of the tables in the join has all the attributes of the table , 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, because it can be proven that if a scheme is decomposed in tables to , the decomposition will be a
Another way to describe a join dependency is to say that the relationships in the join dependency are independent of each other.
Unlike in the case of
Formal definition
Let be a relation schema and let be a decomposition of .
The relation satisfies the join dependency
- if
A join dependency is trivial if one of the is 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 if and only if R satisfies
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.
See also
References
- .
- ^ ISBN 9780201537710.
- ^ Silberschatz, Korth. Database System Concepts (1st ed.).