Selection Operation in Relational Algebra

The selection operation is a unary operation that is performed on one relation. The selection
operation is used to retrieve tuples from the relation that satisfies the given condition. It is denoted
by σ. Selection operation in relational algebra is written as: σcondition (Relationname)

We can also add multiple conditions if required using the operators ∧ (AND), ⋁and (OR). These
operators are used to combine multiple conditions as required in the problem. Below is the selection
operator representation with multiple conditions.

σcondition1 operator condition2 … condition n (Relationname)

To select all the tuples of a relation we write the selection operation without any condition.

σ (Relationname)

Projection Operation in Relational Algebra

The projection operation is a unary operation that is performed on a relation. A projection operation
is used to retrieve all the values of one or more attributes. It is denoted by π. Projection operation in
relational algebra is written as:

πcolumnname(Relationname)

We can add multiple column names in projection operation using the comma operator if required.
The comma operator is used when we have to retrieve multiple column values. Below is the
projection operation representation to output multiple columns.

πcolumnname1, columnname2, …,columnnamen (Relationname)

Rename Operation in Relational Algebra

The rename operation is operation applied on one relation. Rename operation as the name suggests
is used to rename the relation, attributes or both. It is denoted by ρ.

Rename Operation for Renaming Relation

Rename operation for renaming relation is written as:

ρ New_relation_name (Old_relation_name)

For example: To rename relation R to S: ρ S(R)

Rename Operation for Renaming Columns of Relation

Rename operation for renaming columns of relation is written as:

ρ (New_columnnames) (Relation_name)

Rename Operation for Renaming Both Relation and Columns of Given Relation

Rename operation for renaming both relation and columns of relation R (a, b, c) is written as:

ρ New_relation_name(New_columnnames) (Old_relation_name)

Example 1

Consider the student table given below −

 Regno Branch Section

1 CSE A

2 ECE B

3 CIVIL B

4 IT A

To display all the records of student table, use the command given below −

σ(student)

To display all the records of CSE branch in student table, we can use the command mentioned below
−

σbranch=cse(student)

Output

RegNo Branch Section

1 CSE A

To display all the records in student tables whose regno>2, we can use the command given below −

σRegNo>2(student)

Output

RegNo Branch Section

3 CIVIL B

4 IT A

To display the record of ECE branch section B students, use the command given below −

σbranch=ECE ^ section=B(student)

To display the records of section B CSE and IT branch, use the following command −

σSection=B ^ Branch=cse ∨ branch=IT(student)

To display branch, section column of student table, use the command given below −

∏branch,section(student)
Output

Branch Section

CSE A

ECE B

CIVIL B

IT A

JOIN OPERTION

A JOIN operation combines rows from two or more tables based on a related column or set of
columns, known as a key. The resulting table, known as a join table, contains all the columns from
the original tables, with each row representing a combination of the rows from the original tables
that satisfy the join condition.

There are several types of JOINs, each with its own unique characteristics and use cases −

 INNER JOIN − An INNER JOIN combines rows from both tables that match the join condition.
It returns only the rows that satisfy the condition and discards the rest.

 OUTER JOIN − An OUTER JOIN combines all rows from both tables, including those that do
not satisfy the join condition. There are three types of OUTER JOINs: LEFT JOIN, RIGHT JOIN,
and FULL JOIN.

 LEFT JOIN − A LEFT JOIN returns all rows from the left table, along with any matching rows
from the right table. If there is no match, NULL values are returned for the right table's
columns.

 RIGHT JOIN − A RIGHT JOIN returns all rows from the right table, along with any matching
rows from the left table. If there is no match, NULL values are returned for the left table's
columns.

 FULL JOIN − A FULL JOIN returns all rows from both tables, along with NULL values for any
non-matching rows.

JOIN Syntax
The syntax for a JOIN operation varies depending on the programming
language and database management system being used. Here is an
example of the general syntax for a JOIN operation in SQL −
SELECT * FROM table1 JOIN table2 ON table1.key = table2.key;
 In this example, the SELECT statement retrieves all columns from both
table1 and table2, and the JOIN clause specifies the tables to be joined
and the join condition using the ON keyword.

JOIN Examples

Here is an example of an INNER JOIN in SQL that combines the "customers" and "orders" tables
based on the "customer_id" column −

SELECT *

FROM customers

INNER JOIN orders

ON customers.customer_id = orders.customer_id

This INNER JOIN will return a table containing all rows from both the "customers" and "orders" tables
where the "customer_id" column in the "customers" table matches the "customer_id" column in the
"orders" table.

What is a DIVISION operation?

The DIVISION operation is a binary relational operation that divides one set of rows into another set
of rows based on specified conditions. It is similar to a JOIN operation, but the resulting table
contains only the rows that belong to the first set and satisfy the division condition.

DIVISION Syntax

The syntax for a DIVISION operation varies depending on the programming language and database
management system being used. Here is an example of the general syntax for a DIVISION operation
in SQL −

SELECT *

FROM table1

WHERE EXISTS (SELECT * FROM table2 WHERE table1.key = table2.key)

In this example, the SELECT statement retrieves all columns from table1, and the WHERE clause uses
the EXISTS keyword to check if there are any rows in table2 that satisfy the division condition.

DIVISION Examples

