Relational Algebra and My SQL(II)

1
Relational Algebra and My SQL(II)
Lecture 6 CS157B

• Prof. Sin Min Lee
• Deparment of Computer Science
• San Jose State University

2
Lecture 12Further relational algebra, further
SQL

• www.cl.cam.ac.uk/Teaching/current/Databases/

3
Todays lecture

• Where does SQL differ from relational model?
• What are some other features of SQL?
• How can we extend the relational algebra to match
  more closely SQL?

4
Duplicate rows

• Consider our relation instances from lecture 6,
  Reserves, Sailors and Boats
• Consider
• SELECT rating,age
• FROM Sailors
• We get a relation that doesnt satisfy our
  definition of a relation!
• RECALL We have the keyword DISTINCT to remove
  duplicates

5
Multiset semantics

• A relation in SQL is really a multiset or bag,
  rather than a set as in the relational model
• A multiset has no order (unlike a list), but
  allows duplicates
• E.g. 1,2,1,3 is a bag
• select, project and join work for bags as well as
  sets
• Just work on a tuple-by-tuple basis

6
Bag operations

• Bag union
• Sum the number of times that an element appears
  in the two bags, e.g.
• 1,2,1?1,2,3 1,1,1,2,2,3
• Bag intersection
• Take the minimum of the number of occurrences in
  each bag, e.g.
• 1,2,1?1,2,3,3 1,2
• Bag difference
• Proper-subtract the number of occurrences in the
  two bags, e.g.
• 1,2,1-1,2,3,3 1

7
Laws for bags

• Note that whilst some of the familiar
  (set-theoretic) laws continue to hold, some of
  them do not
• Example R?(S?T) (R?S)?(R?T) ??

8
Extended relational algebra

• Add features needed for SQL
• Bag semantics
• Duplicate elimination operator, ?
• Sorting operator, ?
• Grouping and aggregation operator, ?
• Outerjoin operators, oV, Vo, oVo

9
Duplicate-elimination operator

• ?(R) relation R with any duplicated tuples
  removed
• R ?(R)
• This is used to model the DISTINCT feature of SQL

A B
1 2
3 4
1 2
A B
1 2
3 4
10
Sorting

• ?L1, Ln(R) returns a list of tuples of R,
  ordered according to the attributes L1, , Ln
• Note ? does not return a relation
• R ?B(R)
  (5,2),(1,3),(3,4)
• ORDER BY in SQL, e.g.
  
• SELECT
• FROM Sailors
• WHERE ratinggt7
• ORDER BY age, sname

A B
1 3
3 4
5 2
11
Extended projection

• SQL allows us to use arithmetic operators
• SELECT age5
• FROM Sailors
• We extend the projection operator to allow the
  columns in the projection to be functions of one
  or more columns in the argument relation, e.g.
• R ?AB,A,A(R)

AB A.1 A.2
3 1 1
7 3 3
A B
1 2
3 4
12
Arithmetic

• Arithmetic (and other expressions) can not be
  used at the top level
• i.e. 22 is not a valid SQL query
• How would you get SQL to compute 22?

13
Aggregation

• SQL provides us with operations to summarise a
  column in some way, e.g.
• SELECT COUNT(rating)
• FROM Sailors
• SELECT COUNT(DISTINCT rating)
• FROM Sailors
• SELECT COUNT()
• FROM Sailors WHERE ratinggt7
• We also have SUM, AVG, MIN and MAX

14
Grouping

• These aggregation operators have been applied to
  all qualifying tuples. Sometimes we want to apply
  them to each of several groups of tuples, e.g.
• For each rating, find the average age of the
  sailors
• For each rating, find the age of the youngest
  sailor

15
GROUP BY in SQL

• SELECT DISTINCT target-list
• FROM relation-list
• WHERE qualification
• GROUP BY grouping-list
• The target-list contains
• List of column names
• Aggregate terms
• NOTE The variables in target-list must be
  contained in grouping-list

16
GROUP BY cont.

• For each rating, find the average age of the
  sailors
• SELECT rating,AVG(age)
• FROM Sailors
• GROUP BY rating
• For each rating find the age of the youngest
  sailor
• SELECT rating,MIN(age)
• FROM Sailors
• GROUP BY rating

