Re: normalization question
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 - 21:13:23 CEST