Exercises
5
Exercises sid snam e rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0 bid bname color 101 Interla Blue 102 Interla Red 103 Clipper Green 104 Marine Red sid bid day 22 101 10/10/9 58 103 11/12/9 Reserves Sailors Boats Find names of sailors who’ve reserved boat #103 Basic operations: Selection ( σ ) gives a subset of rows. Projection ( π ) Deletes unwanted cols. Cross-product ( ) combine two relations. Set-difference ( — ) Tuples in reln. 1, but not in reln. 2. Union ( ) Tuples in reln. 1 and in reln. 2. Additional operations: Intersection () – Tuples in both relns. Join ( ) – like but only keep tuples where common fields equal Division ( / ) – tuples from reln 1 with matches in reln 2 Renaming ( ρ) – Rename columns sname bid serves Sailors (( Re ) ) 103 • Solution 2: sname bid serves Sailors ( (Re )) 103 • Solution 1:
description
Solution 2:. Exercises. Reserves. Sailors. Boats. Solution 1: . Basic operations: Selection ( σ ) gives a subset of rows. Projection ( π ) Deletes unwanted cols. Cross-product ( ) combine two relations. Set-difference ( — ) Tuples in reln. 1, - PowerPoint PPT Presentation
Transcript of Exercises
Exercisessid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0
bid bname color101 Interlake Blue102 Interlake Red103 Clipper Green104 Marine Red
sid bid day22 101 10/10/9658 103 11/12/96
Reserves Sailors Boats
Find names of sailors who’ve reserved boat #103
Basic operations:Selection ( σ ) gives a subset of rows.Projection ( π ) Deletes unwanted cols.Cross-product ( ) combine two relations.Set-difference ( — ) Tuples in reln. 1,
but not in reln. 2.Union ( ) Tuples in reln. 1 and in reln. 2.
Additional operations:Intersection () – Tuples in both relns.Join ( ) – like but only keep tuples
where common fields equalDivision ( / ) – tuples from reln 1 with
matches in reln 2Renaming ( ρ) – Rename columns
sname bid serves Sailors(( Re ) )103
• Solution 2: sname bid serves Sailors( (Re ))103
• Solution 1:
Exercisessid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0
bid bname color101 Interlake Blue102 Interlake Red103 Clipper Green104 Marine Red
sid bid day22 101 10/10/9658 103 11/12/96
Reserves Sailors Boats
Basic operations:Selection ( σ ) gives a subset of rows.Projection ( π ) Deletes unwanted cols.Cross-product ( ) combine two relations.Set-difference ( — ) Tuples in reln. 1,
but not in reln. 2.Union ( ) Tuples in reln. 1 and in reln. 2.
Additional operations:Intersection () – Tuples in both relns.Join ( ) – like but only keep tuples
where common fields equalDivision ( / ) – tuples from reln 1 with
matches in reln 2Renaming ( ρ) – Rename columns
Find names of sailors who’ve reserved a red boat
sname color red Boats serves Sailors(( ' ' ) Re )
A more efficient solution:
sname sid bid color red Boats s Sailors( (( ' ' ) Re ) )
Exercisessid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0
bid bname color101 Interlake Blue102 Interlake Red103 Clipper Green104 Marine Red
sid bid day22 101 10/10/9658 103 11/12/96
Reserves Sailors Boats
Basic operations:Selection ( σ ) gives a subset of rows.Projection ( π ) Deletes unwanted cols.Cross-product ( ) combine two relations.Set-difference ( — ) Tuples in reln. 1,
but not in reln. 2.Union ( ) Tuples in reln. 1 and in reln. 2.
Additional operations:Intersection () – Tuples in both relns.Join ( ) – like but only keep tuples
where common fields equalDivision ( / ) – tuples from reln 1 with
matches in reln 2Renaming ( ρ) – Rename columns
Find sailors who’ve reserved a red or a green boat
( , ( ' ' ' ' ))Tempboats color red color green Boats
sname Tempboats serves Sailors( Re )
Exercisessid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0
bid bname color101 Interlake Blue102 Interlake Red103 Clipper Green104 Marine Red
sid bid day22 101 10/10/9658 103 11/12/96
Reserves Sailors Boats
Basic operations:Selection ( σ ) gives a subset of rows.Projection ( π ) Deletes unwanted cols.Cross-product ( ) combine two relations.Set-difference ( — ) Tuples in reln. 1,
but not in reln. 2.Union ( ) Tuples in reln. 1 and in reln. 2.
Additional operations:Intersection () – Tuples in both relns.Join ( ) – like but only keep tuples
where common fields equalDivision ( / ) – tuples from reln 1 with
matches in reln 2Renaming ( ρ) – Rename columns
Find sailors who’ve reserved a red and a green boat
))Re)''((,( servesBoatsredcolorsidTempred
sname Tempred Tempgreen Sailors(( ) )
Exercisessid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0
bid bname color101 Interlake Blue102 Interlake Red103 Clipper Green104 Marine Red
sid bid day22 101 10/10/9658 103 11/12/96
Reserves Sailors Boats
Basic operations:Selection ( σ ) gives a subset of rows.Projection ( π ) Deletes unwanted cols.Cross-product ( ) combine two relations.Set-difference ( — ) Tuples in reln. 1,
but not in reln. 2.Union ( ) Tuples in reln. 1 and in reln. 2.
Additional operations:Intersection () – Tuples in both relns.Join ( ) – like but only keep tuples
where common fields equalDivision ( / ) – tuples from reln 1 with
matches in reln 2Renaming ( ρ) – Rename columns
Find the names of sailors who’ve reserved all boats
( , ( , Re ) / ( ))Tempsids sid bid serves bid Boats
sname Tempsids Sailors( )
