DBMS

Module 2
//For all the numerical refer ma’am ppt ch2_1//
● Relational Algebra

Types of Relational operation

1. Select Operation:

○ The select operation selects tuples that satisfy a given predicate.

○ It is denoted by sigma (σ).

1. Notation: σ p(r)

Where:

σ is used for selection prediction

r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and
NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.

For example: LOAN Relation

 BRANCH_NAME LOAN_NO AMOUNT

Downtown L-17 1000

Redwood L-23 2000

Perryride L-15 1500

Downtown L-14 1500

Mianus L-13 500

Roundhill L-11 900

Perryride L-16 1300

Input:

1. σ BRANCH_NAME="perryride" (LOAN)

Output:

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500

Perryride L-16 1300

2. Project Operation:

○ This operation shows the list of those attributes that we wish to appear in the
result. Rest of the attributes are eliminated from the table.

○ It is denoted by ∏.
 1. Notation: ∏ A1, A2, An (r)

Where

A1, A2, A3 is used as an attribute name of relation r.

Example: CUSTOMER RELATION

NAME STREET CITY

Jones Main Harrison

Smith North Rye

Hays Main Harrison

Curry North Rye

Johnson Alma Brooklyn

Brooks Senator Brooklyn

Input:

1. ∏ NAME, CITY (CUSTOMER)

Output:

NAME CITY

Jones Harrison

Smith Rye

Hays Harrison
 Curry Rye

Johnson Brooklyn

Brooks Brooklyn

3. Union Operation:

○ Suppose there are two tuples R and S. The union operation contains all the tuples
that are either in R or S or both in R & S.

○ It eliminates the duplicate tuples. It is denoted by ∪.

1. Notation: R ∪ S

A union operation must hold the following condition:

○ R and S must have the attribute of the same number.

○ Duplicate tuples are eliminated automatically.

Example:

DEPOSITOR RELATION

CUSTOMER_NAME ACCOUNT_NO

Johnson A-101

Smith A-121

Mayes A-321

Turner A-176
 Johnson A-273

Jones A-472

Lindsay A-284

BORROW RELATION

CUSTOMER_NAME LOAN_NO

Jones L-17

Smith L-23

Hayes L-15

Jackson L-14

Curry L-93

Smith L-11

Williams L-17

Input:

1. ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Johnson
 Smith

Hayes

Turner

Jones

Lindsay

Jackson

Curry

Williams

Mayes

4. Set Intersection:

○ Suppose there are two tuples R and S. The set intersection operation contains all
tuples that are in both R & S.

○ It is denoted by intersection ∩.

1. Notation: R ∩ S

Example: Using the above DEPOSITOR table and BORROW table

Input:

1. ∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)

Output:
 CUSTOMER_NAME

Smith

Jones

5. Set Difference:

○ Suppose there are two tuples R and S. The set intersection operation contains all
tuples that are in R but not in S.

○ It is denoted by intersection minus (-).

1. Notation: R - S

Example: Using the above DEPOSITOR table and BORROW table

Input:

1. ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Jackson

Hayes

Willians

Curry

6. Cartesian product
 ○ The Cartesian product is used to combine each row in one table with each row in
the other table. It is also known as a cross product.

○ It is denoted by X.

1. Notation: E X D

Example:

EMPLOYEE

EMP_ID EMP_NAME EMP_DEPT

1 Smith A

2 Harry C

3 John B

DEPARTMENT

DEPT_NO DEPT_NAME

A Marketing

B Sales

C Legal

Input:

1. EMPLOYEE X DEPARTMENT

Output:
 E EMP_N EMP_ DEP DEPT_N
MP AME DEPT T_N AME
_I O
D

1 Smith A A Marketing

1 Smith A B Sales

1 Smith A C Legal

2 Harry C A Marketing

2 Harry C B Sales

2 Harry C C Legal

3 John B A Marketing

3 John B B Sales

3 John B C Legal

7. Rename Operation:

The rename operation is used to rename the output relation. It is denoted by rho (ρ).

Example: We can use the rename operator to rename STUDENT relation to STUDENT1.

● ρ(STUDENT1, STUDENT)

● Tuple and Domain Relational Calculus

ppt
● Open source V/s commercial DBMS
 Basis
of Open Source Commercial
Compa Database Database
rison

Commercial
In Open Source
Database are that
Database anyone
which has been
Focus can easily view
created for
the Source code
Commercial
of it.
purpose only.

