Re: Implementing complicated constraints

From: Brian Dick <bdick_at_cox.net>
Date: Fri, 1 Oct 2004 14:42:33 -0400
Message-ID: <puraye2kajza.bcb4yeze03zd.dlg@40tude.net>

On 1 Oct 2004 02:12:59 -0700, Tony Andrews wrote:

> 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!

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)

Received on Fri Oct 01 2004 - 13:42:33 CDT