Re: Implementing complicated constraints

"Tony Andrews" <andrewst_at_onetel.com> wrote in message news:1096561764.439453.281480_at_k17g2000odb.googlegroups.com...
> Brian Dick wrote:
>> Try this (again). This is Mark's solution with an additional table.
>>
>> Table C(CID,
>> pk(CID))
>>
>> Table B(BID, CID,
>> pk(BID, CID),
>> fk(CID ref C(CID)))
>>
>> Table T(CID, ATXT,
>> pk(CID, ATXT),
>> fk(CID ref C(CID))
>>
>> Table A(AID, BID, CID, ATXT,
>> pk(AID, BID, CID),
>> fk(BID, CID) ref B(BID, CID),
>> fk(CID, ATXT) ref T(CID, ATXT))
>
> Yes, that seems to do it. However, we are still sacraficing
> normalisation, are we not? We used to have the FD BID->CID, and now we
> don't. I'm not complaining, since we have a problem to resolve here
> whichever we way do it.
>
> BTW, I find that all these As, Bs and Cs make my head spin, so I have
> reformulated the problem in terms of the familiar EMP and DEPT tables,
> plus a new tables LOCATION and NICKNAME. Each employee is given a
> nickname that is unique within his location - i.e. there can be a
> "Jug-ears" in Boston and in Dallas. So we have:
>
> Table LOCATION (loc,
> pk(loc))
> Table DEPT ( deptno, loc,
> pk(deptno),
> uk(deptno,loc))
> Table NICKNAME( loc, nickname,
> pk(loc,nickname))
> Table EMP( empno, ename, deptno, loc, nickname
> pk(empno),
> fk(deptno,loc) references DEPT(deptno,loc),
> fk(loc,nickname)) references NICKNAME(loc,nickname))
>
> This corresponds to the design above, more or less, with LOCATION=C,
> DEPT=B, NICKNAME=T, EMP=A.
>
> Note that EMP.LOC is a deliberate denormalisation. However, having
> done that, we no longer need the NICKNAME table:
>
> Table LOCATION (loc,
> pk(loc))
> Table DEPT ( deptno, loc,
> pk(deptno),
> uk(deptno,loc))
> Table EMP( empno, ename, deptno, loc, nickname
> pk(empno),
> fk(deptno,loc) references DEPT(deptno,loc),
> uk(loc,nickname))
>
> If I insist on normalising by removing emp.loc (which can be inferred
> from emp.deptno) then I cannot enforce uk(loc,nickname) via *the kind
> of constraints Oracle has*. I would need the more powerful ANSI
> cross-table constraint syntax; or I would have to resort to one of the
> work-arounds discussed elsewhere in this thread.
>
> Conclusion: you can have full normalisation or you can have
> all-declarative constraints - but you can't have both!
>

We go out of sync somewhere. Here's my translation. Looks normalized to me.

 Table LOCATION (loc,

                                 pk(loc))

 Table DEPT ( deptno, loc,
                      pk(deptno, loc),
                      fk(loc) references LOCATION)

Table NICKNAME( loc, nickname,
                                 pk(loc,nickname)
                                 fk(loc) references LOCATION)

 Table EMP( empno, deptno, loc, nickname
                     pk(empno, deptno, loc),
                     fk(deptno, loc) references DEPT,
                     fk(loc, nickname) references NICKNAME)