Projection and Selection Practice Questions in relational algebra

 Projection and Selection Practice Questions:

Student

S_ID	NAME	Branch
1	A	CS
2	B	IT
3	C	CS
4	D	CS
5	E	EE
6	F	EC
7	G	EE

 

 

1. Find the names of all students from CS branch.

Π name (σ Branch = ‘CS’ (Student))

Account (account_no, branch_name, balance)

• Find those account numbers where balance is less than 1000.

Π account_no (σ balance<1000 (Account))

Loan(loan_no, branch_name, amount) 

• Find those loan numbers which are from Delhi branch with amount greater than 1000.

Π loan_no (σ branch_name=’Delhi’ ^ amount>1000 (Loan))

 

Branch (Branch_name, Branch_city, assets) 

• Find branch_name and branch_city with assets more than 100000.

Π branch_name , branch_city (σ assets>100000 (Branch))

Projection Operator in Relational Algebra

 

Projection Operator-

 

• Projection Operator (π) is a unary operator in relational algebra that performs a projection operation.
• It displays the columns of a relation or table based on the specified attributes.
• It is a fundamental / Basic operator
• It is a unary operator

Syntax-

 

π<attribute list>(R)

 

Example-

 

Consider the following Student relation-

ID	Name	Subject	Age
100	Ashish	Maths	19
200	Rahul	Science	20
300	Naina	Physics	20
400	Sameer	Chemistry	21

Student

Result for Query π_{Name, Age}(Student)-

 

Name	Age
Ashish	19
Rahul	20
Naina	20
Sameer	21

Result for Query π_{ID , Name}(Student)-

ID	Name
100	Ashish
200	Rahul
300	Naina
400	Sameer

 

Important Points-

 

Point-01:

 

• The degree of output relation (number of columns present) is equal to the number of attributes mentioned in the attribute list.

Point-02:

 

• Projection operator automatically removes all the duplicates while projecting the output relation.
• So, cardinality of the original relation and output relation may or may not be same.
• If there are no duplicates in the original relation, then the cardinality will remain same otherwise it will surely reduce.

 

Point-03:

 

• If attribute list is a super key on relation R, then we will always get the same number of tuples in the output relation.
• This is because then there will be no duplicates to filter.

Point-04:

 

• Projection operator does not obey commutative property i.e.

π _<list2> (π _<list1> (R)) ≠ π _<list1> (π _<list2> (R))

Point-05:

 

• Selection Operator performs horizontal partitioning of the relation.
• Projection operator performs vertical partitioning of the relation.

 

Point-06:

 

• There is only one difference between projection operator of relational algebra and SELECT operation of SQL.
• Projection operator does not allow duplicates while SELECT operation allows duplicates.
• To avoid duplicates in SQL, we use “distinct” keyword and write SELECT distinct.
• Thus, projection operator of relational algebra is equivalent to SELECT operation of SQL.

Selection Operator in Relational Algebra

 

Selection Operator-

 

·   Selection Operator (σ) is a unary operator in relational algebra that performs a selection operation.

·   It selects those rows or tuples from the relation that satisfies the selection condition.

·   It is a fundamentals / Basic operator

·   It is a Unary Operator

 

σ_{<selection_condition>}(R)

 

·   Select tuples from a relation “Books” where subject is “database”

σ_{subject = “database”} (Books)

·   Select tuples from a relation “Books” where subject is “database” and price is “450”

σ_{subject = “database” ∧ price = “450”} (Books)

·   Select tuples from a relation “Books” where subject is “database” and price is “450” or have a publication year after 2010

σ_{subject = “database” ∧ price = “450” ∨ year >”2010″} (Books)

Important Points-

 

Point-01:

 

·   We may use logical operators like ∧ , ∨ , ! and relational operators like  = , ≠ , > , < , <= , >= with the selection condition.

Point-02:

 

·   Selection operator only selects the required tuples according to the selection condition.

·   It does not display the selected tuples.

·   To display the selected tuples, projection operator is used.

Point-03:

 

·   Selection operator always selects the entire tuple. It can not select a section or part of a tuple.

Point-04:

 

·   Selection operator is commutative in nature i.e.

σ _{A ∧ B} (R) = σ _{B ∧ A} (R)

OR

σ _B (σ _A(R)) = σ _A (σ _B(R))

 

Point-05:

 

