Re: normalization question

From: Brian Selzer <brian_at_selzer-software.com>
Date: Tue, 28 Apr 2009 09:16:54 -0400
Message-ID: <PhDJl.6247$Lr6.658_at_flpi143.ffdc.sbc.com>



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 - 08:16:54 CDT

Original text of this message