##### Quick Contact

##### DBMS Tutorial

- DBMS Tutorial
- What is Database Management System (DBMS)?
- Components of DBMS
- Applications of DBMS
- Three Schema DBMS Architecture
- Difference between DBMS and RDBMS?
- Difference between File Oriented System and DBMS
- Types of Data Models
- DBMS Schema and Instances
- Data Independence and Data Abstraction
- Database Users and Administrator
- DBMS Languages and Interfaces

##### DBMS ER Model

##### DBMS Relational Data Model

##### DBMS Normalization

#### What is Relational Algebra?

Relational Algebra is a compilation of applications to manipulate or access relations. It is a **procedural (or abstract) language** with applications that is executed on additionally current relations to derive outcome (another) relations without modifying the initial relation(s). Furthermore, relational algebra represents the complete schema for each of the outcome relations.

Relational algebra includes a set of relational operators. Each operator has unnecessary relations as its input and generates a relation as its output. So, both the operands and the outcome are connections, and so the output from one operation can develop into the input to another operation.

A fundamental expression in relational algebra includes the following:

- A relation in the database
- A constant relation

The expression is composed of small sub-expressions. For instance, let E_1 and E_2be relational-algebra expressions. Then, the following are also expressions:

- E
_{1}∪E_{2} - E
_{1}-E_{2} - E
_{1}X E_{2} - σ
_{P}(E_{1})where P is a predicate on the attribute in E_{1}. - ∏
_{S}(E_{1})where S is a series involving some of the attributes in E_{1}. - ρ
_{x}(E_{1})where x is the new name for the result of E_{1}.

A query in relational algebra will include the following operations.

##### Types of Operations in Relational Algebra

Following are the two types of operations in relational algebra

- Basic/Fundamental Operations
- Derived Operations

Let us explain these operations one by one with the support of the following examples.

##### Basic/Fundamental Operations

###### SELECT Operation (σ)

The SELECT operation is used to choose a subset of the rows from a relationship that satisfies a selected condition.

###### Syntax of SELECT Operation (σ)

σ_{< Selection Condition >}(R)

Where σ(sigma) is the symbol for SELECT operation.

< Selection Condition >is a Boolean expression described on the attributes of relation R.

In the above syntax, R is a relation or name of a table, and the condition is a propositional logic which helps the relational operators like <,>,≤,≥,=,≠.

**Example:** Consider the following schema

**Employee (Eno, Ename, ECity, EStreet, Salary, Dno)**

**Department (Dno, Dname, DCity, TotalSal)**

Where the Total-Sal is the total salary of all employees in a department.

##### Employee

Eno | Ename | ECity | EStreet | Salary | Dno |
---|---|---|---|---|---|

001 | Rohan | Faridabad | Sec-16 | 42,000 | 01 |

002 | Sumit | Noida | Sec-15 | 35,000 | 02 |

003 | Neha | Delhi | Sec-4 | 28,000 | 01 |

004 | Gaurav | Gurgaon | Sec-11 | 50,000 | 03 |

005 | Raman | Faridabad | Sec-8 | 46,000 | 02 |

###### Department

Dno | DName | DCity | TotalSal |
---|---|---|---|

01 | Marketing | Delhi | 70,000 |

02 | inance | Noida | 81,000 |

**Query1: **Get information on those employees who are drawing a salary of more than 40,000.

σ_{Salary>40000} (Employee)

The resultant relation will be:

Eno | Ename | ECity | EStreet | Salary | Dno |
---|---|---|---|---|---|

001 | Rohan | Faridabad | Sec-16 | 42,000 | 01 |

004 | Gaurav | Gurgaon | Sec-11 | 50,000 | 03 |

005 | Raman | Faridabad | Sec-8 | 46,000 | 02 |

**Query2: **Get information on those employees who are living in Noida.

σ_{Ecity=”Noida”} (Employee)

The resultant relation will be:

Eno | Ename | ECity | EStreet | Salary | Dno |
---|---|---|---|---|---|

002 | Sumit | Noida | Sec-15 | 35,000 | 02 |

##### PROJECT Operation (∏)

The PROJECT operation is used to select a subset of the attributes of relation by defining the name of the necessary attributes. Any matching tuples in the result are automatically deleted. It can also be used to modify the order of attributes in a relation.

##### Syntax of PROJECT Operation (∏)

∏_{< attribute list >}(R)

Where ∏ is the symbol for project operation, < attribute list > is a list of attributes from the relation R.

##### Example

Consider the STUDENT relation

SID | NAME | SUBJECT | GRADE | AGE |
---|---|---|---|---|

