Monday, December 16, 2019

Unit-3 (3.3) Relational Algebra


Relational model stores data in the form of tables. This concept purposed by Dr. E.F. Codd, a researcher of IBM in the year 1960s. The relational model consists of three major components:
1. The set of relations and set of domains that defines the way data can be represented (data structure).
2. Integrity rules that define the procedure to protect the data (data integrity).
3. The operations that can be performed on data (data manipulation).
A rational model database is defined as a database that allows to group its data items into one or more independent tables that can be related to one another by using fields common to each related table.
Relational model can represent as a table with columns and rows. Each row is known as a tuple. Each table of the column has a name or attribute.
------------------  --------------------------------------------------------------------

Relational Model Concepts

  1. Attribute: Each column in a Table. Attributes are the properties which define a relation. e.g., Student_Rollno, NAME,etc.
  2. Tables – In the Relational model the, relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes.
  3. Tuple – It is nothing but a single row of a table, which contains a single record.
  4. Relation Schema: A relation schema represents the name of the relation with its attributes.
  5. Degree: The total number of attributes which in the relation is called the degree of the relation.
  6. Cardinality: Total number of rows present in the Table.
  7. Column: The column represents the set of values for a specific attribute.
  8. Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples.
  9. Relation key - Every row has one, two or multiple attributes, which is called relation key.
  10. Attribute domain – Every attribute has some pre-defined value and scope which is known as attribute domain

------------------
Domain: It contains a set of atomic values that an attribute can take.
Tables − In relational data model, relations are saved in the format of Tables. This format stores the relation among entities. A table has rows and columns, where rows represents records and columns represent the attributes.
Tuple − A single row of a table, which contains a single record for that relation is called a tuple.
Relation instance − A finite set of tuples in the relational database system represents relation instance. Relation instances do not have duplicate tuples. Or In the relational database system, the relational instance is represented by a finite set of tuples. Relation instances do not have duplicate tuples.
Relation schema − A relation schema describes the relation name (table name), attributes, and their names. Or  A relational schema contains the name of the relation and name of all columns or attributes.
Relation key − Each row has one or more attributes, known as relation key, which can identify the row in the relation (table) uniquely. Or In the relational key, each row has one or more attributes. It can identify the row in the relation uniquely
Attribute domain − Every attribute has some pre-defined value scope, known as attribute domain. It contains the name of a column in a particular table. Each attribute Ai must have a domain, dom(Ai)

--------------------------------------------------------------------
Properties of Relations
Ø  Name of the relation is distinct from all other relations.
Ø  Each relation cell contains exactly one atomic (single) value
Ø  Each attribute contains a distinct name
Ø  Attribute domain has no significance
Ø  tuple has no duplicate value
Ø  Order of tuple can have a different sequence
----------------------------------------------------------------------------------------
Ø  A table has a name that is distinct from all other tables in the database.
Ø  There are no duplicate rows; each row is distinct.
Ø  Entries in columns are atomic. The table does not contain repeating groups or multivalued attributes.
Ø  Entries from columns are from the same domain based on their data type including:
o   number (numeric, integer, float, smallint,…)
o   character (string)
o   date
o   logical (true or false)
Ø  Operations combining different data types are disallowed.
Ø  Each attribute has a distinct name.
Ø  The sequence of columns is insignificant.
Ø  The sequence of rows is insignificant.


Relational Algebra
Relational algebra is a procedural query language. It gives a step by step process to obtain the result of the query. It uses operators to perform queries.

We have learned the 6 fundamental operations of relational algebra:
Rename _ (Rho) ρ
Selection _ σ Sigma
Projection _ uppercase of (pi) ∏
Set union -
Set difference : -
Cartesian product _ X

Types of Relational operation

1. Select Operation:

  • The select operation selects tuples that satisfy a given predicate.
  • It is denoted by sigma (σ).
Notation:  σ p(r)
Where:
σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
For example: LOAN Relation
BRANCH_NAME
LOAN_NO
AMOUNT
Downtown
L-17
1000
Redwood
L-23
2000
Perryride
L-15
1500
Downtown
L-14
1500
Mianus
L-13
500
Roundhill
L-11
900
Perryride
L-16
1300
Input:
σ BRANCH_NAME="perryride" (LOAN)  
Output:
BRANCH_NAME
LOAN_NO
AMOUNT
Perryride
L-15
1500
Perryride
L-16
1300

2. Project Operation:

  • This operation shows the list of those attributes that we wish to appear in the result. Rest of the attributes are eliminated from the table.
  • It is denoted by ∏.
1.            Notation: ∏ A1, A2, An (r)   
Where
A1A2A3 is used as an attribute name of relation r.
Example: CUSTOMER RELATION
NAME
STREET
CITY
Jones
Main
Harrison
Smith
North
Rye
Hays
Main
Harrison
Curry
North
Rye
Johnson
Alma
Brooklyn
Brooks
Senator
Brooklyn
Input:
1.               ∏ NAME, CITY (CUSTOMER)  




