Join dependency
Encyclopedia
A join dependency is a constraint on the set of legal relations over a database scheme. A table T is subject to a join dependency
if T can always be recreated by joining multiple tables each having a subset of the attributes of T. If one of the tables in the join has all the attributes of the table T, the join dependency is called trivial.
The join dependency plays an important role in the Fifth normal form
, also known as project-join normal form, because it can be proven that if you decompose a scheme
in tables 
to 
, the decomposition will be a lossless-join decomposition if you restrict the legal relations on 
to a join dependency on 
called 
.
Another way to describe a join dependency is to say that the set of relationships in the join dependency is independent of each other.
It is obvious that you can derive the following relations:
Since the relationships are independent you can say there is a join dependency as follows: *((order-number, customer-name), (order-number, pizza-name), (order-number,delivery-boy)).
If each customer has his own delivery-boy however, you could have a join-dependency like this: *((order-number, customer-name), (order-number, delivery-boy), (customer-name, delivery-boy), (order-number,pizza-name)),
but *((order-number, customer-name, delivery-boy), (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.
Dependency theory (database theory)
Dependency theory is a subfield of database theory which studies implication and optimization problems related to logical constraints, commonly called dependencies, on databases....
if T can always be recreated by joining multiple tables each having a subset of the attributes of T. If one of the tables in the join has all the attributes of the table T, the join dependency is called trivial.
The join dependency plays an important role in the Fifth normal form
Fifth normal form
Fifth normal form , also known as Project-join normal form is a level of database normalization designed to reduce redundancy in relational databases recording multi-valued facts by isolating semantically related multiple relationships...
, also known as project-join normal form, because it can be proven that if you decompose a scheme
in tables to , the decomposition will be a lossless-join decomposition if you restrict the legal relations on to a join dependency on called .Another way to describe a join dependency is to say that the set of relationships in the join dependency is independent of each other.
Formal definition
Letbe a relation schema and let
be a decomposition of
.
The relationsatisfies the join dependency
if
.
A join dependency is trivial if one of theis
itself.
Example
Given a pizza-chain that models purchases in table Customer = { order-number, customer-name, pizza-name, delivery-boy }.It is obvious that you can derive the following relations:
- customer-name depends on order-number
- pizza-name depends on order-number
- delivery-boy depends on order-number
Since the relationships are independent you can say there is a join dependency as follows: *((order-number, customer-name), (order-number, pizza-name), (order-number,delivery-boy)).
If each customer has his own delivery-boy however, you could have a join-dependency like this: *((order-number, customer-name), (order-number, delivery-boy), (customer-name, delivery-boy), (order-number,pizza-name)),
but *((order-number, customer-name, delivery-boy), (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.