Brian Dick wrote:
> 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)
It looks normalized because you have removed the functional dependency
dept.deptno->dept.loc. But if the business rule is that dept.deptno
values must be unique company-wide, not just within location, then you
need to reinstate uk(dept.deptno), which restores the FD and then makes
your emp table denormalised again, because it contains emp.loc which is
trasitively dependent on emp.deptno!
Received on Fri Oct 01 2004 - 04:12:59 CDT