17
Grouping and aggregation

• ?L(R) where L is a list of elements that are
  either
• Individual column names (Grouping attributes),
  or
• Of the form ?(A), where ? is an aggregation
  operator (MIN, SUM, ) and A is the column it is
  applied to
• For example,
• ?rating,AVG(age)(Sailors)

18
Semantics

• Group R according to the grouping attributes
• Within each group, compute ?(A)
• Result is the relation consisting of one tuple
  for each group. The components of that tuple are
  the values associated with each element of L for
  that group

19
Example
bar beer price
Anchor 6X 2.50
Anchor Adnams 2.40
Mill 6X 2.60
Mill Fosters 2.80
Eagle Fosters 2.90

• Let R
• Compute ?beer,AVG(price)(R)

20
Example cont.

1. Group according to the grouping attribute, beer
2. Compute average
   of price within groups

bar beer price
Anchor 6X 2.50
Mill 6X 2.60
Anchor Adnams 2.40
Mill Fosters 2.80
Eagle Fosters 2.90
beer price
6X 2.55
Adnams 2.40
Fosters 2.85
21
NULL values

• Sometimes field values are unknown (e.g. rating
  not known yet), or inapplicable (e.g. no spouse
  name)
• SQL provides a special value, NULL, for both
  these situations
• This complicates several issues
• Special operators needed to check for NULL
• Is NULLgt8? Is (NULL OR TRUE)TRUE?
• We need a three-valued logic
• Need to carefully re-define semantics

22
NULL values

• Consider
• INSERT INTO Sailors (sid,sname)
• VALUES (101,Julia)
• SELECT FROM Sailors
• SELECT rating FROM Sailors
• SELECT sname
• FROM Sailors
• WHERE ratinggt0

23
Entity integrity constraint

• An entity integrity constraint states that no
  primary key value can be NULL

24
Outer join

• Note that with the usual join, a tuple that
  doesnt join with any from the other relation
  is removed from the resulting relation
• Instead, we can pad out the columns with NULLs
• This operator is called an full outer join,
  written oVo

25
Example of full outer join
B C
4 5
6 7
A B
1 2
3 4

• Let R Let S
• Then RVS
• But RoVoS

A B C
3 4 5
A B C
1 2 NULL
3 4 5
NULL 6 7
26
Outer joins in SQL

• SQL/92 has three variants
• LEFT OUTER JOIN (algebra oV)
• RIGHT OUTER JOIN (algebra Vo)
• FULL OUTER JOIN (algebra oVo)
• For example
• SELECT FROM Reserves r LEFT OUTER JOIN
  Sailors s ON r.sids.sid

27
Views

• A view is a query with a name that can be used in
  further SELECT statements, e.g.
• CREATE VIEW ExpertSailors(sid,sname,age)
• AS SELECT sid,sname,age
• FROM Sailors
• WHERE ratinggt9
• Note that ExpertSailors is not a stored relation
• (WARNING mysql does not support views ?)

28
Querying views

• So an example query
• SELECT sname
• FROM ExpertSailors
• WHERE agegt27
• is translated by the system to the following
• SELECT sname
• FROM Sailors
• WHERE ratinggt9 AND agegt27

29
Relational Algebra

• The Relational Algebra is used to define the ways
  in which relations (tables) can be operated to
  manipulate their data.
• It is used as the basis of SQL for relational
  databases, and illustrates the basic operations
  required of any DML.
• This Algebra is composed of Unary operations
  (involving a single table) and Binary operations
  (involving multiple tables).

30
SQL

• Structured Query Language (SQL)
• Standardised by ANSI
• Supported by modern RDBMSs
• Commands fall into three groups
• Data Definition Language (DLL)
• Create tables, etc
• Data Manipulation Language (DML)
• Retrieve and modify data
• Data Control Language
• Control what users can do grant and revoke
  privileges

31
Unary OperationsSelectionProjection
32
Selection

• The selection or ? operation selects rows from a
  table that satisfy a condition
•   ? lt condition gt lt tablename gt
• Example ? course CM Students
• Students
• stud name course
• 100 Fred PH stud name course
• 200 Dave CM 200 Dave CM
• 300 Bob CM 300 Bob CM

