Re: MultiValue Databases

From: Neo <neo55592_at_hotmail.com>
Date: 7 Jun 2005 08:43:08 -0700
Message-ID: <1118158988.399655.126370@g47g2000cwa.googlegroups.com>

> ... 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")); Received on Tue Jun 07 2005 - 10:43:08 CDT