Very Computer

Quote:> I had heard that Date had the object = domain idea, but I'd never
> seen any papers talking about it before. However, I'm still really
> none the wiser as to how this would work in practice.

> To me, a domain is something like:

> CREATE DOMAIN phoneNumber AS CHAR(10)

> so that phoneNumbers are always the same underlying datatype in > any

table they are defined in.

Quote:

> I can't see how to make the mental leap from this usage of domains > to

using them to store objects.

Think of the type of a column as an object type (or class.)  Think of the
values of that column in each row as instances of objects of that type.

Quote:> 1) If one column in a table contains the object, what do the other
> columns contain? Would you just have a collection of tables all with
> one column, one table per class?

One column can contain instances of objects of type Name, another can
contain instances of objects of type Map, another can contain instances of
doubles, etc.  Each row in the taple would be a set of objects (or
primitives), one per column.

Quote:> 2) How would joins work? How would you index an attribute within
> an object domain?

Each object type (or class) defines equality and inequality operators, so
there is no problem with the relational calculus, including joins, so there
is no problem with asking

select A.name from A, B where A.map = B.map

Quote:> 3) What would the definition syntax look like?

Like defining a class in an OO language, and providing overloads for
equality and inequality operators.  I haven't actually seen an
implementation.

Quote:> 4) In the interview, Date seems to skirt around the issue of
> inheritance, which to me should be fundamental. I can't believe he
> rehashed the old "are Circles Ellipses" debate! (mind you it was 5
> years ago).

There shouldn't be any great difficulty in defining an inheritance hierarcy
for a column type.

Quote:> Date says "Anybody who tries to argue that relation = object class is
> fundamentally confused" - I guess I'm confused.

Or, more loosely, as to whether the arrangement of columns in a table
represents an object class and rows represent instances of objects of that
class - no, because that would break the relational calculus.  That's Date's
point - rows cannot be regarded as objects without breaking the relational
calculus, but values of individual columns can.

--
Regards,

I'll try to give an answer from Date's point of view.  I highly recommend
you read his book-length version of "The Third Manifesto" for more details.

Quote:>To me, a domain is something like:

>CREATE DOMAIN phoneNumber AS CHAR(10)

This is a particularly simple example of what a domain could be.

Quote:>I can't see how to make the mental leap from this usage of domains to
>using them to store objects. Date gives an example of storing arrays
>in a single row / column, but to me this is completely different than
>storing an object.

Well, in many OO languages, an array _is_ an object.  More specialized
objects are probably more interesting though.  Date's suggestions for object
types that could be used as domains include things like "architectural
drawing", "video clip", and "formatted text document".  Basically, from a
database point of view, an object is any piece of data that's "atomic".  You
operate on objects by using "functions" or "operators" or "member
functions", depending on what OO methodology you're using.  But you don't
query them about their internals.  In that sense, integers are objects too.
You operate on integers using functions like addition and multiplication;
you don't query them about their 2's-complement bit representation.  But the
integer object class is built in; Date wants users to be able to define
their own new object classes.

Quote:>1) If one column in a table contains the object, what do the other
>columns contain? Would you just have a collection of tables all with
>one column, one table per class?

Date addresses this issue in one of the appendices of the book.  Often, what
you think of as an "object" is really best represented as a database tuple.
For instance, an employee can be described as the combination of an employee
ID, a phone number, etc.  You could package all of these attributes into an
object and then write functions like "getID()" and "setPhoneNumber".  But
that's terribly inefficient.  Why not use the relational model here, since
this is exactly what the model is good at?  Store the employee ID in one
column, the phone number in another, etc.  Now you can use relational
queries to extract information about employees in all kinds of ways, without
writing any new functions.

Quote:>2) How would joins work? How would you index an attribute within an
>object domain?

You don't.  Objects don't have attributes.  Well, they do have attributes
internally, at the implementation level.  But they are really defined by
their _behavior_, not by their _structure_.  At the database level, you're
considered with what an object can do or how you can operate on it, not with
what its components are.  This is the essence of object-oriented
programming.  Again, if you want an entity's components to be visible at the
database level, don't encapsulate the entity into an object -- instead,
store it as a tuple.

