CS 141b Distributed Computation LaboratoryRelations and SQL - PowerPoint PPT Presentation

Title: CS 141b Distributed Computation LaboratoryRelations and SQL


1
Today

• Review from last class introduction to
  relational algebra, databases and SQL
• More relational algebra and database issues

2
Introduction to Relations and Databases

• A relation is a set of k-tuples where the jth
  element of the tuple is of some type, say type_j,
  for all j
• Note that the relation is a set
• A bag is a collection of tuples that may include
  duplicates

3
Example of a Relation

• Vertices of a graph form a binary relation
• from vertex to vertex
• Example (0, 2), (1, 2), (2, 3)
• Students in CS 141 form a relation
• (UID, student name, address, )

4
A Relation Can Be Represented As a Table
5
Projection

• Projection of a relation given a subset of its
  attributes
• Project a relation with attributes
• UID
• First name
• Major
• on to attributes
• First name
• Major

6
A Relation Can Be Represented As a Table
7
A Relation Can Be Represented As a Table
8
Projection of the Relation Is a Bag

• (Alex, CS), (Lisa, Bio), Shaun, CS)
• A bag may have duplicates
• Consider projection of the student relation on to
  the attribute major

9
Restriction of a Relation

• Restriction of a relation R given a predicate P
  on the elements of R is a relation R consisting
  of exactly those elements of R that satisfy P
• Restrict student relation to major CS

10
A Relation Can Be Represented As a Table
11
A Relation Can Be Represented As a Table
12
SQL

• Projection and restriction in SQL (not an
  acronym)
• SELECT uid, name
• FROM students
• WHERE major CS
• Semantics first restrict table, then project
• The operation may be in a different order,
  provided the semantics are satisfied

Projection
Original relation
Restriction
13
Product of a Set of Relations

• Cartesian product of relations
• Result has an attribute for each attribute of its
  component relations
• If relations R1 and R2 have M1 and M2 attributes
  respectively, then the product of R1 and R2 has ?
  attributes
• If relations R1 and R2 have N1 and N2 elements
  respectively, then the product of R1 and R2 has ?
  attributes

14
Product of a Set of Relations

• Cartesian product of relations
• Result has an attribute for each attribute of its
  component relations
• If relations R1 and R2 have M1 and M2 attributes
  respectively, then the product of R1 and R2 has
  M1 M2 attributes
• If relations R1 and R2 have N1 and N2 elements
  respectively, then the product of R1 and R2 has
  N1 N2 attributes

15
Consider Another Example Relation

• Admin

16
What Is the Product of This Table and admin?
17
Product
18
Product with Restriction and Projection

• SELECT students.name, admin.name
• FROM students JOIN admin
• ON students.dept admin.dept

Projection
Product
Restriction
19
Product with Restriction
20
Product with Restriction and Projection
21
Further Restriction

• SELECT students.name, admin.name
• FROM students JOIN admin
• ON students.dept admin.dept
• WHERE students.dept CS

Further restriction
22
Product with Further Restriction
23
Product with Further Restrictionand Projection
24
Correlation Names (Aliases)

• Use an alias in place of the actual relation with
  the syntax
• relation name AS alias
• Example
• orders AS R1

25
Example of a Product of a Relation with Itself

• Example of self join
• SELECT customer_name
• FROM orders AS R1, JOIN orders as R2
• ON R1.uid R2.uid
• WHERE R1.partNum 23 AND R2.partNum 34

26
Relations and Functions

• If there are no duplicates in values of an
  attribute B in a relation R, then we can
  represent relation R as a function from attribute
  B to k-tuples

27
UID Has No Duplicates
28
Functional Representation

• 011-91-4812 - (011-91-4812 , Alex, CS)
• 023-82-2185 - (023-82-2185, Lisa, Bio)
• 453-99-3572 - (453-99-3572, Saun, CS)

29
Key of a Table

• A column (or set of columns) in a table is
  identified as a primary key (for now dont be
  concerned about the primary qualifier)
• The bag of values (or tuples) in the primary key
  column (or columns) must not have duplicates
• Thus, a relation can be expressed as a function
  from the primary key to a set of tuples
• In the degenerate case, a primary key is the set
  of all columns of a table

30
Composition Relation Extended by Function

• Given a relation R(x, y, z) a set of 3-tuples
  with attributes of types x, y, z (i.e., a table
  with columns of domain x, y, z)
• Given a function f z - (a, b, c, d) that maps
  from z to 4-tuples (a, b, c, d)
• We can obtain a relation R(x, y, z, a, b, c, d)
  from R, with a one-to-one correspondence between
  elements of R and R, as follows
