chapter 4 - part 2

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

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

COP 4710: Database Systems (Chapter 4) Page 1 Mark Llewellyn COP 4710: Database Systems Fall 2007 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, 823-2790 http://www.cs.ucf.edu/courses/cop4710/fall2007

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

View Full Document
COP 4710: Database Systems (Chapter 4) Page 2 Mark Llewellyn It can be proven (although we aren’t going to go through that proof) that the five fundamental relational operations are sufficient to express any relational-algebra query. What this proof doesn’t state however, is that some complex queries will require extremely lengthy and difficult query expressions. There have been several extensions of the set of operations available in the relational algebra that provide no additional expressive power, but do provide a simplification in the expression required for more complex queries. We’ll look at the most important and common of these redundant operations and also show their definition in terms of the five fundamental operations Redundant Operators in Relational Algebra
COP 4710: Database Systems (Chapter 4) Page 3 Mark Llewellyn Sample Database Scheme city Suppliers pnum snum name status city Parts Jobs name color weight city jnum name numworkers shipment qty date

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

View Full Document
COP 4710: Database Systems (Chapter 4) Page 4 Mark Llewellyn Sample Database Scheme Suppliers snum name status city pnum Parts name color weight city jnum Jobs name numworkers city Shipments snum pnum jnum qty date
COP 4710: Database Systems (Chapter 4) Page 5 Mark Llewellyn The intersection operation produces the set of tuples that appear in both operand relations. Intersection Operator Type: binary Symbol: General form: r s where r and s are union compatible relations Schema of result relation: schema of operation relation Size of result relation (tuples): r Definition: r s r - (r - s) Example: ( π (p#) (SPJ)) ( π (p#) (P))

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

View Full Document
COP 4710: Database Systems (Chapter 4) Page 6 Mark Llewellyn Intersection Operator Examples A B C D a a yes 1 b d no 7 c f yes 34 a d no 6 A B C D a a yes 1 c f yes 34 r = R S R E F G H a a yes 1 b r yes 3 c f yes 34 m n no 56 S E F G H a r no 31 b f yes 30 T A B C D r = R T
COP 4710: Database Systems (Chapter 4) Page 7 Mark Llewellyn As we saw in some of our earlier query expression which involved the Cartesian product operator, we had to provide additional selection operations to remove those combinations of tuples that resulted from the Cartesian product which weren’t related (they didn’t make sense like when a shipment of a specific part was combined with part information but the part information didn’t belong to the part that was being shipped). This occurs so commonly that an operation which is a combination

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

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 02/22/2009 for the course COP 4710 taught by Professor Dr. mark llewellyn during the Fall '07 term at University of Central Florida.

### Page1 / 43

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

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

View Full Document
Ask a homework question - tutors are online