ŘIDITEL

Pohybujeme se v oblasti nejpřísnější logiky, kde při vyjadřování záleží na každém slově, ba na každém písmenku. Uvědomme si proto – jazykovým škarohlídům navzdory – že ŘIDITEL řídí (nebo alespoň by měl), kdežto ŘEDITEL ředí (např. nepřizpůsobivým krabicové víno). Srov. také Řídící učitel, Řiditelna - ta v kontrastu s Ředitelnou, což je část kabinetu chemikáře sloužící správnému naředění chemikálií před demonstračními pokusy ☺.

Úloha ŘIDITEL – zadání

Jste řiditelem prestižního vzdělávacího ústavu, který ve svých mnoha třídách (asi čtyřech) různých ročníků (asi třech) vyučuje nepřeberné davy studentů (dohromady asi deset). Pro jejich závratný počet můžete vytvářet i několik tříd stejného ročníku a stanovujete pro každou třídu maximální počet studentů a jejich třídního učitele. Protože ve vašem městě není tak velká budova, která by pojala všechny vaše třídy, jsou třídy umístěny na různých místech v různých městských čtvrtích.

Studenti nejen studují, ale také vykonávají zkoušky z mnoha náročných předmětů (asi třech) s různým výsledkem. Zkoušející jsou odborníci minimálně ve zkoušených předmětech, ale vůbec to nemusí být třídní učitelé.

Vytvořte databázi, která vám odpoví na závažné otázky, jako např.:

1. Kolik ještě v každém ročníku zbývá volných míst?

2. Kolik ještě v každé třídě zbývá volných míst?

3. Který třídní nezkouší?

4. Ve které městské čtvrti mají studenti nejlepší průměr z matematiky?

5. Ve které městské čtvrti je třída, jejíž studenti mají nejlepší průměr z matematiky?

6. Ve kterém ročníku je víc žen než mužů?

Úloha ŘIDITEL – analýza

Data ke sledování

Data z popisu situace:

Ročník; Třída; Student; MaxStudentů; Třídní; Místo; Předmět; Známka; Zkoušející

Další data z otázek:

Pohlaví

Nutno tedy sledovat alespoň:

Označme tuto tabulku T0.

Dekompozice dat

Datové typy

Zkusme cvičně vyplnit uvedenou datovou strukturu a přitom uvažujme o datovém typu každého pole. Je to jen na nás, popis situace a kladené otázky nás jen mírně vedou. Berme však ohled na to, že jde o úlohu určenou k procvičování, nikoliv k ostrému nasazení v reálném životě.

• Ročník: Zvolme to, co je obvyklé na školách: arabskými číslicemi, zde celé číslo od 1. Šlo by použít i římské číslice (tedy text), to by však mohlo být problémem při řazení.
• Třída: Opět se přidržme školských zvyklostí, tedy písmena A, B atd. Datový typ Text.
• MaxStudentů: Evidentně celé číslo.
• Třídní: Viz poznámka ke sledovaným údajům níže, např. Černý - tedy text.
• Místo: Pokud nepoužijeme pro daný účel trochu nepraktického označení místa např. pomocí PSČ nebo GPS, zbývá slovní označení lokality, např. Vršovice - tedy text.
• Student: Pro jeho označení určitě nepoužijeme např. rodné číslo. Každý má ale příjmení, tedy text. Jen na nás je, jestli se spokojíme pro účely procvičování s malým objemem dat (jen s příjmením, vyloučíme situaci se dvěma Nováky), nebo do tohoto pole zahrneme i jméno, nebo zvolíme dvě textová pole - příjmení a jméno.
• Pohlaví: Možností je několik, dokonce většiny datových typů. Pomineme častý jednoznakový text typu M a Ž. Protože jsou jen dvě pohlaví a existuje datový typ mající jen dvě různě hodnoty (často označovaný jako Logický nebo Ano/Ne), zvolme pro indikaci pohlaví právě tento typ. Pak však bude logičtější, když se toto datové pole nebude jmenovat Pohlaví, ale buď Žena nebo Muž. Dáme přednost dámám a přidělíme tomuto datovému poli jméno ŽENA - a hodnota bude buď Ano nebo Ne.
• Předmět: Pro účely procvičování zvolme vypovídající hodnoty typu Mat, Fyz apod. Půjde tedy o datový typ text.
• Zkoušející: Stejně jako u třídního - viz poznámka ke sledovaným údajům níže, např. Novák - tedy text.
• Známka: Ze zadání (má se pracovat s průměrnou známkou) vyplývá, že půjde o číslo. Jaký způsob známkování zvolíme, je na našem gustu: středoškolské celé číslo od 1 do 5, vysokoškolské od 1 do 4, bodové hodnocení třeba i necelým číslem od 10.0 do 0.0 apod.

Nyní tedy vyplnění nějakými cvičnými daty:

