Relational Algebra and Relational Calculus

1
Lecture 12 (09/29/2005)

• Relational Algebra and Relational Calculus

2
Comments

• COPY as a subclass of VIDEO
• Do exercises 3 and 4 for practice
• Mapping from EER to relational
• Project meetings
• Updated Schedule

3
Joins and Efficiency

• DBMSs efficiency can be affected by the way we
  do joins
• Find MGRSSN of any Research Department in Houston
• Join Department and Dept_Locations and then
  select MGRSSN from tuples where DnameResearch
  and Location Houston
• Select Departments with DnameResearch and and
  Dept_Locations where Dlocation Houston and
  then join results and get MGRSSN

4
Examples of Relational Algebra Queries

• Q1 Retrieve the names and addresses of all
  employees who work for the Research department
• RESEARCH_DEPT ? ? DNAMEResearch (DEPARTMENT)
• RESEARCH_EMPS ? (RESEARCH_DEPT DNUMBER
  DNOEMPLOYEEEMPLOYEE)
• RESULT ? ? FNAME, LNAME, ADDRESS (RESEARCH_EMPS)
• Q2 For every project located in Stafford, list
  the project number and the controlling department
  name
• STAFFORD_PROJS ? ? PLOCATIONSTAFFORD (PROJECT)
• CONTR_DEPT ? (STAFFORD_PROJS DNUMDNUMBER
  DEPARTMENT)
• RESULT ? ? PNUMBER, DNNAME (RESEARCH_EMPS)

5
Examples of Relational Algebra Queries

• Q3 For every project located in Stafford, list
  the project number, the controlling department
  name and the department managers last name,
  address and birth date
• STAFFORD_PROJS ? ? PLOCATIONSTAFFORD (PROJECT)
• CONTR_DEPT ? (STAFFORD_PROJS DNUMDNUMBER
  DEPARTMENT)
• PROJ_DEPT_MGR ? (CONTR_DEPT MGRSSNSSN
  EMPLOYEE)
• RESULT ? ? PNUMBER, DNAME,LNAME, ADDRESS, BDATE
  (RESEARCH_EMPS)

6
Binary Relational Operations

• DIVISION Operation
• Find tuples in R matching ALL those in S
• Find the SSNs of employees working on all
  projects
• Applies to 2 relations R(Z) ? S(X) T(Y), where
  Z X ? Y
• R(SSN, PNO) ? S(PNO) T(SSN)
• T(Y) that includes a tuple t if tuples tR appear
  in R with tR Y t, and with tR X ts for
  every tuple ts in S
• For a tuple t to appear in the result T of the
  DIVISION, the values in t must appear in R in
  combination with every tuple in S

7
Binary Relational Operations
S(A) a1, a2, a3 What is T?

• DIVISION Operation
• The division operation is applied to two
  relations R(Z) ? S(X), where X?? Z
• Find all tuples in R matching ALL those in S
• Find the SSN of employees working on all projects
  as John Smith
• Let Y Z - X (and hence Z X ? Y)
• let Y be the set of attributes of R that are not
  attributes of X
• The result of DIVISION is a relation T(Y) that
  includes a tuple t if tuples tR appear in R with
  tR Y t, and withtR X ts for every tuple
  ts in S
• For a tuple t to appear in the result T of the
  DIVISION, the values in t must appear in R in
  combination with every tuple in S

8
Division
Find the SSNs of employees who work on ALL
projects having a working employee, with the last
name Smith
SIMTH_PNO (PNO) 1, 2
9
Division
10
(No Transcript)
11
(No Transcript)
12
Examples of Relational Algebra Queries

• Q4 Find the names of employees who work on ALL
  projects controlled by department number 5
• DEPT5_PROJS(PNO) ? ? PNUMBER(? DNUM5 (PROJECT))
• EMP_PROJ(SSN, PNO) ? ? ESSN, PNO(WORKS_ON)
• RESULT_EMP_SSNS ? EMP_PROJ DEPT5_PROJS
• RESULT ? ? FNAME, LNAME (RESULT_EMP_SSNS
  ESSN SSN EMPLOYEE)
• Q5Make 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 ? ?SSN(? LNAMESmith(EMPLOYEE))
• SMITH_WORKER_PROJ ? ?PNO(WORKS_ON ESSN SSN
  SMITHS)
• MGRS ? ?LNAME, DNUMBER(EMPLOYEE
  SSNMGRSSN DEPARTMENT)
• SMITH_MANAGED_DEPTS (DNUM) ? ? DNUMBER(?
  LNAMESmith(MGRS))
• SMITH_MANAGED_PROJS (PNO) ? ? PNUMBER(SMITH_MANAGE
  D_DEPTS DNUMBER DNUM PROJECT)
• RESULT ?(SMITH_WORKER_PROJS ? SMITH_MGR_PROJS)

13
Additional Relational Operations

• Aggregate Functions and Grouping
• Number of employees in Department (derived
  attributes)
• A type of request that cannot be expressed in the
  basic relational algebra is to specify
  mathematical aggregate functions on collections
  of values from the database
• Retrieving the average or total salary of all
  employees or the total number of employee tuples
• These functions are used in simple statistical
  queries that summarize information from the
  database tuples
• Common functions applied to collections of
  numeric values include COUNT, SUM, AVERAGE,
  MAXIMUM, and MINIMUM

14
Additional Relational Operations

• Use of the Functional operator F (script f)
• FFUNCTION ATTRIBUTE (R)
• FMAX Salary (Employee) retrieves the maximum
  salary value from Employee
• FMIN Salary (Employee) retrieves the minimum
  Salary value from Employee
• FSUM Salary (Employee) retrieves the sum of the
  Salary from Employee
• Sometimes we want to get aggregate functions over
  groups of tuples (e.g. for all employees in every
  department separately)
• GROUPING_ATTRIBUTES FFUNCTION ATTRIBUTE (R)
• DNO FCOUNT SSN, AVERAGE Salary (Employee) groups
  employees by DNO (department number) and computes
  the count of employees and average salary per
  department
• Note count just counts the number of rows,
  without removing duplicates

15
R(DNO, NO_OF_EMPLOYEES, AVERAGE_SAL ) ? (DNO
FCOUNT SSN, AVERAGE Salary (Employee)) DNO
FCOUNT SSN, AVERAGE Salary (Employee) If no
names are specified for aggregates FUNCTION_ATTRI
BUTE FCOUNT SSN, AVERAGE Salary (Employee)
Get the number of employees and the average
salary in each department
Get the number of employees in the company and
the average salary of an employee
16
Examples of Relational Algebra Queries

• Q6 List the names of all employees with two or
  more dependents
• T1(SSN, NO_OF_DEPTS) ? ESSN F Count DEPDENT_NAME
  (DEPENDENT)
• T2 ? ? NO_OF_DEPS gt2 (T1)
• RESULT ? ? LNAME, FNAME (T2 SSNSSN
  EMPLOYEE)
• Q7 Retrieve the names of employees who have no
  dependents
• ALL_EMPS ? ? SSN(EMPLOYEE)
• EMPS_WITH_DEPS(SSN) ? ? ESSN(DEPENDENT)
• EMPS_WITHOUT_DEPS ? (ALL_EMPS - EMPS_WITH_DEPS)
• RESULT ? ? LNAME, FNAME (EMPS_WITHOUT_DEPS
  SSNSSN EMPLOYEE)