Santa Fe DataBase Solutions is a Sole Proprietorship located in Santa Fe, New Mexico.
SFDBS is exclusively engaged in promoting the use and commercialization of the
Method of Recursive Objects (US Pat 7,548,935).

Santa Fe DataBase Solutions

PATH // www.sfdbs.com > Department Employee Example

Department Employee Example using the Aggregate-Link Schema

The Aggregate-Link (A-L) schema (Figure 1) represents relationships in a manner similar to the Junction Table (JT) representation. Whereas JT employs a single Junction Table in which each tuple links one Parent tuple to one Child tuple, the A-L represention employs two structure relations (Aggregate, Link) with a tuple in Aggregate for each Parent and a tuple in Link for each Child. This page provides a complete example including SQL statements.

**Figure 1. The Aggregate-Link Relationship Representation Schema**

Department-Employee Example using the Aggregate-Link Represention

This example demonstrates the A-L representation for a Department-Employee "works in" relationship. We are using MS SQL Server to perform these steps:

Create the database (DeptEmpDB) and define entity relations DEPT and EMP.
Populate relations DEPT and EMP with individual Departments and Employees.
Define structure relations AGG and LNK for the A-L representation.
Populate the structure relations AGG and LNK to represent the relationship.
Retrieve all DEPT-EMP pairs using the A-L representation.

Schema for the Department-Employee Example.

**Figure 2. The A-L Schema for The Department-Employee Example.**

Create the database (DeptEmpDB) and define entity relations DEPT and EMP.

/* departmentemployee.createdatabase.1.sql */
create database DeptEmpDB
go

/* departmentemployee.createobjectrelations.1.sql */
use DeptEmpDB
go
/* Create DEPT object relation. */
create table DEPT (
  DEPT_pk              char(8) primary key,
  DEPT_departmentName  char(20) not null
)
go
/* Create EMP object relation. */
create table EMP (
  EMP_pk               char(8) primary key,
  EMP_employeeName     char(20) not null,
  EMP_title            char(20) not null
)
go

Populate object relations DEPT and EMP with individual Departments and Employees.

/* departmentemployee.populateobjectrelations.1.sql */
use DeptEmpDB
go
/* insert 5  Departments */
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 8 Employees */
insert EMP(EMP_pk,EMP_employeeName,EMP_title)
   values ('EMP10','John Smith','Engineer III')
go
insert EMP(EMP_pk,EMP_employeeName,EMP_title)
   values ('EMP11','Jane Smith','Marketing III')
go
insert EMP(EMP_pk,EMP_employeeName,EMP_title)
   values ('EMP12','Joe Smith','Salesman I')
go
insert EMP(EMP_pk,EMP_employeeName,EMP_title)
   values ('EMP13','John Brown','Salesman I')
go
insert EMP(EMP_pk,EMP_employeeName,EMP_title)
   values ('EMP14','Jane Brown','Intern')
go
insert EMP(EMP_pk,EMP_employeeName,EMP_title)
   values ('EMP15','Joe Brown','Trainee')
go
insert EMP(EMP_pk,EMP_employeeName,EMP_title)
   values ('EMP16','Jim White','Temporary')
go
insert EMP(EMP_pk,EMP_employeeName,EMP_title)
   values ('EMP17','Jack Mills','Janitor')
go

Define structure relations AGG and LNK that provide the Aggregate-Link representation.

An A-L relationship representation requires two structure relations, Aggregate and Link. We define these here for the relationship between Departments and Employees.

/* departmentemployee.createaggregateviews.1.sql */
use DeptEmpDB
go
/* aggregate view DEPT-EMP represented by tables AGG and LNK */
create table AGG (
  AGG_pk                  integer primary key,
  AGG_DEPT_fk             char(8)
)
go
create table LNK (
  LNK_pk                  integer primary key,
  LNK_AGG_fk              integer not null,
  LNK_EMP_fk              char(8)
)
go

Populate the structure relations AGG and LNK to represent the relationship.

These operations insert tuples to the structure relations AGG and LNK to build the structure that represents the relationship. Each Parent tuple (including NULL) requires one AGG tuple. Each Child tuples requires one LNK tuple.

/* departmentemployee.populateaggregateviews.1.sql */
use DeptEmpDB
go
/*    create DEPT-EMP aggregate instance, parent Department = 'Engineering' */
insert AGG (AGG_pk,AGG_DEPT_fk)
   values (100,'DEPT1')
go
/*    create DEPT-EMP aggregate instance, parent Department = 'Marketing' */
insert AGG (AGG_pk,AGG_DEPT_fk)
   values (101,'DEPT2')
go
/*    create DEPT-EMP aggregate instance, parent Department = 'Sales' */
insert AGG (AGG_pk,AGG_DEPT_fk)
   values (102,'DEPT3')
go
/*    create DEPT-EMP aggregate instance, parent Department = 'Personnel' */
insert AGG (AGG_pk,AGG_DEPT_fk)
   values (103,'DEPT4')
go
/*    create DEPT-EMP aggregate instance, parent Department = '' (NULL) */
insert AGG (AGG_pk,AGG_DEPT_fk)
   values (104,'')
go
/*    add Employee 'John Smith' to 'Engineering' Department */
insert LNK (LNK_pk,LNK_AGG_fk,LNK_EMP_fk)
   values ('1000','100','EMP10')
