When appropriate to violate 1st normal form?
Anthony W. Youngman
Fromm <stephe...@verizon.net> writes
>When might it be appropriate to violate 1st normal form?
>
>For example, I'm setting up a table for bibliographic data. Since the
>number of authors can vary, the only solutions coming to my mind are:
>(a) Have a multi-valued "author" field;
>(b) Have N author fields, where N is larger than the forseen number of
>possible authors on any document;
>(c) Have a separate relation _authors_of_documents, which would be a
>many-to-many relationship between the bibliographic entries and some
>other entity of authors.
>
>(b) is extremely unattractive. What are the merits of (a) vs (c)?
>
>A similar example is "keywords for abstracts." Suppose I have a table
>with text summaries of documents. (One field is a large text data
>type for the abstract/summary itself.) The number of keywords is
>variable, so we have the same situation as with authors of documents.
>
>Since I don't have much experience in (relational) DBs, my gut
>reaction at first was "oh, of course the multivalued attribute
>solution (a) is better." But having read up more, my gut reaction now
>is "no, never ever have a multivalued attribute."
Well, it might be a pig in a relational database. But why not use a
multi-value database instead.
Oh, and ignore Celko's remark about integrity. Multi-value doesn't have
integrity problems as a consequence of violating first normal. It may
have them as a result of bad design, but that's a different issue!
If I was coding your setup AND I JUST WANTED A LIST OF AUTHORS, I would
use multi-value. If I wanted to store information about the authors, I
would still use multi-value, but I'd put "author" in its own table, and
use a multi-value key.
>
>Are there any reasonable conceptual guidelines for this design issue?
>
Don't be blinkered by relational. x-posted to comp.databases.pick. They
might give you some guidelines :-)
Cheers,
Wol
--
Anthony W. Youngman - wol at thewolery dot demon dot co dot uk
Witches are curious by definition and inquisitive by nature. She moved in. "Let
me through. I'm a nosey person.", she said, employing both elbows.
Maskerade : (c) 1995 Terry Pratchett
--CELKO--
have integrity problems as a consequence of violating first normal. It
may have them as a result of bad design, but that's a different issue!
<<
Let me extend SQL a bit and allow a multi-valued column. Can you
assign an obvious reasonable meaning to any of these things?
CREATE TABLE Foo
(key_col MULTI-VAL-CHAR(100) NOT NULL PRIMARY KEY,
junk INTEGER NOT NULL);
-- is this a PK constraint violation in NFNF?
INSERT INTO Foo VALUES ('a,b', 1);
INSERT INTO Foo VALUES ('b,a', 1);
-- what is meaning of these queries?
SELECT key_col, COUNT(DISTINCT junk) AS tally
FROM Foo
GROUP BY key_col;
SELECT junk, COUNT(DISTINCT key_col) AS tally
FROM Foo
GROUP BY junk;
And the same ordering problems occur for equi-joins and theta
operations.
DELETE FROM Foo
WHERE key_col = 'a';
UPDATE Foo
SET key_col = 'x', junk = 2
WHERE key_col = 'a';
UPDATE Foo
SET key_col = 'x', junk = 2
WHERE key_col = 'a,b';
UPDATE Foo
SET key_col = 'x', junk = 2
WHERE key_col = 'a,b,c';
I can sit down and write some awful self-referencing things that will
really push the point. There are sound mathematical reasons Dr. Codd
said theta operations are defined only for scalar values.
Any rules you make will be arbitrary and I can come up with an example
where they fail. There are good reasons that multi-valued (PICK,
etc.) and OODBMS systems failed to catch on.
cmurthi
probably futile. While I don't understand much of Celko's examples (if he
could rephrase these examples in non-jargon, it might be more
convincing,) it is clear that you can create bad or good applications in
any system.
Since FNF db's force you to abide by their rules, the boundaries of "bad"
applications are probably further out, at a price of course. If you
understand what an application is about, and can program well, all this
stuff about mv's having integrity problems is just not there.
That said, I think in general, you have to be a better programmer to
produce good apps in Pick or any non-constrained db. And that's a big part
of why mv/Pick had trouble, because the mentality of the MBA's who run
most companies is to allow for one average programmer to replace another.
Chandru Murthi
kaz
cmurthi wrote:
<SNIP
> the mentality of the MBA's who run most companies is to allow for one
> average programmer to replace another.
>
> Chandru Murthi
<SNIP>
LMAO...I believe they teach that very thing in business schools...And if they
can't find an average programmer in a timely fashion, watch out for who they
do hire....
-- kaz
Anthony W. Youngman
<71062...@compuserve.com> writes
>>> Oh, and ignore Celko's remark about integrity. Multi-value doesn't
>have integrity problems as a consequence of violating first normal. It
>may have them as a result of bad design, but that's a different issue!
><<
>
>Let me extend SQL a bit and allow a multi-valued column. Can you
>assign an obvious reasonable meaning to any of these things?
>
have extended Oracle, IBM have extended DB2 ...
>CREATE TABLE Foo
>(key_col MULTI-VAL-CHAR(100) NOT NULL PRIMARY KEY,
> junk INTEGER NOT NULL);
If that's a multi-value primary key then tough. I presume you could have
a schizophrenic author with a multi-value key. It'll screw up every
multi-value DB I know of though. And if you don't mean a multi-value
primary key, then sorry, my SQL isn't good enough to understand what
you're saying.
If I understand you correctly, then I would agree with you! But either
Codd or Date (I'm not sure which) switched to being rather a fan of many
things multi-value... (I didn't say he was a fan of all of it...)
>
>Any rules you make will be arbitrary and I can come up with an example
>where they fail. There are good reasons that multi-valued (PICK,
>etc.) and OODBMS systems failed to catch on.
Yes - like they didn't have multi-million-dollar marketing budgets.
Multi-value "just works", and it's cheap besides. It's a pretty safe bet
that switching from MV to relational will cost a bomb, and then double
your TCO once you've converted... there's a reason Pick systems kick the
shit out of relational when you compare two systems doing the same or
similar job.
But you said any rules I make are arbitrary, and you can come up with an
example where they fail. Let's see you tackle this:
1. Multi-value primary keys are not permitted (as I said, they'll break
every implementation I know of if you try...)
Think in "entity attribute relationship" mode.
2. Every entity-set is declared as a FILE (table in SQL-ese)
3. Each individual entity is a RECORD in the FILE (row in SQL-ese)
4. Each attribute is a FIELD in a RECORD (column in SQL-ese) and may be
single- or multi-value
5. Each relationship is a FIELD in a RECORD (column in SQL-ese) and may
be single- or multi-value containing the primary key of the other FILE.
And while it's probably irrelevant, any FILE can have virtual fields
pointing at another FILE, which turns it into the equivalent of a SQL
"view". This gives, as I understand it, near as dammit exactly what MS
SQL-server calls a chaptered rowset.
Oh - and do you see why I said data integrity isn't a problem? You can't
add attributes without an entity for them to belong to. Likewise
relationships. And deleting or changing an entity will take all its
attributes and entities with it. It's inherent in the data, rather than
having to be enforced by the database with triggers, constraints, and
all that crap :-) Just design the DB properly (and that is Pick's
Achilles heel - there's far too much badly designed crap out there) and
good data is a side-effect, not something that has to be checked and
enforced :-)
And yeah, I know that's a very relational approach. Relational IS good
design. But don't enforce it where it don't fit! That's the beauty of
Pick - like C it's close to the metal, while SQL is like Pascal - it
tries to force you to do things the way it thinks it should be done -
and if its model of reality doesn't match reality, then it expects you
to change reality to fit...
ashe...@inlandkwpp.com
>Actually, trying to convince a true-blue FNF-er that Mv is acceptable is
>probably futile. While I don't understand much of Celko's examples (if he
>could rephrase these examples in non-jargon, it might be more
>convincing,) it is clear that you can create bad or good applications in
>any system.
>
>Since FNF db's force you to abide by their rules, the boundaries of "bad"
>applications are probably further out, at a price of course. If you
>understand what an application is about, and can program well, all this
>stuff about mv's having integrity problems is just not there.
>
>That said, I think in general, you have to be a better programmer to
>produce good apps in Pick or any non-constrained db. And that's a big part
>of why mv/Pick had trouble, because the mentality of the MBA's who run
>most companies is to allow for one average programmer to replace another.
>
>Chandru Murthi
>
FNF also can still get you flaws in your system. MV works if designed
correctly but if you are doing a relational database to FNF you are
still leaving alot of room for design problems. Relational databases
(depending on the complexity of the system) most likely should be
normalized to 2nd, 3rd, (and even BOYCE-CODD normal form (is that what
you guys call it?)) .
I've seen some papers on ibms site about nested hierchy database
structures and mv databases but a quick google search didn't give me
anything. Maybe someone would be kind enough to post some good
educational docs about the mv database structure and its design
patterns that you would use to build a "propper" multivalued database
(ie mv equivlant procedures to normalization, bubble diagrams, bachman
diagrams etc).
-Adam
Patrick Payne
#1: MV only
Main Table
0> Main table key
1> someinfo
2> someinfo
3> author1]author2]author3]author4 - (multivalued out for each
author)
Author Table
key - author1
1> author infomration
#2: MV and Relational
Main Table
0> Main Table key
1> someinfo
2> someinfo
Main Table - Child table information
0> seq key
1> Main Table Key
2> Author code
-- One child entry for each author, one-many relationship with main
table
Author Table
0>Author key
1> author info
#2 is the 1st normal form and really the only way Relational can do
things. #1 is our own answer to the problem that eliminates one
table. Integretiy only comes into play with #2 in that deletion of a
main table entry requires the deletion of child entries. In MV we do
not have the exact same issue, but one could delete a Author and the
code would remain in the Main Table entries.
The only real disadvantage of #1 is either Size (hundreds and hundreds
of authors) and/or simple reporting that breaks out details of
authors. Here SQL does have some nice JOIN features. Bottom line is
you can follow the 1st normal format if you want in Pick, but since
pick has no join feature you will still be forced to write some tools
to re-merge the information.
- Patrick
Albert D. Kallal
The problem is that we do NOT use sql, and further I cannot recall ever
seeing a mv application that has a multi-valued key. We simply don't do
that. In fact, it is not workable. So that those examples really do not
apply. Mv systems don't work that way.
Further, while in reports, and general mv queries the key id appears as a
regular field, in fact it is a separate concept. That means we don't create
a field, and then later decide that is going to be a key field. In fact, we
can't even write a record to disk without some key id. The key id, and a
field are not the same in mv land. In code, the key id cannot be referenced
as a regular field like in other database systems.
Note that most versions of sql let you create tables without some type of
key id, this is not even possible in mv land. Further, as mentioned those
key id's are not multi-valued.
So, remember that many (not all) tables in mv land do NOT require a join
between some key field for a join. In fact, we don't have a join command in
mv land.
For some examples on a typical sql statement vs a typical mv query, you can
read my following link.
I give an example of a very simple sql join to find a salesman who sold a
blue and a red car. You can see how poorly sql solves this common problem.
In pick, it is a dead simple, and not even a join even needed. This is
because the related table DOES NOT have a primary key (it is call a
controlling and dependent set of data, and no key id's exits for those
multi-valued data sets - the use the key id of the parent table, and that
key id is NOT REPEATED!).
That article of mine with the sql example can be found at::
http://www.attcanada.net/~kallal.msn/Articles/fog0000000006.html
--
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com
http://www.attcanada.net/~kallal.msn
--CELKO--
multi-valued key. We simply don't do that. In fact, it is not
workable. <<
I agree it is not workable; that was my point. But now your datatype
system has to include rules for scalars and non-scalars. I have no
idea what the rules would be, but it looks like:
1) Scalar columns can be keys
2) MV columns cannot be keys
3) Tables cannot be joined on MV columns
4) Tables can be joined on scalar columns
>> The key id, and a field are not the same in mv land. In code, the
key id cannot be referenced as a regular field like in other database
systems. <<
That drives me nuts! I want a key I can verify in the reality that I
am modleing, not one that exists only in the physical storage of the
database. I also want to specify multiple, overlapping keys via UNIQUE
constraints.
>> Note that most versions of sql let you create tables without some
type of key id, this is not even possible in mv land. <<
That was a big mistake in System R that was carried over to Standard
SQL ...
>> In fact, we don't have a join command in mv land. <<
Sounds like it is sure hard to find new relationships in the data on
the fly.
>> I give an example of a very simple sql join to find a salesman who
sold a blue and a red car. <<
>> I am looking for a sales rep who sold both a red AND a blue car. <<
SELECT S1.salesrep
FROM Salesreps AS S1,
CarsSold AS C1
WHERE S1.salesrep_id = C1.salesrep_id
AND C1.color IN ('red', 'blue')
GROUP BY S1.salesrep
HAVING COUNT (DISTINCT C1.color) = 2;
Or to generalize it, with a table for the colors to be found:
CREATE TABLE ColorList(color CHAR(10) NOT NULL PRIMARY KEY);
SELECT S1.salesrep
FROM Salesreps AS S1, CarsSold AS C1
WHERE S1.salesrep_id = C1.salesrep_id
AND C1.color IN (SELECT color FROM ColorList)
GROUP BY S1.salesrep
HAVING COUNT (DISTINCT C1.color)
= (SELECT COUNT(*) FROM ColorList);
This is a relational division, but what do you with MV, when you want
the salesrep who sold ONLY red and blue cars?
BobJ
method can do anything that the other can do. The primary difference lies
in the formal constraints. There are many of these in SQL and few of these
in MV. Which is better? The one that you think is better. There are, of
course, some areas where the choice is fairly obvious. The lack of
discipline with MV makes it a good choice where speed of development is very
important but makes MV a poor choice where reliability is very important.
Not to say that some people can't develop fast with SQL nor that some people
can't develop reliable code with MV. I have maintained over the years that
95% of the really good programs are written by 5% of the programmers. The
other 95% need to use VB and SQL!
Let me soften that a bit. Celko's publications are outstanding and one
can assume fairly safely that he is one of the 5% who has happened to settle
on SQL. The population of programmers that fit the 95/5 pattern are those
who build business oriented systems, not those who write systems programs.
The whole argument is moot anyway. SQL dominates the world of business
oriented programming and there is not much chance that any MV platform is
going to make a dent in this armor
But the discussion is interesting.
BobJ
"--CELKO--" <71062...@compuserve.com> wrote in message
news:c0d87ec0.03050...@posting.google.com...
Patrick Payne
from writing a little code. My father worked as a Informix 4gl
developer for around 20 years (It took him only 1 month to come up to
speed with D3 and he is very impressed with it now). We was
continually cleaning up processes that utilized compound selects/joins
(like above) that when run against large databases either choked or
took hours (mainly for inventory systems, companies like Columbia
Sportsware and Lockheed skunk works to name a couple). The classic
fix was to change a complicated join to a seperate cursor lookup of
the primary key, and then execute independent code selects for the
joined detail information. This allowes you as the developer to
#1: debug individual transactions
#2: optimize your own code
#3: in the end it was easier to read information
#4: Ability to stop the query process under certain conditions.
His transition to pick was made easier since this is how we ARE ALWAYS
forced to do things, i.e.
execute 'select file',//SELECT.>CURSOR1
eof=0
loop
readnext id from CURSOR1 else eof=1
until eof do
read rec from file, id else rec=''
secondary.info=rec<5>
execute 'select file2 where info =
"':secondary.info:'",//SELECT.>CURSOR2
eof.cursor2
loop
readnext secondary.id from CURSOR2 else eof.cursor2=1
until eof.cursor2 do
*
repeat
* now do secondary select information
repeat
* In fact I had never used the CURSOR syntax he started working for
me. He was very confortable with the syntax and in the end looked
very similiar to his sql code. Seem to learn something new every day.
My point is these goofy joins while neat will usually kill your
machine if your site/database grows. You are also completly dependent
on your SQL engine and its capability to optimize the query. Again, I
know of a instance where a Informix sql upgrade actually hurt the
optimization engine and caused a 50 second query to jump to 20
minutes. The poor end user is stuck with almost no way to personally
optimize the query other than rewriting the entire module.
- Patrick
71062...@compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.03050...@posting.google.com>...
Anthony W. Youngman
<71062...@compuserve.com> writes
>>> I cannot recall ever seeing a mv application that has a
>multi-valued key. We simply don't do that. In fact, it is not
>workable. <<
>
>I agree it is not workable; that was my point. But now your datatype
>system has to include rules for scalars and non-scalars. I have no
>idea what the rules would be, but it looks like:
At which point you promptly show that you've never actually used a
multi-value database ... I don't mean to be rude but you clearly don't
grasp what's going on.
>
> 1) Scalar columns can be keys
Let's rephrase that. THE KEY must be a scalar. By turning the logic
round, I've actually fundamentally changed the meaning. It's a different
way of seeing the world. Once you see through someone else's eyes, the
world will never look the same ...
And you said you don't like keys that can't be accessed as "just another
column". If you use MV keys like SQL uses sequential (and meaningless
from the data point of view) numbers, why do you need to access them?
Again, it's just a different view, which you seem to find somewhat
difficult because it's foreign to you.
> 2) MV columns cannot be keys
Yes, but it does not make sense to use a MV column as a key. That's just
plain bad design (and similar blunders are possible in SQL-land...)
> 3) Tables cannot be joined on MV columns
Firstly, MV-land doesn't have tables. Secondly, why not? I do it all the
time. Most MV columns are likely to be columns of, in SQL-ese, "foreign
keys". It doesn't make sense if you can't join FILEs that way.
Think of a "one to many" relationship. In SQL, you would store the
foreign key in the "many" table. In MV, we'd store a MV list of foreign
keys in the "one" "table". Which means we get into far less trouble with
"many to many" relationships :-)
Oh - and because we don't (normally) dedicate a "table" to defining a
relationship, if we couldn't join on MV columns then we couldn't have a
"many to many" relationship, which actually we can implement incredibly
easily.
> 4) Tables can be joined on scalar columns
Of course. A scalar column is just a subset of a MV column.
BobJ
although they are not mutually exclusive. Most of us are not deep with SQL,
so don't expect a guy who has dedicated his working life to SQL to be deep
with Pick in five minutes. At least he is looking at it and thinking, which
is more than I can say for some SQL "experts" with famous names (think
Pascal). A mind is a terrible thing to waste - and a closed mind is wasted
by definition.
BobJ
"Anthony W. Youngman" <thew...@nospam.demon.co.uk> wrote in message
news:um3cCcBs...@thewolery.demon.co.uk...
cmurthi
>> I cannot recall ever seeing a mv application that has a multi-valued key. We simply don't do that. In fact, it is not workable. <<
I agree it is not workable; that was my point. But now your datatype
system has to include rules for scalars and non-scalars. I have no
idea what the rules would be, but it looks like:1) Scalar columns can be keys
2) MV columns cannot be keys
3) Tables cannot be joined on MV columns
4) Tables can be joined on scalar columns
>> The key id, and a field are not the same in mv land. In code, the
key id cannot be referenced as a regular field like in other database
systems. <<That drives me nuts! I want a key I can verify in the reality that I
am modleing, not one that exists only in the physical storage of the
database. I also want to specify multiple, overlapping keys via UNIQUE
constraints.
>> Note that most versions of sql let you create tables without some
type of key id, this is not even possible in mv land. <<That was a big mistake in System R that was carried over to Standard
SQL ...>> In fact, we don't have a join command in mv land. <<
Sounds like it is sure hard to find new relationships in the data on
the fly.
>> I give an example of a very simple sql join to find a salesman who
sold a blue and a red car. <<>> I am looking for a sales rep who sold both a red AND a blue car. <<
SELECT S1.salesrep
FROM Salesreps AS S1,
CarsSold AS C1
WHERE S1.salesrep_id = C1.salesrep_id
AND C1.color IN ('red', 'blue')
GROUP BY S1.salesrep
HAVING COUNT (DISTINCT C1.color) = 2;
Not all MV systems work exactly the same; to get reps who sold Blue
and Red, some will support:
SELECT SALESREPS WITH CAR_COLOR = "RED" AND = "BLUE"
on others, you'd have to create two Dictionary (meta-definition) fields
which select for BLUE and RED, then:
SELECT SALESREPS WITH CAR_COLOR_BLUE > "0" AND WITH CAR_COLOR_RED
> "0"
(there may be simpler ways to do this, but it's late). Incidentally, creating these defs is just a matter of editing a record in the Dictionary of the SALESREP file; every file has an associated Dictionary
Or to generalize it, with a table for the colors to be found:CREATE TABLE ColorList(color CHAR(10) NOT NULL PRIMARY KEY);
SELECT S1.salesrep
FROM Salesreps AS S1, CarsSold AS C1
WHERE S1.salesrep_id = C1.salesrep_id
AND C1.color IN (SELECT color FROM ColorList)
GROUP BY S1.salesrep
HAVING COUNT (DISTINCT C1.color)
= (SELECT COUNT(*) FROM ColorList);This is a relational division, but what do you with MV, when you want
the salesrep who sold ONLY red and blue cars?
SELECT SALESREPS WITH EVERY CAR_COLOR = "RED" OR = "BLUE"
(or something similar). You can see this is actually quite easy in
MV. Omit the EVERY and you get sales reps who either Blue or Red or both.
I'm glad for this exchange since it gives us a chance to show how easy MV is.
Chandru Murthi
Bob Badour
news:b9en9o$1gk$1...@slb2.atl.mindspring.net...
> Lack of knowledge does not necessarily equate to lack of intelligence -
> although they are not mutually exclusive. Most of us are not deep with
SQL,
> so don't expect a guy who has dedicated his working life to SQL to be deep
> with Pick in five minutes. At least he is looking at it and thinking,
which
> is more than I can say for some SQL "experts" with famous names (think
> Pascal). A mind is a terrible thing to waste - and a closed mind is
wasted
> by definition.
Who is more closed minded? The educated person who looked, thought,
understood and rejected or the uneducated person who looked, thought,
misunderstood and embraced?
The uneducated person who misunderstands both the product and the criticisms
of the product's flaws can either educate himself or dismiss the educated
critic. Dismissing the educated critic would be rather closed minded.
Wouldn't you agree?
Albert D. Kallal
>
> 1) Scalar columns can be keys
> 2) MV columns cannot be keys
Lets clarify this a bit. There is nothing stopping you from having a record
with ONE of the fields that is a list of related records. So, a mv column
can't be a key field, but a mv field sure the heck can be a list of primary
keys from another file. This is VERY common in mv land.
> 3) Tables cannot be joined on MV columns
We don't have a join, so..yes that is true. However, we often grab data from
other tables. We use the equivalent of a trans late. If you map that multi
value table into a sql model, in this case it works fine. In mv we use the
dictionary item to define a lookup to the other table. Even more incredible,
this lookup does not requite a index. Further once you have defined this
lookup field, then you don't need to even know how to do a join. This is
better then a "view" in sql, since the field can be used in all queries.
Having to use a join is often a real pain, and big set back. So, lack of a
join has two sides to the coin. Having ALWAYS to use a join is not a plus my
books.
> That drives me nuts! I want a key I can verify in the reality that I
> am modleing, not one that exists only in the physical storage of the
> database. I also want to specify multiple, overlapping keys via UNIQUE
> constraints.
You are thinking a traditional relational model. We often use compound keys
in mv land, they are just different (often we will create a key value that
is separated by Date*accountNumber for example). Further, we have field
definitions that allow us to sort, and use each value in that compound key
as if it were a field.
> Sounds like it is sure hard to find new relationships in the data on
> the fly.
This is one weak spot. On the other hand is it more of a straw argument. I
can not EVER remember EVER writing a business application where I was
creating relationships, and joins on the fly. Can you? Not very common at
all. I not saying this is bad, or good, but in real business appcltions, it
does not happen very much. I used sql for 10 years in my applications, and
have NEVER had any of them do a join on the fly. Sure, if you are data
analyst you do joins on the fly. In application run by your users you will
create the views for them, but at the very lest the whole process is hidden
from the users.
> >> I am looking for a sales rep who sold both a red AND a blue car. <<
>
> SELECT S1.salesrep
> FROM Salesreps AS S1,
> CarsSold AS C1
> WHERE S1.salesrep_id = C1.salesrep_id
> AND C1.color IN ('red', 'blue')
> GROUP BY S1.salesrep
> HAVING COUNT (DISTINCT C1.color) = 2;
>
Ah, but the above is wrong. As I said, 9 out 10 get it wrong. If the sales
man sold 2 red cars, the above still works, and that is the WRONG answer. I
simply want anyone who sold a red and a blue car. Guys, a red and a blue
car, how hard can that be?. This is such a simple question, and yet I get
tons of people every month sending some simple sql statement that does not
work.
I mean, I would feel bad if the blue and red car was some "trick" question,
or we had to do real mental gymnastics here. A salesman who sold red and a
blue car is not a heavy duty technical relational database question. Yet,
some one who is VERY GOOD at sql just got that most simple question wrong. I
not being mean here, but why do I constantly get people giving wrong sql to
the above question. If there is some wording in question that needs to be
changed, I will respectfully change it. The number of people getting the
above question wrong in sql is becoming embarrassing.
In pick, if you ask a average person to do the above query, they get it
right. The answer is:
list tblCarsSold SalesRep Make Model Color
with Color = "Red" and with Color = "Blue"
Even when you read the above, it makes sense!
> This is a relational division, but what do you with MV, when you want
> the salesrep who sold ONLY red and blue cars?
My example mv query did just that. That was my question. Might I add that
this is a extremely common "class" problems that sql does a poor job with.
You can certainly work out conditions in child tables that apply to the
parent tables, but sql is a bit clumsy in this regards.
Of course I am sure we both can find all kinds of examples that sql, or mv
aql does well (or not well).
My only point about mv land was that many questions in mv land are very
intuitive, and the fact that you posted a wrong answer just proves my point
more. To find out which sales man sold a blue and red car should not require
a huge discussion on database designs, sub queries, and relational joins (3
heavy concepts). In pick you just ask that simple question and you get a
simple answer. Some sql systems now have a natural language query system,
but pick gives a simple result because of elegance in design, not some
trick.
We should not have be some database guru to answer that simple question.
Note that my question is a true fair and reason question. My red/blue
question is also a typical question that applies to invoices, and all kinds
of questions that are common.
As mentioned, most of the mv systems lets you map the data into sql anyway,
but that to me is step down. You have to approach problems different in mv
land. If you think 100% relational, then some solutions will not work in mv
land. (and the reverse is true!).
This mind set change is very much like moving from the old dbase format to
pick (or a modern sql engine). In a modern database, we don't have record
numbers like dbase did. However, the whole coding process in dbase assumed
record numbers (punched cards anyone?). When dabase programmers moved to
sql, or even ms-access, or pick, many had to endure a "mind" set change,
until they begin to think in solutions that are appropriate to the tool
being used.
The same applies to sql, and mv land.
The mv design is simple, and elegant, and I can come up with many examples
where it solves problems far better then does the relational model.
If you look at the follwing screen shot, on the left is a class object in VB
that is 600+ lines of code, and on the right is ONE pick mv reocrd. They are
close in function, and mv reocrd need almost NO code to prove the same
features.
http://www.attcanada.net/%7ekallal.msn/Articles/PickSql/Appendex4.html
Albert D. Kallal
If the sales sold 10 red cars, and 10 blue, then that sales man did in fact
sell a red, and blue car. Thus the sales man should be included.
I repeat, this is NOT a trick question. Any query that gives any sales man
who sold a red and a blue car is fine.
This is not a question who sold just 1 red car and 1 blue car.
Simply, anyone who sold a red and a blue car.....
or anyone who had two invoices in the last two months, or a zillion type of
questions that are the same as the above.
This is so natural and simple in pick. We are experienced database people
here, can you imagine how difficult this is for inexperienced people?
It should not be hard, and in pick is simple:
note that in pick, the following is wrong:
list tblCarsSold SalesRep Make Model Color
with Color = "Red" and Color = "Blue"
Of course, a car cannot be both red and blue at the same time! Hence the
correct pick answer is:
list tblCarsSold SalesRep Make Model Color
with Color = "Red" and with Color = "Blue"
--
Patrick Schaaf
>Who is more closed minded? The educated person who looked, thought,
>understood and rejected or the uneducated person who looked, thought,
>misunderstood and embraced?
The first person, clearly. Having rejected, they will not reconsider
when situations change. They have rejected. The second person can learn
by experience where to loosen the embrace, and can be in full control
of the extent of the embrace. That's how the second person becomes
educated, over time.
Interesting fights you have here on this newsgroups...
best regards
Patrick
Mecki
<snip>
> This is a relational division, but what do you with MV, when you want
> the salesrep who sold ONLY red and blue cars?
You simply write a new dictionary, let´s call it REDBLUE, with a correlative
like:
A;IF N(COLOR) # "red" THEN IF N(COLOR) # "blue" THEN "0" ELSE "1" ELSE "1".
Now you add AND WITH EVERY REDBLUE = "1" and you have a select list with all
reps who sold only red and blue cars.
No problem
Mecki
Albert D. Kallal
every single new question.
My simple elegant solution of
list tblCarsSold SalesRep Make Model Color
with Color = "Red" and With Color = "Blue"
Will work for all those who sold a red and blue car.
Now, to extend the question to sales man that sold ONLY RED and Only blue
cars, then we just tack on that additional "only" condition. There is no
need to define a dictionary.
So, we simply add the condition of
"and with each color = red or blue"
I am not kidding, the above reads like normal thought human patterns, and
you can just add the above on, and it works in pick. We will get:
list tblCarsSold SalesRep Make Model Color
with Color = "Red" and with Color = "Blue"
and with each Color = "Red" or "Blue"
That is all you need. I am not trying to knock your solution of adding a
dict item, but I did want to impress the original poster on once again pick
solves this question in a easy common sense fashion.
The above is classic "set" intersection theory, and by adding the "each
color" statement it works. The reasons why pick is so simple is that each
"color" represents a set, and in the non multi value world, one field is NOT
a set.
Hence, the above is painful in sql.
The mv model once again handles this with such ease, and elegance.
For sure one can come up with all kinds of queries in sql that pick does not
handle well, but it never ceases to surprise me how the mv model works.
Set theory in multi values in pick is very cool.
Just looking at that above solution sends chills down by back...
Bob Badour
news:mEEua.128470$ya.41...@news1.calgary.shaw.ca...
> Just to 100% clear on that red/blue car question
>
> If the sales sold 10 red cars, and 10 blue, then that sales man did in
fact
> sell a red, and blue car. Thus the sales man should be included.
He sold a red and a blue car, but he did not sell a red and blue car. Some
of the cars he sold were red and some were blue but none of them were both.
> I repeat, this is NOT a trick question.
Why, then, do you have so much difficulty understanding it?
> Any query that gives any sales man
> who sold a red and a blue car is fine.
The SQL query Joe provided does exactly that.
If a red and blue car is different from a red car and different from a blue
car, I think your PICK solution will incorrectly include the salesman who
sold no red cars, no blue cars but one or more red and blue cars.
> This is not a question who sold just 1 red car and 1 blue car.
I know. Neither is it a question of who sold only red cars and blue cars,
but you told Joe that your query answers the question of who sold only red
cars and blue cars.
> Simply, anyone who sold a red and a blue car.....
> or anyone who had two invoices in the last two months, or a zillion type
of
> questions that are the same as the above.
>
> This is so natural and simple in pick.
Then why did you get the answer wrong? And why does the query you provided
as the solution make so little sense?
> We are experienced database people
> here
Some of us are. Then again, I have met people who worked with a product for
a decade and got one month's experience 120 times.
>, can you imagine how difficult this is for inexperienced people?
I have met many people who were able to devise correct SQL queries for
rather complex questions with very little training and almost no experience.
The problem with SQL is there are often too many ways to write correct
queries for complex questions (or even for simple questions.) For most
products, some ways will perform well while others will not, and
inexperienced users cannot predict which perform and which don't.
SQL actually encourages inexperienced users to think about their questions
in precise terms. It seems that PICK discourages users from thinking at all.
It is naive to believe that every simply phrased query will return exactly
what the user wants without requiring the user to think about what they are
asking.
> It should not be hard, and in pick is simple:
>
> note that in pick, the following is wrong:
>
> list tblCarsSold SalesRep Make Model Color
> with Color = "Red" and Color = "Blue"
>
> Of course, a car cannot be both red and blue at the same time!
If Color is a multivalued attribute of Car, a can can be both red and blue
at the same time. For instance, the truck in my driveway is both tan and
purple at the same time.
Of course, I realise that the expression above is a contradiction even if
Color is a multivaled attribute of Car because it requires a single scalar
among a set of scalars to have two values.
> Hence the
> correct pick answer is:
>
> list tblCarsSold SalesRep Make Model Color
> with Color = "Red" and with Color = "Blue"
If Color is a multivalued attribute of Car, why won't this query return
SalesReps who sold one or more red and blue cars but who sold fewer than one
red car or fewer than one blue car?
Anthony Youngman
> "BobJ" <b...@rjoslyn.com> wrote in message
> news:b9en9o$1gk$1...@slb2.atl.mindspring.net...
> > Lack of knowledge does not necessarily equate to lack of intelligence -
> > although they are not mutually exclusive. Most of us are not deep with
> SQL,
> > so don't expect a guy who has dedicated his working life to SQL to be deep
> > with Pick in five minutes. At least he is looking at it and thinking,
> which
> > is more than I can say for some SQL "experts" with famous names (think
> > Pascal). A mind is a terrible thing to waste - and a closed mind is
> wasted
> > by definition.
>
> Who is more closed minded? The educated person who looked, thought,
> understood and rejected or the uneducated person who looked, thought,
> misunderstood and embraced?
Yup. Except the "expert" the other Bob is dismissing can best be
characterised as "looked, jumped to conclusions, and rejected". He is
an excellent mathematician, but his major flaw is that he believes in
the maths, and is not open to testing that maths as to whether it's
actually a good fit to the real world that's "out there".
>
> The uneducated person who misunderstands both the product and the criticisms
> of the product's flaws can either educate himself or dismiss the educated
> critic. Dismissing the educated critic would be rather closed minded.
> Wouldn't you agree?
Would that there were more truly educated critics ... :-(
>
> > BobJ
Cheers,
Wol
Bob Badour
> Well, that is rather nasty that we have to define a new dictionary item
for
> every single new question.
>
> My simple elegant solution of
>
> list tblCarsSold SalesRep Make Model Color
> with Color = "Red" and With Color = "Blue"
>
> Will work for all those who sold a red and blue car.
But that is different from the original question which asked for all those
who sold a red and a blue car.
> Now, to extend the question to sales man that sold ONLY RED and Only blue
> cars, then we just tack on that additional "only" condition. There is no
> need to define a dictionary.
>
> So, we simply add the condition of
>
> "and with each color = red or blue"
How do you specify that you want only those who sold one or more red and
blue cars regardless of whether they sold any red cars, any blue cars or
even any green cars? How do you specify that you want only those who sold at
least one red car and at least one blue car where a red and blue car is
considered neither red nor blue?
> I am not kidding, the above reads like normal thought human patterns, and
> you can just add the above on, and it works in pick. We will get:
>
> list tblCarsSold SalesRep Make Model Color
> with Color = "Red" and with Color = "Blue"
> and with each Color = "Red" or "Blue"
This still does not answer the question of who sold a red and a blue car and
only red cars and blue cars because it will include all those who sold red
and blue cars.
The query is deceptively simple because it gives an incorrect answer while
deluding naive users into thinking they are getting a correct answer.
> That is all you need.
If you are willing to accept sloppy, incorrect solutions, I suppose it is
all you need.
> I am not trying to knock your solution of adding a
> dict item, but I did want to impress the original poster on once again
pick
> solves this question in a easy common sense fashion.
Except that sloppy, incorrect solutions won't impress anyone.
> The above is classic "set" intersection theory, and by adding the "each
> color" statement it works.
Intersection? I think not. It takes the union of all colors of all cars a
salesrep sells and then tests for exclusive containment. By first taking the
union, it loses any distinction between cars with two or more colors and two
or more cars with one color each.
> The reasons why pick is so simple is that each
> "color" represents a set, and in the non multi value world, one field is
NOT
> a set.
If pick is so simple, why do you consistently get the answer to such a
simple question so wrong?
> Hence, the above is painful in sql.
Joe already gave one correct answer to your original question and you have
yet to provide one. In the face of this, your conclusion strikes me as
irrational.
> The mv model once again handles this with such ease, and elegance.
Ease and elegance require correctness. You provide a simple but incorrect
solution--it's simpler than possible.
> For sure one can come up with all kinds of queries in sql that pick does
not
> handle well, but it never ceases to surprise me how the mv model works.
Given your inability to detect when it doesn't work, I suppose your surprise
is understandable.
> Set theory in multi values in pick is very cool.
I might be impressed if pick had not totally ignored set theory.
> Just looking at that above solution sends chills down by back...
If only you had the sense to trust your fear...
Bob Badour
news:9a993dee.03050...@posting.google.com...
> "Bob Badour" <bba...@golden.net> wrote in message
news:<exEua.140$sv5.27...@mantis.golden.net>...
> > "BobJ" <b...@rjoslyn.com> wrote in message
> > news:b9en9o$1gk$1...@slb2.atl.mindspring.net...
> > > Lack of knowledge does not necessarily equate to lack of
intelligence -
> > > although they are not mutually exclusive. Most of us are not deep
with
> > SQL,
> > > so don't expect a guy who has dedicated his working life to SQL to be
deep
> > > with Pick in five minutes. At least he is looking at it and thinking,
> > which
> > > is more than I can say for some SQL "experts" with famous names (think
> > > Pascal). A mind is a terrible thing to waste - and a closed mind is
> > wasted
> > > by definition.
> >
> > Who is more closed minded? The educated person who looked, thought,
> > understood and rejected or the uneducated person who looked, thought,
> > misunderstood and embraced?
>
> Yup. Except the "expert" the other Bob is dismissing can best be
> characterised as "looked, jumped to conclusions, and rejected".
It's your loss that you have such a closed minded opinion of him.
> > The uneducated person who misunderstands both the product and the
criticisms
> > of the product's flaws can either educate himself or dismiss the
educated
> > critic. Dismissing the educated critic would be rather closed minded.
> > Wouldn't you agree?
>
> Would that there were more truly educated critics ... :-(
I agree; even though, I suspect there are a few more than you realise.
Albert D. Kallal
> wrote in message news:eaKua.152$zQ6.31...@mantis.golden.net...
> "Albert D. Kallal" <kal...@msn.com> wrote in message
> news:mEEua.128470$ya.41...@news1.calgary.shaw.ca...
> > Just to 100% clear on that red/blue car question
> >
> > If the sales sold 10 red cars, and 10 blue, then that sales man did in
> fact
> > sell a red, and blue car. Thus the sales man should be included.
>
> He sold a red and a blue car, but he did not sell a red and blue car. Some
> of the cars he sold were red and some were blue but none of them were
both.
I never meant to mean one car with two colors. That never came into the
picture. To avoid confusing we should call a two colors car red-blue.
That is not the question.
> If a red and blue car
We don't have red/blue cars here. We just have single color cars for this
demo.
Next time around I will use the "make" of the car....since color can be a
source of confusing as your point out.
No one ever in thread brought up the idea, or even considered the concept of
two colour cars here.
Read the original post. The sql posted is wrong....
--
Albert D. Kallal (MVP)
Albert D. Kallal
> wrote in message news:sIKua.153$YQ6.31...@mantis.golden.net...
> "Albert D. Kallal" <kal...@msn.com> wrote in message
> news:T7Jua.143976$ja.54...@news2.calgary.shaw.ca...
> > Well, that is rather nasty that we have to define a new dictionary item
> for
> > every single new question.
> >
> > My simple elegant solution of
> >
> > list tblCarsSold SalesRep Make Model Color
> > with Color = "Red" and With Color = "Blue"
> >
> > Will work for all those who sold a red and blue car.
>
> But that is different from the original question which asked for all those
> who sold a red and a blue car.
No it is not. The original question was give me any sales man who sold a red
and a blue car. There was never any restrictions on the fact of just 1 red
car, or just one blue car. It could have been 10 blue cars, and 10 red cars.
That still means he sales man sold a red and blue car. There was also never
any restrictions on the fact that the sales man sold just blue and red cars,
the above makes no assumptions on a green car being sold also.
Read the original question.
However, if the question was extended to only include salesman that sold
blue and red cars (no other colors), then next statement of mine is again
correct:
> > list tblCarsSold SalesRep Make Model Color
> > with Color = "Red" and with Color = "Blue"
> > and with each Color = "Red" or "Blue"
>
> This still does not answer the question of who sold a red and a blue car
and
> only red cars and blue cars because it will include all those who sold red
> and blue cars.
Yes, it does answer that question. Is not the above question the same thing?
The above list will only return sales men who sold red and blue cars. If
they sold any other color, then they will not be included in the above. What
more can their be to this question?
Please expand on the this:
> because it will include all those who sold red
> and blue cars.
yes...it will, and that is correct! We only have single colored cars
here....
Mecki
there are many ways to skin a cat.
AFAIK your solution will not work with all mv, especially earlier,
platforms/releases, but if you have a version that will support your syntax,
I agree that your solution is more elegant of course.
In Access mixing/nesting AND and OR can sometimes be quite challenging and
creating new dictionaries is often the "easy way out".
IMO creating new dictionaries may be sometimes inconvenient but not "nasty".
After all dictionaries are one of the most powerful tools mv has to offer.
Mecki
BobJ
> note that in pick, the following is wrong:
>
> list tblCarsSold SalesRep Make Model Color
> with Color = "Red" and Color = "Blue"
>
Red and Blue and thus qualify for inclusion.
> Of course, a car cannot be both red and blue at the same time! Hence the
Albert D. Kallal
And of course if anyone was allowing cars with more then one color, then the
sql gets even MORE ugly, since now we need a 3rd table, and are joining 3
tables deep!!
So, while my original question NEVER did in fact allow cars with two colors,
in pick you can go right ahead and just start adding cars with two colors!
Pick shines, since you can go ahead and actually do this!
In sql, this simple change (or even miss-understanding that the other poster
had will result in a complete a table re-deigns. And we wonder why so many
IT projects fail. I was talking about red cars and blue cars. The other
person thought I meant a two colored car (a red and blue car!!).
Regardless, in sql to allow cars with more then one color, another table
needs to be added, and we are now up to 3 tables joined. Worse, is our
condition is now 3 tables deep, and that gets ugly.
You think it was difficult in sql when we allowed cars of just one color. If
we actually extend this to allow more then one color, then the sql starts to
be come hopeless confusing.
Don't get me wrong, I really do like sql, and I use sql every day.
But lets give that MV model credit when it is due! This is one case, and
even more telling was how pick even works with the miss-understanding of
red-blue cars!
We are actually fee to start adding cars with two colors, and not even
change the table designs!
--
Albert D. Kallal
By the way, I did test my example on ap-dos, and that is least 10 years old.
And, you know what? , no big deal either way!
What works in mv land just works anyway!
I see nothing wrong about using a dict item to help this problem. In fact,
resorting to defined a dict items is time honoured practice in pick! The
idea of dict items is a way of life in pick!
I did have my guns blazing out of their holster on that one.
I just wanted to impress that we did not have to create a dict item,...not
that it is bad idea.
Thanks anyway!
BobJ
subject. Yes, in Pick we just add colors to the attribute without any need
to indicate that the attribute can legally have values. In SQL there would
be a constraint that would prevent that. The constraint gets in the way of
the original programmer, but protects the system from the maintenance
programmer who either has inadequate documentation or has not studied the
problem. Which is better? The industry in general seems to have adopted
strong typing and constraints as being the normal way to do business. We
may not like it but I think that we have to learn to live with it.
BobJnews:Q6Lua.292124$vs.22...@news3.calgary.shaw.ca...
dp
FWIW I spent two years working with SB+ on uniVerse then nearly six
years to present day with SQL using Informix 4GL. I missed the MV
stuff so much I now fill a large amount of my spare time with D3 and
only ever use the Informix when I have to -- between 9 and 5.
patric...@yahoo.com (Patrick Payne) wrote in message news:<b6da8ff0.03050...@posting.google.com>...
Dieter Nöth
>>>>I am looking for a sales rep who sold both a red AND a blue car. <<
>>
>>SELECT S1.salesrep
>> FROM Salesreps AS S1,
>> CarsSold AS C1
>> WHERE S1.salesrep_id = C1.salesrep_id
>> AND C1.color IN ('red', 'blue')
>>GROUP BY S1.salesrep
>>HAVING COUNT (DISTINCT C1.color) = 2;
>>
>
> Ah, but the above is wrong. As I said, 9 out 10 get it wrong. If the sales
> man sold 2 red cars, the above still works, and that is the WRONG answer.
Please check the meaning of DISTINCT in SQL and you know you're wrong...
Dieter
Albert D. Kallal
Distinct count will work. There is number of sql's out there that does not
support distinct like that, but it certainly will work on something like
sql-server.
Albert D. Kallal
> Read the original post. The sql posted is wrong....
Well, it turns out I was wrong. As mentioned, the sql posted will work.
However, to clarify, if there ever way a possibility of car having two
colors, then the sql example would have needed a 3 tables.
I should have used car make here!
Given the assumption that a car can only have one color, then the pick
solution works, and so does the sql posted!
--
Albert D. Kallal
Kevin Powick
kal...@msn.com says...
> Don't get me wrong, I really do like sql, and I use sql every day.
I just had to jump in. SQL Blows. Only for the simplest queries is it
like MV's Access/English. As we've seen, when the queries get more
complex they become difficult to decipher and can kill a system's
response time.
What really gets me about SQL is that in these Access vs SQL debates,
SQL is only looked at in terms of queries. Where SQL really shows how
terribly awkward it can be is when you start to use the language to
update a database. It's brutal.
The extreme awkwardness of creating, updating/maintaining a db via SQL
is the main reason there are so many great GUI tools to do the job. The
opposite is true for MV databases - Hardly any GUI tools, even after
almost 40 years, because the command line tools, the editor, and a
simple programming language give developers the utmost in power and
flexibility, with extreme ease of use.
--
Kevin Powick
Albert D. Kallal
cross posted in to comp.bases:
"Kevin Powick" <nos...@nomail.com
>What really gets me about SQL is that in these Access vs SQL debates,
>SQL is only looked at in terms of queries.
He does not mean ms-access here, but picks/d3 query language that has had
the "access" name for about 30 years now.
Albert D. Kallal
I think as long as cool heads prevail, then this stuff is most interesting.
It is always tough to prevent things moving from a good technical discussion
to a mud sling!
However, I most certainly enjoyed the thread also. In fact, even having to
admit that the sql posted does in fact work is was also nice to learn!
--
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com
http://www.attcanada.net/~kallal.msn
Anwa
"Patrick Schaaf" <b...@bof.de> wrote in message
news:2003050915...@nemo.roka.net...
> In comp.databases you write:
>
> >I add one more thing, just in case the readers are confused, as this post
is
> >cross posted in to comp.bases:
>
> >"Kevin Powick" <nos...@nomail.com
>
> >>What really gets me about SQL is that in these Access vs SQL debates,
> >>SQL is only looked at in terms of queries.
>
> >He does not mean ms-access here, but picks/d3 query language that has had
> >the "access" name for about 30 years now.
>
> Thanks! I'm pretty new to even reading these groups, I have a non-DB
> background, and I was immediately and thoroughly confused.
>
> Interesting reading, all in all. I'm doing statistics aggregation
> writing C programs, as part of my day job, end deploy Berkeley DB
> where I need persistent hashes. I'm looking here to explore some of
> the awkward external what-does-it-do alternatives, and the ongoing
> debates are really good for orientation.
>
> best regards
> Patrick
Anthony W. Youngman
<bba...@golden.net> writes
>SQL actually encourages inexperienced users to think about their questions
>in precise terms. It seems that PICK discourages users from thinking at all.
Which is fine if the user actually understands the problem at hand. All
too often (with both SQL *and* Pick) they don't.
Pick is like C, SQL is like Pascal. It's not wise to put C in the hands
of the inexperienced, nor is it wise to give Pick to them. But in the
hands of those who know what they're doing, they'll run rings round the
opposition.
And as I keep saying, few Pick people are qualified in computing. The
chances are they know their own field backwards, but really need an
expert in databases to help them. But, in my experience, EXACTLY the
same could be said for a hell of a lot of MS-Access databases ...
Anthony W. Youngman
<bba...@golden.net> writes
>> Set theory in multi values in pick is very cool.
>
>I might be impressed if pick had not totally ignored set theory.
I might be impressed if SQL did not *enforce* set theory. Yup, a Pick
programmer is an idiot if he ignores it. But if you're handling a real
world scenario where set theory is inappropriate (ie where order is
important?), then SQL forces nasty contortions on you.
Any solution which imposes unnecessary constraints cannot be elegant, by
definition...
Bob Badour
> "Bob Badour" <bba...@golden.net
>
> > wrote in message news:eaKua.152$zQ6.31...@mantis.golden.net...
> > "Albert D. Kallal" <kal...@msn.com> wrote in message
> > news:mEEua.128470$ya.41...@news1.calgary.shaw.ca...
> > > Just to 100% clear on that red/blue car question
> > >
> > > If the sales sold 10 red cars, and 10 blue, then that sales man did in
> > fact
> > > sell a red, and blue car. Thus the sales man should be included.
> >
> > He sold a red and a blue car, but he did not sell a red and blue car.
Some
> > of the cars he sold were red and some were blue but none of them were
> both.
>
> I never meant to mean one car with two colors. That never came into the
> picture. To avoid confusing we should call a two colors car red-blue.
>
> That is not the question.
But it was your answer to the question. You asserted that any user writing
the query to answer your question would write the simple query you gave and
that it was the correct answer to the question. Users might naively write
the query that way, but it is not the correct answer to the question.
> > If a red and blue car
>
> We don't have red/blue cars here.
Look, you cannot make up the rules as you go along. You asserted that any
user would write the simple query you gave and that it was the correct
query. It is not the correct query when cars have an mv color attribute,
which means the solution you gave is not correct and the correct solution is
not as simple as you asserted.
> We just have single color cars for this
> demo.
You cannot change the requirements after the fact to make your incorrect
solution work. You are trying to demonstrate how mv makes things easier and
simpler. It doesn't. Joe gave a correct solution to the question and you
gave an incorrect solution. And you tried to make fun of Joe. LOL
> Next time around I will use the "make" of the car....since color can be a
> source of confusing as your point out.
Make won't help you any. I've been in a car that was both a Pontiac Acadian
and a Chevy Chevette. They were identical models marketed under two brand
names that year, and the one I was in had the markings and logos for both
brands. If you are arguing for mv attributes, then you have to accept mv
attributes.
> No one ever in thread brought up the idea, or even considered the concept
of
> two colour cars here.
I did. You argue in favour of pick's mv attributes so I assume mv
attributes. Joe's solution to the question was undeniably correct, and your
solution was undeniably incorrect in the presense of mv attributes. Since
you were arguing in favour of mv attributes, your solution was undeniably
incorrect.
> Read the original post. The sql posted is wrong....
I did, and the sql posted is correct. You were not able to write a correct
query in pick, and you are not able to recognize a correct query in SQL. The
irony of you making fun of Joe is just too much.
Bob Badour
> "Bob Badour" <bba...@golden.net
>
> > wrote in message news:sIKua.153$YQ6.31...@mantis.golden.net...
> > "Albert D. Kallal" <kal...@msn.com> wrote in message
> > news:T7Jua.143976$ja.54...@news2.calgary.shaw.ca...
> > > Well, that is rather nasty that we have to define a new dictionary
item
> > for
> > > every single new question.
> > >
> > > My simple elegant solution of
> > >
> > > list tblCarsSold SalesRep Make Model Color
> > > with Color = "Red" and With Color = "Blue"
> > >
> > > Will work for all those who sold a red and blue car.
> >
> > But that is different from the original question which asked for all
those
> > who sold a red and a blue car.
>
> No it is not. The original question was give me any sales man who sold a
red
> and a blue car.
You just repeated what I said. A red and blue car is different from a red
and a blue car.
> There was never any restrictions on the fact of just 1 red
> car, or just one blue car.
That doesn't turn a red and blue car into a red and a blue car.
> It could have been 10 blue cars, and 10 red cars.
That still does not turn a red and blue car into a red and a blue car. A red
and blue car is one car. A red and a blue car are two cars.
> That still means he sales man sold a red and blue car.
No, it does not. A salesman who sold a red and a blue car did not
necessarily sell a red and blue car. A salesman who sold a red and blue car
did not necessarily sell a red and a blue car. You don't even understand
your own question.
> There was also never
> any restrictions on the fact that the sales man sold just blue and red
cars
If the salesman sold only blue and red cars, he could not have sold a red
and a blue car because neither red cars nor blue cars are blue and red. If
he sold only blue cars and red cars, he might have sold a red and a blue
car, though.
> the above makes no assumptions on a green car being sold also.
> Read the original question.
Understand the original question. You still don't seem to understand that
there is a big difference in meaning between "a red and a blue" and "a red
and blue".
> However, if the question was extended to only include salesman that sold
> blue and red cars (no other colors), then next statement of mine is again
> correct:
If a salesman sold only blue and red cars, he could not have sold a red and
a blue car because neither red cars nor blue cars are blue and red cars.
> > > list tblCarsSold SalesRep Make Model Color
> > > with Color = "Red" and with Color = "Blue"
> > > and with each Color = "Red" or "Blue"
> >
> > This still does not answer the question of who sold a red and a blue car
> and
> > only red cars and blue cars because it will include all those who sold
red
> > and blue cars.
>
> Yes, it does answer that question. Is not the above question the same
thing?
No. A red and blue car is not a red car and is not a blue car. The original
question allows the salesman to sell green cars or red and blue cars or more
than one red car or more than one blue car. However, if a salesman sold a
red and blue car without selling any red cars and without selling any blue
cars, your query will return the salesman even though he does not fit the
requirements of the question.
> The above list will only return sales men who sold red and blue cars.
It will return salesmen who sold red cars and blue cars and red and blue
cars. However, the original question did not ask for salesmen who sold red
and blue cars. It asked for salesmen who sold a red and a blue car.
You don't even understand your own damned question--even after it has been
explained to you a dozen ways from sunday. How ridiculous is that?
> If
> they sold any other color, then they will not be included in the above.
What
> more can their be to this question?
They will be included if they sold a red and blue car which is a different
colour from a red car and a different colour from a blue car.
> Please expand on the this:
>
> > because it will include all those who sold red
> > and blue cars.
>
> yes...it will, and that is correct!
No, it is not! You asked for all those who sold a red and a blue car. A
salesman can sell a red and blue car without selling either a red car or a
blue car. You make fun of Joe for writing a correct query and you don't even
comprehend sufficient english to understand your own damned question!?!
> We only have single colored cars
> here....
No, we don't. You are arguing in favour of mv attributes. You have to accept
the consequences of mv attributes, or you are just blowing smoke.
Bob Badour
news:b9fshe$kff$03$1...@news.t-online.com...
> Albert,
>
> there are many ways to skin a cat.
> AFAIK your solution will not work with all mv, especially earlier,
> platforms/releases, but if you have a version that will support your
syntax,
> I agree that your solution is more elegant of course.
How can it have any elegance when it is not even correct?
Bob Badour
> You are correct. Nasty was not a good word here! Sorry about that!!
>
> By the way, I did test my example on ap-dos, and that is least 10 years
old.
>
> And, you know what? , no big deal either way!
>
> What works in mv land just works anyway!
>
> I see nothing wrong about using a dict item to help this problem. In fact,
> resorting to defined a dict items is time honoured practice in pick! The
> idea of dict items is a way of life in pick!
>
> I did have my guns blazing out of their holster on that one.
>
> I just wanted to impress that we did not have to create a dict item,...not
> that it is bad idea.
Why would an incorrect query impress anyone?
Bob Badour
> "Albert D. Kallal" <kal...@msn.com
>
> > Read the original post. The sql posted is wrong....
>
> Well, it turns out I was wrong. As mentioned, the sql posted will work.
You are wrong about just about everything, Albert: 1. Joe's query correctly
answers the question. 2. Your query does not correctly answer the question.
3. The correct pick query is not simple (I'm not even sure it's possible).
4. The SQL query that correctly answers the question for multichromatic cars
requires only two tables. 5. There is a big difference between "a red and a
blue car" and "a red and blue car". 6. I understood your question, which you
claimed was simple, perfectly as it was written. 7. It is easier to write a
correct SQL query to answer your question than it is to write a correct pick
query to answer your question. 8. In spite of your denials, you tried to
ridicule Joe. (He often deserves ridicule but not in this case.)
Did you make any other assertions in this thread?
> However, to clarify, if there ever way a possibility of car having two
> colors, then the sql example would have needed a 3 tables.
No, it won't. I will require only two tables: one table to associate
salesreps to cars and a second table to associate cars to colours.
> I should have used car make here!
I would have still assumed an mv make and my arguments would have been every
bit as valid. I have been in multi-make cars.
> Given the assumption that a car can only have one color, then the pick
> solution works, and so does the sql posted!
I see no reason to make that assumption when you are arguing in favour of
the ease and simplicity of mv attributes. Given you waxed poetic on the
alleged simplicity and reasonableness of your question when you were
ridiculing Joe, you will have to accept the question as written as the
complete specification. Your question did not prohibit bichromatic cars, the
pick product will not prohibit users from making cars bichromatic even if
that was never intended, and you are arguing in favour of the simplicity and
ease of mv attributes.
I will not grant you the after-the-fact assumption, and I see no reason why
anyone should.
Bob Badour
news:i1$vGSBM7...@thewolery.demon.co.uk...
> In article <eaKua.152$zQ6.31...@mantis.golden.net>, Bob Badour
> <bba...@golden.net> writes
> >SQL actually encourages inexperienced users to think about their
questions
> >in precise terms. It seems that PICK discourages users from thinking at
all.
>
> Which is fine if the user actually understands the problem at hand. All
> too often (with both SQL *and* Pick) they don't.
I disagree. The majority of users understand good SQL designs quickly and
well even when they have no programming background. English speaking users
(and probably others) have to overcome some differences between idiomatic
english use of the words "and" and "or". If you want bananas and oranges,
you have to use "or", for instance.
The users I have dealt with were generally able to write correct queries,
but not necessarily performant queries. For instance, the idiomatic "and/or"
issue often drives users to use "in" clauses because they understand that
they want the items in their list, and at one time 'in' clauses were
generally slow on most platforms. This is less of an issue now.
> Pick is like C, SQL is like Pascal. It's not wise to put C in the hands
> of the inexperienced, nor is it wise to give Pick to them.
And this violates the basic principles of data management. The whole idea of
separating the data management concern is to make the function accessible to
non-expert users.
> But in the
> hands of those who know what they're doing, they'll run rings round the
> opposition.
If the opposition has adequate physical independence, pick will not run
rings around anything. Quite the opposite.
> And as I keep saying, few Pick people are qualified in computing. The
> chances are they know their own field backwards, but really need an
> expert in databases to help them. But, in my experience, EXACTLY the
> same could be said for a hell of a lot of MS-Access databases ...
I've worked with plenty of SQL users who had no particular background in
computing beyond a short training course on SQL, who were proficient in
their domains and who could write correct SQL queries without any
assistance.
What does that say about pick vs sql?
Kevin Powick
bba...@golden.net says...
> No, it does not. A salesman who sold a red and a blue car did not
> necessarily sell a red and blue car. A salesman who sold a red and blue car
> did not necessarily sell a red and a blue car. You don't even understand
> your own question.
Filename (table) :SALES - Represents car sales
Attribute (field): COLOUR - Colour of vehicle sold. Can be multi-valued
because not all cars are a single colour.
Pick Access/English language query to list names of sales reps who only
sold cars that are either blue or red (single coloured cars).
LIST SALES WITH EVERY COLOUR = "RED" OR WITH EVERY COLOUR = "BLUE"
REPNAME
Pretty simple. I think on some systems EVERY is called EACH.
If you just wanted the cars that were both red and blue (2 colour
cars).
LIST SALES WITH COLOUR = "RED" AND WITH COLOUR = "BLUE" REPNAME
--
Kevin Powick
Patrick Latimer
7. It is easier to write a
> correct SQL query to answer your question than it is to write a correct
pick query to answer your question.
Yea right, show me the SQL
ex.
FILENAME = TEST1
:CT TEST1
1
001 RED
2
001 BLUE
3
001 RED]BLUE
:SELECT TEST1 WITH *A1 = "RED" AND WITH *A1 = "BLUE"
[404] 1 items selected out of 3 items.
>CT TEST1
3
001 RED]BLUE
Albert D. Kallal
> 2. Your query does not correctly answer the question.
yes, it does answer the question.
> 3. The correct pick query is not simple (I'm not even sure it's possible).
No, my example answers the question, and it is simple and elegant.
> 4. The SQL query that correctly answers the question for multichromatic
cars
We never had, nor assumed multichromatic cars.
You are going to need 3 tables if you allow multichromatic cars.
We stared out with two tables:
tblSalesMan
tblCarsSold
How can we possibility have a multichromatic car with the above two tables?
You can't, you need a 3rd table here? If you read the sql, then that
assumption is implied. Please explain how we can have multi-colour cars with
the above tables? Did you not read the sql?
>6. I understood your question, which you
> claimed was simple, perfectly as it was written.
Then why do you claim that my pick query does not solve the question? The
only possible reason I could see why you don't think my aql posted does not
work is due to you thinking we were talking about multi-colour cars.
However, the sql tables don't allow that.
> 7. It is easier to write a
> correct SQL query to answer your question than it is to write a correct
pick
> query to answer your question.
I don't think so. Not only does my sample query read easier, it allows one
to return the color, make, model. The use of the domain aggrate function in
the sql does NOT. Just try and modify that sql to return the color, make,
model. It fails miserably.
I don't understand how, or why you claim that only two tables are need here.
>
> I will require only two tables: one table to associate
> salesreps to cars and a second table to associate cars to colours.
How do you come up with one two tables?
> Your question did not prohibit bichromatic cars
The assumption was single colour cars. And based on the sql posted, unless
there was 3rd table involved for allowing multi-colors, then anyone who read
the sql would easily make the same conclusion that we are dealing with
single color cars here. Please explain with two tables SalesMan, and
carssold how you can have more then one color for car in a normalized sql
design?
Given now that we are clear on single colour cars, the pick aql posted works
fine.
What part does not work? For both the case of a salesman who sold blue
cars, and some red cars, and the 2nd case of
salesman who sold ONLY some (blue cars, and some red cars). Both answers
work just fine.
Are you perhaps looking a 3rd case where only 1 red car was sold, and only
one blue car (in other words, the sales man sold only two cars in total, one
red, and one blue - that question was never asked..but a solution that is
easy).
I not sure what you don't get here.
> I will not grant you the after-the-fact assumption, and I see no reason
why
> anyone should.
I not trying to argue for multi-colour cars. In fact, I thought the reason
why you did not think my solution works is due to confusion on this issue.
Now that we are assuming single colour cars, then you can see how my
solution works.
If you wish, I can post some screen shots on pick of how this works. I just
tested it, and my queries work just fine.
Bob Badour
news:MPG.192617adf...@news21.on.aibn.com...
> In article <cUWua.163$9K3.49...@mantis.golden.net>,
> bba...@golden.net says...
>
> > No, it does not. A salesman who sold a red and a blue car did not
> > necessarily sell a red and blue car. A salesman who sold a red and blue
car
> > did not necessarily sell a red and a blue car. You don't even understand
> > your own question.
>
> Filename (table) :SALES - Represents car sales
>
> Attribute (field): COLOUR - Colour of vehicle sold. Can be multi-valued
> because not all cars are a single colour.
>
> Pick Access/English language query to list names of sales reps who only
> sold cars that are either blue or red (single coloured cars).
>
> LIST SALES WITH EVERY COLOUR = "RED" OR WITH EVERY COLOUR = "BLUE"
> REPNAME
>
> Pretty simple. I think on some systems EVERY is called EACH.
How does the above query restrict the results so that the result excludes
salesreps who sold only red cars or only blue cars? The original question
requires that the salesrep sold a red and a blue car.
If the above query works correctly for the original question, it is not at
all clear how it does so.
> If you just wanted the cars that were both red and blue (2 colour
> cars).
>
> LIST SALES WITH COLOUR = "RED" AND WITH COLOUR = "BLUE" REPNAME
How does this query exclude the red, white and blue cars?
Bob Badour
news:3EBC5178...@NOTHEREp-a-link.com...
> <snip>
> 7. It is easier to write a
> > correct SQL query to answer your question than it is to write a correct
> pick query to answer your question.
> <snip>
> Yea right, show me the SQL
Joe Celko already did that in an earlier message. I suggest you look it up
on deja.
> ex.
> FILENAME = TEST1
>
> :CT TEST1
> 1
> 001 RED
> 2
> 001 BLUE
> 3
> 001 RED]BLUE
> :SELECT TEST1 WITH *A1 = "RED" AND WITH *A1 = "BLUE"
>
> [404] 1 items selected out of 3 items.
> >CT TEST1
>
> 3
> 001 RED]BLUE
Where are the sales reps?
Albert D. Kallal
> > We only have single colored cars
> > here....
>
> No, we don't. You are arguing in favour of mv attributes. You have to
accept
> the consequences of mv attributes, or you are just blowing smoke.
>
No, we DO NOT have multi-colour cars. Why are YOU now making this
assumption? Please show me how the original sql posted can have multi-color
cars. You can not. Anyone who bothered to read the sql would realize this.
You are the ONLY ONE making this argument here that we have cars with more
then one color. That is NOT within the scope of the problem. No one else has
made this assuming. You are the ONLY ONE making this up as we go along here.
Simply put, please explain how the sql posted allows more then one color? If
we allow multi-colour cars, then in fact the sql posted is wrong! (you can't
have this both ways). All problems need a limit of scope.
I will make this simple for you. If you can explain to me how the sql posted
can have cars with more then one color, and I will apologise to you. How can
you insist that the sql works, and my answer does not. The only reason why
my answer would not work is because you are now changing the original
question! You are now changing the problem half way through, and now all of
a sudden we can have multi-colour cars now!. This is absurd. If the problem
DOES ALLOW multi colour cars, then the sql posted is wrong!! You are tying
to have this problem both ways. It is ridiculous to have a problem stated,
and then all of a sudden change the rules of the problem for the pick
solution. This is nuts!
There is no way one can assume that we are allowing cars with more then one
color. It is not in the scope of this question , and it NEVER WAS. YOU are
the only one standing here and shoving this assumption on to everyone.
The fact remains that the sql posted cannot possibility assuming the idea of
two colour cars. So, why then are you? It is simply not reasonable. I am
appealing to you to reason with me here. You must eventually realise that
the scope of the problem never did allow multi-color cars.
Why are you now making the rules here for what can be assumed? The original
question NEVER assumed, or implied in any way that multi colour cars are
allowed. It never did.
Why do you hang on this straw hat? To even suggest, or bring in the concept
of multi-colour cars is just confusing this whole issue.
The sql posted does NOT allow this. The problem stated remains the same in
both sql, or aql. If we change the question, or "problem" between each
system, then what kind of comparison is that? How can we even compare the
problems?
I am simply asking how can one make the assumption of multi-color cars in
the stated problem? If you insist on changing the problem as we go along,
then we have a big trouble here. However, I do await a reasoned and logical
exposing as to how one can make the assumption that the original problem
allows multi-colour cars. It is not a reasonable assuming based on the sql,
and problem as stated. Why do you insist on being un-reasonable?
Why not be a big boy, and admit, that the scope of this problem does not,
and never did intend to allow multi-colour cars. Why is it so difficult for
you to admit and see that the problem as stated does not, and never did
imply multi-colour cars? Please explain to me why you cannot bend on this
issue?
Given single color cars, Then my mv aql posted of mine will work. Here it
is:
list tblCars salesman Color make (i
salesman.. Color..... make......
Joe Red GM
blue Ford
Green Chev
David Red Ford
blue Jeep
Frank Red GM
shelia Blue Chev
Albert Red Ford
blue GM
Mr. Red Red GM
Red GM
Red Ford
[405] 6 items listed out of 6 items.
Now, lets get any salesman who sold a red car and a blue car. (they could
have sold many cars, but they must have sold at least 1 red car, and 1 blue
car). this is the original question. So, the solution I posted is:
list tblCars salesman Color make
with color = "Red" and with color = "Blue"
The results are:
Page 1 tblCars
salesman.. Color..... make......
Joe Red GM
blue Ford
Green Chev
David Red Ford
blue Jeep
Albert Red Ford
blue GM
[405] 3 items listed out of 6 items.
Now, what part of that does not work? And note how much simpler it is then
the sql (which does not even return any of the child records).
Further, it was then asked what if in fact we DID want sales man who sold
ONLY red cars and blue cars (as many of each as you wish, but they all must
be red or blue). We are adding to the original question now.
We get:
:list tblCars salesman Color make
with color = "Red" and with color = "Blue"
and with each color = "Red" or "Blue"
salesman.. Color..... make......
David Red Ford
blue Jeep
Albert Red Ford
blue GM
[405] 2 items listed out of 6 items.
Again, please explain what does not work?
Again, show me how the sql posted is simpler. And show me how the sql can
return things like the color, and make (by the way, the original problem did
return those values...so, to be fair, the sql should return those values,
and as it is posted...it does not!).
Of course, if we do allow multi-color cars, what we have will not work, but
of course, the original sql will also not work, so again, I don't get the
point about trying to change this problem to multi-colour cars. It is a
different problem!
Please, please please, tell the world why this problem asked cannot be the
same, and you insist that the pick solution must deal with a different
problem?
Albert D. Kallal
Until you agree on the scope of this problem, and that everyone else on the
planet was talking about single colour cars, then you will see that my
solution works perfectly.
It is only that you continue to change the problem as stated between the sql
system, and the pick system would my solution not work.
Like, ya, man,..if YOU change the problem, then my solution does not work!
Why in world would you change the problem as stated between the two systems?
It is extremely dishonest of you to change the problem, and then state my
solution does not work. My solution is good, and fine based on the original
stated problem.
I find your behoving of standing behind the idea of "changing" the original
problem to critize my fine solution very sad.
No one, but NO one offered, or made the assuming that we allow multi-color
cars. I just don't know what part about this you don't get.
Kevin Powick
bba...@golden.net says...
> > LIST SALES WITH EVERY COLOUR = "RED" OR WITH EVERY COLOUR = "BLUE"
> > REPNAME
> How does the above query restrict the results so that the result excludes
> salesreps who sold only red cars or only blue cars? The original question
> requires that the salesrep sold a red and a blue car.
Ok, I thought you were just looking for the sales records of blue cars
and red cars. I now think I see what you're "driving" at. You want to
know, out of all the sales that were made, which reps only made sales
where the car was either blue or red, but at least one sale of each.
These reps had no sales of any other colour.
In other words, some reps sold red cars, and blue cars, and green cars,
or even red and blue (2 colour cars), but which reps sold nothing but
red cars and blue cars -- at least one of each, or is it strictly one
of each?
Can you please verify the assumptions, so I can try to provide a
solution?
--
Kevin Powick
Kevin Powick
kal...@msn.com says...
> I mean, I would feel bad if the blue and red car was some "trick" question,
> or we had to do real mental gymnastics here. A salesman who sold red and a
> blue car is not a heavy duty technical relational database question.
> In pick, if you ask a average person to do the above query, they get it
> right. The answer is:
> list tblCarsSold SalesRep Make Model Color
> with Color = "Red" and with Color = "Blue"
Sorry Albert, but this query will list records where both "Red" and
"Blue" are present in the attribute defined as Colour. Both Red and
Blue MUST be present, but this does not exclude the possibility of
other colours, such as Yellow, Black, etc.
Therefore, only cars that are multi-chromatic and a minimum of two
colours (Red and Blue) will be listed. So will cars with three colours
such as Red, Blue, and Black.
I believe this is incorrect.
If the assumption is that the attribute Colour is not multi-valued and
only a single colour code would exit, then your query would have to
read:
list tblCarsSold SalesRep Make Model Color
with Color = "Red" or with Color = "Blue"
--
Kevin Powick
Kevin Powick
kal...@msn.com says...
> My simple elegant solution of
>
> list tblCarsSold SalesRep Make Model Color
> with Color = "Red" and With Color = "Blue"
>
> Will work for all those who sold a red and blue car.
No, it will only show records where both Red and Blue are present
(multi-valued) in the attribute defined by Colour. If the assumption
is no multi-coloured cars exist, then you need to use OR WITH instead
of AND WITH.
> Now, to extend the question to sales man that sold ONLY RED and Only blue
> cars, then we just tack on that additional "only" condition. There is no
> need to define a dictionary.
>
> So, we simply add the condition of
>
> "and with each color = red or blue"
> list tblCarsSold SalesRep Make Model Color
> with Color = "Red" and with Color = "Blue"
> and with each Color = "Red" or "Blue"
This is wrong as well. Adding this condition does not work. It is
contradictory and will never produce any results because the first
condition
with Color = "Red" and with Color = "Blue"
asks for records with both Red and Blue in the Colour attribute, but
the second condition
and with each Color = "Red" or "Blue"
states that every/each colour in the attribute must be the same, only
Red or only Blue.
If you start talking about using the EACH modifier, then the assumption
is that the COLOUR attribute is multi-valued, but I belive you stated
this is not the assumption.
--
Kevin Powick
Bob Badour
Nope. Albert's original challenge was to write a query that answers the
question: "Which salesmen sold both a red and a blue car?"
> Can you please verify the assumptions, so I can try to provide a
> solution?
The question only requires they sold at least one red car and one blue car.
A salesman who sold only a red and blue car sold neither a red car nor a
blue car and should not appear on the list. It doesn't matter how many red
or blue cars a saleman sold provided the salesman sold at least one of each.
Nor does it matter how many cars of other colors a salesman sold as long as
the salesman sold at least one red car and at least one blue car.
Bob Badour
> Read my other post.
>
> Until you agree on the scope of this problem
I agree that the original question fully, simply, clearly and reasonably
defines the scope of the problem as you initially contended when you were
foolishly ridiculing Joe for succeeding.
> It is only that you continue to change the problem as stated between the
sql
> system, and the pick system would my solution not work.
I changed nothing. Joe changed nothing. We both provided SQL solutions that
correctly handle all possible data including all possible multichromatic
cars.
So far, the score on queries that correctly and fully answer the original
question is: SQL 2, PICK 0
> Like, ya, man,..if YOU change the problem
I didn't change the problem. I answered the original question using SQL, and
you have proved totally incompetent to do the same with pick.
> then my solution does not work!
Your solution does not work because it does not answer the original
question. It answers a different question.
> Why in world would you change the problem as stated between the two
systems?
I didn't. Why in the world would you challenge anyone to write a query to
correctly answer a stupid question and then refuse to do the same yourself
after two people successfully take you up on the challenge? Admit it: Pick
sucks. SQL works. Relational rules.
> It is extremely dishonest of you to change the problem
I have not changed the problem. It is extremely dishonest of you to pretend
I have.
> and then state my
> solution does not work.
It is extremely dishonest of you to insist your solution works when it
doesn't work and even more dishonest of you to insist that this proves
anything else.
> My solution is good
> and fine based on the original
> stated problem.
Your solution sucks because it is not a solution to the original stated
problem at all. It answers the wrong question. Instead of answering: "Which
salesmen sold both a red and a blue car?", your query answers: "Which
salesmen sold both a red and a blue car or sold a red and blue car?"
> I find your behoving of standing behind the idea of "changing" the
original
> problem to critize my fine solution very sad.
I find you pathetic. What a loser.
> No one, but NO one offered, or made the assuming that we allow multi-color
> cars.
No one, but NO one prohibited them either. Your query answers the wrong
question. My query answers the correct question. Joe's query answers the
correct question. You failed.
> I just don't know what part about this you don't get.
I don't get how you can be as dense as you appear.
Albert D. Kallal
salesman.. Color..... make......
Frank Red GM
shelia Blue Chev
The results are:
Page 1 tblCars
salesman.. Color..... make......
be red or blue). We are adding to the original question now. This was NOT
a oringal restriction. But, lets thow it in, we get:
:list tblCars salesman Color make
with color = "Red" and with color = "Blue"
and with each color = "Red" or "Blue"
salesman.. Color..... make......
David Red Ford
blue Jeep
Albert Red Ford
blue GM
[405] 2 items listed out of 6 items.
Again, please explain what does not work?
Again, show me how the sql posted is simpler. And show me how the sql can
return things like the color, and make (by the way, the original problem did
return those values...so, to be fair, the sql should return those values,
and as it is posted...it does not! In all fairness, the sql does produce
the results asked for.
Of course, if we do allow multi-color cars, what we have will not work, but
of course, the original sql will also not work, so again, I don't get the
point about trying to change this problem to multi-colour cars. It is a
different problem!
Please, please please, tell the world why this problem asked cannot be the
same, and you insist that the pick solution must deal with a different
problem?
--
Albert D. Kallal (MVP)
Albert D. Kallal
>
> I changed nothing. Joe changed nothing. We both provided SQL solutions
that
> correctly handle all possible data including all possible multichromatic
> cars.
The posted sql does not work for multi-colour cars. We have two tables, the
salesman, and the carsold. The car sold is going to have things like the
color, make, model, serial number etc. That one record simply represents a
car with normal attributes assigned to a car.
To allow that car more then one color, you need another table. How does the
posted sql work, when that atomic record that represents a car has more then
one color? How can this be?
Please explain how a record with model, make, color etc can have more then
one color. Is one to repeat, or duplicate the record over and over for each
color? Who would suggest such a design?.
> Your solution sucks because it is not a solution to the original stated
> problem at all. It answers the wrong question. Instead of answering:
"Which
> salesmen sold both a red and a blue car?"
When we state a red and blue car, we are talking about two cars here. Read
the original question, and look at the original sql, and the original aql
posted. As mentioned, in the scope of this problem, we never dealt with
mutli-colour cars. So, by logic, you have to realize that when we say a red
and a blue car, we mean two cars....
Perhaps, one should state "a red car and a blue car" in place of the
statement red and a blue car. This is intended to mean the same thing, and
is perhaps a source of confusing.
However, if you look at the original sql, or original aql, then you can
easily conclude what is being said here.
Albert D. Kallal
> If the assumption is that the attribute Colour is not multi-valued and
> only a single colour code would exit, then your query would have to
> read:
>
> list tblCarsSold SalesRep Make Model Color
> with Color = "Red" or with Color = "Blue"
"OR" will simply give "one" or the "other". We don't want "or" we need
"both" a red car, and a blue car.
So, if the salesman sold just blue car, the above will return true, and that
is not what we want.
Lets try it:
list tblCars salesman Color make
with color = "Red" OR with color = "Blue"
Page 1 tblCars
salesman.. Color..... make......
Joe Red GM
blue Ford
Green Chev
David Red Ford
blue Jeep
Frank Red GM
shelia Blue Chev
Albert Red Ford
blue GM
Mr. Red Red GM
Red GM
Red Ford
[405] 6 items listed out of 6 items.
We can't use "or", since that gives the above results. That means a Red car
"OR" a blue car. We want a red car AND also those who sold a blue car. So it
follows that we must use "and" in place of the "or".
The question is find any sales man who sold a red car, and also that the
sales man sold a blue car. This in no way hints that they could not have
sold a green car for example.
Simply ask the sales man, did you sell a red car, and also did you sell a
blue car? If the sales man can answer yes to that question, then for sure he
should be included in the list. This is quite clear. We did not ask if he
did, or did NOT sell a green car did we? We also did not ask if he sold 5
red cars, and also 5 blue cars. The original question only asks if a blue
car and also a red car was sold. That salesman certainly could also have
sold 5 red cars and 5 blue cars, but fact STILL remains that he did still
sell a red car, and also sold blue car. The question clearly implies at
least one red car and at least one blue car, but after that, we cannot throw
in further restrictions (because further restricts were NOT STATED. We can't
throw in additional restrictions when none are offered).
Here is other queries that posted already:
The raw table:
list tblCars salesman Color make (i
salesman.. Color..... make......
Joe Red GM
blue Ford
Green Chev
David Red Ford
blue Jeep
Frank Red GM
shelia Blue Chev
Albert Red Ford
blue GM
Mr. Red Red GM
Red GM
Red Ford
[405] 6 items listed out of 6 items.
Now, lets get any salesman who sold a red car *AND* also sold a blue car.
They could
have sold many cars, but they must have sold at least 1 red car, and 1 blue
car. This is the original question. So, the solution I posted is:
list tblCars salesman Color make
with color = "Red" and with color = "Blue"
Note the above use of "and"
The results from the pick command prompt are:
Page 1 tblCars
salesman.. Color..... make......
Joe Red GM
blue Ford
Green Chev
David Red Ford
blue Jeep
Albert Red Ford
blue GM
[405] 3 items listed out of 6 items.
Further, it was then asked what if in fact we really did in fact want
JUST sales man who sold ONLY some red cars and sold ONLY some blue cars.
In other words, no other colors allowed.
Hence, we simply thus going to further narrow down the
above question.
Get me all the red/blue salesman, and further restrict it to
ONLY THOSE who sold red cars, and blue cars. This was NOT a original
restriction. But, lets throw it in, we get:
By throwing in "each color = "Red" or "Blue"" we will get all records with
red, or all records with blue. However, need to restrict this further to
BOTH!!
So, we just keep the original criteria!
This is just two sets being appended together with "and"
:list tblCars salesman Color make
with color = "Red" and with color = "Blue"
and with each color = "Red" or "Blue"
salesman.. Color..... make......
David Red Ford
blue Jeep
Albert Red Ford
blue GM
[405] 2 items listed out of 6 items.
I am rather frustrated, since I don't know what I am failing to communicate
in this problem?
It seems so CRYSTAL clear to me. This seems so simple. I am certain that
some thing or some concept is not being communicated correctly by me.
I really feed bad about my failure to communicate something that seems so
clear.
I kind feel I need to apologise to this group for causing so much noise.
I stand 100% on what I have stated, and the above just seems so natural. I
worked with pick this way for years, and barely even think about it.
sdavmor
Mr. Badour, I draw your attention to a post from way up at the beginning
of this thread:
Albert: >>I give an example of a very simple sql join to find a salesman
who sold a blue and a red car. I am looking for a sales rep who sold both
a red AND a blue car.<<
[[I take this to clearly mean "A sales rep who sold both a red car AND
also a blue car". He doesn't say to find a salesman who sold a car that
is both blue and red. I believe that you introduced that idea.]]
Celko SQL:
SELECT S1.salesrep
FROM Salesreps AS S1,
CarsSold AS C1
WHERE S1.salesrep_id = C1.salesrep_id
AND C1.color IN ('red', 'blue')
GROUP BY S1.salesrep
HAVING COUNT (DISTINCT C1.color) = 2;
Celko continues: >>Or to generalize it, with a table for the colors to be
found:<<
Celko SQL:
CREATE TABLE ColorList(color CHAR(10) NOT NULL PRIMARY KEY);
SELECT S1.salesrep
FROM Salesreps AS S1, CarsSold AS C1
WHERE S1.salesrep_id = C1.salesrep_id
AND C1.color IN (SELECT color FROM ColorList)
GROUP BY S1.salesrep
HAVING COUNT (DISTINCT C1.color)
= (SELECT COUNT(*) FROM ColorList);
Celko: >>This is a relational division, but what do you with MV, when you
want the salesrep who sold ONLY red and blue cars?<<
[[And, to the best that I can tell, without setting up the problem on a
machine of my own, Albert has answered Celko's question with some very
straight-forward AQL. It might be worthwhile for Celko to step back in
here and clarify what he meant by "the salesrep who sold ONLY red and blue
cars". Since he was answering Albert's post, I read that he wanted to
know which salesreps had sold blue cars and red cars and nothing else. If
he meant cars that are painted both red and blue I'd like him to say so,
since only Baldour appears to taken that meaning. In a follow-on post
Albert provided the following:]]
Albert: >>Here is the aql:<<
Albert: >>Given single color cars, Then my mv aql posted of mine will
work. Here it is:<<
Albert AQL:
list tblCars salesman Color make (i
salesman.. Color..... make......
Joe Red GM
blue Ford
Green Chev
David Red Ford
blue Jeep
Frank Red GM
shelia Blue Chev
Albert Red Ford
blue GM
Mr. Red Red GM
Red GM
Red Ford
[405] 6 items listed out of 6 items.
Albert: >>Now, lets get any salesman who sold a red car and a blue car.
(they could have sold many cars, but they must have sold at least 1 red
car, and 1 blue car). this is the original question.<<
[[Which it indeed is, referencing back to the older post, despite Badour's
continued assertion to the contrary.]]
Albert: >>So, the solution I posted is:<<
Albert AQL:
list tblCars salesman Color make
with color = "Red" and with color = "Blue"
Albert: >>The results are:
Page 1 tblCars
salesman.. Color..... make......
Joe Red GM
blue Ford
Green Chev
David Red Ford
blue Jeep
Albert Red Ford
blue GM
[405] 3 items listed out of 6 items.<<
[[Which is correct. AQL understands that it is to return those items
(records) in which the mult-valued color attribute is both "red" and "blue".]]
Albert: >>Now, what part of that does not work? And note how much simpler
it is then the sql (which does not even return any of the child records).<<
[And now we get to where the rubber meets the road.]
Albert: >>Further, it was then asked what if in fact we DID want sales man
who sold ONLY red cars and blue cars (as many of each as you wish, but
they all must be red or blue). We are adding to the original question now.
This was NOT a oringal restriction. But, lets thow it in, we get:<<
Albert AQL:
:list tblCars salesman Color make
with color = "Red" and with color = "Blue"
and with each color = "Red" or "Blue"
[[So now, by adding the last restricting clause, the item (record) for
"Joe" doesn't get returned becuase he sold some red cars, some blue cars
(both wanted) & some green cars (not wanted).]]
salesman.. Color..... make......
David Red Ford
blue Jeep
Albert Red Ford
blue GM
[405] 2 items listed out of 6 items.
--
Cheers,
SDM -- a 21st century schizoid man
http://systemstheory.net internet music project
http://thecleanersystem.com software for dry cleaners
NP: nothing
Mecki
I am having a bit of a problem understanding this bickering about
multi-coloured cars, sorry.
I don´t know sql (I heard it´s pronounced squeal by people who have to use
it :-)) that well, but I thought that it would use only flat tables and
doesn´t allow multivalues.
And since the topic is about 1st normal form, that doesn´t allow multivalues
by definition.
So how can you have 2 colours if you only have one cell for the colour?
Do you call it "red and blue"?
In which case Access (I suppose you call it aql) would regard that as a
different colour too, unless you use wildcards of course.
OTOH this discussion is sort of futile since more and more Pick programmers
lack the knowledge how to write dictionaries and how to design a good mv
database, and many of those who still do are regarded as "to old" to find a
job these days.
Younger IT professionals, managers and graduates have been "brainwashed"
that 1st normal form is the only way to design databases and OO the only way
to write code.
Therefore they use Databasic and/or more and more non-mv languages (sql,
java etc.) for reporting these days and not Access (aql).
Just take a look at comp.databases.pick, and you will see that nearly all
topics are about how to access mv data with non-mv tools like sql and
problems with linux or NT and hardly any how to use Access or even Databasic
and mv database design.
Since the event of built-in indexing of secondary keys in mv databases good,
solid database design often seems to have been replaced by more grunt and
analyst/programmers with supposedly lower-cost coders to maintain (butcher)
existing code.
In the mv-world you seem to get away with it.
I have also trouble with the data structure - after all we should be talking
about the real world here and not a purely theoretical scenario.
In mv I would use at least 3 files (tables) to store the data, and I guess
that most mv analysts will agree with me.
1. SalesReps
2. CarsSold
3. CarColours
The SalesReps file would contain for each rep a record with all relevant
information of the rep like name, address, social security number etc. as
well as a multivalued attribute with the keys of the cars sold by that rep
(call it an index if you like).
The CarsSold file would contain records of all cars sold with the relevant
information for cars like make, model, chassis number, sold by (x-reference
to SalesReps) and of course the colour code(s) which could be multivalued.
The CarColours file would contain all information relating to colours like
colour name (there is no such thing as a blue car), base colour (blue, red,
green), which makes or models come in this colour and whatever else could be
relevant.
This last file would also be used to ensure only valid colours are entered
during data input (i.e. you can´t have a VW in a BMW colour).
There are major differences in the design of mv and flat databases.
Important in this case is the multivalued list of cars sold (index) in the
SalesReps file.
You need to define hierarchies in mv databases to achieve optimal results.
As you can see in our example these hierarchies could go both ways.
To leave that out is IMO a design fault that I have seen especially by so
called analysts who try to enforce 1st normal form in mv databases and
therefore refuse to use multivalues.
Even though Pick has built-in indexing for secondary keys this approach is
IMO very important not only for performance but also for ease of programming
and reporting.
If you try to enforce 1st normal form in a mv database, it becomes a "dog"
probably even worse than any flat DBMS.
So now we have a "properly" designed mv database.
If we want information about cars, we will use the CarsSold file as the base
file to report on.
If we want information about sales reps, we use the SalesReps file.
If we want information about colours we use the CarColours file.
Next we have to talk about dictionaries and virtual attributes.
In Pick you can create virtual attributes in dictionaries, so I would have
dictionaries in the SalesReps file that will display the make, model etc. of
the cars sold and the base colour which is stored actually 2 files down.
That last dictionary, let´s call it BaseColour, will read for each car sold
contained in the multivalued attribute in the SalesReps file the record in
the CarsSold file using the key(s) and then the colour description(s) from
the CarColours file using the key(s) contained in the CarsSold file.
The user will simply type "list SalesReps BaseColour" and will get a listing
of all rep´s ids and the base colours of all cars they sold even though the
information is gathered from 3 different files (tables) - no joins required,
instant response even with multi gigabyte files.
If you want all reps who sold only red and blue cars you would type
list SalesReps with BaseColour = "Red" and with BaseColour = "Blue" and with
every BaseColour = "Red" or "Blue".
If a "Red and Blue" car is included or not depends solely on the dictionary
BaseColour and is independent from the data in the CarsSold or CarColours
file being multivalued or not.
Now, what can be more elegant?
The real world is not flat, it is multi-dimensional.
Just take a map for instance.
It is a flat (2 dimensional) representation of the earth just like sql
tables are the 2 dimensional representation of a multi-dimensional data
world.
You cannot show real distances and proportions on a map, you can only do
that on a globe.
So why do so many people believe that you can store data only in 2
dimensional tables?
Mecki
Kevin Powick
kal...@msn.com says...
> I am rather frustrated, since I don't know what I am failing to communicate
> in this problem?
>
> It seems so CRYSTAL clear to me. This seems so simple. I am certain that
> some thing or some concept is not being communicated correctly by me.
Now that you have presented the file structure you are basing your
solutions on, I understand.
Your Sales file contains one record for each sales rep, with the sales
data multi-valued within it, similar to:
00 RepID
01 RepName
02 InvoiceId]InvoiceId
03 Colour]Colour
04 Make]Make
My Sales file contains one record for each sale (invoice), with the
possibility of multi-coloured cars.
00 InvoiceID
01 RepID
02 RepName
03 Make
04 Colour]Colour
Now that the understanding of the file structures is clear (I hope),
both of our solutions are correct. Actually, yours is "more" correct
(based on your file structure) as I was only trying to show the Red and
Blue sales.
I don't know what the "correct" structure was supposed to be, a summary
file like yours with mv sales data or an invoice file like mine, but in
the end, I believe that complete MV systems are much more flexible and
easier to build/maintain than SQL databases.
--
Kevin Powick
Kevin Powick
bba...@golden.net says...
> The question only requires they sold at least one red car and one blue car.
Ok.
> A salesman who sold only a red and blue car sold neither a red car nor a
> blue car and should not appear on the list.
Yes, Understood.
> It doesn't matter how many red
> or blue cars a saleman sold provided the salesman sold at least one of each.
> Nor does it matter how many cars of other colors a salesman sold as long as
> the salesman sold at least one red car and at least one blue car.
Ok. Now the question becomes one of file structure. In a previous
response to Albert, I agree that his solutions are correct for the file
layout/structure/schema he is using, but "wrong" for the layout I
proposed.
Both the MV and SQL solutions make assumptions about file layouts, so
one is not really comparing apples to apples.
Since file layouts are usually determined by overall system
requirements for data storage and retrieval, both the SQL and MV system
would be designed in such a way that all necessary reports could be
generated.
For me, the important issue is that for any given set of system
specifications, a MV system is inherently easier to create and maintain
than a SQL system.
--
Kevin Powick
Bob Badour
> In article <1Z1va.189$aj5.54...@mantis.golden.net>,
> bba...@golden.net says...
>
> > The question only requires they sold at least one red car and one blue
car.
>
> Ok.
>
> > A salesman who sold only a red and blue car sold neither a red car nor a
> > blue car and should not appear on the list.
>
> Yes, Understood.
>
> > It doesn't matter how many red
> > or blue cars a saleman sold provided the salesman sold at least one of
each.
> > Nor does it matter how many cars of other colors a salesman sold as long
as
> > the salesman sold at least one red car and at least one blue car.
>
> Ok. Now the question becomes one of file structure. In a previous
> response to Albert, I agree that his solutions are correct for the file
> layout/structure/schema he is using, but "wrong" for the layout I
> proposed.
>
> Both the MV and SQL solutions make assumptions about file layouts, so
> one is not really comparing apples to apples.
We are comparing queries that purport to correctly answer the original
question to queries that purport to correctly answer the original question.
That sounds like comparing apples to apples to me.
Both SQL solutions answer the question correctly for all possible data. That
is the only requirement.
I am still waiting for someone to write a pick query that answers the
question correctly. If it is so simple and easy, why am I still waiting?
> Since file layouts are usually determined by overall system
> requirements for data storage and retrieval
Either the query answers the question correctly or it doesn't. Albert
bragged about the alleged benefits of mv attributes and bragged about how
simple and correct his answer was. (In fact, I see Albert wrote entire
articles bragging about pick without understanding either his own question
or his own proposed solution. The hubris astounds me.) The query he proposed
does not answer the original question in an mv system. That has nothing to
do with file layouts. The SQL solutions use two different schemas and yet
both answer the question correctly.
> both the SQL and MV system
> would be designed in such a way that all necessary reports could be
> generated.
If that is the case, why are there no pick programmers capable of writing a
query that correctly answers the question? After all, the challenge was
presented by a pick proponent as a vehicle to demonstrate pick's alleged
ease of use!?!
From what I can see, pick makes it very easy to write incorrect solutions
and atrophies the intellectual skills required to recognize or to care about
correctness.
> For me, the important issue is that for any given set of system
> specifications, a MV system is inherently easier to create and maintain
> than a SQL system.
Instead of making wildly fantastic and completely indefensible assertions,
why don't you just give us the "inherently easier to create" pick query that
correctly answers the question?
Two sql programmers have already provided correct solutions using sql. It
took only minutes to create either of the correct sql queries. They were
easy to create.
At least three pick programmers have attempted to write correct queries
including Albert who made the original challenge, and after two days (well,
a year and a half in Albert's case) and several failed attempts, not one of
the pick programmers has been able to write a pick query that correctly
answers the question. If it is so much easier to write correct pick queries,
why does it seem so impossible?
Albert originally created the challenge more than a year ago in an article
where he made one audacious false statement after another. He concluded
with:
"Anything that is so simple and yet so powerful for sure marks the sign of a
genius at work."
Anything that is so simple and so wrong for sure does not. Both proposed SQL
solutions are simple and powerful. A relational solution would be both
simpler and more powerful still. Not one of the proposed pick solutions is
correct. What does that say about SQL and pick?
100% success for SQL, 100% failure for pick. It doesn't get much starker
than that.
Albert D. Kallal
In fact, lets give your 3 table design a go.
here we go. Lets even make the color table list table use a ID, and not the
"color"
So, we get
list tblColors Color
Page 1 tblColors
tblColors. Color.....
1 Red
2 Blue
3 Green
[405] 3 items listed out of 3 items.
Now, lets make a flat file called cars sold, and that will use the id of
color for the "color" lookup, and this is again a flat table. We will of
course add a bridge def item, so that for each car sold, a multi-valued
keylist in the salesRep table is maintain for us without code.
Our table cars sold becomes:
list tblCarsSold SalesId ColorId Color Make
Page 1 tblCarsSold
tblCarsSold SalesId ColorId Color..... Make......
1 1 1 Red Ford
2 1 2 Blue GM
3 2 1 Red GM
4 2 2 Blue Ford
5 2 3 Green Chev
6 3 1 Red Ford
7 3 2 Blue Jeep
8 4 1 Red GM
9 5 2 Blue Chev
10 6 1 Red GM
11 6 1 Red GM
12 6 1 Red Ford
[405] 12 items listed out of 12 items.
Note how ColorId is just the "color" id, and Color is translate field I make
to the color table. (so, in the future, I will not list ColorId, but just
use the field color).
As mentioned, SalesId is our salesman id, and we define a bridge function to
give us a multi-valued list in the main salesREp file. That bridge function
def for the curious is
<08> btblReps;1;2.
Ok, now in this whole mess, there will be ONLY ONE multi-valued field, and
it is set automatically via the bridge code.
lets List the Main File:
list tblreps Salesman CarsSoldId
Page 1 tblreps
tblreps... Salesman.. CarsSoldId..
2 Joe 3
4
5
3 David 6
7
4 Frank 8
5 shelia 9
6 Mr. Red 10
11
12
1 Albert 1
2
[405] 6 items listed out of 6 items.
Now, I will define the color field as a double trans late:
I will define Make as a single trans late.
Ok, done, now let use my original aql query on the 3 tables (which does
include a double trans late by the way).
The statement is identical to the original example. I will also put in the
(i) just like I did the first time around (I used the id suppress for all
examples)
Anyway, we get:
list tblReps SalesMan Color Make (i
Page 1 tblReps
SalesMan.. Color..... Make......
Joe Red GM
Blue Ford
Green Chev
David Red Ford
Blue Jeep
Frank Red GM
shelia Blue Chev
Mr. Red Red GM
Red GM
Red Ford
Albert Red Ford
Blue GM
[405] 6 items listed out of 6 items.
Ok, now lets just do the same thing as before. the IDENTICAL aql works. And
in fact, the cars sold table is completely flat. The colors table is also
completely flat.
By the way, these results are actual cut and past from my pick terminal
session.
list tblReps SalesMan Color Make
with Color = "Red" and with Color = "Blue"
Page 1 tblReps
SalesMan.. Color..... Make......
Joe Red GM
Blue Ford
Green Chev
David Red Ford
Blue Jeep
Albert Red Ford
Blue GM
[405] 3 items listed out of 6 items.
And, lets even do the last example, to further narrow down to reds and blue.
We get:
list tblReps SalesMan Color Make
with Color = "Red" and with Color = "Blue"
and with each Color = "Red" or "Blue"
Page 1 tblReps
SalesMan.. Color..... Make......
David Red Ford
Blue Jeep
Albert Red Ford
Blue GM
[405] 2 items listed out of 6 items.
Simply amazing that the exact same aql statement works, but the difference
here is that we are now dealing with 3 separate tables here!
Yes, Mecki, your table design is excellent with Pick!. Considering that I
can take my sample crappy "one table" design that I used for the demo, and
then and take something like your THREE table design, and use my EXACT SAME
aql statement is got to knock a few socks off here.
I certainly think the above example deserves a round of applause for Richard
Pick.
As mentioned, the screens are cut and paste from an actual pick terminal
session.
(I am using a 12 year old version of pick here = a very old ap-dos on my
notebook).
Not bad...not bad at all....
Albert D. Kallal
My soltion as posted is:
Given single color cars, Then my mv aql posted of mine will work. Here it
is:
list tblCars salesman Color make (i
salesman.. Color..... make......
Joe Red GM
blue Ford
Green Chev
David Red Ford
blue Jeep
Frank Red GM
shelia Blue Chev
Albert Red Ford
blue GM
Mr. Red Red GM
Red GM
Red Ford
[405] 6 items listed out of 6 items.
Now, lets get any salesman who sold a red car and a blue car. (they could
have sold many cars, but they must have sold at least 1 red car, and 1 blue
car). This is the original question. So, the solution I posted is:
list tblCars salesman Color make
with color = "Red" and with color = "Blue"
The results are:
Page 1 tblCars
salesman.. Color..... make......
Joe Red GM
blue Ford
Green Chev
David Red Ford
blue Jeep
Albert Red Ford
blue GM
[405] 3 items listed out of 6 items.
Now, what part of that does not work? And note how much simpler it is then
the sql (which does not even return any of the child records?).
Further, it was then asked what if in fact we DID want sales man who sold
ONLY red cars and blue cars (as many of each as you wish, but they all must
be red or blue). We are adding to the original question now. This was NOT
a oringal restriction. But, lets throww it in, we get:
:list tblCars salesman Color make
with color = "Red" and with color = "Blue"
and with each color = "Red" or "Blue"
salesman.. Color..... make......
David Red Ford
blue Jeep
Albert Red Ford
blue GM
[405] 2 items listed out of 6 items.
Again, please explain what does not work?
--
Patrick Latimer
Bob Badger wrote:
> "Patrick Latimer" <lati...@NOTHEREp-a-link.com> wrote in message
> news:3EBC5178...@NOTHEREp-a-link.com...
<snip>
>>Yea right, show me the SQL
>
>
> Joe Celko already did that in an earlier message. I suggest you look
it up
> on deja.
I believe it's called GOOGLE now.
>
>
>
>>ex.
>>FILENAME = TEST1
>>
>>:CT TEST1
>> 1
>>001 RED
>> 2
>>001 BLUE
>> 3
>>001 RED]BLUE
>>:SELECT TEST1 WITH *A1 = "RED" AND WITH *A1 = "BLUE"
>>
>>[404] 1 items selected out of 3 items.
>> >CT TEST1
>>
>> 3
>>001 RED]BLUE
>
>
> Where are the sales reps?
>
The salesman is salesman #3. I can list all the information I
want about him. But you statement it is easier to write a
SQL statement than Access/English statement is incorrect. I know.
I write both.
and
SORT TEST1 WITH *A1 = "RED" AND WITH *A1 = "BLUE" TOTAL CNT
(CNT is a standard dictionary setup by most designers for most files)
is much simpler than
SELECT S1.salesrep
FROM Salesreps AS S1,
CarsSold AS C1
WHERE S1.salesrep_id = C1.salesrep_id
AND C1.color IN ('red', 'blue')
GROUP BY S1.salesrep
HAVING COUNT (DISTINCT C1.color) = 2;
as well as less verbose. So explain again how;
"It is easier to write a correct SQL query to
answer your question than it is to write a correct
pick query to answer your question."
Pick = 1 SQL = 0
Bob Badour
> Here is the aql:
>
> Given single color cars
Your original post did not limit the possible colors for cars in any way. It
allows multiple colors for cars. Both SQL solutions allow multiple colors
for cars. Both SQL solutions correctly answer the original question without
changing it no matter what data are in the database.
Your query does not answer the original question of which salesmen sold both
a red and a blue car. Your query answers the question of which salesmen
either sold a red and blue car or sold both a red and a blue car.
> Then my mv aql posted of mine will work.
It does not work. Your intellectual dishonesty disgusts me. To demonstrate
whether mv attributes make things easy as you originally proclaimed as your
goal, you need to include mv data:
Sheila { Red, Blue } GM ...
would do.
{ Sheila, Frank } { Red, Blue } { Pontiac, Chev }
would do even better.
Your query fails when either of the above appear in the data.
> list tblCars salesman Color make
> with color = "Red" and with color = "Blue"
>
> The results are:
>
> Page 1 tblCars
>
> salesman.. Color..... make......
>
> Joe Red GM
> blue Ford
> Green Chev
>
> David Red Ford
> blue Jeep
>
> Albert Red Ford
> blue GM
Sheila { Red, Blue} GM
{ Sheila, Frank } { Red, Blue } { Pontiac, Chevy }
Oops, that doesn't work. Does it? I guess mv attributes only work when there
are no mv attributes.
> Now, what part of that does not work?
The part with pick and mv attributes.
> And note how much simpler it is then
Note how incorrect it is. It fails to answer the original question.
> the sql (which does not even return any of the child records).
The original question does not ask for child records, you dope. What kind of
asshole puts out a challenge and then criticizes people for actually meeting
the challenge correctly?
> Further, it was then asked what if in fact we DID want sales man who sold
> ONLY red cars and blue cars
First, provide a query that correctly answers the original question and then
worry about extending it to answer different questions.
> Again, please explain what does not work?
The part with pick. The part with mv attributes. Duh! It's pointless to
continue the discussion if you cannot even comprehend simple english
sentences.
> Again, show me how the sql posted is simpler.
Provide a pick query that correctly answers the question and I will show how
SQL is simpler. First, I will use a stopwatch... tick, tick, tick...
It took only minutes to create correct SQL solutions. You have been working
at the pick solution for more than a year and still no correct answer. Tick,
tick, tick...
> In all fairness, the sql does produce
> the results asked for.
In all fairness, no pick solution produces the results asked for. If I
accept that doctoring the data to make the query work is legitimate, I can
propose:
Select * from salesrep;
as my solution to the problem. It would be just as correct as your proposed
pick query, and just look at how much simpler it is!
> Of course, if we do allow multi-color cars
Um, you mean if we actually allow mv data in our mv attributes? If we
actually accept that pick allows multi-color cars and we cannot stop it?
Wow, what a stretch!
> what we have will not work
Duh! What you have does not work. Doctoring the data does not make it work.
You are simply deluding yourself.
> but
> of course, the original sql will also not work
Prove it. I have already proved that the sql WILL work for all possible data
including any multichromatic cars.
> so again, I don't get the
> point about trying to change this problem to multi-colour cars.
You don't get your original question. You don't get your own proposed
solution. You don't get that the problem always included multi-colour cars.
And you are the one who was stupid enough to point and laugh at Joe for
getting all of the above correct.
I did not change the problem. Joe did not change the problem. We just solved
the problem correctly. We both produced SQL queries that correctly answer
the original question. We both did so easily and quickly because we were
using SQL, which obviously is much simpler to use and much more powerful.
> It is a
> different problem!
The problem is and has always been: "Which salesmen sold both a red and a
blue car?" Your query does not answer that question. Both SQL queries answer
exactly that question. It is the same problem. You failed, and you lack the
intellectual integrity to admit your failure. And you had the gall to try to
ridicule Joe for succeeding!!!
You, yourself, repeated how simple, clear, complete and reasonable the
question is as a problem statement. That problem statement allows
multi-color cars and it is the simple, clear, complete and reasonable
problem statement YOU devised!
> Please, please please, tell the world why this problem asked cannot be the
> same, and you insist that the pick solution must deal with a different
> problem?
I have only asked that the pick solution answer the original question--just
as two SQL solutions answer the original question. The pick solution cannot
doctor the data to make the results appear to answer the original question.
The query must answer the correct question.
It was YOUR challenge. If you want to back out of your challenge and admit
failure, do so. Otherwise, put up or shut up. You cannot just pretend you
succeeded without actually succeeding. Have you no integrity?
Patrick Latimer
I'd ignore this guy, his logic is
flawed and he seems to want to
change the rules when his argument
is successfully refuted.
Patrick, <;=)
Bob Badour
news:b9ii4j$j1ud5$1...@ID-19805.news.dfncis.de...
I did not introduce anything--mv attributes did. Albert's solution to his
own challenge is incorrect. Albert's query will return salesmen who sold no
red cars, who sold no blue cars, but who sold one or more red and blue cars.
This means the query will include salesmen who do not meet the criteria of
the original question.
> [[And, to the best that I can tell, without setting up the problem on a
> machine of my own, Albert has answered Celko's question with some very
> straight-forward AQL.
Then you need to do better, because Albert failed to even answer his own
question let alone Joe's.
> It might be worthwhile for Celko to step back in
> here and clarify what he meant by "the salesrep who sold ONLY red and blue
> cars".
Perhaps at least one pick proponent could provide a query that correctly
answers Albert's original question before any pick proponents start making
further demands or challenges.
> Albert: >>Now, lets get any salesman who sold a red car and a blue car.
> (they could have sold many cars, but they must have sold at least 1 red
> car, and 1 blue car). this is the original question.<<
>
> [[Which it indeed is, referencing back to the older post, despite Badour's
> continued assertion to the contrary.]]
Where have I asserted the contrary? I have correctly repeated the damned
question over and over again to prove that Albert's query answers the wrong
question. Albert's query includes salesmen who sold no red cars and no blue
cars but one or more red and blue cars. The original question does not ask
for those salesmen! Albert's query answers the wrong question. His query
answers: "Now, let's get any salesman who sold a red and blue car or who
sold a red and a blue car." And that is NOT the original question.
You pick folks are doing a great job of convincing me that pick damages the
brain. No cohort can be so universally stupid innately.
> [[Which is correct. AQL understands that it is to return those items
> (records) in which the mult-valued color attribute is both "red" and
"blue".]]
It is not correct. The input data were doctored to make the result appear
correct. It is not valid to simply omit the test data that causes the test
to fail.
> [[So now, by adding the last restricting clause,
First, provide a query that answers the original question, then worry about
extending the solution to answer other questions.
Patrick Latimer
>>>Nope. Albert's original challenge was to write a query that answers the
>>>question: "Which salesmen sold both a red and a blue car?"
I answered that one son.
*some* SQL people must be dense.
Patrick :)-
P.S. bye
Bob Badour
> "Bob Badour" <bba...@golden.net
>
> >
> > I changed nothing. Joe changed nothing. We both provided SQL solutions
> that
> > correctly handle all possible data including all possible multichromatic
> > cars.
>
> The posted sql does not work for multi-colour cars.
Yes, it does. Both sql solutions work for multi-colour cars. The first
solution, Joe's solution, requires users to encode each unique combination
of colours with a unique value, which means that red and blue cars will have
a uniquely identified colour. The second solution, my solution, works both
when users associate multiple colours with a car and when users encode
combinations of colours with a unique value.
> To allow that car more then one color, you need another table.
No, I don't. The colour domain can directly represent colour combinations.
Joe's solution works for every way a user can enter a multichromatic car in
his schema. My solution works for every way a user can enter a
multichromatic car in my schema. Your solution answers the wrong question.
You failed your own challenge proving that pick is difficult and feeble.
> How does the
> posted sql work, when that atomic record that represents a car has more
then
> one color?
The colour attribute indicates a unique combination of colours as a scalar
value. How does the posted sql not work?
> How can this be?
In the case of SQL, easily, simply and powerfully.
> Please explain how a record with model, make, color etc can have more then
> one color.
Please explain why a scalar value cannot describe a combination of colours.
> Is one to repeat, or duplicate the record over and over for each
> color?
No.
> Who would suggest such a design?.
Certainly not me. I guess your straw man is worthless.
> > Your solution sucks because it is not a solution to the original stated
> > problem at all. It answers the wrong question. Instead of answering:
> "Which
> > salesmen sold both a red and a blue car?"
>
> When we state a red and blue car, we are talking about two cars here.
There is a difference between "a red and blue car", which is a single car,
and "a red and a blue car", which are two cars. The indefinite article has
meaning in english. The original question asked for a red and a blue car and
it did not ask for red and blue cars.
> Read
> the original question, and look at the original sql, and the original aql
> posted.
I did. The sql Joe posted answers the correct question. Joe's query returns
all salesmen who sold at least one red car and at least one blue car
regardless of what other cars they sold. Joe's query will not return any
salesman who sold no red cars or no blue cars even if the salesman sold a
red and blue car.
The sql I posted likewis answers the correct question.
The pick query you posted answers the wrong question.
You failed your own challenge and every pick proponent reading this thread
has so far failed your challenge proving that pick is difficult and feeble
while sql is simple and powerful.
> As mentioned, in the scope of this problem, we never dealt with
> mutli-colour cars.
Look, I will not accept your cop-out. The original question defines the
scope of the problem, and it does not in any way exclude the possibility of
multichromatic cars in the original data. When you were making fun of Joe
for giving a query that answers the correct question, you went on and on and
on and on about how simple, clear, complete and reasonable the original
question is as a problem statement. You cannot now say the question is
incomplete and requires additional unstated assumptions. Given you are
arguing in favour of the ease and power of mv attributes, you will just have
to accept their consequences.
> So, by logic, you have to realize that when we say a red
> and a blue car, we mean two cars....
I already realize that. Do you realize that your query answers the wrong
question? I have explained it to you many times now. Any reasonably
intelligent individual who speaks english should understand by now.
> Perhaps, one should state "a red car and a blue car" in place of the
> statement red and a blue car. This is intended to mean the same thing, and
> is perhaps a source of confusing.
I don't care whether we change the question to a synonymous question. I
already understand that "a red and a blue car" means the same thing as "a
red car and a blue car". I also understand that "a red and blue car" does
not mean the same thing.
> However, if you look at the original sql, or original aql, then you can
> easily conclude what is being said here.
I don't need to look at either to understand what the question says. The
question speaks for itself. When I look at Joe's SQL query, I can see that
it answers the correct question. When I look at your pick query, I can see
that it answers a different question. Joe succeeded, and you failed. Get it?
Bob Badour
> "Kevin Powick" <nos...@nomail.com
>
> > If the assumption is that the attribute Colour is not multi-valued and
> > only a single colour code would exit, then your query would have to
> > read:
> >
> > list tblCarsSold SalesRep Make Model Color
> > with Color = "Red" or with Color = "Blue"
>
> "OR" will simply give "one" or the "other". We don't want "or" we need
> "both" a red car, and a blue car.
>
> So, if the salesman sold just blue car, the above will return true, and
that
> is not what we want.
>
> Lets try it:
>
[snip]
> We can't use "or", since that gives the above [incorrect] results. That
means a Red car
> "OR" a blue car. We want a red car AND also those who sold a blue car. So
it
> follows that we must use "and" in place of the "or".
>
> The question is find any sales man who sold a red car, and also that the
> sales man sold a blue car. This in no way hints that they could not have
> sold a green car for example.
It in no way hints that they could not have sold a red and blue car either.
They could have. The problem is your solution will include those who sold
one or more red and blue cars even though they sold no red cars or no blue
cars.
> Simply ask the sales man, did you sell a red car, and also did you sell a
> blue car? If the sales man can answer yes to that question, then for sure
he
> should be included in the list. This is quite clear. We did not ask if he
> did, or did NOT sell a green car did we?
We also did not ask if he sold a red and blue car, did we? If your query is
correct, why will it include salemen who sold a red and blue car without
selling both a red and a blue car?
> We also did not ask if he sold 5
> red cars, and also 5 blue cars. The original question only asks if a blue
> car and also a red car was sold. That salesman certainly could also have
> sold 5 red cars and 5 blue cars, but fact STILL remains that he did still
> sell a red car, and also sold blue car. The question clearly implies at
> least one red car and at least one blue car, but after that, we cannot
throw
> in further restrictions (because further restricts were NOT STATED. We
can't
> throw in additional restrictions when none are offered).
Exactly. We cannot throw in additional restrictions that there are no red
and blue cars.
...incomplete because the raw test data did not include any red and blue
cars.
> Further, it was then asked what if in fact we really did in fact want
> JUST sales man who sold ONLY some red cars and sold ONLY some blue cars.
First, provide a query that answers the original question and then worry
about extending it to answer other questions.
> I am rather frustrated, since I don't know what I am failing to
communicate
> in this problem?
You are transmitting just fine. Either your reception or perception is
whacked.
> It seems so CRYSTAL clear to me.
That's because you lack the cognitive ability to understand your original qu
estion or to compose a pick query that answers it.
> This seems so simple.
If you refuse to acknowledge any of your own failings or any of pick's
failings, I suppose everything could seem simple. It doesn't make anything
simple--you are just in denial.
> I am certain that
> some thing or some concept is not being communicated correctly by me.
As I say above, you are transmitting just fine. Your communication problem
relates either to your reception or to your perception.
Joe correctly composed a query that answers the original question. Joe
correctly communicated that query to you. Somehow, after you received the
information, you incorrectly perceived that the query failed to answer the
original question.
You composed a query that answers a different question. Even though you
composed the query and transmitted it to others, when you received your own
information back, you incorrectly perceived that the query answers the
original question. I transmitted to you exactly what question your query
does answer and it is a different question from the original question.
Somehow, after you received the information, you continued to perceive that
your query answers the original question.
You have been transmitting all kinds of smokescreens and red herrings. I
transmit the information of how the smokescreens and red herrings are
irrelevant. Somehow, after you receive the information, you persist in
perceiving relevance to them.
I cannot be certain whether your communication problem relates to reception
or to perception, but I can narrow it down to one of the two for you. Good
luck.
> I really feed bad about my failure to communicate something that seems so
> clear.
You have plenty of reasons to feel badly, but that is the least of them.
> I kind feel I need to apologise to this group for causing so much noise.
You could end the noise right now by admitting you failed or by providing a
query that answers the correct question.
> I stand 100% on what I have stated
This is consistent with a failure either to receive or to perceive the
information that every assertion you have made was proved false.
> and the above just seems so natural.
Fantasy worlds can seem like whatever you want them to, but that does not
make them real.
> I
> worked with pick this way for years, and barely even think about it.
The lack of thought is quite evident. I find it sad that pick has so badly
handicapped your intellect.
Bob Badour
The queries fail to answer the original question even with the file
structures above. Why are pick programmers so universally unable to analyse
their own queries?
> I don't know what the "correct" structure was supposed to be, a summary
> file like yours with mv sales data or an invoice file like mine, but in
> the end, I believe that complete MV systems are much more flexible and
> easier to build/maintain than SQL databases.
Why, then, can no pick programmer provide a query that correctly answers the
original question? You folks have had 2 days now, and Albert had more than a
year head-start.
I find no evidence to conclude flexibility or ease for pick. In this thread,
I find only evidence that pick damages minds.
Ed Sheehan
> I asked for only red and/or not blue.
Another guy wrote:
>No you didn't.
One guy wrote:
> No, I/you did too.
Another wrote:
> Yes, I did not!
One guy wrote:
> Prove it!
Another wrote:
> The proof is in the pudding.
One wrote:
> What has that got to do with cars, pudding brain?
Another wrote:
> That's right. I win.
One wrote:
> Yeah, you win a whole case of Jello Pudding Snacks.
Another wrote:
> Right. Deliver it in a red and/or not blue car.
Albert D. Kallal
> It does not work. Your intellectual dishonesty disgusts me. To demonstrate
> whether mv attributes make things easy as you originally proclaimed as
your
> goal, you need to include mv data:
>
> Sheila { Red, Blue } GM ...
>
> would do.
>
> { Sheila, Frank } { Red, Blue } { Pontiac, Chev }
>
> would do even better.
Sure, I'll add the above and let Shelia have a two color car. Further, I
will use my example that I used for Mecki in this thread (that means we have
3 tables, but if you read the other post, you will see the aql was IDENTICAL
in both cases). I will also add a expression called ColorCount which is
simple count of the number of colors for each car. This again is a common
and simple "calculated" field in pick (it is a expression - for the curious,
that field expression is (s(fieldname # "").).
Ok, so, with your new data, we now get:
list tblCarsSold SalesId SalesRep Color ColorCount Make (I
Page 1 TBLCARSSOLD
SalesId SalesRep.. Color..... ColorCount Make......
1 Albert Red 1 Ford
1 Albert Blue 1 GM
2 Joe Red 1 GM
2 Joe Blue 1 Ford
2 Joe Green 1 Chev
3 David Red 1 Ford
3 David Blue 1 Jeep
4 Frank Blue 1 Jeep
4 Frank Red 1 GM
5 shelia Red 2 Chev
Blue
6 Mr. Red Red 1 GM
6 Mr. Red Red 1 GM
6 Mr. Red Red 1 Ford
[405] 13 items listed out of 13 items.
Note how Shelia is the only one with a car with a color count of two.
The query to get that person who sold a car that has two colors, and it is
red/blue would be:
LIST tblCarsSold SalesId SalesRep Color ColorCount Make
with colorCount = "2"
and with color = "Red" and with Color = "Blue"
Page 1 TBLCARSSOLD
SalesId SalesRep.. Color..... ColorCount Make......
5 shelia Red 2 Chev
Blue
[405] 1 items listed out of 12 items.
It seems rather simple to me, but that was not the original problem.
--
Albert D. Kallal
Patrick Latimer
Patrick, <;=)
Albert D. Kallal
Patrick Payne
routine to a dictionary item. Now that requires programming, ok that
is true. But your SQL statement is plenty complicated enough that
your normal joe could not create it either.
Example:
orders.... a1...............
1 red
blue
2 red
3 blue
4 green
red
5 green
blue
red
We want to get only item 1 and 5
001 a
002 0
003
004
005
006
007
008 a;'red,blue']CALL SELECT.BY.COLOR.RTNE
009 R
010 1
001 SUBROUTINE SELECT.BY.COLOR.RTNE(IN.DATA)
002 RECORD=ACCESS(3)
003 COLORS=RECORD<1>
004 CONVERT ',' TO CHAR(254) IN IN.DATA
005 OK=1; FOUNDS=''
006 NUM.IN.DATA=DCOUNT(IN.DATA,CHAR(254))
007 FOR X=1 TO NUM.IN.DATA
008 FOUNDS<X>='0'
009 NEXT X
010 NUMBER.ITEMS=DCOUNT(COLORS,CHAR(253))
011 FOR X=1 TO NUMBER.ITEMS UNTIL NOT(OK)
012 COLOR=COLORS<1,X>
013 LOCATE COLOR IN IN.DATA SETTING FOUND ELSE FOUND=0
014 IF FOUND THEN
015 FOUNDS<FOUND>='1'
016 END ELSE
017 OK=0
018 END
019 NEXT X
020 IF FOUNDS<*> = "0" THEN OK=0; * comment this out if you just want
red/blue on any invoice instead of red/blue required on each invoice
021 IN.DATA=OK
022 RETURN
ORDERS.... SELECT.BY.COLOR A1.............
3 0 blue
4 0 green
red
5 0 green
blue
red
1 1 red
blue
2 0 red
* Now I may still not have exactly what you wanted, you said ONLY RED
AND BLUE CARS. Does this mean a red and blue on each invoice, or
overall only Red And Blue on any invoice? The routine can be adjusted
to do either. In fact, the extent to what I can do is probably much
more advanced than you can do in a SQL statement, since I am writing
in code.
sdavmor <sda...@somewhereincyberspace.com> wrote in message news:<b9ii4j$j1ud5$1...@ID-19805.news.dfncis.de>...
Bob Badour
> <snip>
> >>>Nope. Albert's original challenge was to write a query that answers the
> >>>question: "Which salesmen sold both a red and a blue car?"
> <snip>
> I answered that one son.
Sorry, Patrick. Your query did not answer the question. It included saleman
#3 who sold a red and blue car but neither a red car nor a blue car.
> *some* SQL people must be dense.
Some are. Lots are not. I have yet to see evidence of a pick person who is
not too dense to provide a pick query that correctly answers Albert's
original question. So far, I have only seen evidence of one pick person
bright enough to understand why Albert's solution fails.
Bob Badour
But that is not the original question, and you cannot answer the original
question even with the colorCount derived field.
You have failed again.
By the way, if we assume derived fields, the sql query that correct answers
the original question is:
select * from rbreps;
The relational equivalent is:
rbreps
Now, show me the pick solution that not only answers the correct question
but is simpler than a single word. Good luck.
Bob Badour
> I have posted an answer with a two color car just to make you happy....
If you have succeeded, I certainly have not seen it. Did you cross post the
solution to comp.databases where everyone can see it? Does the correct
solution even exist?
Bob Badour
> Bob,
>
> I am having a bit of a problem understanding this bickering about
> multi-coloured cars, sorry.
>
> I don´t know sql (I heard it´s pronounced squeal by people who have to use
> it :-)) that well, but I thought that it would use only flat tables and
> doesn´t allow multivalues.
Multivalues are not necessary. Albert created a challenge to demonstrate the
relative simplicity, ease and power of mv attributes. He demonstrated the
opposite.
It is easy to write an SQL query that correctly answers the original
question from Albert's challenge. Both Joe's solution and my solution allow
multichromatic cars without resorting to mv attributes or to relation-valued
attributes. Scalars suffice to represent the data.
Both Joe's solution and my solution correctly answer the original question.
None of the proposed pick queries answer the original question, and so far,
none of the pick proponents have demonstrated the ability to even recognize
that fact.
I conjecture that no pick query can correctly answer the original question
in the presence of mv colour attributes. (Anyone arguing in favour of mv
attributes must accept them in the solution.) Pick and mv are so complex,
difficult and feeble one cannot express a query to correctly answer the
original question.
We have already established that pick is complex, difficult and feeble
relative to SQL; otherwise, someone would have proposed a pick query that
correctly answers the original question by now. I am now conjecturing that
pick and mv are complex, difficult and feeble in absolute terms. All anyone
has to do to disprove the conjecture is provide a pick query that correctly
answers the original question while allowing mv colour attributes and
multiple sales per salesman.
To make it interesting, I'll even offer a reward. A book of my choice to the
first pick query I receive that I am convinced answers the original
question. Please CC attempted solutions to my email address
mailto:bba...@golden.net so that I can determine the order of submission.
Neither SQL solution required a view so I will not accept any pick solution
that relies on the dictionary. If we were to allow views, the SQL solution
becomes: "Select * from some_view". The relational equivalent would simply
be "some_view", and I cannot imagine a simpler solution.
The original question was (near enough): "Which salesmen sold both a red and
a blue car?"
> And since the topic is about 1st normal form, that doesn´t allow
multivalues
> by definition.
> So how can you have 2 colours if you only have one cell for the colour?
> Do you call it "red and blue"?
A user can choose some value to represent a distinct colour combination.
Yes, they might use the value you propose above.
> In which case Access (I suppose you call it aql) would regard that as a
> different colour too, unless you use wildcards of course.
However, pick also allows users to describe colour combinations by entering
multiple colours in the mv colour attribute. In an argument for the
simplicity, ease and power of mv attributes, I think it is only fair to
allow mv attributes. Don't you?
If users can enter colour combinations as multiple colour values in a single
attribute, the query must still answer the original question after some user
does so.
> OTOH this discussion is sort of futile since more and more Pick
programmers
> lack the knowledge how to write dictionaries and how to design a good mv
> database, and many of those who still do are regarded as "to old" to find
a
> job these days.
I agree that the discussion is futile, but for completely different reasons.
> Just take a look at comp.databases.pick, and you will see that nearly all
I prefer not to, thank you. I have already determined that pick is a lousy
product that is damaged beyond all repair, as anyone with even an elementary
education in data management should be able to determine for themselves. I
have no desire to waste my time with pick. I only see this thread because it
is cross-posted to comp.databases.
> There are major differences in the design of mv and flat databases.
I find your 'flat' characterization of n-dimensional relations rather
stupid. The major difference between mv and the relational model is: When
using the relational model, one can address any fact. Because pick cannot
address every fact, it is impossible to write a pick query that correctly
answers Albert's original question. The pick language becomes needlessly
complex requiring special 'with' versions of every comparison operator etc.
and it still lacks the expressive power to handle mv attributes through mv
pointers.
> Important in this case is the multivalued list of cars sold (index) in the
> SalesReps file.
As is the mv colour attribute on every car.
> You need to define hierarchies in mv databases to achieve optimal results.
The above only means that pick lacks physical independence, which further
increases complexity and difficulty while decreasing flexibility and power.
[irrelevant physical crap snipped]
> So now we have a "properly" designed mv database.
Do we?
> If we want information about cars, we will use the CarsSold file as the
base
> file to report on.
> If we want information about sales reps, we use the SalesReps file.
> If we want information about colours we use the CarColours file.
So, while the SQL solution requires only two tables, your solution requires
three files replacing an mv colour attribute with an mv pointer attribute to
a CarColours file. I don't see how that helps.
> Next we have to talk about dictionaries and virtual attributes.
> In Pick you can create virtual attributes in dictionaries, so I would have
> dictionaries in the SalesReps file that will display the make, model etc.
of
> the cars sold and the base colour which is stored actually 2 files down.
> That last dictionary, let´s call it BaseColour, will read for each car
sold
> contained in the multivalued attribute in the SalesReps file the record in
> the CarsSold file using the key(s) and then the colour description(s) from
> the CarColours file using the key(s) contained in the CarsSold file.
> The user will simply type "list SalesReps BaseColour" and will get a
listing
> of all rep´s ids and the base colours of all cars they sold even though
the
> information is gathered from 3 different files (tables) - no joins
required,
> instant response even with multi gigabyte files.
In the above query, how does the user tell the difference between a SalesRep
who sold a single red and blue car and a SalesRep who sold two cars: one red
and one blue? This is vitally important for answering the original question,
because the query must not include any salesreps who sold a red and blue car
without selling both a red and a blue car.
> If you want all reps who sold only red and blue cars you would type
> list SalesReps with BaseColour = "Red" and with BaseColour = "Blue" and
with
> every BaseColour = "Red" or "Blue".
That is not the original question, and even if it were, your solution does
not work correctly. Try again. The question does not care how many other
cars a saleman sold as long as the salesman sold both a red and a blue car.
If you remove the "with every" clause, you revert to Albert's incorrect
query. Albert's query and your rephrasing of it here will return salesmen
who sold no red cars and/or no blue cars as long as they sold a red and blue
car.
> If a "Red and Blue" car is included or not depends solely on the
dictionary
> BaseColour and is independent from the data in the CarsSold or CarColours
> file being multivalued or not.
> Now, what can be more elegant?
A correct solution.
> The real world is not flat, it is multi-dimensional.
An n-ary relation is n-dimensional, and a relational database comprises a
universe of multiple possibly overlapping n-dimensional spaces. As I said, I
find your use of the word 'flat' rather stupid.
> It is a flat (2 dimensional) representation of the earth just like sql
> tables are the 2 dimensional representation of a multi-dimensional data
> world.
A picture of an apple is two-dimensional but the apple is three-dimensional.
Likewise, a tabular representation of a relation is two-dimensional but the
relation, itself, is not.
You are confusing the representation with the thing.
> So why do so many people believe that you can store data only in 2
> dimensional tables?
Why do so many people believe that n-dimensional relations (tables if you
prefer) have only 2 dimensions? Quite young children can tell the difference
between a picture of an apple and the apple itself.
Bob Badour
news:b6da8ff0.03051...@posting.google.com...
> sdavmor <sda...@somewhereincyberspace.com> wrote in message
news:<b9ii4j$j1ud5$1...@ID-19805.news.dfncis.de>...
> > Bob Badour wrote:
> >
> > > "Kevin Powick" <nos...@nomail.com> wrote in message
> > > news:MPG.192652c75...@news21.on.aibn.com...
> > >
> > >>In article <Ja_ua.183$xA4.52...@mantis.golden.net>,
> > >>bba...@golden.net says...
> > >>
> > >>
> I feel it is easy to do what you want, your admin has to add a call
> routine to a dictionary item.
Regardless what you feel, requiring an administrator to write a program to
fulfill a basic dbms function does qualify as "easy".
> Now that requires programming, ok that
> is true. But your SQL statement is plenty complicated enough that
> your normal joe could not create it either.
I disagree. I have worked with plenty of normal joe's who could write a
correct query to answer the question in SQL. I have yet to encounter an
expert pick programmer who can write a query to answer Albert's original
question.
> Example:
> orders.... a1...............
>
> 1 red
> blue
> 2 red
> 3 blue
> 4 green
> red
> 5 green
> blue
> red
>
> We want to get only item 1 and 5
No, neither your data nor your claim for what we want to get has anything to
do with Albert's original question. Where are the salemen? Where are the
cars? Which colours are associated with which car? Which saleman sold which
car?
> * Now I may still not have exactly what you wanted, you said ONLY RED
> AND BLUE CARS.
No, I did not say that. I am continually amazed that pick programmers cannot
comprehend written english. It appears to be a universal failing.
The original question is: "Which salesmen sold both a red and a blue car?"
The question says nothing about only having red and blue cars.
> Does this mean a red and blue on each invoice, or
> overall only Red And Blue on any invoice?
The question asks for salemen who sold both a red and a blue car, and it
does not ask for a red and blue anything. Although, the query should return
a salesman who sold a red and a blue car even if he also sold a red and blue
car or a green car or more than one red car etc.
> The routine can be adjusted
> to do either.
I can write application programs in any number of programming languages
including a variety of SQL stored procedure languages too, but that does not
prove anything about the simplicity, ease or power of anything. It only
demonstrates that one can choose needless complexity if one wants to.
> In fact, the extent to what I can do is probably much
> more advanced than you can do in a SQL statement, since I am writing
> in code.
So, pick is complicated and difficult but you can make it still more
complicated and still more difficult. Good. That's nice.
Albert D. Kallal
> > LIST tblCarsSold SalesId SalesRep Color ColorCount Make
> > with colorCount = "2"
> > and with color = "Red" and with Color = "Blue"
>
> But that is not the original question, and you cannot answer the original
> question even with the colorCount derived field.
>
> You have failed again.
No, to quote my website, and the original question:
<quote>
...and lets just ask what sales man sold a blue and a red car.
</quote>
Note that I don't say blue and red car, I said "a blue and a red car". That
means two cars.
So, I did in fact have the word "a" in the orginal question. I was not sure,
but I just checked, and it is the case.
And, to quote further:
<quote>
I am looking for a sales rep who sold both a red AND a blue car.
</quote>
That means the sales man sold a red car, and also sold a blue car. So, even
reading my site, it is quite clear that we never looked for a single "red
and blue" car. We never did! The "a" makes this clear.
You simply can't find the question of looking for a car that is blue and
red. No such thing was ever suggested. However, now with the above posted
aql, the stuff still works for both cases anyway.
Watch how the above data looks when we run the query from the tblReps table
and NOT from the tblCarsSold (we can run this from either table by the way).
We get:
list tblReps SalesMan Color Make ColorCount
Page 1 tblReps
SalesMan.. Color..... Make...... ColorCount
Albert Red Ford 1
Blue GM 1
Joe Red GM 1
Blue Ford 1
Green Chev 1
David Red Ford 1
Blue Jeep 1
Frank Red GM 1
Blue Jeep 1
shelia Red Blue Chev 2
Mr. Red Red GM 1
Red GM 1
Red Ford 1
[405] 6 items listed out of 6 items.
Now, both solutions are available here. So far, we have not had to resort to
procedures, or even a stored proc.
Kevin Powick
bba...@golden.net says...
> The queries fail to answer the original question even with the file
> structures above. Why are pick programmers so universally unable to analyse
> their own queries?
Bob. You are, or think you are, an SQL guru, but it's obvious that you
do not understand multi-valued data and how Pick queries act on that
data.
Having re-read the thread, I think there was confusion on Albert's part
that a car could be multi-coloured, but this does not mean the Pick
query is not possible, nor is it difficult.
Also, while Albert has expressed his frustration in a civilized manner,
he has not stooped to insults and profanity as you have. I'm a little
different than Albert and don't mind telling you that I think you're a
fucking asshole, but that's not really the point is it? You've pretty
much proven that yourself quite handily.
Will I provide a mv query solution for you? No, because you're lack of
understanding of MV data and MV queries would lead you to believe that
the solution is incorrect, so it's pointless.
You have successfully reinforced your belief that SQL is superior and
that MV databases are feeble. Fine. Go away and be happy with your
convictions. You "win".
--
Kevin Powick
Ed Sheehan
they'd tell you if you asked nicely.
I like blue cars more than red. Oxidation, you know. Especially in the
desert, where it gets plenty hot. How many hot cars did they sell?
I have a Buick Regal T-Type, but I'd trade in in an instant for a Park
Avenue Ultra (Supercharged). It's nearly as fast as a T, and a way-nice
ride. I'd like it in white because it's cooler in the summer, and in the
snow - instant camo!
Ed
Bob Badour
> In article <7ubva.213$5n6.57...@mantis.golden.net>,
> bba...@golden.net says...
>
> > The queries fail to answer the original question even with the file
> > structures above. Why are pick programmers so universally unable to
analyse
> > their own queries?
>
> Bob. You are, or think you are, an SQL guru
No, Kevin, I am not that nor do I think I am that. I am a programmer and a
computer systems analyst who is highly critical of SQL for its many
failings--none of which involve the lack of support for multivalued
attributes, by the way.
> but it's obvious that you
> do not understand multi-valued data
If I don't understand multi-valued data, and you do, please provide a query
that correctly answers the original question from Albert's challenge and
that correctly handles multi-valued data. Is that such an unreasonable
request?
It may be unreasonable to ask it of you, but it is certainly not
unreasonable to ask it of Albert after he strutted around like some peacock
ridiculing Joe for providing a correct solution. Now, is it?
> and how Pick queries act on that
> data.
If that is the case, why is it that I can understand Albert's query answers
the question: "Which salesmen sold a red and blue car or sold both a red and
a blue car?" instead of the original question while not one of the pick
proponents can? Just add a salesman who sold a red and blue car but no red
cars and no blue cars to Albert's test data, and you will see that the query
answers the wrong question. Undeniably.
> Having re-read the thread, I think there was confusion on Albert's part
> that a car could be multi-coloured, but this does not mean the Pick
> query is not possible, nor is it difficult.
Then provide the correct query. Not one pick proponent has been able to
provide a query that correctly answers this "simple, clear, reasonable"
question in spite of several attempts over periods of time ranging from days
to years. And remember, it was a pick proponent who strutted around making
all kinds of noise about how the example question he came up with
demonstrates the simplicity, ease and power of pick's mv attributes.
If it is so simple, easy and powerful, where is the pick query that answers
the correct question?
> Also, while Albert has expressed his frustration in a civilized manner,
> he has not stooped to insults and profanity as you have.
So? Why should I care how some stupid asshole who ridicules people for
correctly answering his challenge expresses his denials, frustrations and
embarassments? Ridiculing Joe when he thought Joe's solution failed to
answer the question was certainly no more civilized behaviour than mine.
If he cannot take it, why would he dish it out?
> I'm a little
> different than Albert and don't mind telling you that I think you're a
> fucking asshole, but that's not really the point is it?
At least I am a fucking asshole who comprehends english, admits when he is
wrong, doesn't pretend to be something else, and doesn't go around claiming
he can do the impossible.
> You've pretty
> much proven that yourself quite handily.
Good. Now, can you provide the pick query that correctly answers the
original question?
> Will I provide a mv query solution for you? No, because you're lack of
> understanding of MV data and MV queries would lead you to believe that
> the solution is incorrect, so it's pointless
In other words, the query is impossible to write, and you lack the
intellectual integrity to simply admit it. Unable to cope with your
failings, you have to somehow project the failings onto me. Hey! If that's
what you gotta do to live with yourself, then that's what you gotta do. I'm
happy to serve.
Bob Badour
> > >
> > > LIST tblCarsSold SalesId SalesRep Color ColorCount Make
> > > with colorCount = "2"
> > > and with color = "Red" and with Color = "Blue"
> >
> > But that is not the original question, and you cannot answer the
original
> > question even with the colorCount derived field.
> >
> > You have failed again.
>
> No, to quote my website, and the original question:
>
> <quote>
> ...and lets just ask what sales man sold a blue and a red car.
> </quote>
>
> Note that I don't say blue and red car
I know. This is why the query you gave, which returns salesmen who did not
sell either a red or a blue car, answers the wrong question. If a salesman
sells only a red and blue car, your query includes the salesman in the
result. The result is wrong because the query is wrong.
> I said "a blue and a red car". That
> means two cars.
I know. So your query, which returns a salesman who sold only one car, a red
and blue car, does not answer the original question. See? The query is
wrong.
Prove it to yourself. Add the necessary test data and run your query.
> So, I did in fact have the word "a" in the orginal question.
I know--I never claimed you didn't. That indefinite article has meaning
though. It excludes a salesman who sold only a red and blue car from the
valid result. However, it excludes nothing from the possible test data. Your
query answers a different question: "lets just ask what sales man sold a
blue and red car or sold a blue and a red car." Your proposed query answers
a different question from the original question you posed. The challenge is
to answer the original question.
> I was not sure,
> but I just checked, and it is the case.
I don't know why you would have to check that you agree with my
interpretation of the question. But if you felt it was necessary, fine. Now,
just verify the behaviour of you query using a complete set of test data...
> And, to quote further:
>
> <quote>
> I am looking for a sales rep who sold both a red AND a blue car.
> </quote>
>
> That means the sales man sold a red car, and also sold a blue car. So,
even
> reading my site, it is quite clear that we never looked for a single "red
> and blue" car. We never did! The "a" makes this clear.
Your query, however, does look for salemen who sold a red and blue car.
That's why the query answers the wrong question. You failed to meet your own
challenge, and you lack the intellectual capacity to recognize that fact
even after it is explained to you. Sheesh!
> You simply can't find the question of looking for a car that is blue and
> red. No such thing was ever suggested.
If it was not suggested, why does your query look for them? Your question
doesn't mention green cars or yellow cars, but I am sure you understand that
your test data must contain cars of other colours. The combination red and
blue is just another colour like green or yellow. According to the question
you posed, salesmen get neither credit nor demerit for selling a red and
blue car. However, according to the pick query you propose, salesmen do get
credit for selling a red and blue car.
Imagine a situation where the red cars are not selling, the blue cars are
not selling but the red and blue cars just fly off the lot. The dealership
decides that the salespeople are just not pushing the red cars and the blue
cars enough so it introduces an incentive: Every salesman who sells both a
red and a blue car each month gets a $100 bonus.
The dealership will be very unhappy with your query when it discovers that
all of the salesmen have been getting the bonus every month without selling
any red or blue cars.
> However, now with the above posted
> aql, the stuff still works for both cases anyway.
No, it does not. Your query answers the wrong question. The original
question does not exclude red and blue cars from the test data; although, it
excludes salemen who sold only a single red and blue car from the valid
result.
Huh? Where is the solution that answers the original question? "shelia" is
not a valid answer to who sold both a red and a blue car. She sold neither.
The dealership is going to be pissed after it discovers you gave her the
$100.
Kevin Powick
bba...@golden.net says...
> If I don't understand multi-valued data, and you do, please provide a query
> that correctly answers the original question from Albert's challenge and
> that correctly handles multi-valued data. Is that such an unreasonable
> request?
LIST REPSALES WITH COLOUR = "RED" AND WITH COLOUR = "BLUE" REPID
REPNAME
It's deceptively simple and probably appears incorrect to you because
you cannot seem to comprehend what the structure of REPSALES would be.
> So? Why should I care how some stupid asshole who ridicules people for
> correctly answering his challenge expresses his denials...
Considering how many posts you've made, you seem to care a great deal.
> At least I am a fucking asshole who comprehends english
I'm glad we can all agree on at least one thing.
> In other words, the query is impossible to write
I've taken the bait because I had a really good day today, and seeing
you work yourself into a seething rage has actually become quite
amusing.
--
Kevin Powick
Anthony W. Youngman
<bba...@golden.net> writes
>"Anthony W. Youngman" <thew...@nospam.demon.co.uk> wrote in message
>news:i1$vGSBM7...@thewolery.demon.co.uk...
>> In article <eaKua.152$zQ6.31...@mantis.golden.net>, Bob Badour
>> <bba...@golden.net> writes
>> >SQL actually encourages inexperienced users to think about their
>questions
>> >in precise terms. It seems that PICK discourages users from thinking at
>all.
>>
>> Which is fine if the user actually understands the problem at hand. All
>> too often (with both SQL *and* Pick) they don't.
>
>I disagree. The majority of users understand good SQL designs quickly and
>well even when they have no programming background. English speaking users
>(and probably others) have to overcome some differences between idiomatic
>english use of the words "and" and "or". If you want bananas and oranges,
>you have to use "or", for instance.
>
>The users I have dealt with were generally able to write correct queries,
>but not necessarily performant queries. For instance, the idiomatic "and/or"
>issue often drives users to use "in" clauses because they understand that
>they want the items in their list, and at one time 'in' clauses were
>generally slow on most platforms. This is less of an issue now.
>
Were they able to design correct (and good) databases? And not just
write queries to get the data out.
I have found users pick up relational ideas quickly. IF they've got a
good teacher :-) And I'd much rather they used their relational design
in a multi-value database...
>
>> Pick is like C, SQL is like Pascal. It's not wise to put C in the hands
>> of the inexperienced, nor is it wise to give Pick to them.
>
>And this violates the basic principles of data management. The whole idea of
>separating the data management concern is to make the function accessible to
>non-expert users.
>
But what if data management is the concern of the non-expert user? You
seem to be talking solely about queries, whereas I'm looking at the
whole thing holistically (although I haven't particularly stressed that
point).
>
>> But in the
>> hands of those who know what they're doing, they'll run rings round the
>> opposition.
>
>If the opposition has adequate physical independence, pick will not run
>rings around anything. Quite the opposite.
>
What do you mean? By "physical independence" do you mean it is nice and
clean mathematically, and the user is insulated from the realities of
data storage?
Well, the solution to solving RSA-encrypted messages is nice and clean
mathematically. In principle it's dead easy. In practice you very
rapidly hit the brick wall known as "computational explosion".
Providing an answer to a SQL query is nice and clean mathematically. All
too often, however (and especially if the user is oh so conveniently
insulated from how the database stores the data), the practice means you
hit the wall of computational explosion. Why do people keep on throwing
hardware at relational databases? Why do we keep on hearing horror
stories about how slow they are?
With Pick, the query engine usually has pretty much just ONE available
path through the data. It doesn't need an optimiser - the DESIGN
constrains the engine such that it's near 100% efficient.
Let's see you come up with a war-story where a Pick system was replaced
by a relational DB, and produced similar results with similar hardware.
All the stories I hear are along the lines where the "experts" (in
relational technology, that is) have ported the system and, on hardware
MANY times more powerful, have difficulty matching the speed of the old
system. What about that one I think I mentioned in a previous thread,
where consultants were boasting of a 10% speed improvement. Until the
person in charge of the Pick system pointed out they had a twin-Pentium-
III and were oh so proud of beating a P90...
>
>> And as I keep saying, few Pick people are qualified in computing. The
>> chances are they know their own field backwards, but really need an
>> expert in databases to help them. But, in my experience, EXACTLY the
>> same could be said for a hell of a lot of MS-Access databases ...
>
>I've worked with plenty of SQL users who had no particular background in
>computing beyond a short training course on SQL, who were proficient in
>their domains and who could write correct SQL queries without any
>assistance.
>
>What does that say about pick vs sql?
Well it don't say much about SQL, that's for sure. Those Pick users
weren't writing queries - they were writing the entire database
application !!!
Cheers,
Wol
--
Anthony W. Youngman - wol at thewolery dot demon dot co dot uk
Witches are curious by definition and inquisitive by nature. She moved in. "Let
me through. I'm a nosey person.", she said, employing both elbows.
Maskerade : (c) 1995 Terry Pratchett
Anthony W. Youngman
<bba...@golden.net> writes
>> *some* SQL people must be dense.
>
>Some are. Lots are not. I have yet to see evidence of a pick person who is
>not too dense to provide a pick query that correctly answers Albert's
>original question. So far, I have only seen evidence of one pick person
>bright enough to understand why Albert's solution fails.
>
work fine, because "red and blue" is not red, nor is it blue.
Oh, and while it may not be pertinent to the question, you can't have a
car that is legally "red and blue" over here, because the system cannot
cope with more than one colour. I would presume that's because it's been
written in SQL :-)
Anthony W. Youngman
<bba...@golden.net> writes
>"Patrick Payne" <patric...@yahoo.com> wrote in message
>news:b6da8ff0.03051...@posting.google.com...
>> sdavmor <sda...@somewhereincyberspace.com> wrote in message
>news:<b9ii4j$j1ud5$1...@ID-19805.news.dfncis.de>...
>> > Bob Badour wrote:
>> >
>> > > "Kevin Powick" <nos...@nomail.com> wrote in message
>> > > news:MPG.192652c75...@news21.on.aibn.com...
>> > >
>> > >>In article <Ja_ua.183$xA4.52...@mantis.golden.net>,
>> > >>bba...@golden.net says...
>> > >>
>> > >>
>
>> I feel it is easy to do what you want, your admin has to add a call
>> routine to a dictionary item.
>
>Regardless what you feel, requiring an administrator to write a program to
>fulfill a basic dbms function does qualify as "easy".
Since when have Pick databases had admins? Most USERS write programs.
Oh, by the way, adding a call routine to a dictionary is a lot easier
than writing a program ...
Bob Badour
> In article <Rfdva.225$aA6.57...@mantis.golden.net>, Bob Badour
> <bba...@golden.net> writes
> >> *some* SQL people must be dense.
> >
> >Some are. Lots are not. I have yet to see evidence of a pick person who
is
> >not too dense to provide a pick query that correctly answers Albert's
> >original question. So far, I have only seen evidence of one pick person
> >bright enough to understand why Albert's solution fails.
> >
> Well, if the car's colour is "red and blue", then Albert's solution will
> work fine, because "red and blue" is not red, nor is it blue.
It does not work fine because the query will include salemen who sold a car
with both red and blue in the mv colour attribute even though they did not
sell a red car and did not sell a blue car.
Pick will not prevent a user from putting both red and blue values in the
colour attribute.
> Oh, and while it may not be pertinent to the question, you can't have a
> car that is legally "red and blue" over here, because the system cannot
> cope with more than one colour. I would presume that's because it's been
> written in SQL :-)
That would be rather presumptious given we already have two SQL solutions
that both handle multichromatic cars correctly and still no pick solution
that does.
Bob Badour
> In article <6Udva.229$bF6.57...@mantis.golden.net>, Bob Badour
> <bba...@golden.net> writes
> >"Patrick Payne" <patric...@yahoo.com> wrote in message
> >news:b6da8ff0.03051...@posting.google.com...
> >> sdavmor <sda...@somewhereincyberspace.com> wrote in message
> >news:<b9ii4j$j1ud5$1...@ID-19805.news.dfncis.de>...
> >> > Bob Badour wrote:
> >> >
> >> > > "Kevin Powick" <nos...@nomail.com> wrote in message
> >> > > news:MPG.192652c75...@news21.on.aibn.com...
> >> > >
> >> > >>In article <Ja_ua.183$xA4.52...@mantis.golden.net>,
> >> > >>bba...@golden.net says...
> >> > >>
> >> > >>
> >
> >> I feel it is easy to do what you want, your admin has to add a call
> >> routine to a dictionary item.
> >
> >Regardless what you feel, requiring an administrator to write a program
to
> >fulfill a basic dbms function does qualify as "easy".
>
> Since when have Pick databases had admins? Most USERS write programs.
I saw the program given. If USERS write those programs, pick must require
that all the USERS are expert programmers. That is not a reasonable
requirement for users.
> Oh, by the way, adding a call routine to a dictionary is a lot easier
> than writing a program ...
So you assert. What I saw was a program. I'll believe my eyes over your
assertions any day.
Bob Badour
> In article <uAfva.230$ES6.58...@mantis.golden.net>,
> bba...@golden.net says...
>
> > If I don't understand multi-valued data, and you do, please provide a
query
> > that correctly answers the original question from Albert's challenge and
> > that correctly handles multi-valued data. Is that such an unreasonable
> > request?
>
> LIST REPSALES WITH COLOUR = "RED" AND WITH COLOUR = "BLUE" REPID
> REPNAME
>
> It's deceptively simple and probably appears incorrect to you because
> you cannot seem to comprehend what the structure of REPSALES would be.
Deceptive is right, because the query does not answer the correct question.
I can think of any number of structures REPSALES could have. However, the
above query is not correct for any of them. It will still include a salesman
who sold a red and blue car even though the salesman sold no red cars and no
blue cars. Such a salesman does not meet the criteria of the original
question which requires the query to list only salemen who sold at least one
red car and at least one blue car.
> > So? Why should I care how some stupid asshole who ridicules people for
> > correctly answering his challenge expresses his denials...
>
> Considering how many posts you've made, you seem to care a great deal.
How does my attempt to communicate to him and to other readers of this
thread demonstrate an interest in how he expresses his denials, frustrations
and embarassments?
Is english a second language for you?
> > At least I am a fucking asshole who comprehends english
>
> I'm glad we can all agree on at least one thing.
>
> > In other words, the query is impossible to write
>
> I've taken the bait because I had a really good day today, and seeing
> you work yourself into a seething rage has actually become quite
> amusing.
I am happy to inspire your imagination. However, I must point out that the
query you gave does not answer the original question; it answers the same
question as Albert's query, which is not the original question at all.
Bob Badour
Most casual users prefer to spend their time working on their area of
expertise and not on designing databases; however, I recall several who were
quite proficient at designing databases for temporary extracts for various
analyses.
> And not just
> write queries to get the data out.
Most casual users only want to get the data out, and it is difficult to
evaluate their ability for tasks they don't want to do.
> I have found users pick up relational ideas quickly.
> IF they've got a
> good teacher :-)
The teacher has to teach the ideas, but I have not noticed a particular need
for exceptional teaching skills. Beyond that I have only noticed a
resistance to learning relational ideas among programmers. The general
population seems to get the ideas pretty quick.
> And I'd much rather they used their relational design
> in a multi-value database...
From what I have seen, it seems that would destroy their cognitive ability.
I don't know why you would wish that on anybody.
> >> Pick is like C, SQL is like Pascal. It's not wise to put C in the hands
> >> of the inexperienced, nor is it wise to give Pick to them.
> >
> >And this violates the basic principles of data management. The whole idea
of
> >separating the data management concern is to make the function accessible
to
> >non-expert users.
> >
> But what if data management is the concern of the non-expert user?
Separating the data management concern from the programming concern makes
data management accessible to non-expert users. I apologize if I was not
clear enough earlier.
> >> But in the
> >> hands of those who know what they're doing, they'll run rings round the
> >> opposition.
> >
> >If the opposition has adequate physical independence, pick will not run
> >rings around anything. Quite the opposite.
> >
> What do you mean? By "physical independence" do you mean it is nice and
> clean mathematically, and the user is insulated from the realities of
> data storage?
Yes. That is part of physical independence. Physical independence means
having as much flexibility as possible to use any physical structure from
which one can derive the nice clean mathematical design.
> Well, the solution to solving RSA-encrypted messages is nice and clean
> mathematically. In principle it's dead easy. In practice you very
> rapidly hit the brick wall known as "computational explosion".
Physical independence does not cause "computational explosion". Nice try,
though.
> Providing an answer to a SQL query is nice and clean mathematically. All
> too often, however (and especially if the user is oh so conveniently
> insulated from how the database stores the data), the practice means you
> hit the wall of computational explosion.
This means the SQL database you are using lacks sufficient physical
independence.
> Why do people keep on throwing
> hardware at relational databases?
I have never known anyone to throw hardware at a relational database. I have
known people to throw hardware at problems caused by products not
implementing the relational model, and I have known people to throw hardware
at problems caused by uneducated practitioners of which there are far too
many.
> Why do we keep on hearing horror
> stories about how slow they are?
Because you are listening to marketeers spread misinformation and ignorance.
> With Pick, the query engine usually has pretty much just ONE available
> path through the data.
Exactly. It has no independence whatsoever. The logical model maps directly
onto one and only one physical arrangement. If one needs to change the
physical arrangement of things to meet evolving performance requirements,
one must rewrite all of the code.
> It doesn't need an optimiser - the DESIGN
> constrains the engine such that it's near 100% efficient.
It's not that it doesn't need an optimiser: I cannot use one. This not only
restricts the dbms, but it restricts the user. The user is simply unable to
express most of their queries.
> Let's see you come up with a war-story where a Pick system was replaced
> by a relational DB, and produced similar results with similar hardware.
Anecdotes do not prove anything. One cannot generalize from an anecdote. One
can predict from a generalization.
> All the stories I hear are along the lines where the "experts" (in
> relational technology, that is) have ported the system and, on hardware
> MANY times more powerful, have difficulty matching the speed of the old
> system.
I have heard all kinds of stories about failed projects, because those are
newsworthy. I am unfamiliar with any failed projects involving an expert in
relational technology. Which specific examples do you refer to and which
specific experts were involved? Or is this just some urban myth you are
repeating?
> What about that one I think I mentioned in a previous thread,
> where consultants were boasting of a 10% speed improvement. Until the
> person in charge of the Pick system pointed out they had a twin-Pentium-
> III and were oh so proud of beating a P90...
What makes you think those consultants were expert in anything except
getting the invoice prepared on the first of the month?
> >> And as I keep saying, few Pick people are qualified in computing. The
> >> chances are they know their own field backwards, but really need an
> >> expert in databases to help them. But, in my experience, EXACTLY the
> >> same could be said for a hell of a lot of MS-Access databases ...
> >
> >I've worked with plenty of SQL users who had no particular background in
> >computing beyond a short training course on SQL, who were proficient in
> >their domains and who could write correct SQL queries without any
> >assistance.
> >
> >What does that say about pick vs sql?
>
> Well it don't say much about SQL, that's for sure. Those Pick users
> weren't writing queries - they were writing the entire database
> application !!!
What an ugly, tedious task to force onto users when they want to get on with
doing their real jobs! Yuck!
Did you see Albert's challenge elsewhere in this thread? At least four
expert pick programmers have tried to write a query that correctly answers a
very simple question over the last couple days, and not one of them has
succeeded while we already have two completely different SQL solutions that
answer the correct question. What does that say about pick vs. sql?
The question was: "Which salesmen sold both a red and a blue car?" Simple,
no?
Kevin Powick
bba...@golden.net says...
> > LIST REPSALES WITH COLOUR = "RED" AND WITH COLOUR = "BLUE" REPID
> > REPNAME
> the
> above query is not correct for any of them. It will still include a salesman
> who sold a red and blue car even though the salesman sold no red cars and no
> blue cars.
It will not.
The colour code for a red car is RED.
The colour code for a blue car is BLUE.
So what is the colour code for a red and blue car? Maybe it's RDBL?
It doesn't matter because one thing for sure is that it is neither RED
nor BLUE.
The query works.
--
Kevin Powick