Re: normalization question

"Brian Selzer" <brian_at_selzer-software.com> wrote in message news:4nMJl.15688$pr6.6106_at_flpi149.ffdc.sbc.com...
> This might work:
>
> {Box, Customer, Vendor, Date} key {Box}
>
> {Customer, Vendor, Item, Box} with keys
> {Customer, Vendor, Item} and {Box, Item}
>
> Inclusion dependency:
> {Customer, Vendor, Item, Box}[Box, Customer, Vendor] IN
> {Box, Customer, Vendor, Date}[Box, Customer, Vendor]
>
> The inclusion dependency is not strictly a foreign key constraint, since
> it references a superkey instead of just a candidate key, but from a
> practical standpoint, it can be implemented using the machinery that is
> available for implementing foreign keys, since most implementations permit
> referencing the columns in any unique constraint.
>

Here's example DDL that works on Microsoft Sql Server 2000 that implement a "foreign superkey" inclusion dependency:

CREATE TABLE R1
(

    A INT NOT NULL,
    B INT NOT NULL,
    C INT NOT NULL,
    D INT NOT NULL,

    A INT NOT NULL,
    B INT NOT NULL,
    C INT NOT NULL,
    E INT NOT NULL,
    PRIMARY KEY (B, C, E),

> "myotheraccount" <levtoma_at_yahoo.com> wrote in message
> news:aac6577c-b5bd-42b3-981c-9dc5cf98f75e_at_b6g2000pre.googlegroups.com...
> Brian,
> Good analysis. I definitely need to dust off my copy of Date's book.
> I feel like this is getting closer, but I don't think it's quite right
> yet. The issue with this model is that rule #2 is not enforced.
> Example:
>
> BoxID Date
> 1 '1/1/2009'
>
> CustomerID VendorID ItemID BoxID
> 1 1 1 1
> 2 1 2 1
>
> This scenario adheres to all the keys, but same box is being sent to
> two different customers.
>
>
> On Apr 28, 9:16 am, "Brian Selzer" <br..._at_selzer-software.com> wrote:
>> Assuming that a Box can contain multiple Items,
>>
>> {Customer, Vendor, Item, Box, Date} has irreducible FDs
>> {Customer, Vendor, Item} --> Box.,
>> {Customer, Vendor, Item} --> Date,
>> Box --> Customer,
>> Box --> Vendor, and
>> Box --> Date.
>>
>> As a consequence, {Customer Vendor, Item, Box, Date} has overlapping keys
>> {Customer, Vendor, Item} and {Box, Item}.
>>
>> The following decomposition is in 3NF and enforces all of your rules
>>
>> {Box, Date} with key {Box}, and
>>
>> {Customer, Vendor, Item, Box}
>> with keys {Box, Item} and {Customer, Vendor, Item}
>>
>> Provided the foreign key constraint,
>> {Customer, Vendor, Item, Box}[Box] IN {Box, Date}[Box]
>> is also in force.
>>
>> {Box, Date} is in 5NF.
>> {Customer Vendor, Item, Box} is in 3NF, but not in BCNF. Unfortunately,
>> {Customer Vendor, Item, Box} cannot be decomposed into independent
>> projections.
>>
>> If you have a copy of Date's /An Introduction to Database Systems/, I
>> suggest you look up dependency preservation. It's section 12.4 in the 8th
>> edition.
>
>
Received on Wed Apr 29 2009 - 14:57:05 CEST