## Pré-visualização | Página 3 de 4

Properties s Select is distributive over the binary Boolean operations, that is, s(A = a)(r g s) ≡ s(A = a) (r) g s(A = a) (s); where g = È, Ç and r and s are relations over R Proof: We want to prove s(A = a)(r Ç s) ≡ s(A = a)(r) Ç s(A = a)(s) s(A = a)(r Ç s) = s(A = a) ({t|t Î r and t Î s}) = {t’ Î ({t|t Î r and t Î s}) t'(A) = a} = {t’’|t’’Î r and t’’(A) = a} Ç { t|t Î s and t(A) = a} = s (A= a) ({t’’|t’’Î r}) Ç s (A= a) ({t|t Îs}) = s (A= a) (r) Ç s (A= a) (s) CS470: Introduction to Database Systems V. Kumar 46 Relational Algebra Operations Example: s(FN=‘Susan’)(Student Ç Prof) = ? s(FN=‘Susan’)(Student) Ç s(FN=‘Susan’)(Prof) =? CS470: Introduction to Database Systems V. Kumar 47 Relational Algebra Operations Projection P A unary (monadic) operator on a relation r that creates a new relation r’ that is a vertical subset of the r Let X be a subset of the attributes of R(A1, A2, …, An). Let X be (A1, A3, A5). The result of the projection of r(R) onto X is a new relation r'(X), which obtained by extracting the columns (attributes) from R which are in X, i.e., A1, A2, and A5. At the end of this process the relation r'(X) may contain duplicate tuples, which are removed by PROJECT operation. Also known as duplicate elimination. CS470: Introduction to Database Systems V. Kumar 48 Relational Algebra Operations Format of P P <list of attributes of the operand relation separated by ","> (r) Example P Age(Emp) Result: Age 21 22 19 P Age,SSN (Emp) Result: Age SSN 21 123456789 22 113456789 19 122456789 CS470: Introduction to Database Systems V. Kumar 49 Relational Algebra Operations CS470: Introduction to Database Systems V. Kumar 50 Relational Algebra Operations CS470: Introduction to Database Systems V. Kumar 51 Relational Algebra Operations Properties of P If two P s are applied on a relation in a row, then the latter subsumes the former Proof If YÎ XÎ R then P Y(P X (r)) = P Y(r). Similarly, for a string of P s only the outermost P counts. If X1 Î X2 Î ... Î Xm Î R then P x1(P x2 ( . . . (P xm(r)) . . .)) = P x1(r). Point to remember: YÎ XÎ R or X1 Î X2 Î ... Î Xm Î R CS470: Introduction to Database Systems V. Kumar 52 Relational Algebra Operations Properties of P P commutes with s when the attribute(s) in the predicate(s) of are among the attributes in the set onto which the P is being taken. That is if AÎ X, X Í R and r is a relation on R, then P X (s(A = a)(r)) = s(A = a)(P X (r)) Proof P X(s(A = a) (r)) = P X ({t Î r|t(A) = a}) = {t'(X)|t' Î {t Î r|t(A) = a}} = {t(X)|t Î r and t(A) = a} = {t(X) t Î r | t(A) = a} = {t Î r | t(A) = a and t(X) |t Î r } = s(A = a)({t(X)|t Î r}) = s(A = a)(P X(r)) CS470: Introduction to Database Systems V. Kumar 53 SELECT with PROJECTION and RENAME CS470: Introduction to Database Systems V. Kumar 54 Boolean Operations We will use the following relations in the discussion of these operators. r(A B C) s(A B C) a1 b1 c1 a1 b2 c1 a1 b2 c1 a2 b2 c1 a2 b1 c2 a2 b2 c2 Union compatibility rule: two relations are union compatible iff they have the same degree and the domain of corresponding attributes are the same, but their names may be different (synonyms) 2 relations R(A1, A2…An) and S(B1, B2…Bn) are union compatible if they have same degree n and if dom(Ai) = dom (Bi) for 1<=i<=n. Boolean operators require that operand relations must be union compatible. CS470: Introduction to Database Systems V. Kumar 55 Boolean Operations Intersection Ç A binary operator. It creates a new relation from the two (operand) relations. The new relation contains the tuples, which are common in both the operand relations. Example r Ç s = r(A B C) s(A B C) a1 b1 c1 a1 b2 c1 a1 b2 c1 Ç a2 b2 c1 a2 b1 c2 a2 b2 c2 = q(A B C) a1 b2 c1 Class task: Prove that r Ç s = r - (r - s) CS470: Introduction to Database Systems V. Kumar 56 Boolean Operations Union È A binary operator. It creates a new relation from the two (operand) relations that contains the common and non-common tuples of the operand relations. Example r È s = r(A B C) s(A B C) = q(A B C) a1 b1 c1 a1 b2 c1 a1 b1 c1 a1 b2 c1 È a2 b2 c1 a1 b2 c1 a2 b1 c2 a2 b2 c2 a2 b1 c2 a2 b2 c1 a2 b2 c2 CS470: Introduction to Database Systems V. Kumar 57 Boolean Operations Difference - A binary operator. It creates a new relation from the two (operand) relations. The new relation contains the tuples, which are in the left hand relation but not in the right hand relation. Note that r - s ¹ s - r Example r - s = r(A B C) s(A B C) = q(A B C) a1 b1 c1 a1 b2 c1 a1 b1 c1 a1 b2 c1 - a2 b2 c1 a2 b1 c2 a2 b1 c2 a2 b2 c2 Class task. Prove that r - s ¹ s - r CS470: Introduction to Database Systems V. Kumar 58 Boolean Operations CS470: Introduction to Database Systems V. Kumar 59 Boolean Operations CS470: Introduction to Database Systems V. Kumar 60 Boolean Operations CS470: Introduction to Database Systems V. Kumar 61 Boolean Operations CS470: Introduction to Database Systems V. Kumar 62 Boolean Operations CS470: Introduction to Database Systems V. Kumar 63 Relational Algebra Operations-Set Theory CARTESIAN PRODUCT CS470: Introduction to Database Systems V. Kumar 64 Relational Algebra Operations-Set Theory CARTESIAN PRODUCT CS470: Introduction to Database Systems V. Kumar 65 Relational Algebra Operations-Set Theory CARTESIAN PRODUCT CS470: Introduction to Database Systems V. Kumar 66 Relational Algebra Operations-Set Theory CARTESIAN PRODUCT CS470: Introduction to Database Systems V. Kumar 67 Join Operations Join A Join combines two relations on a common attribute. In order to combine two relations there must at least be one attribute with common domain between both the relations but their names may or may not be the same. For example, one database may call income as salary and other as monthly income, however, their domains are the same. The result of a join is a new relation, which contains those tuples that satisfy the join condition CS470: Introduction to Database Systems V. Kumar 68 Join Operations CS470: Introduction to Database Systems V. Kumar 69 Join Operations CS470: Introduction to Database Systems V. Kumar 70 Join Operations Is this what we wanted? Name of Manager? How to get? CS470: Introduction to Database Systems V. Kumar 71 Join Operations CS470: Introduction to Database Systems V. Kumar 72 Join Operations Join types There are three types of join Equijoin Natural join (*) Theta join () CS470: Introduction to Database Systems V. Kumar 73 Join Operations CS470: Introduction to Database Systems V. Kumar 74 Join Operations CS470: Introduction to Database Systems V. Kumar 75 Join Operations Equijoin steps Get cross product of two relations Apply the join condition on the result of the cross product Example Get cross product. r (A B C) s (M B) a1 b1 c1 m1 b1 a2 b2 c2 m2 b2 r s = A B C M B a1 b1 c1 m1 b1 a1 b1 c1 m2 b2 a2 b2 c2 m1 b1 a2 b2 c2 m2 b2 Common domain attribute = B CS470: Introduction