Qinglian
• Home Page
• Assignments Due • Progress Report
• Handouts
• Tutorials
• Homeworks
• Lab Projects
• Log Out
Help
Copyright © 2007-2021 Gradiance Corporation.
Gradiance Online Accelerated Learning
Spring-21 HW4
Number of questions: 7 Positive points per question: 3.0 Negative points per question: 1.0
1. Suppose relation R(X, Y, Z) has the tuples:
Compute the bag union of the following four expressions, each of which is the bag projection (PI) of a grouping (GAMMA) operation using renaming (RHO):
1. PI_A(RHO_R{X, Y, A}(GAMMA_{X, Y, AVG(Z)}(R))) 2. PI_A(RHO_R{Y, A}(GAMMA_{Y, SUM(Z)}(R)))
3. PI_A(RHO_R{X, A}(GAMMA_{X, MIN(Z)}(R)))
4. PI_A(RHO_R{Y, A}(GAMMA_{Y, MAX(X)}(R)))
Demonstrate that you have computed this bag correctly by identifying, from the list below, the correct count of occurrences for one of the elements.
a) 3 appears exactly three times.
b) 2 appears exactly once.
c) 4 appears exactly two times.
d) 5 appears exactly three times.
2. Suppose relation R(L, M, N) has the tuples:
X
Y
Z
2
6
5
5
6
1
9
0
1
9
0
7
5
6
5
L
M
N
1
1
2
2
1
1
2
3
2
1
1
1
3
2
1
1
1
3
Using bag projection and intersection, compute Π(L,M)(R)∩ρS(L,M) (Π(M,N)(R)). Note that the renaming is only to give the two projections
the same schema. Which of the following is true about the tuples that appear in the result?
a) (2, 3) does not appear in the result.
b) (3, 2) appears twice in the result.
c) (1, 2) appears once in the result.
d) (1, 3) appears once in the result.
3. Suppose relation R(A, B, C, D) has the tuples:
A
B
C
D
2
2
3
3
3
2
4
4
3
3
5
2
3
3
2
5
4
4
3
3
4
4
4
4
5
3
5
2
3
2
2
5
5
2
3
3
5
2
4
4
Using bag projection and difference, compute πA,B(R) – ρS( A,B)(πC,D(R)).
Note that the remaining is only to give the two projections the same schema.
Which of the following is true about the tuples that appear in the result?
a) (5, 2) appears once in the result.
b) (3, 2) appears once in the result.
c) (5, 3) appears once in the result.
d) (4, 4) appears -1 times in the result.
4. Suppose relation R1(L, M) has the tuples: R1
L
M
7
f
3
d
4
e
6
d
1
a
9
b
3
j
and suppose relation R2(M, N) has the tuples: R2
Identify which of the following (L, M, N) tuples can result from the left natural outer-join of R1 and R2.
a) (4, e, null)
b) (null, null, null)
c) (4, e, 3)
d) (7, f, 2)
M
N
c
3
d
2
b
6
i
5
e
3
5. Here are three relations, R1(m, n), R2(m, n), and R3(m, n). Their current values are:
R1
R2
R3
Compute the result of the following query:
SELECT R1.m, R1.n, R2.n, R3.n FROM R1, R2, R3 WHERE R1.n = R2.m AND R2.n <> R3.n AND R3.m <> b;
Identify in the list below the true statement about whether or not a tuple appears in the output and how many times it appears in the output.
a) (b, a, a, b) appears once.
b) (b, b, a, b) does not appear.
c) (a, b, b, b) appears once.
d) (a, b, a, b) appears twice.
6. Suppose relation R(a, b, c, d, e) currently has the tuples: R
Which of the following tuples is in the generalized projection PROJ_{b, d – a, 3 * e}(R)?
a) (8, -4,7)
b) (1, 1, 9)
c) (2, 1, 4, 3, 3)
d) (3, 3, 4)
7. Consider the relational database shown below: student(studentname, street, city) study(studentname, universityname, SAT)
m
n
a
a
a
b
b
a
b
b
m
n
a
a
a
b
b
a
b
b
m
n
a
a
a
b
b
a
b
b
a
b
c
d
e
1
4
3
7
3
2
1
4
3
3
5
3
1
2
2
3
8
5
1
7
university(universityname, city) tutor(tutorname, personname)
Identify the correct relational algebra expression for the queries shown below.
Assume the following notations: Π – Projection
∞ – Natural Join
σ – Selection
× – Products
a) Find the names of all students in this database who live in the
same city as the university for which they study. σstudentname
(student ∞ (study × university))
b) Find the names of all students whose SAT score is greater than the SAT score for every student of NC State University. Πstudentname(
study) – (σstudy.studentname (study ∞ study.SAT <=study2.SAT) ρstudy2(study))
c) Find the names of all students in this database who live in the same city as the university for which they study. Πstudentname (student ∞ study ∞ university)
d) Find the names and cities of residence of all students who study at NC State University. Πstudentname, city(student ∞ (σuniversityname = "NC State University" (university)))
Submit Homework