
// CF 7-21

// Incumbents table

CREATE TABLE Incumbents (
                        SSN             integer,
                        PCN             integer,
                        StartDate       date,
                        EndDate         date,
                        PRIMARY KEY (SSN, PCN)
                        );

// Positions table & data

CREATE TABLE Positions (
                        PCN             integer,
                        JobTitleCode1   integer,
                        StartDate       date,
                        EndDate         date,
                        PRIMARY KEY (PCN)
                       );
// JobTitles table & data

CREATE TABLE JobTitles (
                        JobTitleCode    integer,
                        JobTitle        char(66),
                        PRIMARY KEY (JobTitleCode)
                       );

// Actual modifications

INSERT INTO Incumbents 
   SELECT 111223333,
          Incumbents.PCN,
          DATE '12/05/1997',
          Incumbents.EndDate
     FROM Incumbents, Positions, JobTitles
    WHERE SSN = 111223333
      AND Incumbents.StartDate <= DATE '12/05/1997'
      AND Incumbents.EndDate > DATE '12/05/1997'
      AND Positions.JobTitleCode1 = JobTitleCode
      AND JobTitle = 'Director, Computer Center'
      AND Positions.StartDate <= DATE '12/05/1997'
      AND DATE '12/05/1997' < Positions.EndDate;

UPDATE Incumbents
   SET EndDate = DATE '12/05/1998'
 WHERE SSN = 111223333
   AND PCN <> (SELECT PCN
                 FROM Positions, JobTitles
                WHERE Positions.JobTitleCode1 = JobTitleCode
                  AND JobTitle = 'Director, Computer Center'
                  AND Positions.StartDate <= DATE '12/05/1997'
                  AND DATE '12/05/1997' < Positions.EndDate)
   AND Incumbents.StartDate < DATE '12/05/1997'
   AND Incumbents.EndDate > DATE '12/05/1997';

UPDATE Incumbents
   SET PCN = (SELECT PCN
                FROM Positions, JobTitles
               WHERE Positions.JobTitleCode1 = JobTitleCode
                 AND JobTitle = 'Director, Computer Center'
                 AND Positions.StartDate <= DATE '12/05/1997'
                 AND DATE '12/05/1997' < Positions.EndDate)
 WHERE SSN = 111223333
   AND StartDate >= DATE '12/05/1997';

