Intro into Database Management

Relational Algebra and Calculus :

Query Languages are structured commands to insert and retrieve data from a database.​

Relational  Query Languages​

• The Relational Algebra is an algebraic notation, where queries are expressed by applying specialized operators to the relations.​

• The Relational Calculus which is a logical notation, where queries are expressed by formulating some logical restrictions that the tuples in the answer must satisfy. ​

Relational Algebra​:

1. Relational algebra operations work on one or more relations to define another relation without changing the original relations. ​
2. Both operands and results are relations, so output from one operation can become input to another operation. ​

There are six basic operations in relational algebra,

1. Selection, ​
2. Projection, ​
3. Intersection ​
4. Cartesian product​
5. Union​
6. Set Difference.​

1.Selection:

Works on a single relation R and defines a relation that contains only those tuples (rows) of R that satisfy the specified condition (predicate). 

In general  the select operation is denoted as​ σ <selection condition>(R)​

For example, List all staff with a salary greater than £10,000.​

σsalary > 10000(Staff) ​

2.Projection:

Works on a single relation R and defines a relation that contains a vertical subset of R,​ extracting the values of specified attributes and​
eliminating duplicates.​

The general form of the PROJECTION operation is​ Π<attribute list>(R)​

For example, Produce a list of salaries for all staff, showing only staffNo, fName, lName, and salary details. ​

ΠstaffNo, fName, lName, salary (Staff) ​

We can use Selection as well as projection in one query statement,

3.Set Operations:

It takes an input of two relations instances, they require to input set to be compatible.

1. They have the same number of fields
2. Corresponding fields, taken in order from left to right have the same domains.

1.Union:

1. Union of two relations R and S defines a relation that contains all the tuples of R, or S, or both R and S, duplicate tuples being eliminated. ​
2. ​R and S must be union-compatible. ​

2.Intersection:

1. Defines a relation consisting of the set of all tuples that are in both R and S. ​
2. R and S must be union-compatible.

3.Set difference :

1. Defines a relation consisting of the tuples that are in relation R, but not in S.
2. R and S must be union-compatible.

4.Cartesian product​: Defines a relation that is the concatenation of every tuple of relation R with every tuple of relation S. ​ ​

4.Division:

Division is typically required when you want to find out entities that are interacting with all entities of a set of different type entities.​
The division operator is used when we have to evaluate queries which contain the keyword ‘all’.

Division is not supported by SQL implementations. However, it can be represented using other operations.(like cross join, Except, In )​

Given two relations(tables): R(x,y) , S(y).​
R and S : tables​
x and y : column of R​
y : column of S​

R(x,y) div S(y) means gives all distinct values of x from R that are associated with all values of y in S.​

5.Join​:

The join operation is used to combine related tuples from two relations into single tuples.​ ​​

The general form of a join operation on two relations R(A1,A2,…An) and S(B1,B2,…Bm ) is R⋈<join condition>S​

Some important points about joints:

1. You can nest and chain joins to join more than two tables, but DBMS works its way through your query by executing joins on exactly two tables at a time.
2. If a join’s connecting columns contains nulls, the nulls never join.
3. Joins exist only for the duration a query and aren’t part of the database.

1.Cross Joins: Cross join is also referred to as Cartesian Product. For every row in the LEFT Table of the CROSS JOIN all the rows from the RIGHT table are returned and Vice-Versa.​

2.Equi Joins:

1. SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.​
2. You may also perform EQUI JOIN by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality.​

3.Natural Joins:

1. It is a special case of an equijoin, it compares all the columns in one table with corresponding columns that have the same name in the other table for equality.
2. Works only if the input tables have one or more pairs of meaningfully comparable, identically named columns
3. Performs join implicitly, It can be replicated explicitly with an ON clause in JOIN syntax or a WHERE clause in WHERE syntax.

4.Inner Joins:

1. It combines the rows retrieved from multiple tables on the basis of the common columns of the table or any conditions that tuples satisfy.​ ​
2. In this kind of a JOIN, we get all records that match the condition in both the tables, and records in both the tables that do not match are not reported. ​
3. INNER JOIN is based on the single fact that : ONLY the matching entries in BOTH the tables SHOULD be listed.​​

SELECT * FROM​ A INNER JOIN B​ USING (col1, col2,…)

5.Left joins:

Left Outer Join/Left Join returns all the rows from the LEFT table and the corresponding matching rows from the right table. If right table doesn’t have the matching record then for such records right table column will have NULL value in the result.​

6.Right Outer Join:

Right Outer Join/Right Join returns all the rows from the RIGHT table and the corresponding matching rows from the left table. If left table doesn’t have the matching record then for such records left table column will have NULL value in the result.​

7.FULL OUTER JOIN​:

It returns all the rows from both the tables, if there is no matching row in either of the sides then it displays NULL values in the result for that table columns in such rows.​

Full Outer Join = Left Outer Join + Right Outer Join​

​8.Self Joins:

If a Table is joined to itself using one of the join types explained above, then such a type of join is called SELF JOIN.​

Now if we need to get the name of the Employee and his Manager name for each employee in the Employee Table. Then we have to Join Employee Table to itself as Employee and his Manager data is present in this table only as shown in the below query:​

Relational Calculus​:

1. Relational calculus query specifies what is to be retrieved rather than how to retrieve it. ​ ​
2. No description of how to evaluate a query. ​ ​
3. Non procedural language.​ ​
4. When applied to databases, relational calculus has forms : tuple and  domain. ​

1.Tuple relational Calculus:

Tuple variable is a variable that ‘ranges over’ a named relation: i.e., variable whose only permitted values are tuples of the relation.​

A simple tuple relational calculus query is of the form {t | COND(t)}​

where t is a tuple variable and COND(t) is a conditional expression involving . ​The result is set of all tuples t that satisfy COND(t)​

For example, To find details of all staff earning more than £10,000: ​

    {S | Staff(S) AND S.salary > 10000} ​

2.Domain Relational Calculus:

Uses variables that take values from domains instead of tuples of relations. ​

If F(d1,d2, . . . ,dn) stands for a formula composed of atoms and d1,d2, . . . ,dn represent domain variables, then:​

 {d1,d2, . . . ,dn | F(d1,d2, . . . ,dn)} is a general domain relational calculus expression. ​

For example, Find the names of all managers who earn more than £25,000. ​

fN, lN | ( sN, posn, sex, DOB, sal, bN)​ (Staff (sN, fN, lN, posn, sex, DOB, sal, bN) AND posn = ‘Manager’AND  sal > 25000)}​