Transcript LAB 4 - Gediz

LAB 4
FIGURES
EMPLOYEE TABLE
PROJECT TABLE
DEPARTMENT TABLE
DEPENDENT TABLE
WORKS_ON TABLE
DEPT_LOCATIONS TABLE
RENAMING OPERATION
Note : Ssn shouldn’t be underlined.
UNION OPERATION
UNION OPERATION
INTERSECTION OPERATION
SET DIFFERENCE (OR MINUS)
CARTESIAN PRODUCT
CARTESIAN PRODUCT
CARTESIAN PRODUCT
JOIN OPERATION
NATURAL JOIN OPERATION
NATURAL JOIN
LEFT OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
DIVISION OPERATION
AGGREGATE FUNCTIONS
ᵨ R(Dno,No_of_employees,Average_sal)(Dno COUNT Ssn, AVERAGE Salary(EMPLOYEE))
No renaming :
AGGREGATE FUNCTIONS
ᵨ R(Dno,No_of_employees,Average_sal)(Dno COUNT Ssn, AVERAGE Salary(EMPLOYEE))
No grouping :
QUERY 1
Retrieve the name and address of all employees who work for
the ‘Research’ department.
QUERY 1
RESEARCH_DEPT <- σ Dname=’Research’ (DEPARTMENT)
RESEARCH_EMPS <- (RESEARCH_DEPT Dnumber=Dno EMPLOYEE)
RESULT <- ∏ Fname, Lname, Address (RESEARCH_EMPS)
OR
• ∏ Fname, Lname, Address (σ Dname=’Research’(DEPARTMENT
Dnumber=Dno
(EMPLOYEE)))
QUERY 2
• List the names of employees with two or more dependents.
QUERY 2
T1(Ssn, No_of_dependents) <- Essn
COUNT Dependent_name
T2 <- σ No_of_dependents>2 (T1)
RESULT <- ∏ Lname, Fname (T2 * EMPLOYEE)
(DEPENDENT)
QUERY 3
• Find the names of employees who work on all the projects
controlled by department number 5.
QUERY 3
DEPT5_PROJS <EMP_PROJ <-
ᵨ
ᵨ
(Pno)(∏ Pnumber
(σ Dnum=5(PROJECT)))
(Ssn,Pno)(∏ Essn,Pno(WORKS_ON))
QUERY 3
RESULT_EMP_SSNS <- EMP_PROJ  DEPT5_PROJS
RESULT <- ∏ Lname, Fname (RESULT_EMP_SSNS * EMPLOYEE)
QUERY 4
List the names of managers who have at least one dependent.
QUERY 4
MGRS(Ssn) <- ∏ Mgr_ssn (DEPARTMENT)
EMPS_WITH_DEPS(Ssn) ∏ Essn (DEPENDENT)
MGRS_WITH_DEPS <- (MGRS ∩ EMPS_WITH_DEPS)
RESULT <- ∏ Lname, Fname (MGRS_WITH_DEPS * EMPLOYEE)