Re: MultiValue Databases

From: John <no_at_email>
Date: Tue, 07 Jun 2005 17:16:08 +0100
Message-ID: <42a5c843$0$13769$db0fefd9_at_news.zen.co.uk>

Neo wrote:

>>... Sincerely, Gene Wirchenko

>
>
> I realize that you, Hugo, Lee and Ed are professionals. Could you give
> a brief summary of your expertise with respect to Relational Databases
> (ie. college degree, years of experience, positions held in companies
> and/or consultant positions) ?
>
> Since no one has posted RM scripts for the trival xrdb examples, I post
> one myself below. The script models three persons named John, Mary and
> Bob. John has two phone numbers, Mary has one, Bob has none. Assume a
> person can have 0 to many phone numbers. Assume a phone numbers can be
> assigned to multiple persons. I have normalized the phone numbers to
> allow adding various attributes to some phone numbers later.
>
> While no one example can provide a complete understanding, it can
> provides a starting point. Could you explain why RM is more
> general/superior when it exposes more of the underlying data model and
> implementation details (ie TABLES containers, ID fields, data types,
> etc). Since I am obviously no RM expert, could you help write a query
> to find a person with phone numbers 111-1111 and 222-2222. I started
> the query, but I could not complete it.
>
> **** Script for xrdb *****************************************
> // Create type person and make it an item of main directory.
> (CREATE type inst *person & dir item it)
>
> // Create type phone#.
> (CREATE type inst *phone#)
>
> // Create John with two phone numbers.
> (CREATE person inst *john)
> (CREATE john phone# +111-1111)
> (CREATE john phone# +222-2222)
>
> // Create Mary with one phone number.
> (CREATE person inst *mary
> & it phone# +222-2222)
>
> // Create Bob with no phone number.
> (CREATE person inst *bob)
>
> // Find things with phone# 111-1111 and 222-2222.
> // Finds John.
> (SELECT * phone# 111-1111
> & * phone# 222-2222)
>
> **** Script for RMDB *****************************************
> CREATE TABLE phone (int phoneID, char number);
> CREATE TABLE person (int personID, char name);
> CREATE TABLE person_phone (int personID, int phoneID);
>
> INSERT INTO phone (phoneID, number) VALUES (1, "111-1111");
> INSERT INTO phone (phoneID, number) VALUES (2, "222-2222");
>
> INSERT INTO person (personID, name) VALUES (1, "john");
> INSERT INTO person (personID, name) VALUES (2, "mary");
> INSERT INTO person (personID, name) VALUES (3, "bob");
>
> INSERT INTO person_phone (1, 1);
> INSERT INTO person_phone (1, 2);
> INSERT INTO person_phone (2, 2);
>
> // How does one find persons with phone# 111-1111 and 222-2222 ?
> // I have started the queries but it is not correct.
> // It only finds persons whose phone# is 111-1111.
> // Please help correct it or suggest new schema to support query.
> SELECT person.*
> FROM phone INNER JOIN (person INNER JOIN person_phone ON
> person.personID = person_phone.personID) ON phone.phoneID =
> person_phone.phoneID
> WHERE (((phone.number)="111-1111"));
>

Hi,

I'm not really interested in the discussion, but here is a query that should do what you need (I haven't tested it BTW).

   SELECT person.name
   FROM person,

          phone,
          person_phone
   WHERE        person.personID = person_phone.personID
   AND    person_phone.phoneID  =        phone.phoneID

   AND phone.number = "111-1111"
INTERSECT
   SELECT person.name
   FROM person,

          phone,
          person_phone
   WHERE        person.personID = person_phone.personID
   AND    person_phone.phoneID  =        phone.phoneID

   AND phone.number = "222-2222"

As an aside, one of the things I like about relational algebra is that it is defined in terms of a few simple operations and has a good theoretical basis that allows you to reason about it.

The xrdb query is less verbose, but that's not necessarily better.

Hope this helps in any case,

John Received on Tue Jun 07 2005 - 18:16:08 CEST