Home -> Usenet -> comp.databases.theory -> How can one normalize this table?

How can one normalize this table?

From: <chris65536_at_yahoo.com>
Date: 14 Dec 2005 17:48:49 -0800
Message-ID: <1134611329.358990.12250_at_g47g2000cwa.googlegroups.com> 





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?
Received on Thu Dec 15 2005 - 02:48:49 CET












Original text of this message