1 cse 480: database systems lecture 15: relational algebra reference: read chapter 6.1-6.3.1 of the...
TRANSCRIPT
![Page 1: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/1.jpg)
1
CSE 480: Database Systems
Lecture 15: Relational Algebra
Reference:
Read Chapter 6.1-6.3.1 of the textbook
![Page 2: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/2.jpg)
2
What is Relational Algebra?
A formal way to express a query in relational model– A query consists of relational expressions describing the
sequence of operators applied to obtain the query result
Example :
LName, Sex ( DNo=4 OR Salary>100000 (EMPLOYEEEMPLOYEE) )
– and are the operators
![Page 3: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/3.jpg)
3
Why Study Relational Algebra?
SELECT A.Name, B.GradeFROM A, BWHERE A.Id = B.Id
Name, Grade (Id,Name(A B))
Query processing in DBMS
![Page 4: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/4.jpg)
4
Relational Algebra Operator
Output of a relational algebra expression is a relation
Types of operators in a relational algebra expression– Unary: op(Relation)
– Binary: Relation op Relation
Relational algebra operators– select, project, union, set difference, Cartesian product
![Page 5: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/5.jpg)
5
Select Operator ()
condition (R) (NOT THE SAME AS SELECT in SQL)
Resulting relation – contains only tuples in R that satisfy the given condition
– has the same attributes (columns) as the original relation
Example:
Student Student Status=‘Junior’(StudentStudent)
1123 John 123 Main Junior1234 Lee 123 Main Senior5556 Mary 7 Lake Dr Freshman9876 Bart 5 Pine St Junior
1123 John 123 Main Junior9876 Bart 5 Pine St Junior
Id Name Address Status Id Name Address Status
![Page 6: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/6.jpg)
6
Selection Condition
select_condition (relation)– Selection condition acts as a filter to the rows in relation
Selection condition is a Boolean expression– Simple selection condition:
<attribute> operator <constant> <attribute> operator <attribute>
where operator: <, , , >, =,
– <condition> AND <condition>– <condition> OR <condition>– NOT <condition>
![Page 7: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/7.jpg)
7
Selection Condition - Examples
Id>3000 OR Status=‘Freshman’ (Student) selectivity = 2/4
Id>3000 AND Id <3999 (Student) selectivity = 0/4
NOT(Status=‘Senior’) (Student) selectivity = 3/4
Status‘Senior’ (Student) selectivity = 3/4
Student
1123 John 123 Main Junior1234 Lee 123 Main Senior5556 Mary 7 Lake Dr Freshman9876 Bart 5 Pine St Junior
Id Name Address StatusSelectivity
Fraction of tuples selected by a selection condition
![Page 8: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/8.jpg)
8
Selection Condition – Incorrect Queries
Find professors who taught both CS315 and CS305– CrsCode=‘CS315’ AND CrsCode=‘CS305’ (TEACHING) X
Find courses taught by CS professors– PROFESSOR.Id=Teaching.ProfId AND PROFESSOR.DeptId=‘CS’ (TEACHING) X
![Page 9: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/9.jpg)
9
Properties of SELECT operator
Commutative:– <condition1>( < condition2> (R)) = <condition2> ( < condition1> (R))
Cascade of SELECT operations may be applied in any order:– <cond1>(<cond2> (<cond3> (R)) = <cond2> (<cond3> (<cond1> ( R)))
Cascade of SELECT operations may be replaced by a single selection with a conjunction of all the conditions:– <cond1>(< cond2> (<cond3>(R)) = <cond1> AND < cond2> AND < cond3>(R)))
![Page 10: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/10.jpg)
10
Project Operator
Produces a relation containing a subset of columns of its input relation
– attribute list(R)
Example:
Student
Name,Status(Student)
1123 John 123 Main Junior1234 Lee 123 Main Freshman5556 Mary 7 Lake Dr Senior9876 Bart 5 Pine St Junior
John JuniorLee FreshmanMary SeniorBart Junior
Id Name Address Status Name Status
![Page 11: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/11.jpg)
11
Project Operator
Resulting relation has no duplicates; therefore it can have fewer tuples than the input relation
Example:
StudentStudent Address(StudentStudent)
123 Main7 Lake Dr5 Pine St
Address
1123 John 123 Main Junior1234 Lee 123 Main Freshman5556 Mary 7 Lake Dr Senior9876 Bart 5 Pine St Junior
Id Name Address Status
![Page 12: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/12.jpg)
12
Properties of PROJECT Operator
Number of tuples in the result of projection <list>(R) is always less or equal to the number of tuples in R– If list of projected attributes includes a superkey, the resulting
relation has the same number of tuples as the input relation
PROJECT operator is not commutative– <list1> ( <list2> (R) ) <list2> ( <list1> (R) )
![Page 13: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/13.jpg)
13
Relational Algebra Expressions
Find the Ids and names of junior undergraduate students
Id, Name ( Status=’Junior’ (StudentStudent) )
1123 John9876 Bart
ResultResult
Id Name
StudentStudent
1123 John 123 Main Junior1234 Lee 123 Main Freshman5556 Mary 7 Lake Dr Senior9876 Bart 5 Pine St Junior
Id Name Address Status
![Page 14: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/14.jpg)
14
Relational Algebra Expressions
We may want to apply several relational algebra operations one after the other
– We can write the operations as a single relational algebra expression by nesting the operations, or
– We can apply one operation at a time and create intermediate result relations.
– In the latter case, we must give names to the relations that hold the intermediate results.
![Page 15: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/15.jpg)
15
Single versus Sequence of operations
Query: Find the first name, last name, and salary of all employees who work in department number 5
We can write a single relational algebra expression: – FNAME, LNAME, SALARY( DNO=5(EMPLOYEE))
OR We can explicitly show the sequence of operations, giving a name to each intermediate relation:– DEP5_EMPS DNO=5(EMPLOYEE)
– RESULT FNAME, LNAME, SALARY (DEP5_EMPS)
![Page 16: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/16.jpg)
16
RENAME operator
The RENAME operator is denoted by
We may rename the attributes of a relation or the relation name or both– S (B1, B2, …, Bn )(R)
Change the relation name from R to S, and Change column (attribute) names to B1, B2, …..Bn
– S(R) : Change the relation name only to S
– (B1, B2, …, Bn )(R) : Change the column (attribute) names only to B1, B2, …..Bn
![Page 17: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/17.jpg)
17
Example
Change relation name to Emp and attributes to First_name, Last_name, and Salary
Emp(First_name, Last_name, Salary ) ( FNAME,LNAME,SALARY (EMPLOYEE))
or
Emp(First_name, Last_name, Salary) FNAME,LNAME,SALARY (EMPLOYEE)
![Page 18: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/18.jpg)
18
Set Operators
A relation is a set of tuples; therefore set operations are applicable: – Intersection:
– Union: – Set difference (Minus):
Set operators are binary operators– Operator: Relation Relation Relation
Result of set operation is a relation that has the same schema as the combining relations
![Page 19: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/19.jpg)
19
Examples: Set Operations
A B
x1 x2
x3 x4
A B
x1 x2
x5 x6
X Y X Y X – Y
X Y
A B
x1 x2
A B
x1 x2
x3 x4
x5 x6
A B
x3 x4
![Page 20: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/20.jpg)
20
Example
STUDENT
INSTRUCTOR
STUDENT
INSTRUCTOR
STUDENT – INSTRUCTOR
INSTRUCTOR – STUDENT
![Page 21: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/21.jpg)
21
Union Compatible Relations
Set operations are limited to union compatible relationsunion compatible relations– Two relations are union compatible if:
they have the same number of attributes, and the domains of corresponding attributes are type compatible (i.e.
dom(Ai)=dom(Bi) for i=1, 2, ..., n).
The resulting relation has the same attribute names as the first operand relation
![Page 22: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/22.jpg)
22
Example
Tables: StudentStudent (SSN, Name, Address, Status) ProfessorProfessor (Id, Name, Office, Phone)are not union compatible.
But
Name (StudentStudent) and Name (ProfessorProfessor)
are union compatible
![Page 23: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/23.jpg)
23
Exercise
Relations:– EmployeeEmployee (SSN, Name, Address)
– ProfessorProfessor (SSN, Office, Phone)
– StudentStudent (SSN, Status)
Find the SSN of student employees SSN (EmployeeEmployee) SSN (StudentStudent)
Find the SSN of employees who are not professors SSN (EmployeeEmployee) – SSN (ProfessorProfessor)
Find the SSN of employees who are neither professors nor students
SSN (EmployeeEmployee) – ( SSN (StudentStudent) SSN (ProfessorProfessor))
![Page 24: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/24.jpg)
24
Cartesian Product
R S is expensive to compute:– Number of columns = degree(R) + degree(S)
– Number of rows = number of rows (R) × number of rows (S)
A B C D A B C D x1 x2 y1 y2 x1 x2 y1 y2 x3 x4 y3 y4 x1 x2 y3 y4 x3 x4 y1 y2 RR SS x3 x4 y3 y4 RR SS
![Page 25: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/25.jpg)
25
Example
BrokerBroker (BrokerId, BrokerName)
ClientClient (ClientId, ClientName)
List all the possible Broker-Client pairs– Broker Client
BrokerID BrokerName
1 Merrill Lynch
2 Morgan Stanley
3 Salomon Smith Barney
ClientId ClientName
A11 Bill Gates
B11 Steve JobsBrokerId BrokerName ClientId ClientName
1 Merrill Lynch A11 Bill Gates
1 Merrill Lynch B11 Steve Jobs
2 Morgan Stanley A11 Bill Gates
2 Morgan Stanley B11 Steve Jobs
3 Salomon Smith Barney A11 Bill Gates
3 Salomon Smith Barney B11 Steve Jobs
![Page 26: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/26.jpg)
26
More Complex Examples
Find the names of employees who are not supervisors
EmployeeNonsupAnswer
EmployeeEmployeeNonsup
.Employee.Nonsup,,
SSNSSNLnameMInitFname
SuperSSNSSN
![Page 27: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/27.jpg)
27
Example
Find the names of employees who earn more than their supervisors
EmpEmployeeAnswer
Employee)Sal,SSN1(Emp
SalSalary AND SSN1SuperSSN,
,
LnameFname
SalarySSN
![Page 28: 1 CSE 480: Database Systems Lecture 15: Relational Algebra Reference: Read Chapter 6.1-6.3.1 of the textbook](https://reader030.vdocuments.us/reader030/viewer/2022032607/56649eda5503460f94be9564/html5/thumbnails/28.jpg)
28
Summary (Relational Algebra Operators)
Unary operators– SELECT condition(R)
– PROJECT Attribute-List(R)
– RENAME S(A1,A2,…Ak)(R)
Set operators– R S
– R S
– R – S or S – R
Cartesian product– R S