Re: normalization question

From: Brian Selzer <>
Date: Mon, 27 Apr 2009 08:53:51 -0400
Message-ID: <Q%hJl.15594$>

"Matko" <> wrote in message news:gt3ihl$qq1$
> R(CustomerID, VendorID, ItemID, BoxID)
> R is a key itself. So there are no non-prime attributes and R must enforce
> #1, #2, #3 and BCNF.
> Simpler is better!

Wouldn't rule #3 require that {CustomerID, VendorID, ItemID} be a key? And wouldn't {BoxID, ItemID} also be a key? The entire heading of R is a superkey, not a key.

> "myotheraccount" <> wrote in message
> I've run into a design issue that I just can't wrap my head around. I
> tried to model here below with different types of information, as the
> actual information I am modeling is confidential.
> Here are the rules:
> 1) There are items, vendors, customers, and boxes.
> 2) Boxes contain items and are sent from one vendor to one customer on
> a specific date.
> 3) Items can only be sent once from each vendor to each customer
> The question is, how is this modeled in the highest possible normal
> form.
> So far I've come up with two possible solutions, both unsatisfactory:
> Box Entity, which has a unique key of CustomerID, VendorID, and Date
> BoxItem Entity, which has a unique key of BoxID, and ItemID
> This solution does not enforces rule #3
> Alternatively, I've come up with:
> Shipment Entity, which has a unique key of CustomerID and VendorID.
> Box Entity, which has a unique key of ShipmentID and Date
> ShipmentItem Entity, which has a unique key of ShipmentID and ItemID,
> and contains a BoxID
> This does not comply with third normal form. What happens when the
> ShipmentID/BoxID of a ShipmentItem does not match the ShipmentID of
> the Box? It could be argued that this does not adequately enforce
> rule #2
> Any ideas on the "proper" way to model this? WWDACD (Would would Date
> and Codd do?)
> Thanks in advance!
Received on Mon Apr 27 2009 - 14:53:51 CEST

Original text of this message