1:1 problem

[indigodream]

Hi all,

I'm stuck on an assignment so could do with some help. (Please don't tell me the answer - don't want to cheat. A nudge would be nice though!)

I'm trying to model some requirements. I have an ER model with a 1:1 relationship, mandatory on both sides. Part of the question is to derive a relational model from this ER model, and I'm stuck on how to represent this relationship. In reality the two relations would probably be denormalized into one but my tutor says that's not the answer in this case. Apparently there is 'something missing' from my attempt at the question.

The entity types are:
Staff (StaffNo, Name)
PersonalDocument (StaffNo, Year, RemainingLeave)

PersonalDocument stores the current year and the remaining days leave for the member of staff it relates to. A PersonalDocument 'is identified by the member of staff to whom they refer'. At the moment I've got the relations: PersonalDocument with attributes as above plus StaffNo declared as foreign (not null) and alternate keys. My problem is how to represent the relationship on the Staff side - I've suggested a foreign key or a CHECK constraint, but apparently they're both wrong.

Any advice would be appreciated.

[skhanal]

You only need primary key on Staff table on Staffno. Primary key enforces uniqueness in Staff. But you need a Unique contraint in Personaldocument table on Staffno to maintain 1:1 relation

[indigodream]

You only need primary key on Staff table on Staffno. Primary key enforces uniqueness in Staff. But you need a Unique contraint in Personaldocument table on Staffno to maintain 1:1 relation

But StaffNo is also the primary key in PersonalDocument so is unique.

[skhanal]

The Foreign key should be the answer, but FK is a physical concept, your tutor may be looking for "Identifying Relationship" which is logical model concept.