Re: Principle of Orthogonal Design

On 28 jan, 22:26, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> > "Jan Hidders" <hidd..._at_gmail.com> wrote in message
>
> >news:43bf804b-4ed0-493c-8499-2b3e8a83ee52_at_z17g2000hsg.googlegroups.com...
> > On 28 jan, 15:29, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> > > "Jan Hidders" <hidd..._at_gmail.com> wrote in message
>
> > >news:0a872b75-448e-4131-ba5b-6bcee88da815_at_e10g2000prf.googlegroups.com...
>
> > > > On 28 jan, 02:12, mAsterdam <mAster..._at_vrijdag.org> wrote:
> > > >> Jan Hidders wrote:
> > > >> > mAsterdam wrote something very much like:
> > > >> >> Pragmatical redefinitions must be temporary and tracked.
> > > >> > Sure, we agree on that.
>
> > > >> <unsnip>
>
> > > >> Wether the relation between heading and tuples goes
> > > >> via names or ordering is relevant or not.
>
> > > >> If it is not I want it out of scope.
>
> > > >> </unsnip>
>
> > > > I don't think that it is possible to get it out of scope. If you think
> > > > it is, then by all means provide an equivalent and complete definition
> > > > where it is. I'm also not sure what your problem exactly is. We have a
> > > > definition that works for the named perspective, which is arguably the
> > > > most appropriate for the relational model anyway, so can we now
> > > > please, please, please, pretty please, move on with the discussion?
>
> > > I don't think the definition is sufficient even for the named
> > > perspective:
>
> > Certainly. I think I already said earlier that there is a stronger
> > version that removes even more redundancy. But I wanted to wait a
> > little until mAsterdam had gotten his head around the current one.
>
> > > consider
>
> > > R1 {J, K}
> > > KEY {J}
> > > KEY {K}
>
> > > R2 {J, A}
> > > KEY {J}
> > > FOREIGN KEY {J} REFERENCES R1
>
> > > R3 {K, A}
> > > KEY {K}
> > > FOREIGN KEY {K} REFERENCES R1
>
> > > Supposing that J, K and A have different types and discounting any
> > > meaning
> > > attributed by relation names, there is overlap between R2 and R3.
>
> > > J and K are both keys for R1, so J --> K and K --> J.
>
> > > And due to the foreign keys between R2 and R1 and R3 and R1:
>
> > > from J --> K and K --> A, J --> A can be inferred;
> > > from K --> J and J --> A, K --> A can be inferred.
>
> > You can reason like that about FDs in the context of a single
> > relation, but you seem to do it here at schema level. What exactly
> > does it mean that J --> K holds at schema level? The only way I can
> > make sense of your statements is if you are working under the
> > universal relation assumption. Are you? Or are you perhaps assuming a
> > few extra dependencies you haven't told us about? Dependencies like
> > (R1 NJN R2)[K,A] = R3?
>
> I didn't have the universal relation assumption in mind.  The foreign key
> constraints require that whenever there is a tuple in either R2 or R3, there
> must also be a tuple in R1, so since there must be a tuple in both R2 and R1
> or both R3 and R1, it stands to reason that
>
> whenever R2 then R2 JOIN R1,
> or
> whenever R3 then R3 JOIN R1.
>
> From that it follows that
>
> the FDs from R2 and the FDs from R1 must also hold in R2 JOIN R1
> and
> the FDs from R3 and the FDs from R1 must also hold in R3 JOIN R1
>
> because the foreign keys ensure that
>
> (R2 JOIN R1) {J, A} = R2
> and
> (R3 JOIN R1) {K, A} = R3
That actually follows even if there are no inclusion dependencies / foreign keys. Btw. why did you not mention previously the relations on which the FDs hold? That rather drastically changes their meaning. But so, yes, it follows that K --> A holds on R2 JOIN R1 and J --> A holds on R3 JOIN R1. Why then do you think it follows that if there are tuples {j1, k1} in R1, {j1, a1} in R2, and {k1, a2} in R3, the database is inconsistent? I really don't see the contradiction here. Sure, we now have two A values associated with a J value and a K value, but so what? That certainly doesn't contradict the fact that K --> A holds on R2 JOIN R1 and J --> A holds on R3 JOIN R1.

• Jan Hidders