ER DIAGRAMS TO SQL QUERIES

ONE TO MANY:

For any A there are many Bs. For any B there is no more than one A. (Equivalently, there may be one A)

           

create table B
(
 b_id type,
 b_name type,
 <b_other>
 constraint primary key(b_id)
);
create table A
(
 a_id type,
 a_name type,
 b_id type,
 <a_other>,
 constraint primary key(a_id),
 constraint foreign key(b_id) references B
);
 
ONE TO MANY WITH TOTAL PARTICIPATION:
  For any A there are possibly many Bs. For any B, there only one A.
 
create table B
  (b_id type primary key,
   b_name type,
   <b_other>);
create table A (
   a_id type primary key,
   a_name type,
   b_id type not null,
   <a_other>,
   foreign key (b_id) references B);
 
 
MANY TO MANY:
          For any A there are possibly many Bs. For any B there are possibly many As.

           

        create table A

  (a_id type,

   a_name type,

   <a_other>,

   primary key (a_id));

create table B

  (b_id type,

   b_name type,

   <b_other>,

   primary key (b_id));

create table R

  (a_id type,

    b_id type,

   <r_other>,

   primary key (a_id, b_id),

   foreign key (a_id) references A,

   foreign key (b_id) references B);

ONE TO ONE(0-1—0-1):

For any A there may be one B. For any B there may be one A

create table A
  (a_id type,
   a_name type,
   <a_other>,
   primary key (a_id));
 
create table B
  (b_id type,
   b_name type,
   <b_other>,
   primary key (b_id));
 
create table R (
   a_id type,
   b_id type not null,
   <r_other>,
   primary key (a_id),
   foreign key (a_id) references A,
   foreign key (b_id) references B,
   unique (b_id));
 
 
MANY TO ONE:
 
create table A
  (a_id type,
   a_name type,
   <a_other>,
   primary key (a_id));
 
create table B
  (b_id type,
   b_name type,
    a_id type, type
   <b_other>,
   primary key (b_id),
   foreign key (a_id) references A);
 
 
 
AGGREGATE:

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

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);

WEAK ENTITIES:

These entities exist only when another entity exists.
employee(id, name)
salhist(Id, revision_date, salary)
 
create table employee (
   id type primary key,
   name type);
create table salhist (
   id type,
   revision_date date,
   salary number,
   primary key (id, revision_date),
   foreign key (id) references employee
    on delete cascade);
 
 
ONE TO ONE (1-1):

 

For any A there must be one B. For any B there must be one A.

create table A

  (a_id type,

   a_name type,

   <a_other>,

   primary key (a_id));

create table B

  (b_id type,

   b_name type,

   fk_b_a type not null,

   primary key (b_id),

   unique (fk_b_a),

   foreign key (fk_b_a) references A);