Re: Multiplicity, Change and MV

"x" <x_at_not-exists.org> wrote in message news:e1gfct$7l6$1_at_emma.aioe.org...
>
> "JOG" <jog_at_cs.nott.ac.uk> wrote in message
> news:1144686256.966117.179590_at_e56g2000cwe.googlegroups.com...
>
>> Change bothers me. Especially in database schema, and specifically when
>> we want to accomodate change in the cardinalities of the relationships
>> we are modelling.
>
> What schema ?
>
>> This is what I'd like to focus on here - I am interested in exploring
>> how one might reduce the dependency between query-form and
>> db-structure.
>
> It occured to me that perhaps a command line interface would be better for
> the task. :-)
>
>>--------------------------
>>MV - Set/List value approach (Pick?):
>>--------------------------
>>Courses = { (name:French), (name:English), (name:German) }
>>Lecturers = {
>> (id:1, name:Tom, teaches: {French, German} ),
>> (id:2, name:Bob, teaches: English )
>>}
>
> How about
> Lecturers = {
> (id:1, name:Tom, {teaches: {French, German}, play {tennis}} ),
> (id:2, name:Bob, {teaches: English, play{violin}} )
> }
>
>> Immediately I've lost the added functionality of having a teaching
>> relation - but that's not a current concern because *at the moment* I'm
>> focusing on preserving query form. A greater concern is that a set (or
>> list if one prefers) has now materialized where previously none
>> existed.
>
> What if something else materialize suddenly ? :-)
>
>

Ok, I'm gonna take a stab at this from a 'classic MV' perspecitve, ala Pick. (wanted to yesterday, but waited until some SQL responses percolated a bit) So here goes...

First off, the schema is no problem at all and modifying it later also poses no threat to data integrity, consider the following:

The general concept relys upon the granularity of the data requirement, that is to say, how much detail do you want to store about each element? In my experience we should allow for as much detail as possible, so I would recommend building a seperate MV file (SQL table?) for each primary element.

"Courses" would be a file/table with primary elements having id's identifying them independently such as:

Courses = { (name:French101), (name:English105), (name:German203) }

Within each of these would be placed elements that relate to that particular course, such as , prerequisites, class locations, lecturers, etc.

Another file/table for "Lecturers" with primary elements similar to courses, but pertaining to lecturers such as:

Lecturers = {
(id:1, name:Tom, {teaches: {French, German}, play {tennis}} ), (id:2, name:Bob, {teaches: English, play{violin}} )  }

In "Pickdom" we don't usually concern ourselves with all of the sintactical delimiter boardering ({}) et.al. so I'll model it in a more conventional Pick-like form:

---
File "Courses"

ID - French101
LECTURER - Tom
ROOM - Bldg A/Rm 106
PREQUESITE - (null)

ID - German105
LECTURER - Bob
ROOM - Bldg B/Rm 200
PREREQUISITE - German101

---

File "Lecturer"

ID - Tom
COURSE - French101
PLAY - Tennis
LIKES - Wine

ID - Bob
COURSE - German105
PLAY - Violin
LIKES - (null)

---
etc...

Now to extract the meaningful data we would construct a sentence such as; 
SELECT LECTURER IF COURSE = "French101"  -  this will return a single result 
of Lecturer "Tom"

If it turns out that Tom also qualifies to teach German105, then "German105" 
would be added to his record such as:

ID - Tom
COURSE - French101]German105
PLAY - Tennis
LIKES - Wine

And then if we execute a sentence like; SELECT LECTURER IF COURSE = 
"German105"  -  this will return a result containing BOTH Lecturer "Tom" and 
"Bob" because they both contain the value of "German105" in their Courses 
attribute in their data record (column).

As time goes by and we discover there are more things we need to track 
concerning the lecturers we simply add attributes to those records requiring 
them (such as the "PLAY" and "LIKES" attributes shown above)

By adding what is called a "Dictionary" item to describe these attributes, 
they can then be extracted with an "English" sentence as described above. 
In any event, all attributes can be exposed to a program that reads (or 
writes) these records regardless of the existence of a dictionary item 
describing them.  And in the most recent versions of MV databases 
(Ladybridge/openQM, IBM/Universe, etc) a trigger can be employed to maintain 
data integrity to prevent read/write/delete operations from executing 
without proper structure, subject to programmer's (or data base admin) 
control.

In a similar vein a reference dictionary item can be constructed to link to 
the Course file for the "PREREQUISITE" item to the lecturer file to allow 
for the following: SELECT LECTURER IF PREREQUISITE = "German101" and again 
we would see two lecturers selected, because both Tom and Bob have lecturer 
records containing courses linked to prerequisites of "German101".  Notice 
that the value "German101" does not exist in the lecturer file, but because 
the course "German105" contains this as a prerequisite, then the lecturers 
having courses that in turn contain this value, they will be returned.

Much, much more can be done, but I've run too long already.

NOTE: JOG - If change concerns you and you really need a 'real world' 
capability to do what you say, you owe it to yourself to take a look at 
openQM or other equivalents and model your requirements.  I'm confident that 
you will be pleasantly surprised with how quickly you can achieve an 
operational result that is fast, functional, and extensible.

BFaux -