Re: Modelling Disjoint Subtypes
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 22 Mar 2007 21:20:32 GMT
Message-ID: <AqCMh.13819$PV3.141850_at_ursa-nb00s0.nbnet.nb.ca>
>> Joe Thurbon wrote:
>>
>>> I have (another) question about examples in Pascal's "Practical
>>> Issues in Database Management" book. This time it is about modelling
>>> disjoint entity subtypes.
>>>
>>> The example given in the book (section 6.2.2 for those who have it)
>>> is of an "employee" entity with a subtype commissioned employee. The
>>> relational representation for this example is
>>>
>>> Employee: EMP# Name HireDate Salary
>>> ====---------------------------
>>> 1 Smith 1/1/2001 16150
>>> 2 Jones 1/1/2002 14350
>>>
>>>
>>> Commissioned_Employee: EMP# Commission
>>> ====-------------
>>> 1 5000
>>>
>>> There is also the natural generalisation to allowing employees to be
>>> salary-only, commission-only, or both.
>>>
>>>
>>> Employee: EMP# Name HireDate
>>> ====
>>>
>>> Commissioned: EMP# Commission
>>> ====
>>>
>>> Salaried: EMP# Salary
>>> ====
>>>
>>> An exercise left to the reader (why is it always the one I'm
>>> interested in that's left to the reader!?) is to model the case
>>> whereby employees can be either salaried, commissioned, but _not_
>>> both. That is, each of the subtypes is disjoint.
>>>
>>> I can think of perhaps three ways to do this, but none seem to be as
>>> elegant as the above examples, and so I'm wondering if I'm missing
>>> something simple.
>>>
>>> The first approach I can think of uses some sort of
>>> type-discriminator, in the employee table. This seems fraught with
>>> difficulties: how do you select the table to join on based on the
>>> type discriminator other than using multiple queries? How do you
>>> prevent nonsense data appearing, for example, in the 'commissiones'
>>> table for an employee whos type discriminator is is 'salaries'. So
>>> I'm guessing that a type discriminator does not work.
>>>
>>> The second approach is to have only one sub-type, but have two
>>> columns "Type", "Amount" whereby type gives a clue as to how to
>>> interpret the ammount (i.e. is it salary or commission). This seems
>>> problematic, since , in some sense, the domain of commissions is
>>> different to the domain of salaries (at least, according to my
>>> understanding of chapter 1 of "Practical Issues..."
>>>
>>> The other approach requires some sort of check constraint that
>>> simulates a kind of 'distributed key' (c.f. Darwen's 'Final Null In
>>> Th Coffin'). I can't really work out how this would be done
>>> relationally.
>>>
>>> So I'm stuck. Any hints or clues?
>>
>>
>> What if you had an attribute that was a constant? How might that
>> change your analysis above?
Date: Thu, 22 Mar 2007 21:20:32 GMT
Message-ID: <AqCMh.13819$PV3.141850_at_ursa-nb00s0.nbnet.nb.ca>
Joe Thurbon wrote:
> Bob Badour wrote: >
>> Joe Thurbon wrote:
>>
>>> I have (another) question about examples in Pascal's "Practical
>>> Issues in Database Management" book. This time it is about modelling
>>> disjoint entity subtypes.
>>>
>>> The example given in the book (section 6.2.2 for those who have it)
>>> is of an "employee" entity with a subtype commissioned employee. The
>>> relational representation for this example is
>>>
>>> Employee: EMP# Name HireDate Salary
>>> ====---------------------------
>>> 1 Smith 1/1/2001 16150
>>> 2 Jones 1/1/2002 14350
>>>
>>>
>>> Commissioned_Employee: EMP# Commission
>>> ====-------------
>>> 1 5000
>>>
>>> There is also the natural generalisation to allowing employees to be
>>> salary-only, commission-only, or both.
>>>
>>>
>>> Employee: EMP# Name HireDate
>>> ====
>>>
>>> Commissioned: EMP# Commission
>>> ====
>>>
>>> Salaried: EMP# Salary
>>> ====
>>>
>>> An exercise left to the reader (why is it always the one I'm
>>> interested in that's left to the reader!?) is to model the case
>>> whereby employees can be either salaried, commissioned, but _not_
>>> both. That is, each of the subtypes is disjoint.
>>>
>>> I can think of perhaps three ways to do this, but none seem to be as
>>> elegant as the above examples, and so I'm wondering if I'm missing
>>> something simple.
>>>
>>> The first approach I can think of uses some sort of
>>> type-discriminator, in the employee table. This seems fraught with
>>> difficulties: how do you select the table to join on based on the
>>> type discriminator other than using multiple queries? How do you
>>> prevent nonsense data appearing, for example, in the 'commissiones'
>>> table for an employee whos type discriminator is is 'salaries'. So
>>> I'm guessing that a type discriminator does not work.
>>>
>>> The second approach is to have only one sub-type, but have two
>>> columns "Type", "Amount" whereby type gives a clue as to how to
>>> interpret the ammount (i.e. is it salary or commission). This seems
>>> problematic, since , in some sense, the domain of commissions is
>>> different to the domain of salaries (at least, according to my
>>> understanding of chapter 1 of "Practical Issues..."
>>>
>>> The other approach requires some sort of check constraint that
>>> simulates a kind of 'distributed key' (c.f. Darwen's 'Final Null In
>>> Th Coffin'). I can't really work out how this would be done
>>> relationally.
>>>
>>> So I'm stuck. Any hints or clues?
>>
>>
>> What if you had an attribute that was a constant? How might that
>> change your analysis above?
> > > I'm afraid I've found this clue (hint?) a little too cryptic. > > It spawned a few thoughts. For example, the second approach above might > be changed to > > EMP# Salary SalaryFactor Commission CommisssionFactor > ==== > > Where SalaryFactor and CommissionFactor were either 0 or 1, and the > employees total remuneration was a linear equation. But that seemed, um, > fanciful. (Not to mention that it takes a very liberal interpretation of > your clue, and doesn't work in the general case). > > I can't see how to apply your hint to either of the other approaches. In > approach one, you need at least as many values as there are sub-types. > In approach three, the desire is to have a constraint which ensures that > for a given key, that key appears only in one of the subtype tables, and > I don't see how a constant attribute helps there. I guess you could use > the constant attribute as some sort of token to indicate which sub-type > is the 'active' one, but I don't see how that is different to approach > three. > > Am I just barking up the wrong tree? Would you like to tell me the answer?
You are barking up the wrong tree. If at all possible, the only constraints one should have are foreign key references. What exactly prevents one from using a foreign key constraint to enforce the disjoint requirement? Received on Thu Mar 22 2007 - 22:20:32 CET