Re: Multiplicity, Change and MV
Date: Tue, 11 Apr 2006 17:08:17 +0300
Message-ID: <e1gd7m$r0p$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.
Me also.
> Now, if multiplicity occurs due to a change in teaching practices, the
> db-structure obviously has to change in order to accomodate this. For
> example, below the teacher-course relationship has evolved into a
> many-many relationship, so producing a new relation, with a primary key
> of (teacher_id, course).
If the relation Teaches was {0,1} to {0,1} and not 1-1 then the design is
bad.
This is why you said contrived ?
The 0 part give us trouble also. :-)
> Courses = { (name:French), (name:English), (name:German) }
> Lecturers = { (id:1, name:Tom), (id:2, name:Bob) }
> Teaching = {
> (teacher_id:1, course:French),
> (teacher_id:1, course:German),
> (teacher_id:2, course:English),
> }
> Apologies for this basic (and contrived) example, but hopefully its
> sufficient to show that change will have knock on effects to the
> queries of extant applications which interact with the database.
Even if only the degree of some candidate key change, it is enough to cause us trouble. :-)
> 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. I find it jarring that a change in multiplicity has
> introduced a new relation - perhaps it should have existed in the first
> place even with the 1-m relationship?
I think it existed before as Lecturers {id, teaches} Rename (id as teacher_id, teaches as course). :-)
> Either way, going against my
> instincts, below I'm considering MV approaches, of which I can
> determine two:
> --------------------------
> 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 )
> }
>
> Immediately I've lost the added functionality of having a teaching
> relation -
Indeed. Where is the teaching relation ?
> but that's not a current concern because *at the moment* I'm
> focusing on preserving query form.
How the query form looks like ?
> A greater concern is that a set (or
> list if one prefers) has now materialized where previously none
> existed. I am currently unclear how to tally this with the underlying
> predicate logic that is being encoded
What predicate logic would you use for many sorted relational calculus for example ?
> - there is also the added
> complications of how to interpret equality comparisons for concepts
> such as WHERE clauses given the introduction of aggregates.
> --------------------------
> MV - Multiple-attribute approach:
> --------------------------
> Courses = { (name:French), (name:English), (name:German) }
> Lecturers = {
> (id:1, name:Tom, teaches:French, teaches:English),
> (id:2, name:Bob, teaches:French, teaches:German)
> }
>
> Here I do not have the concern of sets/lists appearing from the ether,
> but I have lost the guaranteed regularity of the relation (that all
> tuples are of the same cardinality)
Nulls :-)
> - however I am currently unsure as
> to whether this is actually a concern outside of tabular visualization.
Visualization, indeed. :-)
Other are possible as shown by Codd in its RM/T paper.
> Further a tuple is obviously no longer a function mapping given the
> field name repetiton, yet it is still a mathematical relation.
What is the degree of the relation ? :-)
> More of a concern is that with the loss of functional mapping it
necessarily
> loses the concept of functional dependency - although I believe that I
> can still state conditional/biconditional dependencies.
???
Which "teaches" do you pick ?
> In the area I work in, changes in cardinality and table schema are
> rife. Scientific data, especially the way we want to contextualize and
> record it, is changing constantly (this is one of the reasons that the
> hideous EAV model got so much funding).
If the schema does not change we could have used only the plain old files with a transactional system, not a RDBMS.
> Perhaps I just have to accept
> structural and hence query change via the RM, but perhaps there is a
> solution that will suit my theoretic demands as well as being
> practically effective?
Is there any chance that the solution is RM ? Received on Tue Apr 11 2006 - 16:08:17 CEST