Copying a PKFK Representation to A-L

The Aggregate-Link representation can be used to represent any relationship represented by either the Primary Key / Foreign Key (PKFK) representation or the Junction Table (JT) representation.

In this page, we show by example how PKFK relationships can be copied into an A-L representation. (See Copying a JT Representation to A-L for the corresponding example where the relationship to copy uses the JT representation.)

Copying the PKFK Example of the Department-Employee Relationships to A-L

This example uses the PKFK representation for a Department-Employee relationship and copies it into an equivalent A-L representation. We are using MS SQL Server to perform these steps:

  • Create a database (pkfkDB) and define object relations DEPT and EMP
  • Populate DEPT and EMP object relations
  • Retrieve all DEPT-EMP pairs using the PKFK representation.
  • Define structure relations AGG and LNK for the A-L representation.
  • Use SQL to copy the PKFK representation to the A-L representation.
  • Retrieve all DEPT-EMP pairs using the A-L representation.
  • Account for orphans in the PKFK representation.

Create a database (pkfkDB) and define object relations DEPT and EMP.

/* mssql.pkfkDB.createdatabase.1.sql */
/* PKFK example - creates a database named pkfkDB. */
create database pkfkDB;
go
/* mssql.pkfkDB.createobjecttables.1.sql */
/*   Creates object tables for a DEPT-EMP relationship. */
use pkfkDB;
go
create table DEPT (
   DEPT_pk              char(6)   primary key,
   DEPT_departmentname  char(20)  not null
);
go
create table EMP (
   EMP_pk               char(6)   primary key,
   EMP_employeename     char(20)  not null,
   EMP_DEPT_fk          char(6)
);
go

Populate DEPT and EMP object relations.

/* mssql.pkfkDB.populateobjectrelations.1.sql */
/*   Inserts DEPT and EMP object records. */
use pkfkDB;
go
insert DEPT(DEPT_pk,DEPT_departmentname) values ('DEPT1','Engineering');
go
insert DEPT(DEPT_pk,DEPT_departmentname) values ('DEPT2','Marketing');
go
insert DEPT(DEPT_pk,DEPT_departmentname) values ('DEPT3','Sales');
go
insert DEPT(DEPT_pk,DEPT_departmentname) values ('DEPT4','Personnel');
go
insert DEPT(DEPT_pk,DEPT_departmentname) values ('DEPT5','Projects');
go
insert EMP(EMP_pk,EMP_employeename,EMP_DEPT_fk) values ('EMP10','John Smith','DEPT1');
go
insert EMP(EMP_pk,EMP_employeename,EMP_DEPT_fk) values ('EMP11','Jane Smith','DEPT2');
go
insert EMP(EMP_pk,EMP_employeename,EMP_DEPT_fk) values ('EMP12','Joe Smith','DEPT3');
go
insert EMP(EMP_pk,EMP_employeename,EMP_DEPT_fk) values ('EMP13','John Brown','DEPT2');
go
insert EMP(EMP_pk,EMP_employeename,EMP_DEPT_fk) values ('EMP14','Jane Brown','DEPT1');
go
insert EMP(EMP_pk,EMP_employeename,EMP_DEPT_fk) values ('EMP15','Joe Brown','DEPT2');
go
insert EMP(EMP_pk,EMP_employeename,EMP_DEPT_fk) values ('EMP16','Jim White',NULL);
go
insert EMP(EMP_pk,EMP_employeename,EMP_DEPT_fk) values ('EMP17','Jack Mills',NULL);
go

Here's what the Department-Employee example looks like graphically:

Figure 1. The Department-Employee PKFK Example.

Retrieve all DEPT-EMP pairs using the PKFK representation.

/* mssql.pkfkDB.selectusingpkfk.1.sql */
/*   Selects DEPT-EMP pairs using PKFK. */
use pkfkDB;
go
select d.DEPT_pk,d.DEPT_departmentname,e.EMP_pk,e.EMP_employeename,e.EMP_DEPT_fk
from DEPT as d left outer join EMP as e
on d.DEPT_pk = e.EMP_DEPT_fk;
go

The left outer join picks up childless parents (Personnel,Projects). A full outer join would also pick up orphans (not shown, see below).

The results of the preceding query:

Table 1. Retrieve DEPT-EMP pairs using PKFK representation.
DEPT_pk DEPT_departmentname EMP_pk EMP_employeename EMP_DEPT_fk
DEPT1EngineeringEMP10John SmithDEPT1
DEPT1EngineeringEMP14Jane BrownDEPT1
DEPT2MarketingEMP11Jane SmithDEPT2
DEPT2MarketingEMP13John BrownDEPT2
DEPT2MarketingEMP15Joe BrownDEPT2
DEPT3SalesEMP12Joe SmithDEPT3
DEPT4PersonnelNULLNULLNULL
DEPT5ProjectsNULLNULLNULL

Define structure relations AGG and LNK for the A-L representation.

