Re: normalization question

"Matko" <mklaic_at_foi.hr> wrote in message news:gt6jfc$q42$1_at_ss408.t-com.hr...
> RULE #3:
> "Third normal form (very informal definition): A relation variable is in
> 3NF if and only if the nonkey attributes (if any) are
> a. Mutually independent, and
> b. Irreducibly dependent on the primary key."
> says C. J. Date
>
> If you add more rows like this:
>
> R1
> BoxID, CustomerID, VendorID
> 1, 1, 1
> 2, 1, 1
> 3, 1, 2
> 4, 3, 1
>
> you can see that CustomerID and VendorID are mutually independent, in
> other words, value of CustomerID doesn't imply exact value of VendorID and
> vice versa.
>
> R1 is irreducibly independent because key is single attribute.
> For example, CustomerID isn't irreducibly dependent on key candidate
> {BoxID, VendorID} because attribute VendorID can be removed from
> functional dependency (FD) {BoxID, VendorID} --> CustomerID.
>
> R1 should enforce rule #3.
>
> Tell me if i'm wrong.
>

You're wrong: both Box1 and Box2 could contain Item1.

> P.S. One thing should't be forgotten. Relation schemes (R, F) (relation
> variables or relvars, where F is set of FDs) enforce rules, i. e. if you
> observe relation (entries, values) with few entries it could seem to you
> that rules aren't enforced.
>
> What do you think?
>
> Matko
>
> "myotheraccount" <levtoma_at_yahoo.com> wrote in message
> news:ecd466e7-0180-4349-84aa-0a06f4da6985_at_s38g2000prg.googlegroups.com...
> 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
>
> R2
> BoxID, ItemID
> 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 Tue Apr 28 2009 - 14:46:26 CEST