DBMS ASSIGNMENT

Name: Ritesh Verma

University Roll Number: 31401219005
Stream :BCA
Year: 2nd
Semester: 4th
Subject : Data Base Management System
Subject Code: BCAN-401
Assignment : CA3
College: Techno India College of Technology
University: Maulana Abul Kalam Azad
University of Technology (MAKAUT)
Submitted to: Sarbani Dasgupta Madam.
1. Difference between the following :
a) Theta Join
Theta join combines tuples from different relations provided they satisfy the theta
condition. The join condition is denoted by the symbols θ.
Notation
R1 ⋈θ R2
R1 and R2 are relations having attributes (A1, A2, …., An) and (B1, B2, …, Bn) such
that the attributes don’t have anything in common, that is R1 ∩ R2 = Φ.
Student_Detail −
STUDENT ⋈ Student.Std = Subject.Class SUBJECT

b) Equi Join
When theta join uses only equality comparison operator, it is said to be equijoin. The
example corresponds to equijoin.
Student_Detail −
STUDENT ⋈Student.Std = Subject.Class SUBJECT

c) Natural join ( ) ⋈
Natural join does not use any comparison operator. It does not concatenate the way a
Cartesian product does. We can perform a Natural Join only if there is at least one
common attribute that exists between two relations. In addition, the attributes must have
the same name and domain.
Natural join acts on those matching attributes where the values of attributes in both the
relations are same.
A⋈ B

d) Outer Joins

Theta Join, Equijoin and Natural Join called inner joins. An inner join includes only
those tuples with matching attributes and the rest are discarded in the resulting relation.
Therefore, we need to use outer joins to include all the tuples from the participating
relations in the resulting relation. There are three kinds of outer join – left outer join,
right outer join, and full outer join.

Left Outer Join (A B)

All the tuples from the left relation, R, are included in the resulting relation. If there are tuples
in R without any matching tuple in the Right relation S, then the S-attributes of the resulting
relation are made NULL.

Right Outer Join (A B)

All the tuples from right relation, S are included in the resulting relation. If there are tuples in
S without any matching tuples in R, then the R-attributes of resulting relation are made
NULL.
Full Outer Join (A B)
All the tuples from both participating relations are include in the resulting relation. If there
are no matching tuples for both relations, their respective unmatched attributes are made
NULL.

Q) Define the five-basic operator of relational algebra with an example each.

Ans: - The five-basic operator of relational algebra are:
1) Select Operation (σ)
It selects tuples that satisfy the given predicate from a relation.
Notation - σp(r)
Where σ stands for selection predicate and r stands for relation. p is prepositional logic
formula which may use connectors like and, or, and not. These terms may use relational
operators like − =, ≠, ≥, < ,  >,  ≤.
For Example –
σsubject = "database (Books)"

Output – Select tuples from books where subject is ‘database’.

σ (Books)
subject = "database" and price = "450"

Output – Select tuples from books where subject is ‘database’ and ‘price’ 450 or those
books published after 2010.

2) Project Operation (∏)

It projects column(s) that satisfy a given predicate.
Notation − ∏A1, A2, An (r)
Where A1, A2 , An are attribute names of relation r.
Duplicate rows are automatically eliminated, as relation is a set.
For example −
∏subject, author (Books)
Selects and projects columns named as subject and author from the relation Books.

3) Union Operation (∪)

It performs binary union between two given relations and is defined as −
r ∪ s = { t | t ∈ r or t ∈ s}
Notation − r U s
Where r and s are either database relations or relation result set (temporary relation).
For a union operation to be valid, the following conditions must hold −

 r, and s must have the same number of attributes.

 Attribute domains must be compatible.
  Duplicate tuples are automatically eliminated.
∏ author (Books) ∪ ∏ author (Articles)
Output − Projects the names of the authors who have either written a book or an article or
both.

4) Set Difference (−)

The result of set difference operation is tuples, which are present in one relation but are
not in the second relation.
Notation − r − s
Finds all the tuples that are present in r but not in s.
∏ author (Books) − ∏ author (Articles)
Output − Provides the name of authors who have written books but not articles.
5) Cartesian Product (Χ)
Combines information of two different relations into one.
Notation − r Χ s
Where r and s are relations, and their output will be defined as −
r Χ s = { q t | q ∈ r and t ∈ s}
σauthor = 'tutorial'(Books Χ Articles)
Output − Yields a relation, which shows all the books and articles written by tutorial.

