FAQ
I'm almost done reading Date's book. I have a bunch of questions and
need to reread and think about a number of sections. There is one basic
question, though, where I am convinced I must be missing something.

How do you apply Foreign Keys if relations are defined using relational
types?

One example given by Date is logically representing employee data either
in a relation variable (EMP) with attributes empno, ename, deptno, ...
OR by defining a type emp(empno, ename, deptno, ...). This type is then
the data type (domain) of an EMP relvar. The second relation is thus
effectively a set of employee objects.

I see how joins can be done in the second case (use a built-in funtion
THE_DEPTNO(emp) which will extract the deptno value from each tuple),
but what about a foreign key on DEPTNO?

If I read this in the morning and the question is as unclear as I
suppose it is, I'll expound on this.

Henry

Search Discussions

  • Lex de Haan at Jul 20, 2005 at 2:57 am
    Hi Henri,

    I think the two issues are orthogonal. every relvar is based on a set of
    attributes, and every attribute is associated with a type. that is, a type of
    arbitrary complexity. so indeed, you can "hide" or encapsulate several employee
    properties into a single attribute if you like.

    regardless how complicated you choose your relvar attributes, once that exercise
    is done you must assign (candidate) keys for that relvar. a candidate key is
    just a subset of the relvar heading (and the heading is the set of attributes)
    with the property that it uniquely identifies every tuple. well, a trivial key
    is the heading itself -- and if your relvar only has one attribute, that
    attribute *must* be the key ...

    hope this helps,
    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Henry Poras
    Sent: Wednesday, July 20, 2005 08:17
    To: oracle-l_at_freelists.org
    Subject: The Third Manefesto

    I'm almost done reading Date's book. I have a bunch of questions and need to
    reread and think about a number of sections. There is one basic question,
    though, where I am convinced I must be missing something.

    How do you apply Foreign Keys if relations are defined using relational types?

    One example given by Date is logically representing employee data either in a
    relation variable (EMP) with attributes empno, ename, deptno, ...
    OR by defining a type emp(empno, ename, deptno, ...). This type is then the data
    type (domain) of an EMP relvar. The second relation is thus effectively a set of
    employee objects.

    I see how joins can be done in the second case (use a built-in funtion
    THE_DEPTNO(emp) which will extract the deptno value from each tuple), but what
    about a foreign key on DEPTNO?

    If I read this in the morning and the question is as unclear as I suppose it is,
    I'll expound on this.

    Henry

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Jul 20, 2005 at 3:04 am
    sorry Henri,

    when reading my own reply and your original question, I now see I was
    incomplete. undskyld.

    foreign keys. well, there is no such simple concept anymore in your case --
    because indeed, which attribute should the foreign key refer to? this means
    that this foreign key constraint must be expressed as an assertion. as we
    probably all agree, not a good idea -- so having a "good old" employees relvar
    with separate attributes (empno, ename, sal, ...) is not that stupid after all
    :-)

    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Lex de Haan
    Sent: Wednesday, July 20, 2005 09:55
    To: henry_at_itasoftware.com; oracle-l_at_freelists.org
    Subject: RE: The Third Manefesto

    Hi Henri,

    I think the two issues are orthogonal. every relvar is based on a set of
    attributes, and every attribute is associated with a type. that is, a type of
    arbitrary complexity. so indeed, you can "hide" or encapsulate several employee
    properties into a single attribute if you like.

    regardless how complicated you choose your relvar attributes, once that exercise
    is done you must assign (candidate) keys for that relvar. a candidate key is
    just a subset of the relvar heading (and the heading is the set of attributes)
    with the property that it uniquely identifies every tuple. well, a trivial key
    is the heading itself -- and if your relvar only has one attribute, that
    attribute *must* be the key ...

    hope this helps,
    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Henry Poras
    Sent: Wednesday, July 20, 2005 08:17
    To: oracle-l_at_freelists.org
    Subject: The Third Manefesto

    I'm almost done reading Date's book. I have a bunch of questions and need to
    reread and think about a number of sections. There is one basic question,
    though, where I am convinced I must be missing something.

    How do you apply Foreign Keys if relations are defined using relational types?

    One example given by Date is logically representing employee data either in a
    relation variable (EMP) with attributes empno, ename, deptno, ...
    OR by defining a type emp(empno, ename, deptno, ...). This type is then the data
    type (domain) of an EMP relvar. The second relation is thus effectively a set of
    employee objects.

    I see how joins can be done in the second case (use a built-in funtion
    THE_DEPTNO(emp) which will extract the deptno value from each tuple), but what
    about a foreign key on DEPTNO?

    If I read this in the morning and the question is as unclear as I suppose it is,
    I'll expound on this.

    Henry

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Henry Poras at Jul 21, 2005 at 9:50 am
    Thanks Lex,

    That's what I was thinking. Taking a few more tentative steps (as I
    mentioned, I plan to add detail and confidence as I give myself more
    pondering time), let me start by correcting the spelling of the book title
    :) (I meant The Third Manifesto).

    Rough picture with a bit of handwaving (it took me years to be able to type
    and handwave simultaneously). There are a number of ways to model data. It
    can be modeled relationally (and given a reasonably complex system the
    logical representation is not unique. I can come up with more than one
    relational model). It can be modeled with an Object Oriented representation.
    Relational databases and database management systems have the nice features
    of transactions, ad hoc queries, and a certain amount of data integrity
    built in through normalization. A lot of developers and development tools
    use the object representation.

    There has always been a struggle mapping from an object oriented
    representation to a relational representation. One thing Date has shown is
    that objects can be accomodated naturally within a relational model by
    representing the object class as a type/domain. (Don't map object classes to
    relvars). This should make the mapping between the object world and the
    relational world much cleaner and easier.

    However, (and this is where more of the handwaving comes in), it appears
    that doing so removes some of the nice features typically inherent in a
    relational model. We have already mentioned Referential Integrity
    constraints. That level of data integrity enforcement seems to be gone.
    There also seems to be a conflict between 1st and 3rd normal form. Say you
    have an employee type which contains an attribute (I don't think this is the
    right word, but I'm not sure what is) of address. Address contains city and
    zip which are dependent on one another. You woudn't have a relational table
    of empno, ename, city, zip. But since the employee type (object) is atomic,
    you don't worry about the city/zip redundancies (it's atomic, not
    encapsulated. You can still see the granularity of the data within the
    type).

    Wait a second. You can nest types in types, so [city, zip] can be of a
    PostOfficeCode type which is a domain containing all valid city, zip
    combinations. That still doesn't solve the FK issue, however.

    Henry

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Lex de Haan
    Sent: Wednesday, July 20, 2005 4:03 AM
    To: lex.de.haan_at_naturaljoin.nl; henry_at_itasoftware.com;
    oracle-l_at_freelists.org
    Subject: RE: The Third Manefesto

    sorry Henri,

    when reading my own reply and your original question, I now see I was
    incomplete. undskyld.

    foreign keys. well, there is no such simple concept anymore in your case --
    because indeed, which attribute should the foreign key refer to? this means
    that this foreign key constraint must be expressed as an assertion. as we
    probably all agree, not a good idea -- so having a "good old" employees
    relvar with separate attributes (empno, ename, sal, ...) is not that stupid
    after all
    :-)

    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Lex de Haan
    Sent: Wednesday, July 20, 2005 09:55
    To: henry_at_itasoftware.com; oracle-l_at_freelists.org
    Subject: RE: The Third Manefesto

    Hi Henri,

    I think the two issues are orthogonal. every relvar is based on a set of
    attributes, and every attribute is associated with a type. that is, a type
    of arbitrary complexity. so indeed, you can "hide" or encapsulate several
    employee properties into a single attribute if you like.

    regardless how complicated you choose your relvar attributes, once that
    exercise is done you must assign (candidate) keys for that relvar. a
    candidate key is just a subset of the relvar heading (and the heading is the
    set of attributes) with the property that it uniquely identifies every
    tuple. well, a trivial key is the heading itself -- and if your relvar only
    has one attribute, that attribute *must* be the key ...

    hope this helps,
    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Henry Poras
    Sent: Wednesday, July 20, 2005 08:17
    To: oracle-l_at_freelists.org
    Subject: The Third Manefesto

    I'm almost done reading Date's book. I have a bunch of questions and need to
    reread and think about a number of sections. There is one basic question,
    though, where I am convinced I must be missing something.

    How do you apply Foreign Keys if relations are defined using relational
    types?

    One example given by Date is logically representing employee data either in
    a relation variable (EMP) with attributes empno, ename, deptno, ...
    OR by defining a type emp(empno, ename, deptno, ...). This type is then the
    data type (domain) of an EMP relvar. The second relation is thus effectively
    a set of employee objects.

    I see how joins can be done in the second case (use a built-in funtion
    THE_DEPTNO(emp) which will extract the deptno value from each tuple), but
    what about a foreign key on DEPTNO?

    If I read this in the morning and the question is as unclear as I suppose it
    is, I'll expound on this.

    Henry

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Lex de Haan at Jul 21, 2005 at 10:20 am
    Hi Henri,

    what a coincidence --
    I just finished a phone conversation with Chris, and I mentioned this thread to
    him :-)

    first of all, I am happy to say that he agreed with my reply below.
    about your additional comments: normal forms (1NF, 3NF) have nothing to do with
    the relational model; the only relational requirement is that relvars are
    "normalized" -- i.e. in 1NF.

    indeed, apart from the integrity constraint issues, using complex types
    typically introduces redundancy in your model as well, just like any "regular"
    data model not in 2NF or 3NF.

    I hope you were able to read between the lines that Chris Date and Hugh Darwen
    are *not exactly* advocating this approach. they just show that if you *insist*
    you can do it -- in other words, the relational model does not forbid you to use
    attributes of any complexity.

    Note: there are more stupid things you can do without violating the relational
    model :-)

    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Henry Poras
    Sent: Thursday, July 21, 2005 16:50
    To: lex.de.haan_at_naturaljoin.nl; oracle-l_at_freelists.org
    Subject: RE: The Third Manefesto

    Thanks Lex,

    That's what I was thinking. Taking a few more tentative steps (as I mentioned, I
    plan to add detail and confidence as I give myself more pondering time), let me
    start by correcting the spelling of the book title
    :) (I meant The Third Manifesto).

    Rough picture with a bit of handwaving (it took me years to be able to type and
    handwave simultaneously). There are a number of ways to model data. It can be
    modeled relationally (and given a reasonably complex system the logical
    representation is not unique. I can come up with more than one relational
    model). It can be modeled with an Object Oriented representation.
    Relational databases and database management systems have the nice features of
    transactions, ad hoc queries, and a certain amount of data integrity built in
    through normalization. A lot of developers and development tools use the object
    representation.

    There has always been a struggle mapping from an object oriented representation
    to a relational representation. One thing Date has shown is that objects can be
    accomodated naturally within a relational model by representing the object class
    as a type/domain. (Don't map object classes to relvars). This should make the
    mapping between the object world and the relational world much cleaner and
    easier.

    However, (and this is where more of the handwaving comes in), it appears that
    doing so removes some of the nice features typically inherent in a relational
    model. We have already mentioned Referential Integrity constraints. That level
    of data integrity enforcement seems to be gone.
    There also seems to be a conflict between 1st and 3rd normal form. Say you have
    an employee type which contains an attribute (I don't think this is the right
    word, but I'm not sure what is) of address. Address contains city and zip which
    are dependent on one another. You woudn't have a relational table of empno,
    ename, city, zip. But since the employee type (object) is atomic, you don't
    worry about the city/zip redundancies (it's atomic, not encapsulated. You can
    still see the granularity of the data within the type).

    Wait a second. You can nest types in types, so [city, zip] can be of a
    PostOfficeCode type which is a domain containing all valid city, zip
    combinations. That still doesn't solve the FK issue, however.

    Henry

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Lex de Haan
    Sent: Wednesday, July 20, 2005 4:03 AM
    To: lex.de.haan_at_naturaljoin.nl; henry_at_itasoftware.com; oracle-l_at_freelists.org
    Subject: RE: The Third Manefesto

    sorry Henri,

    when reading my own reply and your original question, I now see I was
    incomplete. undskyld.

    foreign keys. well, there is no such simple concept anymore in your case --
    because indeed, which attribute should the foreign key refer to? this means
    that this foreign key constraint must be expressed as an assertion. as we
    probably all agree, not a good idea -- so having a "good old" employees relvar
    with separate attributes (empno, ename, sal, ...) is not that stupid after all
    :-)

    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Lex de Haan
    Sent: Wednesday, July 20, 2005 09:55
    To: henry_at_itasoftware.com; oracle-l_at_freelists.org
    Subject: RE: The Third Manefesto

    Hi Henri,

    I think the two issues are orthogonal. every relvar is based on a set of
    attributes, and every attribute is associated with a type. that is, a type of
    arbitrary complexity. so indeed, you can "hide" or encapsulate several employee
    properties into a single attribute if you like.

    regardless how complicated you choose your relvar attributes, once that exercise
    is done you must assign (candidate) keys for that relvar. a candidate key is
    just a subset of the relvar heading (and the heading is the set of attributes)
    with the property that it uniquely identifies every tuple. well, a trivial key
    is the heading itself -- and if your relvar only has one attribute, that
    attribute *must* be the key ...

    hope this helps,
    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Henry Poras
    Sent: Wednesday, July 20, 2005 08:17
    To: oracle-l_at_freelists.org
    Subject: The Third Manefesto

    I'm almost done reading Date's book. I have a bunch of questions and need to
    reread and think about a number of sections. There is one basic question,
    though, where I am convinced I must be missing something.

    How do you apply Foreign Keys if relations are defined using relational types?

    One example given by Date is logically representing employee data either in a
    relation variable (EMP) with attributes empno, ename, deptno, ...
    OR by defining a type emp(empno, ename, deptno, ...). This type is then the data
    type (domain) of an EMP relvar. The second relation is thus effectively a set of
    employee objects.

    I see how joins can be done in the second case (use a built-in funtion
    THE_DEPTNO(emp) which will extract the deptno value from each tuple), but what
    about a foreign key on DEPTNO?

    If I read this in the morning and the question is as unclear as I suppose it is,
    I'll expound on this.

    Henry

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Karen Morton at Jul 20, 2005 at 6:49 am
    I thought I'd use this thread as an opportunity to announce that Hotsos will be sponsoring CJ Date in Dallas on Oct. 19-20, 2005. He'll be delivering his two-part seminar based largely on his book (attendees get a copy). To get more information, see http://www.hotsos.com/events/DD201.php?event_id=62.




    Karen Morton
    Hotsos Enterprises, Ltd.
    http://www.hotsos.com
  • Henry Poras at Jul 21, 2005 at 12:00 pm
    Thanks Lex,

    Confirmation from both you and Chris is nice to have. Thanks for the help.

    Henry

    -----Original Message-----
    From: Lex de Haan
    Sent: Thursday, July 21, 2005 11:17 AM
    To: henry_at_itasoftware.com; oracle-l_at_freelists.org
    Subject: RE: The Third Manefesto

    Hi Henri,

    what a coincidence --
    I just finished a phone conversation with Chris, and I mentioned this thread
    to him :-)

    first of all, I am happy to say that he agreed with my reply below. about
    your additional comments: normal forms (1NF, 3NF) have nothing to do with
    the relational model; the only relational requirement is that relvars are
    "normalized" -- i.e. in 1NF.

    indeed, apart from the integrity constraint issues, using complex types
    typically introduces redundancy in your model as well, just like any
    "regular" data model not in 2NF or 3NF.

    I hope you were able to read between the lines that Chris Date and Hugh
    Darwen are *not exactly* advocating this approach. they just show that if
    you *insist* you can do it -- in other words, the relational model does not
    forbid you to use attributes of any complexity.

    Note: there are more stupid things you can do without violating the
    relational model :-)

    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Henry Poras
    Sent: Thursday, July 21, 2005 16:50
    To: lex.de.haan_at_naturaljoin.nl; oracle-l_at_freelists.org
    Subject: RE: The Third Manefesto

    Thanks Lex,

    That's what I was thinking. Taking a few more tentative steps (as I
    mentioned, I plan to add detail and confidence as I give myself more
    pondering time), let me start by correcting the spelling of the book title
    :) (I meant The Third Manifesto).

    Rough picture with a bit of handwaving (it took me years to be able to type
    and handwave simultaneously). There are a number of ways to model data. It
    can be modeled relationally (and given a reasonably complex system the
    logical representation is not unique. I can come up with more than one
    relational model). It can be modeled with an Object Oriented representation.
    Relational databases and database management systems have the nice features
    of transactions, ad hoc queries, and a certain amount of data integrity
    built in through normalization. A lot of developers and development tools
    use the object representation.

    There has always been a struggle mapping from an object oriented
    representation to a relational representation. One thing Date has shown is
    that objects can be accomodated naturally within a relational model by
    representing the object class as a type/domain. (Don't map object classes to
    relvars). This should make the mapping between the object world and the
    relational world much cleaner and easier.

    However, (and this is where more of the handwaving comes in), it appears
    that doing so removes some of the nice features typically inherent in a
    relational model. We have already mentioned Referential Integrity
    constraints. That level of data integrity enforcement seems to be gone.
    There also seems to be a conflict between 1st and 3rd normal form. Say you
    have an employee type which contains an attribute (I don't think this is the
    right word, but I'm not sure what is) of address. Address contains city and
    zip which are dependent on one another. You woudn't have a relational table
    of empno, ename, city, zip. But since the employee type (object) is atomic,
    you don't worry about the city/zip redundancies (it's atomic, not
    encapsulated. You can still see the granularity of the data within the
    type).

    Wait a second. You can nest types in types, so [city, zip] can be of a
    PostOfficeCode type which is a domain containing all valid city, zip
    combinations. That still doesn't solve the FK issue, however.

    Henry

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Lex de Haan
    Sent: Wednesday, July 20, 2005 4:03 AM
    To: lex.de.haan_at_naturaljoin.nl; henry_at_itasoftware.com;
    oracle-l_at_freelists.org
    Subject: RE: The Third Manefesto

    sorry Henri,

    when reading my own reply and your original question, I now see I was
    incomplete. undskyld.

    foreign keys. well, there is no such simple concept anymore in your case --
    because indeed, which attribute should the foreign key refer to? this means
    that this foreign key constraint must be expressed as an assertion. as we
    probably all agree, not a good idea -- so having a "good old" employees
    relvar with separate attributes (empno, ename, sal, ...) is not that stupid
    after all
    :-)

    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Lex de Haan
    Sent: Wednesday, July 20, 2005 09:55
    To: henry_at_itasoftware.com; oracle-l_at_freelists.org
    Subject: RE: The Third Manefesto

    Hi Henri,

    I think the two issues are orthogonal. every relvar is based on a set of
    attributes, and every attribute is associated with a type. that is, a type
    of arbitrary complexity. so indeed, you can "hide" or encapsulate several
    employee properties into a single attribute if you like.

    regardless how complicated you choose your relvar attributes, once that
    exercise is done you must assign (candidate) keys for that relvar. a
    candidate key is just a subset of the relvar heading (and the heading is the
    set of attributes) with the property that it uniquely identifies every
    tuple. well, a trivial key is the heading itself -- and if your relvar only
    has one attribute, that attribute *must* be the key ...

    hope this helps,
    kind regards,

    Lex.


    Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Henry Poras
    Sent: Wednesday, July 20, 2005 08:17
    To: oracle-l_at_freelists.org
    Subject: The Third Manefesto

    I'm almost done reading Date's book. I have a bunch of questions and need to
    reread and think about a number of sections. There is one basic question,
    though, where I am convinced I must be missing something.

    How do you apply Foreign Keys if relations are defined using relational
    types?

    One example given by Date is logically representing employee data either in
    a relation variable (EMP) with attributes empno, ename, deptno, ...
    OR by defining a type emp(empno, ename, deptno, ...). This type is then the
    data type (domain) of an EMP relvar. The second relation is thus effectively
    a set of employee objects.

    I see how joins can be done in the second case (use a built-in funtion
    THE_DEPTNO(emp) which will extract the deptno value from each tuple), but
    what about a foreign key on DEPTNO?

    If I read this in the morning and the question is as unclear as I suppose it
    is, I'll expound on this.

    Henry

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 20, '05 at 1:20a
activeJul 21, '05 at 12:00p
posts7
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase