Cvičení – Referenční integrita
Praktické cvičení – Referenční integrita
Zadání
Máme část školní databáze:
TRIDA
| id | nazev |
|---|---|
| 1 | 3.A |
| 2 | 3.B |
| 3 | 4.A |
ZAK
| id | jmeno | trida_id |
|---|---|---|
| 1 | Jan Novák | 1 |
| 2 | Petra Malá | 1 |
| 3 | Tomáš Veselý | 2 |
| 4 | Eva Králová | 8 |
PREDMET
| id | nazev |
|---|---|
| 1 | Databáze |
| 2 | Počítačové sítě |
| 3 | Programování |
ZAPIS
| zak_id | predmet_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 3 | 3 |
| 4 | 7 |
Databáze obsahuje chyby.
Vaším úkolem je zjistit, které odkazy nejsou správné a jak jim může databáze zabránit.
Úkol 1 – Najděte chyby
Najděte všechny záznamy, které porušují referenční integritu.
Do odpovědi napište:
Chyba 1:
Proč je to chyba:
Chyba 2:
Proč je to chyba:
Úkol 2 – Určete primární a cizí klíče
U následujících sloupců určete, zda se jedná o:
PK – PRIMARY KEY
FK – FOREIGN KEY
| Sloupec | PK / FK |
|---|---|
| TRIDA.id | |
| ZAK.id | |
| ZAK.trida_id | |
| PREDMET.id | |
| ZAPIS.zak_id | |
| ZAPIS.predmet_id |
Úkol 3 – Nakreslete vztahy v drawDB
V drawDB vytvořte nebo upravte model obsahující tabulky:
TRIDA
ZAK
PREDMET
ZAPIS
Vytvořte mezi nimi správné vazby.
Model musí obsahovat vztahy:
TRIDA → ZAK
ZAK → ZAPIS
PREDMET → ZAPIS
U každého vztahu správně určete:
primární klíč,
cizí klíč.
Úkol 4 – Opravte chybná data
Opravte původní data tak, aby již neporušovala referenční integritu.
Například pokud:
trida_id = 8
ale třída 8 neexistuje, musíte se rozhodnout:
přiřadit existující třídu,
vytvořit novou třídu,
nebo hodnotu nepovolit.
Stejně postupujte u chybného predmet_id.
Úkol 5 – Co dovolí databáze?
Rozhodněte, zda mají být následující operace POVOLENY, nebo ZAKÁZÁNY.
| Operace | Povoleno / Zakázáno |
|---|---|
| Přidat žáka s trida_id = 1 | |
| Přidat žáka s trida_id = 50 | |
| Přidat zápis zak_id = 1, predmet_id = 2 | |
| Přidat zápis zak_id = 100, predmet_id = 2 | |
| Přidat zápis zak_id = 1, predmet_id = 99 |
Ke každému zakázanému případu vysvětlete proč.
Úkol 6 – Mazání záznamů
Předmět:
Databáze
má několik zápisů žáků.
Co se má stát, pokud někdo předmět Databáze smaže?
Vyberte jednu možnost:
A – RESTRICT
Předmět nelze smazat, dokud existují jeho zápisy.
B – CASCADE
Smaže se předmět a současně všechny jeho zápisy.
C – SET NULL
Zápisy zůstanou, ale predmet_id bude NULL.
Napište, kterou možnost byste pro tuto databázi zvolili:
Moje volba:
Proč:
Bonus – SQL
Napište, co znamená tento příkaz:
FOREIGN KEY (trida_id) REFERENCES trida(id)
Odpověď:
Co odevzdat
Odevzdejte jeden pdf dokument, který bude obsahovat:
screenshot hotového modelu z drawDB,
označené PRIMARY KEY a FOREIGN KEY,
správně vytvořené vztahy mezi tabulkami,
nalezené chyby v původních datech,
vyplněnou tabulku z Úkolu 5,
odpověď, zda byste při mazání předmětu použili RESTRICT, CASCADE nebo SET NULL a proč.
Kontrola před odevzdáním
Zkontrolujte si:
existuje každé trida_id, na které žák odkazuje?
existuje každý zak_id v tabulce ZAPIS?
existuje každý predmet_id v tabulce ZAPIS?
jsou správně určeny PRIMARY KEY?
jsou správně určeny FOREIGN KEY?
neukazuje některý cizí klíč na neexistující záznam?
Hlavní pravidlo
FOREIGN KEY může odkazovat pouze na záznam, který skutečně existuje.
Jinými slovy:
Databáze nesmí ukazovat na něco, co neexistuje.