Copying a JT Representation to A-L

/* mssql.jtDB.createdatabase.1.sql */
/* JT example - creates a database named jtDB. */
use master;
go
create database jtDB;
go
/* mssql.jtDB.createobjectandjtrelations.1.sql */
/*   Creates object tables for a Department-Employee relationship. */
use jtDB;
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
);
go
create table JNCT (
   JNCT_DEPT_fk         char(6)   not null,
   JNCT_EMP_fk          char(6)   not null,
      primary key(JNCT_DEPT_fk,JNCT_EMP_fk));
go

/* mssql.jtDB.populateobjectandjttables.1.sql */
/*   Inserts DEPT (Department, EMP (Employee) object records, JNCT structure records. */
use jtDB;
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) values ('EMP10','John Smith');
go
insert JNCT(JNCT_DEPT_fk,JNCT_EMP_fk) values ('DEPT1','EMP10');
go
insert EMP(EMP_pk,EMP_employeename) values ('EMP11','Jane Smith');
go
insert JNCT(JNCT_DEPT_fk,JNCT_EMP_fk) values ('DEPT2','EMP11');
go
insert EMP(EMP_pk,EMP_employeename) values ('EMP12','Joe Smith');
go
insert JNCT(JNCT_DEPT_fk,JNCT_EMP_fk) values ('DEPT3','EMP12');
go
insert EMP(EMP_pk,EMP_employeename) values ('EMP13','John Brown');
go
insert JNCT(JNCT_DEPT_fk,JNCT_EMP_fk) values ('DEPT2','EMP13');
go
/* Notice that John Brown in Sales Department as well as Marketing. */
insert JNCT(JNCT_DEPT_fk,JNCT_EMP_fk) values ('DEPT3','EMP13');
go
insert EMP(EMP_pk,EMP_employeename) values ('EMP14','Jane Brown');
go
insert JNCT(JNCT_DEPT_fk,JNCT_EMP_fk) values ('DEPT1','EMP14');
go
insert EMP(EMP_pk,EMP_employeename) values ('EMP15','Joe Brown');
go
insert JNCT(JNCT_DEPT_fk,JNCT_EMP_fk) values ('DEPT2','EMP15');
go
/* Notice that Employees Jim White and Jack Mills are not assigned Departments. */
insert EMP(EMP_pk,EMP_employeename) values ('EMP16','Jim White');
go
insert EMP(EMP_pk,EMP_employeename) values ('EMP17','Jack Mills');
go

/* mssql.jtDB.selectusingjt.1.sql */
/*   Selects Department-Employee pairs using JT (excludes childless parents, orphans). */
use jtDB;
go
select d.DEPT_pk,d.DEPT_departmentname,e.EMP_pk,e.EMP_employeename
from DEPT as d
     inner join JNCT as j on d.DEPT_pk = j.JNCT_DEPT_fk
     inner join EMP  as e on j.JNCT_EMP_fk = e.EMP_pk
order by d.DEPT_pk,e.EMP_pk;
go

/* mssql.jtDB.selectusingjt.1.sql */
/*   Selects Department-Employee pairs using JT (includes childless parents and orphans). */
use jtDB;
go
select d.DEPT_pk,d.DEPT_departmentname,e.EMP_pk,e.EMP_employeename
from DEPT as d
     left outer join JNCT as j on d.DEPT_pk = j.JNCT_DEPT_fk
     right outer join EMP  as e on j.JNCT_EMP_fk = e.EMP_pk
order by d.DEPT_pk,e.EMP_pk;
go

/* mssql.jtDB.addstructurerelations.1.sql */
/*   Adds aggregate and link tables. */
use jtDB;
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)   not null
);
go

/* mssql.jtDB.copyjttoal.1.sql */
/*   Copies JT Department-Employee relationship to A-L (no orphans). */
use jtDB;
go
insert into AGG(AGG_DEPT_fk) 
   select DEPT_pk from DEPT;
go
insert into LNK(LNK_AGG_fk,LNK_EMP_fk)
   select AGG_pk,EMP_pk
      from AGG as a
         join JNCT as j on a.AGG_DEPT_fk = j.JNCT_DEPT_fk
         join EMP  as e on j.JNCT_EMP_fk = e.EMP_pk;
go

/* mssql.jtDB.selectusingal.1.sql */
/*   Selects Department-Employee pairs using A-L. */
use jtDB;
go
select d.DEPT_pk,d.DEPT_departmentname,e.EMP_pk,e.EMP_employeename
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

/* mssql.jtDB.accountfororphans.1.sql */
/*   Copies JT Department-Employee orphans to A-L. */
use jtDB;
go
insert AGG(AGG_DEPT_fk) values (NULL);
go
insert 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_pk not in (SELECT l.LNK_EMP_fk FROM LNK as l);
go