Tableau [215]

Tableau are used for restricted algebraic expressions:

• select in the form $\sigma_{A = c}$
• project $\pi_{R}$
• natural join $\Join$

The goal will be

• (convert `declarative' query to 'procedural' algebra)
• convert algebraic expression to tableau
• minimize the number of joins required
• convert tableau to algebraic expression
• (implement procedurally the algebraic expression)

Tableau: Example [216]

Schema

employees(NAME,COMPANY,LOCATION)
e(NCL)

r

N	C	L
sue	ibm	ny
mary	xerox	la
joe	ibm	la

Query

Get the employees and their locations for people working for the same company.

Algebra

$\pi_{NL}(\pi_{NC}(r) \Join \pi_{CL}(r))$

Domain

{ $a(N)c(L) \vert \exists a_{1}(N) \exists b(C) \exists c_{1}(L) (r(abc_{1}) \wedge r(a_{1}bc))$}

Tableau: Example [217]

N	C	L
a	b$_{1}$	c$_{1}$
a$_{1}$	b$_{1}$	c
a		c

• each column is named by attribute
• a variable may only appear in one column
• non-subscripted variables are distinguished
• subscripted variables are not distinguished
• subscripted variables have been projected out
• projection corresponds to distinguished
• constants are allowed for selection
• if a distinguished variable appears in a column, it must also appear in the summary for that column
• the summary row has blanks if constants and variables have been projected out
• join appends the rows of each tableau
• N rows $\Rightarrow$ N-1 joins

Tableau: Example [218]

e(NCL): $\pi_{NL}(\pi_{NC}(r) \Join \pi_{CL}(r))$

$\pi_{NL}$

N	C	L
a	b1	c1
a1	b1	c
a		c

$\uparrow$

$\Join$

N	C	L
a	b	c1
a1	b	c
a	b	c

$\nearrow \nwarrow$

$\pi_{NC}$

N	C	L
a	b	c1
a	b

$\pi_{CL}$

N	C	L
a1	b	c
	b	c

Tableau: Example [219]

r(ABC): $\pi_{AC}(\sigma_{C=\lq cat\lq }(\pi_{AB}(r) \Join \pi_{BC}(r)))$

$\pi_{AC}$

A	B	C
a	b1	c1
a1	b1	CAT
a		CAT

$\uparrow$

$\sigma_{C=cat}$

A	B	C
a	b	c1
a1	b	CAT
a	b	CAT

$\uparrow$

$\Join$

A	B
a	b	c1
a1	b	c
a	b	c

$\nearrow \nwarrow$

$\pi_{AB}$

A	B	C
a	b	c1
a	b

$\pi_{BC}$

A	B	C
a1	b	c
	b	c

Tableau to Algebra [220]

Look for the last operation performed, work backwards

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

• no constants $\Rightarrow$ no selection
• everything distinguished $\Rightarrow$ no projection
• must be $\Join$, but cannot be on non-distinguished

a	b1	c1
a1	b	c1
a	b

$\Join$

a	b2	c2
a2	b2	c
a		c

$\pi_{AB}$

a	b1	c
a1	b	c
a	b	c

$\Join \pi_{AC}$

a	b	c2
a2	b	c
a	b	c

$\pi_{AB}((ab1c) \Join (a1bc)) \Join \pi_{AC}((abc2) \Join (a2bc))$

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

Minimize Tableau [221]

Mapping such that duplicate rows can be removed

• non-distinguished to distinguished
• or other non-distinguished

a	b1	c1
a2	b2	c
a1	b1	c
a		c

homomorphism h: $c \rightarrow c,b2 \rightarrow b1, a2 \rightarrow a1$

Result, remove row 2

a	b1	c1
a1	b1	c
a		c

Start over with a new row

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

can not be minimized since the substitution is global.

Minimize Tableau: Example [222]

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

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

Minimize: remove row 3 with $b2 \rightarrow b1,c2 \rightarrow c1$

a	b1	c1
a1	b1	c
a2	b3	c1
a		c

MUST Minimize: remove row 3 with $b3 \rightarrow b1,a2 \rightarrow a$

a	b1	c1
a1	b1	c
a		c

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

Minimize Tableau: Example [223]

meal(Flight,Date,Meal,Option,Nummeals)

$\pi_{FD}(\pi_{DM}(\pi_{FOM}(\sigma_{F=56}(meal)) \Join \pi_{DO}(meal)) \Join meal)$

$\sigma_{F=56}$

F	D	M	O	N
56	d	m	o	n
56	d	m	o	n

$\pi_{FOM}$

F	D	M	O	N
56	d1	m	o	n1
56		m	o

$\pi_{DO}$

F	D	M	O	N
f1	d	m1	o	n2
	d		o

$\Join$

F	D	M	O	N
56	d1	m	o	n1
f1	d	m1	o	n2
56	d	m	o

$\pi_{DM}$

F	D	M	O	N
56	d1	m	o1	n1
f1	d	m1	o1	n2
	d	m

$\Join meal$

F	D	M	O	N
56	d1	m	o1	n1
f1	d	m1	o1	n2
f	d	m	o	n
f	d	m	o	n

$\pi_{FD}$

F	D	M	O	N
56	d1	m2	o1	n1
f1	d	m1	o1	n2
f	d	m2	o2	n3
f	d

Minimize Tableau: Example [224]

$\pi_{AB}(\pi_{ACD}(r) \Join \pi_{C}(\sigma_{B=2 \wedge D=3}(r)) \Join \pi_{BD}(r))$

$\sigma_{B=2 \wedge D=3}$

A	B	C	D
a	2	c	3
a	2	c	3

$\pi_{C}$

A	B	C	D
a1	2	c	3
		c

$\pi_{ACD}$

A	B	C	D
a	b2	c	d
a		c	d

$\Join$

A	B	C	D
a	b2	c	d
a1	2	c	3
a		c	d

$\pi_{BD}$

A	B	C	D
a2	b	c1	d
	b		d

$\Join$

A	B	C	D
a	b2	c	d
a1	2	c	3
a2	b	c1	d
a	b	c	d

$\pi_{AB}$

A	B	C	D
a	b2	c2	d2
a1	2	c2	3
a2	b	c1	d2
a	b

Minimize Tableau: Example [225]

Minimize

remove row3, $c2 \rightarrow c1,d2 \rightarrow d1$ remove row4, $a2 \rightarrow a1,c3 \rightarrow c$

A	B	C	D
a	b1	c1	d1
a1	b1	c	4
a	b1	c2	d2
a2	b1	c3	4
a		c

A	B	C	D
a	b1	c1	d1
a1	b1	c	4
a		c

Convert to Algebra

No constant on summary $\Rightarrow$ no selection.

Everything on summary not dist. $\Rightarrow$ projection.

$\pi_{AC}$

a	b	c1	d1
a1	b	c	4
a	b	c	4

$\pi_{AC}(\sigma_{D=4}$

a	b	c1	d1
a1	b	c	d
a	b	c	d

Everything on summary is dist $\Rightarrow$ no projection.

a	b	c1	d1
a	b

$\Join$

a1	b	c	d
	b	c	d

$\pi_{AB}(abcd) \Join \pi_{BCD}(abcd)$

Answer: $\pi_{AC}(\sigma_{D=4}(\pi_{AB}(r) \Join \pi_{BCD}(r)))$

Minimize Tableau: Example [226]

Minimize

Row 1 takes out 2,3, Row 5 takes out 6,7

A	B	C	D	E
a	b1	5	d1	e1
a	b1	c1	d2	e2
a	b2	c2	d3	e3
a1	b1	c3	7	e4
a2	b3	c4	7	e
a3	b4	c5	7	e5
a4	b5	c5	d4	e
a			7	e

A	B	C	D	E
a	b1	5	d1	e1
a1	b1	c3	7	e4
a2	b3	c4	7	e
a			7	e

Convert to Algebra

$\sigma_{D=7}$

a	b1	5	d1	e1
a1	b1	c3	d	e4
a2	b3	c4	d	e
a			d	e

Minimize Tableau: Example [227]

$\sigma_{D=7}(\pi_{ADE}($

a	b1	5	d1	e1
a1	b1	c3	d	e4
a2	b	c4	d	e
a	b	5	d	e

$\sigma_{D=7}(\pi_{ADE}(\sigma_{C=5}($

a	b1	c	d1	e1
a1	b1	c3	d	e4
a2	b	c4	d	e
a	b	c	d	e

a	b1	c	d1	e1
a1	b1	c3	d	e4
a		c	d

$\Join$

a2	b	c4	d	e
	b		d	e

$\pi_{ACD}$

a	b	c	d1	e1
a1	b	c3	d	e
a	b	c	d	e

$\Join \pi_{BDE}(r)$

a

b

c

d1

e1

$\Join$

a1

b

c3

d

e

Answer: $\sigma_{D=7}(\pi_{ADE}(\sigma_{C=5}(\pi_{ACD}(\pi_{ABC}(r) \Join \pi_{BDE}(r)) \Join \pi_{BDE}(r))))$