Re: normalization question

From: Matko <mklaic_at_foi.hr>
Date: Tue, 28 Apr 2009 11:50:29 +0200
Message-ID: <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.

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 - 04:50:29 CDT

Original text of this message