Quote:>3) What would the definition syntax look like?

>CREATE DOMAIN EMPLOYEE_OBJECT AS empId int, phoneNumber CHAR(10) etc.?

Date never talks about the language you'd use to implement an object's
structure and behavior.  Instead, he concentrates on how you would _specify_
its behavior.  That is, in his hypothetical language "D", you would state
the names of your object types, and you would state the list of functions
that can be used to operate on those kinds of objects.  To write the
implementations of those functions, you'd use a different language, since
you're working at a different level of abstraction.

Quote:>4) In the interview, Date seems to skirt around the issue of
>inheritance, which to me should be fundamental. I can't believe he
>rehashed the old "are Circles Ellipses" debate! (mind you it was 5
>years ago).

Well, here's where I think Date goes a little bit astray.  He admits that he
doesn't understand inheritance very well, and also claims that nobody else
understands it well either (which may actually be true).  In the book, he
proposes a type system that includes inheritance, but I don't think his type
system is very good.  He's open to other suggestions for type systems and
inheritance systems, as long as they are rigorously defined and
mathematically clean, as the relational model is.

                                 ^^^^^^
It's rather the class = domain, in contrast to class = table.

[ snipped ]

Quote:>> 1) If one column in a table contains the object, what do the other
>> columns contain? Would you just have a collection of tables all with
>> one column, one table per class?

>One column can contain instances of objects of type Name, another can
>contain instances of objects of type Map, another can contain instances of
>doubles, etc.  Each row in the taple would be a set of objects (or
>primitives), one per column.

After all, tables are relations, so the columns hold related data.
Putting a Person object together with a Map object into a tuple
makes them related regarding the relation in which the tuple appears.

Quote:>> 2) How would joins work? How would you index an attribute within
>> an object domain?

>Each object type (or class) defines equality and inequality operators, so
>there is no problem with the relational calculus, including joins, so there
>is no problem with asking

>select A.name from A, B where A.map = B.map

There is, however, a problem in asking

 select A.name from A where A.person = A.map.author

(where person and map are columns of the respective object types)
because the database system is unable to support this kind of
query once you decided to make author part of Map. Either you have
encapsulation, so the DBS cannot put an index on author, or you
break it and make author a column in another table relating maps
(without authors) and persons that are the maps' authors, thus
revealing the (formerly) internal structure of maps. Or you allow
indexes on function return values, which is possible but very hard
(Informix does it, AFAIK).

[ snipped ]

Regards,
  Jrgen Schlegelmilch
--
/----------------------------------------------------------------------\
 Juergen Schlegelmilch   http://www.informatik.uni-rostock.de/~schlegel

 University of Rostock, Computer Science Dept.,  18051 Rostock, Germany

On Wed, 11 Aug 1999 20:14:25 -0500, "Daniel Parker"

<snip>

Quote:>> 2) How would joins work? How would you index an attribute within
>> an object domain?

>Each object type (or class) defines equality and inequality operators, so
>there is no problem with the relational calculus, including joins, so there
>is no problem with asking

>select A.name from A, B where A.map = B.map

I'll stick with my favourite example of employees in departments. How
would you relate ("join") an employee to the department the employee
works in?

If I understand correctly, you can't, because the database views
employees and departments as indivisible, atomic objects - you can
join the entire object, but not parts of the object to parts of other
objects.

I guess what I'm saying is, in the relational model, relationships are
made using foreign and primary keys, which implies the entities in the
database are divisible, and hence cannot be put into a single column.

I don't think you can get round this by saying you have to invoke
getDepartment() on the employee, because how would the (newly
re-instantiated) employee get its department? By querying the database
with a key (so we're back where we started). Otherwise, you've got to
navigate relationships based on pointers, and that's clearly not
relational.

--
Karl

[ ... ]

Quote:>>> 2) How would joins work? How would you index an attribute within
>>> an object domain?
>> Each object type (or class) defines equality and inequality
>> operators, so there is no problem with the relational calculus,
>> including joins, so there is no problem with asking