Output:
NAME
CITY
Jones
Harrison
Smith
Rye
Hays
Harrison
Curry
Rye
Johnson
Brooklyn
Brooks
Brooklyn

3. Union Operation:

  • Suppose there are two tuples R and S. The union operation contains all the tuples that are either in R or S or both in R & S.
  • It eliminates the duplicate tuples. It is denoted by .
1.   Notation: R  S   
A union operation must hold the following condition:
  • R and S must have the attribute of the same number.
  • Duplicate tuples are eliminated automatically.

Example:

DEPOSITOR RELATION
CUSTOMER_NAME
ACCOUNT_NO
Johnson
A-101
Smith
A-121
Mayes
A-321
Turner
A-176
Johnson
A-273
Jones
A-472
Lindsay
A-284

BORROW RELATION
CUSTOMER_NAME
LOAN_NO
Jones
L-17
Smith
L-23
Hayes
L-15
Jackson
L-14
Curry
L-93
Smith
L-11
Williams
L-17
Input:
1.                  ∏ CUSTOMER_NAME (BORROW)  ∏ CUSTOMER_NAME (DEPOSITOR)  
Output:
CUSTOMER_NAME
Johnson
Smith
Hayes
Turner
Jones
Lindsay
Jackson
Curry
Williams
Mayes

4. Set Intersection:

  • Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in both R & S.
  • It is denoted by intersection ∩.
1.               Notation: R ∩ S   

Example: Using the above DEPOSITOR table and BORROW table
Input:
1.                     ∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)  
Output:
CUSTOMER_NAME
Smith
Jones

5. Set Difference:

  • Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in R but not in S.
  • It is denoted by intersection minus (-).
1.                  Notation: R - S  
Example: Using the above DEPOSITOR table and BORROW table
Input:
1.                        ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)  
Output:
CUSTOMER_NAME
Jackson
Hayes
Willians
Curry





6. Cartesian product

  • The Cartesian product is used to combine each row in one table with each row in the other table. It is also known as a cross product.
  • It is denoted by X.
1.      Notation: E X D  

Example:

EMPLOYEE
EMP_ID
EMP_NAME
EMP_DEPT
1
Smith
A
2
Harry
C
3
John
B
DEPARTMENT
DEPT_NO
DEPT_NAME
A
Marketing
B
Sales
C
Legal
Input:
1.                        EMPLOYEE X DEPARTMENT  
Output:
EMP_ID
EMP_NAME
EMP_DEPT
DEPT_NO
DEPT_NAME
1
Smith
A
A
Marketing
1
Smith
A
B
Sales
1
Smith
A
C
Legal
2
Harry
C
A
Marketing
2
Harry
C
B
Sales
2
Harry
C
C
Legal
3
John
B
A
Marketing
3
John
B
B
Sales
3
John
B
C
Legal

7. Rename Operation:

The rename operation is used to rename the output relation. It is denoted by rho (ρ).
Example: We can use the rename operator to rename STUDENT relation to STUDENT1.
1.                        ρ(STUDENT1, STUDENT)  

Join Operations:

A Join operation combines related tuples from different relations, if and only if a given join condition is satisfied. It is denoted by .

Example:

EMPLOYEE
EMP_CODE
EMP_NAME
101
Stephan
102
Jack
103
Harry
SALARY
EMP_CODE
SALARY
101
50000
102
30000
103
25000
1.      Operation: (EMPLOYEE  SALARY)
Result:
EMP_CODE
EMP_NAME
SALARY
101
Stephan
50000
102
Jack
30000
103
Harry
25000

Types of Join operations:


1. Natural Join:

  • A natural join is the set of tuples of all combinations in R and S that are equal on their common attribute names.
  • It is denoted by .
Example: Let's use the above EMPLOYEE table and SALARY table:
Input:
1.      ∏EMP_NAME, SALARY (EMPLOYEE  SALARY)
Output:
EMP_NAME
SALARY
Stephan
50000
Jack
30000
Harry
25000

2. Outer Join:

The outer join operation is an extension of the join operation. It is used to deal with missing information.
Example:
EMPLOYEE
EMP_NAME
STREET
CITY
Ram
Civil line
Mumbai
Shyam
Park street
Kolkata
Ravi
M.G. Street
Delhi
Hari
Nehru nagar
Hyderabad

FACT_WORKERS
EMP_NAME
BRANCH
SALARY
Ram
Infosys
10000
Shyam
Wipro
20000
Kuber
HCL
30000
Hari
TCS
50000
Input:
1.      (EMPLOYEE  FACT_WORKERS)
Output:
EMP_NAME
STREET
CITY
BRANCH
SALARY
Ram
Civil line
Mumbai
Infosys
10000
Shyam
Park street
Kolkata
Wipro
20000
Hari
Nehru nagar
Hyderabad
TCS
50000
An outer join is basically of three types:
  1. Left outer join
  2. Right outer join
  3. Full outer join