Úvaha 1

Elementem umístění studentů je nějaká třída jistého ročníku (určitě nebude student X studovat ve 2A a 2C současně). Kdyby nějaký student studoval souběžně v 1. ročníku a 3. ročníku (např. jiné specializace), administrativně půjde o jiného studenta – už z důvodu pobírání státní dotace MŠ podle počtu studentů. Z toho důvodu vyplývá struktura dat jedné třídy jednoho ročníku např. takto:

Tabulka TŘÍDY

Primárním klíčem je evidentně spojený klíč [Ročník;Třída].

Tabulka T0 se tedy redukuje na tabulku T1:

Úvaha 2

Každý student má své jméno (které podle národních zvyklostí může být značně složité), a v daném okamžiku dává k disposici další údaje o své osobě – např. jde-li o ženu nebo muže (event. další, zde nesledované). Dále v daném okamžiku studuje v konkrétním ročníku konkrétní třídě. Logicky by tedy studenti měli mít svou tabulku, pro účely vazby na další tabulky opatřené nějakým jednoduchým jedinečným kódem (např. oblíbeným osobním číslem):

Tabulka STUDENTI

Tabulka T1 se dále redukuje na tabulku T2 při současné náhradě označení studenta (místo plným jménem) jeho osobním číslem:

Úvaha 3

Každý řádek tabulky T2 je však nutnou a postačující informací o vykonání jedné zkoušky jednoho studenta z jednoho předmětu. Jde tedy o seznam (=tabulku) vykonaných zkoušek. Označme proto tabulku T2 např. Zkoušky:

Tabulka ZKOUŠKY

Vazby mezi tabulkami

Relace v úloze ŘIDITEL

Poznámky ke sledovaným údajům

Nikde (ani v popisu situace ani v kladených otázkách) není nic, co by nutilo sledovat u pedagogů něco jiného než jeho jednoznačnou identifikaci. Jestli např. angličtináře pošle řiditel zkoušet matiku, je to čistě věc mezi nimi dvěma. Protože celá tato úloha má za cíl hlavně procvičit práci v prostředí databázových programů a zejména sestavování příkazu Select kategorie SQL, netřeba vymýšlet další tabulku. Pro identifikaci kantorek a kantorů zde postačí např. jednoduché jedinečné příjmení (opět jako u studentů si sami pro sebe vyloučíme dva Nováky). Ovšem individuálnímu mentálnímu rozletu se samozřejmě meze nekladou☺. Analogicky např. městské čtvrti, předměty ...

Často mají studenti tendenci jednoznačně identifikovat třídu v ročníku jediným údajem (= datovým polem). To samozřejmě lze, ale je to samoúčelné a mnohdy matoucí. Zde by byl nejhorší možný způsob volit tzv. Automatické číslo. Na první pohled by nad hodnotou např. 2 mnozí kroutili hlavou, proč je třída 2 třídou 4. ročníku. Proto se zde procvičuje spojený indexový klíč. V kontextu relací nemusí sice všechny databázové systémy podporovat všechny jejich funkce (např. aktualizaci v kaskádě), pro tvorbu a vykonávání dotazů však žádné omezení není.

Úloha ŘIDITEL - Dotazy

Úloha 1

select
   S.ROCNIK,
  (
    select sum(T.MAXSTUD)
    from TRIDY T
    where S.ROCNIK=T.ROCNIK
  ) - Count(S.ROCNIK) as VOLNYCH_MIST
from STUDENTI S
group by S.ROCNIK

Úloha 2

select 
  T.ROCNIK,
  T.TRIDA,
  T.MAXSTUD - 
  (select count(S.ROCNIK)
   from STUDENTI S
   where
     T.ROCNIK=S.ROCNIK and T.TRIDA=S.TRIDA
  ) AS VOLNYCH_MIST
from TRIDY T
group by T.ROCNIK, T.TRIDA, T.MAXSTUD

Úloha 3

select TRIDNI as NEZKOUSI
from TRIDY
where TRIDNI not in (select ZKOUSEJICI from ZKOUSKY)

Úloha 4

S pomocným dotazem:

Pomocný dotaz Uloha4a_PrumeryMat:

select
  T.MISTO, Avg(Z.ZNAMKA) AS PRUM_MAT
from 
  (ZKOUSKY Z left join STUDENTI S on Z.OSCIS=S.OSCIS)
     left join TRIDY T on (S.ROCNIK = T.ROCNIK) and (S.TRIDA = T.TRIDA)
where Z.PREDMET="Mat"
group by T.MISTO

Finální Uloha4:

select MISTO
FROM Uloha4a_PrumeryMat
where PRUM_MAT=(select Min(PRUM_MAT) from Uloha4a_PrumeryMat)

Jediným dotazem:

