If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Relational Database: Union Compatibility Question

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 <:>
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
you must take what you read (especially on the internet) with a grain of salt
__________________
rudy.ca | @rudydotca
Buy my SitePoint book: Simply SQL
Reply With Quote
  #7 (permalink)  
Old
Moderator.
 
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!
__________________
Tony Andrews
http://tinyurl.com/tonyandrews

Last edited by andrewst; 12-01-04 at 07:10. Reason: Pedantic grammar correction
Reply With Quote
  #8 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #9 (permalink)  
Old
Moderator.
 
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".
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #10 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #12 (permalink)  
Old
Moderator.
 
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))
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #13 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
  #14 (permalink)  
Old
Moderator.
 
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!
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #15 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On