Re: sql views for denomalizing

Marshall Spight wrote:
> dawn wrote:
> >
> > Querying for all students currently taking a math course who are not
> > math majors is non-trivial in SQL (due to the negation along with
> > multiple 1 to many relationships, if I'm thinking clearly) and would be
> > trivial in both datatrieve and pick:
> >
> > 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.

> 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, but the query was like this for pick. Pick only sees one entity at a time and views the rest of the world through that entity, thereby simplifying the query language, but requiring defining any attribute you might want to all entities through which you might want to see them (there are tradeoffs, obviously, but users sure like the simplicity of queries).

> 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).

> 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.

> 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. Then the statement is that the problem is left for the reporting tools, so the user doesn't have to feel the pain. But I consider the developer a user. Thanks for hearing. cheers! --dawn

>
> Marshall
Received on Sun Jul 31 2005 - 01:07:49 CEST