>> select A.name from A, B where A.map = B.map

karl> I'll stick with my favourite example of employees in
karl> departments. How would you relate ("join") an employee to the
karl> department the employee works in?

karl> If I understand correctly, you can't, because the database views
karl> employees and departments as indivisible, atomic objects [ ... ]

Why? That would be a particularly horrid schema design. Date's point is
precisely that tables are not classes, and that if an employee relation
becomes a collection of employee objects than any sensible normalisation
rule is broken.

All Date is doing is repeating the fairly ancient requirement that a
DBMS to be called relational must have an extensible type system,
i.e. the DBMS must allow the DBA to define new types (domains) other
than numeric, text, currency, time, memo, ... (and other builtin ones),
and new associated operators.

There is no suggestion that a relation like 'employee' should be
transformed into one such atomic type; indeed it would be abhorrent,
because a relation e.g. 'employee(name*,dept*,salary)' conveys semantic
information that a type such as 'struct employee { string name, string
dept, unsigned salary; }' does not.

Perhaps it would be of benefit to all the confused people out there to
revise what the concept of relation is, and how different it is from
that of record type. In particular, a relation is not an array of
records and neither it is a spreadsheet-like table with rows, even if
lots of people like to think of them as such.

<snip>

Quote:>All Date is doing is repeating the fairly ancient requirement that a
>DBMS to be called relational must have an extensible type system,
>i.e. the DBMS must allow the DBA to define new types (domains) other
>than numeric, text, currency, time, memo, ... (and other builtin ones),
>and new associated operators.

He was trying to do a bit more than that. To quote Date from the
article: "The question is how to integrate the good ideas of
object-oriented database with relational ideas." (*)

Quote:

>There is no suggestion that a relation like 'employee' should be
>transformed into one such atomic type; indeed it would be abhorrent,
>because a relation e.g. 'employee(name*,dept*,salary)' conveys semantic
>information that a type such as 'struct employee { string name, string
>dept, unsigned salary; }' does not.

Did you read the article to which I was referring? Date says:

"Now, you have two ways to represent employees. You can have them
represented by rows and tables, as we typically do in a relational
system. Or you can have a domain of employees. How do you choose?
That's not my problem."

I find the last sentence an amazing cop out. Clearly, if you store
employees by rows and tables, there's nothing OO about that!

Date answers his own question (*): "The relational model is so solid
and so robust; to quote the manifesto, 'The relational model needs no
extension, no correction, no subsumption, and, above all, no
perversion' in order for it to accommodate the good ideas of OO.
Another way to say the same thing is that the good ideas of
object-oriented are completely orthogonal to the relational model."

Well I don't think this has been successfully demonstrated at all,
especially when you also consider the way inheritance is (not!) dealt
with.

--

Karl

