How can one normalize this table?

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?

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.

So if this table is decomposed into three tables: employee-position,
employee-office, and office-position, these table seem to be in BCNF.
But there is no way of enforcing the rule that one position at a
particular office can be filled by only one employee. If someone
inserts "John Smith" as "manager" in to the employee-position table,
and also "Bob Williams" as "manager" too. And then inserts both their
names in the employee-office table for the same office, that rule would
be broken.

I figure the answer is obvious but my knowledge in the area is too
lacking. Can anybody help me?

.

Relevant Pages

  • Re: simple database
    ... Into what component of the database did you enter over 100 ... The company's Employee ... ID number can be a primary key in that table, ... Possessors, and Transfers are the names he is using. ...
    (microsoft.public.access.gettingstarted)
  • Re: simple database
    ... Always when designing a database you need to work out what are the ... employee, employee to employee, or employee to shop) ... PossessorID Primary Key (preferably this should be the Employee ... Each time a tool changes hands, create a record in tblTransfers (easily ...
    (microsoft.public.access.gettingstarted)
  • Re: Bookmark not working (again) in Access 2007
    ... I haven't changed anything in the database. ... I have a database that tracks accidents. ... Each employee in the employee table called, ... A main data entry form called, ...
    (microsoft.public.access.forms)
  • Re: Bookmark not working (again) in Access 2007
    ... If you think there may be corruption in tables, export each table to a format that Access recognizes from your working database -- try XML format since it retains more structure information -- second choice would be Excel, then CSV ... Each employee in the employee table called, ... A main data entry form called, ... I'm now thinking there might have been some kind of corruption with the database. ...
    (microsoft.public.access.forms)
  • Re: Bookmark not working (again) in Access 2007
    ... I haven't changed anything in the database. ... I have a database that tracks accidents. ... Each employee in the employee table called, ... A main data entry form called, ...
    (microsoft.public.access.forms)