BAZA SPJ

TABELA S - snabdevač:

TABELA P - deo:

TABELA J - projekat:

TABELA SPJ - pošiljka :

Relaciona algebra i relacioni račun

Primer 1: Pronaći snabdevača koji isporučuje najveću isporuku.

Relaciona algebra:

DEFINE ALIAS SPJ1 FOR SPJ
DEFINE ALAIAS SPJ2 FOR SPJ

( SPJ [SRBR, KOL]
MINUS
(( SPJ1 TIMES SPJ2) WHERE SPJ1.KOL < SPJ2. KOL) [ SPJ1.SRBR, SPJ1.KOL]) JOIN S) [SIME]

Relacioni racun:

RANGE OF SX IS S
RANGE OF SPJX IS SPJ

SX.SIME
WHERE EXISTS SPJX ( SPJX.SRBR=S.SRBR AND NOT EXISTS SPJY ( SPJY.KOL > SPJX.KOL))

Primer 2: Izdvojiti šifru projekta koji snabdeva bar jedan snabdevač koji nije u istom gradu kao i projekat.

Relaciona algebra:

((J TIMES S WHERE J.GRAD<>S.GRAD) JOIN SPJ)[JRBR]

Relacioni račun:

RANGE OF SX IS S
RANGE OF JX IS J
RANGE OF SPJX IS SPJ

JX.JRBR
WHERE EXISTS SPJX EXISTS SX(
SPJX.SRBR=SX.SRBR AND SPJX.JRBR=JX.JRBR AND JX.GRAD<>SX.GRAD)

Primer 3: Izdvojiti ime snabdevača koji isti deo isporučuje svim projektima.

Relaciona algebra:

((SPJ[SRBR, PRBR, JRBR]
DIVIDE BY
J[JRBR])
JOIN S)[SIME]

Relacioni račun:

RANGE OF SX IS S
RANGE OF JX IS J
RANGE OF PX IS P
RANGE OF SPJX IS SPJ

SX.SIME
WHERE EXISTS PX (FORALL JX ( EXISTS SPJX(
SPJX.SRBR=SX.SRBR AND SPJX.JRBR=JX.JRBR AND SPJX.PRBR=PX.PRBR)))

Primer 4: Pronaći redne brojeve snabdevača koji deo 'P2' isporučuju za tačno dva projekta.

Relaciona algebra:

DEFINE ALIAS SPJ1 FOR SPJ
DEFINE ALIAS SPJ2 FOR SPJ
DEFINE ALIAS SPJ3 FOR SPJ

((SPJ1 TIMES SPJ2) WHERE SPJ1.SRBR=SPJ2.SRBR AND SPJ1.PRBR='P2' AND SPJ2.PRBR='P2' AND SPJ1.JRBR<>SPJ2.JRBR) [SRBR]

MINUS

((SPJ1 TIMES SPJ2 TIMES SPJ3) WHERE
SPJ1.SRBR=SPJ2.SRBR AND SPJ2.SRBR=SPJ3.SRBR
AND SPJ1.PRBR='P2' AND SPJ2.PRBR='P2' AND SPJ3.PRBR='P2'
AND SPJ1.JRBR<> SPJ2.JRBR AND SPJ2.JRBR<>SPJ3.JRBR AND SPJ1.JRBR<>SPJ3.JRBR) [SRBR]

Relacioni račun:

RANGE OF SX IS S
RANGE OF SPJX IS SPJ
RANGE OF SPJY IS SPJ
RANGE OF SPJZ IS SPJ

SX.SRBR
WHERE EXISTS SPJX(
SPJX.SRBR=SX.SRBR AND SPJX.PRBR='P2' AND EXISTS SPJY(
SPJY.SRBR=SX.SRBR AND SPJY.PRBR='P2' AND SPJX.JRBR<>SPJY.JRBR AND NOT EXISTS SPJZ(
SPJZ.SRBR=SX.SRBR AND SPJZ.PRBR='P2' AND SPJZ.JRBR<>SPJY.JRBR AND SPJZ.JRBR<>SPJX.JRBR)))