: Inheritance is a concept pertaining to code/type systems, and if one
: embeds an extensible OO type system in a relational database one can,
: why not, have inheritance among domain types. This is irrelevant to the
: relaitonal model, which requires only that domain types be an extensible
: type system; how the extensibility is achieved is up to the designer of
: a particular DBMS.

  I agree with most all else that you say, Piercarlo. And only emphasise
 that the engineering has caught up to the theory. In ORDBMS
 systems (DB2 and INFORMIX's latest) you can use a procedural language
 and a component model to implement your domains.

  But I think table inheritance is a good OO idea that was *not* part of the
 R model. I'm pushed to this conclusion by the way it simplifies some
 makes some common and knotty design problems.

   relation employees( emp_id,name,dept_no,manager );
   relation full_timers( emp_id,name,dept_no,manager,salary )
   under employees;
   relation sales_reps( emp_id,name,dept_no,manager,salary,tot_sales)
   under full_timers;
   relation contractors ( emp_id,name,dept_no,manager,hours_pw,rate_ph)
   under employees;

  Simple example hierarchy. PK is pretty obvious: emp_id. FK to Dept
 on dept_no. The model makes sense where you have a set of facts
 that are 'related' in some ways, but differ in others. You want to
 treat certain aspects -- the existance of an employee, each employee's
 dept -- in the same way. But you want to treat how they are paid
 differently.

  Now, consider queries like:

   "How many employees does each manager have?"
   "How many employees in each department?"

  Without table/relational inheritance, the only alternatives you're left
 with are separate tables (UNION in queries) or lots of NULLs (which I'm
 sure Chris D likes less! ;-) ). But the two queries above become:

  SELECT E1.Emp_Id, COUNT(*)               SELECT D.Dept_No, COUNT(*)
    FROM Employees E1, Employees E2          FROM Dept D, Employees E
   WHERE E1.Manager = E2.Emp_Id             WHERE E.Dept_No = D.Dept_No
  GROUP BY E1.Emp_Id;                       GROUP BY D.Dept_No;

  Much simpler & cleaner than the alternatives.

  In their 'Third Manifesto' Date & Darwen kind of make heavy weather of this.
 They strongly argue for type inheritance, but barely give this other
 idea a 6 page glance in Appendix D.

  KR

    Pb

Why do you prefer this schema rather than the following plain old relational
schema:

  relation employees (emp_id, name, dept_no, manager);
  relation full_timers (emp_id -> employees.emp_id, salary);
  relation sales_reps (emp_id -> full_timers.emp_id, tot_sales);
  relation contractors (emp_id -> employees.emp_id, hours_pw, rate_ph);

?

(I'm using -> to indicate foreign key relationships, i.e. inter-table
integrity constraints.)

Quote:>  Now, consider queries like:

>   "How many employees does each manager have?"
>   "How many employees in each department?"

Easy in my schema, because every employee is represented in the employees
relation.

Quote:>  Without table/relational inheritance, the only alternatives you're left
> with are separate tables (UNION in queries) or lots of NULLs (which I'm
> sure Chris D likes less! ;-) ).

No UNION operations or NULLs required in my schema.

Quote:> But the two queries above become:

>  SELECT E1.Emp_Id, COUNT(*)               SELECT D.Dept_No, COUNT(*)
>    FROM Employees E1, Employees E2          FROM Dept D, Employees E
>   WHERE E1.Manager = E2.Emp_Id             WHERE E.Dept_No = D.Dept_No
>  GROUP BY E1.Emp_Id;                       GROUP BY D.Dept_No;

These queries work in my schema.

If you want to see relations like the ones in your schema, you can define
them as views using simple joins on the base relations.  A good DBMS should
let you update these views directly, no?

Am I missing something here?

karl> On 23 Aug 1999 00:02:08 +0100, pierc...@Dial.PIPEX.com (Piercarlo

piercarl> All Date is doing is repeating the fairly ancient requirement
piercarl> that a DBMS to be called relational must have an extensible
piercarl> type system, i.e. the DBMS must allow the DBA to define new
piercarl> types (domains) other than numeric, text, currency, time,
piercarl> memo, ... (and other builtin ones), and new associated
piercarl> operators.

karl> He was trying to do a bit more than that. To quote Date from the
karl> article: "The question is how to integrate the good ideas of
karl> object-oriented database with relational ideas." (*)

But that's in effect the same thing I said. The "good ideas of OO
databases", as he has argued many times, are having an extensible (OO of
course) type system.  An extensible type system (and this in practice
means an OO one) is an essential requirement of the relational
model. So, we rejoice.

Most existing relational databases have a good idea, relation based data
modelling, and a bad one, an inflexible domain type system. Most
existing OO databases have a good idea, an extensible type system for
data domains, and a bad idea, network-style data modelling. Date simply
would like for the OO type system to appear in the context of relation
based data modeling systems; this is getting the good idea of OO
databases into relational databases.

