%
% Understanding Relational Database Query Languages, S. W. Dietrich, Prentice Hall, 2001.
%
% -------------------------------------------------------------------------------------------------------------------------------
%                 TUPLE RELATIONAL CALCULUS (TRC)
% -------------------------------------------------------------------------------------------------------------------------------
%                        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)
%
%--------------------------------------------------------------------------------------------------------------------------------
%
% TRC: Fundamental EMPLOYEE TRAINING Queries

qSelection := 
    { E | employee(E) and E.eSalary > 100000 }; 

qProjection :=
    { E.eLast, E.eFirst, E.eTitle | employee(E)};


managers := 
    { E.eID | employee(E) and E.eTitle='Manager' }; 
coaches := 
    { E.eID | employee(E) and E.eTitle='Coach' }; 
qUnion :=
    { T | managers(T) or coaches(T) };

% Alternative for qUnion: one step
qUnionA :=
    { E.eID | employee(E) and (E.eTitle = 'Manager' or E.eTitle = 'Coach') };

% managers := { E.eID | employee(E) and E.eTitle='Manager' }; 
takenCourse := 
    { T.eID | takes(T)  }; 
qDifference :=
    { T | managers(T) and not takenCourse(T) };

%Alternative for qDifference: one step
qDifferenceA :=
    { E.eID | employee(E) and E.eTitle='Manager' and  
	not (exists T) (takes(T) and T.eID=E.eID) };

qProduct :=
    { E.eID, C.cID | employee(E) and trainingCourse(C) };

% TRC: Additional EMPLOYEE TRAINING Queries

% managers := { E.eID | employee(E) and E.eTitle='Manager' };
% takenCourse := { T.eID | takes(T) };  
qIntersection :=
    { T | managers(T) and takenCourse(T)  };

% Alternative for qIntersection: one step
qIntersectionA :=
    { E.eID | employee(E) and E.eTitle='Manager' and  
	(exists T) (takes(T) and T.eID=E.eID) };
qJoin :=
    { E, A | employee(E) and  technologyArea(A) and E.eID=A.aLeadID };

qNaturalJoin :=
    { C.cTitle, T.tYear, T.tMonth, T.tDay | 
	trainingCourse(C) and takes(T) and C.cID=T.cID  };

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

% TRC: Safety Example

leads :=
    { E.eID | employee(E) and (exists A)(technologyArea(A) and A.aLeadID=E.eID) };

qSafety :=
    { E | employee(E) and not (exists L) (leads(L) and L.eID=E.eID)};

% TRC: Example EMPLOYEE TRAINING Queries

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

dbCourse :=
   { T.cID, T.cTitle, T.cHours | trainingCourse(T) and (exists A) 
      (technologyArea(A) and A.aID = T.areaID and A.aTitle = 'Database') }; 

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

dbEmployee :=
{ E.eID, E.eLast, E.eFirst, E.eTitle | employee(E) and  
   (exists T,D) (takes(T) and dbCourse(D) and T.eID=E.eID and T.cID=D.cID) };

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

q3 :=
    { E.eID, E.eLast, E.eFirst, E.eTitle | employee(E) and  
   not (exists T) (takes(T) and T.eID=E.eID)  };

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

q4 :=
    { E.eID, E.eLast, E.eFirst, E.eTitle| employee(E) and 
	(exists T1,T2,C1,C2) 
	   (takes(T1) and T1.eID=E.eID and  
	    takes(T2) and  T2.eID=E.eID and  
	    trainingCourse(C1) and T1.cID=C1.cID and 
	    trainingCourse(C2) and T2.cID=C2.cID and 
	    C1.areaID  <>  C2.areaID) }; 

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

q5 :=
    { E | employee(E) and  
	  not (exists S) (employee(S) and S.eSalary  <  E.eSalary) }; 

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

q6 :=
    { E.eID, E.eLast, E.eFirst, E.eTitle | employee(E) and 
	(exists B)(dbEmployee(B) and B.eID=E.eID) and
	not (exists D)(dbCourse(D) and  
	    not (exists T) (takes(T) and T.eID=E.eID and T.cID=D.cID) )  }; 

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