The exercises of dr. Heckel explained in a succinct way
Exercises on Sales
materi latihan Java Fundamental dan kunci jawaban.Full description
materi latihan Java Fundamental dan kunci jawaban.
jarret exercises pianoDescription complète
examen telmex algebraDescripción completa
Descripción completa
Full description
algebraDescripción completa
Descripción: curso algebra
Descripción: algebra
algebraFull description
Descripción: Curso de algebra
Descripción: practicar e ingresaras
Descripción completa
06-02525 Databases 2 Spring Semester 2011
The University of Birmingham School of Computer Science c Achim Jung & Uday Reddy & Ata Kab´an an 2011
Exercise Exercise Sheet 4 Exercise 1: Outer Joins Given below are two tables T 1 and T 2 . T 1 :
A
B
C
1 4 7
2 5 8
3 6 9
T 2 :
B
C
D
2 2 2 6
3 3 6 7
10 11 10 12
a. Calculate the (natural) inner join T 1 ⊲⊳ T 2 of the two tables. T 1 and T 2 are dangling tuples in this calculation? b. Which Which tuples tuples of T ◦
c. Calculate the (natural) (natural) outer join join of the two tables. tables. (This (Thi s is denoted T 1 ⊲⊳ T 2 .) d. Which tuples of the outer join are included if we are only interested in the left outer outer join?
Exercise 2: Equations of relational algebra Consider the following equations concerning the interaction between the relational algebra operators ∩ (intersection), ⊲⊳ (natural join), and π (projection). Which of them are always true, and which of them may fail? In the first case, your answer should consist consist of a justification justification for your belief (see overleaf overleaf for an example). example). In the second case, you should indicate indicate an example where the equation equation fails. fails. Where an equation equation is true, true, say which of the two sides would generally evaluate faster in a database management system. ⊲⊳ T = R ⊲⊳ T ∩ S ⊲⊳ ⊲⊳ T a. (R ∩ S ) ⊲⊳
b. πN (R ∩ S ) = π N (R) ∩ πN (S ) (where N is some set of attribute names from the schema of R and S ) c. σC ∧C (R × S ) = σ C (R) × σC (S ) where the condition C 1 only mentions attributes of R and C 2 only mentions attributes of S . 1
2
1
2
d. R × (S − T ) = R × S − R × T
Exercise 3: Understanding relational algebra Explain each of the following relational algebra expressions in plain English, and translate them into SQL. ⊲⊳ courses)) − πname (σlevel=1 (courses)) a. πname (σnumbers >100 (lecturing ⊲⊳ ⊲⊳ lecturing ⊲⊳ ⊲⊳ courses)) ∩ b. π{lastname ,name } (σyear =1999 (staff ⊲⊳ π{lastname ,name } (σlevel=2 (staff ⊲⊳ ⊲⊳ lecturing ⊲⊳ ⊲⊳ courses))
Exercise 4: From SQL to relational algebra Translate the following SQL queries into relational algebra. a.
SELECT c.name FROM lecturing l1, lecturing l2, courses c WHERE l1.cid=l2.cid AND l1.cid=c.cid AND l1.sid=l2.sid AND l1.year=1999 AND l2.year=2000;
b.
SELECT s.lastname FROM staff s WHERE s.sid NOT IN (SELECT l.sid FROM lecturing l);
Exercise 5: Outer joins in SQL The problem “What was the percentage of first class marks overall in 2003?” (from Exercise 4) cannot be solved using normal inner joins unless nested queries are used. Can it be solved using outer joins?
Example justification for algebraic properties Consider the associativity law (R ⊲⊳ S ) ⊲⊳ T = R ⊲⊳ (S ⊲⊳ T )). We can justify the fact that it is true as follows. In general, the column names of a join table R ⊲⊳ S are given by c ol(R ⊲⊳ S ) = c ol(R) ∪ c ol (S ). The rows r ows (R ⊲⊳ S ) include all and only those combinations of r ows (R) and r ows (S ) which have matching values in c ol (R) ∩ c ol(S ). We first check the two tables have the same column names. c ol((R ⊲⊳ S ) ⊲⊳ T ) = (c ol (R) ∪ c ol(S )) ∪ c ol (T ). c ol (R ⊲⊳ ( S ⊲⊳ T )) = c ol (R ) ∪ (c ol (S ) ∪ c ol (T )). These two are equal by the associativity of ∪. As mentioned above, the r ows (R ⊲⊳ S ) are the combinations of r ows (R) and r ows(S ) which have matching values in c ol(R) ∩ c ol (S ). Hence r ows((R ⊲⊳ S ) ⊲⊳ T ) consists of the combinations of r ows (R ⊲⊳ S ) and r ows (T ) which have matching values in ( c ol (R ) ∪ c ol (S )) ∩ c ol (T ) = (c ol (R ) ∩ c ol (T )) ∪ (c ol (S ) ∩ c ol(T )). That means that r ows((R ⊲⊳ S ) ⊲⊳ T ) consists of the combinations of r ows (R), r ows(S ) and r ows (T ) which have matching values in c ol (R) ∩ c ol(S ), c ol(R) ∩ c ol (T ) and c ol(S ) ∩ c ol (T ). Doing a similr analysis for the right hand side, we find that the rows of R ⊲⊳ ( S ⊲⊳ T ) are the combinations of the rows of R , S and T which have matching values in c ol(R) ∩ c ol(S ), c ol(R) ∩ c ol(T ) and c ol(S ) ∩ c ol(T ). Since both the sides have the same set of column names and the same set of rows, they are equal as tables.