Relational algebra - Theoretical

Question 1

What is relational algebra?

Answer 1

It is a formal language that created to formally describe relational databases

Question 2

What is a relation?

Answer 2

A table

Question 3

What is a closure in relational algebra?

Answer 3

It is a nested query (subquery) which output is used in the outer query.

e.g:
SELECT * FROM
Employees 
WHERE DName = (SELECT 
Dname 
From Departments
WHERE Dname = "Research")

Question 4

How many operations are there in relational algebra?

Answer 4

Originally it included 8 but more have been added since..

Question 5

What is a unary operation?

Answer 5

An operation that only involves one single relation (1 table)

Question 6

How many fundamental operations are there in relational algebra?

Answer 6

Five;

1. selection
2. projection
3. Cartesian product
4. union
5. minus.

Question 7

What are the additional operations besides the fundamental ones in relational algebra??

Answer 7

Join
intersection
Division operations

Question 8

What is a binary operation?

Answer 8

an operation that do work on two relations (2 tables) and results in a single table (it shows the result in a new table).

Question 9

What is the selection operation?

Answer 9

Unary operation (works on a single table), it defines a new relation (table) with all the tuples (rows) that is selected (satisfy condition)

Question 10

What is the projection operation?

Answer 10

Unary operation that outputs a new relation that contains all the columns of given attributes.

e.g. all names that exist in the table, by selecting all names and phonenumbers.

Question 11

What is the union operation?

Answer 11

Binary operation(two relations)
Outputs one relation that contains all the tuples of the two relations without duplications

Question 12

What are the requirements of the union operation?

Answer 12

The schemas must match; same number of attributes and the corresponding attributes has the same domain (but naming is irrelevant)

Question 13

What is the minus operation?

Answer 13

Binary operation that defines a relation consisting of tuples that are in one but not the other.. So rows that only exist in one of the tables
opposite of intersection

Question 14

What is the intersection operation?

Answer 14

binary operation that results in a relation that consist of only the tuples that exist in both realtions.

Question 15

Rename operation

Answer 15

unary operation that gives new names to a relation or attributes.

Question 16

Cartesian product operation

Answer 16

binary operation that defines a relation, that consists of the concatenation of every tuple of both relations. null can appear

Question 17

What are joins

Answer 17

binary operations that outputs one table with data from both tables.

Question 18

What types of joins exist?

Answer 18

We distinguish between Inner- and outer-joins.

Inner Joins: Theta, Natural, EQUI
Outer Join: Left, Right, Full

Question 19

What is considered as the default join operation?

Answer 19

EQUI / INNER JOIN

Question 20

What is an EQUI / INNER JOIN

Answer 20

INNER JOIN that is comparator-based; it uses ‘=’ comparisons to do joins.

Question 21

Here is an example of an equi-join:

Answer 21

SELECT *
FROM TableName1, TableName2
WHERE TableName1.ColumnName = TableName2.ColumnName;
-- OR
SELECT *
FROM TableName1
JOIN TableName2
ON TableName1.ColumnName = TableName2.ColumnName;

Question 22

If we do joins with another operator than ‘=’ is it then an equi-join?

Answer 22

No, that is a theta join.

a theta join becomes an equi join by using only the = operator

Question 23

What is a theta join

Answer 23

It is also an INNER JOIN that allows you to join based on other operators than = like: , ≥, ≠

Question 24

How can you choose between using theta or equi join?

Answer 24

If your theta join only uses = DBMS will make use of equi join

Question 25

What is a natural join

Answer 25

natural joins are inner joins. They do not use operators. Therefore the attributes need same name and domain and there should be at least one common attribute.

It selects on the common attributes and eliminates duplicate attributes (e.g. if id appears in both)

Question 26

What is the difference between outer and inner joins?

Answer 26

an outer join does not require each tuple to have a matching column, whereas the inner joins work by joining on the common column.

Question 27

What are the types of the outer joins?

Answer 27

left outer join
right outer join
full outer join

Question 28

What is a left outer join?

Answer 28

returns all the rows from the table A even though no match has been found in second table B and still appends the matching rows together, leaving null values where it needs. example of a result:
Num Square Cube
2	      4	      8
3	      9 	      18
4	      16       -

Question 29

what is a right outer join?

Answer 29

RIGHT JOIN returns all the columns from the table on the right even if no matching rows have been found in the table on the left.

Where no matches have been found in the table on the left, NULL is returned.

Question 30

What is a full join?

Answer 30

It is an outer join that retains all information on both tables.

Question 31

If you want to retain all information from a table while joining another table onto which join should you make?

Answer 31

a left or right outer join

Question 32

Can the operations in relational algebra perform mathematical calculations?

Answer 32

No for that you need aggregate functions.

Which is, sum() and so on

Question 33

What is aggregate functions?

Answer 33

That is functions that you run over attributes of a relation

Question 34

Which aggregate functions are there?

Answer 34

COUNT to return the number of values of a given attribute
– SUM to calculate the sum of the values in a given attribute
– AVG to calculate the average value of a given attribute.
– MIN to return the smallest value in a given attribute.
– MAX to return the largest value in a given attribute