Re: How can one normalize this table?

In article <1134956471.117968.30830_at_g47g2000cwa.googlegroups.com>, chris65536_at_yahoo.com says...
>
> > If you do it this way, you'll need a multi-table constraint to implement
> > the FD { p, o } -> { e }.
>
> I'm not sure how to specify a multi-table constraint. I am going to
> have to look into that. I am not actually working with a full-blown
> relational DBMS. I just don't know how to indicate this functional
> dependency without introducing a new table.

My personal favourite solution is two tables: POE(p, o, e) with { p, o } and { o, e } as keys; EP(e, p) with { e } as key; and a foreign superkey (FSK) from POE { e, p } to EP. That will take care of all the FDs. It will have some redundancy, but it will be controlled by the FSK, and you will have the ability to assign a position to an employee without assigning an office.

Alternatively, use just the table POE(p, o, e) with the same keys as above, and enforce e -> p like this (D4 syntax):

create constraint EmployeeDeterminesPosition   not exists (POE rename { p p1, o o1 }

              join (POE rename { p p1, o o2 })
              where p1 <> p2);

Not all DBMSs are capable of that kind of constraint, however, and the FSK solution is probably more time efficient.

-- 
Jon