Re: Implementing complicated constraints

Brian Dick wrote:
> I didn't see the requirement for globally unique dept.deptno. So,
just add
> the uk(dept.deptno) IN ADDITION TO the pk(deptno, loc). Same for Emp,
too.
> This doesn't change the relationships between the tables or between
the
> columns within a table.
>
> Now we have.
>
> Table LOCATION (loc,
> pk(loc))
>
> Table DEPT ( deptno, loc,
> pk(deptno, loc),
> uk(deptno),
> 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),
> uk(empno),
> fk(deptno, loc) references DEPT,
> fk(loc, nickname) references NICKNAME)

Yes, and that may be fine. But you DO see that it isn't in 3NF, don't you? Because we now have:
1) deptno->loc
2) empno->deptno,loc

i.e. the column emp.loc is only transitively dependent on empno, so would need to be removed to get to 3NF. Imagine the HR department moves from Boston to Dallas - that will involve updates to DEPT and EMP, i.e. an update anomaly.
If you still don't see that, I don't know how else I can express it! Received on Fri Oct 01 2004 - 16:02:43 CDT