Re: How can one normalize this table?

chris65536@xxxxxxxxx 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.)
.

Relevant Pages

  • Re: Autopopulate/ AutoFill Help Needed.
    ... designed relational database, you only want to store information ONCE. ... if you have an Employee table (which based on Ofer's previous reply, ... Manager again in the Form Info table. ...
    (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)
  • 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)
  • Re: Solved - Python: automate input to MySQL query
    ... Dennis Lee Bieber wrote: ... SELECT * FROM employee, manager ... database instead of littering the code? ... manager_id and the employee table would have an employee_id. ...
    (comp.lang.python)