Do the "columns" in a table in a RMDB have order?

Question

I learned that there is no concept of order in terms of tuples (e.g. rows) in a table but according to wikipedia "a tuple is an ordered list of elements". Does that mean that attributes do have an order? If yes why would they be treated differently, couldn't one add another column to a table (which is why the tuples don't have order)?

"In this notation, attribute–value pairs may appear in any order." does this mean attributes have no order?

Answer 1

There are 2 kinds of tuples, so to speak. There is "pure mathematics", and there indeed tuples are typically defined as "ordered lists of values". Thus, in mathematical theories, it makes sense to speak of "the first value in a tuple" and such. This may be the sense or the context that your Wikipedia article is referring to.

The Haskell language supports this kind of tuple and, e.g., it also has a fst() operator to extract the "first" value out of such a tuple.

Codd realized, however, that this would be extremely impractical when applying this mathematical concept of tuples-as-ordered-lists to the field of data management. In data management, he wanted addressability of the values by attribute name, rather than by ordinal position. Indeed, imagine the devastating consequences if "the second attribute out of five" is removed from a table, and now all the programs that address "the third" and "the fourth" attribute of that same table now have to be inventarised and adapted.

So in the relational model, tuples are sets-of-named-values instead, and consequently, in the kind of tuples that play a role in the relational model of data, there is indeed not any concept of ordering of the values.

And then as indicated in that other response, there is SQL and its blasphemous deviations from relational theory. In SQL, the ordering of attributes in tuples and headings is quite meaningful, and the consequences are all over the place. In FK declarations, correspondance of the respective referring and referred attributes is by ordinal position, not by name. Other cases are with UNIONs and EXCEPTs. Take a table T, columns X and Y the same type.

SELECT X,Y FROM T UNION SELECT Y,X FROM T

is not invalid per se, but the standard prescribes that the column names in the result are system-defined (!). Implementations that "do the sensible thing" and deviate from this, producing a table with columns named X and Y, respectively, then face their users with the consequence that the former expression is not identical to

SELECT Y,X FROM T UNION SELECT X,Y FROM T

(because the column ordering X,Y is another ordering than Y,X and thence the headings are unequal, and consequently the tables are unequal.)

SELECT X,Y FROM T EXCEPT SELECT Y,X FROM T

gives results that will leave many novice SQL users scratching their heads for quite a while.