33
Projection

• The projection or ? operation selects a list of
  columns from a table.
• ? lt column list gt lt tablename gt
• Example ? stud, name Students
• Students
• stud name course stud name
• 100 Fred PH 100 Fred
• 200 Dave CM 200 Dave
• 300 Bob CM 300 Bob

34
Selection / Projection

• Selection and Projection are usually combined
• ? stud, name (? course CM Students)
• Students
• stud name course
• 100 Fred PH stud name
• 200 Dave CM 200 Dave
• 300 Bob CM 300 Bob

35
Binary OperationsCartesian ProductTheta
JoinInner JoinNatural JoinOuter JoinsSemi
Joins
36
Cartesian Product

• Concatenation of every row in the first relation
  (R) with every row in the second relation (S)
• R X S

37
Cartesian Product - Example

• Students Courses
• stud name course course name
• 100 Fred PH PH Pharmacy
• 200 Dave CM CM Computing
• 300 Bob CM
• Students X Courses
• stud Students.name course course Courses.name
• 100 Fred PH PH Pharmacy
• 100 Fred PH CM Computing
• 200 Dave CM PH Pharmacy
• 200 Dave CM CM Computing
• 300 Bob CM PH Pharmacy
• 300 Bob CM CM Computing

38
Theta Join

• A Cartesian product with a condition applied
• R ? ltconditiongt S

39
Theta Join - Example

• Students Courses
• stud name course course name
• 100 Fred PH PH Pharmacy
• 200 Dave CM CM Computing
• 300 Bob CM
• Students ? stud 200 Courses
• stud Students.name course course Courses.name
• 200 Dave CM PH Pharmacy
• 200 Dave CM CM Computing

40
Inner Join (Equijoin)

• A Theta join where the ltconditiongt is the match
  () of the primary and foreign keys.
• R ? ltR.primary_key S.foreign_keygt S

41
Inner Join - Example

• Students Courses
• stud name course course name
• 100 Fred PH PH Pharmacy
• 200 Dave CM CM Computing
• 300 Bob CM
• Students ? course course Courses
• stud Students.name course course Courses.name
• 100 Fred PH PH Pharmacy
• 200 Dave CM CM Computing
• 300 Bob CM CM Computing

42
Natural Join

• Inner join produces redundant data (in the
  previous example course and course). To get rid
  of this duplication
• ? lt stud, Students.name, course, Courses.name gt
  
• (Students ? ltcourse coursegt Courses)
• Or
• R1 Students ? ltcourse coursegt Courses
• R2 ? lt stud, Students.name, course,
  Courses.name gt R1
• The result is called the natural join of Students
  and Courses

43
Natural Join - Example

• Students Courses
• stud name course course name
• 100 Fred PH PH Pharmacy
• 200 Dave CM CM Computing
• Bob CM
• R1 Students ? ltcourse coursegt Courses
• R2 ? lt stud, Students.name, course,
  Courses.name gt R1
• stud Students.name course Courses.name
• 100 Fred PH Pharmacy
• 200 Dave CM Computing
• 300 Bob CM Computing

44
Outer Joins

• Inner join rows of one table which do not
  satisfy the ltconditiongt.
• Left Outer Join R ltR.primary_key
  S.foreign_keygt S
• All rows from R are retained and unmatched rows
  of S are
• padded with NULL
• Right Outer Join R ltR.primary_key
  S.foreign_keygt S
• All rows from S are retained and unmatched rows
  of R are
• padded with NULL

45
Left Outer Join - Example

• Students Courses
• stud name course course name
• 100 Fred PH PH Pharmacy
• 200 Dave CM CM Computing
• 400 Peter EN CH Chemistry
• Students ltcourse coursegt Courses
• stud Students.name course course Courses.name
• 100 Fred PH PH Pharmacy
• 200 Dave CM CM Computing
• Peter EN NULL NULL

46
Right Outer Join - Example

• Students Courses
• stud name course course name
• 100 Fred PH PH Pharmacy
• 200 Dave CM CM Computing
• 400 Peter EN CH Chemistry
• Students ltcourse coursegt Courses
• stud Students.name course course Courses.name
• 100 Fred PH PH Pharmacy
• 200 Dave CM CM Computing
• NULL NULL NULL CH Chemistry

