lecture 4 Relational Algebra my sql work

DATABASE SYSTEM
Relational Algebra
Lecture 5

Introduction
 Relational algebra and relational calculus are formal languages associated with the
relational model
 Informally, relational algebra is a (high-level) procedural
language and relational calculus a non-procedural language
 However, formally both are equivalent to one another
 A language that produces a relation that can be derived using
relational calculus is said to be relationally complete

Relational Algebra
 Relational algebra operations work on one or more relations to define another relation
without changing the original relations
 Both operands and results are relations, so output from one operation can become input
to another operation
 Allows expressions to be nested, just as in arithmetic is called closure property

Relational Algebra Operations
 Five basic operations in relational algebra:
 Selection, Projection, Cartesian product, Union, and Set
Difference
 These perform most of the data retrieval operations
needed
 Also have Join, Intersection, and Division operations,
which can be expressed in terms of five basic operations
 Unary vs. binary operations

Relational Algebra Operations..

Selection (Restriction)
predicate (R)
 sigma
 Works on a single relation R and defines a relation that contains only
those tuples (rows) of R that satisfy the specified condition (predicate)
 More complex predicate can be generated using the logical operators
∧ (AND),∨ (OR) and ~ (NOT)
SELECT * FROM staff
WHERE (Designation = 'manager' AND salary > 20000);

Example: Selection (Restriction)
 List all staff with a salary greater than £10,000
salary > 10000 (Staff)

Projection
 Works on a single relation R and defines a relation that contains a vertical
subset of R, extracting the values of specified attributes
 It eliminates duplicates
 Represented with letter 𝜋
 a1, a2. . . , an(R)

Example: Projection
 Produce a list of salaries for all staff, showing only
staffNo, fName, lName, and salary details
ΠstaffNo, fName, lName, salary(Staff)

Union
 R  S
 Union of two relations R and S defines a relation that contains all
the tuples of R, or S, or both R and S, duplicate tuples being
eliminated
 R and S must be union-compatible
 Num of column must be same
 Domain of any column must be same (datatype)
 If R and S have I and J tuples, respectively, union is obtained by
concatenating them into one relation with a maximum of (I + J)
tuples

Example: Union
 List all cities where there is either a branch ofﬁce
or a property for rent
Πcity(Branch) ∪ Πcity(PropertyForRent)

Set Difference
 R – S
 Defines a relation consisting of the tuples that are in relation R,
but not in S

Example: Set Difference
 List all cities where there is a branch ofﬁce but no
properties for rent
Πcity(Branch) - Πcity(PropertyForRent)

Intersection
 R  S
 Defines a relation consisting of the set of all tuples that are in both
R and S
 Expressed using basic operations:
R  S = R – (R – S)

Example: Intersection
 List all cities where there is both a branch office
and at least one property for rent
Πcity(Branch) ∩ Πcity(PropertyForRent)

Cartesian Product
 The Cartesian product operation defines a relation that is the concatenation of
every tuple of relation R with every tuple of relation S.
 Notation r x s (all possible pairing)
 Example:
 List the names and comments of all clients who have viewed a property for rent.
 The names of clients are held in the Client relation and the details of viewings are
held in the Viewing relation. To obtain the list of clients and the comments on
properties they have viewed, we need to combine these two relations:

Cartesian Product
 In its present form, this relation contains more information than we require.
 For example, the first tuple of this relation contains different clientNo values. To obtain the
required list, we need to carry out a Selection operation on this relation to extract those
tuples where
Client.clientNo = Viewing.clientNo. The complete operation is thus:

Rename Operator
 It is a unary operator (input is a single relationship)
 It is used to give the name of our choice to a new relation obtained
after using any linear algebra operation
 ρ(R,E)
 Where: ρ is used to represent rename operator
 R: new relation name that we want
 E: Relation algebra expression operation

Join Operation
 Join operation is an additional or derived operator that simplifies queries
 It is a combination of cartesian product and selection process
 Pairs two or tuples from different relations if and only if given condition is
satisfied

Natural join Example
Emp_no Emp_name Dep_no
E1 Ali khan D1
E2 asad D2
E3` Farhan D1
E4 Malik
Dep_no Dep_name Loc
D1 IT isl
D2 hr lhr
D3` accounts khr
Select emp_name from emp_info natural join dept;

Equi join
 SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the
associated tables. An equal sign (=) is used as comparison operator in the where
clause to refer equality.
 You may also perform EQUI JOIN by using JOIN keyword followed by ON keyword
and then specifying names of the columns along with their associated tables to check
equality.
SELECT column_list
FROM table1, table2....
WHERE table1.column_name =
table2.column_name;

Example
Dep_no Dep_name Loc
D1 IT isl
D2 hr lhr
D3` accounts isl
Emp_no Emp_name Address
E1 Ali khan isl
E2 asad lhr
E3` Farhan lhr
E4 Malik
Select emp_name from emp_info,dept where emp_info.Address=dept.Loc;

Left joins
 The LEFT JOIN keyword returns all records from the left table (table1), and the
matching records from the right table (table2).

Example
Dep_no Dep_name Loc
D1 IT isl
D2 hr lhr
D3` accounts khr
E1 Asad D1
E2 Ali D2
E3` Farhan D1
E4 Malik
Select emp_name , Dep_name ,Loc from emp_info left outer join dept ON emp_info.Dep_no=dept.D_NO ;
Emp_no Emp_name Dep_name Loc
E1 Asad IT Isl
E2 Ali Hr Lhr
E3 Farhan Accounts Khr
E4 Malik Null null

Right outer join
 RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on
the right side of the join and matching rows for the table on the left side of the
join. For the rows for which there is no matching row on the left side, the result-
set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.

Example
Dep_no Dep_name Loc
D1 IT isl
D2 hr lhr
D3` accounts khr
D4 Testing isl
E1 Asad D1
E2 Ali D2
E3` Farhan D1
Select emp_name , Dep_name ,Loc from emp_info right outer join dept ON
emp_info.Dep_no=dept.D_NO ;
Emp_no Emp_name Dep_name Loc
E1 Asad IT Isl
E2 Ali Hr Lhr
E3 Farhan Accounts Khr
null null testing isl

Full outer join
 The FULL OUTER JOIN keyword returns all records when there is a match in left
(table1) or right (table2) table records.

lecture 4 Relational Algebra my sql work

More Related Content

Similar to lecture 4 Relational Algebra my sql work

Recently uploaded

lecture 4 Relational Algebra my sql work