Q2. Explain Relational Algebra using the operators (∂, ∏, U, - , X and show that: A∩B
= AUB-((A-B) U (B-A)).
1. First part:
 The select operation is to identify a set of tuples which is a part of a
relation and to extract only this tuple out. The select operation selects
tuples that satisfy a given predicate or condition.
 It is a unary operation defined on a single relation.
 It is denoted as σ.

Consider the following table”Book”:

Ace-no Yr-pub Title
734216 1982 Algorithm design
237325 1995 Database systems
631523 1992 Compiler design
543211 1991 Programming
376112 1992 Machine design

Example 1:- Select from the relation “Book” all the books whose year of
publication is 1992.
  σYr-pub=1992(Book)
Example 2:- Select from the relation “Book” all the books whose Ace-no is
greater than equal to 56782.
 σAcc-no>=56782(Book)
 The project Operation returns its argument relation with certain attributes left out.
 It is unary operation defined on a single relation.
 It is denoted as ∏.
Example:- List all the Title and Ace-no of the “Book” relation.
 ∏Ace-no,Title(Book)
 The Union operation is used when we need some attributes that appear in either or
both two relations.
 It is denoted as ∪.
Example:-
Borrower (customer-name, loan-number)
Depositor (customer-name, account-number)
Customer (customer-name, street-number, customer-city)
List all the customers who have either an account or a loan or both codes:
∏customer-name (Borrower)∪ ∏ customer-name (Depositor)
For a Union operation r ∪ s to be valid, two conditions must hold:
 The relation r ∪s must be same,i.e., they must have the same number of
attributes.
 The domains of the ith attributes of r and the ith attribute of s must be the
same for all i.
 The set difference operation finds tuples in one relation but not in other.
 It is denoted as –
Example:-
Find the names of all customers who have an account but not a loan.
∏customer-name (Depositor)-∏customer-name (Borrower)
 The Cartesian product operation allows combining information from two relations.
 It is denoted as r X s where r and s are relations.

Consider the following relation or table “r”:

A B
a 1
b 2
c 2

Consider another relation or table “s”.

B C
3 1a
2 2b
 Therefore r Χ s gives:

r.A r.B s.B s.C

A 1 3 1a
A 1 2 2b
B 2 3 1a
B 2 2 2b
B 2 3 1a
C 2 2 2b

2. Second Part
From the Venn diagram,the intersection of the two sets A &B in the shaded
part i.e., C A ∪ B=((A-B)∪ (B-A))∪ C
 (A ∪B)-((A-B)∪(B-A))=C
 A∩B-(A∩B)-((A-B)∩(B-A))[proved]

A C B

Q3. Let R=(A,B) and S=(A,C) and let r(R) and r(S) be relations.
Write relational algebra expression equivalent to the domain relational calculus
expression:
a) {<a> | there exist b (<a, b> belongs to r^b = 17}

=> ∏˄(σB=7(r))

b) {<a, b, c> | <a, b> belongs to r^<a, b> belongs to s}

=> r1s

Q4. Consider the following relational schema:

employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages(person-name, manager-name)

Give an example in the relational algebra to express each of the following Queries:
a. Find the names of all employees who work for First Bank Corporation.
 ∏person-name (σ company-name=’First Bank Corporation’ (works))
b. Find the names and cities of residence of all employees who work for First Bank
Corporation.
 ∏person-name,city (employee ⋈(σcompany-name =’First Bank
Corporation’
(works)))
c. Find the names, street address, and cities of residence of all employees who work for
First Bank Corporation and earn more than $10,000 per annum.
 ∏person-name, street, city(σ(company-name= ‘First Bank Corporation’˄
salary>10000)works ⋈ employee)
d. Find the names of all employees in this database who live in the same city as the
company for which they work.
 ∏person-name (employee ⋈ works ⋈ company)
e. Find the names of all employees who live in the same city and on the same street as do
their managers.
 ∏person-name(employee⋈manages)⋈(manager-
name=employee2.person-
name˄employee.street=employee2.street˄employee.city=employee2.cit
y) (ρemployee2
(employee)))
f. Find the names of all employees in this database who do not work for First Bank
Corporation.
 ∏person-name (σcompany-name ≠ ‘First Bank Corporation’(works))
g. Find the names of all employees who earn more than every employee of Small Bank
Corporation.
∏person-name(works)-(∏works.person-name(works
⋈(works.salary ≤ works2.salary ˄ works2.company-
name=’small Bank Corporation’) ρworks2(works)))
h. Assume the companies may be located in several cities. Find all companies located in
every city in which Small Bank Corporation is located.
∏company-name(company÷(∏city(σcompany-name=’SmallBank
Corporation’(company))))
i. Find the company with the most employees.
 t1← company-nameGcount-distinct person-name(works)
  t2← maxnum-employees (ρ company-name, num-employees)
