QUERIES

1

• QUERIES
• PART I - The Formal Approach

The Relational Algebra
2

• QUERIES

• Relational Algebra (formal mathematical approach)
• QBE ( Query By Example) - The MS Access approach
  (originally designed by Zloof at Borland for
  dBase and Paradox)
• SQL (Structured Query Language) - Typical
  mainframe approach

3

• The Relational Algebra

• a procedural query language
• we will use formal mathematical notation

4

• What do we mean by an "algebra"?

• "Ordinary" algebra works on numbers, and contains
  operators that work on numbers 24, 6-3, 95/5,
  87.
• Relational Algebra works on relations, and has
  operators which work on relations (tables).

5

• Relational Operators

• Relational operators are applied to one or two
  relations, and produce a new relation as a result
• Def A predicate is a statement which is either
  true or false, depending on the substitution for
  a variable.

6

• Definition of the Select Operator

• The SELECT operator s
• Syntax for the Select operator is s
  (relation)

predicate
7

• What does the Select operator do?

• The Select operator, s , selects tuples (rows)
  from the relation which satisfy (make TRUE) the
  specified predicate. So, we start with a given
  relation, and produce a new relation which is a
  (horizontal) subset of the old one, depending
  upon which rows are extracted from the old
  relation.

8
The STUDENT Relation

• STUID

STUNAME
MAJOR
CREDITS
151234
Jones,Ted
MGMT
47
241504
Smith, Jane
CS
101
310927
Kool, Bill
MATH
68
428541
Lan, Jackie
IS
93
529624
Witt, Stu
COE
75
629632
Kahn, Fran
CS
65
The query s (STUDENT)
STUID 241504
STUID
STUNAME
MAJOR
CREDITS
241504
Smith, Jane
CS
101
9
STUNAME
MAJOR
CREDITS

• STUID

151234
Jones,Ted
MGMT
47
241504
Smith, Jane
CS
101
310927
Kool, Bill
MATH
68
428541
Lan, Jackie
IS
93
529624
Witt, Stu
COE
75
629632
Kahn, Fran
CS
65
s (Student)
MAJOR 'CS'
STUID
STUNAME
MAJOR
CREDITS
241504
Smith, Jane
CS
101
629632
Kahn, Fran
CS
65
10

• The 'Usual' Comparisons are Allowed in the
  Predicate

• We may include the "usual" comparison operators
  ( lt , gt , , etc.) in the predicate.
• We can form "compound" predicates with an AND (
  L ) and the OR ( V ).

11

• STUID

STUNAME
MAJOR
CREDITS
151234
Jones,Ted
MGMT
47
241504
Smith, Jane
CS
101
310927
Kool, Bill
MATH
68
428541
Lan, Jackie
IS
93
529624
Witt, Stu
COE
75
629632
Kahn, Fran
CS
65
s
(Student)
((MAJOR 'CS' ) L (CREDITS lt 100))V(MAJOR'IS')
STUID
STUNAME
MAJOR
CREDITS
428541
Lan, Jackie
IS
93
629632
Kahn, Fran
CS
65
12

• The Project Operator p

• a unary operator (works on a single relation)
• produces a vertical subset of a relation,
  extracting the values of the attributes we
  specify, eliminating duplicates, and placing the
  values in a new relation

13

• The Project Operator, p

p (relation)
attribute(1),attribute(2),...attribute(n)
This will produce a list of the attribute values
given, for all entities in the specified relation.
14

• p (Student)

STUNAME, MAJOR
Query states What are the names and majors of
all students?
STUNAME
MAJOR
Jones,Ted
MGMT
Smith, Jane
CS
Kool, Bill
MATH
Lan, Jackie
IS
Witt, Stu
COE
Kahn, Fran
CS
15

• Use the Relational Algebra to write a query which
  lists the names and id's of all students who are
  IS majors

p ( s (student))
STUNAME, STUID MAJOR'IS'
The innermost (select expression) produces
STUID
STUNAME
MAJOR
CREDITS
428541
Lan, Jackie
IS
93

The "project" is applied to this relation,
yielding
STUNAME
STUID
Lan, Jackie
428541
16

• The Natural JOIN Operator in The Relational
  Algebra

• "the heart of the relational algebra"
• a binary operator
• we "join" together two tables, based on a common
  attribute (the Join attribute)
