Relational Query Languages
Used for data manipulation and data retrieval. Relational model support simple yet powerful query languages. To understand SQL, we need good understanding of two relational query languages (i.e., relational algebra and relational calculus).
Basic Operation in Relational Algebra
The operations in relational algebra are classified as follows.
Selection (σ): The select operation selects tuples/rows that satisfy a given predicate or condition. We use (σ) to denote selection. The predicate/condition appears as a subscript to σ.
Projection (π): It selects only required/specified columns/attributes from a given relation/table. Projection operator eliminates duplicates (i.e., duplicate rows from the result relation).
Union (∪): It forms a relation from rows/tuples which are appearing in either or both of the specified relations. For a union operation R ∪ S to be valid, below two conditions must be satisfied.
- The relations Rand S must be of the same entity i.e., they must have the same number of attributes.
- The domains. of the i th attribute of R and i th attribute of S must be the same, for all i.
Intersection (∩): It forms a relation of rows/ tuples which are present in both the relations R and S. Ensure that both relations are compatible for union and intersection operations.
Set Difference (-): It allows us to find tuples that are in one relation but are not in another. The expression R – S produces a relation containing those tuples in R but not in S.
Cross Product/Cartesian Product (×): Assume that we have n1 tuples in R and n2 tuples in S. Then, there are n1 * n2 ways of choosing a pair of tuples; one tuple from each relation. So, there will be (n1 * n2) tuples in result relation P if P = R × S.
So, we can say that Cross product between two relation relate each and every tuple of one relation to the other relation.
Natural Join(⋈): A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.
Conditional Join(⋈C): It is performed by obtaining the cross product on given tables and then followed by applying the condition C on the obtained result. it is similar to conditional join except the fact that condition can be applied for any attributes (not restricted to the common attributes).
Outer Join: it is further divided into three parts:
- Left Outer Join(⟕): Output the records of natural join plus the record of left table which fails to appear in natural join.
- Right Outer Join(⟖): Output the records of natural join plus the record of right table which fails to appear in natural join.
- Full Outer Join(⟗): Output the records of natural join plus the record of left table as well as right table which fails to appear in natural join.
Rename Operator(ρ): It is used to rename the instance of a relation. it can also be used to rename the attributes of the given relation. also, the changes are not reflected to the original database.
- The relation returned by division operator will return those tuples from relation A which are associated to every B’s tuple.
- For R/S, Attributes of S should be proper subset of R.
Cardinalities: For a relation R with n distinct tuples and relation S with m distinct tuples-
|R⋈S||0 to nm tuples|
|R⋈CS||0 to nm tuples|
|R⟕S||n to nm tuples|
|R⟖S||m to nm tuples|
|R⟗S||max(m,n) to mn tuples|
|R∪S||max(m,n) to m+n tuples|
|R∩S||0 to min(m,n) tuples|
|R-S||0 to n tuples|
|R/S||0 to n/m tuples|
You can follow the detailed champion study plan for GATE CS 2021 from the following link:
Candidates can also practice 110+ Mock tests for exams like GATE, NIELIT with BYJU'S Exam Prep Test Series check the following link:
Get unlimited access to 21+ structured Live Courses all 112+ mock tests with Online Classroom Program for GATE CS & PSU Exams: