7 ms·

I'm not sure what you mean by fit poorly in the relational model. ORDER BY, LIMIT/OFFSET have to do with presentation of data. So, although it's highly desired

by cico71 14y ago

I'm not sure what you mean by fit poorly in the relational model. ORDER BY, LIMIT/OFFSET have to do with presentation of data. So, although it's highly desired that a language based on the relational model supports them, they have nothing to do with the model per se. As for opaque keys, I thought the old debate about surrogate/artificial and natural keys was over years ago. The relational model has nothing to say about it, that would be like trying to make a model understand if facts are true in the real world or not. A key is a key. Moreover, last time I've checked transitive closure operators were defined for the relation model (people should not stop ad Codd papers, Date and Darwen wrote a lot of books, e.g. The Third Manifesto, extending on the original ideas). NULL is a completely different beast and this is the only real thing one can consider problematic. In some cases NULL just means the predicate for the relation is different because values for an attribute don't apply, so this is not really a problem, in some other cases we simply don't know the value when we are collecting information, and this is indeed a problem. Date follows Wittgestein that said we should remain silent about things we can't speak about i.e. we shouldn't collect incomplete information, Codd came up with I-marks, A-marks and n-valued logic, SQL collapsed everything into NULLs and 3-valued logic.

xi 14y ago
ORDER BY, LIMIT/OFFSET have to do with presentation of data. So, although it's highly desired that a language based on the relational model supports them, they have nothing to do with the model per se. By fit poorly I mean that you cannot express most real-world business inquiries using pure relational primitives without ORDER BY or LIMIT/OFFSET and that's why I think relational algebra is not usable per se. SQL fixed this problem by adding many non-relational constructs, but but without any sense of consistency or direction. I also strongly disagree that ORDER BY and LIMIT/OFFSET are presentational operations since I often use them not only for wrapping the outer SELECT, but also within correlated subqueries. To show some proof, here are a few queries which are hard or impossible to express in relational algebra: 1. Show the blog post with the largest number of comments [^]. 2. Show the tags associated with the blog post with the largest number of comments. 3. For each blog category, show the 3 top blog posts by the number of comments. [^] If more than one exist, pick the latest. NULL is a completely different beast and this is the only real thing one can consider problematic. I think NULL is only hard because relational model is a wrong way to look at the data. If you see an entity attribute not as a column of a tuple, but as a function from an entity set to some value domain, the fact that the attribute is nullable just means that the function is not total. There is a well developed mathematical apparatus for partial functions, in which NULL becomes a bottom value injected to the value domain, and tri-valued logic is simply a monotonic extension of regular Boolean operators.
- MattRogish 14y ago
  As long as you can represent your query with predicate logic and not violate set theory (or other relational tenants), it's perfectly "relational". It's important to note that folks have figured out how to extend Codd's original relational algebra with stuff like aggregation. As the OP mentioned, "Relational" doesn't mean "What Codd wrote in a single paper back in 1969". It has continued to evolve, both with Codd's direct involvement and from successors like Date, Darwin, and Pascal. Codd wasn't an all-seeing, all-knowing data-management demi-god - but his general theory of relational database management and the core tenants are still super awesome. Extensions to it, as long as they don't violate the RM, are just as valid as Codd's original work. That means #1 is totally relational (as an aside, you don't need ORDER BY or LIMIT for it either). Indeed, relational algebra supports aggregations (http://en.wikipedia.org/wiki/Relational_algebra#Aggregation http://en.wikipedia.org/wiki/Relational_algebra#Aggregation). It's important to separate the query language from core RDBMS theory, as the two are orthogonal. Codd suggested Relational Algebra as a reference language but never intended for it to be the only way to communicate with a RDBMS. See CJ Date's excellent discussion on ORDER(BY): http://books.google.com/books?id=WuZGD5tBfMwC&lpg=PA163&ots=WeqU4pFMMA&pg=PA163#v=onepage&q&f=false http://books.google.com/books?id=WuZGD5tBfMwC&lpg=PA163&... EDIT: It even seems folks have figured out how to make "LIMIT" relational operator: http://stackoverflow.com/questions/10229535/relational-algebra-for-limit-operator http://stackoverflow.com/questions/10229535/relational-algeb... I have not read the paper, so I cannot discuss the validity of the approach.
  - xi 14y ago
    That means #1 is totally relational (as an aside, you don't need ORDER BY or LIMIT for it either). I would love to see it. Yes, you can do it in SQL, but I'd say it's not easy at all without ORDER BY and LIMIT or windowing functions and I don't know if you can do it in Tutorial D. For the reference, #1 is: Show the blog post with the largest number of comments. If more than one exist, pick the latest. The schema is: post(id integer, created timestamp) comment(id integer, post_id integer) See CJ Date's excellent discussion on ORDER(BY) I read it and the book as well, but I wouldn't call it excellent. What I read there is a reluctant admission of failure to incorporate an important operation to his query model. I see no attempt to analyze why it doesn't work or adapt the query model to make ORDER a regular operation.