Poznámka: Tohle vypadá hrozivě, ale vytvoření je velmi jednoduché. Vyjde se z předchozího řešení s pomocným dotazem Uloha4a_PrumeryMat, jehož text se zkopíruje do schránky. Výsledný dotaz vznikne z předchozího třířádkového finálního řešení nahrazením dvou výskytů totožných zdrojů (vyznačeno tučně) obsahem schránky. To koneckonců přesně kopíruje logiku uvažování při analýze úlohy 4.

select MISTO

from

(
  select
    T.MISTO, Avg(Z.ZNAMKA) AS PRUM_MAT
  from 
    (ZKOUSKY Z left join STUDENTI S on Z.OSCIS=S.OSCIS)
       left join TRIDY T on (S.TRIDA = T.TRIDA) and (S.ROCNIK = T.ROCNIK)
  where Z.PREDMET="Mat"
  group by T.MISTO
)

where PRUM_MAT=
(
  select Min(PRUM_MAT) from 
    (
      select
        T.MISTO, Avg(Z.ZNAMKA) AS PRUM_MAT
      from 
        (ZKOUSKY Z left join STUDENTI S on Z.OSCIS=S.OSCIS)
           left join TRIDY T on (S.TRIDA = T.TRIDA) and (S.ROCNIK = T.ROCNIK)
      where Z.PREDMET="Mat"
      group by T.MISTO
    )
)

Úloha 5

S pomocným dotazem:

Pomocný dotaz Uloha5a_PrumeryMat:

select 
  T.ROCNIK, T.TRIDA, Avg(Z.ZNAMKA) AS PRUM_MAT
from
  (ZKOUSKY Z left join STUDENTI S on Z.OSCIS=S.OSCIS)
   left join TRIDY T on S.ROCNIK = T.ROCNIK and S.TRIDA = T.TRIDA
where Z.PREDMET = "Mat"
group by T.ROCNIK, T.TRIDA

Finální Uloha5:

select T.MISTO 
from 
  Uloha5a_PrumeryMat P left join TRIDY T
    on P.ROCNIK=T.ROCNIK and P.TRIDA=T.TRIDA
where
  P.PRUM_MAT =
    (select Min(PRUM_MAT) from Uloha5a_PrumeryMat)

Jediným dotazem:

Poznámka: Stejně jako u předchozí úlohy je vytvoření celkem jednoduché, protože mechanické. Vyjde se z předchozího řešení s pomocným dotazem Uloha5a_PrumeryMat, jehož text se zkopíruje do schránky. Výsledný dotaz vznikne z předchozího sedmiřádkového finálního řešení nahrazením dvou tučně označených výskytů totožných zdrojů obsahem schránky. To stejně jako u předchozí úlohy přesně kopíruje logiku uvažování při analýze úlohy 5.

select T.MISTO 
from
 (
   select 
     T.ROCNIK, T.TRIDA, Avg(Z.ZNAMKA) AS PRUM_MAT
   from
     (ZKOUSKY Z left join STUDENTI S on Z.OSCIS=S.OSCIS)
       left join TRIDY T on S.ROCNIK = T.ROCNIK and S.TRIDA = T.TRIDA
   where Z.PREDMET = "Mat"
   group by T.ROCNIK, T.TRIDA
 ) P
  left join TRIDY T on P.ROCNIK=T.ROCNIK and P.TRIDA=T.TRIDA
where
  P.PRUM_MAT =
    (select Min(PRUM_MAT)
     from 
       (
        select 
          T.ROCNIK, T.TRIDA, Avg(Z.ZNAMKA) AS PRUM_MAT
        from
          (ZKOUSKY Z left join STUDENTI S on Z.OSCIS=S.OSCIS)
            left join TRIDY T on S.ROCNIK = T.ROCNIK and S.TRIDA = T.TRIDA
        where Z.PREDMET = "Mat"
        group by T.ROCNIK, T.TRIDA
       )
    )

Úloha 6

select
 ROCNIK,
 (select count(ROCNIK) as POC_ZEN from STUDENTI SZ where ZENA and S.ROCNIK=SZ.ROCNIK) AS ZEN,
 (select count(ROCNIK) as POC_MUZU from STUDENTI SM where not ZENA and S.ROCNIK=SM.ROCNIK) AS MUZU
from STUDENTI S
group by ROCNIK
having
 (select count(ROCNIK) as POC_ZEN from STUDENTI SZ where ZENA and S.ROCNIK=SZ.ROCNIK) >
 (select count(ROCNIK) as POC_MUZU from STUDENTI SM where not ZENA and S.ROCNIK=SM.ROCNIK)

Poznámka: Uvedený text příkazu Select by měl pracovat ve všech databázových systémech. Některé méně zdařilé systémy nepovolují naprosto logický tvar klauzule Having:

... having ZEN > MUZU

Rev.: 11/2021