Re: Constraints and Functional Dependencies

On Feb 24, 10:46 am, paul c <toledobythe..._at_oohay.ac> 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:
>
> >>> 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?".
>
> 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 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.

Neat example.

I really can't think of a logical reason to require a foreign key to reference a primary key; this example shows a use case outside of that requirement. Although I do think such use cases won't be common. (But that's no reason to forbid them!)

Also, I'm proposing a system in which there's just one kind of costraint, and that requirement seems awfully arbitrary in that light.

Marshall Received on Sun Feb 25 2007 - 02:44:14 CET