Cartesian Product Operation in SQL and RDBMS - concept discussion with example

  Cartesian Product Operation

 

* The Cartesian product operation is denoted by a cross ( X ) which allows us to combine information from any two relations .

 

* One can write the Cartesian Product of Relations R1 and R2 as R1 x R2

 

* A relation is by definition a subset of a Cartesian product of a set of domains .

 

* From the definition , one can have an intuition about the definition of the Cartesian product operation .

 

* Since the same attribute name may appear in both R1 and R2 , one may need to devise a naming schema to distinguish between the attributes .

 

* One can do the attachment to an attribute , the name of the relation from which the attribute originally came from .

 

* For example , the relation schema for

r = borrower * loan is :

 

(

borrower.customer_name ,

borrower.loan_number,

loan.loan_number ,

loan.branch_name ,

loan.branch_name ,

loan.amount

)

 

* With the schema , one can distinguish between borrower.loan_number from loan.loan_number as both the attributes do have the same name but the main relational table for the table are different which are loan and borrower

 

* The naming convention for any of the relations or schemas requires that the relation should have distinct names

 

* In general , if we have two relations r1(R1) and r2(R2) , then r1 x r2 is a relation whose schema is the concatenation of relations R1 and R2 .

 

Relation R contains all the tuples t for which there is a tuple t1 in relation r1 ; and a tuple t2 in r2 for which t|R1| = t|R1| and t|R2| = t2|R2|

 

* Suppose we want to find the names of all the customers who have a loan at the "PerryRidge" branch , then one may need the information in both the loan relation and the borrower relation through the given selection statement .

 

<< FIG - 01 >>

 

 

 From the given statement , one can find that a selection of attribute "branch-name" can be done with a relation existing from the relational tables for borrower and the loan relational table but given that the branch_name is "PerryRidge" over here .

 

* From the above relation , if one wants to find if there is a cartesian product operation that associates every tuple of loan with every tuple of borrower , with the customer having a loan in the “PerryRidge” branch , then there is some tuple in borrower x loan that contains the name of the customers which can be obtained by

criterion / condition from one table borrower to table loan where borrower.loan_number = loan.loan_number

 

Graphic Representation of all the involved Processes in Data Processing in Data Analytics and Data Science

 

Union Operation in RDBMS - Fundamental Relational Algebra Concept

 

            Union Operation - RDBMS

        Fundamental Relational Algebra Concept

=======================================

* Scenario where a Union Operation over a rdbms table could be used : -

Consider a query to find the names of all the bank customers who have either an account or a loan or both in a bank .

 

* To find the names of the customer who have an account and also a deposit account in the bank , the search query would consider the search to take over "depositor" relation table and the "borrower" relation table .

 

* In order to find out the names of all the customers with a loan in the bank , the query that could be used for the operation would be :

 

{figure-01}

The upper relational equation is a form of projection which allows us to produce the relevant relation that returns the argument relation specified within the parenthesis . So as we are interested in finding out the names of the customers who have a loan account , the

relevant result would be fetched from the projection relation as mentioned in the above statement .

 

* Similarly , if we want to know the names of all the customers with an account in the bank , then it can be expressed in the following projection equation :

 

{figure-02}

 

 

* Therefore , in order to find the answer to the raised question in our scenario that is discussed in the opening statement , one needs to do a "Union" operation over the two sets which is : we need all the customer names that appear in either both or two of the relations which can be found out by using the binary operation Union upon both the queries . The relevant relational equation can be represented in the given manner :

 

    {figure-03}

 

* The resulting relation for the query would result in a relation with the relevant tuples from both the tables

 

* The resulting relations are sets from which duplicate values are eliminated .

 

In Hindsight :

In our example , we took the union of two sets , both of which consisted of the attribute "customer_names" values . In general , one must ensure that unions are taken between compatible relations which would generate the appropriate results without duplicates.

 

* Points to note when using a Union Operation over relational sets :

 

If r is a set and s is set , and one needs to find { r U s } , If r is a set and s is set , and one needs to find { r U s } , then the conditions that should satisfy and hold for both relations

1)        The relations r and s must be of the same type and they must have the same number of attributes

 

2)   The domains of the ith attribute of r and ith attribute of s must be the same for all values of i . One may note that , both r and s are either database relations or temporary relations that are the result of relational algebra expressions as given in figure 1 and figure 2 of the article .

Self Variable in _init_ constructor

 

Self Variable in _init_ constructor

* 'Self' is a default variable that contains the memory address of the instance of the current class which is under current usage .

 

* So , one can use 'self' instance variable to refer to all the instance variables and Instance Methods .

 

* When an instance of the class is created , the

instance name contains the memory location of the instance . The memory location is internally passed to the 'self' . For example , one can create an instance of the Student Class in the given manner :

 

S1 = Student()

 

* Here , 's1' contains the memory address of the instance . This memory address is internally and by default passed to the 'self' variable .

 

 

* Since the 'self' knows the memory address of the instances , it can refer to all the members of the instance .

 

