CZU_3_DBS - Relační algebra

up:: ⌂

• 🔗
  • 🎥P(4) - fit jde i trochu poslouchat
  • editor - learn by trying
  • gpt-sheet
    • todo: quiz
  • tip
    • postupné ladění

Cheatsheet

Název	RA	SQL
Projekce	r[a,b]	SELECT a,b FROM r
Selekce	r(podminka)	SELECT * FROM r WHERE podminka
Přirozené spojení	r*s	r NATURAL JOIN s
Obecné spojení	r[id=s.r_id]>s	r JOIN s ON r.id = s.r_id
Antijoin	r!*>s	RIGHT ANTI JOIN / NOT EXISTS
Sjednocení	r∪s	UNION
Průnik	r∩s	INTERSECT
Rozdíl	r\s	EXCEPT
Kartézský součin	r×s	CROSS JOIN
Dělení	r÷s	— (složitější SQL)
Přejmenování	r[a->b]	AS

Syntax

* = přirozené spojení
<* = levé přirozené spojení
*> = pravé přirozené spojení
!<* = levý antijoin
!*> = pravý antijoin
[] = obecné spojení
<] = levé obecné spojení
[> = pravé obecné spojení
!<] = levý obecný antijoin 
![> = pravý obecný antijoin 
∪ = množinové sjednocení
∩ = množinový průnik
\ = množinový rozdíl
÷ = množinové dělení
× = kartézský součin
∨ = logická disjunkce
∧ = logická konjunkce 
¬ = logická negace
+ = plus
- = mínus
∕ = děleno
∗ = krát
⊆ = podmnožina
⊂ = vlastní podmnožina
→ - funkční závislost
-> přejmenování 
⁺ = krát
'18.3.2012' = datum

Examples

• Primat zkouška

🐒 Zoo

🔗

1 - Zvíře, které nikdo nikdy nekrmil

krmeni!*>zvire

SELECT DISTINCT *
FROM zvire z
WHERE NOT EXISTS(SELECT * FROM krmeni k WHERE k.id_zvire = z.id_zvire);

2 - Zvíře, které nikdo nikdy nekrmil

druh(nazev='slon')[zvire.id_druh=druh.id_druh>zvire(jmeno='Jumbo')[zvire.id_osobnost=osobnost.id_osobnost>osobnost

SELECT o.*
FROM zvire z
     JOIN druh d ON z.id_druh = d.id_druh
     JOIN osobnost o USING (id_osobnost)
WHERE d.nazev = 'slon'
  AND z.jmeno = 'Jumbo';

3 - Zviře, u kterého ve všech jeho vyšetření asistoval pouze doktor s osobním číslem 215 a nikdo jiný.

{zamestnanec(osobni_cislo=215)*>doktor[id_zamestnanec=id_asistoval>vysetreni*>zvire}
\
{zamestnanec(osobni_cislo!=215)*>doktor[id_zamestnanec=id_asistoval>vysetreni*>zvire}

SELECT zv.*
FROM zamestnanec za
     JOIN doktor d ON za.id_zamestnanec = d.id_zamestnanec
     JOIN vysetreni v ON d.id_zamestnanec = v.id_asistoval
     JOIN zvire zv ON v.id_zvire = zv.id_zvire
WHERE za.osobni_cislo = 215
EXCEPT
SELECT zv.*
FROM zamestnanec za
     JOIN doktor d ON za.id_zamestnanec = d.id_zamestnanec
     JOIN vysetreni v ON d.id_zamestnanec = v.id_asistoval
     JOIN zvire zv ON v.id_zvire = zv.id_zvire
WHERE za.osobni_cislo != 215;

4 - Zvířata, která krmil každý ošetřovatel

{krmeni[id_zvire, id_zamestnanec]÷osetrovatel[id_zamestnanec]}*zvire

-- dvojí negace
SELECT *
FROM zvire z
WHERE NOT EXISTS(
        SELECT *
        FROM osetrovatel o
        WHERE NOT EXISTS(
                SELECT * FROM krmeni k WHERE k.id_zvire = z.id_zvire AND k.id_zamestnanec = o.id_zamestnanec
            )
    );

-- počet - pozor na distinct!
SELECT *
FROM zvire z
WHERE (SELECT COUNT(DISTINCT id_zamestnanec) FROM krmeni k WHERE k.id_zvire = z.id_zvire)
          =
      (SELECT COUNT(id_zamestnanec)
       FROM osetrovatel)
;

--posloupnost operací s využitím cross join (univerzální kvantifikátor)
WITH zvirata AS (SELECT id_zvire FROM zvire),
     osetrovatele AS (SELECT id_zamestnanec FROM osetrovatel),
     mozne_krmeni AS (SELECT *
                      FROM zvirata
                           CROSS JOIN osetrovatele),
     nastale_krmeni AS (SELECT id_zvire, id_zamestnanec FROM krmeni),
     nenastale_krmeni AS (SELECT * FROM mozne_krmeni EXCEPT SELECT * FROM nastale_krmeni),
     zvire_ktere_nekrmil_kazdy AS (SELECT id_zvire FROM nenastale_krmeni),
     zvire_krmeno_kazdym AS (SELECT * FROM zvirata EXCEPT SELECT * FROM zvire_ktere_nekrmil_kazdy)
SELECT *
FROM zvire_krmeno_kazdym
     NATURAL JOIN zvire;

7 - Zvíře které nikdo nikdy nevyšetřoval na ospalost.

vysetreni(ucel='ospalost')!*>zvire

SELECT *
FROM zvire
EXCEPT
SELECT zvire.*
FROM zvire
     JOIN vysetreni USING (id_zvire)
WHERE ucel = 'ospalost';

8 - Nalezněte takového zaměstnance, který vyšetřil buďto hrocha nebo slona, ale nevyšetřoval oba tyto druhy.

{{druh(nazev='hroch')[zvire.id_druh=druh.id_druh>zvire*>vysetreni[id_vysetril=id_zamestnanec>doktor*>zamestnanec}
∪
{druh(nazev='slon')[zvire.id_druh=druh.id_druh>zvire*>vysetreni[id_vysetril=id_zamestnanec>doktor*>zamestnanec}}
\
{{druh(nazev='hroch')[zvire.id_druh=druh.id_druh>zvire*>vysetreni[id_vysetril=id_zamestnanec>doktor*>zamestnanec}
∩
{druh(nazev='slon')[zvire.id_druh=druh.id_druh>zvire*>vysetreni[id_vysetril=id_zamestnanec>doktor*>zamestnanec}}

--ukážeme si jak jsou využít v jednom dotazu hlavní množinové operace
(SELECT z.*
 FROM zamestnanec z
      JOIN doktor d ON z.id_zamestnanec = d.id_zamestnanec
      JOIN vysetreni v ON d.id_zamestnanec = v.id_vysetril
      JOIN zvire zv ON v.id_zvire = zv.id_zvire
      JOIN druh dr ON zv.id_druh = dr.id_druh
 WHERE dr.nazev = 'hroch'
 UNION
 SELECT DISTINCT z.*
 FROM zamestnanec z
      JOIN doktor d ON z.id_zamestnanec = d.id_zamestnanec
      JOIN vysetreni v ON d.id_zamestnanec = v.id_vysetril
      JOIN zvire zv ON v.id_zvire = zv.id_zvire
      JOIN druh dr ON zv.id_druh = dr.id_druh
 WHERE dr.nazev = 'slon')
EXCEPT
(SELECT DISTINCT z.*
 FROM zamestnanec z
      JOIN doktor d ON z.id_zamestnanec = d.id_zamestnanec
      JOIN vysetreni v ON d.id_zamestnanec = v.id_vysetril
      JOIN zvire zv ON v.id_zvire = zv.id_zvire
      JOIN druh dr ON zv.id_druh = dr.id_druh
 WHERE dr.nazev = 'hroch'
 INTERSECT
 SELECT DISTINCT z.*
 FROM zamestnanec z
      JOIN doktor d ON z.id_zamestnanec = d.id_zamestnanec
      JOIN vysetreni v ON d.id_zamestnanec = v.id_vysetril
      JOIN zvire zv ON v.id_zvire = zv.id_zvire
      JOIN druh dr ON zv.id_druh = dr.id_druh
 WHERE dr.nazev = 'slon');

23 - Vyšetření, která se udála před 18.3.2012 a mají nějaká opatření. Vypište všechny atributy vyšetření i opatření.

vysetreni(datum<'18.3.2012')*opatreni

SELECT *
FROM vysetreni
     JOIN opatreni USING (id_vysetreni)
WHERE datum < TO_DATE('18.3.2012', 'dd.mm.yyyy');

25 - Všechny popisy opatření, která vydal doktor se specializací ‘Tržné rány’

{doktor(specializace='Tržné rány')*>vysetreni*>opatreni}[popis]

SELECT DISTINCT popis
FROM opatreni
     NATURAL JOIN vysetreni
     NATURAL JOIN doktor
WHERE specializace = 'Tržné rány';

26 - Nalezni dvojice zaměstnanců, kteří bydlí na stejné adrese

{zamestnanec[id_adresa,jmeno->jmeno1,prijmeni->prijmeni1,id_zamestnanec->id_zamestnanec1]*zamestnanec[id_adresa,jmeno->jmeno2,prijmeni->prijmeni2,id_zamestnanec->id_zamestnanec2]}
(id_zamestnanec1<id_zamestnanec2)[id_adresa,jmeno1,prijmeni1,jmeno2,prijmeni2]

SELECT id_adresa, z1.jmeno AS jmeno1, z1.prijmeni AS prijmeni1, z2.jmeno AS jmeno2, z2.prijmeni AS prijmeni2
FROM zamestnanec z1
     JOIN zamestnanec z2 USING (id_adresa)
WHERE z1.id_zamestnanec < z2.id_zamestnanec
;