Here is an example of a DIVISION operation in SQL that divides the "customers" table into two sets
based on the "customer_type" column −

SELECT * FROM customers WHERE EXISTS (SELECT * FROM orders WHERE customers.customer_id =
orders.customer_id);

This DIVISION operation will return a table containing all rows from the "customers" table where
there is a matching row in the "orders" table based on the "customer_id" column.

KEYS:

Keys play an important role in the relational database.

 It is used to uniquely identify any record or row of data from the table. It is also used to
establish and identify relationships between tables.

For example, ID is used as a key in the Student table because it is unique for each student. In the
PERSON table, passport_number, license_number, SSN are keys since they are unique for each
person.

SUPER KEY:

Primary key

o It is the first key used to identify one and only one instance of an entity uniquely. An entity
can contain multiple keys, as we saw in the PERSON table. The key which is most suitable
from those lists becomes a primary key.

o In the EMPLOYEE table, ID can be the primary key since it is unique for each employee. In the
EMPLOYEE table, we can even select License_Number and Passport_Number as primary keys
since they are also unique.

o For each entity, the primary key selection is based on requirements and developers.

Candidate key

o A candidate key is an attribute or set of attributes that can uniquely identify a tuple.

o Except for the primary key, the remaining attributes are considered a candidate key. The
candidate keys are as strong as the primary key.

For example: In the EMPLOYEE table, id is best suited for the primary key. The rest of the attributes,
like SSN, Passport_Number, License_Number, etc., are considered a candidate key.
3. Super Key

Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a
candidate key.

For example: In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME), the name of two
employees can be the same, but their EMPLYEE_ID can't be the same. Hence, this combination can
also be a key. The super key would be EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-NAME), etc.

Foreign key

o Foreign keys are the column of the table used to point to the primary key of another table.

o Every employee works in a specific department in a company, and employee and department
are two different entities. So we can't store the department's information in the employee
table. That's why we link these two tables through the primary key of one table.

o We add the primary key of the DEPARTMENT table, Department_Id, as a new attribute in the
EMPLOYEE table.
 o In the EMPLOYEE table, Department_Id is the foreign key, and both the tables are related.

Entity Integrity Constraints

Entity integrity constraints state that primary key can never contain null value because primary key is
used to determine individual rows in a relation uniquely, if primary key contains null value then we
cannot identify those rows. A table can contain null value in it except primary key field.

Example:

It is not allowed because it is containing primary key as NULL value.

Student_id Name Semester Age

21CSE101 Ramesh 5th 20

21CSE102 Kamlesh 5th 21

21CSE103 Aakash 5th 22

Mukesh 5th 20

Referential integrity constraints

It can be specified between two tables. In case of referential integrity constraints, if a Foreign key in
Table 1 refers to Primary key of Table 2 then every value of the Foreign key in Table 1 must be null or
available in Table 2.
Example:

Here, in below example Block_No 22 entry is not allowed because it is not present in 2nd table.

Student_id Name Semester Block_No

22CSE101 Ramesh 5th 20

21CSE105 Kamlesh 6th 21

22CSE102 Aakash 5th 20

23CSE106 Mukesh 2nd 22

Block_No Block Location

20 Chandigarh

21 Punjab

25 Delhi

CHARACTERISTICS OF DBMS:

Security :

Database access must be limited for users to prevent unauthorized access and changes.
Authentication ensures users have specific rights to access the Database, allowing only authorized
actions. For example, administrators may modify the entire Database, while regular employees can
only edit their personal profiles.

ACID Properties

ACID properties – Atomicity, Consistency, Isolation, and Durability – maintain data integrity in DBMS.
Transactions are either fully completed or not at all (Atomicity). Changes bring data from one valid
state to another (Consistency). Multiple transactions occur independently (Isolation). Successful
transactions are permanent (Durability).
SQL and No-SQL Databases

SQL databases organize data into structured tables with rows and columns, providing a rigid and
relational approach to storage. Conversely, No-SQL databases offer flexibility, enabling data storage in
various formats beyond traditional tables. For instance, MongoDB, a popular No-SQL database,
adopts JSON for data representation.

Ease of Access (The DBMS Queries)

Before the advent of DBMS, data was stored using a file and folder system, which made searching for
specific information, like a student’s name, a laborious and time-consuming task. Each search
operation had to be performed manually within the files and folders. However, with the introduction
of DBMS, accessing data became significantly easier.

Stores Any Kind of Structured Data

One of the fundamental characteristics of a Database Management System (DBMS) is its capability to
store data in a structured format. While many examples focus on student databases for better
comprehension, it is essential to recognize that DBMS can accommodate an unlimited amount of
data.

Self-explaining nature

In DBMS, databases can contain other databases, and each database also contains metadata, which
refers to data about the data itself. For instance, in a school database, information like the total
number of rows and the names of tables would be considered metadata. The self-explaining nature
of the database arises from the fact that all the data is stored in a structured format, enabling
automatic understanding and organization of information.

Real-World Entity

A distinguishing characteristic of a Database Management System (DBMS) is its ability to uniquely

represent real-world entities in a structured and organized manner. A DBMS excels in creating a
comprehensive and easily understandable mapping of real-world objects, relationships, and
properties, transforming them into distinct data entities within the database.