Re: Constraints and Functional Dependencies

From: paul c <>
Date: Sat, 24 Feb 2007 18:46:21 GMT
Message-ID: <1K%Dh.1130332$R63.232307_at_pd7urf1no>

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.

p Received on Sat Feb 24 2007 - 19:46:21 CET

Original text of this message