relational algebra and relational calculus mca
TRANSCRIPT
-
8/7/2019 Relational Algebra and Relational Calculus MCA
1/30
` Language in which user requests information fromthe database.
` Categories of languages
procedural
non-procedural` Pure languages:
Relational Algebra
Tuple Relational Calculus
Domain Relational Calculus` Pure languages form underlying basis of query
languages that people use.
-
8/7/2019 Relational Algebra and Relational Calculus MCA
2/30
` Procedural language
` User has to specify what is required and what are
the steps to obtain the required output.
` The operators take one or more relations as inputs
and give a new relation as a result.
` Two types of relational operators-
Traditional Set operators
Special operators
-
8/7/2019 Relational Algebra and Relational Calculus MCA
3/30
` Traditional set operators are:- Union
Intersection
Difference
Cartesian Product
` Union:-A union of two relations is a relation that contain all
data elements belonging to both relations while writing the
duplicate values only once.
In order to perform the union operation , both operand relations
must be union compatible i.e. they must have same no. of columns
drawn from same domain (i.e. must be of same data type).
It is represented by symbol U.
-
8/7/2019 Relational Algebra and Relational Calculus MCA
4/30
Cust_name Cust_status
Sham Good
Rahul Excellent
Karan Bad
Cust_name Cust_status
Sham Good
Rahul Excellent
Cust_name Cust_status
Karan Bad
Sham Good
R S
R U S
-
8/7/2019 Relational Algebra and Relational Calculus MCA
5/30
` Intersection of two relations consists of a third
relation with all rows that are common to both
relations.
` It is represented by symbol .
` Taking above example, R S is
Cust_name Cust_status
Sham Good
R S
-
8/7/2019 Relational Algebra and Relational Calculus MCA
6/30
` Difference of two relations consist of a thirdrelation which consists of elements which arepresent in one relation and not in another.
` It is represented using symbol -.
`
Minus is not associative.
Cust_name Cust_status
Rahul Excellent
Cust_name Cust_status
Karan Bad R-S S-R
-
8/7/2019 Relational Algebra and Relational Calculus MCA
7/30
` Cartesian product of two relations include each row of
one relation is associated with each row of another
relation.
` It is denoted by cross (X).
A B
1
2
C D
10
20 11
24
A B C D
1 10
1 20
1 11
1 24
2 10
2 20
2 11
2 24
R
S
R X S
-
8/7/2019 Relational Algebra and Relational Calculus MCA
8/30
` Four special relational operators are:-
Selection
Projection
Join
Division
` Selection:-Yields a horizontal subset of a given relation.
` Those tuples or rows of a table are selected which satisfy a
particular condition (predicate).` It is represented by Greek letter sigma .
` Notation: Wp (r)
` p is called the selection predicate.
-
8/7/2019 Relational Algebra and Relational Calculus MCA
9/30
` E.g. Suppose from supplier table, we have to select
values where city is Mohali.
The query is like
` city= Mohali (Supplier)
Sno Sname Status City
S1 Suneet 20 Mohali
S2 Ankit 10 Amritsar
S3 Amit 30 Amritsar
S4 Raj 20 Chandigarh
-
8/7/2019 Relational Algebra and Relational Calculus MCA
10/30
` Projection operation on a table simply forms another table by
copying specified columns from original table while eliminatingany duplicated rows.
` Projection is denoted by
` Consider the following employee table.
` Projections ofAge:- Age (employee)
Empno Ename Age Sal
E01 Smith 25 7500
E02 John 36 10000
E03 Allen 42 10000 Age
25
36
42
-
8/7/2019 Relational Algebra and Relational Calculus MCA
11/30
` Notation:
A1, A2, , Ak (r)
whereA1,A2 are attribute names and r is a relation
name.
` The result is defined as the relation ofkcolumns
obtained by erasing the columns that are not listed
` Duplicate rows removed from result, since relations
are sets
-
8/7/2019 Relational Algebra and Relational Calculus MCA
12/30
` An extension of the join operation that avoids loss of
information.` Computes the join and then adds tuples form one
relation that do not match tuples in the other relationto the result of the join.
` Uses nullvalues:
nullsignifies that the value is unknown or does notexist
All comparisons involving nullare (roughlyspeaking) false by definition.
-
8/7/2019 Relational Algebra and Relational Calculus MCA
13/30
` Relation loan
Relation borrower
customer-name loan-number
Jones
Smith
Hayes
L-170
L-230
L-155
3000
4000
1700
loan-number amount
L-170
L-230
L-260
branch-name
Downtown
Redwood
Perryridge
-
8/7/2019 Relational Algebra and Relational Calculus MCA
14/30
` Inner Joinloan Borrower
loan-number amount
L-170
L-230
3000
4000
customer-name
Jones
Smith
branch-name
Downtown
Redwood
JonesSmith
null
loan-number amount
L-170L-230
L-260
30004000
1700
customer-namebranch-name
DowntownRedwood
Perryridge
Left Outer Joinloan Borrower
-
8/7/2019 Relational Algebra and Relational Calculus MCA
15/30
` Right Outer Joinloan borrower
loan borrower
Full Outer Join
loan-number amount
L-170
L-230
L-155
3000
4000
null
customer-name
Jones
Smith
Hayes
branch-name
Downtown
Redwood
null
loan-number amount
L-170L-230
L-260
L-155
30004000
1700
null
customer-name
JonesSmith
null
Hayes
branch-name
DowntownRedwood
Perryridge
null
-
8/7/2019 Relational Algebra and Relational Calculus MCA
16/30
` Is used to divide a relation A of degree m+n (no. of
columns in a relation) by relation B of degree n, produces
a relation of degree m.
` It is denoted by z .
-
8/7/2019 Relational Algebra and Relational Calculus MCA
17/30
sno pno
s1 p1
s1 p2
s1 p3s1 p4
s2 p1
s2 p2
s3 p2s4 p2
s4 p4
pno
p2
pno
p2
p4
pno
p1
p2
p4
sno
s1
s2s3
s4
snos1
s4
sno
s1
A
B1
B2
B3
A z B1 A z B2 A z B3
-
8/7/2019 Relational Algebra and Relational Calculus MCA
18/30
` The result to the right of the n is assigned tothe relation variable on the left of the n.
` A n B
` Value of B is assigned to A.
-
8/7/2019 Relational Algebra and Relational Calculus MCA
19/30
`
Aggregation function takes a collection of values and returnsa single value as a result.
avg: average valuemin: minimum valuemax: maximum value
sum: sum of valuescount: number of values
` Aggregate operation in relational algebra
G1, G2, , Gn g F1( A1), F2( A2),, Fn( An) (E)
E is any relational-algebra expression
G1, G2 , Gn is a list of attributes on which to group (can beempty)
Each Fi is an aggregate function
Each Ai is an attribute name
-
8/7/2019 Relational Algebra and Relational Calculus MCA
20/30
` Relation r: A B
C
g sum(c) (r)sum-C
27
-
8/7/2019 Relational Algebra and Relational Calculus MCA
21/30
` Relation account grouped by branch-name:
branch-name
gsum(balance)
(account)
branch-name account-number balance
Perryridge
PerryridgeBrighton
Brighton
Redwood
A-102
A-201A-217
A-215
A-222
400
900750
750
700
branch-name balance
Perryridge
Brighton
Redwood
1300
1500
700
-
8/7/2019 Relational Algebra and Relational Calculus MCA
22/30
` Result of aggregation does not have a name Can use rename operation to give it a name
For convenience, we permit renaming as part ofaggregate operation
branch-name g sum(balance) as sum-balance (account)
-
8/7/2019 Relational Algebra and Relational Calculus MCA
23/30
Tuple oriented relational calculus
Domain Oriented relational calculus
-
8/7/2019 Relational Algebra and Relational Calculus MCA
24/30
` Non procedural language
`
User is not concerned with the procedure to obtain the results, he/she just tell theresults and output is available without knowing the method of retrieval.
` A query is expressed as a formula consisting of a no. of variables and an
expression involving these variables.
` It is up to DBMS to transform these non procedural queries into procedural
queries.
` This concept was first proposed by Codd.
` It is based on predicate calculus.
` Propositions specifying a property consist of an expression that names an
individual object and another expression called the predicate stand for the property
that object possesses.
` E.g. MCA is a class ObjectMCA, India
India is a country. Predicate- Class, Country
-
8/7/2019 Relational Algebra and Relational Calculus MCA
25/30
` A convenient method of writing the statements is to place the
predicate first and follow it with the object enclosed in parenthesis.
` E.g. MCA is a class can be written asis a class (MCA) or
Class (MCA)
` Finally, if we use symbols for both the predicate and the object, we
can rewrite the statement as P(X) where P is the predicate and X is theobject.
` These are known as one place predicate.
` Two place predicate-require two objects.
` E.g. Naresh is taller than RajeshABC is intelligent than XYZ
These can be written as
TALLER_THAN (Naresh, Rajesh)
INTELLIGENT_THAN (ABC,XYZ)
-
8/7/2019 Relational Algebra and Relational Calculus MCA
26/30
` A predicate followed by its arguments is called an Atomic Formula.
` E.g. CLASS(MCA)
TALLER_THAN (Naresh ,Rajesh)
` Atomic formulas can also be combined using logical connectors
such as
NOT or Negation
OR V
AND ^
IMPLICATION
` E.g. oracle is a DBMS and ABC is a company
Can be written in atomic formula as
DBMS (oracle) COMPANY(ABC)
-
8/7/2019 Relational Algebra and Relational Calculus MCA
27/30
` Based on specifying a number of tuple variables.
` A nonprocedural query language, where each query is of the form{t|P(t) }
` It is the set of all tuples tsuch that predicate Pis true fort
` tis a tuple variable, t[A] denotes the value of tuple ton attribute A
` t rdenotes that tuple tis in relation r
` Pis a formulasimilar to that of the predicate calculus.` E.g. {t | Book (t) and t. Price>100}
Will get you all books whose price is greater than 100.It retrievesattributes for each selected value.
` To retrieve only some of the attributes say TITLE,AUTHOR,PRICE,
Query is like
{ t.TITLE, t. AUTHOR, t. PRICE|BOOK (t) and t. PRICE>200}
-
8/7/2019 Relational Algebra and Relational Calculus MCA
28/30
` Thus in a tuple calculus, we need
A condition to select the required tuples from the relation. A set of attributes to be retrieved.
` WFF (Well Formed Formula)
Every condition is a WFF.A WFF is constructed fromconditions, Boolean operations (AND,OR,NOT) and
quantifiers like for all ()
and there exists().y tr (Q(t))|there exists a tuple in t in relation r
such that predicate Q(t) is true
y tr (Q(t)) |Qis true for all tuples tin relation r
-
8/7/2019 Relational Algebra and Relational Calculus MCA
29/30
`
In Domain Calculus the variables range over singlevalues from domains of attributes rather than rangingover tuples.
` An expression of domain calculus is
{ x1,x2, ,xn " |P(x1, x2, ,xn)}
x1, x2, ,xn represent domain variables Prepresents a formula similar to that of the predicate calculus
` E.g. Get emp no in relation emp
EX where emp (empno: EX)
` Get emp no for job ClerkEX where emp (empno: Ex, job=Clerk)
-
8/7/2019 Relational Algebra and Relational Calculus MCA
30/30
RELATIONALALGEBRA RELATIONAL CALCULUS
1. It is a procedural method of
solving the queries.
2. The solution is obtained by
stating what is required and what
are the steps to obtain that
information.
3. It is used as a vehicle for
implementation of Relational
Calculus.
4.Two types operators -
Traditionalset operators, Special operators.
1. It is a non procedural method of
solving the queries.
2. The solution is obtained by stating
what is required and letting the
system find the answer.
3. Relational Calculus queries are
converted into equivalent
relational algebra format using
codds Reduction algorithm and
than it is implemented using
relational operators.
4. Two types calculus- Tuple oriented
, Domain oriented.