chapter 4 - part 2 - COP 4710: Database Systems Fall 2009...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
COP 4710: Database Systems (Chapter 4) Page 1 Mark Llewellyn COP 4710: Database Systems Fall 2009 Chapter 4 – Relational Query Languages – Part 2 School of Electrical Engineering and Computer Science University of Central Florida Instructor : Dr. Mark Llewellyn [email protected] HEC 236, 407-823-2790 http://www.cs.ucf.edu/courses/cop4710/fall2009
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COP 4710: Database Systems (Chapter 4) Page 2 Mark Llewellyn Sample Database Scheme
Background image of page 2
COP 4710: Database Systems (Chapter 4) Page 3 Mark Llewellyn 1. Find all the supplier numbers for suppliers located in Milan or who ship to any job in a quantity greater than 40. [ π (s#) ( σ (city = Milan) (S))] [ π (s#) ( σ (qty > 40) (SPJ))] 2. Find all the supplier numbers for suppliers who ship only red parts. [ π (S.name) ( σ ((SPJ.s#=S.S#) AND (SPJ.p#=P.p#) AND (color=red)) (SPJ × S × P))] - [ π (S.name) ( σ ((SPJ.s#=S.S#) AND (SPJ.p#=P.p#) AND (color red)) (SPJ × S × P))] Some Practice Queries Using Only Five Fundamental Operators To simplify the query expressions assume that S = suppliers, P = parts, J = jobs, and SPJ = shipments
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COP 4710: Database Systems (Chapter 4) Page 4 Mark Llewellyn 3. Find the supplier names for those suppliers who are located in the same city as a job to which they ship parts. T1 = (S × SPJ × J) T2 = σ (S.s# = SPJ.s#) (T1) //select tuples which match on s# T3 = σ (J.j# = SPJ.j#) (T2) //select tuples which match on j# T4 = σ (J.city = S.city) (T3) //select tuples from the same city T5 = π (S.name) (T4) //project final attribute set Some Practice Queries Using Only Five Fundamental Operators (cont.)
Background image of page 4
COP 4710: Database Systems (Chapter 4) Page 5 Mark Llewellyn 4. Find all the part numbers which are shipped by both supplier “S1” and supplier “S2”. NOTE: The following expression in not correct! Why not? π (p#) ( σ ((s# = S1) AND (s# = S2)) (SPJ)) The following is the correct way of expressing this query in RA. [ π (p#) ( σ (s#=S1) (SPJ)] ([ π (p#) ( σ (s#=S1) (SPJ)] - [ π (p#) ( σ (s#=S2) (SPJ)]) Some Practice Queries Using Only Five Fundamental Operators (cont.)
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
COP 4710: Database Systems (Chapter 4) Page 6 Mark Llewellyn 5. Find the supplier numbers for those suppliers who supply both a red part and a blue part. NOTE: The following expression in not correct! Why not? π (s#) ( σ ((color = blue) AND (SPJ.p# = P.p#) AND (color=red)) (P × SPJ)) The following is the correct way of expressing this query in RA. T1 = π (s#) ( σ ((color = blue) AND (SPJ.p# = P.p#)) (P × SPJ)) T2 = π (s#) ( σ ((color = red) AND (SPJ.p# = P.p#)) (P × SPJ)) T3 = T2 T1 T4 = T2 T3 Some Practice Queries Using Only Five Fundamental Operators (cont.)
Background image of page 6
COP 4710: Database Systems (Chapter 4) Page 7 Mark Llewellyn 6. Find all pairs (s#, j#) such that the supplier and the job are located in the same city, yet that supplier does not have a shipment to that job. T1 =
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the 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.

Page1 / 42

chapter 4 - part 2 - COP 4710: Database Systems Fall 2009...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online