In order to create an Aggregate-Link representation, we need to create the Aggregate and Link structure relations. Notice that the integrity constraints on these relations defines a (0,1)-to-(0,M) relationship to match the PKFK representation.

/* mssql.pkfkDB.addstructuretables.1.sql */
/*   Adds aggregate and link tables. */
use pkfkDB;
go
create table AGG (
   AGG_pk       integer   identity(1,1) primary key,
   AGG_DEPT_fk  char(6)   unique
);
go
create table LNK (
   LNK_pk       integer   identity(1,1) primary key,
   LNK_AGG_fk   integer   not null,
   LNK_EMP_fk   char(6)   unique not null
);
go

Use SQL to copy the PKFK representation to the A-L representation.

/* mssql.pkfkDB.copypkfktoal.1.sql */
/*   Copies PKFK DEPT-EMP relationship to A-L. */
use pkfkDB;
go
insert into AGG(AGG_DEPT_fk) 
   select DEPT_pk from DEPT;
go
insert into LNK(LNK_AGG_fk,LNK_EMP_fk)
   select a.AGG_pk,e.EMP_pk
      from AGG as a
         join EMP as e on a.AGG_DEPT_fk = e.EMP_DEPT_fk;
go

In the first step, this construction creates one aggregate record for each DEPT record. In the second step, it creates one link record for each EMP record with an EMP_DEPT_fk equal to some AGG record's AGG_pk. Childless parents (Personnel, Projects) are handled correctly but orphans (Jim White, Jack Mills) are ignored. (See below for SQL queries that account for the orphans correctly.)

Retrieve all DEPT-EMP pairs using the A-L representation.

/* mssql.pkfkDB.selectusingal.1.sql */
/*   Selects Department-Employee pairs using PKFK. */
use pkfkDB;
go
select d.DEPT_pk,d.DEPT_departmentname,e.EMP_pk,e.EMP_employeename,e.EMP_DEPT_fk
from DEPT       as d 
  right outer join AGG            as a on d.DEPT_pk = a.AGG_DEPT_fk
  left outer join LNK as l on a.AGG_pk = l.LNK_AGG_fk
  left outer join EMP       as e on l.LNK_EMP_fk = e.EMP_pk
order by d.DEPT_pk, e.EMP_pk;
go

Compare the results (below) of the preceding A-L query to the PKFK results (above):

Table 2. Retrieve DEPT-EMP pairs using A-L representation.
DEPT_pk DEPT_departmentname EMP_pk EMP_employeename EMP_DEPT_fk
DEPT1EngineeringEMP10John SmithDEPT1
DEPT1EngineeringEMP14Jane BrownDEPT1
DEPT2MarketingEMP11Jane SmithDEPT2
DEPT2MarketingEMP13John BrownDEPT2
DEPT2MarketingEMP15Joe BrownDEPT2
DEPT3SalesEMP12Joe SmithDEPT3
DEPT4PersonnelNULLNULLNULL
DEPT5ProjectsNULLNULLNULL

Accounting for orphans.

As indicated above, the SQL statements "to copy the PKFK representation to the A-L representation" would omit orphans. To include orphans, we need one more aggregate instance (i.e., AGG tuple) with a NULL valued AGG_DEPT_fk and an insert/select that inserts LNK tuples for EMP objects with NULL valued EMP_DEPT_fk with LNK_AGG_fk set to this new AGG tuple's primary key:

/* mssql.pkfkDB.accountfororphans.1.sql */
/*   Copies PKFK Department-Employee relationship to A-L. */
use pkfkDB;
insert into AGG(AGG_DEPT_fk) values (NULL);
go
insert into LNK(LNK_AGG_fk,LNK_EMP_fk)
   select a.AGG_pk,e.EMP_pk
      from AGG as a
         cross join EMP as e 
            where a.AGG_DEPT_fk is NULL and e.EMP_DEPT_fk is NULL;
go

The following table displays the results of the A-L select query (above) after accounting for orphans:

Table 3. Retrieve DEPT-EMP pairs (including orphans) using A-L representation.
DEPT_pk DEPT_departmentname EMP_pk EMP_employeename EMP_DEPT_fk
NULLNULLEMP16Jim WhiteDEPT1
NULLNULLEMP17Jack MillsDEPT1
DEPT1EngineeringEMP10John SmithDEPT1
DEPT1EngineeringEMP14Jane BrownDEPT1
DEPT2MarketingEMP11Jane SmithDEPT2
DEPT2MarketingEMP13John BrownDEPT2
DEPT2MarketingEMP15Joe BrownDEPT2
DEPT3SalesEMP12Joe SmithDEPT3
DEPT4PersonnelNULLNULLNULL
DEPT5ProjectsNULLNULLNULL

A PKFK representation of relationship can be copied to an A-L representation with or without childless parent tuples and with or without parentless child tuples (orphans). (See Copying a JT Representation to A-L for the corresponding example where the copied relationship uses the JT representation.)

Page Content first published: November 13, 2007
Page Content last updated: November 13, 2007