Re: How can one normalize this table?

In article <1134611329.358990.12250_at_g47g2000cwa.googlegroups.com>, chris65536_at_yahoo.com says...
> I have been struggling with this problem for awhile. There may be an
> obvious answer and I just don't know a lot about database
> normalization. I am trying to design a database for information about
> a company. I want the database to be in BCNF.
>
> The entities I have identified are: employee, office, and position.
> The company has many employees, offices, and positions. An employee
> can work at many offices but hold only one position. For instance,
> let's say employee "John Smith" has the position of "manager". He can
> not have the position of "clerk". He can work at many different
> offices. An office has many positions. For instance an office might
> have the positions: "manager," "clerk," "janitor," "secretary," etc...
>
> So there is a relationship among employee, office, and position. An
> employee has a position at an office. But for a particular position,
> and office can have only one employee. So, for example, two employees
> cannot be the "manager" at the same office. How do I decompose the
> employee-office-position table into equivalent BCNF tables without
> losing any information?

This problem is the exact same as the one in the "Enforcing functional dependency constraints" thread. And it is the classic example of a relvar that is in 3NF, but not in BCNF, and cannot be decomposed without splitting up an FD. Most database textbooks explain it.

> In the employee-office-position table, I think the primary key is
> (office, position) because it uniquely identifies a relationship. But
> employee is a foreign key and position is dependent on employee. So
> maybe (office, employee) is the primary key. But position is dependent
> on employee alone and not office.

Both { office, position } and { office, employee } are keys, plain and simple. "Primary" has no bearing on normalisation.

-- 
Jon