Re: normalization question
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.,
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} --> Date,
Box --> Customer,
Box --> Vendor, and
Box --> Date.
{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 - 15:16:54 CEST