Relational Algebra

Relational Algebra

Submitted By: Waheed Imtiaz

13-arid-222

Submitted To: Munir Ahmad

Dated : 04-11-2013

Select (π) Projection

Where () Selection

Union (∪)

Setdifference (-)

Cartesianproduct ()

Set intersection (∩)

Join ( )• Operators

In computer scince, relational algebra is an offshoot of first-order logic and of algebra Of sets concerned with operations over finitar relations, usually made more convenient to work with by identifying the components of a tuples by a name (called attribute) rather than by a numeric column index, which is called a relation in database terminology

Relational Algebra

PROJECTION(π)

Id P.Name Age Address

1 M.Hafeez 30 Faisal Abad

2 Afridi 33 Karachi

3 Misbah 39 Faisal Abad

4 Abdul Razzaq 33 Lahore

5 Malik 32 Sialkot

Pak

P.Id

PL.Name Age Address

1 M.Hafeez 30 Faisalabad

2 Umar 26 Lahore

3 Kamran 29 Lahore


5 Ahmad 24 Lahore

Lions

SQL Querry :

select * from PakRelational Algebra Querry :

π* (Pak)



2 Afridi 33 Karachi



ResultPak

Assuming that we want to retrieve All records From Table Pak then:

SELECTION( ᵟ )



2 Afridi 33 Karachi



5 Malik 32 Sialkot

P.Id

PL.Name Age Address


2 Umar 26 Lahore

3 Kamran 29 Lahore


5 Ahmad 24 Lahore

Pak

Lions

SQL Querry :

Select * from Lions where Address=’Lahore’

Relational Algebra Querry :

π * ᵟAddress=’Lahore’ (Lions)

P.Id PL.Name Age

2 Umar 26

3 Kamran 29

4 Abdul Razzaq 33

5 Ahmad 24

ResultLions

Assumin that if we want to retrieve data from Tables Lions of those players who belongs to Lahore then:

Union(U)

Id P.Name Age Address Category

1 M.Hafeez 30 Faisal Abad All

2 Umar 26 Lahore Bat

3 Akmal 29 Lahore WK

4 Abdul Razzaq 33 Lahore All

5 Afridi 33 Karachi All

6 Malik 32 Sialkot All

7 Misbah 39 Faisal Abad Bat

Pak



2 Umar 26 Lahore

3 Kamran 29 Lahore


5 Ahmad 24 Lahore

Lions

SQL Querry :

select Age from Pak Union select Age from Lions.

Relational Algebra Querry :

π Age (Pak) U π Age(Lions)Result

Age

302629333239

Result

Assuming that if we want to retrieve the age of players from both tables and same data retrieve only one time then:

INTERSECTION(∩)

Id F_Nmae L_Name Num City

201 John Corter 3241 Johansberg

202 William Philip 4543 California

203 Harry Porter 6543 Washington

204 Waqar Baig 3242 Palandari

205 RJ Asif 4312 Los Angelus

R1

R2Id F_Name L_Name Num City

222 Vin Diesel 5432 Los Angelus

223 Bruce Wills 6543 California

224 Jacky Chan 8765 Shinghai


205 RJ Sajan 4312 Los Angelus

SQL Query :

select City from R1 intersect select City from R2

Relational Algebra Query :

π City (R1) ∩ π City (R2)

City

California

Palandari

Los Angelus

Result

Assuming that we want to retrieve those city names which are exist in both columns then:

Set difference (-)

201 John Corter 3241 Johansberg

202 William Philip 4543 California

203 Harry Porter 6543 Washington


205 RJ Asif 4312 Los Angelus

R1

Id F_Name L_Name Num City

222 Vin Diesel 5432 Los Angelus

223 Bruce Wills 6543 California

224 Jacky Chan 8765 Shinghai


205 RJ Sajan 4312 Los Angelus

R2

SQL Query :

select Id from R2 not exist in (select Id from R1)

Relational Algebra Query :

π Id (R2) - π Id (R1)

Id

222

223

224

Result

Assuming that we want retrieve Ids from Table R2 which are does not exist in table R1 then:

S-id Name Age

1 Waheed 21

2 Ali 24

3 Manan 22

Cartesianproduct(x)

MIT

Cid Title CreditHr

705 DDS 3

706 OS 3

707 OOP 4

Course

SQL Query :

Select * from student,courseRelational Algebra Query :

π* (Student*Course)

S-id Name Age Cid Title CreditHr

1 Waheed 21 705 DDS 3

1 Waheed 21 706 OS 3

1 Waheed 21 707 OOP 4

2 Ali 24 705 DDS 32 Ali 24 706 OS 32 Ali 24 707 OOP 43 Manan 22 705 DDS 33 Manan 22 706 OS 33 Manan 22 707 OOP 4

Result

Asssuming that we want to retrieve the all corses which are registered by all students:

JOIN

Cus_id Cus_Name City Contact

101 Ali Rawalpindi 345678

102 Saleem Multan 678534

103 Waseem Islamabad 543216

104 Rizwan Rahim Yar Khan 675439

105 Ijlal Islamabad 876549

Customer

Ord_id Cus_id Order Date

35 102 27-Aug-2010

37 104 28-Aug-2010

38 105 29-Aug-2010

40 - 29-Aug-2010

Orders

SQl Query :select customer.cus_name,orders.ord_id from customer join orders on customer.cust_id=orders.cust_id

Relational Algebra Query:

πcustomer.cus_name,orders.ord_id(Customer

customer.cus_id=orders.cus_idOrders)

Cus_Name Ord_id

Saleem 35

Rizwan 37

Ijlal 38

Result

Assuming that we want to Retrieve customer and order id who’s id matches

SQl Query :select customer.cus_name,orders.ord_id from customer Right join orders on customer.cust_id=orders.cust_idRelational Algebra Query:

πcustomer.cus_name,orders.ord_id(Customer customer.cus_id=orders.cus_idOrders)

Right Join

Cus_Name Ord_ID

Saleem 35

Rizwan 37

Ijlal 38

- 40

Result

Assuming that we to retrieve all ids from order table and related names

Left Join( )

SQl Query :select customer.cus_id,orders.ord_id from customer Left join orders on customer.cust_id=orders.cust_id

Relational Algebra Query:

πcustomer.cus_id,customer.cus_name,orders.ord_id(Customer customer.cus_id=orders.cus_idOrders)

Cus_id Cus_Name Ord_id

101 Ali

102 Saleem 35

103 Waseem

104 Rizwan 37

105 Ijlal 38

Result

Assuming that if we want to join two tables and retrieve the all ids and names from customer table and order ids related to customers ids then:

Division (/)Goal: Produce the tuples in one relation,that match all tuples in another relation.

Pid Pname2 H&S

3 Brite

4 Dove

5 Axe

Product

CID CName

201 Waheed

202 Ali

203 Rizwan

Customer

Pid CID

2 202

3 201

4 201

2 203

5 201

3 202

2 201

Transaction

Suppose we want to find the name of Customers who buy every product then:

SQL Query:SELECT CName FROM Customer WHERE NOT EXISTS (SELECT Pid FROM Product WHERE Pid NOT IN (SELECT Pid from Transaction WHERE Customer.CID = Transaction.CID))

Relational Algebra Query: ∏name((transaction/product) join customer)

ResultCNameWaheed

Relational Algebra

Education

Transcript of Relational Algebra