·   Degree of the relation from a selection operation is same as degree of the input relation.

Point-06:

 ·        The number of rows returned by a selection operation is obviously less than or equal to the number of rows in the original table.

·        Minimum Cardinality = 0

·        Maximum Cardinality = |R|

 

 

Class 11 CS Practical - 52

 

52 - Write a program in Python to find the mean of numeric values stored in the given tuple.

T=eval(input("Enter a numeric tuple: "))

print("Tuple is: \n", T)

mean=sum(T)/len(T)

print("Mean of the numeric values of the tuple = ",mean)

Output:

Enter a numeric tuple: (2, 8, 9, 6, 5)

Tuple is: 

 (2, 8, 9, 6, 5)

Mean of the numeric values of the tuple =  6.0

Class 11 CS Practical - 51

 

51 - Write a program in Python to count the frequency of all elements of a tuple

T= (10, 20, 30, 20, 40, 30, 20, 50)

print("Tuple is: ",T)

Uni= ()

for i in T:

    if i not in Uni:

        Uni = Uni + (i,)

for i in Uni:

    print(i,":",T.count(i))

Output:

Tuple is:  (10, 20, 30, 20, 40, 30, 20, 50)

10 : 1

20 : 3

30 : 2

40 : 1

50 : 1

Class 11 CS Practical - 50

 

50 - Write a program in Python to count the frequency of all elements of a list.

L= [10, 20, 30, 20, 40, 30, 20, 50]

print("List is: ",L)

Uni= []

Freq= []

for i in L:

    if i in Uni:

        index=Uni.index(i)

        Freq[index]+= 1

    else:

        Uni.append(i)

        Freq.append(1)

for i in range(len(Uni)):

    print(Uni[i],":",Freq[i])

Output:

List is:  [10, 20, 30, 20, 40, 30, 20, 50]

10 : 1

20 : 3

30 : 2

40 : 1

50 : 1

Class 11 CS Practical - 49

 

49 - WAP in the Python to create a list and find the mean of all numeric values stored in the list.

L=eval(input("Enter a list: "))

n=len(L)

N=[]

for i in L:

    if isinstance(i,int) or isinstance(i,float):

        N.append(i)

print("Mean of numeric values stored in the list:")

print(sum(N)/len(N))

Output:

Enter a list: [5,'A',9,6.5,'Python',3.5]

Mean of numeric values stored in the list:

6.0

Class 11 CS Practical - 48

 

48 - WAP in Python to create a dictionary with name of employee, their salary and access them.

Emp={}

c='y'

while c in 'yY':

    name=input("Enter the name of employee: ")

    salary=float(input("Enter the salary of employee: "))

    Emp[name]=salary

    c=input("Do you want to add more emp(y/n): ")

print("Details of Employee:")

print(Emp)

Output:

Enter the name of employee: Ram

Enter the salary of employee: 38545

Do you want to add more emp(y/n): y

Enter the name of employee: Raj

Enter the salary of employee: 45854

Do you want to add more emp(y/n): y

Enter the name of employee: Aman

Enter the salary of employee: 47825

Do you want to add more emp(y/n): n

Details of Employee:

{'Ram': 38545.0, 'Raj': 45854.0, 'Aman': 47825.0}

Class 11 CS Practical - 47

 47 - WAP in Python to input a string and display the frequency of each character using dictionary

S=input("Enter a string: ")

F={}

for i in S:

    if i in F:

        F[i]+=1

    else:

        F[i]=1

print("Frequency of characters in the given string: ")

for i, j in F.items():

    print(i,":",j)

Output:

Enter a string: S P SHARMA

Frequency of characters in the given string:

S : 2

  : 2

P : 1

H : 1

A : 2

R : 1

M : 1

Class 11 CS Practical - 46

 

46 - Write a program in Python to input the value of x and n and print the sum of the following series:

x + x²/2! + x³/3! + x⁴/4! + -------- xⁿ /n!

import math as m

x=int(input("Enter the value of x: "))

n=int(input("Enter the value of n: "))

sum=0

for i in range(1,n+1):

    sum=sum+pow(x,i)/m.factorial(i)

  print("Sum of the series=",sum)

Output:

Enter the value of x: 2

Enter the value of n: 3

Sum of the series= 5.333333333333333

 

Enter the value of x: 2

Enter the value of n: 4

Sum of the series= 6.0