Lecture 9 09/14/15 Instructor: Yu-San Lin yusan@psu.edu - PowerPoint PPT Presentation

CMPSC431W: ¡Database ¡Management ¡Systems ¡ Lecture ¡9 ¡ 09/14/15 ¡ Instructor: ¡Yu-­‑San ¡Lin ¡ ¡ yusan@psu.edu ¡ ¡ Course ¡Website: ¡hCp://www.cse.psu.edu/~yul189/cmpsc431w ¡ Slides ¡based ¡on ¡McGraw-­‑Hill ¡& ¡Dr. ¡Wang-­‑Chien ¡Lee ¡ ¡ 1 ¡ ¡

Outline ¡ • Chapter ¡3 ¡ – Review ¡exercise ¡from ¡last ¡Tme ¡ – View ¡ – Quiz ¡3 ¡ • Chapter ¡4 ¡ – RelaTonal ¡algebra ¡ • SelecTon ¡& ¡projecTon ¡ • Set ¡operaTons ¡ 2 ¡

How ¡would ¡you ¡implement ¡this? ¡ CREATE ¡TABLE ¡Dependents ¡( ¡ name ¡ ¡pname ¡CHAR(20), ¡ ¡age ¡INTEGER, ¡ ssn ¡ lot ¡ pname ¡ age ¡ ¡policyid ¡INTEGER, ¡ ¡PRIMARY ¡KEY ¡(________________), ¡ ¡FOREIGN ¡KEY ¡(________) ¡ ¡ Employees ¡ Dependents ¡ ¡REFERENCES ¡Policies, ¡ ¡ ¡_________________); ¡ Purchas Benefici er ¡ ary ¡ CREATE ¡TABLE ¡Policies ¡( ¡ ¡policyid ¡INTEGER, ¡ Policies ¡ ¡cost ¡REAL, ¡ ¡ssn ¡CHAR ¡(11) ¡________, ¡ ¡PRIMARY ¡KEY ¡(policyid), ¡ policyi ¡FOREIGN ¡KEY ¡(ssn) ¡ ¡ cost ¡ d ¡ ¡REFERENCES ¡Employees, ¡ ¡ 3 ¡ ¡_________________); ¡

Views ¡ • View: ¡a ¡table ¡whose ¡rows ¡are ¡not ¡explicitly ¡stored ¡ in ¡the ¡database ¡but ¡are ¡computed ¡as ¡needed ¡ from ¡a ¡view ¡definiTon ¡ ¡ ¡ ¡CREATE ¡VIEW ¡B-­‑Students ¡(name, ¡sid, ¡course) ¡ ¡ ¡AS ¡SELECT ¡S.sname, ¡S.sid, ¡Ecid ¡ ¡ ¡FROM ¡Students ¡S, ¡Enrolled ¡E ¡ ¡ ¡WHERE ¡S.sid ¡= ¡E.studid ¡AND ¡E.grade ¡= ¡‘B’; ¡ ¡ • Can ¡be ¡used ¡to ¡present ¡necessary ¡informaTon, ¡ while ¡hiding ¡details ¡in ¡underlying ¡relaTon(s). ¡ 4 ¡

CHAPTER ¡4: ¡RELATIONAL ¡ALGEBRA ¡ 5 ¡

Formal ¡RelaTonal ¡Languages ¡ • Two ¡mathemaTcal ¡Query ¡Language ¡form ¡the ¡ basis ¡for ¡“real” ¡languages ¡(e.g., ¡SQL), ¡and ¡for ¡ implementaTon: ¡ – RelaTonal ¡Algebra: ¡_________, ¡very ¡useful ¡for ¡ represenTng ¡execuTon ¡plans ¡ – RelaTonal ¡Calculus: ¡_________, ¡lets ¡users ¡ describe ¡what ¡they ¡want, ¡rather ¡than ¡how ¡to ¡ compute ¡it ¡ 6 ¡

Preliminaries ¡ • A ¡query ¡is ¡applied ¡to ¡_____________ ¡ • The ¡result ¡of ¡a ¡query ¡is ¡a ¡_____________ ¡ • PosiTonal ¡v.s. ¡name-­‑field ¡notaTon: ¡ – PosiTonal ¡notaTon: ¡easier ¡for ¡formal ¡definiTons ¡ – Named-­‑field ¡notaTon: ¡more ¡readable ¡ 7 ¡

