Relational Algebra:
---------------------
is a procedural query language. Its main purpose is:
* take relations as input and results other relations as output.
There are some fundamental opearations of relational algebra.
- Select(σ)
- Projection(π)
- Rename (ρ)
- Union operation (υ)
- Set Difference (-)
- Intersection(∩)
- Cartesian product(X)
-----------------------------------------------------------------------------------
-------
GENERAL statement:
P [col_names] S [condition] (table_name)
-----------------------------------------------------------------------------------
-------
* After discussion on 'select' and 'projection' we came to know that, both symbols
are to replaces some keywords in query.
-----------------------------------------------------------------------------------
-------
UNION operation (U)
---------------------
* The UNION operation operforms union between specified tables and specified
relations.
* To specify the union, use syntax:
(T1 U T2)
To specify the UNION operation, there are some rules.
- T1 and T2 must have same numbers of columns/attributes
- The attribute's metadata must also be compatible/same
- Duplicate records/rows gets automatically removed.
-----------------------------------------------------------------------------------
--------
create table book_authors
(
author_name varchar(25),
book_title varchar(20)
....
....
)
create table article_writers
(
article_writer varchar(25),
article_title varchar(20),
.....
.....
)
EXAMPLE 1
***********
Table 1 : book_authors
author_name book_title
---------------------------
Ramesh Let Us C
Jayesh Let Us C++
Varad Python
Nupur Java Programming
Prashant Adv. Python
Table 2 : article_writers
article_writer article_title
----------------------------------------
Varad New trends in Programming
Nupur Programming era
Prashant Adv. Python
Jayesh Tricks and logics in programming
Ramesh Evolving programming skills
Prashant Adv. Python
QUERY SYNTAX:
-------
select ATTRIB_LIST from TABLE1_NAME where condition
UNION
select ATTRIB_LIST from TABLE2_NAME where condition
FOR example:
---------------
select aurthor_name, book_title from book_authors
UNION
select article_title from article_writers
OUTPUT Table
-------------
author_name book_title article_title
------------------------------------------------
Ramesh Let Us C Evolving programming skills
Jayesh Let Us C++ Tricks and logics in programming
Varad Python New trends in Programming
Nupur Java Programming Programming era
Prashant [Link] [Link]
-----------------------------------------------------------------------------------
---------
select ..... INNER JOIN ... ON condition
UNION
select ..... RIGHT JOIN .... ON condition
OR
select ..... UNION ......
INNER JOIN
select ..... UNION ......
-----------------------------------------------------------------------------------
-----------
Relation algebra statement of UNION (U) is:
ACTUAL QUERY:
--------------
select aurthor_name, book_title from book_authors
UNION
select article_title from article_writers
RELATIONAL ALGEBRA UNION (U)
--------------------------------
π aurthor_name, book_title (book_authors)
U
π article_title (article_writers)
-----------------------------------------------------------------------------------
------------
Example 2
----------
ACTUAL QUERY:
--------------
select aurthor_name, book_title from book_authors where author_name='Nupur'
UNION
select article_title from article_writers where article_writer='Nupur'
RELATIONAL ALGEBRA UNION (U)
--------------------------------
π aurthor_name, book_title σ author_name='Nupur' (book_authors)
U
π article_title σ article_writer='Nupur'(article_writers)
-----------------------------------------------------------------------------------
--------------
*** Additionally we have another keyword/constraint "UNION ALL"
This will union two tables but includes duplicate rows also.
-----------------------------------------------------------------------------------
-------------