(t1))∏company-name
(ρt3(company-name, num-employees)(t1) ⋈ ρt4(num-employees) (t2))
j. Find the company with the smallest payroll.
 t1← company-nameGdum salary (works)
 t2← minpayroll (ρcompany-payroll(company-name, payroll)
(t1))∏companyname(ρt3(company-name, payroll)(t1) ⋈ρt4(payroll)(t2))
k. Find those companies whose employees earn a higher salary, on average, than the
average salary at First Bank Corporation.
 t2←company-nameG avg salary(works)
 t2←σcompany-name=”First Bank Corporation”(t1)
5. Give expression using the tuple relational calculus and the domain relational calculus to
express each of the following queries:

a. Find the names of all employees who work for First Bank Corporation.
 Tuple relation calculus-
 {t|Ǝ s Є works(t[person-name]=s[person-name]˄ s[companyname]=”First
Bank Corporation”)}
 Domain relational calculus-
 {<p>|Ǝ c,s (<p,c,s> Є works ˄ c=”First Bank Cororation”)}
b. Find the names and cities of residence of all employees who work for First Bank
Corporation.
 Tuple relation calculus-
I. sa{t|Ǝ r Є employee Ǝ s Є works (t[person-name]=r[person-name] ˄ t[city]=r[city]
˄ r[person-name]=s[person-name] ˄ s[companyname]=”First Bank
Corporation”)}
 Domain relational calculus-
II. {<p,c>|Ǝ co,sa,st(<p,co,sa> Є works ˄ <p,st,c> Є employee ˄ co=”First
Bank Corporation”)}
c. Find the names, street address, and cities of residence of all employees who work
for First Ban Corporation and earn more than $10,000 per annum.
a. Tuple relation calculus-
i. {t|t Є employee ˄ (Ǝ s Є works(s[person-name]=t[person-name] ˄
s[company-name]=”First Bank Corporation” ˄ s[salary]>10000))}
b. Domain relational calculus-
i. {<p,s,c>|<p,s,c> Є employee ˄ Ǝ co,sa(<p,co,sa> Є works ˄ co=”First
Bank Corporation” ˄ sa>10000)}
d. Find the names of all employees in the database who live in the same city as the
company for which they work.
 Tuple relational calculus-
 {t|Ǝ e Є employee Ǝ w Є works Ǝ c Є company(t[person-
name]=e[personname] ˄ e[person-name]=w[person-name] ˄
w[companyname]=c[company-name] ˄ e[city]=c[city])}
 Domain relational calculus-
 {<p>|Ǝ st,c,co,sa(<p,st,c> Є employee ˄ <p,co,sa> Є works ˄ <co,c> Є
company)}
e. Find the names of all employees who live in the same city and on the same street
as do their managers.
 Tuple relational calculus-
 {t|Ǝ l Є employee Ǝ m Є manages Ǝ r Є employee(l[personname]=m[person-
name] ˄ m[manager-name]=r[person-name] ˄ l[street]=r[street] ˄
l[city]=r[city] ˄ t[person-name]=l[person-name)}
a. Domain relational calculus-
 {<t>|Ǝ s,c,m(<t,s,c> Є employee ˄ <t,m> Є manages ˄ <m,s,c> Є employee)}
f. Find the names of all employees in this database who do not work for First Bank
Corporation.
 Tuple relational calculus-
{t|Ǝ w Є works(w[company-name]≠ ‘First Bank Corporation’ ˄
t[personname]=w[person-name])}
 Domain relational calculus-
{<p>|Ǝ c,s(<p,c,s> Є works ˄ c ≠ ‘First Bank Corporation’)}
g. Find the names of all employees who earn more than every employee of Small
Bank Corporation.
 Tuple relational calculus-
 {t|Ǝ w Є works(t[person-name]=w[person-name] ˄ ∀ s Є
works(s[company-name]= ‘Small Bank
Corporation’⟹w[salary]>s[salary]))}
 Domain relational calculus-
 {<p>|Ǝ c,s(<p,c,s> Є works ˄ ∀ p2,c2,s2(<p2,c2,s2> ˄ works ˄
c2=
‘Small Bank Corporation’ ⟹ s>s2))}
h. Assume the companies may be located in several cities. Find all companies
located in every city in which Small Bank Corporation is located.
i.  Tuple relational calculus-
  {t|∀ s Є company(s[company-name]= ‘Small Bank Corporation’ ⇒ Ǝ r Є
company(t[company-name]=r[company-name] ˄ r[city]= s[city]))}
 Domain relational calculus-
 {<co>|∀ co2,ci2(<co2,ci2> Є company ˄ co2= ‘Small Bank Corporation’⇒
<co2,ci2> Є company)}