# Re: Constraints and Functional Dependencies

From: mAsterdam <mAsterdam_at_vrijdag.org>
Date: Sat, 24 Feb 2007 21:09:57 +0100
Message-ID: <45e09b3b\$0\$334\$e4fe514c_at_news.xs4all.nl>

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?
> 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?

> 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.
Ok, so be it. Let's assume this is required.

> If I
> understand the argument it is that it would be simpler/easier for an
> implementation if the system allowed a foreign key F1
> (StudentId,StaffId) for TutorFor referencing the join of Enrolment and
> TutorFor, eg., the view V={StudentId,StaffId,CourseId} where V is
> constrained by its own foreign key F2 (StaffId,CourseId) referencing
> Teaches. If I've got this right, the schema is in BCNF, but neither F1
> or F2 references a key.

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? Received on Sat Feb 24 2007 - 21:09:57 CET

Original text of this message