a. Left outer join:

  • Left outer join contains the set of tuples of all combinations in R and S that are equal on their common attribute names.
  • In the left outer join, tuples in R have no matching tuples in S.
  • It is denoted by .
Example: Using the above EMPLOYEE table and FACT_WORKERS table
Input:
1.      EMPLOYEE  FACT_WORKERS




EMP_NAME
STREET
CITY
BRANCH
SALARY
Ram
Civil line
Mumbai
Infosys
10000
Shyam
Park street
Kolkata
Wipro
20000
Hari
Nehru street
Hyderabad
TCS
50000
Ravi
M.G. Street
Delhi
NULL
NULL

b. Right outer join:

  • Right outer join contains the set of tuples of all combinations in R and S that are equal on their common attribute names.
  • In right outer join, tuples in S have no matching tuples in R.
  • It is denoted by .
Example: Using the above EMPLOYEE table and FACT_WORKERS Relation
Input:
1.      EMPLOYEE  FACT_WORKERS

Output:
EMP_NAME
BRANCH
SALARY
STREET
CITY
Ram
Infosys
10000
Civil line
Mumbai
Shyam
Wipro
20000
Park street
Kolkata
Hari
TCS
50000
Nehru street
Hyderabad
Kuber
HCL
30000
NULL
NULL

c. Full outer join:

  • Full outer join is like a left or right join except that it contains all rows from both tables.
  • In full outer join, tuples in R that have no matching tuples in S and tuples in S that have no matching tuples in R in their common attribute name.
  • It is denoted by .
Example: Using the above EMPLOYEE table and FACT_WORKERS table
Input:
1.      EMPLOYEE  FACT_WORKERS

Output:
EMP_NAME
STREET
CITY
BRANCH
SALARY
Ram
Civil line
Mumbai
Infosys
10000
Shyam
Park street
Kolkata
Wipro
20000
Hari
Nehru street
Hyderabad
TCS
50000
Ravi
M.G. Street
Delhi
NULL
NULL
Kuber
NULL
NULL
HCL
30000

3. Equi join:

It is also known as an inner join. It is the most common join. It is based on matched data as per the equality condition. The equi join uses the comparison operator(=).
Example:
CUSTOMER RELATION
CLASS_ID
NAME
1
John
2
Harry
3
Jackson
PRODUCT
PRODUCT_ID
CITY
1
Delhi
2
Mumbai
3
Noida
Input:
1.                  CUSTOMER  PRODUCT    




Output:
CLASS_ID
NAME
PRODUCT_ID
CITY
1
John
1
Delhi
2
Harry
2
Mumbai
3
Harry
3
Noida

Division Operator (÷): 
Division operator A÷B can be applied if and only if:
·         Attributes of B is proper subset of Attributes of A.
·         The relation returned by division operator will have attributes = (All attributes of A – All Attributes of  B)
·         The relation returned by division operator will return those tuples from relation A which are associated to every B’s tuple.
Consider the relation STUDENT_SPORTS and ALL_SPORTS given in Table 1 and Table 2 above.
Table 1
STUDENT_SPORTS
ROLL_NO
SPORTS
1
Badminton
2
Cricket
2
Badminton
4
Badminton

Table 2
ALL_SPORTS
SPORTS
Badminton
Cricket 

To apply division operator as
  STUDENT_SPORTS÷ ALL_SPORTS
·         The operation is valid as attributes in ALL_SPORTS is a proper subset of attributes in STUDENT_SPORTS.
·         The attributes in resulting relation will have attributes {ROLL_NO,SPORTS}-{SPORTS}=ROLL_NO
·         The tuples in resulting relation will have those ROLL_NO which are associated with all B’s tuple {Badminton, Cricket}. ROLL_NO 1 and 4 are associated to Badminton only. ROLL_NO 2 is associated to all tuples of B. So the resulting relation will be:
ROLL_NO
2

Assignment
Assignments are often used to increase clarity by cutting a long query into multiple steps, each of which can be described by a short line.
Denoted by:  T   ß[expression]
where [expression] is a relational algebra expression, and T is a table variable.
The assignment stores in T the table output by [expression].

Reference:

2 comments:

  1. I am really feel glad to read out published post on this blog. Apart from this, I am still waiting other post for escalating my knowledge power. Checkout How to Check NECO result

    ReplyDelete
  2. Excellent blog, very clear and informative. Thank you for an outstanding content. Check is kwasu post utme de form still on

    ReplyDelete

Reference

https://notesformsc.org/locks-database-management https://mycareerwise.com/content/concept-of-lossless-and-lossy-join-decomposition/content/...