Primer 5: Pronaći projekte za koje se isporučuju delovi od različitih snabdevača iz istog grada (različiti snabdevači ne moraju da isporučuju isti deo projektu). Izdvojiti šifru projekta i grad snabdevača.

Relaciona algebra:

DEFINE ALIAS S1 FOR S
DEFINE ALIAS S2 FOR S
DEFINE ALIAS SPJ1 FOR SPJ
DEFINE ALIAS SPJ2 FOR SPJ

((S1 JOIN SPJ1) TIMES (S2 JOIN SPJ2) WHERE S1.SRBR<>S2.SRBR AND SPJ1.JRBR=SPJ2.JRBR AND S1.GRAD=S2.GRAD) [SPJ1.JRBR, S1.GRAD]

Relacioni račun:

RANGE OF JX IS J
RANGE OF SX IS S
RANGE OF SY IS S
RANGE OF SPJX IS SPJ
RANGE OF SPJY IS SPJ

JX.JRBR, SX.GRAD
WHERE EXISTS SPJX (
SPJX.SRBR=SX.SRBR AND JX.JRBR=SPJX.JRBR) AND EXISTS SPJY(
SPJY.JRBR=JX.JRBR AND SPJY.SRBR<>SPJX.SRBR AND EXISTS SY (
SY.SRBR=SPJY.SRBR AND SY.GRAD=SX.GRAD))

Primer 6: Pronaći snabdevače koji:

• isporučuju SRAF za sve projekte

  Relaciona algebra:

  ((P WHERE PIME='SRAF') JOIN SPJ)[SRBR, JRBR]
  DIVIDE BY
  J[JRBR]

  Relacioni račun:

  RANGE OF SX IS S
  RANGE OF JX IS J
  RANGE OF PX IS P
  RANGE OF SPJX IS SPJ

  SX.SRBR
  WHERE NOT EXISTS JX (NOT EXISTS SPJX (
  SPJX.SRBR=SX.SRBR AND SPJX.JRBR=JX.JRBR AND EXISTS PX ( PX.PRBR=SPJX.PRBR AND PX.PIME='SRAF')))

• ne isporučuju SRAF za sve projekte ali isporučuju nekom projektu

Relaciona algebra:

(SPJ JOIN (P WHERE PIME='SRAF'))[SRBR]
MINUS
(((P WHERE PIME='SRAF') JOIN SPJ)[SRBR, JRBR]
DIVIDE BY J[JRBR])

Relacioni račun:

RANGE OF SX IS S
RANGE OF JX IS J
RANGE OF PX IS P
RANGE OF SPJX IS SPJ

SX.SRBR
WHERE EXISTS JX ( NOT EXISTS SPJX (
SPJX.SRBR=SX.SRBR AND SPJX.JRBR=JX.JRBR AND EXISTS PX ( PX.PRBR=SPJX.PRBR AND PX.PIME='SRAF')))
AND
EXISTS SPJX ( SPJX.SRBR=SX.SRBR AND EXISTS PX (PX.PIME='SRAF' AND PX.PRBR=SPJX.PRBR))

• ne isporučuju SRAF za sve projekte.

Relaciona algebra:

S[SRBR]
MINUS
(P WHERE PIME='SRAF') JOIN SPJ)[SRBR, JRBR]
DIVIDE BY J[JRBR]

Relacioni račun:

RANGE OF SX IS S
RANGE OF JX IS J
RANGE OF PX IS P
RANGE OF SPJX IS SPJ

SX.SRBR
WHERE EXISTS JX ( NOT EXISTS SPJX (
SPJX.SRBR=SX.SRBR AND SPJX.JRBR=JX.JRBR AND EXISTS PX (
PX.PRBR=SPJX.PRBR AND PX.PIME='SRAF')))