need help with subtype supertype

[monksorlo]

I'm a recent college grad and I'm building a database for my family business, which is a writing instrument manufacturer. I learnt about the concepts of subtype and supertype, but my professor told us that he almost never use subtype and supertype, instead he would do something else just for performance reasons. Can any expert here please tell me how should I go about subtype and supertype. I'm totally lost as to how to actually implement it. Thanks in advance

[tesuji]

Hi monksorlo

what do you mean by subtype/supertype. Does this deal with the concept of Generalization/Specialization of extended entity relationship model? you may state an example.

If your professor suggest doing something else just for performance reasons at that very early design stage, you may change professors.

krs,
tesu

[monksorlo]

That professor is actually really good. Performance is kind of like his specialty, that's why he talks about it a lot.
What I mean by supertype and subtype is, for example, we have parts, there are two different kinds of parts. One is manufactured parts, the other one is purchased part. the purchased part table has a relation with the supplier table, while the manufactured part is a relaltion with equipment and such.

I have two questions
1. how do I write it out in sql
2. is there any way to achieve the same result while boosting performance

Thanks very much for helping!!

[tesuji]

Hi monksorlo,

sorry, it has never been my intention to discredit your prof. My given statement sounds kind of sloppy.

Your subtype/supertype is exactly that what I meant with the extension of entity relationship model Generalization/Specialization. Supertype is equivalent to generalization and subtype to specialization.

As for your first question, there are several ways to map supertype/subtype of EERM into SQL of relational model. I will give you the common solution (what is indeed not that high-performance).

Supertype contains all attributes in common of all specializations. So what is in common of both manufactured and purchased parts? Obviously part number, name, category, Dimensions like weight etc.

Specialized attributes of manufactured parts would be the material the part is made from, equipment (what s that?), etc. Purchased parts is specizfied by supplier, price, delivery time etc. I assume there is only one supplier for a specific part, that is an one-to-many relationship. (If there are various supplier for same part and a specific supplier supplies various parts you would have a many-to-many relationship. If so, you must create a linking table what links purchased parts and supplier). Now we can create tables and their relationships:

-- supertype parts
create table parts(partnumber integer not null, name varchar(100), category char(10), weight decimal(10,2), primary key (partnumber));

-- subtypes, there can only be one-to-one relationship between supertype and subtypes (except you allow that a specific part can be both manufatured and purchased, sometimes)

-- -- subtype manufactured parts
create table manparts(partnumber integer not null, materialnumber integer, equipmentnumber integer, primary key (partnumber), foreign key (partnumber) references parts, foreign key (equipmentnumber) references equipment);

-- -- subtype purchased parts
create table purchparts (partnumber integer not null, suppliernumber integer, price decimal (10,2), deliverytime integer, primary key (partnumber), foreign key (partnumber) references parts, foreign key (suppliernumber) references supplier );

Now we want to query our tables, e.g.
-- list of all parts and their supplier names. This can be done by two inner joins:

select parts.partnumber, parts.name, purchparts.price, supplier.name
from parts join purchparts join suppliers;

(This does function only if you have supplied all primary and foreign keys, for example suppliernumber must be primary key of table suppliers and foreign key of purchparts)

Now your second question: well, here you see the (not-that-important) problem with the performance: Because whole information is spread over three tables two joins are necessary (what is absolutely common, particularly if you have done correct normalization up to 3NF)

How can we reduce number of joins what would really improve performance?

By definition, between supertype and subtpyes can only be an one-to-one relationship. Therefore all common attributes of supertype can be moved into the associated suptypes, for example

-- merge parts with purchparts:

create table purchasedparts(partnumber integer not null, name varchar(100), category char(10), weight decimal(10,2), suppliernumber integer, price decimal (10,2),
deliverytime integer, primary key (partnumber), foreign key (suppliernumber) references supplier);

Now our query again:

-- list of all parts and their supplier names. This can be done by ONE inner join:
select partnumber, purchasedparts.name, price, supplier.name
from purchasedparts join suppliers;

Here we need only one join what gains better performance.

I hop this "much-too-long thesis" helps you.

krs,
tesu

[monksorlo]

Thanks very very much for taking the time to respond to my questions. I really really appreciate it. It is wonderfully helpful

[bilalhaider]

Dear i got really confused after viewing the answer. I have a confusion that what is the need (partnumber integer not null) in each table in case of implementing super type and subtype.
Could we use simply "type" attribute in the super type parts table as all the parts have common features like part number and name only difference is one is manufactured and the other is purchased. Why so much replication?
Am i right?