Hari Thapliyaal

Dr. Hari Thapliyal is a seasoned professional and prolific blogger with a multifaceted background that spans the realms of Data Science, Project Management, and Advait-Vedanta Philosophy. Holding a Doctorate in AI/NLP from SSBM (Geneva, Switzerland), Hari has earned Master’s degrees in Computers, Business Management, Data Science, and Economics, reflecting his dedication to continuous learning and a diverse skill set. With over three decades of experience in management and leadership, Hari has proven expertise in training, consulting, and coaching within the technology sector. His extensive 16+ years in all phases of software product development are complemented by a decade-long focus on course design, training, coaching, and consulting in Project Management. In the dynamic field of Data Science, Hari stands out with more than three years of hands-on experience in software development, training course development, training, and mentoring professionals. His areas of specialization include Data Science, AI, Computer Vision, NLP, complex machine learning algorithms, statistical modeling, pattern identification, and extraction of valuable insights. Hari’s professional journey showcases his diverse experience in planning and executing multiple types of projects. He excels in driving stakeholders to identify and resolve business problems, consistently delivering excellent results. Beyond the professional sphere, Hari finds solace in long meditation, often seeking secluded places or immersing himself in the embrace of nature.

2 minute read

Relational Algebra

SQL and Relational Algebra

Relational algebra (RA) is considered as a procedural query language where the user tells the system to carry out a set of operations to obtain the desired results. i.e. The user tells what data should be retrieved from the database and how to retrieve it.

Relational algebra notions can be implemtned via any any SQL language like PL/SQL, TSQL, SQLite SQL, DB2 SQL, MariaDB SQL, FireBird SQL, PSQL, ANSI SQL commands in any databases like MySQL, PostgreSQL, Oracle, SQLServer SQL server.

Tables

1. SELECT (σ) - Where clause

Used for selecting/filtering rows/tuples/records.

\(σ_p(r)\) σ is the predicate r stands for relation which is the name of the table

p is prepositional logic

\(σ_{topic = 'Database'}(Tutorials)\)
\(σ_{topic = 'Database' and author = 'Hari'}( Tutorials)\)
\(σ_{sales > 50000} (Customers)\)

2. Projection(π) - Select Attributes

Used for selecting columns/attributes/features.

\[Π_{CustomerName, Status} (Customers)\]

3. Rename (ρ)

ρ (a/b)R will rename the attribute ‘b’ of relation by ‘a’.

\[ρ (EmpName/EmployeeName)R\]

4. Union operation (υ)

A ∪ B

For a union operation to be valid, the following conditions must hold –

A and B must be the same number of attributes. Attribute domains need to be compatible. Duplicate tuples should be automatically removed.

5. Set Difference (-)

A – B

The attribute name of A has to match with the attribute name in B. The two-operand relations A and B should be either compatible or Union compatible. It should be defined relation consisting of the tuples that are in relation A, but not in relation B.

6. Intersection

A ∩ B

The attribute name of A has to match with the attribute name in B. The two-operand relations A and B should be either compatible or Union compatible. It should be defined relation consisting of the tuples that are in relation A, and in relation B.

7. Cartesian Product(X) in DBMS

The example shows all rows from relation A and B whose column 2 has value 1

\[σ_{column 2 = '1'} (A X B)\]

8. Join Operations

Join operation is essentially a cartesian product followed by a selection criterion.

Join operation denoted by ⋈.

Inner Joins:

In an inner join, only those tuples that satisfy the matching criteria are included, while the rest are excluded

8.1 Theta: A ⋈θ B

  • \[A ⋈ _{A.column 2 > B.column 2} (B)\]

8.2 EQUI join

  • \[A ⋈ _{A.column 2 = B.column 2} (B)\]

8.3 Natural join :

Natural join can only be performed if there is a common attribute between the relations

  • C ⋈ D

9. Outer join:

9.1 Left Outer Join(A ⟕ B)

In the left outer join, operation allows keeping all tuple in the left relation.

9.2 Right Outer Join ( A ⟖ B )

In the right outer join, operation allows keeping all tuple in the right relation

9.3 Full Outer Join ( A ⟗ B)

In a full outer join, all tuples from both relations are included in the result irrespective of the matching condition.