Equivalence Rules (Cont.) 8.The projection operation distributes over the theta join operation as follows: (a)if 0 involves only attributes from L1 L2: Πu,(E,凶gE2)=(Π，(E)凶g(Π，(E2)》 (b)Consider a join E1Ne E2. Let L1 and L2 be sets of attributes from E1 and E2, respectively. Let L3 be attributes of E that are involved in join condition 0, but are not in L1L2,and let L be attributes of E2 that are involved in join condition 0, but are not in L1L2. Πu,(E,凶aE2)=Π，u,(Π4u(E)凶Mg(Π，uL(E2)》 Database System Concepts-6th Edition 1.12 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 1.12 ©Silberschatz, Korth and Sudarshan th Edition Equivalence Rules (Cont.) 8. The projection operation distributes over the theta join operation as follows: (a) if involves only attributes from L1 L2 : (b) Consider a join E1 E2 . Let L1 and L2 be sets of attributes from E1 and E2 , respectively. Let L3 be attributes of E1 that are involved in join condition , but are not in L1 L2 , and let L4 be attributes of E2 that are involved in join condition , but are not in L1 L2 . ( ) ( ( )) ( ( )) L1L2 E1 E2 = L1 E1 L2 E2 ( ) (( ( )) ( ( ))) L1L2 E1 E2 L1L2 L1L3 E1 L2 L4 E2 =
Equivalence Rules (Cont.) 9.The set operations union and intersection are commutative EE2=E20E1 E1∩E2=E2∩E1 (set difference is not commutative). 10.Set union and intersection are associative. (E10E2)UE3=E10(E2E3) (E1∩E2)∩E3=E1∩(E2∩E3) 11.The selection operation distributes over and-. 0(E1-E2)=O(E)-0(E2) and similarly for and in place of Also: O6(E1-E2)=O(E1)-E2 and similarly for in place of -but not for 12.The projection operation distributes over union Π(E1UE2)=(Π(E1)U(Π(E2) Database System Concepts-6th Edition 1.13 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 1.13 ©Silberschatz, Korth and Sudarshan th Edition Equivalence Rules (Cont.) 9. The set operations union and intersection are commutative E1 E2 = E2 E1 E1 E2 = E2 E1 (set difference is not commutative). 10. Set union and intersection are associative. (E1 E2 ) E3 = E1 (E2 E3 ) (E1 E2 ) E3 = E1 (E2 E3 ) 11. The selection operation distributes over , and –. (E1 – E2 ) = (E1 ) – (E2 ) and similarly for and in place of – Also: (E1 – E2 ) = (E1 ) – E2 and similarly for in place of –, but not for 12. The projection operation distributes over union L (E1 E2 ) = (L (E1 )) (L (E2 ))
Exercise n Create equivalence rules involving I The group by/aggregation operation I Left outer join operation Database System Concepts-6th Edition 1.14 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 1.14 ©Silberschatz, Korth and Sudarshan th Edition Exercise n Create equivalence rules involving l The group by/aggregation operation l Left outer join operation
Transformation Example:Pushing Selections Query:Find the names of all instructors in the Music department,along with the titles of the courses that they teach Πname,te(dept name=1 Music” (instructor凶(teaches凶Πcourse_id,tite(course)》 Transformation using rule 7a. ☐name,tte(dept name=Music"(instructor))凶 (teaches凶IΠcourse_ia,tite(course)》 Performing the selection as early as possible reduces the size of the relation to be joined. Database System Concepts-6th Edition 1.15 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 1.15 ©Silberschatz, Korth and Sudarshan th Edition Transformation Example: Pushing Selections Query: Find the names of all instructors in the Music department, along with the titles of the courses that they teach name, title(dept_name= “Music” (instructor (teaches course_id, title (course)))) Transformation using rule 7a. name, title((dept_name= “Music” (instructor)) (teaches course_id, title (course))) Performing the selection as early as possible reduces the size of the relation to be joined
Example with Multiple Transformations Query:Find the names of all instructors in the Music department who have taught a course in 2009,along with the titles of the courses that they taught IIname,titledept_name=Music"year=2009 (instructor凶(teaches凶Πcourse_ia,tie(course))） Transformation using join associatively(Rule 6a): IIname,title(dept_name=Music"gear=2009 ((instructor凶teaches)XIΠcourse_ia,te(course)》 Second form provides an opportunity to apply the "perform selections early"rule,resulting in the subexpression deptname=Music"(instructor)year=2009(teaches) Database System Concepts-6th Edition 1.16 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 6 1.16 ©Silberschatz, Korth and Sudarshan th Edition Example with Multiple Transformations Query: Find the names of all instructors in the Music department who have taught a course in 2009, along with the titles of the courses that they taught name, title(dept_name= “Music”year = 2009 (instructor (teaches course_id, title (course)))) Transformation using join associatively (Rule 6a): name, title(dept_name= “Music”gear = 2009 ((instructor teaches) course_id, title (course))) Second form provides an opportunity to apply the “perform selections early” rule, resulting in the subexpression dept_name = “Music” (instructor) year = 2009 (teaches)