• For each element (x, y, z) in R, have an element
  (x, y, z, f(z)) in R

31
Example Function

• 011-91-4812 - (011-91-4812, Alex, CS)
• 023-82-2185 - (023-82-2185, Lisa, Bio)
• 453-99-3572 - (453-99-3572, Shaun, CS)

32
Example Relation
33
Extending the Relation with f(uid)
34
Primary Key
35
Primary Key Defines Function

• 011-91-4812 - (011-91-4812 , Alex, CS)
• 023-82-2185 - (023-82-2185, Lisa, Bio)
• 453-99-3572 - (453-99-3572, Saun, CS)

Primary key
36
Foreign Key
37
The Relational Extension

• The relational extension in SQL is a JOIN over
  the foreign key of one table and the primary key
  of another table
• For our example
• SELECT students.uid, students.name,
  students.dept, classes.class
• FROM students JOIN classes
• ON students.uid classes.uid
• primary key foreign key

38
Outer Join (As Opposed to Inner Join)

• Each element in both tables appears at least once
  in the result set, even if the element in one
  table doesnt match the element in the other

39
Recall this Example Relation Classes
40
And Recall This One
41
Recall the Inner Join
42
The Outer Join

• Which element in the first relation is missing in
  the result set shown in the last slide?
• Which element in the second relation is missing?
• So, what should the result set for the outer join
  be?

43
Outer Join Result
44
Computing with Nulls

• What do you think the following should be?
• TRUE AND NULL?
• FALSE AND NULL?
• NULL AND NULL?

45
Computing with Nulls

• What do you think the following should be?
• TRUE AND NULL NULL
• FALSE AND NULL FALSE
• NULL AND NULL NULL
• Meaning of other operators (e.g., OR) is
  self-evident

46
Self-Evident (Not Discussed in Detail Here)

• UNION of relations
• DIFFERENCE between relations
• Nested queries
• SELECT columns
• FROM attributes
• WHERE operator (SELECT (SELECT .) )

47
Subqueries Example

• SELECT names
• FROM students
• WHERE uid IN (SELECT uid
• FROM classes)

The operator IN has the obvious meaning
48
Subqueries Example

• SELECT names
• FROM students
• WHERE uid NOT IN (SELECT uid
• FROM classes)

The operator NOT IN has the obvious meaning
49
SET Functions

• COUNT, AVG, MAX, MIN
• Before we discussed statements of the form
• SELECT uid, name projection
• FROM students
• WHERE major CS
• Example notation with functions
• SELECT COUNT() not a
  projection
• FROM STUDENTS

50
Restrictions and Functions in SELECT

• The SELECT mixes two concepts restrictions and
  functions
• What would you get with
• SELECT NAME, COUNT()
• FROM STUDENTS
• projection, not a projection

51
Groups

• GROUP BY attributes organizes the result set as
  a sequence of subsets where all attributes in a
  subset are identical
• What do you think the result of this query is
• SELECT dept, COUNT()
• FROM students
• GROUP BY dept
• for the example weve been working with (next
  slide)?

52
Running Example Table Students
53
Which of These Alternatives? This?
54
Or This?
55
Yes, Its the Latter

• SELECT dept, COUNT()
• FROM students
• GROUP BY dept
• set operations
  are on groups

56
INSERT Element

• INSERT INTO table_name
• (columnList)
• VALUES
• (value_list)
• INSERT INTO students
• (uid, name, dept)
• VALUES
• (869-94-7832, Jessica, EE)

57
INSERT Element

• INSERT INTO students
• (uid, name, dept)
• VALUES
• (869-94-7832, Jessica, EE)
• Unspecified values in an INSERT are set to NULL

58
INSERT Set of Elements

• INSERT INTO table_name
• SELECT
• The relation obtained by eliminating duplicates
  in the result set of the SELECT is inserted into
  the specified table

59
Updates and Deletes

• Example
• UPDATE products
• SET price price 0.9
• WHERE sales_volume
• DELETE FROM products
• WHERE sales_volume 0

60
Referential Integrity

• For every value k of a foreign key in a table T,
  there must exist a primary key with value k in a
  table T
• So, what happens if k is deleted from table T?

61
Options to Maintain Referential Integrity

• NO ACTION disallow the deletion of the primary
  key
• CASCADE delete elements with the orphaned
  foreign key
• SET NULL
• SET DEFAULT

62
VIEWs

• CREATE VIEW cs_students (name, uid)
• AS
• SELECT name, uid
• FROM students
• WHERE dept CS
• When to use VIEWs?

63
Next Class

• Transactions
• Concurrency control