by
Hello out there,
I hope somebody can help me to find a solution to following problem:
Assume we have following entity-relationship-diagram:
--------
|person|
--------
|
/ \ (1,1)
/ \
------------
| |
------ -------
|man | |woman|
------ -------
As a result we have 3 tables:
* person with the primary key p# and the attributes which have man and
woman in common
* man and woman which have each p# as primary key and as foreign key to
person too, and the
attributes which are specific for man and woman
The ER-diagramm tells us also that:
* a person can only be a man OR a woman. It can't be a man and a woman
simultaneously.
-> disjointness-constraint
* a person MUST be a man or a woman. It is forbidden that lines in the
person table exists
which are not referenced by a line in the man- or the woman table.
-> completeness constraint
The implementation of the disjointness constraint is easy. We just have
to create two triggers, one for the man and one for the woman table
which are fired by an insert in the corresponding table. The triggers
has to look if the primary key still exists in the other table and if
yes to rollback the transaction.
In contrast the implementation of the completeness constraint is much
more difficult. In theory the insert operation into the person-table and
i.e. the man table should be one (atomic) operation. But the insert
statement in SQL doesn't support an insert into two table
simultaneously. So a user has two make two separate inserts. But nobody
can guarantee the he or she makes the second. So it could be, that we
have a line in person which is never referenced by a line in man or
woman - in fact a person with no sex. My first idea to fix the problem
was to write a stored procedure. But I can't force the user to call that
procedure instead of making the inserts manual. My second idea was to
create a view which results of an inner join of the person and i.e. the
man table over the primary key. Next I could prevent the user to insert
directly into the person table with the grant command and as a result
the user can only insert over the view were he or she must use a
complete list of all attributes (those of person and those of man). But
usually views which results of a join operation can't be updated. So I
had to revoke that idea too. My last one was to add a (boolean) field to
the person table which determines the sex of the person (i.e. 'M' and
'F'). Then an insert trigger on the person table inspect the boolean
value and make the corresponding insert operation in the man or in the
woman table. The disadvantage of that method is that all
man-specific-attributes can only be set to their default values or to
NULL. But the completeness would be guaranteed. A second disadvantage is
that if we have more than two sub-entity-typs the insert trigger must
implement a huge and complex switch-case-statement. And the user must
make an update statement to set the sub-type specific attributes.
I know that this example could be done much more easier if we include
all man and woman specific attributes in the person table. But in real
life that may be impossible for example if we have the super-typ
business contact and customer and supplier with lots of specific
attributes as sub-types.
I hope some of you can help me.
Greetings
Dominik