Re: Dreaming About Redesigning SQL

"Marshall Spight" <mspight_at_dnai.com> wrote in message news:<h%Qob.56961$mZ5.338904_at_attbi_s54>...
> "Mike Preece" <michael_at_preece.net> wrote in message news:1b0b566c.0310312141.67b3f186_at_posting.google.com...
> > In the relational model, as in the Pick model, if everyone had just
> > one unique phone number (or none at all), the phone number could be
> > considered to be an attribute of the person in exactly the same way
> > that their name or date of birth could be.
> Yes.
> > If it becomes necessary to record more than one phone number against a
> > person, does the relational model dictate that the logical schema must
> > change?
> Yes. Doesn't the Pick model as well? Isn't the size constraint on the
> number of phone numbers part of the schema?

It is tempting to jump straight in and say "No. It doesn't. Pick doesn't have size constraints on the number of values in an attribute. It will happily allow one, none or a dozen phone numbers in the same attribute.".

Schema.

OK. Here we get into the potentially tricky area of semantics. I have to concede that my use of the words "logical schema" was ill-chosen for the point I wished to make. The schema is much more than simply the organisation (normalisation) of data into (tables, columns and rows) or (files, items, attributes, multivalues and subvalues) or (classes and instances of a class) or (entities and associations) or what-have-you. It can include integrity constraints of various kinds, field validation, referential integrity enforcement, inherited properties, etc., etc., etc., depending on who you're discussing it with. It's not just the organisation of related data - it includes rules to ensure data integrity and, to varing degrees, its correct use.

In answer to your question then, I have to say that "Yes. The schema will have to change." The rules concerning how the attribute is used will have to change. If there are input fields prompting for a phone number there will now have to be changes to allow a phone number to be added and deleted as well as updated. Programs and "dictionaries" in foreign files (tables) that get the phone number for a Person will have to be checked to ensure they can handle more than one phone number. This is all part of the overall schema - the way the data is used.

We would not have to change the existing data or its organisation into sets in any way. The phone number attribute will allow multiple phone numbers to be added. Nothing about the file has to change to allow this - just some of the ways in which the phone number attribute is used. Many enquiries and reports will continue to function perfectly unchanged regardless of the fact that the attribute has become multivalued.

> > I don't know the correct terminology, but would the list of
> > phone numbers for a person have to be held separately in its own table
> > and "referred to"?
> This is standard practice, but I'm not sure if "have to" is the right way
> to say it.

OK. To recap. We began this discussion with a PersonsPhones file. When we have a many:many relationship like that, does relational theory dictate that the data is stored in a separate table?

> > This doesn't have to happen in Pick. An attribute can be multivalued.
> Are you considering an attribute changing from single-valued to multivalued
> as NOT being a schema change? Or does Pick not have a way to distinguish
> between the two cases?

As to your first question - as I said before, I think "schema" is too broad a word really, but there is nothing to prevent an application suddenly starting to use an attribute that was single valued as multivalued. You raise a good point in your second question. In itself, Pick does not make any distinction between attributes that are empty from those that are not, those that are single valued from those that are multivalued or those that can contain subvalues within multivalues. If you enquire on a file the output will be formatted according to the nature of the data in the file. Multivalued data in a columnar listing will occupy multiple rows where associated single values will occupy one. Similarly, subvalues will occupy multiple rows for each multivalue. Perhaps you might like to look at the example I gave in the "How would you approach this?" thread.  

> > Do you agree that a list of a person's phone numbers can and should be
> > considered an attribute of the person - just as a single number is -
> > provided an acceptable means exists to access all "rows" containing a
> > given phone number?
> Roughly speaking, this works for me, but I think it hinges on
> the definition of the word "attribute."

I guess I'd equate it to a tuple - but I'm not 100% sure what a tuple is anymore. Can it have multiple values or is it only allowed to have a single value although that single value can be a collection (array) of multiple values. It all seems a bit muddled to me at the moment - although I am trying (as I'm sure some of you CDT regulars will agree).

From the Pick D3 manual:
"Attribute:
A field used to store data within an item. Attributes represent the highest level data component within an item in a file. An item can contain any number of attributes, each of which can contain any number of values, which in turn can be composed of any number of subvalues."

Also, attributes can be seen as sets within items within files, and as sets containing one, none or multiple values or sets of values.

There is a whole section in the Pick manual dedicated to "attribute defining items".

I'm not exactly sure of the definition in relational theory. Could you perhaps provide one?

Mike. Received on Sun Nov 02 2003 - 03:43:07 CET