Relation Algebra Sid Sname Rating Age 22 Dustin

Relation Algebra

Sid Sname Rating Age 22 Dustin 7 45. 0 31 Lubber 8 55. 5 58 Rusty 10 35. 5 Instance S 1 of Sailors Sid Sname Rating Age 28 Yuppy 9 35. 0 31 Lubber 8 55. 5 44 Guppy 5 35. 0 58 Rusty 10 35. 5 Instance of S 2 Sailors Sid Bid Day 22 101 10 -jun-1996 58 103 11 -dec-1996 Instance R 1 of Reserves

Notations

Rename • We introduce a renaming operator Rename (ρ) • A rename is a unary operator written as ρ x (R) • where the result is identical to R except that the b attribute in all tuples is renamed to an a attribute.

Table Rename • We have two types of renaming functions 1. Table Rename 2. Column Rename

Table Rename • SQL Syntax for renaming – Table rename EX: Rename OLD(table_name) to NEW(table_name); In Relation Algebra Ex: ρ y(x)

Table Rename: ρ y(x) EX: ρ student(Stud) ρ -> ρ denotes for renaming y-> y denotes for New table name X-> x denotes for old table name

Column Rename: SQL Syntax for rename : Syntax : alter table name rename columnname to column name Ex: alter table employee rename empid to employeeid;

Column Rename: Syntax : ρ x 1→x 2(X) ρ -> ρ denotes for renaming x 1 -> x 1 denotes for old column name X 2 -> x 2 denotes for new column name X-> X denotes for table name

Joins • Join operation is one of the most useful operation in relation algebra. • It is used to combine information from two or more relations. • Although a join can be defined as a cross – product followed by selection and projection

Syntax :

Types of Joins • Condition Join • Equi Join • Natural Join

Condition Join • The most generel version of the join operations accepts a join condition C. Syntax The Condition c can refer to the attributes R & S.

Inner join : SQL Syntax : select * from table name inner join table name 2 on tablename. collname=tablename 2. collname; Ex : select * from employee inner join department on employee. did=department. did;

Relation algebra Concepts: syntax : S 1. sid<R 1. sid R 1 Condition join Sid Sname Rating Age Sid Bid Day 22 Dustin 7 45 58 103 11 -dec-1996 31 Lubber 8 58 58 103 11 -decc-1996

Equi Join • In this case EQUI JOIN operation R S. • It denotes when the join operation consists of R. name 1=s. name 2 • If we doin this process there are some redundancy in retaining both arrtibutes in the result.

• For join condition that contains only such equalities. • The join operation is refined bu doing additional projection in which S. name 2 is dropped. • This join operation with this refinement is called equijoin.

• SQL Syntax for equi join : Select * from table name equi join table name 2; Ex : Select * from employee equi join department;

Syntax for equi join : Sid Sname Rating Age Bid Day 22 Dustin 7 45 101 10 -oct-1996 58 Rusty 10 35 103 11 -dec-1996

Natural Join: Same like as Equi Join. • In this case we can omit of join conditions. • The default is that the join condition is a collection of equalities on all common fields.

• It has the nice Property that the result is guaranteed not to have two fields with the same name. • The Equi join expression • R 1 is actually a natural join and can simply be denoted as