111 | Mohit | Maths | A | 15 |

112 | Garima | History | B | 16 |

113 | Aayush | Science | A+ | 14 |

114 | Sheena | English | C | 12 |

**Query1: **Get a list of all the student names and student subjects.

∏_{NAME,SUBJECT}(STUDENT)

The resultant relation will be:

NAME | SUBJECT |
---|---|

Mohit | Maths |

Garima | History |

Aayush | Science |

Sheena | English |

###### Query2: Get a list of all the student ID and student age.

∏_{SID,AGE}(STUDENT)

The resultant relation will be:

SID | AGE |
---|---|

111 | 15 |

112 | 16 |

113 | 14 |

114 | 12 |

##### Union Operation(∪)

UNION operator is represented by ∪ symbol, and it is used to choose all the rows (tuples) from two tables (relations). Matching tuples are automatically deleted from the result.

Earlier taking it union between two relations, provides that both the union must be consistent, which involve that:

- Both the relation must be of a similar degree, i.e., they must have the same number of

attributes. - For all k, the domain of the kth attribute of one relation must be similar to the domain of the kth attribute of another relation.

##### Syntax of UNION Operation(∪)

A1∪A2

In the above syntax, A1 is the name of the first relation or table, and A2 is the name of the second relation or table.

**Example: **Consider the following schema

**DEPOSIT (Cust_Name, Account_No)**

**LOAN (Cust_Name, Loan_No)**

###### DEPOSIT

Cust_Name | Account_No |
---|---|

Mayank | A-101 |

Hardik | A-103 |

Himanshu | A-105 |

Ashutosh | A-106 |

###### LOAN

Cust_Name | Loan_No |
---|---|

Rahu | L-101 |

Hardik | L-103 |

Himanshu | L-105 |

**Query: **Get the names of those customers, who have either an account or loan in a bank

∏_{Cust-Name} (Deposit)∪∏_{Cust-Name} (Loan)

Cust_Name |
---|

Mayank |

Hardik |

Himanshu |

Ashutosh |

Rahul |

##### SET DIFFERENCE Operation(-)

The SET DIFFERENCE operation is a-b, which is between two relations ‘a’ and ‘b’ that generates a relation with the tuples which are there in ‘a’ but not there in ‘b’.

##### Syntax of SET DIFFERENCE Operation(-)

A-B

In the above syntax, A is the name of the first relation or table, and B is the name of the second relation or table.

**Example: **Consider the following schema

**DEPOSIT (Cust_Name, Account_No)**

**LOAN (Cust_Name, Loan_No)**

###### DEPOSIT

Cust_Name | Account_No |
---|---|

Mayank | A-101 |

Hardik | A-103 |

Himanshu | A-105 |

Ashutosh | A-106 |

###### LOAN

Cust_Name | Loan_No |
---|---|

Rahu | L-101 |

Hardik | L-103 |

Himanshu | L-105 |

**Query: **Get the names of those customers, who have an account in a bank, but do not have a loan.

∏_{Cust-Name} (Deposit) -∏_{Cust-Name} (Loan)

The resultant relation will be:

Cust_Name |
---|

Mayank |

Ashutosh |

Rahul |

##### CARTESIAN PRODUCT Operation (X)

CARTESIAN PRODUCT is represented by (X) symbol. The Cartesian product of two tables merges each row in one table with each row in the other table.

The Cartesian product of two relations a and b is expressed as a x b. The resultant relation will be an on the schema that will be a concatenation of the two schemas A and B, expressed as (A, B). For each tuple t_{a}∈a and each tuple t_{b}∈b,there will be a tuple t in axb, such that t[A]=t_{A} and t[B]=t_{B}.

Let say, relation a has p tuples, and the relation b has q tuples, then the resultant relation will have p*q tuples.

##### Syntax of CARTESIAN PRODUCT (X)

A x B

**Example: **Consider the following schema

**EMPLOYEE (Eno, Ename, Salary, Dno)**

**DEPARTMENT (Dno, Dname)**

###### EMPLOYEE

Eno | Ename | Salary | Dno |
---|---|---|---|

101 | Rahul | 15,000 | 1 |

102 | Rohit | 20,000 | 4 |

103 | Rohan | 18,000 | 5 |

###### DEPARTMENT

Dno | Dname |
---|---|

1 | Marketing |

3 | Sales |

5 | Legal |

**Query: **Let’s find the Cartesian product of the table EMPLOYEE and DEPARTMENT.

EMPLOYEE X DEPARTMENT

The resultant relation will be:

Eno | Ename | Salary | Dno | Dno | Dname |
---|---|---|---|---|---|

