Re: How can one normalize this table?

chris65536_at_yahoo.com wrote:
> 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?
>
I think this is an example of n-decomposable join dependency, recognizable by the presence of cyclic constraints. The employee-office-position "table" could be nonloss decomposed only if the constraint employee->office->position didn't exist. But it does, so the design needs an "all-key table."

(I'm not sure I understand all of your constraints; it's not clear whether an employee can fill different positions in different offices.) Received on Thu Dec 15 2005 - 05:10:05 CET