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

Info icon This 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
Image of page 1

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

View Full Document Right Arrow Icon
COP 4710: Database Systems (Chapter 4) Page 2 Mark Llewellyn Sample Database Scheme
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
Image of page 3

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

View Full Document Right 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.)
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.)
Image of page 5

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

View Full Document Right 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.)
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 = π (s#, j#) ( σ (S.city = J.city) (S × J)) //all (s#,j#) pairs in same city T2 = π (s#, j#) ( σ ((S.city = J.city) AND (SPJ.j# = J.j#) AND (SPJ.s# = S.s#)) (S × SPJ × J)) //T2 contains all (s#,j#) pairs representing shipments by suppliers to jobs in the same city.
Image of page 7

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

View Full Document Right Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern