DBMS Concepts

DBMS Tutorial Components of DBMS. Applications of DBMS The difference between file system and DBMS. Types of DBMS DBMS Architecture DBMS Schema Three Schema Architecture. DBMS Languages.

DBMS ER Model

ER model: Entity Relationship Diagram (ERD) Components of ER Model. DBMS Generalization, Specialization and Aggregation.

DBMS Relational Model

Codd’s rule of DBMS Relational DBMS concepts Relational Integrity Constraints DBMS keys Convert ER model into Relational model Difference between DBMS and RDBMS Relational Algebra DBMS Joins

DBMS Normalization

Functional Dependency Inference Rules Multivalued Dependency Normalization in DBMS: 1NF, 2NF, 3NF, BCNF and 4NF

DBMS Transaction

What is Transaction? States of transaction ACID Properties in DBMS Concurrent execution and its problems DBMS schedule DBMS Serializability Conflict Serializability View Serializability Deadlock in DBMS Concurrency control Protocols

Difference

Difference between DFD and ERD

Misc

Advantages of DBMS Disadvantages of DBMS Data Models in DBMS Relational Algebra in DBMS Cardinality in DBMS Entity in DBMS Attributes in DBMS Data Independence in DBMS Primary Key in DBMS Foreign Key in DBMS Candidate Key in DBMS Super Key in DBMS Aggregation in DBMS Hashing in DBMS Generalization in DBMS Specialization in DBMS View in DBMS File Organization in DBMS What Is A Cloud Database What Is A Database Levels Of Locking In DBMS What is RDBMS Fragmentation in Distributed DBMS What is Advanced Database Management System Data Abstraction in DBMS Checkpoint In DBMS B Tree in DBMS BCNF in DBMS Advantages of Threaded Binary Tree in DBMS Advantages of Database Management System in DBMS Enforcing Integrity Constraints in DBMS B-Tree Insertion in DBMS B+ Tree in DBMS Advantages of B-Tree in DBMS Types of Data Abstraction in DBMS Levels of Abstraction in DBMS 3- Tier Architecture in DBMS Anomalies in Database Management System Atomicity in Database Management System Characteristics of DBMS DBMS Examples Difference between Relational and Non-Relational Databases Domain Constraints in DBMS Entity and Entity set in DBMS ER Diagram for Banking System in DBMS ER Diagram for Company Database in DBMS ER Diagram for School Management System in DBMS ER Diagram for Student Management System in DBMS ER Diagram for University Database in DBMS ER Diagram of Company Database in DBMS Er Diagram Symbols and Notations in DBMS How to draw ER-Diagram in DBMS Integrity Constraints in DBMS Red-Black Tree Deletion in DBMS Red-Black Tree Properties in DBMS Red-Black Tree Visualization in DBMS Redundancy in Database Management System Secondary Key in DBMS Structure of DBMS 2-Tier Architecture in DBMS Advantages and Disadvantages of Binary Search Tree Closure of Functional Dependency in DBMS Consistency in Database Management System Durability in Database Management System ER Diagram for Bank Management System in DBMS ER Diagram for College Management System in DBMS ER Diagram for Hotel Management System in DBMS ER Diagram for Online Shopping ER Diagram for Railway Reservation System ER Diagram for Student Management System in DBMS Isolation in DBMS Lossless Join and Dependency Preserving Decomposition in DBMS Non-Key Attributes in DBMS Data Security Requirements in DBMS DBMS functions and Components What is Homogeneous Database? DBMS Functions and Components Advantages and Disadvantages of Distributed Database

Relational algebra in DBMS

Relational algebra in DBMS

Relational algebra is a procedural query language used to retrieve the data from a database in a different way. It works on the relational model. It performs various operations such as insert, delete, update, and many other operations in the table. As relational algebra is a procedural language, it knows how to retrieve data and which type of data to be retrieved.

Operations in relational algebra:

We can divide the operations in relational algebrainto two categories:

  1. Basic Operation
  2. Derived Operation

Basic Operations:

  • Select (σ)
  • Project ()
  • Union (υ)
  • Set Difference(-)
  • Cartesian product (X)
  • Rename (ρ)

Derived Operations:

  • Natural Join
  • Left, right, full outer join
  • Division 
  • Intersection

Let us discuss all one by one:

Select operator

The select operator selects the specific rows (tuples) and shows them in a result set. The selected rows (tuples) will be displayed as the output. These rows (tuple) are selected according to the specified conditions.

The select operator is denoted by the sigma (σ) sign.

Syntax:

σ CONDITION (table_name)

Let's understand this with an example:

Below is a table named STUDENT. First, we will select a specific row where the STUDENT_NAME is "Ram."

Relational algebra in DBMS

Query:

? STUDENT_NAME=”Ram” (STUDENT)

Output:

Relational algebra in DBMS
It is the same as where clause in SQL.

Project Operator:

This operator is used to fetch the specific column which the user wants. The selection of the column is made according to the given condition. This operation deletes the duplicate rows in the table.

 It is denoted by ?(pi).

Syntax:

? CONDITION (table_name)

For example, we have a table STUDENT with three columns, and we are going to fetch only two columns STUDENT_ID and STUDENT_NAME.

Relational algebra in DBMS

Query:

? STUDENT_ID, STUDENT_NAME (STUDENT)

Output:

Relational algebra in DBMS

Union Operator

This operator is used to fetch all rows from the two tables. The ? symbol denotes it. Thus, all the rows (tuple) of the two tables appear once in the union set.

Note: There is no duplicate row present after union operation. If the column is present in both the tables, then only one column will appear containing data of both the tables.

Syntax:

table_name1 ? table_name2

Let’s understand more with the help of an example. We have two tables name STUDENT and BATCH. So we can fetch the data ofthe column “STUDENT_NAME” from both the tables in a single column as below:

Table1:STUDENT

Relational algebra in DBMS

Table2:BATCH

Relational algebra in DBMS

Query:

? STUDENT_NAME (STUDENT) ? ? STUDENT_NAME (BATCH)

Output:

Relational algebra in DBMS

Set Difference

This operation is used to select specific rows. For example, suppose we have two tables (t1 and t2). Now, if we want to select the row which is present in t1, not in t2, this operation can be used. It is denoted by – symbol.

Syntax:

Table_name1 –table_name2

Let’s understand this operation with example taking the same table STUDENT and BATCH present above.

Query:

? STUDENT_NAME (BATCH) - ? STUDENT_NAME (STUDENT)

Output:

Relational algebra in DBMS

Cartesian product (X)

This operation takes place between two tables. For example, let's have two tables, t1 and t2, then the Cartesian product of these two tables is t1Xt2. The product occurs as each row of the first table combines with each row of the second table.

Syntax:

t1 X t2

Let’s take two tables, t2 and t1, for example.

Table t1:

Relational algebra in DBMS

Table t2:

Relational algebra in DBMS

Query:

t1 X t2

Output:

Relational algebra in DBMS

Rename

Rename operation in relational algebra is used to rename the table name or the column name. Rename is denoted by ?. Using rename statement, we can change the table name as well as the column name.

Syntax:

?(new_table_name, old_table_name)

Let's take the above table BATCH in this example. After using this syntax, the new table name will be replaced by the old one. Here we will change the table name to COURSE.

Query:

?(BATCH, COURSE)

The name of the table has been changed from BATCH to COURSE.

Intersection Operation

This operation will select the rows which are present in both tables. It is denoted by ? symbol.

Syntax:

table_name1 ? table_name2

For example: Let ustake table STUDENT and COURSE as an example.

The standard column (with all the data) present in these two tables will be selected and displayed.Here we are going to select STUDENT_ID from both the tables.

Query:

? STUDENT_ID (STUDENT) ? ? STUDENT_ID (COURSE)

Output:

Relational algebra in DBMS