Examples:
Example MYSQL, Examples: Oracle,
s PostgreSQL, DB2, Splunk etc.
MongoDB etc.

Cost They are free or They are premium

have additional and are not free
 and affordable like open source
cost. database.

The community The community

can see, share, cannot see,
Commun and modify the exchange, or
ity code of modify the code of
open-source commercial DBMS
DBMS software. software.

The code is not

Because the
accessible to
source code is
Source unauthorized
open, there is a
Code users and has a
risk of coding
high level of
malfunction.
protection.

Technica It provide
It provide limited
l guaranteed
technical support.
support technical support.
 In this Software is
In this software is
available under
License available under
high licensing
free licensing.
cost.

In this user’s get

In this User’s
dedicated support
needs to rely on
Support from Vendor’s
Community
from where one’s
Support.
buy.

In this Installation In this Installation

Installati
and Updates are and updates are
on and
administrated by administrated by
Updates
user. Software Vendor.
 ● Bug fixes are ● There is a

simple to single point of

implement contact for any

without issues that

having to go arise. That

through the implies, you

approval pay for specific

process at needs, and

corporate. there is a party

● For any to blame if

premium difficulties
Benefits
solution, a develop.

free open ● The licensing is

source usually

alternative obvious, and it

with the same comes with a

or more guarantee.

functionality is ● Developers

always typically have a

accessible. thorough plan

● Because it is in place for the

more visible programme

by nature, it and release

can be updates as

inspected for needed. This

security allows

concerns, businesses to

which is a big save money on

benefit. the costs of

technical

outages and

failures.
 ● Volunteer

Technical

support

● Because of

Compatibility

difficulties, it Strict Licensing

cannot be guidelines

assured that it
Source code
will work in
cannot be
each user’s
modified so
Drawba environment
cks extra cost is
due to
incurred for
compatibility
getting more
difficulties.
functionality of
● Professionals
premium
are required to
features
manage and

even install

the essential

infrastructure

because some

of these
 difficulties

vary from

software to

hardware.

● Open source

also poses a

significant

security risk

because some

of it can easily

contain

security

exploits.

● Domain and data dependency

DOMAIN-
A domain in DBMS is a unique set of values permitted for an attribute in a table. For
example, a domain of week-of-days can accept Monday, Tuesday, ...., Sunday as
possible values, a domain of integers can accept whole numbers that are negative,
positive, and zero.

DATA DEPENDENCY-
Dependencies in DBMS is a relation between two or more attributes. In more simple
terms we can say that some data values are dependent on other data values in
order to get recognized.

Example-
Roll no→ name
In this example Roll no. will be unique for each student but two students can have the same
name. Suppose we want to know about the student whose name is Amit but there are two
students with the name Amit, so in this case, the name doesn’t uniquely identifies the
student(we have confusion about which student’s information we want). But if we take Roll
no.(say 101) then we will get information from one student whose name is Amit and Roll no.
is 101, hence no confusion. Therefore the name is dependent on Roll no.

It has the following types in DBMS −

● 1. Functional Dependency
● 2. Fully-Functional Dependency
● 3. Transitive Dependency
● 4. Multivalued Dependency
● 5. Partial Dependency

● Armstrong’s axioms

Introduction to Axioms Rules

● Armstrong's Axioms is a set of rules.

● It provides a simple technique for reasoning about functional

dependencies.

● It was developed by William W. Armstrong in 1974.

● It is used to infer all the functional dependencies on a relational

database.

Various Axioms Rules

A. Primary Rules

Rule Reflexivity
1 If A is a set of attributes and B is a subset of A, then A holds B. { A → B }
Rule Augmentation
2 If A hold B and C is a set of attributes, then AC holds BC. {AC → BC}
It means that attribute in dependencies does not change the basic
dependencies.

Rule Transitivity
3 If A holds B and B holds C, then A holds C.
If {A → B} and {B → C}, then {A → C}
A holds B {A → B} means that A functionally determines B.

B. Secondary Rules

Rule 1 Union
If A holds B and A holds C, then A holds BC.
If{A → B} and {A → C}, then {A → BC}

Rule 2 Decomposition
If A holds BC and A holds B, then A holds C.
If{A → BC} and {A → B}, then {A → C}

Rule 3 Pseudo Transitivity

If A holds B and BC holds D, then AC holds D.
If{A → B} and {BC → D}, then {AC → D}

Sometimes Functional Dependency Sets are not able to reduce if

the set has following properties,

1. The Right-hand side set of functional dependency holds only one

