Document Sample

G64DBS Manipulating Data Database Systems • The Relational Data Model is designed so that data may be processed with mathematical operations. Lecture 3 • In maths an operation is a procedure that produces a new value from one or more input values. Relational Operators • For example: Projection Tim Brailsford Relational Database Operations • There are EIGHT relational database operations • Four relational operations • Projection • Selection • Join • Division • Four set operations • Union • Difference • Intersection • Cartesian Product Algebra Relational Algebra • Algebra • “The branch of mathematics concerning the study of the rules of operations and relations.” • Operations and Operators • Relational operators take one or two relations as inputs and return relations as the result. • “An action or procedure which produces a new value from one or more input values.” • For example: + -!÷ • Set operators take one or two sets as inputs and return sets as the result. • Take numbers as input and return numbers as results • Some operations have restrictions • For example you can’t divide by zero Projection PROJECT Operator • Extracts data vertically (i.e. as a column) • PROJECT (relational operator) • A unary operator • i.e. it operates on a single relation • Chooses some of the attributes of the relation • i.e. columns of the table • PROJECT Product name Melon Strawberry Apple Melon Selection SELECT Operator • Extracts data horizontally (i.e. as a row) • SELECT (relational operator) • A unary operator • i.e. it operates on a single relation • Chooses the tuples in a relation which satisfy certain conditions. • i.e. it selects rows of the table • For example: SELECT Product name where Unit price > 500G Melon Division Division Operator • Extracts rows whose column values match those • / (relational operator) in the second table, but only returns columns that don’t exist in the second table. • A binary operator Sales Table • Note: Export Destination Table • For A / B to be deﬁned the attributes of B must be a subset of the attributes of A • The tuples of A / B are all tuples d, such that for EVERY b in B, the combination of d and b is a tuple in A A The result of the division will be the B D date on which there are exports to b both The Kingdom of Minanmi and b d the Alpha Empire. Cartesian Product Product Operator • Combines all rows in the two tables. • Cartesian product operator - X (set operator) X 3 rows • A binary operator • The tuples of the product A B are all tuples which can be formed by coalescing a tuple from A and a tuple from B • Coalescing: 3x3= 9 rows • (a,b,c) and (x,y,z) become (a,b,c,x,y,z) Join Join • Joins two tables together using a shared key (i.e. primary/foreign) Product Table Sales Table Natural Join Outer Join (Inner Join) • Usually called JOIN • Natural joins can “lose” some tuples from one relation, if they do not have a match in the other • JOIN is a binary operator that combines the relation PRODUCT and SELECT operators into a single operation. • Outer join may retain the tuples which would be lost by natural join • The result of the natural join of A and B is the • Left outer join of A and B retains all of A combinations of tuples a from A and b from B where certain conditions are satisﬁed • Right outer join of A and B retains all of B Outer Join (example) Union Product Table Sales Table Product code Product name Unit price Date Product code Quantity • Combine all rows in two tables 101 Melon 800G 11/1 102 1,100 102 Strawberry 150G 11/1 101 300 103 104 Apple Lemon 120G 200G 11/5 11/8 11/9 103 101 105 1,700 500 400 ! Product code Product name Unit price Date Quantity Product code Product name Unit price Date Quantity 101 Melon 800G 11/1 300 101 Melon 800G 11/1 300 101 Melon 800G 11/8 500 101 Melon 800G 11/8 500 102 Strawberry 150G 11/1 1,100 102 Strawberry 150G 11/1 1,100 103 Apple 120G 11/5 1,700 103 Apple 120G 11/5 1,700 104 Lemon 200G NULL NULL 105 NULL NULL 11/9 400 Product left outer join Sales Product right outer join Sales Union Union Operator • Union - ! (set operator) • Binary operator - it takes two relations, A and B, both of which must have the same set of attributes • The union, A ! B, consists of all the tuples in A and all the tuples in B Intersection Intersection • Extract items that are included in both tables. " Intersection Operator Difference • Extracts rows from one of the tables • For example - all of the products in the ﬁrst table that don’t appear in the second. • Intersection - " (set operator) • Binary operator - it takes two relations, A and B, both of which must have the same set of attributes • The intersection, A " B, consists of those tuples that are in both A and B Difference Difference (2) • The results depend upon which table contains rows to extract, and which has rows to exclude. • For example - all of the products in the second table that don’t appear in the ﬁrst. Difference (2) Difference Operator • Difference - (set operator) • Binary operator - it takes two relations, A and B, both of which must have the same set of attributes • The difference, A - B, consists of those tuples in A which are not in B

DOCUMENT INFO

Shared By:

Categories:

Tags:

Stats:

views: | 13 |

posted: | 6/26/2012 |

language: | English |

pages: | 5 |

OTHER DOCS BY yvtong

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.