101 | Rahul | 15,000 | 1 | 1 | Marketing |

101 | Rahul | 15,000 | 1 | 3 | Sales |

101 | Rahul | 15,000 | 1 | 5 | Legal |

102 | Rohit | 20,000 | 4 | 1 | Marketing |

102 | Rohit | 20,000 | 4 | 3 | sales |

102 | Rohit | 20,000 | 4 | 5 | Legal |

103 | Rohan | 18,000 | 5 | 1 | Marketing |

103 | Rohan | 18,000 | 5 | 3 | sales |

103 | Rohan | 18,000 | 5 | 15 | Legal |

##### RENAME Operation (ρ)

A RENAME is a unary operation written as ρ_(a/b)(R) where the result is exact to R, except that the b field in all tuples is renamed to a field.

##### Syntax of RENAME Operation (ρ)

ρ (new_relation_name, old_relation_name)

###### Example

Consider the STUDENT relation

SID | NAME | SUBJECT | GRADE | AGE |
---|---|---|---|---|

111 | Mohit | Maths | A | 15 |

112 | Garima | History | B | 16 |

113 | Aayush | Science | A+ | 14 |

114 | Sheena | English | C | 12 |

**Query: **Get a list of all the student names.

_{ρNAME}, ∏_{NAME}(STUDENT)

The resultant relation will be:

NAME |
---|

Mohit |

Garima |

Aayush |

Sheena |

##### Derived Operations

##### SET INTERSECTION Operation (∩)

The intersection operator is denoted by the (∩) symbol, which is used to show all the rows of frequent attributes from two relations.

##### Syntax of SET INTERSECTION Operation (∩)

A∩B

In the above syntax, A is the name of the first relation or table, and B is the name of the second relation or table.

**Example: **Consider the following schema

**DEPOSIT (Cust_Name, Account_No)**

**LOAN (Cust_Name, Loan_No)**

###### DEPOSIT

Cust_Name | Account_No |
---|---|

Mayank | A-101 |

Hardik | A-103 |

Himanshu | A-105 |

Ashutosh | A-106 |

###### LOAN

Cust_Name | Loan_No |
---|---|

Rahu | L-101 |

Hardik | L-103 |

Himanshu | L-105 |

**Query: **Get the names of those customers, who have an account as well as loan in a bank

∏_{Cust-Name} (Deposit) ∩ ∏_{Cust-Name} (Loan)

The resultant relation will be:

Cust_Name |
---|

Hardik |

Himanshu |

##### DIVISION Operation (/)

The DIVISION is a binary operation that is composed of A/B. The outcome includes the constraints of rows in A to the attribute names exclusive to A, i.e., in the header of A, but not in the header of B, for which it influences that all their mixtures with rows in B are demonstration in A.

##### Syntax of DIVISION Operation (/)

A/B

**Example: **Consider the following Banking Schema

###### Branch

Branch-Name | Branch-City |
---|---|

Sec-18, HDFC | Noida |

South Delhi | Delhi |

West Delhi | Delhi |

###### Account

Account-No | Amount | Branch-Name |
---|---|---|

101 | 20,000 | Sec-18, HDFC |

102 | 25,000 | South Delhi |

103 | 30,000 | West Delhi |

###### Depositor

Customer-Name | Account-No |
---|---|

Vikram | 101 |

Rohit | 102 |

Rohit | 103 |

**Query: **Find the names of all customers who have accounts in all branches of Delhi.

A1←∏ _{Branch-Name(σ_(Branch-City=”Delhi”} (Branch))

A2←∏_{Customer-Name,Branch-Name} (Depositor C Account)

**Result:**A2/A1

##### A1

Branch-Name |
---|

South Delhi |

West Delhi |

##### A2

Customer-Name | Branch-Name |
---|---|

Vikram | Sec-62, HDFC |

Rohit | South Delhi |

Rohit | West Delhi |

The result relation will be:

Rohit |

##### ASSIGNMENT Operation (←)

Sometimes, it is useful to write a relational algebra expression by split it into sub-expressions and authorize the intermediate outcomes to temporary relation-variables. The ASSIGNMENT operation is represented by (←) designate the outcome of a relational-algebra sub-expression to a provisional relation-variable. The relation-variable can be used in subsequent expressions, equal to any permanent relation. Assignment to a permanent relation cause affects its modification.

##### Joins Operation(⋈)

A join operation is denoted by ⋈ (Bowtie) Symbol. Joins are the merger of related rows from two different relations or tables into an individual type. It is associated with a cartesian product.

Enroll Yourself in Live Training: DBMS Training