Re: normalization question

From: Matko <mklaic_at_foi.hr>
Date: Mon, 27 Apr 2009 19:46:27 +0200
Message-ID: <gt4r10$4bt$1_at_ss408.t-com.hr>



Thanks! You are right!

R1(BoxID, CustomerID, VendorID) with key {BoxID} R2(BoxID, ItemID) witk key {BoxID, ItemID}

What do you think?

"Brian Selzer" <brian_at_selzer-software.com> wrote in message news:Q%hJl.15594$pr6.3490_at_flpi149.ffdc.sbc.com...
>
> "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 - 12:46:27 CDT

Original text of this message