Tabela DOSIJE
INDEKS | IME | PREZIME | MESTORODJENJA | DATUPISA |
---|
Tabela PREDMET
IDPREDMETA | OZNAKA | NAZIV | ESPB |
---|
Tabela ISPITNIROK
SKGODINA | OZNAKAROKA | NAZIV |
---|
Tabela ISPIT
INDEKS | IDPREDMETA | SKGODINA | OZNAKAROKA | OCENA | DATPOLAGANJA | BODOVI |
---|
Rešiti na relacionoj algebri:
define alias predmet1 for predmet
((predmet times predmet1)
where predmet.espb=predmet1.espb and
predmet.idpredmeta<predmet1.idpredmeta)
[predmet.oznaka, predmet.naziv, predmet1.oznaka, predmet1.naziv]
(((predmet where naziv='Analiza 1')[idpredmeta] join (ispit where ocena>5)) join ispitnirok)[naziv]
dosije[indeks] minus (ispit where oznakaroka='apr')[indeks]
ispit[skgodina, oznakaroka, idpredmeta, indeks]
divideby
dosije[indeks]
((predmet where espb>5)[idpredmeta]
union
(ispit where ocena > 5 and datpolaganja='20.01.2015')[idpredmeta]
((predmet where espb>5)[idpredmeta]
minus
(ispit where ocena > 5 and datpolaganja='20.01.2015')[idpredmeta]
define alias p1 for predmet
define alias p2 for predmet
define alias p3 for predmet
p3[naziv, espb]
minus
((p1 times p2) where p1.espb < p2.espb)[p1.naziv, p1.espb]
(ispit where ocena>5)[indeks, skgodina, idpredmeta]
divideby
predmet[idpredmeta]
Rešiti na relacionom računu
range of px is predmet
px.oznaka, px.naziv
range of px is predmet
px.idpredmeta, px.oznaka, px.naziv, px.espb
where px.espb=6
ili
px.*
where px.espb=6
range of px is predmet
range of ix is ispit
ix.indeks, ix.ocena, px.naziv
where ix.idpredmeta=px.idpredmeta
range of px is predmet
range of py is predmet
px.oznaka, px.naziv, py.oznaka, py.naziv
where px.idpredmeta>py.idpredmeta and px.espb=py.espb
range of px is predmet
range of ix is ispit
px.oznaka, px.naziv
where exists ix(ix.idpredmeta=px.idpredmeta and ix.indeks=20140026 and ix.ocena>5)
range of dx is dosije
range of px is predmet
range of ix is ispit
dx.indeks
where forall px(exists ix(ix.indeks=dx.indeks and ix.idpredmeta=px.idpredmeta))
ili
dx.indeks
where not exists px( not exists ix(ix.indeks=dx.indeks and ix.idpredmeta=px.idpredmeta))
range of dx is dosije
range of px is predmet
range of ix is ispit
px.naziv
where forall dx(exists ix(ix.indeks=dx.indeks and ix.idpredmeta=px.idpredmeta))
range of dx is dosije
range of ix is ispit
range of px is predmet
px.naziv
where espb=6 and exists ix(ix.idpredmeta=px.idpredmeta and
exists dx( dx.indeks=ix.indeks and dx.prezime='Vukovic'))
range of dx is dosije
range of ix is ispit
range of iy is ispit
range of px is predmet
dx.indeks
where forall px( if exists ix(ix.idpredmeta=px.idpredmeta and ix.indeks=20140025 and ix.ocena > 5)
then exists iy(iy.idpredmeta=px.idpredmeta and iy.indeks=dx.indeks and iy.ocena>5))
ili
range of dx is dosije
range of ix is ispit
range of iy is ispit
range of px is predmet
dx.indeks
where forall px( not exists ix(ix.idpredmeta=px.idpredmeta and ix.indeks=20140025 and ix.ocena > 5)
or exists iy(iy.idpredmeta=px.idpredmeta and iy.indeks=dx.indeks and iy.ocena>5))
ili
range of dx is dosije
range of ix is ispit
range of iy is ispit
range of px is predmet
dx.indeks
where not exists px( exists ix( ix.idpredmeta=px.idpredmeta and ix.indeks=20140025 and ix.ocena>5)
and not exists iy(iy.idpredmeta=px.idpredmeta and iy.indeks=dx.indeks and iy.ocena>5))
ili
range of ix is ispit
range of iy is ispit
range of iz is predmet
ix.indeks
where forall iy( if iy.indeks=20140025 and iy.ocena>5 then
exists iz( iz.indeks=ix.indeks and iz.idpredmeta=iy.idpredmeta and iz.ocena>5))
range of dx is dosije
range of ix, iy is ispit
range of px is predmet
dx.indeks, dx.ime, dx.prezime
where exists ix(ix.indeks=dx.indeks and ix.ocena>5 and
exists px(px.idpredmeta=ix.idpredmeta and px.naziv='Programiranje 1' and
not exists iy(iy.indeks=dx.indeks and iy.idpredmeta<>px.idpredmeta and iy.ocena>5)))
range of px is predmet
range of py is predmet
px.naziv, px.espb
where not exists py (py.espb>px.espb)
range of dx is dosije
range of ix is ispit
range of px is predmet
dx.indeks, dx.ime, dx.prezime, px.naziv
where px.espb=6 and exists ix(px.idpredmeta=ix.idpredmeta and dx.indeks=ix.indeks and ix.ocena>5)
range of ix is ispit
range of iy is ispit
range of px is predmet
ix.indeks, ix.skgodina
where forall px( exists iy (px.idpredmeta=iy.idpredmeta
and ix.indeks=iy.indeks and ix.ocena>5 and ix.skgodina=iy.skgodina))
Rešiti na relacionoj algebri i relacionom računu
Primer 14: Pronaći indekse studenata koji su predmet sa identifikatorom 1001 polagali tačno dva puta.
relaciona algebra
define alias i1 for ispit
define alias i2 for ispit
define alias i3 for ispit
((i1 times i2) where i1.indeks=i2.indeks and i1.idpredmeta=1001 and
i2.idpredmeta=1001 and i1.datpolaganja<>i2.datpolaganja)[i1.indeks]
minus
((i1 times i2 times i3) where i1.indeks=i2.indeks and i1.indeks=i3.indeks and
i1.idpredmeta=1001 and i2.idpredmeta=1001 and i3.idpredmeta=1001 and
i1.datpolaganja<>i2.datpolaganja and i1.datpolaganja<>i3.datpolaganja and
i2.datpolaganja<>i3.datpolaganja )[i1.indeks]
relacioni račun
range of dx is dosije
range of ix is ispit
range of iy is ispit
range of iz is ispit
dx.indeks
where exists ix(ix.indeks=dx.indeks and ix.idpredmeta=1001 and exists iy(
iy.indeks=dx.indeks and iy.idpredmeta=1001 and ix.datpolaganja<>iy.datpolaganja
and not exists iz(iz.indeks=dx.indeks and iz.idpredmeta=1001 and
ix.datpolaganja<>iz.datpolaganja and iy.datpolaganja<>iz.datpolaganja)))
Primer 2: Pronaći predmete koje su položili dva studenta upisana na fakultet istog dana. Izdvojiti datum upisa na fakultet i naziv predmeta.
relaciona algebra
define alias ispit1 for ispit
define alias dosije1 for dosije
(((ispit where ocena>5) join dosije) times ((ispit1 where ocena>5) join dosije1)
where ispit.idpredmeta=ispit1.idpredmeta and ispit.indeks
join predmet)[datupisa, naziv]
relacioni račun
range of px is predmet
range of dx is dosije
range of dy is dosije
range of ix is ispit
range of iy is ispit
dx.datupisa, px.naziv
where exists ix(ix.indeks=dx.indeks and ix.idpredmeta=px.idpredmeta and ix.ocena>5 and
exists iy(iy.idpredmeta=px.idpredmeta and iy.ocena>5 and exists dy(dy.indeks<>dx.indeks
and dx.datupisa=dy.datupisa)))
Primer 3: Pronaći indeks studenta koji
relaciona algebra
(ispit where ocena>5)[indeks, idpredmeta]
divideby
(predmet where espb=6)[idpredmeta]
relacioni račun
range of px is predmet
range of dx is dosije
range of ix is ispit
dx.indeks
where forall px( if px.espb=6 then exists ix(ix.indeks=dx.indeks and
ix.idpredmeta=px.idpredmeta and ix.ocena>5))
relaciona algebra
((ispit where ocena>5) join (predmet where espb=6)[idpredmeta])[indeks]
minus
((ispit where ocena>5)[indeks, idpredmeta]
divideby
(predmet where espb=6)[idpredmeta])
relacioni račun
range of px is predmet
range of py is predmet
range of dx is dosije
range of ix is ispit
range of iy is ispit
dx.indeks
where exists px(px.espb=6 and not exists ix(ix.indeks=dx.indeks and
ix.idpredmeta=px.idpredmeta and ix.ocena>5))
and exists py(py.espb=6 and exists iy(iy.indeks=dx.indeks and
iy.idpredmeta=py.idpredmeta and iy.ocena>5))
relaciona algebra
dosije[indeks]
minus
((ispit where ocena>5)[indeks, idpredmeta]
divideby
(predmet where espb=6)[idpredmeta])
relacioni račun
range of px is predmet
range of py is predmet
range of dx is dosije
range of ix is ispit
range of iy is ispit
dx.indeks
where exists px(px.espb=6 and not exists ix(ix.indeks=dx.indeks and
ix.idpredmeta=px.idpredmeta and ix.ocena>5))