47
Combination of Unary and Join Operations

• Students Courses
• stud name address course course name
• 100 Fred Aberdeen PH PH Pharmacy
• 200 Dave Dundee CM CM Computing
• 300 Bob Aberdeen CM
• Show the names of students (from Aberdeen) and
  the names of their courses
• R1 Students ? ltcoursecoursegt Courses
• R2 ? ltaddressAberdeengt R1
• R3 ? ltStudents.name, Course.namegt R2

Students.name Courses.name Fred Pharmacy Bob Com
puting
48
Set Operations

• Union
• Intersection
• Difference

49
Union

• Takes the set of rows in each table and combines
  them, eliminating duplicates
• Participating relations must be compatible, ie
  have the same number of columns, and the same
  column names, domains, and data types
• R S R ? S

A B a2 b2 a3 b3
A B a1 b1 a2 b2 a3 b3
A B a1 b1 a2 b2
50
Intersection

• Takes the set of rows that are common to each
  relation
• Participating relations must be compatible
• R S R ? S

A B a1 b1 a2 b2
A B a2 b2 a3 b3
A B a2 b2
51
Difference

• Takes the set of rows in the first relation but
  not the second
• Participating relations must be compatible
• R S R - S

A B a1 b1 a2 b2
A B a2 b2 a3 b3
A B a1 b1
52
Exercise (May 2004 Exam)

• Employee WorkLoad Project
• empid name empid projid duration projid name
• E100 Fred E100 P001 17 P001 DB
• E200 Dave E200 P001 12 P002 Access
• E300 Bob E300 P002 15 P003 SQL
• E400 Peter
• Determine the outcome of the following
  operations
• A natural join between Employee and WorkLoad
• A left outer join between Employee and WorkLoad
• A right outer join between WorkLoad and Project

53
Relational Algebra Operations written in SQL
54
Unary Operations

• Selection
• ? course Computing Students
• In SQL
• Select
• From Students
• Where course Computing

Projection ? stud, name Students In
SQL Select stud, name From Students
Selection Projection ? stud, name (? course
Computing Students) In SQL Select stud,
name From students Where course Computing

55
Binary Operations/Joins

• Cartesian Product Students X Courses
• In SQL
• Select
• From Students, Courses

Theta Join Students ? ltstud 200gt Courses In
SQL Select From Students, Courses Where
stud 200
56
Binary Operations/Joins

• Inner Join (Equijoin) Students ?
  ltcoursecoursegt Courses
• In SQL
• Select
• From Students, Courses
• Where coursecourse

Natural Join R1 Students ? ltcourse coursegt
Courses R2 ? lt stud, Students.name, course,
Courses.name gt R1 In SQL Select stud,
Students.name, course, Courses.name From
Students, Courses Where coursecourse
57
Outer Joins

• Left Outer Join
• Students ltcourse coursegt Courses
• In SQL
• Select
• From Students, Courses
• Where course course()

Right Outer Join Students ltcourse coursegt
Courses In SQL Select From Students,
Courses Where course() course
58
Combination of Unary and Join Operations

• R1 Students ? ltcoursecoursegt Courses
• R2 ? ltaddressAberdeengt R1
• R3 ? ltStudents.name, Course.namegt R2
• In SQL
• Select Students.name, Courses.name
• From Students, Courses
• Where coursecourse
• AND addressAberdeen

59
Set Operations

• Union R ? S
• In SQL
• Select From R
• Union
• Select From S

Intersection R ? S In SQL Select From
R Intersect Select From S
Difference R - S In SQL Select From
R Minus Select From S
60
SQL Operators

• Between, In, Like, Not

61
SQL Operators

• SELECT
• FROM Book
• WHERE catno BETWEEN 200 AND 400
• SELECT
• FROM Product
• WHERE prod_desc BETWEEN C AND S
• SELECT
• FROM Book
• WHERE catno NOT BETWEEN 200 AND 400

62
SQL Operators

• SELECT Catno
• FROM Loan
• WHERE Date-Returned IS NULL
• SELECT Catno
• FROM Loan
• WHERE Date-Returned IS NOT NULL

63
SQL Operators

