Re: normalization question

From: myotheraccount <levtoma_at_yahoo.com>
Date: Tue, 28 Apr 2009 12:13:23 -0700 (PDT)
Message-ID: <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

This scenario adheres to all the keys, but same box is being sent to two different customers.

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 Tue Apr 28 2009 - 14:13:23 CDT

Original text of this message