It is also making relational databases become more fully relational,
because an extensible type system is an essential requirement of the
relational model. More databases that are styled as "relational" out
there, including most popular commercial ones, should be really called
``quasi-relational'', for they do not fulfil some of the most important
requirements of the relational model, among them the ability to definite
new domain types.

piercarl> There is no suggestion that a relation like 'employee' should
piercarl> be transformed into one such atomic type; indeed it would be
piercarl> abhorrent, because a relation
piercarl> e.g. 'employee(name*,dept*,salary) conveys semantic
piercarl> information that a type such as 'struct employee { string
piercarl> name, string dept, unsigned salary; }' does not.

karl> Did you read the article to which I was referring? Date says:

  Date> Now, you have two ways to represent employees. You can have them
  Date> represented by rows and tables, as we typically do in a relational
  Date> system. Or you can have a domain of employees.

I actually read it, but I was commenting on what _you_ have written,
which does not make much sense, not what Date has written, which
does. What you had written is:

  karl> I'll stick with my favourite example of employees in
  karl> departments. How would you relate ("join") an employee to the
  karl> department the employee works in?

  karl> If I understand correctly, you can't, because the database views
  karl> employees and departments as indivisible, atomic objects [ ... ]

In this it is not Date, who instead writes "How do you choose?  That's
not my problem", but _you_ who is assuming that in some particular case
you want to do joins involving an "employee" relation having a single
field of domain type "employee".

This is abhorrent, as I have explained; a tuple in a relation has data
modeling semantics that are completely absent in a class type. If you
want to do a join you dot it between relations, not between a relation
and a domain via a field of the relation and a subfield of a domain.

On rereading your lines above, let me try to ascribe perhaps too much
importance to a ``freudian'' slip in what you write:

  karl> How would you relate ("join") an employee to the
                      ======
  karl> department the employee works in?

The slip here is to use "relate" when describing a join. Well, in the
relational model the _only_ way to relate two data items is to put them
in the same relation; there is _no other way_. In a schema (in some
imaginary, simplified, DDL, like the other examples below) like

  domain unit: string
  domain person: string
  domain amount: currency

  relation employee(empname*:person,worksin:unit,salary:amount)
  relation department(deptname*:unit,managedby:person)

The only things you can say is that 'empname', 'worksin', and 'salary' are
related, and 'deptname' and 'managedby' are related; that's it. There is no
_explicit_ relationship between 'worksin' and 'deptname' (then you could
add integrity constraints that define _implicit_ relationships, but not
a _relation_).

Now in order to relate data elements in the relational model one puts
them as fields of a relation; if they are independent fields in the same
relation, they are related, if not, they aren't.

In other words, this is the relational model's core feature, that
inter-relation access paths are not part of the schema; any application
may materialize any such path that is valid, such as the one involved in
the [equi]join between 'employee' and 'department' on their fields
'employee.dept' and 'department.deptname', which is valid as the domains
of the two fields are the same, and there is a suitable 'operator ='.

Now that one puts inside a domain type or as a relation field is a
schema design decision. For example the above schema may well be
rewritten as:

  domain unit: string
  domain amount: currency
  domain person: class name:string, worksin:unit,salary:amount end

  employee(emp*:person)
  department(deptname*:unit,managedby:person)

In some applications this might well be appropriate. However in such a
schema one, _by design_ cannot join 'employee' and 'department' on
'employee.emp' and 'department.deptname', because their domain types are
different; unless of course one extends the above by defining an
'operator =' for 'person' that takes a 'unit' parameter.

  Date> How do you choose? That's not my problem.

karl> I find the last sentence an amazing cop out.

Why? He is perfectly correct: that's not _his_ problem. Whether in a
schema "employee" is a relation, which can be joined to another
relation, or a domain, which cannot, depends strictly on context.

It is easy to imagine situations in which "employee" needs to be a
relation, and others in which it can be handled as a domain.

His problem is to point out (using rather imprecise language -- rows and
tables!) that the _third_ alternative, confusing domains with ``tables'',
is abhorrent.

