Re: Foreign keys
Date: Tue, 15 Jan 2008 22:05:24 -0500
Message-ID: <Vvejj.115$uE.85_at_newssvr22.news.prodigy.net>
"Kira Yamato" <kirakun_at_earthlink.net> wrote in message
news:2008011514331750073-kirakun_at_earthlinknet...
> On 2008-01-15 14:16:32 -0500, "Brian Selzer" <brian_at_selzer-software.com>
> said:
>
>>
>> "Kira Yamato" <kirakun_at_earthlink.net> wrote in message
>> news:2008011512561916807-kirakun_at_earthlinknet...
>>> On 2008-01-15 11:51:43 -0500, "Brian Selzer" <brian_at_selzer-software.com>
>>> said:
>>>
>>>>
>>>> "Kira Yamato" <kirakun_at_earthlink.net> wrote in message
>>>> news:2008011510320716807-kirakun_at_earthlinknet...
>>>>> On 2008-01-15 04:37:23 -0500, "Brian Selzer"
>>>>> <brian_at_selzer-software.com>
>>>>> said:
>>>>>
>>>>>>
>>>>>> "Kira Yamato" <kirakun_at_earthlink.net> wrote in message
>>>>>> news:2008011502240916807-kirakun_at_earthlinknet...
>>>>>>> On 2008-01-14 21:18:57 -0500, "Evan Keel" <evankeel_at_sbcglobal.net>
>>>>>>> said:
>>>>>>>
>>>>>>>> Always a physical issue. Never a theory issue.Agree?
>>>>>>>
>>>>>>> Foreign keys are functional dependencies across two relations.
>>>>>>>
>>>>>>> More specifically, let
>>>>>>> R1(K1, A1, B1)
>>>>>>> be a relation with attribute sets K1, A1 and B1 where K1 is R1's
>>>>>>> primary
>>>>>>> key and B1 is a foreign key to the relation
>>>>>>> R2(K2, A2)
>>>>>>> where K2 is R2's primary key and A2 is the set of its remaining
>>>>>>> attributes.
>>>>>>>
>>>>>>> Then the foreign key B1 represents the functional dependency
>>>>>>> B1 --> A2,
>>>>>>> which is the functional dependency across two relation I mentioned
>>>>>>> in
>>>>>>> the
>>>>>>> first sentence.
>>>>>>>
>>>>>>> Furthermore, through transitivity by the functional dependency
>>>>>>> K1 -->
>>>>>>> B1,
>>>>>>> the foreign key also represents the inter-relational functional
>>>>>>> dependency
>>>>>>> K1 --> A2.
>>>>>>>
>>>>>>> Am I correct to say this?
>>>>>>>
>>>>>>
>>>>>> I don't think so. A functional dependency A --> B is surjective,
>>>>>> meaning
>>>>>> not only that for every A there must be one and only one B, but also
>>>>>> that
>>>>>> for every B there must be at least one A. The relationship between
>>>>>> B1
>>>>>> and
>>>>>> A2 above is injective, as is the relationship between K1 and A2.
>>>>>
>>>>> Hmm... According to Atzeni/De Antonellis's book "Relational Database
>>>>> Theory" (section 1.6) he does not include surjectivity as a
>>>>> requirement
>>>>> for functional dependency.
>>>>>
>>>>> --
>>>>
>>>> Functional dependencies are defined in terms of sets of attributes
>>>> within
>>>> the same relation schema. A functional dependency is a statement,
>>>> A -->
>>>> B,
>>>> where A and B are sets of attributes. A relation satisfies the
>>>> functional
>>>> depencency if and only if whenever two tuples agree on values for A
>>>> they
>>>> also agree on values for B. Since both A and B appear in the same
>>>> relation
>>>> schema, whenever there is a value for B, there must also be a value for
>>>> A.
>>>> So it does not matter whether it is a stated requirement, surjectivity
>>>> is
>>>> a
>>>> property that functional dependencies exhibit.
>>>
>>> I think you're confusing the domain of an attribute with the set of
>>> attributes itself.
>>>
>>
>> Are you not a native english speaker?
>
> You are quite right that I am not a native English speaker.
>
>> Did my use of "values for A" confuse
>> you? Suppose that you have two tuples, then you have a value for A in
>> one
>> tuple and a value for A in the other, so that would mean that there are
>> "values for A," right? Especially before those values are compared? Or
>> was
>> it my use of the singular, "value for A," that confused you? If A is a
>> set
>> of attributes, then a value for A would be a set of attribute values, one
>> for each attribute, right?
>>
>>> An *attribute* is a variable that can participate in a relation. Each
>>> attribute has an associated set of values, which is called the *domain*
>>> of
>>> the attributes.
>>>
>>> For example, "age" is an attribute in the relation "person". The
>>> possible
>>> values of "age" can be defined as the set of non-negative integers.
>>>
>>> A functional dependency is a statement concerning the mapping between
>>> the
>>> domains of the attributes, not the set of attributes themselves.
>>>
>>
>> I don't think so. What about a determinant that is composed of more than
>> one attribute? For example, {SalesOrderNo, LineNo} --> PartNo.
>
> If you have a composite key, then the domain of the key is simply the
> cartesian product of the domains of the attributes in the key. So, in you
> case, the domain for {SalesOrderNo, LineNo} is simply Z x Z.
>
The domain for SalesOrderNo need not be Z, it could be SalesOrderNumbers. The domain for LineNo could be SalesOrderLineNumbers. Also, the domain of a composite key is not necessarily the cartesian product of its attributes' domains: it could be a subset of that product, since there may be constraints that limit the possible combinations of values from those domains.
>> What if
>> both the determinant and the dependent draw their values from the same
>> domain? For example, EmployeeId --> ManagerId, since managers are also
>> employees.
>
> What about it? How does that require surjectivity on the domain of
> PartNo?
>
> Conceivably, the domain of PartNo would include all possible integers.
> But the relation will not necessarily have all tuples where *every*
> possible values of PartNo are in some tuple of the relation.
>
I think you're confusing what is possible and what is actual. What is represented by the domains and the intension of a database specifies /what can be/, but due to the domain closure assumption, what is represented by values in the extension of a database states /what is/. A functional dependency does not limit the possible values in each domain: it instead controls which combinations of values are possible. Lets take that one step further, a functional dependency A --> B does not directly limit the possible values for A or for B; instead it limits the possible combinations of values for A and B. Since A --> B only limits AB combinations, then for each value for B there must be at least one value for A, otherwise there wouldn't be an AB combination, would there?
>>
>>> So, if I have a "person" relation with a finite number of tuples, then I
>>> cannot possibly have all possible non-negative integers. But yet, we
>>> still do have a functional dependency from the ID of the person to the
>>> person's age.
>
> --
>
> -kira
>
Received on Wed Jan 16 2008 - 04:05:24 CET