%
% 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)

% DOMAIN RELATIONAL CALCULUS

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

drc1:= 
  {FNAME,LNAME,ADDRESS|
    (exists DNO)
        (department('Research',DNO,_,_) and 
         employee(FNAME,_,LNAME,_,_,ADDRESS,_,_,_,DNO))};

% ----- 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.

drc2:=
  {PNUMBER,DNUM,LNAME,ADDRESS,BDATE|
    (exists MGRSSN)
      (projects(_,PNUMBER,'Stafford',DNUM) and 
       department(_,DNUM,MGRSSN,_) and 
       employee(_,_,LNAME,MGRSSN,BDATE,ADDRESS,_,_,_,_))};

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

drc3:=
  {LNAME,FNAME|
    (exists SSN)
      (employee(FNAME,_,LNAME,SSN,_,_,_,_,_,_) and 
      not (exists PNUMBER)
           (projects(_,PNUMBER,_,5) and 
            not(works_on(SSN,PNUMBER,_))))};

% ----- 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.

drc4:=
  {PNO|
    (exists SSN)
      (employee(_,_,'Smith',SSN,_,_,_,_,_,_) and
      (works_on(SSN,PNO,_) 
        or 
      (exists DNO)
        (department(_,DNO,SSN,_) and 
         projects(_,PNO,_,DNO))))};

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

drc5:=
  {LNAME,FNAME|
    (exists SSN)
      (employee(FNAME,_,LNAME,SSN,_,_,_,_,_,_) and
      (exists DEP1,DEP2)
        (dependent(SSN,DEP1,_,_,_) and 
         dependent(SSN,DEP2,_,_,_) and 
         DEP1 <> DEP2))};
         
% ----- query 6 -----;
% Retrieve the names of employees who have no dependents.

drc6:=
  {LNAME,FNAME|
    (exists SSN)
    (employee(FNAME,_,LNAME,SSN,_,_,_,_,_,_) and 
     not(dependent(SSN,_,_,_,_)))};


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

drc7:=
  {LNAME,FNAME|
    (exists MGRSSN)
      (employee(FNAME,_,LNAME,MGRSSN,_,_,_,_,_,_) and 
       department(_,_,MGRSSN,_) and 
       dependent(MGRSSN,_,_,_,_))};