It is the DBA's problem instead, depending on the circumstances, to come
up with a schema that supports appropriate data semantics; this may
involve choices.

A value may be a subfield of a domain, or a field of a relation; this is
a fairly crucial schema design decision, in part similar to the decision
of whether to relate two fields (put them in the same relation) or not
(put them in separate relations).

For example, instead of

  domain unit:
    class
      name:string
      established:date
      operator =(b:unit)    return name = b.name
    end

  department(deptname*:unit,managedby:person)

one may want to have:

  domain unit: string
  domain since: date

  department(deptname*:unit,established:since)
  managed(dept*:unit,managedby:person)

in which one has made the 'established' field visible at the data
modeling level, thus for example allowing one to check that HR records
are correct by verifying that employees have been transferred to a
department after it has been established, and the 'managedby' field is
not longer related to 'deptname' within 'department' (and thus not to
'established', but within 'managed', which for example allows modeling
that a department has no manager (or multiple managers if we make
'managedby' part of the primary key).

karl> Clearly, if you store employees by rows and tables, there's
karl> nothing OO about that!

Really? on what kind of bizarre argument is this assumption based?
Consider the following schema:

  domain person: string
  domain amount:
    class
      magnitude:integer
      operator =(b:amount)  return magnitude - b.magnitude
      operator <(b:amount)  return magnitude < b.magnitude
      operator Dollars()    return magnitude/100
      operator Cents()      return magnitude%100
    end
  domain unit:
    class
      name:string
      established:date
      operator =(b:unit)    return name = b.name
    end

  employee(empname*:person,worksin:unit,salary:amount)
  department(deptname*:unit,managedby:person)

This looks pretty OO to me. At the same it is relational.

The OO bit is that we have clearly described what an 'amount' or a
'unit' are and what kind of operation we can perform on thsoe domain
types; the relational bit is that then we state relations among
'empname', 'worksin' and 'salary' on hand hand and 'deptname' and
'managedby' on the other.

Again I'll say: if one wants to represent employees as an atomic domain,
fine; if one does, and then marvels that one cannot join a relation and
a domain or two relations using as key a part of an atomic value, then
one has understood _nothing_ about the relational model.

karl> Date answers his own question (*)

  Date> The relational model is so solid and so robust; to quote the
  Date> manifesto, 'The relational model needs no extension, no
  Date> correction, no subsumption, and, above all, no perversion' in
  Date> order for it to accommodate the good ideas of OO.  Another way to
  Date> say the same thing is that the good ideas of object-oriented are
  Date> completely orthogonal to the relational model.

karl> Well I don't think this has been successfully demonstrated at all,
karl> especially when you also consider the way inheritance is (not!)
karl> dealt with.

And it should not be dealt with. There no necessity for a concept like
inheritance in a data model, and it just happens that the relational
model is a data model that indeed does not need it.

Inheritance is a concept pertaining to code/type ...

read more »

Which relational databases allow you to update views describing joins?  I'm not
aware of any, and I don't believe it's logically possible to update such a view
in a consistent, logical manner.  How do such databases deal with scenarios such
as:

create table t1 (apple integer); create table t2 (orange integer);
insert into t1 values (2);
insert into t1 values (3);
insert into t2 values (5);
insert into t2 values (7);

create view v1 as select apple, orange from t1, t2;
update v1 set apple = orange;

What would the final values in t1 be?

Bruce

Quote:> Which relational databases allow you to update views describing
joins?  I'm not
> aware of any, and I don't believe it's logically possible to update
such a view
> in a consistent, logical manner.  How do such databases deal with
scenarios such
> as:

> create table t1 (apple integer); create table t2 (orange integer);
> insert into t1 values (2);
> insert into t1 values (3);
> insert into t2 values (5);
> insert into t2 values (7);

> create view v1 as select apple, orange from t1, t2;
> update v1 set apple = orange;

> What would the final values in t1 be?

> Bruce

Bruce:

There is a some research by Prof. Arthur Keller:

A.M. Keller (1985) Updating relational databases through views. PhD
Thesis, CS Department, Stanford Uiniversity.

I don't know if his ideas have been taken-up in any commercial systems
though. I think you can get a postscript copy of the thesis here:

http://www-db.stanford.edu/pub/keller/#Object

Might be of interest.

Regards,

akmal

ether.berkeley.edu>...

Quote:> > >  But I think table inheritance is a good OO idea that was *not*
part of
> > the
> > > R model. I'm pushed to this conclusion by the way it simplifies
some
> > > makes some common and knotty design problems.

> > >   relation employees( emp_id,name,dept_no,manager );
> > >   relation full_timers( emp_id,name,dept_no,manager,salary )
> > >   under employees;
> > >   relation sales_reps(

emp_id,name,dept_no,manager,salary,tot_sales)

Quote:> > >   under full_timers;
> > >   relation contractors (

emp_id,name,dept_no,manager,hours_pw,rate_ph)

Quote:> > >   under employees;

> > Why do you prefer this schema rather than the following plain old
relational
> > schema:

> >   relation employees (emp_id, name, dept_no, manager);
> >   relation full_timers (emp_id -> employees.emp_id, salary);
> >   relation sales_reps (emp_id -> full_timers.emp_id, tot_sales);
> >   relation contractors (emp_id -> employees.emp_id, hours_pw,
rate_ph);

> > ?

> > (I'm using -> to indicate foreign key relationships, i.e. inter-
table
> > integrity constraints.)

> > >  Now, consider queries like:

> > >   "How many employees does each manager have?"
> > >   "How many employees in each department?"

> > Easy in my schema, because every employee is represented in the
employees
> > relation.

> > >  Without table/relational inheritance, the only alternatives
you're left
> > > with are separate tables (UNION in queries) or lots of NULLs
(which I'm
> > > sure Chris D likes less! ;-) ).