• the new relation will contain the columns of both
  tables which have been joined, and its rows will
  be the concatenation of a row from the first
  table and a row from the second table which
  matches the Join attribute

17

• The Natural Join Symbol Ä

• A ÄB means the Natural Join of relations A and B
• Note that if there is a tuple in relation A in
  which its attribute to be joined does not match
  any value of the attribute to be joined in
  relation B, it will not appear in the joined
  relation.

18
PC
EMPLOYEE

• TAGNUM

COMPID
EMPNUM
EMPNUM
EMPNAME
124
Alvarez, R.
32808
M759
611
37691
B121
124
258
Lopez, M.
57772
C007
567
567
Feinstein, B.
59836
B221
124
611
Dinh, M.
77740
M759
567
80269
C007
852
PC Ä EMPLOYEE would then be
TAGNUM
COMPID
EMPNUM
EMPNAME
32808
M759
611
Dinh. M.
37691
B121
124
Alvarez, R.
57772
C007
567
Feinstein, B.
59836
B221
124
Alvarez, R.
77740
M759
567
Feinstein, B.
19

• List the tag number and computer ID together with
  the name of the employee to whom the PC is
  assigned.

p ( PC Ä EMPLOYEE )
TAGNUM, COMPID, EMPNAME
20

• Answer to the Query

TAGNUM
COMPID
EMPNAME
32808
M759
Dinh. M.
37691
B121
Alvarez, R.
57772
C007
Feinstein, B.
59836
B221
Alvarez, R.
77740
M759
Feinstein, B.
21

• Other Types of Joins

• There are other types of Joins
• Equijoin (join attribute appears twice)
• Theta-Join (join on condition other than
  equality)
• Outer Join( rows in one relation which do not
  match any rows in the other relation on the Join
  attribute will be maintained, with null values
  for the attributes in the other relation).

22

• Two Types of Outer Joins

• Left Outer Join Displays all records from the
  left side of the join, and those records from the
  right side which match records from the left.
• Right Outer Join Displays all records from the
  right side of the join, and only those records
  from the left which have matching values from the
  right side.

23

• Some Examples With Our PC and EMPLOYEE relations

• Theta Join Display tagnums and employee names
  for PC's belonging to employees with EMPNUMs gt
  500.
• Left Outer Join of PC EMPLOYEE This will
  reveal those PC's (if any) which have not been
  assigned to Employees.
• Right Outer Join of PC EMPLOYEEThis will
  reveal those Employees (if any) who have not been
  assigned a PC.

24
PC
EMPLOYEE
TAGNUM
COMPID
EMPNUM
EMPNUM
EMPNAME
32808
M759
611
124
Alvarez, R.
37691
B121
124
258
Lopez, M.
57772
C007
567
567
Feinstein, B.
59836
B221
124
77740
M759
567
611
80269
C007
The Left Outer Join of PC and EMPLOYEE would
be
TAGNUM
COMPID
EMPNUM
EMPNAME
32808
M759
611
M. Dinh
37691
B121
124
R. Alvarez
57772
C007
567
B. Feinstein
59836
B221
124
R. Alvarez
77740
M759
567
B. Feinstein
80269
C007

25

• PC

EMPLOYEE
TAGNUM
COMPID
EMPNUM
EMPNUM
EMPNAME
32808
M759
611
124
Alvarez, R.
37691
B121
124
258
Lopez, M.
57772
C007
567
567
Feinstein, B.
59836
B221
124
77740
M759
567
611
80269
C007
The Right Outer Join of PC and EMPLOYEE would be
EMPNAME
EMPNUM
TAGNUM
COMPID
R. Alvarez
124
32808
M759
R. Alvarez
124
59836
B221
M. Lopez
258


B. Feinstein
567
57772
C007
B. Feinstein
567
77740
M759
M. Dinh
611
80269
C007


26

• Other Relational Algebra Operators

• Cartesian Product ... X
• the concatenation of every tuple (row) of one
  relation with every tuple (row) of the other
  relation.
• written as A X B
• Union Operator ... U
• a binary operator, having usual definition for
  Union A U B consists of tuples (rows) in either
  A or B or both (duplicate rows eliminated)

27

• Other Relational Algebra Operators (continued)

• Intersection Operator L
• binary operator, extract common tuples (rows)
• Difference Operator -
• A - B consists of tuples (rows) in A which are
  not it B