Re: How can one normalize this table?

dawn wrote:
> 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

Rats -- that was homework, wasn't it? In that case, I'll clue you in that many readers here think I'm clueless and clearly I was in this case. --dawn Received on Thu Dec 15 2005 - 05:11:47 CET