* One can use 'self' in the following ways :

1) The 'self' variable is used as a first parameter within the constructor as :

 

def _init_(self):

In this case , 'self' can be used to refer to the instance variables inside the constructor .

 

2) 'self' can be used as a first parameter in the instance method as :

 

def talk(self):

 

* Here , talk() is an instance method as it acts on the instance variables present within the Class and defined under the _init_(self): method .

 

* If the method wants to act on the instance variables , then the method should know the memory location of the instance variables . That memory location is by default available to the talk() method through the 'self' method .

Concept of Polymorphism in OOPS

 

Polymorphism

* The word "Polymorphism" comes from two Greek words "Poly" meaning "many" and "morphos" meaning "forms".

 

* Therefore , Polymorphism represents the ability to assume several different forms

 

* In Programming , if an object or a method is

exhibiting different form of behaviours , then the object is said to having polymorphic nature .

 

* Polymorphism provides flexibility in writing programs in such a way that the programmer uses same method call to perform different operations depending upon the requirement .

 

* Example Code for Polymorphism in Python :

 

- When a function can perform different tasks , then one can say that the function is exhibiting polymorphism behaviour .

 

- A simple example to write a function :

 

  def add(a,b):

       print(a+b)

* Since in Python , the variables are not declared explicitly , we are passing two variables 'a' and 'b' to the add() function where the variables are added to each other .

* CASE 01 - While calling the function , if we pass two integers like 5 and 15 to the function , then the function displays 15 as output .

 

* CASE 02 - If we pass two strings , then the same function concatenates or joins those strings . This means that the same function is adding two integers or concatenating two strings .

 

* Since the Polymorphism function is performing two different tasks , it is said to exhibit polymorphism behaviour .

 

* One can consider the following example for the case of Polymorphism :

=================================================

# Function that exhibits Polymorphism

def add(a,b):

    print(a+b)

 

# calling add() function and passing two integers

add(10,20)

 

# calling add() function and passing two strings

add("Core","Python"

 

 

* The Programming Languages which follow all the five features of OOPS – ( Classes with objects Abstraction , Encapsulation , Inheritance , Polymorphism) are called as object oriented programming languages .

 

* These type of behaviours are exhibited by C++ , Java and Python type of languages

Types of Variables in Python ( related to Classes and Objects in OOPS )

 

Types of Variables in Python

 

* The variables which are written inside a class are of primarily two types :

1) Instance Variables

2) Class Variables / Static Variables

 

* Instance variables are the variables whose separate copy is created in every instance ( or object )

 

* For example , if 'x' is an instance variable and if we create 2 instance variables then there will be 2 copies of 'x' in the instances .

 

* When we modify the copy of 'x' in any instance, then it will not modify the other two copies .

 

==========================================
Python Program to understand Instance Variables
==========================================

 

# instance variable example

Class Sample:

    # this is a constructor

    def __init__(self):

    self.x = 10

 

    # this is an instance method

   def modify(self):

   self.x += 1

 

 

# Create 2 instances

    s1 = Sample()

    s2 = Sample()

    print(' x in s1 = ', s1.x)

    print(' x in s2 = ', s2.x)

 

# modify x in s1

     s1.modify()

    print(' x in s1 = ' , s1.x)

    print(' x in s2 = ' , s2.x)

 

# modify x in s1

    s1.modify()

    print(' x in s1 = ', s1.x)

    print(' x in s2 = ', s2.x)

 

Output

x in s1 = 10

x in s2 = 10

x in s1 = 11

x in s2 = 10

================================================================

 

* Instance Variables are defined and initialised using a constructor with 'self' parameter . In the above program it can be realised in the line just after the usage of the docstring over where the code for declaration and definition of the Instance Variables is mentioned and in the succeeding line the __init__(self) is defined .

 

* In order to access the instance variables one needs Instance methods with 'self' as the first parameter which can be observed from the above code as well

 

* It is possible that the instance methods may have other parameters in addition to the 'self' parameter .

 

* In order to access the instance variables , one can use a self.variable in a program .

 

* It is also possible to access the instance variables from outside the class as : instancename.variable e.g. s1.x .

 

* Unlike instance variables , class variables are the variables whose single copy is available to all the instances of the class .

 

Example of Inheritance Concept ( in OOPS ) in Python

 

 

Example of Inheritance in Python

 

* In Python lets assume that , one can take a class A with two variables 'a' and 'b' and a method like method01() in the given manner .

 

* Since all the methods are needed by another class B , one can extend class B from class A .

 

* If we want some additional members in object B , for example a variable 'c' and a method in the form of method02() . These methods are written in object B .

 

* From this one would remember that class B can use all the members of both A and B .

 

* This means that all the variables 'a' , 'b' , 'c' and the methods method01() and method02() are available to the class B . This means that all the members of class A are inherited by class B .

 

<< FIG >>

 

 

* By creating an object of B , one can access all the members of both the classes A and B that is when an object of class B is created which is a form of derived class object , from the base class , one can create another object .