Re: MultiValue Databases
Date: 7 Jun 2005 13:58:03 -0700
Message-ID: <1118177883.287210.147110_at_g44g2000cwa.googlegroups.com>
>> Assume a person can have 0 to many phone numbers. Assume a phone number can be assigned to multiple persons ... normalized the phone numbers to allow adding various attributes to some phone numbers later.
> ... There really is NO need for a phone ID ...
You are correct, the initial data didn't required it.
>> xrdb: Find things with phone#s 111-1111 and 222-2222: >> (SELECT * phone# 111-1111 & * phone# 222-2222)
> RM Solution #1: SELECT person.* from person where personID in ( SELECT personID from phone INNER JOIN person_phone ON phone.phoneID = person_phone.phoneID WHERE phone.number in ( "111-1111", "222-2222" ) group by personID having count(*) =2 ) ;
> RM Solution #2: SELECT person.* from person, person_phone AS pp1, person_phone AS pp2, phone AS line1, phone AS line2 WHERE line1.number="111-1111" and line1.phoneID = pp1.phoneID AND line2.number="222-2222" and line2.phoneID = pp2.phoneID AND person.personID=pp1.personID AND person.personID=pp2.personID ;
Thanks for posting the above RM solutions.
> (Variation change the last clause to: HAVINGcount(*) >=2) and remove the WHERE clause to find all persons with 2 or more phones. Try that one Neo.)
No doubt SQL has many operations that I would need to implement via code currently, however for now I am focused on something more basic, that is a data model's ability to store and recall anything in a systematic manner. Is greater than (>) a relational operator? If so, is red > blue? Is apple > orange?
> This all is just play and proves nothing.
You are correct, I didn't expect to verify Lee's assertion that RM is the most general/superior data model with a trivial example. Now suppose customer wants to store "John likes Mary". Below I add this data in xrdb and query for the relationship between John and Mary; and also between John and 111-1111. Starting with the provided RM schema/data, how can one accomplish the same? (If new schema is needed, please suggest one)
(CREATE verb inst *like)
(CREATE john like mary)
(SELECT john * mary) // Query returns like.
(SELECT john * 111-1111) // Query returns phone#.
Also after having designed the schema and entering the initial data,
assume customer requests to store 123 as extension of both phone
numbers. Below I show the xrdb script to do so on the populated db.
(Assume a phone# can have 0 to many extentions and that an extension
may have attributes, ie hours of operation).
// Create a type named extension, similar to creating a table name
T_Ext.
(CREATE type inst *ext)
// Adds 123 an instance of ext, similar to adding a row in T_Ext.
// Add reference to 123 as an attribute of 111-1111 and 222-2222.
(CREATE 111-1111 ext +123)
(CREATE 222-2222 ext +123)
// Find persons whose phone# is 222-2222 with ext 123.
(SELECT * phone# (* name 222-2222 & * ext 123))
Please show how to accomplish the same in RM. Received on Tue Jun 07 2005 - 22:58:03 CEST