%
% Understanding Relational Database Query Languages, S. W. Dietrich, Prentice Hall, 2001.
%
% -------------------------------------------------------------------------------------------------------------------------------
%                           DRC By-Name 
% -------------------------------------------------------------------------------------------------------------------------------
%                        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 By Name: Fundamental EMPLOYEE TRAINING Queries



qSelection(employeeID: I, employeeLast: L, employeeFirst: F, 

                         employeeTitle: T, employeeSalary: S) <- 

	employee(eLast: L, eID: I, eFirst: F, eSalary: S, eTitle: T) and 

	S > 100000;

 

qProjection(employeeLast: L, employeeFirst: F, 

                           employeeTitle: T) <-  

  employee(eTitle: T, eLast: L, eFirst: F);



% Alternative for qProjection: separate existential quantification

qProjectionA(lastName: L, firstName: F, title: T) <-  

  (exists I)(exists S)

    (employee(eTitle: T, eLast: L, eFirst: F, eID: I, eSalary: S));



% Alternative for qProjection: shortcut for quantifying multiple attributes

qProjectionB(eLast: L, eFirst: F, eTitle: T) <-  (exists I,S)

  (employee(eLast: L, eFirst: F, eTitle: T, eID: I, eSalary: S));



managers(employeeID: I) <- employee(eID: I, eTitle: 'Manager'); 

coaches(employeeID: I) <- employee(eID: I, eTitle: 'Coach'); 

qUnion(employeeID: I) <- managers(employeeID: I) or 

                                   coaches(employeeID: I);



% Alternative for qUnion: using existential quantification

managersA(eID: I) <- (exists E)  

	(employee(eTitle: E, eID: I) and E='Manager');   

coachesA(empID: I) <- (exists E)   

	(employee(eID: I, eTitle: E) and E='Coach');   

qUnionA(eID: I) <- managersA(eID: I) or coachesA(empID: I);



% Alternative for qUnion: one step

qUnionB(eID: I) <- (exists E) (employee(eID: I,eTitle: E) and 

	(E = 'Manager' or E = 'Coach') );



%Reuse managers from previous query	 

%managers(eID: I) <- employee(eID: I, eTitle: 'Manager'); 

takenCourse(eID: I) <- takes(eID:I); 

qDifference(eID: I) <- managers(employeeID: I) 

                       and not takenCourse(eID: I);  



%Alternative for qDifference: one step

qDifferenceA(eID: I) <- employee(eID: I, eTitle: 'Manager') 

                        and not takes(eID: I);



qProduct(eID: I, cID: C) <-  employee(eID: I) 

                                         and trainingCourse(cID: C);



% DRC By Name: Additional EMPLOYEE TRAINING Queries



%managers(eID: I) <- employee(eID: I, eTitle: 'Manager'); 

%takenCourse(eID: I) <- takes(eID:I); 

qIntersection(eID: I) <- managers(employeeID: I) and 

                                          takenCourse(eID: I);



% Alternative for qIntersection: one step

qIntersectionA(eID: I) <- employee(eID: I, eTitle: 'Manager') and takes(eID: I);



qJoin(eID: I, eLast: L, eFirst: F, eTitle: T, eSalary: S, aID: AI, 

               aTitle: AT, aURL: U, aLeadID: AL) <-     

	        employee(eID: I, eLast: L, eFirst: F, eTitle: T, eSalary: S) and 

	        technologyArea(aID: AI, aTitle: AT, aURL: U, aLeadID: AL) and I=AL;



qNaturalJoin(cTitle: T, tYear: Y, tMonth: M, tDay: D) <- 

                  (exists C)  

	                  (trainingCourse(cID: C, cTitle: T) and    

	                   takes(cID: C, tYear: Y, tMonth: M, tDay: D)); 



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



% DRC: Safety Example



qSafety(eID: I) <- employee(eID: I) 

                              and not technologyArea(aLeadID: I);



% DRC:  Example EMPLOYEE TRAINING Queries



% Q1: What training courses are offered in the `Database' technology area?

%     (cID, cTitle, cHours)



dbCourse(courseID: I, courseTitle: T, courseHours: H) <- 

              (exists A) 

                (technologyArea(aID: A, aTitle: 'Database') and  

                 trainingCourse(areaID: A, cID: I, cTitle: T, cHours: H));



% Q2: Which employees have taken a training course offered in the 

%     'Database' technology area?

%     (eID, eLast, eFirst, eTitle)



dbEmployee(eID: I, eLast: L, eFirst: F, eTitle: T ) <- 

                (exists C)  

                  (dbCourse(courseID: C) and takes(cID: C, eID: I) and 

                   employee(eFirst: F, eID: I, eLast: L, eTitle: T)) ;



% Q3: Which employees have not taken any training courses?

%     (eID, eLast, eFirst, eTitle)



q3(eID: I, eLast: L, eFirst: F, eTitle: T ) <- 

        employee(eID: I, eLast: L, eTitle: T, eFirst: F) and not takes(eID: I);



% Q4: Which employees took courses in more than one technology area?  

%     (eID, eLast, eFirst, eTitle)



q4(eID: I, eLast: L, eFirst: F, eTitle: T ) <- 

          employee(eLast: L, eFirst: F, eTitle: T, eID: I) and 

          (exists C1, C2, A1, A2)  

            (takes(cID: C1, eID: I) and trainingCourse(cID: C1, areaID: A1) and

             takes(eID: I, cID: C2) and trainingCourse(areaID: A2, cID: C2) and 

             A1  <>  A2 ) ;  



% Q5: Which employees have the minimum salary?

%     (eID, eLast, eFirst, eTitle, eSalary)



q5(empID: I, empLast: L, empFirst: F, empTitle: T, empSalary: S ) <- 

         employee(eID: I, eLast: L, eFirst: F, eSalary: S, eTitle: T) and 

         not (exists S2) (employee(eSalary: S2) and S2  <  S); 



% 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: I, eLast: L, eFirst: F, eTitle: T) <- 

         employee(eID: I, eLast: L, eFirst: F, eTitle: T) and 

         dbEmployee(eID: I) and

         not (exists D)  

             (dbCourse(courseID: D)  

              and not takes(eID: I, cID: D) ); 



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