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



% DRC By-Name 



% ----- query 1 -----;

% Retrieve the name and address of all employees who work for the

% 'Research' department.

dbn1(firstName: F,lastName: L,address: A) <- 

    (exists D)

        (department(dnumber: D, dname: 'Research') and

         employee(fname: F, dno: D, address: A, lname: L));



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



dbn2(projectNum: P,departmentNum: D,lastName: L, address: A, birthDate: B) <-

  (exists M)

      (projects(dnum: D, pnumber: P,plocation: 'Stafford') and

       department(mgrssn: M, dnumber: D) and

       employee(address: A, lname: L, bdate: B, ssn: M));



% ----- query 3 -----;

% Find the names of employees who work on all the projects controlled

% by department number 5.



dbn3(last: L,first: F) <-

    (exists S)

      (employee(lname: L, ssn: S, fname: F)

      and

      not(exists P)

           (projects(pnumber: P,dnum: 5) and

          not(works_on(pno: P, essn: S))));



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



dbn4(projectNumber: P) <-

    (exists S)

      (employee(ssn: S, lname:'Smith') and

      (works_on(essn: S,pno: P)

        or

      (exists D)

        (department(mgrssn: S, dnumber: D) and

         projects(pnumber: P, dnum: D))));



% ----- query 5 -----;

% List the names of all employees with two or more dependents.



dbn5(last: L,first: F) <-

    (exists S)

      (employee(lname: L, fname: F,ssn: S) and

      (exists D1,D2)

        (dependent(dependent_name: D1, essn: S) and

         dependent(essn: S,dependent_name: D2) and

         D1 <> D2));



% ----- query 6 -----;

% Retrieve the names of employees who have no dependents.



dbn6(last: L,first: F) <-

    (exists S)

    (employee(ssn: S, fname: F,lname: L) and

     not(dependent(essn: S)));





% ----- query 7 -----;

% List the names of managers who have at least one dependent.



dbn7(lastName: L,firstName: F) <-

    (exists M)

      (employee(fname: F,ssn: M, lname: L) and

       department(mgrssn: M) and

       dependent(essn: M));