I think that is a bit too much. Codd did most of the work
Of course. Codd had the genial idea of applying thousands of years of
advances in maths to the data management field and it has an inmense
merit.
, and before that,
there was mathematical theory, but not applyable to databases directly.
Agreed, most theoretical basis is founded way before Codd walked the earth,
but I find it a bit too much to give ancient mathematicans the credit for the
relational model
Of course, I completely agree with you. But I was talking about the
origins of The Relational Model's strength.
( <- == is-a )
Person <- Employee <- Manager <- CEO
Person <- Employee <- Clerk
Person <- Employee <- Accountant
Now, if you set this up in NIAM or ORM (Halpin) you will use
supertypes/subtypes and which allow you to define relations directly to
'Manager' for example.
But NIAM and ORM are not relational. BTW I never have found them very
useful.
With The Relational Model you have to represent all these information
in several relations (tables). But it is not inheritance. It is an
application of Predicate Logic. Each relation (table) represents a
predicate and each tuple (row) a logical proposition.
Table inheritance does not make sense in The Relational Model. And it
is completely different to the OO class inheritance.
When you want to project this onto a relational model, you can't define is-a
relationships or supertype/subtypes.
Wen you project this onto a relational design the ORM types and
subtypes don't make sense anymore. You have to work in terms of
predicates and propositions.
You can define is-a relationships in many ways.
For instance:
create table IsA(name varchar(20), position varchar(20), primary
key(name, position));
insert into IsA values ('John', 'Manager');
This tuple means that John is a manager.
Relationships are relations and The Relational Model is all about
relations!
Another way:
create table Managers(name varchar(20) primary key);
insert into Managers values ('John');
This means: John is a manager.
You can 'try', but it is very hard.
Nijssen/Halpin mention 2 ways:
1) flatten the supertype/subtype hierarchy and store them all in 1 table,
with all attributes of all types in the hierarchy in that table, and
attributes which are defined with a lower type than the root supertype are
nullable. Furthermore, add a column which identifies the type of the entity
in a particular row.
Horrible practice that breaks The Relational Model. The result is not
a relational design.
BTW "flatten" does not make sense if you are talking about The
Relational Model. Predicates and propositions can't be flat.
2) define 1 table per subtype and add an FK constraint from the PK of the
subtype table to the PK of its supertype table (not THE supertype table!)
One table or more than one. But you can skip ORM and to do that
directly thinking in relational terms.
2) is close, however it doesn't symbolizes a hierarchy per se, because the FK
constraint just illustrates 'relationship between attributes' not an is-a
relationship.
The relation predicates might simbolize anything you want. The FK
constraint is just an integrity constraint. An is-a relationship is a
relation like any other. They are trivial to represent with The
Relational Model.
create table Employees(Id integer primary key, name varchar(20) , wage
numeric);
create table Managers(id integer primary key, department varchar(20),
foreign key (id) references Employees(id));
insert into Employees value(1, 'John', 50000);
This means: John is the employee with the Id 1 and earns 50.000. And
of course it stablishes that John is-an employee.
insert into EmpManagers values (1, 'Accounting');
This means: the employee with Id 1 is the manager of the accounting
department.
From these two propositions you can infere that John is-a manager. And
the DBMS too, if you declare the inference rules:
create view Managers as select * from Employees e, EmpManagers m where
e.Id = m.Id;
Select * from managers.
This should return:
Id Name Wage Department
-------------------------------------------
1 John 50000 Accounting
This means John is an employee, has the Id 1, earns 50.000 and he is
the manager of the accounting department.
Regards
Alfredo
