Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Relational vs. PICK/Object DBMS

Re: Relational vs. PICK/Object DBMS

From: Albert D. Kallal <kallal_at_msn.com>
Date: Wed, 21 Apr 2004 13:14:18 GMT
Message-ID: <Ksuhc.183598$Pk3.21287@pd7tw1no>


First, you want to understand that a Multi-Valued system (pick for example) is NOT to confused with a object dbms.

I like the link you gave us. The tables defined in that link are:

employee' (man#, name, birlhdate)
jobhistory' (man#, jobdate, title)
salaryhistory' (man#, jobdate, salarydate, salary) children' (man#, childname, birthyear)

Lets use the above. This makes the whole discussion easier when we have a set of defined tables already!

Given the above:
>
> 1) Suppose you have a bunch of attributes that describes the entities in
> your database.
> Suppose any attribute is optional for any entity.

I will state right off the bat the above table design as given is already defined in terms of a relational model. This as a general rule means we have to choose some query language that expresses a solution. For most relational systems this means SQL, and for Multi-Valued systems..this means the query languages known by various names (English, Access, Recall). However, in my many exposure to different vendors (jbase, Prime, Ultimate, Pick, IBM/Universe), the "Recall" query language were all were compatible with each other. There is not a ansi standard for MV query, but the industry did get together in the mid 1980's and did work towards making the query system compatible. Since all vendors wanted to steal each other customers..I in fact found compatibility higher among those systems then I did with sql between platforms like Oracle, MySql, ms-sql etc. (by making the query system compatible..you can get customers to switch...and switch we did...many times!).

>
> a) For a given set of attributes
> - find all the entities that for any attribute in the given set have at
> least one value.
> - also find the values of the given attributes

There is no built means to take a "list" of attributes..and get an answer. Take any 3 from the above table:

employee.Name, salaryhistory.salarydate, children.birthyear

We can't go:

Find All with values (Name,SalaryDate, BirthYear)

In a Multi-Value system, for the above given schema, we certainly get a easy query....we can go:

list Man#, Name SalaryDate BirthYear with Name or with SalaryDate or with BirthYear.

The above is a rather nice and simple query (and the above syntax would work on any MV system I used). The reason why the above is so simple is that we don't have "keys" nor have to "join" the data in the query to use the data.

In sql, the above is fair bit of work, as we would have to use a join (the 3 tables in my example), or use sub queries (assuming the implementation supports this).

select man#, Name,

    (select count(*) from salaryhistory where SalaryDate is not null and salaryHistory.man# = employee.man#)) as SalaryDateCount,

    (select count(*) from children where BirthYear is not null and and children.man# = employee.man#) as BirthYearCount,

from employee

    where Name is not null or SalaryDateCount > 0 or BrithYearCount > 0 ;
I suppose one could come with a inner join solution...but it would still be quite a hand full.

employee' (man#, name, birlhdate)
jobhistory' (man#, jobdate, title)
salaryhistory' (man#, jobdate, salarydate, salary) children' (man#, childname, birthyear)

> b) For a given entity
> - find all the attributes that have at least one value for the given
entity
> - also find the values of the attributes for the given entity

Hum, the above is not quite clear. When you say "find"...do you actually mean just show the child records? In a mv system since we have no joins..then you just list the fields in your query.

list man#, name, birlhdate, jobdate, title, salary.

Also, in looking at the tables defined in your link there is a collision in the field names for jobhistory.jobdate, and salaryhistory.jobdate (this would NOT be allowed in a MV system...at least as the tables as is are defined. Feel fee to elaborate on what the above question actually means).

> - find all the attributes that have at least one value for the given
entity

I read the above as show me any parent record that has a least one child record. That being the case, then again with sql, the syntax is going to vary based on what child table you want. With a MV, we could go:

list Man#, Name SalaryDate BirthYear with each SalaryDate <> "" or with each BirthYear <> ""

In sql, we could go..gee..just thinking about this...hum...? If the
attributes in question is from only ONE child table..then the syntax is
easy. If we force a inner join on two child tables..then again finding the
answer is a pain (since we would want a result if a child record existed in table children, OR IN salaryhistory). We are once again back to sub-queries.

The problem is that dealing with several child tables at once don't work very well. (and, when making a question that requites attributes from more then one child table...again the sql is not so clean).

--
Albert D. Kallal
Edmonton, Alberta Canada
pleasenonoSpamKallal_at_msn.com
http://www.attcanada.net/~kallal.msn
Received on Wed Apr 21 2004 - 08:14:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US