attribute.
2. The Left-hand side set of functional dependency cannot be reduced, it
changes the entire content of the set.
3. Reducing any functional dependency may change the content of the
set.

A set of functional dependencies with the above three properties are also
called as Canonical or Minimal.
Trivial Functional Dependency

Trivial If A holds B {A → B}, where A is a subset of B, then it is called a

Trivial Functional Dependency. Trivial always holds Functional
Dependency.

Non-Trivial If A holds B {A → B}, where B is not a subset A, then it is called as a

Non-Trivial Functional Dependency.

Completely If A holds B {A → B}, where A intersect Y = Φ, it is called as a

Non-Trivial Completely Non-Trivial Functional Dependency.

Example:
Consider relation E = (P, Q, R, S, T, U) having set of Functional
Dependencies (FD).

P→Q P→R

QR → S Q→T

QR → U PR → U

Calculate some members of Axioms are as follows,

1. P → T

2. PR → S

3. QR → SU

4. PR → SU

Solution:

1. P → T
In the above FD set, P → Q and Q → T
So, Using Transitive Rule: If {A → B} and {B → C}, then {A → C}
∴ If P → Q and Q → T, then P → T.
P→T

2. PR → S
 In the above FD set, P → Q
As, QR → S
So, Using Pseudo Transitivity Rule: If{A → B} and {BC → D}, then
{AC → D}
∴ If P → Q and QR → S, then PR → S.
PR → S

3. QR → SU
In above FD set, QR → S and QR → U
So, Using Union Rule: If{A → B} and {A → C}, then {A → BC}
∴ If QR → S and QR → U, then QR → SU.
QR → SU

4. PR → SU
So, Using Pseudo Transitivity Rule: If{A → B} and {BC → D}, then
{AC → D}
∴ If PR → S and PR → U, then PR → SU.
PR → SU
● Normal forms
❖ Numerical from yt
● Dependency preservation
❖ Numerical from yt
● Lossless design
❖ numerical
● Evaluation of relational algebra preservation
❖ Numerical
● Query equivalence
Refer GFG:
https://www.geeksforgeeks.org/query-optimization-in-relation
al-algebra/

● Join strategy
Join is an operation in DBMS(Database Management System) that combines the row of two or
more tables based on related columns between them. The main purpose of Join is to retrieve
the data from multiple tables in other words Join is used to perform multi-table query. It is
denoted by ⨝.

Syntax 1
R3 <- ⨝(R1) <join_condition> (R2)
where R1 and R2 are two relations to be join and R3 is a relation that will holds the result of join
operation.

Example
Temp <- ⨝(student) S.roll==E.roll(Exam)
where S and E are alias of the student and exam respectively

Types of Join
1. Inner Join
2. Outer join

Inner join
Inner Join is a join operation in DBMS that combines two or more table based on related
columns and return only rows that have matching values among tables.Inner join of two types.
● Equi Join
● Natural Join

Equi Join
Equi Join is a type of Inner join in which we use euivalence(‘=’) condition in join condition
Example:
Table A

Column A Column B

a a

a b

Table B

Column A Column B
 a a

a c

A ⨝ A.Column B = B.Column B (B)

Result:

Column A Column B

a a

Natural Join
Natural join is a type of inner join in which we not need of any comparison operators. In natural
join columns should have the same name and domain. There should be at least one common
attribute between two tables.
Eample:
Table A

Number Square

2 4

3 9

Table B

Number Cube

2 8

3 27

A⨝B
Number Square Cube

2 4 8

3 9 27

Outer Join
Outer join is a type of join that retrieve matching as well as non-maching records from related
tables.
There three types of outer join
● Left outer join
● Right outer join
● Full outer join

Left Outer Join

It is also called left join. This type of outer join retrieve all records from left table and retrive
maching record from right table.
Example:
Table A

Number Square

2 4

3 9

4 16

Table B

Number Cube

2 8
 3 27

5 75

A⟕B
Result:

Number Square Cube

2 4 8

3 9 27

4 16 –

Right Outer Join

It is also called right join. This type of outer join retrieve all records from right table and retrive
maching record from right table.
Example:
Table A and Table B are same as in left outer join
A⟖B

Number Square Cube

2 4 8

3 9 27

5 – 75

Full Outer Join

In full outer join all the rows from both table are inserted in result table
Eaxmple:
Table A and Table B are same as in left outer join
A⟗B
Result:

Number Square Cube

2 4 8

3 9 27

4 16 –

5 – 75

● Query optimization algebra

Join ke upar ka ans.