RELATIONAL ALGEBRA (III) Prof. Sin-Min LEE Department of Computer Science.

RELATIONAL ALGEBRA RELATIONAL ALGEBRA (III)(III)

Prof. Sin-Min LEEProf. Sin-Min LEE

Department of Computer Department of Computer ScienceScience

Unary Relational Operations: SELECT and Unary Relational Operations: SELECT and PROJECTPROJECT

The PROJECT OperationThe PROJECT Operation Sequences of Operations and the Sequences of Operations and the

RENAME OperationRENAME Operation The SELECT OperationThe SELECT Operation

Relational Algebra Operations from Set Relational Algebra Operations from Set TheoryTheory

The UNION, INTERSECTION, and MINUS The UNION, INTERSECTION, and MINUS OperationsOperations

The CARTESIAN PRODUCT (or CROSS The CARTESIAN PRODUCT (or CROSS PRODUCT) OperationPRODUCT) Operation

Binary Relational Operations: JOIN and Binary Relational Operations: JOIN and DIVISIONDIVISION

The JOIN OperationThe JOIN Operation The EQUIJOIN and NATURAL JOIN The EQUIJOIN and NATURAL JOIN

Variations of JOINVariations of JOIN A Complete Set of Relational Algebra A Complete Set of Relational Algebra

OperationsOperations The DIVISION OperationThe DIVISION Operation

Additional Relational OperationsAdditional Relational Operations

Aggregate Functions and GroupingAggregate Functions and Grouping Recursive Closure OperationsRecursive Closure Operations OUTER JOIN OperationsOUTER JOIN Operations The OUTER JOIN OperationThe OUTER JOIN Operation

SPECIAL RELATIONAL OPERATORSSPECIAL RELATIONAL OPERATORS

The following operators are peculiar to relations:The following operators are peculiar to relations:

- - Join operatorsJoin operators

There are several kind of join operators. We only consider There are several kind of join operators. We only consider three of these here (others will be considered when we three of these here (others will be considered when we discuss null values):discuss null values):

- (1) Condition Joins- (1) Condition Joins

- (2) Equijoins- (2) Equijoins

- (3) Natural Joins- (3) Natural Joins

- - DivisionDivision

JOIN OPERATORSJOIN OPERATORS Condition Joins: Condition Joins: - Defined as a cross-product followed by a selection:- Defined as a cross-product followed by a selection:

R R ⋈⋈cc S = S = σσcc(R (R S) ( S) ( is called the ⋈ is called the ⋈bow-tie)bow-tie)where c is the condition.where c is the condition.

- - Example:Example:Given the sample relational instances S1 and R1Given the sample relational instances S1 and R1

The condition join S ⋈S1.sid<R1.sid R1 yields

EquijoinEquijoin::Special case of the condition join where the join condition consists solely Special case of the condition join where the join condition consists solely

of equalities between two fields in R and S connected by the logical of equalities between two fields in R and S connected by the logical AND operator (AND operator ( ).∧).∧

ExampleExample: Given the two sample relational instances S1 and R1: Given the two sample relational instances S1 and R1

The operator S1 R.sid=Ssid R1 yields

Natural JoinNatural Join

- Special case of equijoin where equalities are implicitly - Special case of equijoin where equalities are implicitly specified on specified on all all fields having the same name in R and S.fields having the same name in R and S.

- The condition c is now left out, so that the “bow tie” - The condition c is now left out, so that the “bow tie” operator by itself signifies a natural join.operator by itself signifies a natural join.

- - N. B.N. B. If the two relations have no attributes in common, the If the two relations have no attributes in common, the natural join is simply the cross-product.natural join is simply the cross-product.

Functional DependencyFunctional Dependency

holds on schema R if, in any holds on schema R if, in any legal relation r(R), for all pairs of legal relation r(R), for all pairs of tuples ttuples t11 and t and t22 in r such that t in r such that t11[[] ] = t= t22[[], it is also the case that t], it is also the case that t11

[[] = t] = t22 [ [].].

Functional DependenciesFunctional Dependencies

FDs defined over two FDs defined over two sets of attributes: X, sets of attributes: X, Y Y R R

Notation: X Notation: X Y reads Y reads as “X determines Y”as “X determines Y”

If X If X Y, then all tuples Y, then all tuples that agree on X must that agree on X must also agree on Yalso agree on Y

X Y Z

1 2 3

2 4 5

1 2 4

1 2 7

2 4 8

3 7 9

R

AAC, but not CC, but not CAA