Example ¡ • Schema ¡example: ¡ Sailors( sid : ¡integer, ¡ sname : ¡string, ¡ ra)ng : ¡integer, ¡ age : ¡real) ¡ Boats( bid : ¡integer, ¡ bname : ¡string, ¡ color : ¡string) ¡ Reserves( sid : ¡integer, ¡ bid : ¡integer, ¡ day : ¡date) ¡ Instance ¡S1 ¡of ¡Sailors ¡ Instance ¡S2 ¡of ¡Sailors ¡ Instance ¡R1 ¡of ¡Reserves ¡ sid ¡ sname ¡ ra9ng ¡ age ¡ sid ¡ sname ¡ ra9ng ¡ age ¡ sid ¡ bid ¡ day ¡ 22 ¡ DusTn ¡ 7 ¡ 45.0 ¡ 28 ¡ yuppy ¡ 9 ¡ 35.0 ¡ 22 ¡ 101 ¡ 10/10/96 ¡ 31 ¡ Lubber ¡ 8 ¡ 55.5 ¡ 31 ¡ Lubber ¡ 8 ¡ 55.5 ¡ 58 ¡ 103 ¡ 11/12/96 ¡ 58 ¡ Rusty ¡ 10 ¡ 35.0 ¡ 44 ¡ guppy ¡ 5 ¡ 35.0 ¡ 58 ¡ Rusty ¡ 10 ¡ 35.0 ¡ 8 ¡

RelaTonal ¡Algebra ¡ • Queries ¡in ¡algebra ¡are ¡composed ¡using ¡a ¡ collecTon ¡of ¡__________ ¡ – Operators ¡can ¡be ¡unary ¡or ¡binary ¡ • Every ¡operator ¡in ¡the ¡algebra ¡accepts ¡(one ¡or ¡ two) ¡relaTon ¡instances ¡as ¡arguments, ¡and ¡ returns ¡a ¡relaTon ¡instance ¡as ¡the ¡result ¡ 9 ¡

SelecTon ¡ • SelecTon ¡(σ) ¡selects ¡subset ¡of ¡rows ¡from ¡ relaTon ¡ • Expression: ¡ SelecTon ¡condiTon ¡ σ raTng>8 (S2) ¡ Return: ¡ sid ¡ sname ¡ ra9ng ¡ age ¡ ¡ 28 ¡ yuppy ¡ 9 ¡ 35.0 ¡ ¡ 58 ¡ Rusty ¡ 10 ¡ 35.0 ¡ • Schema ¡of ¡the ¡result ¡of ¡a ¡selecTon ¡ ¡ = ¡schema ¡of ¡the ¡input ¡relaTon ¡instance ¡ 10 ¡

SelecTon ¡(cont.) ¡ • SelecTon ¡condiTon ¡is ¡a ¡Boolean ¡combinaTon ¡ of ¡ terms ¡that ¡have ¡the ¡forms: ¡ ¡ ¡ ¡ ¡1) ¡a4ribute ¡ op ¡ constant ¡ ¡ ¡ ¡ ¡2) ¡a4ribute1 ¡ op ¡ a4ribute2 ¡ * ¡Comparison ¡operators: ¡<, ¡<=, ¡=, ¡≠, ¡>=, ¡> ¡ * ¡Terms ¡are ¡combined ¡with: ¡ ________ ¡ • The ¡reference ¡to ¡an ¡aCribute ¡can ¡be ¡by ¡either ¡ – ________ ¡(.i ¡or ¡i) ¡ – ______ ¡(.name ¡or ¡name) ¡ 11 ¡

ProjecTon ¡ • ProjecTon ¡(π) ¡deletes ¡unwanted ¡columns ¡from ¡ relaTon ¡ • Example ¡ Fields ¡to ¡be ¡retained ¡ π sname, ¡raTng (S2) ¡ Return: ¡ sname ¡ ra9ng ¡ • The ¡other ¡fields ¡are ¡projected ¡ yuppy ¡ 9 ¡ out ¡ • The ¡schema ¡of ¡the ¡result ¡of ¡a ¡ Lubber ¡ 8 ¡ projecTon ¡is ¡determined ¡by ¡ guppy ¡ 5 ¡ the ¡fields ¡that ¡are ¡projected ¡ Rusty ¡ 10 ¡ 12 ¡

ProjecTon ¡(cont.) ¡ • Duplicates ¡are ¡eliminated ¡ – Example: ¡ π age (S2) ¡ – Return: ¡ • Follows ¡definiTon ¡of ¡relaTon: ¡set ¡of ¡tuples ¡ • Real ¡systems ¡oren ¡omit ¡this ¡step ¡ 13 ¡

