%
% Understanding Relational Database Query Languages, S. W. Dietrich, Prentice Hall, 2001.
%
% -------------------------------------------------------------------------------------------------------------------------------
%                 DOMAIN RELATIONAL CALCULUS (DRC)
% -------------------------------------------------------------------------------------------------------------------------------
%                        EMPLOYEE TRAINING Enterprise
%
%  employee(eID, eLast, eFirst, eTitle, eSalary)
%       primary key (eID)
%  technologyArea(aID, aTitle, aURL, aLeadID)
%       primary key (aID)
%       foreign key (aLeadID) references employee(eID)
%  trainingCourse(cID, cTitle, cHours, areaID)
%       primary key (cID)
%       foreign key (areaID) references technologyArea(aID)
%  takes(eID, cID, tYear, tMonth, tDay)
%       primary key (eID, cID)
%       foreign key (eID) references employee(eID)
%       foreign key (cID) references course(cID)
%
%--------------------------------------------------------------------------------------------------------------------------------
%
% DRC: Fundamental EMPLOYEE TRAINING Queries

qSelection :=
    { EID, ELast, EFirst, ETitle, ESalary |
	employee(EID, ELast, EFirst, ETitle, ESalary) and 
	ESalary > 100000 };
 
qProjection :=
    { ELast, EFirst, ETitle | employee(_, ELast, EFirst, ETitle, _) };

% Alternative for qProjection: separate existential quantification
qProjectionA :=
    { ELast, EFirst, ETitle | (exists EID)(exists ESalary)
	(employee(EID, ELast, EFirst, ETitle, ESalary))  };

% Alternative for qProjection: shortcut for quantifying multiple attributes
qProjectionB :=
    { ELast, EFirst, ETitle | (exists EID, ESalary) 
	(employee(EID, ELast, EFirst, ETitle, ESalary)) };

managers := 
    { EID | employee(EID, _, _, 'Manager', _)  }; 
coaches := 
    { EID | employee(EID, _, _, 'Coach', _)  }; 
qUnion :=
    { EID | managers(EID) or coaches(EID) };

% Alternative for qUnion: using existential quantification
managersA := 
    { EID | (exists ETitle)  
	(employee(EID, _, _, ETitle, _) and ETitle='Manager') };   
coachesA := 
    { EID | (exists ETitle)   
	(employee(EID, _, _, ETitle, _) and ETitle='Coach') };   
qUnionA := 
    { EID | managersA(EID) or coachesA(EID) }; 

% Alternative for qUnion: one step
qUnionB :=
    { EID | (exists ETitle) (employee(EID, _, _, ETitle, _) and 
	(ETitle = 'Manager' or ETitle = 'Coach') )  };

%Reuse managers from previous query	 
% managers := 
%   { EID | employee(EID, _, _, 'Manager', _)  }; 
takenCourse := 
    { EID | takes(EID, _, _, _, _)  }; 
qDifference :=
    { EID | managers(EID) and not takenCourse(EID) };  

%Alternative for qDifference: one step
qDifferenceA :=
    { EID |  employee(EID, _, _, 'Manager', _) and not takes(EID, _, _, _, _) };

qProduct :=  
    { EID, CID | employee(EID, _, _, _, _) and trainingCourse(CID, _, _, _) };

% DRC: Additional EMPLOYEE TRAINING Queries

%managers := 
%    { EID | employee(EID, _, _, 'Manager', _)  };  		
%takenCourse := 
%    { EID | takes(EID, _, _, _,_) };  
qIntersection :=
    { EID | managers(EID) and takenCourse(EID)  };

% Alternative for qIntersection: one step
qIntersectionA :=
    { EID |  employee(EID, _, _, 'Manager', _) and takes(EID, _, _, _, _) };	

qJoin :=
    { EID, ELast, EFirst, ETitle, ESalary, AID, ATitle, AURL, ALeadID |    
	employee(EID, ELast, EFirst, ETitle, ESalary) and 
	technologyArea(AID, ATitle, AURL, ALeadID) and EID=ALeadID };

qNaturalJoin :=
    { CTitle, TYear, TMonth, TDay | (exists CID)  
	(trainingCourse(CID, CTitle, _, _) and    
	 takes(_, CID, TYear, TMonth, TDay))  }; 

% Division: see query Q6 below (see also the separate ABSTRACT DIVISION enterprise)

% DRC: Safety Example

qSafety :=
    { EID | employee(EID, _, _, _, _) and not technologyArea(_, _, _, EID)};

% DRC:  Example EMPLOYEE TRAINING Queries

% Q1: What training courses are offered in the `Database' technology area?
%     (cID, cTitle, cHours)

dbCourse :=
     { CID, CTitle, CHours | (exists AID) 
      (technologyArea(AID, 'Database', _, _) and  
       trainingCourse(CID, CTitle, CHours, AID)) };

% Q2: Which employees have taken a training course offered in the 
%     'Database' technology area?
%     (eID, eLast, eFirst, eTitle)

dbEmployee :=
{ EID, ELast, EFirst, ETitle | (exists CID)  
   (dbCourse(CID, _, _) and takes(EID, CID, _, _, _) and 
   employee(EID, ELast, EFirst, ETitle, _) )  };

% Q3: Which employees have not taken any training courses?
%     (eID, eLast, eFirst, eTitle)

q3:=
{ EID, ELast, EFirst, ETitle |  
   employee(EID, ELast, EFirst, ETitle, _) and not takes(EID, _, _, _, _)  };

% Q4: Which employees took courses in more than one technology area?  
%     (eID, eLast, eFirst, eTitle)

q4 :=
{ EID, ELast, EFirst, ETitle |  
   employee(EID, ELast, EFirst, ETitle, _) and 
   (exists CID1, CID2, AID1, AID2)  
      (takes(EID, CID1, _, _, _) and trainingCourse(CID1, _, _, AID1) and  
       takes(EID, CID2, _, _, _) and trainingCourse(CID2, _, _, AID2) and  
       AID1  <>  AID2 ) };  

% Q5: Which employees have the minimum salary?
%     (eID, eLast, eFirst, eTitle, eSalary)

q5 :=
{ EID, ELast, EFirst, ETitle, ESalary |   
   employee(EID, ELast, EFirst, ETitle, ESalary) and  
   not (exists Salary) (employee(_, _, _, _, Salary) and Salary  <  ESalary) }; 

% Q6: Which employees took all of the training courses offered 
%     in the `Database' technology area?  
%     (eID, eLast, eFirst, eTitle)

% Reusing dbEmployee from Q2, which finds the employees that took a Database course
q6 :=
{ EID, ELast, EFirst, ETitle |   
   employee(EID, ELast, EFirst, ETitle, _) and  
  dbEmployee(EID, _, _, _) and
   not (exists DBCourse)  
       (dbCourse(DBCourse, _, _)  
       and not takes(EID, DBCourse, _, _, _) ) }; 

%-----------------------------------------End EMPLOYEE TRAINING Enterprise--------------------------------------