Re: Principle of Orthogonal Design

> "Jan Hidders" <hidders_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
> > So, if there are tuples {j1, k1} in R1, {j1, a1} in R2, and {k1, a2} in
> > R3,
> > the database is inconsistent due in my opinion to a POOD violation.
> >
> > I think, therefore, that you need to adjust your definitions to reflect
> > the
> > interactions between inclusion dependencies and functional dependencies.
> > I
> > think the closure of F union I is applicable here, where F is the set of
> > all
> > functional dependencies and I is the set of all inclusion dependencies.
>
> That was actually already implicit in my definitions. If I say "there
> is a dependency" it means that either it is specified explicitly or
> logically follows from those that are. But that doesn't really solve
> the problem you identified. For that you need the following
> redefinition of the POOD rule:
>
> DEFINITION: A schema is said to violate POOD if there is a query Q
> over that schema resulting in a relation with header H and in the
> schema a relation R with a minimal join dependency with component C
> such that there is a non-trivial ID Q[H] --> R[C].
>
> To keep this practical Q should probably be restricted to conjunctive
> queries, but in theory it could be any query.
>

I would limit Q to conjunctive queries over which there are inclusion dependencies defined or implied.

> -- Jan Hidders
Received on Mon Jan 28 2008 - 22:26:04 CET