SelecTon ¡& ¡ProjecTon ¡ • We ¡can ¡combine ¡σ ¡and ¡π ¡together ¡ – Example: ¡ π sname, ¡raTng (σ raTng>8 (S2)) ¡ – Return: ¡ • The ¡order ¡of ¡applying ¡the ¡operators ¡is: ¡ __________ ¡ à ¡__________ ¡ 14 ¡

Set ¡OperaTon: ¡Union ¡ • Union ¡( ⋃ ) ¡ R ¡ ⋃ ¡S ¡ – Returns ¡a ¡relaTon ¡instance ¡containing ¡all ¡tuples ¡ that ¡occur ¡in ¡either ¡relaTon ¡instance ¡R ¡or ¡relaTon ¡ instance ¡S ¡(or ¡both) ¡ – R ¡and ¡S ¡must ¡be ¡ union-­‑compa)ble : ¡ • Same ¡number ¡of ¡fields ¡ • Corresponding ¡fields ¡have ¡the ¡same ¡________ ¡ 15 ¡

Set ¡OperaTon: ¡Union ¡(cont.) ¡ • Example: ¡S1 ¡ ⋃ ¡S2 ¡ ¡ sid ¡ sname ¡ ra9ng ¡ age ¡ 22 ¡ DusTn ¡ 7 ¡ 45.0 ¡ 31 ¡ Lubber ¡ 8 ¡ 55.5 ¡ 58 ¡ Rusty ¡ 10 ¡ 35.0 ¡ 28 ¡ yuppy ¡ 9 ¡ 35.0 ¡ 44 ¡ guppy ¡ 5 ¡ 35.0 ¡ 16 ¡

Set ¡OperaTon: ¡IntersecTon ¡ • IntersecTon ¡( ⋂ ): ¡ R ¡ ⋂ ¡S ¡ – Returns ¡a ¡relaTon ¡instance ¡containing ¡all ¡tuples ¡ that ¡occur ¡in ¡both ¡R ¡and ¡S ¡ – The ¡relaTons ¡must ¡be ¡union-­‑compaTble ¡too ¡ • Example: ¡S1 ¡ ⋂ ¡S2 ¡ sid ¡ sname ¡ ra9ng ¡ age ¡ 31 ¡ Lubber ¡ 8 ¡ 55.5 ¡ 58 ¡ Rusty ¡ 10 ¡ 35.0 ¡ 17 ¡

Set ¡OperaTon: ¡Set-­‑difference ¡ • Set-­‑difference ¡(−): ¡ R ¡− ¡S ¡ – Returns ¡a ¡relaTon ¡instance ¡containing ¡all ¡tuples ¡ that ¡occur ¡______________ ¡ – The ¡relaTons ¡must ¡be ¡union-­‑compaTble ¡too ¡ • Example: ¡S1 ¡− ¡S2 ¡ sid ¡ sname ¡ ra9ng ¡ age ¡ 22 ¡ DusTn ¡ 7 ¡ 45.0 ¡ 18 ¡

Set ¡OperaTon: ¡Cross-­‑product ¡ • Cross-­‑product ¡(×): ¡ R ¡× ¡S ¡ – Returns ¡a ¡relaTon ¡instance ¡whose ¡schema ¡ contains ¡all ¡the ¡fields ¡of ¡R ¡followed ¡by ¡all ¡the ¡fields ¡ of ¡S ¡ – Result ¡of ¡R ¡× ¡S: ¡ – Naming ¡conflict ¡might ¡appear ¡ 19 ¡

Set ¡OperaTon: ¡Cross-­‑product ¡(cont.) ¡ • Example: ¡S1 ¡× ¡R1 ¡ ¡ (sid) ¡ sname ¡ ra9ng ¡ age ¡ (sid) ¡ bid ¡ day ¡ 22 ¡ DusTn ¡ 7 ¡ 45.0 ¡ 22 ¡ 101 ¡ 10/10/96 ¡ 22 ¡ DusTn ¡ 7 ¡ 45.0 ¡ 58 ¡ 103 ¡ 11/12/96 ¡ 31 ¡ Lubber ¡ 8 ¡ 55.5 ¡ 22 ¡ 101 ¡ 10/10/96 ¡ 31 ¡ Lubber ¡ 8 ¡ 55.5 ¡ 58 ¡ 103 ¡ 11/12/96 ¡ 58 ¡ Rusty ¡ 10 ¡ 35.0 ¡ 22 ¡ 101 ¡ 10/10/96 ¡ 58 ¡ Rusty ¡ 10 ¡ 35.0 ¡ 58 ¡ 103 ¡ 11/12/96 ¡ 20 ¡