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

qSelection :=
    select  eSalary > 100000 (employee);

qProjection :=
    project eLast, eFirst, eTitle (employee);

qUnion :=
    (project eID (select  eTitle = 'Manager' (employee)))  union  
    (project eID (select  eTitle = 'Coach' (employee)));

% Alternative for qUnion: using or in selection condition
qUnionA :=
    project eID (select  eTitle = 'Manager'  or  eTitle = 'Coach' (employee));

qDifference :=
    (project eID (select eTitle='Manager' (employee))) difference (project eID (takes));

qProduct :=
    (project eID (employee)) product (project cID (trainingCourse));

% Relational Algebra: Additional EMPLOYEE TRAINING Queries

qIntersection :=
    (project eID (select eTitle='Manager'(employee))) intersect (project eID (takes));

% The generic join is not supported by the educational tool by design,
% forcing its equivalent definition in terms of the fundamental relational algebra operators.
qJoin :=
    select eID = aLeadID (employee product technologyArea);

qNaturalJoin :=
    project cTitle, tYear, tMonth, tDay (trainingCourse njoin takes);

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

% Intermediate Tables

managers := 
     project eID (select eTitle='Manager' (employee));
takenCourse := 
    project eID (takes);
qIntersectionA :=
    managers intersect takenCourse;

% Renaming attributes
leads(eID) := 
    project aLeadID (technologyArea);

managersOrLeads :=
    managers union leads;

% Relational Algebra: Example EMPLOYEE TRAINING Queries

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

dbTechArea(areaID) := 
    project aID (select aTitle='Database' (technologyArea)); 
q1 := 
     project cID, cTitle, cHours (dbTechArea njoin trainingCourse);

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

dbCourseID := 
    project cID (q1);
q2 := 
     project eID, eLast, eFirst, eTitle ( (dbCourseID njoin takes) njoin employee);

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

alleIDs := 
    project eID (employee);
takeseIDs := 
    project eID (takes);
q3 :=  
    project eID, eLast, eFirst, eTitle ( (alleIDs difference takeseIDs) njoin employee);

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

eIDaIDs(eID, aID) := 
    project eID, areaID (takes njoin trainingCourse);
eIDaIDsCopy(eID1, aID1) := 
    eIDaIDs;
eIDsMoreThanOne := 
    project eID (select eID=eID1 and aID <> aID1 (eIDaIDs product eIDaIDsCopy));
q4 :=  
    project eID, eLast, eFirst, eTitle (eIDsMoreThanOne njoin employee);

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

empSal := 
    project eSalary(employee);
empSalCopy(eSalary1) := 
    empSal;
empSalNotMin := 
    project eSalary (select eSalary > eSalary1 (empSal product empSalCopy));
empSalMin := 
    empSal difference empSalNotMin;
q5 :=   
    employee njoin empSalMin;

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

% The division operator is not supported by the educational tool by design,
% forcing its equivalent definition in terms of the fundamental relational algebra operators.
% empsAllDB := (project eID, cID (takes)) / dbCourseID;

% Reusing q2, which finds the employees that took a Database course
allEmpWithAllDB :=
    (project eID (q2)) product dbCourseID;

empNotRelatedToSomeDB :=
    project eID (allEmpWithAllDB difference (project eID, cID (takes)));

empsAllDB :=
    (project eID (q2)) difference empNotRelatedToSomeDB;

q6 :=
    project eID, eLast, eFirst, eTitle (empsAllDB njoin employee);

% Query Optimization

% Q1 illustrates: Performing selections as early as possible

% Q1 Option A: selects Database title as early as possible
q1OptA :=
    project cID, cTitle, cHours  
	(select aID=areaID
	   ((project aID (select aTitle='Database' (technologyArea))) product trainingCourse));

% Stepwise refinement of q1OptA
q1A1 := project aID (select aTitle='Database' (technologyArea));
q1A2 := q1A1 product trainingCourse;
q1A3 := select aID=areaID (q1A2);
q1A4 := project cID, cTitle, cHours (q1A3);

% Q1 Option B: performs selection of Database title after product

q1OptB :=
    project cID, cTitle, cHours
	(select aTitle='Database'  
	    (select aID=areaID (technologyArea product trainingCourse)));

% Stepwise refinement of q1OptB
q1B1 := technologyArea product trainingCourse;
q1B2 := select aID=areaID (q1B1);
q1B3 := select aTitle='Database' (q1B2);
q1B4 := project cID, cTitle, cHours (q1B3);

% Q1: Compare size of intermediate tables in stepwise refinement of Option A versus Option B

% Q2 illustrates: Reordering of joins

% Q2 Option A: Join dbCourse with takes first
q2OptA :=
    project eID, eLast, eFirst, eTitle 
	((q1 njoin takes) njoin employee);

% Stepwise refinement of q2OptA    
q2A1 := q1 njoin takes;
q2A2 := q2A1 njoin employee;
q2A3 := project eID, eLast, eFirst, eTitle (q2A2);

% Q2 Option B: Joins with dbCourse last
q2OptB :=
    project eID, eLast, eFirst, eTitle
	(q1 njoin (takes njoin employee));

% Stepwise refinement of q2OptB
q2B1 := takes njoin employee;
q2B2 := q1 njoin q2B1;
q2B3 := project eID, eLast, eFirst, eTitle (q2B2);

% Q2: Compare size of intermediate tables in stepwise refinement of Option A versus Option B

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