Announcements

1
Announcements

• Reading for Friday
• 4.7 4.10
• Homework 3 Due 10/1
• Project Steps 2.1-2?
• Due 10/4
• Exam 1 10/6

2
Relational Data Manipulation Languages

• Lecture 8

3
Types of Relational Data Manipulation Languages

• Procedural proscriptive - user tells system how
  to manipulate data - e.g. relational algebra
• Non-procedural declarative - user tells what
  data is needed, not how to get it - e.g.
  relational calculus, SQL
• Other types
• Graphical user provides illustration of data
  needed e.g. Query By Example(QBE)
• Fourth-generation 4GL uses user-friendly
  environment to generate custom applications
• Natural language 5GL accepts restricted version
  of English or other natural language

4
Relational Algebra

• Theoretical language with operators that apply to
  one or two relations to produce another relation
• Both operands and results are tables
• Can assign name to resulting table (rename)
• SELECT, PROJECT, JOIN allow many data retrieval
  operations

5
SELECT Operation

• Applied to a single table, returns rows that meet
  a specified predicate, copying them to new table
• SELECT tableName WHERE condition GIVING
  newTableName
• Symbolically, newTableName ? predicate
  (table-name)
• Result table is horizontal subset of operand

6
PROJECT Operator

• Operates on single table
• PROJECT tableName OVER (colName,...,colName)
  GIVING newTableName
• Symbolically
• newTableName ? colName,...,colName
  (tableName)

7
Combining SELECT and PROJECT

• Can compose SELECT and PROJECT, using result of
  first as argument for second

8
Product

• Binary operations apply to two tables
• Product

9
Theta-join

• THETA join

10
Equi-join and Natural Join

• EQUIJOIN
• NATURAL JOIN

11
Division

• Binary operator where entire structure of one
  table (divisor) is a portion of structure of the
  other (dividend)

12
Division Example (Club Stu)
13
Set Operations

• Tables must be union compatible
• A UNION B
• A INTERSECTION B
• A MINUS B

14
SELECT Emp WHERE lastName Adams GIVING
T1JOIN T1, Assign GIVING T2
15
Example Tables
STUDENT NAME AGE SEX
Dole 21 M
Carey 24 F Foley 28
F Mohler 31 M
Wayson 18 F Beckman
23 F
FACULTY NAME DEPT
Gold HS
Pierre FL
Volmer PH Quincy
FL Small
BL Foley CS

TRANS TNAME AGE SEX
Claremont 23 F Diamon 20
M Jameson 21 F
Adelman 19 M Mohler
31 M Gold 26 F

16
Sample Queries on the STUDENT/FACULTY/TRANS
Database

• Find the ages of all students
• Find all information on males transferees
• Find all 21 year olds who are students or
  transferees
• Find the ages of all students who have the same
  name as a faculty member.

17
Sample Queries on the STUDENT/FACULTY/TRANS
Database

• Find all possible student advisor pairs. List
  their names only.
• Find all transferees who are the same age as the
  student named Beckman.
• Find all faculty in Quincy's department.