Optimization [294]

Chase tableau using known FD's to eliminate rows.

$\pi_{AB}(r) \Join \pi_{BC}(r)$ and $B \rightarrow C$

a	b	c1
a1	b	c
a	b	c

$\Rightarrow$

a	b	c
a1	b	c
a	b	c

Use $a1 \rightarrow a$

a	b	c
a	b	c
a	b	c

$\Rightarrow$

a	b	c
a	b	c

= r, the join was not required.

Algorithm to Chase Tableau [295]

Iterate until no more changes, for each FD

• if both non-distinguished, pick either
• if one distinguished, pick it
• if constant and variable, pick constant
• if both constants but different, ERROR
• all changes are universal

Minimize with Chase [296]

$\pi_{AB}(\pi_{AC}(\pi_{AB}(r) \Join \pi_{BC}(r)) \Join \pi_{BC}(\pi_{AC}(r) \Join \pi_{AB}(r)))$

$B \rightarrow C, C \rightarrow A$

Chase with $B \rightarrow C$

a	b1	c1
a1	b1	c3
a2	b2	c3
a2	b	c2
a	b

$\Rightarrow$

a	b1	c3
a1	b1	c3
a2	b2	c3
a2	b	c2
a	b

Chase with $C \rightarrow A$

a	b1	c3
a	b1	c3
a	b2	c3
a	b	c2
a	b

Minimize with $b1 \rightarrow b,b2 \rightarrow b, c3 \rightarrow c2$

a	b	c2
a	b

$= \pi_{AB}(r)$

Minimize with Chase [297]

$\pi_{AB}(\pi_{AC}(r) \Join \pi_{BC}(r)) \Join \pi_{BC}(\pi_{AB}(r) \Join \pi_{AC}(r))$

$AC \rightarrow B,B \rightarrow C,C \rightarrow A$

Chase with $B \rightarrow C$

a	b1	c1
a1	b	c1
a2	b	c2
a2	b2	c
a	b	c

$\Rightarrow$

a	b1	c1
a1	b	c1
a2	b	c1
a2	b2	c
a	b	c

Chase with $C \rightarrow A$ and with $AC \rightarrow B$

a	b1	c1
a	b	c1
a	b	c1
a	b2	c
a	b	c

$\Rightarrow$

a	b	c1
a	b	c1
a	b	c1
a	b2	c
a	b	c

Remove duplicate rows

a	b	c1
a	b2	c
a	b	c

$\pi_{AB}(r) \Join \pi_{AC}(r)$

Multi-Valued Dependencies [298]

FD's tell us what can NOT be in a relation.

MVD's tell us what MUST be in a relation.

C	T	H	R	S	G
CS101	deadwood	M9	222	klunk	B
CS101	deadwood	W9	333	klunk	B
CS101	deadwood	F9	222	klunk	B
CS101	deadwood	M9	222	zonker	C
CS101	deadwood	W9	333	zonker	C
CS101	deadwood	F9	222	zonker	C

Course multidetermines Hour Room: $C \rightarrow \rightarrow HR$

That is, if you see

CS101,deadwood,M9,222,klunk,B

CS101,deadwood,W9,333,zonker,C

in the relation, then we would expect to find

CS101,deadwood,W9,333,klunk,B

CS101,deadwood,M9,222,zonker,C

which is the case. Note we have just exhanged HR pairs

Optimize Query with FD,MVD Chase [299]

$R = ABCD, F= D \rightarrow C, B \rightarrow \rightarrow AC$

$\pi_{AB}(r) \Join \pi_{BC}(r)$

Chase with MVD, expand the table,toggle AC

A	B	C	D
a	b	c1	d1
a1	b	c	d2

$\Rightarrow$

a	b	c1	d1
a1	b	c	d2
a1	b	c	d1
a	b	c1	d2

Chase with FD and minimize with $a1 \rightarrow a,d2 \rightarrow d1$

a	b	c	d1
a1	b	c	d2
a1	b	c	d1
a	b	c	d2

$\Rightarrow$

a

b

c

d1

$=\pi_{ABC}(r)$

Query Optimization: Expression Trees [300]

Make a tree structure of the relational algebra query.

• merge into a DAG common subexpressions
• optimize expression by
  • pushing down projections
  • pushing down selections
  • pushing up (delaying) joins

Find Common Subexpressions [301]

$(r1 \Join r2 \Join r3) \cup (r2 \Join r3 \Join r4)$

Do not need to do the $r2 \Join r3$ operation twice

Find Common Subexpressions [302]

$q \Join \pi_{X}(r \Join s) \Join \pi_{X}(r \Join s)$

Optimize Algebraic Expression [303]

A database of pilots, examiners and ratings:

rp(P#,PN,BD), re(E#,EN), rt(P#,E#,DT,RG)

`Find pilots that have been given a low rating by Randy and who are based in the same place as some other pilot who has been given a low rating since 1 June.'

$\pi_{PN}(\sigma_{PN \not =PN1}(\sigma_{EN=Randy \wedge RG <= 6.5}$ $(rp \Join re \Join rt) \Join \pi_{PN1 BD}(\delta_{PN \rightarrow PN1}$ $(\sigma_{DT >= 1June \wedge RG <= 6.5}(rp \Join rt)))))$

Optimize Algebraic Expression [304]

Push down $\sigma$ [305]

Project only needed attributes [306]