Re: How can one normalize this table?
- From: Jon Heggland <heggland@xxxxxxxxxxx>
- Date: Thu, 15 Dec 2005 10:32:21 +0100
In article <1134611329.358990.12250@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
chris65536@xxxxxxxxx says...
> 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?
This problem is the exact same as the one in the "Enforcing functional
dependency constraints" thread. And it is the classic example of a
relvar that is in 3NF, but not in BCNF, and cannot be decomposed without
splitting up an FD. Most database textbooks explain it.
> 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.
Both { office, position } and { office, employee } are keys, plain and
simple. "Primary" has no bearing on normalisation.
--
Jon
.
- References:
- How can one normalize this table?
- From: chris65536@xxxxxxxxx
- How can one normalize this table?
- Prev by Date: Re: 3vl 2vl and NULL
- Next by Date: Re: How can one normalize this table?
- Previous by thread: Re: How can one normalize this table?
- Index(es):
Relevant Pages
|
