Re: normalization question

That's similar to my first proposed solution, but, like that solution, does not enforce rule #3. For example:
R1
BoxID, CustomerID, VendorID

1,        1,                1
2,        1,                1

1,        1
2,        1

The keys will allow this, but it violates Rule #3

On Apr 27, 1:46 pm, "Matko" <mkl..._at_foi.hr> wrote:
> Thanks! You are right!
>
> R1(BoxID, CustomerID, VendorID) with key {BoxID}
> R2(BoxID, ItemID) witk key {BoxID, ItemID}
>
> What do you think?
>
> "Brian Selzer" <br..._at_selzer-software.com> wrote in message
>
> news:Q%hJl.15594$pr6.3490_at_flpi149.ffdc.sbc.com...
>
>
>
>
>
> > "Matko" <mkl..._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" <levt..._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 - 22:11:16 CEST