Chapter 8 Flashcards by Tala Aldhahri

_________ is the basic set of operations for the relational model.

Relational algebra

How well did you know this?

Not at all

Perfectly

T/F: Relational operations enable a user to specify basic retrieval requests (or queries.)

True.

How well did you know this?

Not at all

Perfectly

T/F: The result of a relational operation is a new relation, which may have been formed from one or more input relations.

True.

How well did you know this?

Not at all

Perfectly

“The result of a relational operation is a new relation, which may have been formed from one or more input
relations.”

This property makes the algebra ______.

“closed” (all objects in relational algebra are relations)

How well did you know this?

Not at all

Perfectly

T/F: Not all objects in relational algebra are relations.

F, all objects in relational algebra are relations.

How well did you know this?

Not at all

Perfectly

T/F: The new relations produced by the algebra operations can be further manipulated using operations of the same algebra.

True.

How well did you know this?

Not at all

Perfectly

A sequence of relational algebra operations forms a ______________.

relational algebra expression

How well did you know this?

Not at all

Perfectly

T/F: The result of a relational algebra expression is also a relation that represents the result of a database query (or retrieval request.)

True.

How well did you know this?

Not at all

Perfectly

Relational Algebra consists of several groups of operations…

1.Unary Relational Operations, includes:
SELECT
PROJECT
RENAME

What are their symbols?

σ (sigma), π (pi), ρ (rho)

How well did you know this?

Not at all

Perfectly

Relational Algebra consists of several groups of operations…

Relational Algebra Operations From Set Theory

Which include?

UNION , INTERSECTION, DIFFERENCE (or MINUS, – ), CARTESIAN PRODUCT ( x ).

How well did you know this?

Not at all

Perfectly

Relational Algebra consists of several groups of operations…

Binary Relational Operations

Which include?

JOIN (several variations of JOIN exist), DIVISION.

How well did you know this?

Not at all

Perfectly

Relational Algebra consists of several groups of operations…

Additional Relational Operations

Which include?

OUTER JOINS, OUTER UNION, AGGREGATE FUNCTIONS.

How well did you know this?

Not at all

Perfectly

In general, the select operation is denoted by:
σ <selection>(R)</selection>

How well did you know this?

Not at all

Perfectly

Write the query statement using algebra operations:

Select the EMPLOYEE tuples whose department number is 4.

σ DNO = 4 (EMPLOYEE)

How well did you know this?

Not at all

Perfectly

Write the query statement using algebra operations:

Select the employee tuples whose salary is greater than $30,000.

σ SALARY > 30,000 (EMPLOYEE)

How well did you know this?

Not at all

Perfectly

T/F: The number of tuples in the result of a SELECT is less than (or equal to) the number of tuples in the input relation R.

True.

How well did you know this?

Not at all

Perfectly

T/F: SELECT σ is commutative.

true. ex:
σ <condition1>(σ < condition2> (R)) = σ <condition2> (σ < condition1> (R))</condition2></condition1>

How well did you know this?

Not at all

Perfectly

T/F: The SELECT operation σ <selection>(R) produces a relation S that has the same schema (same attributes) as R.</selection>

true.

How well did you know this?

Not at all

Perfectly

Write the sql equivalent of the following:

σ SALARY > 30,000 (EMPLOYEE)

SELCT * FROM Employee WHERE Salary > 30000;

How well did you know this?

Not at all

Perfectly

Name the algebra operation:

_________ operation keeps certain columns (attributes) from a relation and discards the other columns.

PROJECT

How well did you know this?

Not at all

Perfectly

Name the algebra operation:

________ creates a vertical partitioning.

PROJECT.
(In other words, The list of specified columns (attributes) is kept in each tuple,
while the other attributes in each tuple are discarded.)

How well did you know this?

Not at all

Perfectly

Write the query for the following using relational operations:

List each employee’s first and last name and salary.

π LNAME, FNAME, SALARY (EMPLOYEE)

How well did you know this?

Not at all

Perfectly

The general form of the project operation is:
π<attribute>(R)</attribute>

How well did you know this?

Not at all

Perfectly

The project operation removes any __________ tuples.

duplicate

How well did you know this?

Not at all

Perfectly

T/F: the result of the project operation must be a set of tuples.

True, a set of tuples that do not include duplicates as Mathematical sets do not allow duplicate elements.

T/F: The number of tuples in the result of projection is always less or equal to the number of tuples in R.

True.

T/F: If the list of attributes includes a key of R, then the number of tuples in the result of PROJECT is *EQUAL* to the number of tuples in R.

True.

T/F: PROJECT is commutative.

False, PROJECT is NOT commutative. π (π (R) ) = π (R) as long as contains the attributes in .

Find the results of the following: σ(Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000)(EMPLOYEE)

Find the result of the following: πLname, Fname, Salary(EMPLOYEE).

Write the algebra operation equivalence to the following sql statement: SELECT DISTINCT Lname, Fname, Salary FROM Employee;

πLname, Fname, Salary(EMPLOYEE).

T/F: We can apply one operation at a time and create intermediate result relations, instead of writing a single relational algebra expression.

True.

Write a single relational algebra expression for the following: retrieve the first name, last name, and salary of all employees who work in department number 5.

π Fname, Lname, Salary (σ DNO = 5 (EMPLOYEE))

Write the intermediate result relations for the following: retrieve the first name, last name, and salary of all employees who work in department number 5.

DEP5_EMPS ← σ DNO=5(EMPLOYEE) RESULT ← π FNAME, LNAME, SALARY (DEP5_EMPS)

T/F: The RENAME operation is used to rename the attributes of a relation or the relation name or BOTH.

true.

Write the results of the following: TEMP ← σDno=5(EMPLOYEE) R(First_name, Last_name, Salary) ← πFname, Lname, Salary(TEMP)

In UNION operation, all _______ tuples are eliminated.

duplicate

T/F: The two operand relations R and S must be “type compatible” (or UNION compatible)

True, R and S must have same number of attributes and each pair of corresponding attributes must be type compatible (have same or compatible domains)

Write the alegbra operations for the following: retrieve the social security numbers of all employees who either work in department 5 or directly supervise an employee who works in department 5.

DEP5_EMPS ← σDNO=5 (EMPLOYEE) RESULT1 ← π SSN(DEP5_EMPS) RESULT2(SSN) ← πSUPERSSN(DEP5_EMPS) RESULT ← RESULT1 ∪ RESULT2 [The union operation produces the tuples that are in either RESULT1 or RESULT2 or both.]

T/F: In order to perform relational algerbra operations for any set operation, the operands must be type compatible.

True. (so you need type compatibilty to perform not just union but also intersect and minus)

T/F: The resulting relation for R1 ∪ R2 (also for R1 ∩ R2, or R1 – R2) has the same attribute names as the first operand relation R1 (by convention).

true.

________ operation is used to combine tuples from two relations in a combinatorial fashion.

CARTESIAN (or CROSS) PRODUCT

T/F: if R has nR tuples (denoted as |R| = nR ), and S has nS tuples, then R x S will have nR * nS tuples.

True.

T/F: The two operands do NOT have to be "type compatible” in a cartesian product operation.

true.

T/F: The resulting relation state of a cartesian product operation has one tuple for each combination of tuples—one from R and one from S.

true.

T/F: Generally, CARTESIAN PRODUCT is not and cannot be a meaningful operation.

false, it can be a meanigfuk operation when followed by other operations.

Turn the following into a meaningful cartesian product operation: FEMALE_EMPS ← σ SEX=’F’(EMPLOYEE) EMPNAMES ← π FNAME, LNAME, SSN (FEMALE_EMPS) EMP_DEPENDENTS ← EMPNAMES x DEPENDENT

ACTUAL_DEPS ← σ SSN=ESSN(EMP_DEPENDENTS) RESULT ← π FNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPS)

We can use a single ________ operation instead of a cartesian product followed by a select operation sequence, to combine two related tuples from two relations.

JOIN

The general form of a join operation on two relations R(A1, A2, . . ., An) and S(B1, B2, . . ., Bm) is:

R ⨝ S

find the following using relational alegbra operations: retrieve the name of the manager of each department.

DEPT_MGR ← DEPARTMENT ⨝MGRSSN=SSN EMPLOYEE

The general case of JOIN operation is called a ________: R ⨝theta S

theta-join

T/F: The join condition theta can be any general boolean expression of R and S, such as: R.Ai

true. Most join conditions involve one or more equality conditions “AND”ed together, for example: R.Ai=S.Bj AND R.Ak=S.Bl AND R.Ap=S.Bq

A JOIN operation, where the only comparison operator used is =, is called an ________. Ex: R.Ai=S.Bj AND R.Ak=S.Bl AND R.Ap=S.Bq

EQUIJOIN

T/F: In the result of an EQUIJOIN we always have one or more pairs of attributes that have identical values in every tuple.

true.

Another variation of JOIN called _________ — denoted by * — was created to get rid of the second (superfluous) attribute in an EQUIJOIN condition.

NATURAL JOIN

The standard definition of natural join requires that the two join attributes, or each pair of corresponding join attributes, have the same ______ in both relations.

name.

Find the result of the following relational algebra operations: project_dept ← project * dept.

explanation: a natural join happens by the only attribute with the same name, which is DNUMBER. An implicit join condition is created based on this attribute.. DEPARTMENT.DNUMBER=DEPT_LOCATIONS.DNUMBER [So in other words what happens in natural join is that an implicit condition is created on the attribute that they share the same name with on both relations.]

Find the result of the following relational algebra operations: dept_locs ← department * dept_locations

Write the implicit join condition that will be created and the result in the following natural join: Q ← R(A,B,C,D) * S(C,D,E)

implicit join condition: R.C=S.C AND R.D.=S.D Result keeps only one attribute of each such pair: Q(A,B,C,D,E)

The set of operations including SELECT s, PROJECT p , UNION È, DIFFERENCE - , RENAME r, and CARTESIAN PRODUCT X is called a _______ because any other relational algebra expression can be expressed by a combination of these five operations. Ex: R ∩ S = (R ∪ S ) – ((R - S) ∪ (S - R)) or R ⨝S = σ (R X S)

complete set.

T/F: For a tuple t to appear in the result T of the DIVISION operation, the values in t must appear in R in combination with one tuple in S.

false, with EVERY tuple in S.

Find the following using relational algebra operations: Retrieve the names of employees who work on **all** the projects that ‘John Smith’ works on.

1-list of project numbers that ‘John Smith’ works on in the intermediate relation SMITH_PNOS. 2-create a relation that includes a tuple in the intermediate relation SSN_PNOS. 3-Finally, apply the DIVISION operation to the two relations, which gives the desired employees’ Social Security numbers. 3-Finally, apply the DIVISION operation to the two relations, which gives the desired employees’ Social Security numbers. SMITH ← σFname=‘John’ AND Lname=‘Smith’(EMPLOYEE) SMITH_PNOS ← πPno(WORKS_ON⨝ Essn=Ssn SMITH) SSN_PNOS ← πEssn, Pno(WORKS_ON) SSNS(Ssn) ← SSN_PNOS ÷ SMITH_PNOS RESULT ← πFname, Lname(SSNS * EMPLOYEE)

Find the results of the following: SSN_PNOS ÷ SMITH_PNOS

Find the result of the following: T ← R ÷ S

_________ operation produces a relation R(X) that includes all tuples t[X] in R1(Z) that appear in R1 in combination with every tuple from R2(Y), where Z = X ∪ Y

DIVISION

__________ is an internal data structure to represent a query.

Query Tree

T/F: Query Tree is a standard technique for estimating the work involved in executing the query, the generation of intermediate results, and the optimization of execution.

true.

In a Query Tree, ________ [left side] nodes stand for operations like selection, projection, join, renaming, division, etc. ______ [right side] nodes represent base relations.

internal, leaf.

Draw the Query Tree for the following: For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, address, and birth date.

T/F: duplicates are eliminated when an aggregate function is applied.

False, they are NOT eliminated. if you want to eliminate duplicates remember to use DISTINCT.

T/F: NULL values are considered in aggregation.

False, they are NOT.

T/F: In NATURAL JOIN and EQUIJOIN, Tuples with null in the join attributes are also eliminated.

true.

Write the following using relational algebra operations: For each department, retrieve the DNO, COUNT SSN, and AVERAGE SALARY.

DNO ℱ COUNT SSN, AVERAGE Salary (EMPLOYEE)

T/F: Grouping can be combined with Aggregate Functions.

True. It is done by adding the grouping attribute to the left of the aggregate function symbol. ex: DNO ℱ COUNT SSN, AVERAGE Salary (EMPLOYEE)

A set of operations, called __________ can be used when we want to keep all the tuples in R, or all those in S, or all those in both relations in the result of the join, regardless of whether or not they have matching tuples in the other relation.

OUTER JOIN

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

LEFT OUTER JOIN. A similar operation, right outer join, keeps every tuple in the second or right relation S in the result

A third operation, ____________ , keeps all tuples in both the left and the right relations when no matching tuples are found, padding them with null values as needed.

FULL OUTER JOIN

Write the following using relational algebra operations: list of all employee names as well as the name of the departments they manage if they happen to manage a department.

TEMP ← (EMPLOYEE Ssn=Mgr_ssnDEPARTMENT) RESULT ← πFname, Minit, Lname, Dname(TEMP)

The_________ operation was developed to take the union of tuples from two relations if the relations are not type compatible.

OUTER UNION Example: An outer union can be applied to two relations whose schemas are STUDENT(Name, SSN, Department, Advisor) and INSTRUCTOR(Name, SSN, Department, Rank) Tuples from the two relations are matched based on having the same combination of values of the shared attributes— Name, SSN, Department. If a student is also an instructor, both Advisor and Rank will have a value; otherwise, one of these two attributes will be null. The result relation STUDENT_OR_INSTRUCTOR will have the following attributes: STUDENT_OR_INSTRUCTOR (Name, SSN, Department, Advisor, Rank)

T/F: The OUTER UNION operation will take the union of tuples in two relations R(X, Y) and S(X, Z) that are partially compatible, meaning that only some of their attributes, say X, are type compatible.

True. The attributes that are type compatible are represented only once in the result, and those attributes that are not type compatible from either realtion are also kept in the result relation T(X, Y, Z).

Find the following using relational algebra operations: Retrieve the name and address of all employees who work for the Research’ department.

Find the following using relational algebra operations: Retrieve the names of employees who have no dependents.

Write the following as a single expression:

Rewrite the following as a single expression: