Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

IS230 > Chapter 8 > Flashcards

Chapter 8 Flashcards

1
Q

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

A

Relational algebra

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

True.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

True.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

“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 ______.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

F, all objects in relational algebra are relations.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

True.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

A sequence of relational algebra operations forms a ______________.

A

relational algebra expression

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

True.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Relational Algebra consists of several groups of operations…

1.Unary Relational Operations, includes:
SELECT
PROJECT
RENAME

What are their symbols?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Relational Algebra consists of several groups of operations…

  1. Relational Algebra Operations From Set Theory

Which include?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Relational Algebra consists of several groups of operations…

  1. Binary Relational Operations

Which include?

A

JOIN (several variations of JOIN exist), DIVISION.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Relational Algebra consists of several groups of operations…

  1. Additional Relational Operations

Which include?

A

OUTER JOINS, OUTER UNION, AGGREGATE FUNCTIONS.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Write the query statement using algebra operations:

Select the EMPLOYEE tuples whose department number is 4.

A

σ DNO = 4 (EMPLOYEE)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Write the query statement using algebra operations:

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

A

σ SALARY > 30,000 (EMPLOYEE)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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.

A

True.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

T/F: SELECT σ is commutative.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

true.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Write the sql equivalent of the following:

σ SALARY > 30,000 (EMPLOYEE)

A

SELCT * FROM Employee WHERE Salary > 30000;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Name the algebra operation:

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

A

PROJECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Name the algebra operation:

________ creates a vertical partitioning.

A

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?
1
Not at all
2
3
4
5
Perfectly
22
Q

Write the query for the following using relational operations:

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

A

π LNAME, FNAME, SALARY (EMPLOYEE)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

The project operation removes any __________ tuples.

A

duplicate

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

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

A

True.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

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.

A

True.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

T/F: PROJECT is commutative.

A

False, PROJECT is NOT commutative.
π <list1> (π <list2> (R) ) = π <list1> (R) as long as <list2> contains the attributes in <list1>.</list1></list2></list1></list2></list1>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Find the results of the following:

σ(Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000)(EMPLOYEE)

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Find the result of the following:

πLname, Fname, Salary(EMPLOYEE).

A
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Write the algebra operation equivalence to the following sql statement:

SELECT DISTINCT Lname, Fname, Salary
FROM Employee;

A

πLname, Fname, Salary(EMPLOYEE).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

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

A

True.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

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.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

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.

A

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

35
Q

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

A

true.

36
Q

Write the results of the following:

TEMP ← σDno=5(EMPLOYEE)
R(First_name, Last_name, Salary) ← πFname, Lname, Salary(TEMP)

A
37
Q

In UNION operation, all _______ tuples are eliminated.

A

duplicate

38
Q

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

A

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)

39
Q

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.

A

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.]

40
Q

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

A

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

41
Q

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).

A

true.

42
Q

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

A

CARTESIAN (or CROSS) PRODUCT

43
Q

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.

A

True.

44
Q

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

A

true.

45
Q

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.

A

true.

46
Q

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

A

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

47
Q

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

A

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

48
Q

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.

A

JOIN

49
Q

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

A

R ⨝ <join>S</join>

50
Q

find the following using relational alegbra operations:

retrieve the name of the manager of each department.

A

DEPT_MGR ← DEPARTMENT ⨝MGRSSN=SSN EMPLOYEE

51
Q

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

A

theta-join

52
Q

T/F: The join condition theta can be any general boolean expression of R and S, such as:
R.Ai<S.Bj AND (R.Ak=S.Bl OR R.Ap<S.Bq)

A

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

53
Q

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

A

EQUIJOIN

54
Q

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

A

true.

55
Q

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

A

NATURAL JOIN

56
Q

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.

A

name.

57
Q

Find the result of the following relational algebra operations:

project_dept ← project * dept.

A

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.]

58
Q

Find the result of the following relational algebra operations:

dept_locs ← department * dept_locations

A
59
Q

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)

A

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)

60
Q

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 ⨝<join>S = σ <join> (R X S)</join></join>

A

complete set.

61
Q

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.

A

false, with EVERY tuple in S.

62
Q

Find the following using relational algebra operations:

Retrieve the names of employees who work on all the projects that ‘John Smith’ works on.

A

1-list of project numbers that ‘John Smith’ works on in the intermediate relation
SMITH_PNOS.
2-create a relation that includes a tuple <Pno, Essn> 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)

63
Q

Find the results of the following:

SSN_PNOS ÷ SMITH_PNOS

A
64
Q

Find the result of the following:

T ← R ÷ S

A
65
Q

_________ 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

A

DIVISION

66
Q

__________ is an internal data structure to represent a query.

A

Query Tree

67
Q

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.

A

true.

68
Q

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

A

internal, leaf.

69
Q

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.

A
70
Q

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

A

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

71
Q

T/F: NULL values are considered in aggregation.

A

False, they are NOT.

72
Q

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

A

true.

73
Q

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

A

DNO ℱ COUNT SSN, AVERAGE Salary (EMPLOYEE)

74
Q

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

A

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

75
Q

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.

A

OUTER JOIN

76
Q

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.

A

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

77
Q

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.

A

FULL OUTER JOIN

78
Q

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.

A

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

79
Q

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

A

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)

80
Q

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.

A

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).

81
Q

Find the following using relational algebra operations:

Retrieve the name and address of all employees who work for the Research’ department.

A
82
Q

Find the following using relational algebra operations:

Retrieve the names of employees who have no dependents.

A
83
Q

Write the following as a single expression:

A
84
Q

Rewrite the following as a single expression:

A

IS230 (13 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions