- Fact:
- Ternary relationships are (much) more powerful (= stores much more information than any (fixed) number of binary relationships
Example: see click here
- This ternary relatioship:
can store N3 tuples (= information items)
- But these 3 binary relationships:
can only store 3 × N2 tuples (= information items)
- General rule:
- It is generally
not possible to
represent a
ternary relationshp by
extending
one of the
existing
relation
That is because:
- Some attribute that we add to the relation will always be multi-valued
- It is generally
not possible to
represent a
ternary relationshp by
extending
one of the
existing
relation
- Example:
- Suppose we have the following
content for the
ternary relatioship:
I.e.: we must store the following tuples:
(s1, p1, pj1) (s1, p2, pj2) (s2, p1, pj2) (s2, p2, pj1)The possibilities are as follows:
- Extend the
relation (table)
Supplier
(where supplier name is
a key attribute)
We will get the following tuples:
(s1, s1-addr, s1-phone, p1, {pj1, pj2} ) (s2, s2-addr, s2-phone, p2, {pj1, pj2} )Or the following tuples:
(s1, s1-addr, s1-phone, {p1, p2}, pj1 ) (s2, s2-addr, s2-phone, {p1, p2}, pj2 )Both solutions have one multi-valued attribute and is unacceptable
- Extend the
relation (table)
Part
(where Part name is
a key attribute)
We will get the following tuples:
(p1, p1-addr, p1-phone, s1, {pj1, pj2} ) (p2, p2-addr, p2-phone, s2, {pj1, pj2} )Or the following tuples:
(p1, p1-addr, p1-phone, {s1, s2}, pj1 ) (p2, p2-addr, p2-phone, {s1, s2}, pj2 )Again, both solutions have one multi-valued attribute and is unacceptable
- Extend the
relation (table)
Project
(where project name is
a key attribute)
We will get the following tuples:
(pj1, pj1-addr, pj1-phone, s1, {p1, p2} ) (pj2, pj2-addr, pj2-phone, s2, {p1, p2} )Or the following tuples:
(pj1, pj1-addr, pj1-phone, {s1, s2}, p1 ) (pj2, pj2-addr, pj2-phone, {s1, s2}, p2 )Yet again, both solutions have one multi-valued attribute and is unacceptable
- Extend the
relation (table)
Supplier
(where supplier name is
a key attribute)
- Therefore:
- It is not possible to extend an existing relation to accommodate a ternary relationship
- Suppose we have the following
content for the
ternary relatioship:
- How to
represent a
ternary relationship in
a Relational Model:
- You must create a new relation (= table) to represent a ternary relationship
Example: to represent the ternary relationship
Create this relation (table): Supply( sname, pname, pjname ) Sample content: (s1, p1, pj1) (s1, p2, pj2) (s2, p1, pj2) (s2, p2, pj1)