Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Question Relational Database: Union Compatibility Question

    Hi,

    According to one reference, the union compatibility is defined as
    such:
    (1)the two relations have the same degree n (number of attributes) ,
    and (2) domain (Ai)= domain (Bi) for 1=<i<=n, where domain stands for
    data type.

    In other reference, it is stated that two relations are union
    compatibles if they have the same degree and the *same attribute
    names* and domains. The *order of attributes is immaterial*.

    which one is the fully correct?


    I don't believe that the two relations should have the same attribute
    names as stated in the 2nd definition , but i understand that each
    pair of the two relations attributes should be of the same domain.
    what about the order of attributes? Has it to be the same?

    I understabd from the first definition that the attributes domains
    order should be the same unlike in the second definition. which one is
    correct?

    The relational database is based on the concept of sets where the
    order of rows and columns is not important. This tends to support
    definition 2. However, if we assume that each relation has two
    attributes of the same domain (eg 10 char), how the Union operator can
    choose between them when mapping the attributes of relation A and B,
    having different relative attributes orders


    for instance Relation A schema is Fname,LName, City , Age;
    the second Relation B schema is City1,FName1,Age1,LName1

    where Fname,Fname1,LName,LName1 is restricted to be of 10 chars

    A Union B=?


    Another question, can the foreign key be NULL? are all the DBMS case
    (un)sensistive?



    <:> thank you <:>

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, the attributes do not have to have the same name

    yes, the domains have to be compatible, column by column

    so you can have table1.city and table2.country in the same column of the union, and they would be union compatioble because they are both from the domain of strings

    yes, foreign keys can be null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    4
    thanks for your replay, what about the order? is it immaterial?
    will the DBMS matchs between the attributes of the two tables as explained in my first question?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    of course order matters, but the order is up to you

    you can match hat size and eye colour, if it makes sense to you to do so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    4
    what you have said is logic. I have just started in the field and believe me i quote literally from the reference that i have

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you must take what you read (especially on the internet) with a grain of salt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    I think the reference in this case is C J Date, who is not a big SQL fan, and who defines the UNION as not depending on column order but indeed on column names AND domains matching. If Date were using SQL (which of course he wouldn't be!) then you could do this by his definition:

    SELECT a, b, c FROM t WHERE x=1
    UNION
    SELECT b, c, a FROM t WHERE x=2

    And if the column names were different you would have to make them the same like this:

    SELECT a, b, c FROM t1
    UNION
    SELECT d as a, e as b, f as c FROM t2

    Bearing in mind that this is the Database Concepts and Design forum, not the SQL forum, this definition is not wrong, it is just different from the SQL definition!
    Last edited by andrewst; 12-01-04 at 08:10. Reason: Pedantic grammar correction

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst
    And if the column names were different you would have to make them the same
    are you saying this because you have an intimate knowledge of the sql standard? because you do not have to alias the names like that in any database that i have experience with
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    No, I wasn't talking about the SQL standard at all. I was talking about relational theory, according to C J Date, and using SQL to illustrate the UNION rules. Like I said, that is "different from the SQL definition".

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i think i get it, sorry

    well, tonyosborne, that's a huge grain of salt, isn't it

    your attributes must have the same name in a relational theory union, but not in a real world union in any database you can actually get your hands on



    p.s. andrewst, congrats on having your article linked to, i would've said so on your blog but there's no way i'm going through the hassle of signing up with blogger just to make a comment (and no, you're not the only one i've told this to)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Andrew,

    I don't think anything in the relational model in regards to union has to do with attribute naming. Naming is just as immaterial as in SQL, although I'm scanning Date's Introduction to DB Systems now.

    The domain, of course, matters a whole lot
    Thanks,

    Matt

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by MattR
    I don't think anything in the relational model in regards to union has to do with attribute naming. Naming is just as immaterial as in SQL, although I'm scanning Date's Introduction to DB Systems now.

    The domain, of course, matters a whole lot
    Well, I think you are wrong!

    Here are some references:
    MORE ON THE DEFINITION OF A RELATION
    Relational UNION Is Simple, But SQL's UNION Isn't
    (Readers of a nervous disposition may prefer not to follow the first URL )

    Basically, if you are going to match up the attributes on each side of the UNION you can do it in one of 2 ways:
    1) By column order - the SQL way
    2) By name & domain - the relational way

    You cannot do it by domain alone - consider:

    RELATION A (X INTEGER, Y INTEGER)
    RELATION B (V INTEGER, W INTEGER)

    What is (A UNION B) if we don't use column order?

    I think C J Date would say you have to do something like this:

    (A UNION (B RENAME V AS X, W AS Y))

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, you've hear of Godwin's law?

    well, i created a new one a long time ago called the dbdebumph law

    first person to cite that site automatically loses and the thread is over

    and no, i am not nervous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937
    okay, i think i get it, sorry

    well, tonyosborne, that's a huge grain of salt, isn't it

    your attributes must have the same name in a relational theory union, but not in a real world union in any database you can actually get your hands on

    I understand and empathise with your POV to some extent, but it is well known that SQL does not follow relational theory correctly and fully. Relational theory shouldn't be dismissed just because it has never been implemented and SQL has! There are SQL books and there are relational theory books; it is a good idea to read both.

    Where I do draw the line is when certain relational experts appear to be contemptuous of mere mortals like us for being so stupid as to use SQL at all, as if we should down tools and refuse to create any more databases until someone implements RM properly!

    Quote Originally Posted by r937
    p.s. andrewst, congrats on having your article linked to, i would've said so on your blog but there's no way i'm going through the hassle of signing up with blogger just to make a comment (and no, you're not the only one i've told this to)
    Thanks, and that's a fair point. I don't make the rules!

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst
    Where I do draw the line is when certain relational experts appear to be contemptuous of mere mortals like us for being so stupid as to use SQL at all, as if we should down tools and refuse to create any more databases until someone implements RM properly!
    exactly why the dbdebumph law was invented



    p.s. did you know that enigma is offering to host sql blogs? see the yak corral thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •