Get documents about "Relational_Database-Relational_Database
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 defined 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 satisfied
• 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 first 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 first.
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
Related docs
Other docs by yvtong
Web_Design_Tourism-MASTER_OF_ARTS_TOURISM_MANAGEMENT_NI_TermEnd_Examination_June_
Views: 4 | Downloads: 0
Web_Design_Tourism-Select_Italy_Press_Release__Italy_Travel_and_Vacation_Packages_
Views: 2 | Downloads: 0
Web_Design_Tourism-LEHIGH_UNIVERSITY_STUDY_ABROAD_OFFICE_PC_ROSSIN_COLLEGE_OF_
Views: 1 | Downloads: 0
