CODEWITHSUNDEEP
databasepostgresqldatabase-designentity-relationshiprelational-model
Andrea Colo
Andrea Colo

Reputation: 37

How to translate a (0,N)(0,N)(0,N)ternary relationship in ER model in this situation?

Suppose as entities: HOMEWORK, STUDENT, ANSWER

and constraints(look carefully constraint 1):

    1)A STUDENT can give only 1 ANSWER for the same HOMEWORK
    2) A HOMEWORK can be solved by (0,N) STUDENT each giving their answer
    3)AN ANSWER can be submitted by (0,N) STUDENT 
    4)(Obviously it is possible to give the same answer
for different HOMEWORK 
and that different STUDENT can give the same answer for the same HOMEWORK)

example:

HOMEWORK STUDENT ANSWER
 XXX       A        1
 XXX       B        1
 XXX       C        2
 YYY       B        1
 YYY       C        1
 ZZZ       A        3
 ZZZ       C        1

NOTE It can't happen that a STUDENT submit 2 solutions for the same homework; so inserting the row XXX A 2 should not be permitted

I would model this with a ternary relationship:

    STUDENT---------(0,N) <DO>(0,N)---------HOMEWORK
                           (0,N)
                            |
                            |
                          ANSWER

But then translating to relational model using usual translation algorithms:

-- -- means FOREIGN KEY 
 _____ means PRIMARY KEY

 DO(HOMEWORK,STUDENT,ANSWER) 
    -- -- -- -- -- -- -- -- 
    _______________________ 
 HOMEWORK(with his attributes)
 STUDENT(with his attributes)
 ANSWER(with his attributes)

Since ANSWER is a part of primary key this means that a student can solve the same homework submitting different answers; this violates the desired constraints.

Probably I would solve this:

1)-transforming the ternary relationship DO in a binary relationship and adding an attribute ANSWER to DO
-then create a trigger to check that the value of answer in DO is a possible answer.

Or 

2)Keep ternary relationship but use another trigger

But I would like to know your opinion.(for example if you would model this problem in a different way in ER ).

PS -I use Postgres

Upvotes: 1

Views: 633

Answers (1)

Renzo
Renzo

Reputation: 27424

If I have understood correctly the specifications, I think your problem can be modeled without using triggers, by simply introducing a constraint on the table that represent the ternary relation.

Let's define it in the following way (the attributes “fkT” stand for foreign key for table T):

ProposedSolution (fkAnswer, fkHomework, fkStudent)
  primary key (fkAnswer, fkHomework, fkStudent),
  unique (fkHomework, fkStudent)

Note that the primary key constraint makes unique the combination of student, answer and homework, while allowing the fact that, for instance, different students can give equal solutions (that is the same answer to the same homework), or that students can give the same answer for different homeworks.

What remain to be enforced is the constraint 1: that is a student cannot give multiple answers to the same homework. But this is solved with the unique constraint, that guarantees that in the table we cannot have two tuples with the same value of student and homework.

Upvotes: 2

Related Questions