> > No UNION operations or NULLs required in my schema.

> > > But the two queries above become:

> > >  SELECT E1.Emp_Id, COUNT(*)               SELECT D.Dept_No, COUNT
(*)
> > >    FROM Employees E1, Employees E2          FROM Dept D,
Employees E
> > >   WHERE E1.Manager = E2.Emp_Id             WHERE E.Dept_No =
D.Dept_No
> > >  GROUP BY E1.Emp_Id;                       GROUP BY D.Dept_No;

> > These queries work in my schema.

> > If you want to see relations like the ones in your schema, you can
define
> > them as views using simple joins on the base relations.  A good
DBMS should
> > let you update these views directly, no?

> > Am I missing something here?

--
akmal at bigfoot dot com | www.bigfoot.com/~akmal/

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[Cartesian product example snipped]

I admit I was speaking rather loosely when I said "a good DBMS should let
you update these views directly...".  I don't know what rules today's DBMSs
use for determining which views are updatable.  The example I gave seems
intuitively like it should be updatable, but I don't have a well-founded
argument to explain why.

C. J. Date has come up with some rules for allowing fairly general views to
be updatable.  No DBMS implements all of his suggestions; most implement
some of them.  I have mixed feelings about this work of Date's overall.  His
rules for handling difference views seem to me to be flat-out wrong.  His
rules for other expressions, including joins, seem like they might be ok.
At least they do reasonable things if you give them reasonable tasks to work
on.  Your Cartesian product example was "unreasonable" in this sense, but of
course a DBMS needs to follow a set of well-defined rules and either reject
the update (because it doesn't meet some well-established criterion) or do
something consistent and definable.  I don't have Date's rules handy, so I
can't be sure how they'd handle this particular "unreasonable" situation.
But I'm pretty sure they'd correctly handle the "employees" example that I
proposed, for what that's worth.

If Date's rules (or some other similar set of rules) can't be made to work,
then I'd be more inclined to appreciate Paul's version of "employees" that
uses table inheritance.  But I'd be reluctant to "pollute" the relational
model with a major new concept like table inheritance until I was pretty
confident that we'd exhausted all other attempts to handle the situation.