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

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

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?

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

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

you must take what you read (especially on the internet) with a grain of salt

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!

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

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

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)

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

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

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

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!

Thanks, and that's a fair point. I don't make the rules!

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