CODEWITHSUNDEEP
sql
Shuvendu Roy
Shuvendu Roy

Reputation: 101

SQL Create weak entity set with ternary relationship

Consider the following two entity sets

Employee(ID, name, address)
Dependent(pname, age)

here, Employee is a strong entity set and ID is its primary key. Dependent is weak entity set and pname is its partial key. The entity sets are related via a weak relation policy(amount).

How should i handle the relation Policy? Do I need a separate table to store the relation and the amount attribute or can I include it in Dependent table

Upvotes: 1

Views: 1192

Answers (1)

LAS
LAS

Reputation: 819

You mentioned this is a ternary relationship. Ternary relationships happen when there is a many to many relationship amongst three entities. In this case,


Many Employee --- Many Dependent
Many Employee --- Many Policy     
Many Policy   --- Many Dependent

In order to support this in a relational database, you need to break it up into binary relationships. I'm not sure you truly have a ternary relationship though because you're questioning whether you need a third table. If the relationship were ternary, you'd be dealing with six tables. The three main entities and three intersection tables to break up the many to many relationships.

If your relationship is this:


One Employee --- Many Dependent
One Employee --- One Policy
One Policy   --- Many Dependent

then yes, putting the policy amount in the dependent table rather than create a third table is acceptable depending on your business requirements.

Upvotes: 2

Related Questions