Representing Ternary Relationship in DBMS:
A ternary relationship is a simultaneous
relationship among instances of three entity types. This type of relationship
has degree three. An example of ternary relationship is shown below, in which
three entities ORDER, CUSTOMER and SALESPERSON are associated with each other.
In most cases, a ternary relationship among ORDER
: CUSTOMER : SALESPERSON is treated as two separate binary relationships.
Suppose an order has a single customer, but a customer can have many orders.
Hence the relationship between CUSTOMER and ORDER is one-to-many. Similarly, an
order has one salesperson and a salesperson has many orders. So the relationship
between SALESPERSON and ORDER is also one-to-many.
There may also be relationship between
SALESPERSON and CUSTOMER. For example, a customer can place orders only with a
particular salesperson. The relationship between SALESPERSON and CUSTOMER is
The above-mentioned relationships can be
represented into relation as:
- Create relations one for each entity type
participating in the ternary relationship.
- Place the key of CUSTOMER in ORDER.
- Place the key of SALESPERSON in ORDER. So
the corresponding relations are:
ORDER (Order-No, Order Date, Promised
Date, Customer-No, Salesperson-No)
CUSTOMER (Customer-No, Address)
SALESPERSON (Salesperson-No, Name, Phone)
Representing Binary Relationship
Representing Unary Relationship
Representing Ternary Relationship