Thursday 27 September 2012

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.
           M2M.png
        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



1o21o.png
 
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:
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
 
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:

 

e17.png
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):


 1m21m.png
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);

Labels