go
/*    add Employee 'Jane Smith' to 'Marketing' Department */
insert LNK (LNK_pk,LNK_AGG_fk,LNK_EMP_fk)
   values ('1001','101','EMP11')
go
/*    add Employee 'Joe Smith' to 'Sales' Department */
insert LNK (LNK_pk,LNK_AGG_fk,LNK_EMP_fk)
   values ('1002','102','EMP12')
go
/*    add Employee 'John Brown' to 'Sales' Department */
insert LNK (LNK_pk,LNK_AGG_fk,LNK_EMP_fk)
   values ('1003','102','EMP13')
go
/*    add Employee 'Jane Brown' to 'Engineering' Department */
insert LNK (LNK_pk,LNK_AGG_fk,LNK_EMP_fk)
   values ('1004','100','EMP14')
go
/*    add Employee 'John Brown' to 'Marketing' Department */
insert LNK (LNK_pk,LNK_AGG_fk,LNK_EMP_fk)
   values ('1005','101','EMP13')
go
/*    add Employee 'Joe Brown' to 'Marketing' Department */
insert LNK (LNK_pk,LNK_AGG_fk,LNK_EMP_fk)
   values ('1006','101','EMP15')
go
/*    add Employee 'Jim White' to 'NULL' Department */
insert LNK (LNK_pk,LNK_AGG_fk,LNK_EMP_fk)
   values ('1007','104','EMP16')
go

Here's what all 4 tables look like after all inserts:

Table 1. DEPT (Department)
DEPT_pk	DEPT_departmentName
DEPT1	Engineering
DEPT2	Marketing
DEPT3	Sales
DEPT4	Personnel
DEPT5	Projects

Table 2. EMP (Employee)
EMP_pk	EMP_employeeName	EMP_title
EMP10	John Smith	Engineer III
EMP11	Jane Smith	Marketing III
EMP12	Joe Smith	Salesman I
EMP13	John Brown	Salesman I
EMP14	Jane Brown	Intern
EMP15	Joe Brown	Trainee
EMP16	Jim White	Temporary
EMP17	Jack Mills	Janitor

Table 3. AGG (Aggregate)
AGG_pk	AGG_DEPT_fk
100	DEPT1
101	DEPT2
102	DEPT3
103	DEPT4
104	NULL

Table 4. LNK (Link)
LNK_pk	LNK_AGG_fk	LNK_EMP_fk
1000	100	EMP10
1001	101	EMP11
1002	102	EMP12
1003	102	EMP13
1004	100	EMP14
1005	101	EMP13
1006	101	EMP15
1007	104	EMP16

Figure 3 graphically depicts the Department-Employee example in Tables 1-4 for the Aggregate-Link representation. Notice that Department "Projects" (primary key = "DEPT5") has no corresponding Aggregate tuple and that Employee "Jack Mills" (primary key = "EMP17") is not linked (via a LNK tuple) to any Department. (That is, they are not included in the relationship.)

**Figure 3. The Department-Employee Example.**

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

Retrieval is straightforward. Outer joins account for NULL parents and Parent tuples with no linked child tuples. Child tuples with no Parent are explicitly represented as Child tuples to a NULL parent.

/* departmentemployee.intuitiveretrieval.1.sql */
use DeptEmpDB
go
select DEPT_departmentName, EMP_employeeName, EMP_title
from DEPT
full outer join AGG on DEPT_pk = AGG_DEPT_fk
left outer join LNK on AGG_pk = LNK_AGG_fk
left outer join EMP on LNK_EMP_fk = EMP_pk
order by DEPT_departmentName, EMP_employeeName
go

Results of the previous SQL SELECT query:

Table . SQL SELECT query results
DEPT_departmentName	EMP_employeeName	EMP_title
NULL	Jim White	Temporary
Engineering	Jane Brown	Intern
Engineering	John Smith	Engineer III
Marketing	Jane Smith	Marketing III
Marketing	Joe Brown	Trainee
Marketing	John Brown	Salesman I
Personnel	NULL	NULL
Sales	Joe Smith	Salesman I
Sales	John Brown	Salesman I

We reiterate what we said earlier: Department "Personnel" and Employee "Jim White" are respectively a childless parent and a parentless child, and both are in the relationship. On the other hand, Department "Projects" and Employee "Jack Mills" are not in the relationship. Equivalently, Department "Personnel" is referenced by AGG tuple 103, but no AGG tuple references Department "Projects" (primary key = "DEPT5"), and Employee "Jim White" (primary key = "EMP16") is referenced by LNK tuple 1007, but no LNK tuple references Employee "Jack Mills" (primary key = "EMP17"). The referenced tuples (in DEPT, EMP) appear in the Select query response above, the unreferenced tuples do not. This explicit representation of childless parents and parentless children in the Aggregate-Link representation makes it possible to have relationships that don't include all Parent- and all Child tuples. For the Primary Key / Foreign Key (PKFK) and Junction Table (JT) representations, unreferenced Parent- and Child tuples can be included in the relationship or not, depending on how the Select query is formulated vis-a-vis outer joins. By comparison to A-L, PKFK and JT are not as precise.

It is not surprising then that any relationship represented by either PKFK or JT can also be represented using A-L. See Copying a PKFK relationship to A-L and Copying a JT relationship to A-L for detailed SQL examples.

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