Discovering Relationships in Context 
by Joe Celko


Example 1: 

CREATE TABLE Actors -- nodes
(actor_id INTEGER NOT NULL PRIMARY KEY
 actor_name CHAR(25) NOT NULL);

CREATE TABLE MovieCasts -- edges
(relationship_name  VARCHAR(50) NOT NULL, 
 begin_actor_id INTEGER NOT NULL
         REFERENCES Actors (actor_id)
         ON UPDATE CASCADE
         ON DELETE CASCADE, 
 end_actor_id INTEGER NOT NULL 
          REFERENCES Actors (actor_id)
         ON UPDATE CASCADE
         ON DELETE CASCADE, 
 PRIMARY KEY (relationship_name, begin_actor_id, end_actor_id), 
 CHECK (begin_actor_id <> end_actor_id)); 



Example 2: 

SELECT M1.end_actor_id, 
        M1.relationship_name  
       M1.end_actor_id, 
        M2.relationship_name,  
       M2.end_actor_name 
  FROM MovieCasts AS M1, MovieCasts AS M2
 WHERE M1.begin_actor_name = 'Kevin Bacon'
   AND M1.end_actor_id = M2.begin_actor_id 
   AND M2.end_actor_id = 'Joe Celko'; 

Example 3:

   AND M1.end_actor_id = M2.begin_actor_id;  
   AND M2.end_actor_id = M3.begin_actor_id
   ... 
   AND Mn.end_actor_id = 'Joe Celko' 
  AND Mn.begin_actor_id 
          NOT IN ('Kevin Bacon', M2.end_actor_id, M3.end_actor_id, ..);

1


