Thank you for this response, Jay, and your other long one. They seem very well informed and helpful to the general practitioner. And "Database in Depth" is indeed a great book, and should be read by anyone wanting to understand databases; despite the name, it is actually quite approachable.
Regarding "atomic" et al, my own impression of what Date says is slightly different. My perception is not that he tries to define "atomic" and then gives up, but rather that he emphasizes from the start that practically speaking there is no such thing. That is ... One main point in support of relation-valued attributes (in terms of which all other kinds of collections can be represented, such as arrays) is that rejecting them on the basis of only wanting atomic or scalar values doesn't make sense, because any value can be represented as a collection of other values. For example, a string can be a collection of characters, or a number can be a string of digits, etc. And so, forbidding collection values is making a very arbitrary limitation. This means that the simplest or more pure solution is to allow any value at all as an attribute value. I won't repeat what you said about relations without RVAs being losslessly convertible to relations with them, and back again likewise (it is like taking the math expression "5x + 10y" and changing it to "5 * (x + 2y)", then changing it back). But this is what I was referring to in my original message in the thread where I said using the 2 tables was logically equivalent to using one and making work_history an RVA. In regards to 1NF, as you say, relations are already in 1NF by definition, and only SQL tables are capable of not being in 1NF, since in the general case they are not relations. As far as I know the main distinction here is that SQL allows duplicate rows in a table, and so a SQL table is like a bag, while a relation by definition is a set and never has duplicate tuples (rows). In that respect, having a 1NF table in SQL is as simple as having a primary key or unique key on that table. But even then, some SQL rowsets may have duplicate rows depending on how a SELECT is built, while relations never would. Now, even if SQL isn't the same as the relational model, it has a lot in common, and to a large degree if one uses SQL the right way its behavior can be quite close to the relational model, and more correct and trouble-free. I highly recommend Chris Date's newer book, "SQL and Relational Theory: How to Write Accurate SQL Code" (2009), http://oreilly.com/catalog/9780596523084/ , which talks about how to use the SQL DBMSs you already have in the most effective way possible, in regards to getting the behavior you want error free. And yes, that is very applicable to SQLite specifically. -- Darren Duncan Jay A. Kreibich wrote: > On Sun, Nov 01, 2009 at 01:23:52PM -0800, CityDev scratched on the wall: >> Darren Duncan wrote: >>> Or at least it is in the version of the relational model >>> that allows non-scalar attribute values, but that is the one that Chris >>> Date et al, as well as myself ascribe to. >> I didn't read this through but I recall Chris Date defining a relational >> database as formed from relations normalised to 1NF. First Normal Form >> basically means no substructure ie each column is a single value from a >> scalar domain. > >> To say it would be nice to have structured data is ignoring >> two things: i) we've got this for just fine without it, and ii) SQL is a >> first-order language so it can't easily handle substructure. > > Let me address these two points first. As for "i", then please > explain to my why nearly every SQL database supports array values? > I'm not sure it is in the standard, but it is so common it might > as well be (for those wondering, no, SQLite does not support arrays). > The answer for "ii" is easy: SQL is not the Relational Model, so the > only point you might be making is that SQL makes for a poor relational > language. > > Now back to your first paragraph. This is the more interesting one. > > Executive Summary: Multi-valued attributes (column types) > are not allowed in the Relational Model. Relational-Valued > Attributes (tables in tables) are OK, however, because they > don't count. > > Now a few pages on why.... > > The term used by Date (and Codd) is "atomic", not scalar. Of course, > this is generally meaningless, as it is very hard to define what > makes a value "atomic." Are text values atomic? The SQL standard > includes a number of functions to extract, match, and manipulate > sub-strings. What about integers? SQLite includes bit operations > that can be applied to integer values. Does that make them non-atomic? > > In "Database In Depth", Date eventually just gives up trying to > define "atomic" in a formal sense, says it cannot and should not be > done. In his writings, Date points out that even atoms are not > "atomic," and can be broken down into quarks-- but that the idea of > atomic values is still a good and powerful one, even if it is inexact. <snip> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
