File Structures
[TASK 1] SELECTIVITY
EMPLOYEE(SSN, NAME, SALARY, SUPERVISOR_SSN, PID)
Context: 500 employees:
10% with Salary £50K – £100K
60% with Salary £30K – £50K
30% with Salary less than £30K.
Estimate the selectivity of the following queries (provide the algebra expression):
SQL1
SELECT E.NAME
FROM EMPLOYEE E
WHERE E.SALARY >= 50000
SQL2
SELECT E.NAME
FROM EMPLOYEE E
WHERE E.SALARY >= 50000 OR E.SALARY =< 30000 1 [TASK 1] SELECTIVITY EMPLOYEE(SSN, NAME, SALARY, SUPERVISOR_SSN, PID) Context: 500 employees: 10% with Salary £50K - £100K 60% with Salary £30K - £50K 30% with Salary less than £30K. SQL1 SELECT E.NAME FROM EMPLOYEE E WHERE E.SALARY >= 50000
E.NAME (σE.SALARY > 50000 (EMPLOYEE))
500*10% = 50 employees satisfy the WHERE clause; selectivity: 50/500 = 0.1
SQL2
SELECT E.NAME
FROM EMPLOYEE E
WHERE E.SALARY >= 50000 OR E.SALARY =< 30000
E.NAME (σE.SALARY > 50K OR E.SALARY < 30K (EMPLOYEE))
500*(10% + 30%) = 200 employees; selectivity: 200/500 = 0.4
Or taking those with salary 30K-50K, i.e., 500*(100%-60%)
2
[TASK 2] RELATIONAL ALGEBRA
EMPLOYEE(SSN, NAME, SALARY, SUPERVISOR_SSN, PID)
PROJECT(PID, NAME, MANAGER_SSN)
500 employees: 10% with Salary over £50K
200 projects
SELECT E.NAME, P.NAME
FROM EMPLOYEE E, PROJECT P
WHERE P.PID = E.PID AND E.SALARY > 50000
Calculate the number of tuples fetched:
E.NAME, P.NAME (σE.SALARY > 50K (PROJECT X EMPLOYEE))
E.NAME, P.NAME (σE.SALARY > 50K (PROJECT P.PID = E.PID EMPLOYEE))
E.NAME, P.NAME ( PROJECT P.PID = E.PID (σE.SALARY > 50K(EMPLOYEE)))
3
[TASK 2] RELATIONAL ALGEBRA
EMPLOYEE(SSN, NAME, SALARY, SUPERVISOR_SSN, PID)
PROJECT(PID, NAME, MANAGER_SSN)
500 employees: 10% with Salary over £50K
200 projects
Calculate the number of tuples fetched:
E.NAME, P.NAME (σE.SALARY > 50K (PROJECT X EMPLOYEE))
There will be 500 x 200 = 100,000 tuples retrieved.
E.NAME, P.NAME (σE.SALARY > 50K (PROJECT P.PID = E.PID EMPLOYEE))
There will be 500 tuples retrieved (each employee works in one project)
E.NAME, P.NAME ( PROJECT P.PID = E.PID (σE.SALARY > 50K(EMPLOYEE)))
There will be retrieved: 50 employee tuples and then matched with 50
projects, thus 100 tuples.
4