 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / General DB Topics / DB Theory / October 2007separation of church and state?
I finally sprung for CJ Date's "Writings, 2000-2006" and skimming it, noticed this point in chapter 10. It reminded me of the recent posts about avoiding books that start with silly sentences even though this quote is from page 174: "Ordering, by contrast, is not part of the relational algebra; nor can it be, because its result isn't a relation. This doesn't mean you can't have an ORDER BY operator, of course - it just means that operator isn't part of the algebra as such, and it can't be used in an expression that's nested inside some other (relational) expression, or more generally in any context where the result is indeed required to be a relation. That's why you can't use ORDER BY in a view definition, for example." It seems a little doctrinaire to me. I can agree that the "result isn't a relation" but on the other hand a user could see such a result without knowing that "ORDER BY" was involved and not be faulted for taking it to be a relation. For that matter, in some apps, users take it for granted that all results are arbitrarily ordered and that those results can be used to produce other results. By analogy of separating the logical from physical implementation, if you want to declare a separation of church and state, I'd think you'd need to mention both. Not to tout SQL but I took the above to mean that if a table were declared with an "INDEX", it shouldn't be allowed to participate in expressions of the relational algebra, which seems extreme and somewhat useless to me. This is just one small paragraph and I'm not complaining about the rest of the book - Am I mis-interpreting? > It seems a little doctrinaire to me. I can agree that the "result isn't a > relation" but on the other hand a user could see such a result without > knowing that "ORDER BY" was involved and not be faulted for taking it to > be a relation. What user ever would? Users never see relations. They see various kinds of reports (using the word "report" to mean anything users get to see). Roy >> It seems a little doctrinaire to me. I can agree that the "result isn't a >> relation" but on the other hand a user could see such a result without [quoted text clipped - 5 lines] > > Roy I can buy that relations aren't visible to the naked eye but I'd say that users certainly do see representations of relations (even if the representations are called tables) and the relations are visible in the mind's eye. Maybe if the word "understand" is substituted for "see" it makes more sense, after all, predicates aren't physically visible either but users do need to understand them. > >> It seems a little doctrinaire to me. I can agree that the "result isn't a > >> relation" but on the other hand a user could see such a result without [quoted text clipped - 12 lines] > makes more sense, after all, predicates aren't physically visible either > but users do need to understand them. The way I look at it is that Date tries to remind us of the mathematical nature of relations and that a relation which is a abstract conceptual is not meant to be seens but to be reprensented. It is true that it does seem doctrinaire but at the same time it does make sense. Whether one user does *see* a mathematical function (ex: f(x)) seems as an unappropriate verb to to use: he just represents it at some point in time. The user however may *see* the symbological representation of the function (ex: f(x) = 2x+1) or he sees the graphical representation of the function (in this case a line). imho... > > It seems a little doctrinaire to me. I can agree that the "result isn't a > > relation" but on the other hand a user could see such a result without [quoted text clipped - 3 lines] > What user ever would? Users never see relations. They see various kinds of > reports (using the word "report" to mean anything users get to see). That depends on what you mean by the word "user". Is the user of a table or a view a "user"? >>>It seems a little doctrinaire to me. I can agree that the "result isn't > [quoted text clipped - 12 lines] > That depends on what you mean by the word "user". Is the user of a table or > a view a "user"? One can use a relation without seeing it. For example, if one creates a new relation as a join of two relations, one uses all three relations without ever seeing any of them. One doesn't see anything until one requests some sort of visible output. The visible output is called a report above. > > It seems a little doctrinaire to me. I can agree that the "result isn't a > > relation" but on the other hand a user could see such a result without [quoted text clipped - 3 lines] > What user ever would? Users never see relations. They see various kinds of > reports (using the word "report" to mean anything users get to see). Not really. Users only see a *representation* of a report. The actual report cannot be seen. Well, that's not really true, either. Users only see photons reflected off of the paper the representation is printed on. Okay, that's a simplification. Since black ink *fails* to reflect, users really see the photons reflected off the part of the paper that doesn't have ink on it, and their brains reconstruct a mental image of the glyphs that make up the representation of the report of the relation. ------ All that goofy stuff I wrote above can be argued for, but why? It's much simpler just to point to a paper with "{1, 2, 3}" written on it and say "that is the set containing one, two, and three." Marshall >> > It seems a little doctrinaire to me. I can agree that the "result >> > isn't a [quoted text clipped - 6 lines] >> of >> reports (using the word "report" to mean anything users get to see). [goofy stuff snipped] > All that goofy stuff I wrote above can be argued for, but why? That was pretty much my point: Paul was thinking about possibly true facts but in an irrelevant framework. Roy >>>> It seems a little doctrinaire to me. I can agree that the "result >>>> isn't a [quoted text clipped - 14 lines] > > Roy Could be. Maybe one of Date's meanings is that no system that supports both ordering and some relational algebra is purely relational, even if the "pure" relational part of it could be isolated in some way from the rest! If so, calling the paragraph "doctrinaire" might be a bit of a slur. Also, I should say that my main interest in this stuff is nearly always implementation, this includes making sure that an implementation doesn't veer off in directions that aren't sound. When Codd himself talked of projection and "desired permutation" (admittedly he was talking of columns, not rows) I think an ordinary person could be forgiven for not separating the above two "parts" of a system. When it comes to implementations, I'd fault one that performed a presentation sort that wasn't "any desired permutation" (as Codd put it) but I wouldn't blame one that happened to echo some ordering that was already present in its "stored representation". > Could be. Maybe one of Date's meanings is that no system that supports > both ordering and some relational algebra is purely relational, even if > the "pure" relational part of it could be isolated in some way from the > rest! If so, calling the paragraph "doctrinaire" might be a bit of a slur. In order theory, an ordered set is a pair, consisting of a set and an order relation on that set. It's not a list or anything like that. The question of data structures only comes up when (in implementation land) we want to do the computation of putting the elements in some order. What that looks like is a design decision, and I don't see any reason why it can't look like a relation, at some level at least. In other words, it could be a relation {position, element} in the case of a total order, or {position, {element}} in a preorder. (It's not obvious what it should be for a partial order.) My sense is that Date's ideas about the solution space to this problem have been somewhat artificially constrained as a response to what SQL did. Marshall >> Could be. Maybe one of Date's meanings is that no system that supports >> both ordering and some relational algebra is purely relational, even if [quoted text clipped - 17 lines] > > Marshall Just shooting my mouth off about somebody I don't know, but regarding Date's thoughts being limited by products, I'd say if anything his writings about relations are more influenced by the adhoc methods of thirty or more years ago. I remember being ordered to attend many tutorials about IMS and the Cullinane, Cincom stuff, in fact they were actually brainwashing sessions about what were really single-application solutions. I don't agree with that stuff anymore, haven't for at least twenty years, but some of the "old order" (sorry for the pun), eg., adhoc sorts, still seems reasonable to me for most purposes, even though they depend on various arbitrary representations, such as fixed point decimals and so on. For me the possibility of using relations to describe how a sort is to operate doesn't come up much as a solution to an application problem. Actually, I wouldn't know how to do it either but I'd grant that exploring it might lead to some good insights that might be applied to other problems. >>Could be. Maybe one of Date's meanings is that no system that supports >>both ordering and some relational algebra is purely relational, even if [quoted text clipped - 15 lines] > this problem have been somewhat artificially constrained > as a response to what SQL did. Have you read the Lorentzos, Date, Darwen book on temporal data? It seems clear to me that Date understands the various orders: an interval type is defined by the interval type generator on the basis of a total order. As a data type, a relation has no implicit order. Other data types can define one or more orders, but data types are generally orthogonal to the relational model. Date is careful not to overspecify things. Leaving the RM as broadly applicable as possible is not a flaw or an oversight in my opinion. > >>>> It seems a little doctrinaire to me. I can agree that the "result > >>>> isn't a [quoted text clipped - 23 lines] > implementation, this includes making sure that an implementation doesn't > veer off in directions that aren't sound. In that case, the implementation of Oracle/Rdb should be of interest to you. The market penetration of Oracle/Rdb was limited by the fact that it was bound to the DEC platform. It's still a worthwhile example of an implementation to study for better or worse. If it will help at all, In Oracle/Rdb a view with an ORDER BY in it is read-only. This obviates at least some of the problems that theoreticians may have with the concept. > When Codd himself talked of projection and "desired permutation" > (admittedly he was talking of columns, not rows) I think an ordinary [quoted text clipped - 3 lines] > it) but I wouldn't blame one that happened to echo some ordering that > was already present in its "stored representation". Because of the way data is stored in a computer, what one typically gets for the representation of an unordered set is one of the possible permutations, where the permutation is intended to represent the entire class of permutations of the same unordered set. Ordering the set in some particular way does not change the unordered set that is thus represented. Unless you work around this problem, you run into a real problem when you try to implement the test for equality. If you are given two different permutations that represent the same unordered set, it can be a real bear to determine that the "equal sets" function should return true. > "Marshall" <marshall.spi...@gmail.com> wrote in message > [quoted text clipped - 6 lines] > > Roy I didn't get that; my bad. Marshall > I finally sprung for CJ Date's "Writings, 2000-2006" and skimming it, > noticed this point in chapter 10. It reminded me of the recent posts [quoted text clipped - 16 lines] > that all results are arbitrarily ordered and that those results can be > used to produce other results. It is a little doctrinaire. Oracle/Rdb (originially DEC Rdb/VMS) has always allowed ORDER BY in a view definition. No damage was done to the ability of the user to apply concepts of relational algebra to the task of getting useful work done. One could reference such a view inside another view, or in any other context where an unordered table would have been expected, and the results were logically equivalent to the result one would have obtained in the absence of the ORDER BY. Oracle RDBMS, by contrast, has always forbidden ORDER BY in view definitions. Other than satisfying some people's need for doctrinaire purity, the RDBMS users gained nothing by this restriction. > By analogy of separating the logical from physical implementation, if > you want to declare a separation of church and state, I'd think you'd > need to mention both. Not to tout SQL but I took the above to mean that > if a table were declared with an "INDEX", it shouldn't be allowed to > participate in expressions of the relational algebra, which seems > extreme and somewhat useless to me. I think you're extrapolating Date's remarks to a point that he might not agree with. A table with an index is still a table. BTW, the doctrinaire folks among us will insist that SQL does not have INDEX in the standard. But we all know that SQL databases use indexes to speed up queries. And many application programmers appreciate being told what indexes are present so that they can organize their queries for performance. The doctrinaire people will say that the programmers have no need to know index information, because it's not part of the logical model. In theory they are right. In practice, it helps to provide them with it. > "paul c" ... > [quoted text clipped - 9 lines] > definitions. Other than satisfying some people's need for doctrinaire > purity, the RDBMS users gained nothing by this restriction. ... > I think you're extrapolating Date's remarks to a point that he might not > agree with. A table with an index is still a table. [quoted text clipped - 9 lines] > > I'm not sure it's a stretch. I feel fairly confident about resorting to the Information Principle when asking if a result is correct according to an algebra. Admittedly, I lump aspects such as ordering and indexing as 'not applicable' when trying to apply the IP. If I may be allowed to discount those aspects for results, I don't see why I can't discount, ie., ignore them, for intermediate results. > > "paul c" ... > > [quoted text clipped - 30 lines] > discount those aspects for results, I don't see why I can't discount, > ie., ignore them, for intermediate results. If I'm reading the above correctly, I think you and I are on the same page. ... > I think you're extrapolating Date's remarks to a point that he might not > agree with. A table with an index is still a table. > ... I *suppose* it is but only in the interest of getting along with the rest of the world, most of whom I think would agree with you. It's just that I've never seen a formal definition of a table as a mathematical object, only as a graphical analogy to a relation, without respect to an algebra. Therefore, I don't understand how dbms's manipulate tables in a relational way. It seems to me that they must need to manipulate relations or relation variables instead. > ... > > I think you're extrapolating Date's remarks to a point that he might not [quoted text clipped - 6 lines] > object, only as a graphical analogy to a relation, without respect to an > algebra. I wouldn't call a table a "graphical analogy". I'd call it a logical data structure that serves to implement the storage of a relation. > Therefore, I don't understand how dbms's manipulate tables in > a relational way. It seems to me that they must need to manipulate > relations or relation variables instead. An SQL DBMS manipulates tables, not relations or relational variables. >>... >> [quoted text clipped - 10 lines] > I wouldn't call a table a "graphical analogy". I'd call it a logical data > structure that serves to implement the storage of a relation. If it is logical data structure, then it is an alternative to a relation, which is accurate in the context of SQL. >>Therefore, I don't understand how dbms's manipulate tables in >>a relational way. It seems to me that they must need to manipulate >>relations or relation variables instead. > > An SQL DBMS manipulates tables, not relations or relational variables. Exactly. (note, I changed the thread subject) > ... >> An SQL DBMS manipulates tables, not relations or relational variables. > > Exactly. I presume David could just as well have said "an SQL DBMS manipulates SQL tables". Found (finally) a copy of some draft or other of the SQL standard at http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt . Not sure how this copy might differ from the official ones, but anyway, here's some of what it says in section 4.9 which seems to be about "Concepts": > 4.9 Tables > [quoted text clipped - 4 lines] > The row is the smallest unit of data that can be inserted into a > table and deleted from a table. I'm wondering if there are popular SQL dbms's that follow this. For example, do any of them let me "insert", say, two "rows" that would be considered the same row if a table were a set of rows rather than a multiset of rows, giving, eg.: TableA: ColumnA 1 1 Also wondering about "i-th" values in rows. Does the above also mean that TableB: ColumnA ColumnB 1 2 is not equal to TableC: ColumnB ColumnA 2 1 (all other things being equal)? > (note, I changed the thread subject) > [quoted text clipped - 29 lines] > 1 > 1 All of the "major" SQL DBMS products permit storing more than one identical row in a table. However, they provide several ways the database manager can protect the database from that event. The simplest is to declare a primary key for the table. This will also, however, protect against inserting two rows that differ, but have identical primary key values. Most often, that coincides with the intent of the manager. > Also wondering about "i-th" values in rows. Does the above also mean that > [quoted text clipped - 9 lines] > > (all other things being equal)? The question is moot. Table B and Table C have different headers, even though they contain the same columns. a row with values {2, 1} inserted into TableB would be different from the row that's already there. I'm not nearly as critical of SQL as the relational apologists in this forum. However, I think it's a weakness of SQL that it can't seem to make up its mind whether to use position or name as way of connecting values in a list to their "location". I see this confusion all over the language, and I think it's possible to do better. I am expecting relational apologists to point out specific languages that actually do better. >>(note, I changed the thread subject) >> [quoted text clipped - 74 lines] > I think it's possible to do better. I am expecting relational apologists to > point out specific languages that actually do better. No apologies necessary. > >>(note, I changed the thread subject) > >> [quoted text clipped - 76 lines] > > No apologies necessary. Very funny. "Apologist" and "apology" have somewhat different meanings, at least in modern parlance. Perhaps "apologist" is archaic to begin with. Perhaps I should have said "proponents" rather than "apologists". >>> (note, I changed the thread subject) >>> [quoted text clipped - 81 lines] > > No apologies necessary. SQL problem - clearly > ... I am expecting relational apologists to > point out specific languages that actually do better. > Heh, sorry I'm not an apologist. > All of the "major" SQL DBMS products permit storing more than one > identical [quoted text clipped - 3 lines] > primary > key for the table. Sadly given that it is now considered "best practice" to blindly and automatically attach an entirely spurious unique "primary key" value to every row in a table, that would be entirely futile. > This will also, however, protect against inserting two > rows that differ, but have identical primary key values. Most often, > that > coincides with the intent of the manager. I wonder if you can be that confident about what goes on inside the head of the average (database) manager? Roy > > All of the "major" SQL DBMS products permit storing more than one > > identical [quoted text clipped - 7 lines] > automatically attach an entirely spurious unique "primary key" value to > every row in a table, that would be entirely futile. Why is is sad? What's wrong with declaring a primary key? Or do mean something else by "attach"? > > This will also, however, protect against inserting two > > rows that differ, but have identical primary key values. Most often, [quoted text clipped - 5 lines] > > Roy >> > All of the "major" SQL DBMS products permit storing more than one >> > identical [quoted text clipped - 11 lines] > Why is is sad? What's wrong with declaring a primary key? Or do mean > something else by "attach"? I mean precisely that: attach. Construct a tuple that represents your proposition, and then *attach* a synthetic value that is chosen precisely so that it is unique (usually the next sequential number in practice), and call that your primary key. And that makes me sad. Roy > >> > All of the "major" SQL DBMS products permit storing more than one > >> > identical [quoted text clipped - 16 lines] > that it is unique (usually the next sequential number in practice), and call > that your primary key. And that makes me sad. In that case, I guess you and I are on the same page. I said, and meant, "declare a primary key". Sometimes it's not possible to declare a primary key. More often than not, this means there has been a slip up in the analysis and design. > Roy >> I mean precisely that: attach. Construct a tuple that represents your >> proposition, and then *attach* a synthetic value that is chosen precisely [quoted text clipped - 7 lines] > key. More often than not, this means there has been a slip up in the > analysis and design. I was pretty sure we would turn out to be on the same page. But I fear the situation in the wild is worse than you think. You say "sometimes it's not possible to declare a primary key". I would fall down in a swoon if I met a progammer today who agreed it might impossible to declare a primary key. Virtually no one would recognize that situation because, as I said earlier, it is considered "best practice" to *always* introduce a spurious attribute for a synthetic value and call that the primary key. The problem you describe would simply never be revealed. None of this is to suggest that synthetic keys and surrogate keys do not have their place. They are essential, in their proper place. Roy ... > The question is moot. Table B and Table C have different headers, even > though they contain the same columns. a row with values {2, 1} inserted > into TableB would be different from the row that's already there. > ... Thanks for previous answers. This might see picky but I can't find "header" or "heading" in the text I have. Does the standard use some other term to talk about "headers"? > ... > > The question is moot. Table B and Table C have different headers, even [quoted text clipped - 5 lines] > "header" or "heading" in the text I have. Does the standard use some > other term to talk about "headers"? My use of the term "header" might be non-standard. > ColumnA ColumnB for Table B and > ColumnB ColumnA for Table C are what I was referring to as "headers". The table header, among other things, associates a Column name with a column position. The rows containing the data only have data values, one after the other. The position of a value in a row and the position of the corresponding column name in the header together, establish the linkage between the value and the name. > The table header, > among other things, associates a Column name with a column position. The > rows containing the data only have data values, one after the other. The > position of a value in a row and the position of the corresponding column > name in the header together, establish the linkage between the value and > the name. I think it is worthwhile to make a distinction here between syntax and semantics. Taking advantage of positions can be quite useful in syntax, but it seems to raise a lot of complex annoying issues if position is part of the semantics. Marshall ... >> Also wondering about "i-th" values in rows. Does the above also mean that >> [quoted text clipped - 14 lines] > into TableB would be different from the row that's already there. > ... Another quote I have here is from Codd, 1970: "The term relation is used here in its accepted mathematical sense. Given sets X1 , S, , . . . , S, (not necessarily distinct), R is a relation on these n sets if it is a set of ntuples each of which has its first element from S1, its second element from Sz , and so on.’ We shall refer to Si as the jth domain of R. As defined above, R is said to have degree n. Relations of degree 1 are often called unary, degree 2 binary, degree 3 ternary, and degree n n-ary. For expository reasons, we shall frequently make use of an array representation of relations, but it must be remembered that this particular representation is not an essential part of the relational view being expounded." I get the feeling that the SQL originators ended up describing Codd's description rather than what he was describing! I don't know if this is accurate historically but when I compare Codd's paragraph to the SQL standard section 4.9 the last sentence especially, makes me darned suspicious. OTOH the standard doesn't seem to mention Codd's name, nor the term "relation", so I guess it's possible that those originators might have been intending to make something quite different from his idea, eg., their idea of multisets/duplicates does seem to eliminate a user interpreting a table as a set of logical propositions. I guess also that means Codd's Information Principle doesn't apply in SQL. If I've got that right, they didn't intend to implement Codd's idea and I oughtn't to criticize SQL for being a poor implementation it, but I still think they could be criticized for not implementing it at all! ... > Oracle RDBMS, by contrast, has always forbidden ORDER BY in view > definitions. Other than satisfying some people's need for doctrinaire > purity, the RDBMS users gained nothing by this restriction. > ... That's interesting. Does it mean that ordering a view in Oracle RDBMS always requires some "programming" beyond that of the view definition? > ... >> [quoted text clipped - 5 lines] > That's interesting. Does it mean that ordering a view in Oracle RDBMS > always requires some "programming" beyond that of the view definition? IIRC, also SQLServer Dweeb > Oracle RDBMS, by contrast, has always forbidden ORDER BY in view > definitions. Other than satisfying some people's need for doctrinaire > purity, the RDBMS users gained nothing by this restriction. I disagree. Even in SQL, allowing ORDER BY in a view creates some tough problems. What would it actually mean for a view to be ordered? Presumably it would mean that at least some queries against that view should exhibit some pre-determined ordering. Now please define exactly which such queries against a view may and may not be ordered in that way (queries with joins, projections, aggregations, etc), including those with joins to other views which also have orderings of their own. Assuming you can define such a set of rules, how is the product supposed to behave when the user breaks the rules by issuing a query that cannot properly be ordered according to the view definition? Do we disallow such queries and raise an error or do we silently return a result set that isn't sorted? In the latter case, the user must be expected to understand when the rules are being broken and presumably must also know in advance what the ordering is supposed be. What benefit can the user possibly gain from this added complexity?  SignatureDavid Portas > > Oracle RDBMS, by contrast, has always forbidden ORDER BY in view > > definitions. Other than satisfying some people's need for doctrinaire [quoted text clipped - 9 lines] > projections, aggregations, etc), including those with joins to other views > which also have orderings of their own. Rather than try to answer your question in detail, I'm simply going to point you to Oracle/Rdb. It solved all the tough problems you have raised. It worked just fine in practice. If the user of a view specifies an order, it overrides the order built in to the view definition. If the view is used in a context that requires an unordered table, then it uses an unordered table. I do not know whether or not the Rdb optimizer was smart enough to omit a superfluous order by step. I don't care, either. And, if the view was used in the context of a report writer that only understood sequential data sources, the facility of providing an ORDER BY clause in the view definition was a godsend. > Assuming you can define such a set of rules, how is the product supposed to > behave when the user breaks the rules by issuing a query that cannot > properly be ordered according to the view definition? > Do we disallow such > queries and raise an error or do we silently return a result set that isn't > sorted? In the latter case, the user must be expected to understand when the > rules are being broken and presumably must also know in advance what the > ordering is supposed be. What benefit can the user possibly gain from this > added complexity? I refer you again to Oracle/Rdb and to its user community. You may think it's a problem, but it isn't. ... > I disagree. Even in SQL, allowing ORDER BY in a view creates some tough > problems. [quoted text clipped - 7 lines] > > Assuming you can define such a set of rules, ... My cut is that only the attributes that the view presents can be ordered, however I may be misunderstanding the question. Not to change it, let me ask a different question. If I make a union view of two base tables (or for that matter of two relations) that have a column/attribute A of type T, I presume the union view can't possibly have that same attribute A with a type T2 that is not equal to type T (I'm assuming here that the system doesn't support some kind of type inheritance). But say both of the "bases" have a key constraint such that two tuples t1 and t2 couldn't both be present in either "base". If I define no key constraint for the view, I would think it would be okay for both the t1 and t2 tuples to appear in the view. So the type is propagated, but the key constraint isn't. That seems a kind of unavoidable "rule" to me. I would think an ordering on a base table/relation is kind of similar to the key constraint and maybe there is an even stronger reason for it not to propagate, ie., if ordering is not part of the algebra, how could it? >>I finally sprung for CJ Date's "Writings, 2000-2006" and skimming it, >>noticed this point in chapter 10. It reminded me of the recent posts [quoted text clipped - 47 lines] > index information, because it's not part of the logical model. In theory > they are right. In practice, it helps to provide them with it. It would help more to provide an optimizer that obviates the need for such knowledge. > >>I finally sprung for CJ Date's "Writings, 2000-2006" and skimming it, > >>noticed this point in chapter 10. It reminded me of the recent posts [quoted text clipped - 47 lines] > > index information, because it's not part of the logical model. In theory > > they are right. In practice, it helps to provide them with it. I do not believe this is a programmer-to-dba dba-to-programmer debate but a user-buying-a-crappy-product to editor-who-sell-bullshit- products Considering that companies such as ORACLE, MICROSOFT and IBM which invested billion of dollars in developping product bill up to (100 000 $) per product license (ORACLE), knowledgeable users are perfectly in right to expect *some* quality from such vendors. My two cents... > I finally sprung for CJ Date's "Writings, 2000-2006" and skimming it, > noticed this point in chapter 10. It reminded me of the recent posts [quoted text clipped - 16 lines] > that all results are arbitrarily ordered and that those results can be > used to produce other results. I'm not happy with Date's treatment of this issue. It doesn't distinguish among the different kinds of order for one thing. (Preorder, partial order, total order.) For another, it seems to rely on some intuition about what ordering is, but he doesn't spell that out and I don't think it's even all that good. It seems he's assuming that 1) there's only one kind of order, 2) that an ordered set is isomorphic to a sequence, and 3) that it doesn't matter what one orders on. In fact, 1) there are at least three different kinds of order, 2) is only true for finite totally ordered sets and 3) a total order applied to a set of attributes that is not a superkey becomes a preorder for the relation. I would expect a thorough treatment of order within the relational model to address all of these issues. > By analogy of separating the logical from physical implementation, if > you want to declare a separation of church and state, I'd think you'd > need to mention both. Not to tout SQL but I took the above to mean that > if a table were declared with an "INDEX", it shouldn't be allowed to > participate in expressions of the relational algebra, which seems > extreme and somewhat useless to me. To me this is a separate issue. On the one hand we really want to be able to separate logical results from their performance characteristics. On the other hand, it's important that we have some mechanism for those writing queries to be able to have some model of performance, or anyway some way of considering performance when necessary. Virtually all languages don't do the first one very well; SQL is an exception here. I can't think of an analog in another language to being able to add an index and change the performance of a query independently from its semantics. That's a pretty cool feature. Marshall >> I finally sprung for CJ Date's "Writings, 2000-2006" and skimming it, >> noticed this point in chapter 10. It reminded me of the recent posts [quoted text clipped - 52 lines] > an index and change the performance of a query independently > from its semantics. That's a pretty cool feature. It is not a feature, it is the natural result of the abstraction, the separation of what from how that is fundamental to relational model and implementation. Dr. Dweeb > Marshall > I finally sprung for CJ Date's "Writings, 2000-2006" and skimming it, > noticed this point in chapter 10. It reminded me of the recent posts [quoted text clipped - 26 lines] > This is just one small paragraph and I'm not complaining about the > rest of the book - Am I mis-interpreting? I did not read the thread yet, ... Yes, you are misinterpreting. An index is a physical structure (not a logical one), used by the optimizer when determining the access path. No indices need to exist in theory for any table. This is in fact true on OracleRdb where order by, PRIMARY KEY, FOREIGN KEY etc are in fact enforced (albeit with rather sluggish results) without the need for CREATE INDEX statements. Data in a relation is unordered by definition, as CJD points out. That the database happens to return it in some apparent order is irrelevant. Relational databases normally (and correctly) provide no guarantees of order without the ORDER BY clause. While some products allow the definition of ORDER by in a view definition, and it is somewhat convenient for the DBA to deliver a VIEW to an app. programmer which forces ORDER BY (because app. programmers are too useless to know that they need to specify it and not rely on apparent order), it is in fact incorrect, as CJD notes. Dr. Dweeb |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.