Appendix: Advanced Queries [317]

Example Query: 6.6.3, 8.3.16

Query

Get SNAME who supply ALL parts.

# Assume that S2 does not supply P3

SELECT DISTINCT S.SNAME
FROM   S                   # 5. then S2 will not be in final result set
WHERE  NOT EXISTS          # 4. then this will be FALSE 
    (  
       # 3. then P3 will be in result set
       SELECT *     
       FROM P
       WHERE  NOT EXISTS   # 2. then this will be TRUE for P3
           (
              # 1. since S2 does not supply P3, then result will be empty
              SELECT *              
              FROM   SP
              WHERE  SP.S# = S.S#
              AND    SP.P# = P.P# ) 
           )
     ) ;

GOOD GUYS: TRUE on first NOT EXISTS, FALSE on second NOT EXISTS

Example Query: 7.3.3, 6.6.1 [318]

Query

Get SNAME who supply P2.

Algebra: $\pi_{SNAME}\sigma_{P\char93 =''P2''}(S \Join SP)$

Tuple Calculus:

{ $t(SNAME) \vert \exists u \exists v (S(u) \wedge SP(v) \wedge u[S\char93 ] = v[S\char93 ] \wedge \\ v[P\char93 ] = \lq P2$' $\wedge u[SNAME] = t[SNAME])$ }

Domain Calculus:

{ $n(SNAME) \vert \exists s \exists c \exists q (S(s,n,c) \wedge SP(s,\lq P2$'$,q))$ }

Example Query: 7.3.7, 6.6.6 [319]

Query

Get SNAME (S#) who do NOT supply P2.

A shipment does not exist where the supplier supplies P2.

Algebra: $\pi_{SNAME}(S \Join (\pi_{S\char93 }(S) - \pi_{S\char93 }\sigma_{P\char93 =''P2''}(SP)))$

SQL: NOT IN (NOT EXISTS)

Tuple Calculus (Warning: the following are not SAFE):

{ $t(SNAME) \vert\neg ( \exists u \exists v (S(u) \wedge SP(v) \wedge u[S\char93 ] = v[S\char93 ] \wedge \\ v[S\char93 ] = \lq P2$' $\wedge u[SNAME] = t[SNAME]))$ }

{ $t(S\char93 ) \vert \forall u (SP(u) \wedge u[S\char93 ] = t[S\char93 ] \Rightarrow u[P\char93 ] \neq \lq P2$') }

Remember: $\forall x(x \Rightarrow y) \equiv \neg \exists x(x \wedge \neg y)$

{ $t(S\char93 ) \vert \neg \exists u(SP(u) \wedge u[S\char93 ] = t[S\char93 ] \wedge u[P\char93 ] = \lq P2$') }

Domain Calculus:

{ $n(SNAME) \vert \neg ( \exists s \exists c \exists q (S(s,n,c) \wedge SP(s,\lq P2$'$,q)))$ }

{ $s(S\char93 ) \vert \forall p \forall q (SP(s,p,q) \Rightarrow p \neq \lq P2$') }

Example Query: 7.3.6, 6.6.3 [320]

Query

Get S# who supply ALL parts.

Algebra: $\pi_{S\char93 ,P\char93 }(SP) \div \pi_{P\char93 }(P)$

Algebra: $\pi_{S\char93 }(SP) - \pi_{S\char93 }(\pi_{S\char93 }(SP) \times \pi_{P\char93 }(P) - \pi_{S\char93 ,P\char93 }(SP))$

SQL: NOT IN, NOT IN (NOT EXISTS, NOT EXISTS)

Tuple Calculus:

{ $t(S\char93 ) \vert \forall u ( P(u) \Rightarrow \exists v ( SP(v) \wedge u[P\char93 ] = v[P\char93 ] \wedge v[S\char93 ] = t[S\char93 ]))$ }

Remember: $\forall x(x \Rightarrow y) \equiv \neg \exists x(x \wedge \neg y)$

{ $t(S\char93 ) \vert \neg \exists u ( P(u) \wedge \neg \exists v ( SP(v) \wedge u[P\char93 ] = v[P\char93 ] \wedge v[S\char93 ] = t[S\char93 ]))$ }

Domain Calculus:

{ $s(S\char93 ) \vert \forall p \forall n \forall c ( P(p,n,c) \Rightarrow \exists q ( SP(s,p,q)))$ }

{ $s(S\char93 ) \vert \neg \exists p n c ( P(p,n,c) \wedge \neg \exists q ( SP(s,p,q)))$ }

A part does not exist for which the supplier does not have a shipment.

Divide in Calculus [321]

Define schema $C(C_1,C_2,...,C_m); B(B_1,B_2,...,B_n); A(C,B)$

$A \div B = C = \pi_C(A) - \pi_C [ \pi_C(A)\times B - A ]$

Tuple Calculus:

{
$t(C_1,C_2,...,C_m) \vert \exists w(B(w)) \wedge \forall u (B(u) \Rightarrow \ex... ...\wedge u[B_n] = v[B_n] \wedge \\ v[C_1] = t[C_1] ... \wedge v[C_m] = t[C_m]))$
}

Domain Calculus:

{
$c_1(C_1),c_2(C_2),...,c_m(C_m) \vert \exists b_1,b_2,...,b_n (B(b_1,b_2,...,b_n... ...\forall b_n (B(b_1,b_2,...,b_n) \Rightarrow A(c_1,c_2,...,c_m,b_1,b_2,...,b_n))$
}

Safety of Expressions [322]

Evaluation:

Limited (universe restricted to database instance): $\{x(R)\}=R_{DOM}$

Unlimited (no restriction): $\vert\{x(R)\}\vert = \infty$

Goal: same result relation independent of limited or unlimited

Example: $\{x(R) \vert \neg R(x)\}$

Limited: empty relation

Unlimited: infinite relation

Solution: $\{x(R)\vert \neg R(x) \wedge R_{DOM}(x)\}$

where $R_{DOM} = \pi_{A_1}(R) \times \pi_{A_2}(R) \times ... \times \pi_{A_n}(R)$

Safety of Expressions [323]

Example: $\exists x(R) (x[A] = \lq red$')

Limited: maybe true

Unlimited: always true

Solution: $\exists x(R) ( R(x) \wedge x[A] = \lq red$')

Example: $\{x(R) \vert x[A] = \lq red$'}

Limited: finite relation

Unlimited: infinite relation

Solution: $\{x(R) \vert R(x) \wedge x[A] = \lq red$'}

Solution: $\{x(R) \vert \exists y(R1) (R1(y) \wedge y[A] = \lq red$' $\wedge y[A] = x[A])\}$

Safety of Expressions [324]

Example: $\forall x(R) (x[A] = \lq red$')

Limited: maybe true

Unlimited: always false

Wrong Solution: $\forall x(R) (R(x) \wedge x[A] = \lq red$')

Solution: $\forall x(R) (R(x) \Rightarrow x[A] = \lq red$')

but this could be vacuously true (empty $R$) or

Example: $\forall x(R) (R(x) \wedge x[B] = 9999 \Rightarrow x[A] = \lq red$')

but there are no 9999's

Solution: $\exists x(R)(R(x) \wedge x[B] = 9999) \wedge \forall x(R) (...)$