Re: Constraints and Functional Dependencies

From: paul c <toledobythesea_at_oohay.ac>
Date: Sat, 24 Feb 2007 20:41:10 GMT
Message-ID: <Gp1Eh.1113057$1T2.520608@pd7urf2no>

mAsterdam wrote:

> paul c wrote:
> 

>> mAsterdam wrote:
>>
>>> paul c wrote:
>>>
>>>> Marshall wrote:
>>>>
>>>>> ...
>>>>> With such a system, a relation R with attribute a (which I will
>>>>> write as R(a)) having a as a foreign key into S(b) is expressed
>>>>> as follows:
>>>>>

> (i)
> 


>>>>>   forall R(a): exists S(b): a = b

>>>>>

>>>>> So we can express foreign keys this way.

>>>>> ...

>>>>

>>>>

>>>> I presume that if S had other attributes besides b, this definition 

>>>> would mean that b doesn't need to be a so-called primary key?  (That 

>>>> would be okay with me.)

>>>

>>>

>>>

>>> Not sure if I get this.

>>>

>>> Try:

>>>
>>> b should be a (candidate) key of S, ...
>>
>>
>> My question is why? Why should b be a key of S? You could answer "why
>> not?" and I don't have a theoretical answer for that, other than the
>> possibility, as Marshall hinted at, that requiring it to be a key
>> isn't possible except on a relation-by-relation basis unless we just
>> arbitrarily state it is always so, in which case my question remains
>> "why?".

> 
> 
> Because (i) should, as Marshall stated, express
> the notion of foreign key, specifically a foreign key to S.
> I b is not a key of S, I don't see how it can.
> 
> Cimode even gave a proof that it can't.
> Don't you agree?
> Is the proof flawed?
> ...

I don't know where to look for Cimode's proof but I think what Marshall defined is what I call a "reference", which seems more general than Codd's original foreign key, ie., doesn't exclude the possibility of the reference being a key in the referenced table, if the person who declares the reference so desires. I take it that you want to read "foreign key" literally and insist that the reference involve a key defined for example, the way Marshall defined a key. If I read you right, that's okay by me but I think "reference" encompasses "foreign key" in a way that never diminishes any table a designer who wants to be literal declares at the same as it allows the rest of the table definers much more leeway.

The term "key" is a bastard in the first place. It seems to have been inherited from the times when hardware recognized keys by their physical position.

>> The only argument I've seen for "why not" is also not a theoretical
>> one. It was based on the practical question of whether it would be
>> easier for an implementation/optimizer to maximize its use of "foreign
>> keys" as a shorthand for more lengthy FOL/constraints. I have an
>> example that Hugh Darwen gave, with a name change that I don't think
>> alters his point, apologies to him if I'm mis-stating it :
>>
>> Student { StudentId, Name } KEY { StudentId }
>> Course { CourseId, Title } KEY { CourseId }
>> Staff { StaffId, Name } KEY { StaffId }
>>
>> Teaches { StaffId, CourseId } KEY { StaffId }
>>
>> (note that a teacher may teach only one course, the argument depends
>> on this)
>>
>> Enrolment { StudentId, CourseId } KEY { StudentId, CourseId }
>>
>> (note also that a student may take many courses and that a student may
>> enrol in a course before a teacher is assigned to it)
>>
>> TutorFor { StudentId, StaffId } KEY { StudentId, StaffId }
>>
>> It's possible to enter a row in TutorFor where StaffID stands for a
>> teacher who doesn't teach any course the student is enrolled in.

> 
> 
> If it is a base table, yes. That seems a little strange though,
> by way of example - what would a row mean beyond
> what is already in the other tables?
> Maybe I'm reading to much in the names. ...

I think nothing at all should be read into the names.

> I think I do understand the need/wish to be able to have
> this kind of constraint.
> It would be stretching the concept of foreign key, no?

As I suggested, I think that point of view is overly literal.

p Received on Sat Feb 24 2007 - 14:41:10 CST