# Re: Foreign keys

From: Kira Yamato <kirakun_at_earthlink.net>

Date: Tue, 15 Jan 2008 14:33:17 -0500

Message-ID: <2008011514331750073-kirakun_at_earthlinknet>

>> On 2008-01-15 11:51:43 -0500, "Brian Selzer" <brian_at_selzer-software.com>

>> I think you're confusing the domain of an attribute with the set of

>

> Are you not a native english speaker?

>> An *attribute* is a variable that can participate in a relation. Each

>> So, if I have a "person" relation with a finite number of tuples, then I

Date: Tue, 15 Jan 2008 14:33:17 -0500

Message-ID: <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.

> 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.

>

>> 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.*-- -kiraReceived on Tue Jan 15 2008 - 20:33:17 CET