Re: sql views for denomalizing

Marshall Spight wrote:
> dawn wrote:
> > Marshall Spight wrote:
> > > >
> > > > list students with courseDept = "Math" and with every major <> "Math"
> > >
> > > Ha! I've been trying to get examples of the value of MV out of
> > > you for ages, and at last you slip up and reveal one!
> >
> > Such serendipity. I'm sure I've provided these in the past when I
> > started chatting on cdt. People likely just showed how it was possible
> > with SQL (which I knew) and that was that.
>
> None of the examples I've seen from you in the past have
> been this sophisticated. I'm happy to believe you've posted
> similarly rich queries elsewhere or at other times, but that
> doesn't help me if I haven't seen them. I have tried to get
> illustrative examples from you in the past and been frustrated.

apologies. You might also be interested in the slides at http://tincatgroup.com/mv/trilogy.html that I pointed Lauri to.

> I'm interested in the issues that come up in querying nested
> data, and since you're often going on about the value of
> nested data, I would expect you'd have illustrative examples
> ready-made.

Logically, queries using the query language (compared to using procedural code) on nested structures look just like queries that are not on nested structures. Everything in pick is a query of a single entity, asking about "fields" found in a logical dictionary for that file. A dictionary for a file has all of the fields whose values you can retrieve. Some of these are defined as being derived by taking a foreign key (list) and retrieving a field from a record in another file by way of that foreign key.

>
> > > Let me make sure I understand this: students have many courses;
> > > courses have a courseDept; students have many majors; majors
> > > is a string. Yes? So if we represented this in SQL, we'd use
> > > three tables, Students, Courses (with FK to Students, and with a
> > > courseDept) and Majors (with FK to Students, and major.)
> >
> > Since I've done this with a live system, it was lots more relational
> > tables and also more pick files than that,
>
> Of course. We're just doing illustrative examples.
>
> > > The
> > > Pick version of this would have a multivalued attribute major
> > > inside Students,
> >
> > Since a major is an attribute of a student's program, rather than a
> > student directly (they could be both an undergrad with one set of
> > majors and graduate with another, for example), major would still be
> > modeled in a separate physical "relation" but a virtual field pointing
> > to this list of majors would make it look the same to the user as if
> > the field were in the student relation (file).
>
> Ah! Interesting. Do virtual fields have to be set up ahead of time,
> or can they be made up on a per-query basis?

This differs by pick implementation, but the one I use permits most types of virtual fields to be specified in the query, while all types can be stored as part of a file description.

One type of virtual field is strictly a display or representation definition, perhaps limiting the number of charaters displayed or right justifying a field, or changing its label for output. These are often done inline. Others have operators in them and still others incorporate subroutines of DataBASIC code.

> > > and also a multivaled attribute inside Student
> > > that listed the courses that student was taking. Please correct
> > > or confirm.
> >
> > again, this would be a virtual field pointing to where the information
> > about courses (likely associated with the term, for example) would be
> > found. The "current" part would pull off today's date to find which
> > terms were current, all behind the scenes as part of the virtual field
> > logic.
>
> So, this virtual field stuff just sounds like views.

Similar in that they are logical views of the data. Different in that if a user is looking at data about this entity, all appropriate vocabulary can be specified to this view and there will never be any new rows (records) added. So, if I ask a question about students, then I will get a row for each qualifying student in any view created for students.

> The more I learn about pick, the closer to relational it seems.

Pick people tried to claim that during the relational boom years and there is a lot of set algebra that takes place, particularly with what are called "select lists" or "savedlists". But when I first saw pick in the Prime Information product, I deemed it to be a file system and not a database. I was hasty in that judgement -- but it definitely isn't similar to a SQL-DBMS, both in terms of what it does well and what it does poorly.

> "Files" <-> Relations

yes, ish

> records <-> tuples

yes

> Virtual fields <-> views

ok, sortof

> pick better than SQL:
> nested relations. This seems big to me

yes, also the 2VL

> pick worse than SQL:
> no static typing (but SQL's type system is fairly weak anyway)

while I think there are significant benefits to strong typing, the typical SQL type system provides the stick without the carrot, it seems, so weak typing in the dbms is better. I'm still pretty wishy-washy on typing, I realize.

> only one key

It doesn't really turn out that way. The key may be multiple parts, each of which can have a separate name, but together are tossed into the ID for the record as well (under the default name for the key in the given implementation)

And one can access data through alternate keys, of course.
>
> Also, it seems pick is more closely tied to a single implementation
> model, but that just seems like an accident of history rather than
> a model issue.

If there is some reason that a data repository requires an API that is used by more than one top level organization, then that API needs more constraint handling than a pick CRUD API has. If all applications using the dbms api for crud are owned by the same top level company, then the company has the ability to manage that, with more flexibility than if it were a sql-dbms. That flexibility has trade-offs.

> I'm still not clear on how pick handles many-to-many.

multivalued lists of foreign keys in each file.

> Back to the example query, I want to make sure I'm reading it
> correctly. courseDept is supposed to be multivalue, right?

yes

> So I expect the correct interpretation of 'with courseDept = "Math"'
> is actually 'with *any* courseDept = "Math"'

yes, or 'with some ...' or 'with a ...'

> where I use any
> to indicate "at least one" the same way you use "every" to
> mean all of them.

yes

>
> > > This seems like a really good example query to study.
> >
> > Any time I have brought up this simple case of multiple one to many's
> > in any discussion (face to face), I get both the "that's just a star
> > schema" responses ('cause my user is gonna write one of those, eh?) or
> > the SQL code that performs the same query.
>
> Sometimes I get a little frustrated talking with you, because so
> much of the conversation between you and me ends up being about
> you and some other person you had a similar conversation with in
> the past, and you seem to assume that I'm going to respond the
> same way as those other people did. I might; I might not. I'm
> certainly not going to get any of the "benefits of dialog" we
> we referencing earlier in the week if you don't actually *have*
> the dialog with me as well, though.

Agree. I don't always remember who said what, however, and might assume something has been covered when it hasn't been (just as I sometimes think it hasn't been when I've had the exact same conversation before).

> So I would like to request that you leave out the part about
> times you have had discussions with other people, or even other
> times you've had discussions with me.

I use it only as context for understanding a question or how an answer might be interpreted.

> I try to avoid rhetorical
> questions (not that I succeed 100%, but I try) so when I ask
> a question, it's often an actual question that I really want
> to know the answer to. I try to work step-by-step, to make
> sure I understand each little piece and nothing is getting
> glossed over.

as do I

> If you answer the question you believe is behind
> the question, usually that's not helpful to me.

I'll always answer the way I am understanding the question, and I always suspect that I'm not quite understanding any given question. --dawn

>
> Marshall
Received on Sun Jul 31 2005 - 22:44:14 CEST