AA BB CC DD

tt00 aa11 bb11 cc11 dd11






Minimal cover: AMinimal cover: AB, DB, DBB{A, B, C, D} is a candidate key{A, B, C, D} is a candidate key

RR (A(A BB CC D)D)

11 22 11 22

22 33 11 33

11 22 33 11

33 22 11 33

11 22 33 22

X Y Z

1 2 3

2 4 5

1 2 4

1 2 7

2 4 8

3 7 9

X Y Z

Functional Dependencies Functional Dependencies GraphGraph(example)(example)

ClosureClosure

Let F be a set of functional Let F be a set of functional dependencies.dependencies.

The closure of F, denoted by FThe closure of F, denoted by F++, is , is the set of all functional dependencies the set of all functional dependencies logically implied by F.logically implied by F.

Minimal coverMinimal cover

The concept of minimal cover of F is The concept of minimal cover of F is sometimes called Irreducibe Set of F. To sometimes called Irreducibe Set of F. To find the minimal cover of a set of functional find the minimal cover of a set of functional dependencies F, we transform F such that dependencies F, we transform F such that each FD in it that has more than one each FD in it that has more than one attribute in the right hand side is reduced attribute in the right hand side is reduced to a set of FDs that have only one attribute to a set of FDs that have only one attribute on the right hand side. on the right hand side.

The minimal cover of F is then a set of The minimal cover of F is then a set of FDs such that: FDs such that:

(a) every right hand side of each dependency is (a) every right hand side of each dependency is a single attribute; a single attribute;

(b) for no X -> A in F is the set F - {X -> A} (b) for no X -> A in F is the set F - {X -> A} equivalent to F; equivalent to F;

(c) for no X -> A in F and proper subset Z of X is (c) for no X -> A in F and proper subset Z of X is F - {X -> A} U {Z -> A} equivalent to F. F - {X -> A} U {Z -> A} equivalent to F.

ALGORITHM. Finding a minimal cover G for F

1. set G := F;2. replace each functional dpendency X->A1,A2,...,An in G by the n functional dependencies X->A1,X->A2,...,X->An;3. for each functional dependency X -> A in G for each attribute B that is an element of X {if G is equivalent to ((G - (X->A)) UNION ((X-B)->A)) then replace X->A with (X-B)->A in G}4. for each remaining functional dependency X -> A in G {compute X+ with respect to the set of dependencies (G - (X->A)); if X+ contains A, then remove X->A from G}

Note: In step 3 to determine if G is equivalent to ((G - (X->A)) UNION ((X-B)->A)) you need to see if (X-B)+ in G contains A. If it does then they are equivalent.

R(A,B,C,D) F={AB->CD}Following algorithm:2. G={AB->C,AB->D}3. a) Try to replace AB->C with B->C: {AB->C,AB->D} is not equivalent to {B->C,AB->D} Note that B+ wrt (with respect to) G ={B}. Since it does not contain C, they are not equivalent. b) Try to replace AB->C with A->C: {AB->C,AB->D} is not equivalent to {A->C,AB->D} Note that A+ wrt G ={A}. Since it does not contain C, they are not equivalent. c) Try to replace AB->D with B->D: {AB->C,AB->D} is not equivalent to {AB->C,B->D} Note that B+ wrt G ={B}. Since it does not contain D, they are not equivalent.

d) Try to replace AB->D with A->D: {AB->C,AB->D} is not equivalent to {AB->C,A->D} Note that A+ wrt G ={A}. Since it does not contain D, they are not equivalent. Therefore, we can not make any changes to G in this step.4. a) Try to remove AB->C: We can do this if G is equivalent to H={AB->D} However, AB+ wrt G = {A,B,C,D} <> AB+ wrt H = {A,B,D} b) Try to remove AB->D: We can do this if G is equivalent to I={AB->C} However, AB+ wrt G = {A,B,C,D} <> AB+ wrt I = {A,B,C} Therefore we can not make any changes to G in this step.

Therefore, F is a minimal cover.

R={A,B,C,D,E,F}G={AB->C,B->CD,D->EF,B->F}

ALTERNATIVE I (Synthesis Approach - p 422 in Ramakrishnan)

Place into Minimal Cover (p420 in Ramakrishnan Book):

1) G1={AB->C,B->C,B->D,D->E,D->F,B->F}

2) Remove extra attributes on LHS; AB->C: Can remove A as B+ in G1 does contain C. Thus we get G2={B->C,B->D,D->E,D->F,B->F} Don't need to look at remaining FD because all only have 1 attribute on LHS.

