Database Systems, Chapter 8

Tuple

A single row of a table, which contains a single record for that relation

Tuple Relational Calculus

Filtering variable ranges over tuples Notation − {T | Condition} Returns all tuples T that satisfies a condition.

Domain Relational Calculus

In DRC, the filtering variable uses the domain of attributes instead of entire tuple values (as done in TRC, mentioned above). Notation − { a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)} Where a1, a2 are attributes and P stands for formulae built by inner attributes.

8.5. What role does the concept of foreign key play when specifying the most common types of meaningful join operations?

A foreign key is a column or composite of columns which is/are a primary key of other table that is used to maintain a relationship between two tables. The JOIN operation is used to combine related tuples from two relations into a single tuple.

8.14. When is a query language called relationally complete?

A query language is called relationally complete if a query that is expressed in relational calculus can also be expressed in query landguage

RIGHT OUTER JOIN

A similar operation, right outer join, keeps every tuple in the second or right relation S in the result of R ⟖ S

FULL OUTER JOIN

A third operation, full outer join, denoted by ⟗, keeps all tuples in both the left and the right relations when no matching tuples are found, padding them with null values as needed.

Aggregate Functions & Grouping

A type of request that cannot be expressed in the basic relational algebra is to specify mathematical aggregate functions on collections of values from the database. Examples of such functions include retrieving the average or total salary of all employees or the total number of employee tuples. These functions are used in simple statistical queries that summarize information from the database tuples. Common functions applied to collections of numeric values include SUM, AVERAGE, MAXIMUM, and MINIMUM. The COUNT function is used for counting tuples or values.

Query Tree

An internal data structure to represent a query Standard technique for estimating the work involved in executing the query, the generation of intermediate results, and the optimization of execution Nodes stand for operations like selection, projection, join, renaming, division, .... Leaf nodes represent base relations A tree gives a good visual feel of the complexity of the query and the operations involved Algebraic Query Optimization consists of rewriting the query or modifying the query tree into an equivalent tree.

NATURAL JOIN

Another variation of JOIN called NATURAL JOIN — denoted by * — was created to get rid of the second (superfluous) attribute in an EQUIJOIN condition. because one of each pair of attributes with identical values is superfluous The standard definition of natural join requires that the two join attributes, or each pair of corresponding join attributes, have the same name in both relations If this is not the case, a renaming operation is applied first.

CARTESIAN PRODUCT

Combines information of two different relations into one. Notation − r Χ s Where r and s are relations and their output will be defined as r Χ s = { q t | q ∈ r and t ∈ s}

8.7. How are the OUTER JOIN operations different from the INNER JOIN operations? How is the OUTER UNION operation different from UNION?

Consider two relational databases R and S. When user wants to keep all the tuples in R, or all those in S, or all the tuples in R, or all those in S, or all those in both relations in the result of the JOIN regardless of weather or not they have matching tuples in other relation, set of operations called outer joins can do so. This satisfies the need of queries in which tuples from two tables are to be combined by matching corresponding rows, but without losing any tuples for lack of matching values. When only matching tuples (based on condition) are contained in resultant relation and not all tuples then join is INNER JOIN (EQUIJOIN and NATURALJOIN). For UNION operation databases have to be UNION compatible, i.e, they have same number of attributes and each corresponding pair of attributes have same domain. OUTER UNION operation was developed to take the union of tuples from two relations if the relations are not union compatible. This operation will take UNION of tuples in two relations R(X, Y) and S(X,Z) that are

DIVISION

DIVISION Operation The (binary) DIVISION operation is applied to two relations R(Z) ÷ S(X), where X ⊆ Z Let Y = Z − X (and hence Z = X ∪Y); that is, let Y be the set of attributes of R that are not attributes of S. The result of DIVISION is a relation T(Y) that includes a tuple t if tuples tR appear in R with tR [Y ] = t, and with: tR[X ] = tS for every tuple tS in S. For a tuple t to appear in the result T of the DIVISION, the values in t must appear in R in combination with every tuple in S.

PROJECT