• SELECT Name
• FROM Member
• WHERE memno IN (100, 200, 300, 400)
• SELECT Name
• FROM Member
• WHERE memno NOT IN (100, 200, 300, 400)

64
SQL Operators

• SELECT Name
• FROM Member
• WHERE address NOT LIKE Aberdeen
• SELECT Name
• FROM Member
• WHERE Name LIKE _ES
• Note In MS Access, use and instead of and _

65
Selecting Distinct Values

• Student
• stud name address
• 100 Fred Aberdeen
• 200 Dave Dundee
• 300 Bob Aberdeen
• SELECT Distinct address
• FROM Student
• address
• Aberdeen
• Dundee

66
Exercise

• Employee(empid, name)
• Project(projid, name)
• WorkLoad(empid, projid, duration)
• List the names of employees working on project
• name Databases.

67
Nested Subqueries Use of IN

• SELECT property
• FROM PropertyForRent
• WHERE staff IN( staffs who works
• at branch on 112 A St)

Since there are more than one row selected,
cannot be used.
Source Database Systems Connolly/Begg
68
Use of ANY/SOME

• SELECT name, salary
• FROM Staff
• WHERE salary gt SOME( SELECT salary
• FROM Staff
• WHERE branch A )

Result list of staff with salary greater than
2000.
Result2000,3000,4000
Source Database Systems Connolly/Begg
69
Use of ALL

• SELECT name, salary
• FROM Staff
• WHERE salary gt ALL( SELECT salary
• FROM Staff
• WHERE branch A )

Result list of staff with salary greater than
4000.
Result2000,3000,4000
Source Database Systems Connolly/Begg
70
Use of Any/Some and All

• If the subquery is empty
• ALL returns true
• ANY returns false
• ISO standard allows SOME to be
• used interchangeably with ANY.

Source Database Systems Connolly/Begg
71
Natural Join
4
SELECT a.comcol, a.col1, b.col2, expr1, expr2
FROM table1 a, table2 b WHERE a.comcol
b.comcol
72
Natural Join
4
eg. 25 Make a list of students and the
instruments they learn. (Natural Join)
73
Natural Join
4
eg. 25 Make a list of students and the
instruments they learn. (Natural Join)
SELECT s.class, s.name, s.id, m.type FROM
student s, music m WHERE s.idm.id ORDER BY
class, name
74
Natural Join
4
eg. 26 Find the number of students learning
piano in each class.
Three Parts (1) Natural Join. (2) Condition
m.type"Piano" (3) GROUP BY class
75
Natural Join
4
eg. 26
76
Natural Join
4
eg. 26 Find the number of students learning
piano in each class.
SELECT s.class, COUNT() FROM student s,
music m WHERE s.idm.id AND m.type"Piano"
GROUP BY class ORDER BY class
77
Outer Join
4
78
Outer Join
4
eg. 27 List the students who have not yet chosen
an instrument. (No match)
79
Outer Join
4
eg. 27 List the students who have not yet chosen
an instrument. (No match)

• SELECT class, name, id FROM student
• WHERE id NOT IN ( SELECT id FROM music )
• ORDER BY class, name

80
Outer Join
4
eg. 28 Make a checking list of students and the
instruments they learn. The list should also
contain the students without an
instrument. (Outer Join)
81
Outer Join
4
eg. 28
82
Outer Join
4
eg. 28
SELECT s.class, s.name, s.id, m.type FROM
student s, music m WHERE s.idm.id
UNION SELECT class, name, id, "" FROM
student WHERE id NOT IN ( SELECT id FROM
music ) ORDER BY 1, 2
83
Outer Join
4
84
Multi-Table Queries

• Join
• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join

Source Database Systems Connolly/Begg
85
Join

• SELECT client
• FROM Client c, View v
• WHERE c.client v.client

ISO standard Alternatives
FROM Client c JOIN View v ON c.client
v.client (creates two identical client
columns) FROM Client JOIN View USING
client FROM Client NATURAL JOIN View
Source Database Systems Connolly/Begg
86
Join

• The join operation combines data from two tables
  by forming pairs of related rows where the
  matching columns in each table have the same
  value.
• If one row of a table is unmatched, the row is
  omitted from the resulting table.

Source Database Systems Connolly/Begg