I have four entities: location, department, municipality and sector.
municipality is a subtype of department, sector is a subtype of
municipality, and department, municipality and sector are subtypes of
location. Here are their relation schemas:

location(id)
department(id, name)
municipality(id, dept_id, name)
sector(id, municip_id, name)

department.id, municipality.id and sector.id reference location.id.
dept_id references department.id and municip_id references
municipality.id. The *.id attributes are all primary keys.

Given a value of location.id, how would I determine what subtype it
belongs to? The only way I see of doing this is by querying each
instance of department, municipality and sector individually. I was
thinking of adding an extra attribuite to location called div_type
whose value would tell me whether location.id points to a department,
municipality or sector but this is not very elegent since one could
have a location.id value that references a sector but the div_type says
it is a department.

--
Bernd

eKo1 wrote:
I guess you could use aliases as in (untested):

SELECT
d.id AS department_location_id,
m.id AS municipality_location_id,
s.id AS sector_location_id
FROM
department d,
municipality m,
sector s
WHERE
d.id = x
OR m.id = x
OR s.id = x

eKo1 wrote:
to solve these types of problems, I find it best to leave the physical
model behind (IDs) and try to define the problem in a descriptive form.
IOW, could you describe your problem at a data model (logical) level?
(Sometimes just rephrasing the question reveals the answer.) If
location has no data other than ID, then how would a user ever select
it? (Implied in that question is a hint at the guideline, never reveal
pseudoKey values to the end user.)

Ed

strawberry wrote:
I would still need to look at each of d.id, m.id and s.id and check
which one is not null. This is not very elegant.

Ed Prochak wrote:
Rephrasing: I have an entity called location which can be one of either
a department, municipality or sector. Given an instance of a location
entity, how can I determine whether it is a department, municipality or
sector?

Free wrote:
The problem with that approach is that I have to be very careful with
the data. For example, one could easily add a tuple to the department
relation that references a location whose location_type_cd value
represents a municipality.

I could make (location_id, location_type_cd) the primary key of the
location relation and refernce these attributes in the other relations
but then the value of location_type_cd will be the same in the subtype
relations.

Free wrote:
Doesn't my example constitute a good business example? I just don't
see how I can have super/sub-types without running into the
aformetioned problems.

eKo1 wrote:
Another problem: How can I enforce referential integrity between the
supertype and the subtype? I could easily have a tuple in the location
relation that isn't referenced by any of the subtype relations.

The relational model is really limited to these types of situations.

eKo1 wrote:
I think you should keep location_id as the primary key (otherwise you
could end up with several rows containing the same loction_id). In
addition to the primary key, add a unique constraint for
(location_id, location_type_cd). Your subtables can reference the
unique constraint instead of the primary key. Example

create table super (
super_id ... not null primary key
type_id ... not null
....
);

alter table super add constraint ak1_super unique (super_id, type_id);
alter table super add constraint c1_super check (type_id in (...));

create table sub (
super_id ... not null primary key
type_id ... not null
....
)

alter table sub add constraint fk1_sub foreign key (super_id, type_id)
references super (super_id, type_id);

alter table sub add constraint c1_sub check (type_id = ...);

HTH
/Lennart