by cico71 14y ago

xi 14y ago

ORDER BY, LIMIT/OFFSET have to do with presentation of data. So, although it's highly desired that a language based on the relational model supports them, they have nothing to do with the model per se. By fit poorly I mean that you cannot express most real-world business inquiries using pure relational primitives without ORDER BY or LIMIT/OFFSET and that's why I think relational algebra is not usable per se. SQL fixed this problem by adding many non-relational constructs, but but without any sense of consistency or direction. I also strongly disagree that ORDER BY and LIMIT/OFFSET are presentational operations since I often use them not only for wrapping the outer SELECT, but also within correlated subqueries. To show some proof, here are a few queries which are hard or impossible to express in relational algebra: 1. Show the blog post with the largest number of comments [^]. 2. Show the tags associated with the blog post with the largest number of comments. 3. For each blog category, show the 3 top blog posts by the number of comments. [^] If more than one exist, pick the latest. NULL is a completely different beast and this is the only real thing one can consider problematic. I think NULL is only hard because relational model is a wrong way to look at the data. If you see an entity attribute not as a column of a tuple, but as a function from an entity set to some value domain, the fact that the attribute is nullable just means that the function is not total. There is a well developed mathematical apparatus for partial functions, in which NULL becomes a bottom value injected to the value domain, and tri-valued logic is simply a monotonic extension of regular Boolean operators.

MattRogish 14y ago
As long as you can represent your query with predicate logic and not violate set theory (or other relational tenants), it's perfectly "relational". It's important to note that folks have figured out how to extend Codd's original relational algebra with stuff like aggregation. As the OP mentioned, "Relational" doesn't mean "What Codd wrote in a single paper back in 1969". It has continued to evolve, both with Codd's direct involvement and from successors like Date, Darwin, and Pascal. Codd wasn't an all-seeing, all-knowing data-management demi-god - but his general theory of relational database management and the core tenants are still super awesome. Extensions to it, as long as they don't violate the RM, are just as valid as Codd's original work. That means #1 is totally relational (as an aside, you don't need ORDER BY or LIMIT for it either). Indeed, relational algebra supports aggregations (http://en.wikipedia.org/wiki/Relational_algebra#Aggregation http://en.wikipedia.org/wiki/Relational_algebra#Aggregation). It's important to separate the query language from core RDBMS theory, as the two are orthogonal. Codd suggested Relational Algebra as a reference language but never intended for it to be the only way to communicate with a RDBMS. See CJ Date's excellent discussion on ORDER(BY): http://books.google.com/books?id=WuZGD5tBfMwC&lpg=PA163&ots=WeqU4pFMMA&pg=PA163#v=onepage&q&f=false http://books.google.com/books?id=WuZGD5tBfMwC&lpg=PA163&... EDIT: It even seems folks have figured out how to make "LIMIT" relational operator: http://stackoverflow.com/questions/10229535/relational-algebra-for-limit-operator http://stackoverflow.com/questions/10229535/relational-algeb... I have not read the paper, so I cannot discuss the validity of the approach.
- xi 14y ago
  That means #1 is totally relational (as an aside, you don't need ORDER BY or LIMIT for it either). I would love to see it. Yes, you can do it in SQL, but I'd say it's not easy at all without ORDER BY and LIMIT or windowing functions and I don't know if you can do it in Tutorial D. For the reference, #1 is: Show the blog post with the largest number of comments. If more than one exist, pick the latest. The schema is: post(id integer, created timestamp) comment(id integer, post_id integer) See CJ Date's excellent discussion on ORDER(BY) I read it and the book as well, but I wouldn't call it excellent. What I read there is a reluctant admission of failure to incorporate an important operation to his query model. I see no attempt to analyze why it doesn't work or adapt the query model to make ORDER a regular operation.