3) Remove extra FD from G2:

B->C: Can't be removed since B+ would then not contain C. No other FD in G2 have C on RHS. B->D: Can't be removed since B+ would then not contain D. No other FD in G2 have D on RHS. D->E: Can't be removed since D+ would then not contain E. No other FD in G2 have E on RHS. D->F: Can't be removed since D+ would not contain F. B->F: Can be removed since B->D,D->F. Thus we have the Minimal Cover: G3={B->C,B->D,D->E,D->F}

We now decompose. We get: R1={B,C,D} R2={D,E,F}

Note that A is not in either scheme, also we need to add anotherscheme because neither of these contain a candidate key. Since A,B are not on the RHS any candidate key must containthem. AB+={A,B,C,D,E,F}=R. Thus AB is the key. Since no scheme contains AB, we must add one more scheme: R3={A,B}So we have: R1={B,C,D}, F1={B->C,B->D} R2={D,E,F}, F2={D->E,D->F} R3={A,B}, F3={}Note that this is dependency preserving as F1 union F2 = G3.Is this lossless? (See p 414 in Ramakrishnan) R1 intersect R2 = {D} and D-> R2 (R1 union R2) intersect R3 = {B} and B->{B,C,D,E,F} Therefore this is lossless

ALTERNATIVE II (Decomposition Approach - p 421 in Ramakrishnan)Decompose R using BCNF Decomposition (p416 in Ramakrishnan) From above we know that AB is the key. So we need to look at any FD in F where the LHS is not a superkey. We have the following that are not superkeys: {B->CD,D->EF,B->F} Decompose using B->CD into: R1={A,B,E,F}, R2={B,C,D} F1={B->EF}, F2={B->CD} R2 is in BCNF, but R1 is not. So we split R1 into: R6={A,B}, R7{B,E,F} F6={}, F7={B->EF}

We put F into minimal cover: G3={B->C,B->D,D->E,D->F}

The following functional dependencies are not preserved: {D->E,D->F} So we create a relation scheme for each of these: R3={D,E}, R4={D,F} F3={D->E}, F4={D->F}

Combining R3 and R4, we get:

R6={A,B}, F1={} R7={B,E,F}, F1={B->EF} R2={B,C,D}, F2={B->CD} R5={D,E,F}, F5={D->EF}

This is dependency preserving and lossless.

Candidate KeysCandidate Keys

an attribute (or set of attributes) that an attribute (or set of attributes) that uniquely identifies a rowuniquely identifies a row

primary key is a special candidate keyprimary key is a special candidate key values cannot be nullvalues cannot be null

e.g. e.g. ENROLL (Student_ID, Name, Address, …)ENROLL (Student_ID, Name, Address, …)

• PK = Student_IDPK = Student_ID• candidate key = Name, Addresscandidate key = Name, Address

… … candidate keycandidate key

a candidate key must satisfy:a candidate key must satisfy: unique identification. unique identification.

• implies that each nonkey attribute is functionally implies that each nonkey attribute is functionally dependent on the key (for not(A dependent on the key (for not(A B) to be true, A B) to be true, A must occur more than once (with a different B), or must occur more than once (with a different B), or A must map to more than one B in a given row)A must map to more than one B in a given row)

nonredundancy nonredundancy • no attribute in the key can be deleted and still be no attribute in the key can be deleted and still be

uniqueunique• minimal set of columns (Simsion)minimal set of columns (Simsion)

keys and dependencieskeys and dependencies

EMPLOYEE1 (Emp_ID, Name, Dept_Name, Salary)

Emp_IDEmp_ID NameName Dept_NameDept_Name SalarySalary

functional dependency

determinant

EMPLOYEE2 (Emp_ID, Course_Title, Name, Dept_Name, Salary, Date_Completed)

Emp_IDEmp_IDCourse_Course_

TitleTitleNameName

Dept_Dept_

NameName SalarySalary Date_Date_

Comp.Comp.

not fully functionally dependant on the primary key

Trivial Functional DependencyTrivial Functional Dependency

In general, a functional dependency In general, a functional dependency of the form of the form is trivial if is trivial if

(Example) A(Example) AB, BCB, BCCC

RELATIONAL ALGEBRA (III) Prof. Sin-Min LEE Department of Computer Science.

Documents

Transcript of RELATIONAL ALGEBRA (III) Prof. Sin-Min LEE Department of Computer Science.