%
% Fundamentals of Database Systems, 4th ed., R. Elmasri and S. B. Navathe, Addison Wesley, 2004
%
% Schema (Chapter 5, Figure 5.5, page 136)
%   employee(fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno)
%        primary key (ssn)
%        foreign key (supersnn) references employee(ssn)
%        foreign key (dno) references department(dnumber)
%   department(dname, dnumber, mgrssn, mgrstartdate)
%        primary key (dnumber)
%	 foreign key (mgrssn) references employee(ssn)
%   dept_locations(dnumber, dlocation)
%        primary key (dnumber,dlocation)
%        foreign key (dnumber) references department(dnumber)
%%%%  NOTE THAT THE TABLE project IS NAMED projects BECAUSE OF THE project RELATIONAL ALGEBRA OPERATOR.
%   projects(pname, pnumber, plocation, dnum)
%        primary key (pnumber)
%        foreign key (dnum) references department(dnumber)
%   works_on(essn, pno, hours)
%        primary key (essn, pno)
%        foreign key (esnn) references employee(ssn)
%        foreign key (pno) references projects(pnumber)
%   dependent(essn, dependent_name, sex, bdate, relationship)
%        primary key (essn, dependent_name)
%        foreign key (esnn) references employee(ssn)
% Instance (Chapter 5, Figure 5.6, page 137)
%   Note there is one additional works_on('123456789',3,15) tuple so that query 3 is non-empty.
% Queries (Chapter 6, Section 6.5, pp 171-173)

% RELATIONAL ALGEBRA---------------------------------------------
 
% ----- query 1 -----;
% Retrieve the name and address of all employees who work for the 
% 'Research' department.

researchDept :=
	select dname='Research' (department);

researchEmps :=
	select dnumber=dno (researchDept product employee);

alg1 :=
	project fname, lname, address (researchEmps);

% ----- query 2 -----;
% For every project located in 'Stafford', list the project number, the
% controlling department number, and the department manager's last name,
% address, and birthdate.

staffordProjs :=
	select plocation='Stafford' (projects);

controllingDept :=
	select dnum=dnumber (staffordProjs product department);

projDeptMgr :=
	select mgrssn=ssn (controllingDept product employee);

alg2 :=
	project pnumber, dnum, lname, address, bdate (projDeptMgr);

% ----- query 3 -----;
% Find the names of employees who work on all the projects controlled
% by department number 5.

dept5Projs(pno) :=
	project pnumber (select dnum=5 (projects));

empProj(ssn, pno) :=
	project essn, pno (works_on);

empProjSsns :=
	project ssn (empProj);

% All possibilities of employees working on dept5 projects.
possEmpsDept5 :=
	dept5Projs product empProjSsns;

% Employees that don't work on all dept5 projects..
empsNotAllDept5 :=
	project ssn (possEmpsDept5 difference empProj);

resultEmpSsns :=
	empProjSsns difference empsNotAllDept5;

alg3 :=
	project lname, fname (resultEmpSsns njoin employee);

% ----- query 4 -----;
% Make a list of project numbers for projects that involve an employee
% whose last name is 'Smith', either as a worker or as a manager of the
% department that controls the project.

smiths(essn) :=
	project ssn (select lname='Smith' (employee));

smithWorkerProjs :=
	project pno (works_on njoin smiths);

mgrs :=
	project lname, dnumber (select ssn=mgrssn (employee product department));

smithManagedDepts(dnum) :=
	project dnumber (select lname='Smith' (mgrs));

smithMgrProjs(pno) :=
	project pnumber (smithManagedDepts njoin projects);

alg4 :=
	smithWorkerProjs union smithMgrProjs;

% ----- query 5 -----;
% List the names of all employees with two or more dependents.

% Make two copies of employees with dependents.
empdep1(essn1, depname1) :=
	project essn, dependent_name (dependent);

empdep2(essn2, depname2) := 
	empdep1;

% Employees with more than one dependent.
empsGtoneDep(ssn) :=
	project essn1 (select (essn1=essn2) and (depname1<>depname2) (empdep1 product empdep2));

alg5 :=
	project lname, fname (employee njoin empsGtoneDep);

% ----- query 6 -----;
% Retrieve the names of employees who have no dependents.

allEmps :=
	project ssn (employee);

empsWithDeps(ssn) :=
	project essn (dependent);

empsWithoutDeps :=
	allEmps difference empsWithDeps;

alg6 :=
	project lname, fname (empsWithoutDeps njoin employee);

% ----- query 7 -----;
% List the names of managers who have at least one dependent.

mgrSsns(ssn) :=
	project mgrssn (department);

% Following line commented out since already defined in query 6.
% empsWithDeps(ssn) := project essn (dependent);

mgrsWithDeps :=
	mgrSsns intersect empsWithDeps;

alg7 :=
	project lname, fname (mgrsWithDeps njoin employee);