Aggregate

This is the part-of relationship. Consider again the relationship among car, body, and engine.

aggregate.png

This looks like the subtype relationship but it is interpreted differently. Attributes are not “inherited” by the subparts like they are by the subtypes. Suppose we had the following information to store:

car
id, name
body
id, name
engine
id, name

Without the subtype relationship we would create the following relations:

car(__id__, name)
body(__id__, name)
engine(__id__, name)

However, this does not reflect that a certain body and a certain engine are subparts of a certain car. To add these facts, we need to add the ids of the subparts to the car relation:

car(__id__, name, __body_id__, __engine_id__)
body(__id__, name)
engine(__id__, name)

The SQL create statements for the above relations are as follows:

create table car (
  id type primary key,
  name type,
  body_id type,
  engine_id type,
  foreign key (body_id) references body,
  foreign key (engine_id) references engine);
create table body
  (id type primary key,
   name type);
create table engine
  (id type primary key,
   name type);

Back to ER to SQL page.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License