Re: normalization question

This might work:

{Box, Customer, Vendor, Date} key {Box}

{Customer, Vendor, Item, Box} with keys

    {Customer, Vendor, Item} and {Box, Item}

Inclusion dependency:

    {Customer, Vendor, Item, Box}[Box, Customer, Vendor] IN

        {Box, Customer, Vendor, Date}[Box, Customer, Vendor]

The inclusion dependency is not strictly a foreign key constraint, since it references a superkey instead of just a candidate key, but from a practical standpoint, it can be implemented using the machinery that is available for implementing foreign keys, since most implementations permit referencing the columns in any unique constraint.

"myotheraccount" <levtoma_at_yahoo.com> wrote in message news:aac6577c-b5bd-42b3-981c-9dc5cf98f75e_at_b6g2000pre.googlegroups.com... Brian,
Good analysis. I definitely need to dust off my copy of Date's book. I feel like this is getting closer, but I don't think it's quite right yet. The issue with this model is that rule #2 is not enforced. Example:

BoxID Date
1 '1/1/2009'

CustomerID VendorID ItemID BoxID

1                 1              1         1
2                 1              2         1

On Apr 28, 9:16 am, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> Assuming that a Box can contain multiple Items,
>
> {Customer, Vendor, Item, Box, Date} has irreducible FDs
> {Customer, Vendor, Item} --> Box.,
> {Customer, Vendor, Item} --> Date,
> Box --> Customer,
> Box --> Vendor, and
> Box --> Date.
>
> As a consequence, {Customer Vendor, Item, Box, Date} has overlapping keys
> {Customer, Vendor, Item} and {Box, Item}.
>
> The following decomposition is in 3NF and enforces all of your rules
>
> {Box, Date} with key {Box}, and
>
> {Customer, Vendor, Item, Box}
> with keys {Box, Item} and {Customer, Vendor, Item}
>
> Provided the foreign key constraint,
> {Customer, Vendor, Item, Box}[Box] IN {Box, Date}[Box]
> is also in force.
>
> {Box, Date} is in 5NF.
> {Customer Vendor, Item, Box} is in 3NF, but not in BCNF. Unfortunately,
> {Customer Vendor, Item, Box} cannot be decomposed into independent
> projections.
>
> If you have a copy of Date's /An Introduction to Database Systems/, I
> suggest you look up dependency preservation. It's section 12.4 in the 8th
> edition.
Received on Wed Apr 29 2009 - 01:26:37 CEST