Introduction Relational algebra defines the theoretical way of manipulating table contents through a number of relational operators These relational operators include SELECT or RESTRICT PROJECT ID: 696450
Download Presentation The PPT/PDF document "LECTURE 4 RELATIONAL ALGEBRA" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
LECTURE 4
RELATIONAL
ALGEBRASlide2
Introduction
Relational algebra
defines the theoretical way of manipulating table contents through a number of relational operators
These relational operators include;
SELECT (or RESTRICT)
PROJECT
JOIN
PRODUCT
INTERSECT
UNION
DIFFERENCE
DIVIDESlide3
Introduction (cont’d)
The relational operators have the property of
closure,
i.e., relational algebra operators are used on existing tables to produce new tables
The relational operators are classed as being unary or binary
Unary operators such as SELECT and PROJECT, can be applied to one relation
Binary operators such as JOIN are applied on two relationsSlide4
SELECTion
SELECT or RESTRICT, can be used to list all of the row values, or return only the row
v
alues that match a specified criterion.
The SELECT operator is denoted by
and is formally defined as:
(R)
or (RELATION) where (R) is the set of specified tuples of the relation R and is the predicate (or criterion) to extract the required tuples.It is possible to create more complex criteria by using the logical operators AND, OR, and NOT
Slide5
SELECT (cont’d)
OrderNum
PartNum
21608
AT94
21610
DR93
21610DW1121613KL6221614KT0321617BV06
21617
CD52
21619DR9321623KV29
OrderLine
(OrderLine)
(OrderLine)
OrderNumPartNum21608AT9421610DR9321610DW1121613KL6221614KT0321617BV0621617CD5221619DR9321623KV29
OrderNum
PartNum
21608
AT94Slide6
PROJECTion
This operator returns all values for selected attributes.
The PROJECT operator is denoted by
and formally defined as:
or
w
here the projection of the relation
R, denoted by
is the set of specified attributes a
1
…..an of the relation R Slide7
PROJECT (cont’d)
OrderNum
PartNum
21608
AT94
21610
DR93
21610DW1121613KL6221614KT0321617BV06
21617
CD52
21619DR9321623KV29
OrderLine
OrderNum(OrderLine)
OrderNum216082161021610
21613
2161421617216172161921623Slide8
UNION
The UNION set operator combines all tuples from two relations, excluding duplicate tuples.
The relations must have the same attribute characteristics (the columns and domains must be identical) to be used in the UNION.
When two or more tables share the same number of columns, i.e., have the same degree, and when the share the same (or compatible) domains, they are said to be
union- compatible Slide9
UNION(cont’d)
The UNION operator is denoted by
and formally defined as:
The union of relations
and
denoted by
with degree
n, is the relation
where for each
i
(i = 1, 2, …n), ai and bi must have compatible domains. The degree of
R3 is the same as that of
R1 and R2. However, the cardinality of
R3 is
a+b, only if a and b are the cardinalities of R1 and R
2 respectively
Slide10
UNION (cont’d)
OrderNum
PartNum
21608
AT94
21610
DR93
21610DW1121613KL62OrderLine1OrderNum
PartNum
21614
KT0321617BV0621617CD52
21619
DR9321623KV29
OrderLine2
OrderNumPartNum21608AT94
21610
DR9321610DW1121613KL6221614KT0321617BV0621617CD5221619DR9321623KV29OrderLine1 OrderLine2 Slide11
INTERSECT
The INTERSECT operator denoted as
, returns only the tuples that appear in both relations
The tables must be union-compatible to give valid results.
The INTERSECT
operator is
formally defined as:The intersect of relations and denoted by with degree n, is the relation
that includes only those tuples of
R
1 that also appear in R2 where for each i (i = 1, 2, …n), a
i and bi
must have compatible domains. Slide12
INTERSECT(cont’d)
PartNum
DR93
DW11
BV06
CD52
PartNum
AT94DR93DW11KL62KT03KV29
Part1
Part2
Part1
Part2PartNum
DR93
DW11Slide13
DIFFERENCE
The DIFFERENCE operator returns all tuples in one relation that are not found in the other relation.
The DIFFERENCE operator requires that the two relations be union-compatible.
The DIFFERENCE
operator is
formally
defined as:The difference of relations and denoted by with degree m, is the relation
that includes all the tuples that are in
R
1 but not in R2 where for each i (i = 1, 2, …m), ai
and bi must have compatible domains
. Slide14
DIFFERENCE (cont’d)
PartNum
DR93
DW11
BV06
CD52
PartNum
AT94DR93DW11KL62KT03DR93
KV29
Part1
Part2
Part1Part2
PartNum
AT94KL62KT03KV29Slide15
CARTESIAN PRODUCT
The CARTESIAN PRODUCT is usually written as
with the new resulting relation
containing all the attributes which are present in
and
along with all possible combinations of tuples from both
and It can be formally defined as:The CARTESIAN PRODUCT of two relations
with cardinality
i and with cardinality j is a relation
with degree
, cardinality
and attributes
. This can be denoted as
The
CARTESIAN PRODUCT is not a very useful operation by itself, as it combines many tuples that have no association with each other. However, when used in conjunction with the RESTRICT (SELECT) operator, it becomes a very important operator known as a JOIN Slide16
CARTESIAN PRODUCT (cont’d)
OrderNum
OrderDate
21608
10/20/2010
21610
10/20/2010
2161310/21/2010PartNumDescriptionDR93Gas Range
DW11
Washer
OrderNumOrderDatePartNum
Description21608
10/20/2010DR93Gas Range
21610
10/20/2010DR93Gas Range2161310/21/2010
DR93
Gas Range2160810/20/2010DW11Washer2161010/20/2010DW11Washer2161310/21/2010DW11WasherPart
Orders
Product of Orders
and
PartSlide17
DIVISION
The division operation produces a new relation by selecting the tuples in one relation,
,
that match every row in another relation,
.
It is the inverse of the CARTESIAN PRODUCT.
DIVISION, denoted by , can be formally defined as:The DIVISION of two relations with cardinality i and
with cardinality
j
is a relation with degree and cardinality
.
Slide18
Division (cont’d)
OrderNum
21610
PartNum
DR93
DW11
OrderNum
PartNum21608AT9421610DR93
21610
DW11
21613KL6221614KT03
21617BV06
21617CD5221619
DR93
21623KV29OrderLine Part
OrderLinePartSlide19
JOIN
The JOIN operation is one of the essential operations of relational algebra.
It is said to be the real power behind the relational database, allowing the use of independent tables linked by common attributes
The JOIN of two relations
R
1
and R2 is a restriction on their Cartesian product R1XR2 to meet a specified criterion.The join itself is defined on an attribute a of R1 and b of R2 where the attributes a and b share the same domain.Slide20
JOIN(cont’d)
The JOIN operator is formally defined as:
The join of two relations
R
1
(
a
1, a2,…., an) and R2(b1, b2,…, bm) is a relation R3 with degree and attributes (a1,
a
2
,…, an, b1, b2,…, bm) Types of join operationsTHETA JOIN
EQUIJOINNATURAL JOINLEFT OUTER JOIN
RIGHT OUTER JOIN Slide21
THETA JOIN AND EQUIJOIN
EQUIJOIN is on of the most commonly used joins which links tables on the basis of an equality condition that compares specified columns of each table
The outcome of the equijoin does not eliminate duplicate columns , and the condition or criterion used must be explicitly defined.
The equijoin takes its name from the equality comparison operator (=) used in the conditionSlide22
THETA JOIN AND EQUIJOIN(cont’d)
If any other comparison operator is used the join is called a THETA JOIN, denoted by
θ
(
θ
-join)
Therefore, theta represents a predicate which consists of the following comparison operators (
<, <=, >=, <>) EQUIJOIN is a special type of THETA JOIN Let R1(a1, a2,…., an) and R2(b1, b2,…, bm) be relations which may have different schemas. Then, θ-join of R1 and R
2
is denoted as R1 θR2 The equijoin is denoted as R1 R1.a = R2.bR
2
Slide23
THETA JOIN AND EQUIJOIN(cont’d)
It is possible to express the
θ
-join and the equijoin in terms of restriction and Cartesian product operations
Eg
; equijoin
R
1 R1.a = R2.bR2 may also be written as R1.a = R2.b(R1 XR2 )Looking at the θ-join and the equijoin in this way allows for some rules to be created which helps in the computation of such joins on two relations:Compute R
1
X
R2 . This first performs a Cartesian product to form all possible combinations of the rows of R1 and R2 Restrict the Cartesian product to only those rows where the values in certain columns match Slide24
Equijoin Example
DEPT_CODE
DEPT_NAME
ACCT
Accounting
BIOL
Biology
CISComputer Info. SystemsENGLEnglishSTU_NUMSTU_LNAME
STU_FNAME
STU_DOB
DEPT_CODE321452BowserWilliam12 February 1972
BIOL
324257SmithsonAnne
15 November 1977CIS
324258BrewerJuliette23 August 1966
ACCT
324269OblonskiWalter16 September 1973CIS324273smithJohn30 December 1955ENGLSTUDENTDEPARTMENTSlide25
Equijoin
Example (cont’d)
STU_NUM
STU_LNAME
STU_FNAME
STU_DOB
S.DEPT_CODE
D.DEPT_CODEDEPT_NAME321452BowserWilliam12 February 1972BIOLACCTAccounting
321452
Bowser
William12 February 1972BIOLBIOL
Biology321452
BowserWilliam12 February 1972
BIOL
CISComputer Info. Systems321452BowserWilliam
12 February 1972
BIOLENGLEnglish324257SmithsonAnne15 November 1977CISACCTAccounting324257SmithsonAnne15 November 1977CISBIOLBiology324257SmithsonAnne15 November 1977CIS
CIS
Computer Info. Systems
324257
Smithson
Anne
15 November 1977
CIS
ENGL
English
324258
Brewer
Juliette
23 August 1966
ACCT
ACCT
Accounting
324258
Brewer
Juliette
23 August 1966
ACCT
BIOL
Biology
324258
Brewer
Juliette
23 August 1966
ACCT
CIS
Computer Info. Systems
324258
Brewer
Juliette
23 August 1966
ACCT
ENGL
English
324269
Oblonski
Walter
16 September 1973
CIS
ACCT
Accounting
324269
Oblonski
Walter
16 September 1973
CIS
BIOL
Biology
324269
Oblonski
Walter
16 September 1973
CIS
CIS
Computer Info. Systems
324269
Oblonski
Walter
16 September 1973
CIS
ENGL
English
324273
smith
John
30 December 1955
ENGL
ACCT
Accounting
324273
smith
John
30 December 1955
ENGL
BIOL
Biology
324273
smith
John
30 December 1955
ENGL
CIS
Computer Info. Systems
324273
smith
John
30 December 1955
ENGLENGLEnglish
Cartesian product (STUDENT X DEPARTMENT)Slide26
Equijoin Example (cont’d)
STU_NUM
STU_LNAME
STU_FNAME
STU_DOB
S.DEPT_CODE
D.DEPT_CODE
DEPT_NAME321452BowserWilliam12 February 1972BIOLBIOLBiology
324257
Smithson
Anne15 November 1977CISCIS
Computer Info. Systems324258
BrewerJuliette23 August 1966
ACCT
ACCTAccounting324269OblonskiWalter
16 September 1973
CISCISComputer Info. Systems324273smithJohn30 December 1955ENGLENGLEnglishSTUDENT_IN_DEPT = STUDENT.DEPT_CODE = DEPARTMENT.DEPT_CODE(STUDENT X DEPARTMENT ) Slide27
NATURAL JOIN
The natural join operation is the most common variant of the joins and requires that the two operant relations must have at least one common attribute, i.e., attributes that share the same domain. The common column(s) is (are) referred to as the
join column(s)
The natural join is in fact an equijoin, however, in addition, the duplicate attributes are dropped with the resulting relation containing one less column than that of the equijoinSlide28
NATURAL
JOIN(cont’d)
Let
R
1
be a relation having attributes (a
1
, a2,…, an, y), R2 be another relation having attributes (b1, b2,…, bmy) where y is a set of common attributes (join column(s)) which share the same domainThe natural join operator is defined as:The natural join of R1 and R2, denoted R1 X R2, consists of combining the tuples of R1 and R2 to build a new relation R3, such that if
,
,
,
then
Slide29
NATURAL
JOIN(cont’d)
The common set of attributes
y
appears only once in
R
3;
the notation correspond to the attribute value of a tuple of R1 The steps required to perform the natural join of two relations are:Compute R1 X R2. Select those tuples where . Only the rows are selected where the attribute values in the join column(s) are equal
Perform a PROJECT operation on either
to the result in step 2, and call it y in the final relation. This is to ensure that the final relation results in a single copy of each attribute in the joining column, thereby eliminating duplicates
Slide30
Natural Join Example
CUSTOMER
AG
ENT
CUS_CODE
CUS_LNAME
CUS_POSTCODE
AGENT_CODE1132445WalkerM1 5RT2311217782AdaresNW6 4RT
125
1312243
Rakowski6789541671321242Rodriguez
NW6 2WS
1251542311Smithson
N4 3YP421
1657399Vanloo67543W231
AGENT_CODE
AGENT_PHONE12501812439887167018134267782310181243112433301131234445Slide31
Natural J
oin
Example (cont’d)
Cartesian product
(
CUSTOMER
X AGENT)C.CUS_CODEC.CUS_LNAMEC.CUS_POSTCODEC.AGENT_CODEA.AGENT_CODEA.AGENT_PHONE1132445WalkerM1 5RT231
125
01812439887
1132445WalkerM1 5RT231167
01813426778
1132445WalkerM1 5RT
231231
018124311241132445WalkerM1 5RT
231
333011312344451217782AdaresNW6 4RT125125018124398871217782AdaresNW6 4RT125167018134267781217782AdaresNW6 4RT125231018124311241217782Adares
NW6 4RT
125
333
01131234445
1312243
Rakowski
678954
167
125
01812439887
1312243
Rakowski
678954
167
167
01813426778
1312243
Rakowski
678954
167
231
01812431124
1312243
Rakowski
678954
167
333
01131234445
1321242
Rodriguez
NW6 2WS
125
125
01812439887
1321242
Rodriguez
NW6 2WS
125
167
01813426778
1321242
Rodriguez
NW6 2WS
125
231
01812431124
1321242
Rodriguez
NW6 2WS
125
333
01131234445
1542311
Smithson
N4 3YP
421
125
01812439887
1542311
Smithson
N4 3YP
421
167
01813426778
1542311
Smithson
N4 3YP
421
231
01812431124
1542311
Smithson
N4 3YP
421
333
01131234445
1657399
Vanloo
67543W
231
125
01812439887
1657399
Vanloo
67543W
231
167
01813426778
1657399
Vanloo
67543W
231
231
01812431124
1657399
Vanloo
67543W
231
333
01131234445Slide32
Natural Join
Example (cont’d)
CUSTOMER
X
AG
ENT CUS_CODECUS_LNAMECUS_POSTCODEAGENT_CODEAGENT_PHONE1132445WalkerM1 5RT
231
01812431124
1217782AdaresNW6 4RT12501812439887
1312243
Rakowski678954167
018134267781321242
RodriguezNW6 2WS12501812439887
1657399
Vanloo67543W23101812431124Slide33
OUTER JOIN
When using the theta and natural join, it is possible that some tuples in the joined relations do not have identical values for the common attributes. As a result these tuples will be ‘lost’.
If it is required that all tuples from the original tables be shown in the resulting relation, then it is necessary to have a join which keeps all the tuples in R
1
which have no corresponding values in R
2
. The tuples in the R
2 will have null values. This type of join is known as the outer joinSlide34
OUTER JOIN (cont’d)
There are 3 common types of the outer join
Left outer join – keeps data from the left-hand relation
Right outer join – keeps data from the right-hand relation
Full outer join – keeps data from both relations
The steps for determining an outer join are very similar to those for computing a natural join, except that data from the left or right side of the relation, depending on whether one is performing a left or right outer join is included.Slide35
OUTER JOIN (cont’d)
The stages in performing a left outer join are:
Compute
R
1
X
R
2. Select those tuples where . Only the rows are selected where the attribute values in the join column(s) are equalSelect those tuples in R1 that do not have matching values in R2 , so
Perform a PROJECT operation on either
to the result in step 2, and call it y in the final relation.
This is to ensure that the final relation results in a single copy of each attribute in the joining column, thereby eliminating duplicates. Finally, project the rest of attributes in R1
and R2 , except y, and drop the R
1 and R2 in the final relation Slide36
Left Outer Join Example (cont’d)
A
left outer join
of CUSTOMER and AGENT, will return all the tuples in the CUSTOMER relation, including those that do not have a matching value in the AGENT relation.
CUS_CODE
CUS_LNAME
CUS_POSTCODE
AGENT_CODEAGENT_PHONE1132445WalkerM1 5RT231018124311241217782Adares
NW6 4RT
125
018124398871312243Rakowski678954
16701813426778
1321242RodriguezNW6 2WS
125
018124398871657399Vanloo67543W231
01812431124
1542311SmithsonN4 3YP421NULLSlide37
Right Outer Join Example (cont’d)
A
right outer join
of CUSTOMER and AGENT, will return all the tuples in the AGENT relation, including those that do not have a matching value in the CUSTOMER relation.
CUS_CODE
CUS_LNAME
CUS_POSTCODE
AGENT_CODEAGENT_PHONE1132445WalkerM1 5RT231018124311241217782Adares
NW6 4RT
125
018124398871312243Rakowski678954
16701813426778
1321242RodriguezNW6 2WS
125
018124398871657399Vanloo67543W231
01812431124
NULLNULLNULL33301131234445Slide38
CONSTRUCTING QUERIES USING RELATIONAL ALGEBRAIC EXPRESSIONS
The main purpose of relational algebra is to provide a way to create and manipulate relations (tables) in a database.
During the lifetime of a database, users will ask many different kinds of queries. The task of building a query involves breaking the query down into a number of smaller steps, where each step generates a set of intermediate results which are then used in the steps of the query.Slide39
CONSTRUCTING QUERIES USING RELATIONAL ALGEBRAIC EXPRESSIONS
The following steps should be followed when building a query using
relational algebraic expressions
List all the attributes needed to give the answer
Select all the relations needed based on the list of attributes.
Specify the relational operators and the intermediate results that are neededSlide40
Building Queries Example -
Car Maintenance Database
In a small database that stores information about the maintenance of cars, each car is required to undergo an inspection each year to test to see if it is roadworthy. After each inspection a maintenance record is created and any repairs that are needed are recorded. A repair can require new parts to be purchased and fitted. If a car needs a repair then the EVALUATION is set to FAIL until all the repairs are completed and then it is set to PASS. The Entity Relationship Diagram (ERD) and tables are shown below.Slide41
Car Maintenance Example (cont’d)
Car inspection ERDSlide42
Car Maintenance Example (cont’d)
REGISTRATION
CAR_MAKE
CAR_MODEL
CAR_COLOUR
MODEL_YEAR
LINCENCE_NO
3679MR82ToyotaCorollaBlue20061967fr89768E-TS865
Nissan
Micro
Red20041973Smith121PE57UVPPeugeot
407
Blue20071990byt3212PISE567
Volkswagen
EosLime2006DF-678-WVROMA482
Volkswagen
Golf GTBlack2007AQ-123-AVZ-BA975Peugeot207Black20071980vrt7312PART_NOPART_NAMEPART_COST12390Paint sealantsGhȻ14.9512391WiperGhȻ19.9512392
Brake pads
GhȻ24.99
12393
Brake Discs
GhȻ49.54
12395
Spark Plugs
GhȻ0.99
12396
Airbag
GhȻ24.95
12397
Tyres
GhȻ25.00
CAR
PARTSlide43
Car Maintenance Example (cont’d)
INSPECTION_CODE
REGISTRATION
INSPECTION_DATE
EVALUATION
100036
PE57UVP
10/05/2008FAIL100390ROMA48201/09/2008 106750
E-TS865
01/03/2006
PASS122456Z-BA97503/10/2008FAIL
145678
PISE56730/09/2007PASS200450
E-TS865
21/02/2005PASS200456E-TS86501/04/2007
FAIL
MAINTENANCE_RECORDINSPECTION_CODEPART_NO10675012396106750123971000361239320045012391100036123972004501239220045612397REPAIRSlide44
Car Maintenance Example (cont’d)
Consider the following query asked by a user:
‘List all information about cars where the model year is after 2006’
To answer this query, one must first interpret that ‘
List all information about
cars’ means list all attributes in the relation CAR. The user only wants to see information on cars where the attribute MODEL_YEAR>2006. using the relational operator SELECT we can write the query as a relational algebraic expression as:
Slide45
Car Maintenance Example (cont’d)
REGISTRATION
CAR_MAKE
CAR_MODEL
CAR_COLOUR
MODEL_YEAR
LINCENCE_NOPE57UVPPeugeot407
Blue
2007
1990byt3212ROMA482VolkswagenGolf GTBlack
2007
AQ-123-AVZ-BA975Peugeot
207Black
20071980vrt7312Slide46
Car
Maintenance Assignment
Display all the part names and their prices where the cost of the part is greater than GhȻ20.00
List the car registration and model details and part numbers for all cars where the model year is 2007, where an inspection was carried out after 01/03/2008, which resulted in a part being required for the repair.