Re: normalization question

"Matko" <mklaic_at_foi.hr> wrote in message news:gt3ihl$qq1$1_at_ss408.t-com.hr...
> 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" <levtoma_at_yahoo.com> wrote in message
> news:4d142477-3b5d-4e56-a7de-34415d2bb728_at_u10g2000vbd.googlegroups.com...
> 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