Re: Enforcing functional dependecy constraints
Date: Wed, 07 Dec 2005 23:28:32 GMT
Message-ID: <AeKlf.69379$Eq5.32192_at_pd7tw1no>
David Cressey wrote:
> "x" <x_at_not-exists.org> wrote in message
> news:dn1j2m$fpo$1_at_domitilla.aioe.org...
>
>>Hi ! >> >>Since this is a database theory group and I'm bored by the null and 3vl >>threads I ask yet another elementary question: >> >>Suppose you have this relation R(A,B,C) with the following functional >>dependencies AB->C and C->B. >>What is the best way to implement this in available SQL DBMS in your
>
> opinion
>
>>? >> >>Regards, >>x >>
>
> I'm not sure what you mean by "best". If it's my opinion of "what's best",
> I'm going to dodge the question by giving the universal answer: "It
> depends".
>
> But before we move from relations to SQL, maybe we could discuss a little
> normalization:
>
> You could decompose the relation R(A,B,C) into two relations S(A,C) and
> T(C,B).
David C. put it more simply, but let me meander on as it's good exercise for me.
I just had another go at it, this time figuring out the closure for each of the possible subsets of A, B and C. The only non-trivial ones I came up with were the original two FD's. If we are assuming that only keys will be used to enforce the FD's as written, it might seem that we can't avoid keeping R(A,B,C) and adding T(C,B) unless we somehow throw AB->C out the window.
On the other hand, let me make up some different column names, eg., A becomes Teacher, B becomes Book and C becomes Course.
The original dependencies become 1) Teacher,Book->Course and 2)Course->Book.
From 1) it might seem that a teacher could use multiple books for a course but from 2) no matter the teacher, a course uses only one book, so it seems that C->B is a stricter constraint and David's T(C,B) relation seems to make sense because C could be its key.
Maybe a useful way to look at AB->C is rather that when a teacher uses a particular book, a particular course is implied.
If teachers can teach more than one course, presumably S(A,C) aka S(Teacher,Course) would be all-key and David could teach both algebra and calculus. This doesn't violate the given FD's. Neither would U(A,B) aka U(Teacher,Book).
If we join S(Teacher,Course) and T(Course,Book) we could "lose" some S tuples and some T tuples, but this isn't the same as "losing" tuple-components from R by projecting into S and T or introducing spurious ones by joining again. If we project S and T from R, there can be only one T-tuple for any set of S-tuples that have a unique Course, eg., when a teacher uses a certain book, only one course is implied. If we join T(Course,Book) and U(Teacher,Course) the same seems to be the case.
If R is a "view", there might be S rows that don't participate in R, simply because a teacher hasn't assigned a book to a course or T rows that don't show up because no teacher has been assigned to a course, but that doesn't change the stated FD's for R.
I agree with David but also I guess that the above comes down to saying that BCNF is possible either with S(A,C) and T(C,B) or with U(A,B) and T(C,B), assuming that C is a key.
Cheers,
p
Received on Thu Dec 08 2005 - 00:28:32 CET