It __________ column(s) that satisfy a given predicate. Notation − ∏A1, A2, An (r) Where A1, A2 , An are attribute names of relation r. Duplicate rows are automatically eliminated, as relation is a set.

UNION

It performs binary union between two given relations and is defined as r ∪ s = { t | t ∈ r or t ∈ s} Notation − r U s Where r and s are either database relations or relation result set (temporary relation). For a union operation to be valid, the following conditions must hold − r, and s must have the same number of attributes. Attribute domains must be compatible. Duplicate tuples are automatically eliminated.

Relational Calculus

Relational Calculus is a non-procedural query language, that is, it tells what to do but never explains how to do it.

SELECT: σ

Selects all tuples that satisfy the selection condition from a relation R. Notation − σp(r) Where σ stands for selection predicate and r stands for relation. p is prepositional logic formula which may use connectors like and, or, and not. These terms may use relational operators like − =, ≠, ≥, < , >, ≤.

LEFT OUTER JOIN

The left outer join operation keeps every tuple in the first or left relation R in R ⟕ S; if no matching tuple is found in S, then the attributes of S in the join result are filled or "padded" with null values.

Converting Single Expressions

The previous examples all summarized one or more attributes of an entire set of tuples - Maximum Salary or Count (number of) Ssn Grouping can be combined with Aggregate Functions Example: For each department, retrieve the Dno, COUNT Ssn, and AVERAGE SALARY A variation of aggregate operation F allows this: Grouping attribute placed to left of F symbol Aggregate functions to right of symbol Dno F COUNT Ssn, AVERAGE Salary (EMPLOYEE) This groups employees by Dno (department number) and computes the count of employees and average salary per group (department)

DIFFERENCE

The result of R - S, is a relation that includes all tuples that are in R but not in S The result's attribute names will be the same as the attribute names in R The two operand relations R and S must be "type compatible"

INTERSECTION

The result of the operation R ∩ S is a relation that includes all tuples that are in both R and S The result's attribute names will be the same as the attribute names in R The two operand relations R and S must be "type compatible"

RENAME

The results of relational algebra are also relations but without any name. The __________ operation allows us to __________ the output relation. This operation is denoted with small Greek letter rho ρ. Notation − ρ x (E) Where the result of expression E is saved with name of x.

JOIN

The sequence of CARTESIAN PRODUCT followed by SELECT is used quite commonly to identify and select related tuples from two relations A special operation, called JOIN combines this sequence into a single operation This operation is very important for any relational database with more than a single relation, because it allows us combine related tuples from various relations The general form of a join operation on two relations R(A1, A2, ..., An) and S(B1, B2, ..., Bm) is R ⋈ <join condition> S where R and S can be any relations that result from general relational algebra expressions.

8.2. What is union compatibility? Why do the UNION, INTERSECTION, and DIFFERENCE operations require that the relations on which they are applied be union compatible?

The two relations are said to be union compatible if both the relations have the same number of attributes and the domain of the similar attributes is same. The UNION, INTERSECTION and DIFFERENCE operations require that the relations on which they are applied be union compatible because all these operations are binary set operations. The tuples of the relations are directly compared under these operations and the tuples should have same no of attributes and the domain of the similar attributes should be same.

8.3. Discuss some types of queries for which renaming of attributes is necessary in order to specify the query unambiguously.

When a query has an NATURAL JOIN operation than renaming foreign key attribute is necessary, if the name is not already same in both relations, for operation to get executed. In EQUIJOIN after the operation is performed there are two attributes that have same values for all tuples. These are attributes which have been checked in condition. In NATURAL JOIN one of them has been removed only single attribute is there.

8.6. What is the FUNCTION operation? For what is it used?

it is used as aggregate function operation is used to perform some mathematical aggregate functions. It also allows the grouping of data/tuples based on some attributes of the relation.

Universal Quantifier: ∀

∀ is called the universal or "for all" quantifier because every tuple in "the universe of" tuples must make F true to make the quantified formula true.

Existential Quantifier: ∃

∃ is called the existential or "there exists" quantifier because any tuple that exists in "the universe of" tuples may make F true to make the quantified formula true.