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.

If I understand the requirements correctly, there is no need for an e-o-p base table. Position can be an attribute in the employee table. Then you need only an employee-office relationship table.

> So if this table is decomposed into three tables: employee-position,

You did indicate that an employee can hold one and only one position, right? Then this is just an employee table. Make sense or did I misinterpret? --dawn Received on Thu Dec 15 2005 - 05:05:58 CET