This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: Problem #1 (40 points – 10 points each): Form relational algebra expressions that will produce the correct results for each of the following queries: Use only the five fundamental operators in your expressions. Use this sample database: s (s# , name, rank, city, workers) p (p# , name, color, weight, city) j (j# , name, workers, city) spj (s#, p#, j# , qty) where: in s: rank is a numeric field, and workers is the number of employees of that supplier. in p: city is the city in which the part is built. in j: workers is the number of workers on that job. 1. List the names of all the suppliers who supply part P2 to any job. Step-by-step technique: T1 = σ (p#=P2) (spj) //get all shipment tuples that involve part P2 T2 = s × T1 //combine all P2 shipments with all supplier tuples T3 = σ (s.s# = spj.s#) (T2) //remove “junk” tuples from T2, want s# matches T4 = π (name) (T3) //project only supplier names from T3 Single expression technique: π (name) ( σ (s.s# = spj.s#) (s × ( σ (p#=P2) (spj)))) Another form that also works is: π (name) ( σ ((s.s# = spj.s#) AND (p# = P2)) (s × spj)) Think about why the first form might be better than the second form. COP 4710 Homework #2 KEY - 1 COP 4710 – Database Systems – Fall 2009 Homework #2 – 135 points Due: In class Thursday October 22 nd . NO LATE ASSIGNMENTS ACCEPTED Answer each of the following questions completely. Make sure that your answers are neatly written and very readable. Points will be deducted if your assignment is not presented in a neat format. For example, don’t turn it in on notebook paper torn out of a spiral notebook. KEY 2. List the names of those cities in which there is a job located that employs more than 200 workers. Step-by-step technique: T1 = σ (workers > 200) ( j ) //select all tuples in j with > 200 workers T2 = π (city) (T1) //project the city attribute from tuples in T1 Single expression technique: π (city) ( σ (workers > 200) ( j )) 3. List the supplier names for those suppliers who supply at least one red part.3....
View Full Document
This note was uploaded on 11/01/2009 for the course CS cop4710 taught by Professor Dr. mark llewellyn during the Fall '09 term at University of Central Florida.
- Fall '09
- Dr. Mark Llewellyn