Seminární práce z předmětu: Databázové systémy PDF

UNIVERZITA PARDUBICE\ Fakulta elektrotechniky a informatiky Název projektu: **Seminární práce z předmětu: Databázové systémy** Program: B0613P140007 - Webové technologie Jméno a příjmení: Lucie Vítová Obsah {#obsah.ListParagraph.TOCHeading} ===== 1 Analýza 3 1.1 Scénář 3 1.1.1 Strukturální...

UNIVERZITA PARDUBICE\ Fakulta elektrotechniky a informatiky Název projektu: **Seminární práce z předmětu: Databázové systémy** Program: B0613P140007 - Webové technologie Jméno a příjmení: Lucie Vítová Obsah {#obsah.ListParagraph.TOCHeading} ===== 1 Analýza 3 1.1 Scénář 3 1.1.1 Strukturální a procedurální pravidla 5 1.1.2 Jednoduchá analýza 6 2 Návrh relační databáze 7 2.1 Konceptuální schéma -- ERD 7 2.1.1 Diskuze smyček 12 2.1.2 ERDish věty 13 Normalizace a 3. NF 15 2.2 Relační model dat 16 3 Implementace databáze 20 3.1 Fyzický model dat 20 Naplnění fyzického modelu dat -- postup 20 Číselníky 20 Sekvence a jejich použití 20 UNIQUE, indexy a další integritní omezení 21 Implementace supertypu/subtypu 22 Slabé entity 22 PFK u vazebních tabulek 22 Závěr k fyzickému modelu 23 3.1.1 Integritní omezení (IO) 23 Tabulky bez cizích klíčů jako první 25 Tabulky s cizími klíči až poté 25 Dodržení UNIQUE omezení 26 Postup pro závislé tabulky 26 Výhody tohoto postupu 26 Souhrn postupu 27 4 Výčet SQL dotazů 28 Závěr 39 Přílohy 40 [Analýza] ===================== Pracuji pro fiktivní společnost **StudyMates s.r.o.**, která se zaměřuje na vývoj softwaru pro studenty a školy. Naším hlavním produktem je aplikace „StudyMates", která studentům slouží k nahrávání, organizaci a rychlému opakování jejich studijních materiálů. Firma vznikla z mého nadšení pro vzdělávání a dnes mám okolo 500 aktivních uživatelů, převážně z řad středoškoláků a vysokoškoláků. Cílem je databázové řešení, které zvládne: - Uchovávat informace o **uživatelích** (identita, jméno, email, obor studia, apod.). - Umožnit jim nahrávat **materiály** (typicky PDF, Word, obrázky), které lze zařazovat do různých **kategorií** a k nimž mohou být automaticky rozpoznány **klíčové informace**. - Z **klíčových informací** generovat **kvízy** s **otázkami** a **odpověďmi** pro opakování učiva, a ukládat jejich **výsledky**. - Poskytovat možnost psaní **poznámek** k materiálům a dovolovat upravit si **systémové nastavení** (jazyk, téma) na míru. #### **Využití databáze** {#využití-databáze.ListParagraph} Databáze bude sloužit jako hlavní úložiště všech výše zmíněných entit: uživatelé, materiály, kvízy, kategorie atd. Aplikace poběží v cloudu a bude k ní přistupovat webová i mobilní verze StudyMates. Údaje z databáze se využijí pro: - **Rychlé vyhledání** dokumentů a poznámek. - **Generování kvízů** z klíčových informací. - **Analytiku** (pro studenta: přehled výsledků, pro firmu: využití aplikace). [Scénář] -------------------- Společnost **StudyMates s.r.o.** je inovativní firma zaměřená na vývoj softwarových řešení pro studenty. Na počátku jsme fungovaly jako malý startup s cílem usnadnit studentům archivaci a třídění studijních materiálů. Postupně jsem do aplikace přidávala další funkce, až se z ní stal komplexní nástroj pro podporu učení. V současné době máme okolo 500 aktivních uživatelů (středoškoláků i vysokoškoláků), ale mým cílem je tento počet během roku alespoň zdvojnásobit. Společnost provozuje webovou a mobilní aplikaci **StudyMates**, v níž se každý **uživatel** (*id\_uzivatele, jmeno, prijmeni, email, datum\_registrace, obor\_studia*) registruje vyplněním e-mailu, jména a příjmení. Následně mu systém založí osobní profil, kam si může nahrávat libovolné **materiály** (*id\_materialu, nazev, typ, datum\_nahrani, obsah, velikost*). Ty jsou přiřazovány buď automaticky (pomocí analýzy obsahu), nebo manuálně do jedné či více **kategorií** (*id\_kategorie, nazev*), jako třeba „Biologie" nebo „Matematika". Po nahrání materiálu aplikace provádí analýzu textu, z níž se vygenerují **klíčové informace** (*id\_informace, text\_informace, pozice\_v\_dokumentu*). Ty se vážou ke konkrétnímu materiálu a uživateli zvýrazňují důležité pojmy. Z těchto informací je možné vytvořit **kvíz** (*id\_kvizu, nazev\_kvizu, datum\_vytvoreni*), který obsahuje sadu **otázek** (*id\_otazky, text\_otazky, typ\_otazky*). Každá otázka pak má několik **odpovědí** (*id\_odpovedi, text\_odpovedi, je\_spravna*). Jakmile student kvíz vyplní, uloží se **výsledek kvízu** (*id\_vysledku, datum\_vypracovani, skore*), který je propojený jak s daným kvízem, tak s uživatelem. Student si tak může později prohlédnout, jak v daném kvízu dopadl. Kromě toho si studenti mohou k jednotlivým materiálům psát **poznámky** (*id\_poznamky, text\_poznamky*) a do **systémového nastavení** (*id\_nastaveni, jazyk, tema, format\_datumu*) si uloží například výběr jazyka aplikace a barevné schéma (tmavý či světlý režim). Základní proces vypadá takto: student nahraje PDF nebo textový soubor, aplikace jej zanalyzuje a vytáhne z něj důležité pojmy. Z těchto pojmů automaticky sestaví otázky, které uloží do kvízu. Student si následně materiál a kvíz uloží, případně si dokumenty rozdělí do kategorií či k nim připojí vlastní poznámky. **Hlavní interakce tak lze shrnout do několika kroků**: 1. **Uživatel se registruje** → vloží se záznam do tabulky **UŽIVATEL**. 2. **Nahraje materiál** → tabulka **MATERIÁL**, přiřazení do **KATEGORIE** a extrakce **KLÍČOVÝCH INFORMACÍ**. 3. **Vytvoří z materiálu KVÍZ** → do něj se uloží **OTÁZKY** → a k těm **ODPOVĚDI**. 4. **Po vyplnění kvízu se uloží VÝSLEDEK KVÍZU** (spojen s uživatelem i kvízem). 5. **Student si píše POZNÁMKY** k materiálům a konfiguruje **SYSTÉMOVÉ NASTAVENÍ** podle svých preferencí. ### [Strukturální a procedurální pravidla] #### **Strukturální pravidla** {#strukturální-pravidla.ListParagraph} - **SP1**: Jeden *uživatel* může mít více *materiálů*, ale každý materiál patří právě jednomu uživateli (1:N). - **SP2**: Kategorie může obsahovat více materiálů, ale pro potřeby projektu mám vztah 1:N (každý materiál se zařazuje do jedné hlavní kategorie). - **SP3**: Ke každému *materiálu* může být více *klíčových informací*. - **SP4**: *Kvíz* se váže buď k uživateli (jeho autorovi) nebo může být obecný. *Otázka* patří jednomu kvízu, *odpověď* patří jedné otázce. - **SP5**: Výsledek kvízu je svázán s *uživatelem* a *kvízem* (N:M by se řešilo tabulkou, ale tady stačí 1:1 pro unikátní složený klíč). - **SP6**: Poznámka se vztahuje 1:N k materiálu (jeden materiál může mít víc poznámek). - **SP7**: Systémové nastavení patří právě jednomu uživateli (1:1). #### **Procedurální pravidla** {#procedurální-pravidla.ListParagraph} - **PP1**: Při registraci se kontroluje, zda *email* už neexistuje (UNIQUE omezení). Datum registrace se ukládá automaticky (např. SYSDATE). - **PP2**: Po nahrání materiálu (PDF, text apod.) se spustí analyzátor, který vyhledá klíčová slova a vytvoří záznamy v tabulce KLÍČOVÉ INFORMACE. - **PP3**: Pokud se uživatel rozhodne vygenerovat kvíz, aplikace projde klíčové informace daného materiálu, sestaví otázky a odpovědi a uloží je do příslušných tabulek. - **PP4**: Při mazání uživatele se rozhoduje, zda se jeho materiály a kvízy smažou (ON DELETE CASCADE) nebo je nelze odstranit, pokud jsou výsledky kvízů. - **PP5**: Když si student zapíše *poznámku*, uloží se text přímo k ID materiálu. Poznámku lze editovat, dokud student materiál nesmaže. - **PP6**: V systémovém nastavení lze měnit jazyk a téma. Při každém přihlášení se načte toto nastavení a podle něj se aplikace zobrazí. ### [Jednoduchá analýza] ### {#section.ListParagraph} **USER - id\_user, firstName, lastName, email (Unique), registrationDate, studyField** **DOCUMENT** - *id\_document, title, documentType, uploadDate, content, fileSize, users\_id\_user* **CATEGORIE** - id\_category, name, documents\_id\_document **KEY\_INFORMATION** - *id\_info, textInfo, position, documents\_id\_document* **QUIZ** - *id\_quiz, name, creationDate* **QUESTION** - *id\_question, text, typeQuestion, quizes\_id\_quiz* **ANSWER** - *id\_answer, text, isCorrect, questions\_id\_question* **QUIZ\_RESULT** - *id\_result, completionDate, score, users\_id\_user, quizes\_id\_quiz* **NOTE** - *id\_note, text, documents\_id\_document* **SYSTEM\_SETTING** - *id\_setting, theme, language, dateFormat, users\_id\_user* [Návrh relační databáze] ==================================== Konceptuální schéma -- ERD -------------------------- #### **USER - DOCUMENT** {#user---document.ListParagraph} - **Relace:** upload - **Kardinálnost:** 1:N - Jeden uživatel může nahrát více dokumentů. - Každý dokument musí být přiřazen jednomu uživateli. - **Optionalita:** - **USER:** 0:N (uživatel nemusí nahrát žádný dokument). - **DOCUMENT:** 1 (každý dokument musí být nahrán uživatelem). - **Odůvodnění:** Dokumenty jsou nahrávány uživateli, což zajišťuje sledování vlastnictví a odpovědnosti za obsah. #### **USER - QUIZ** {#user---quiz.ListParagraph} - **Relace:** creates - **Kardinálnost:** 1:N - Jeden uživatel může vytvořit více kvízů. - Každý kvíz je vytvořen jedním uživatelem. - **Optionalita:** - **USER:** 0:N (uživatel nemusí vytvořit žádný kvíz). - **QUIZ:** 1 (každý kvíz má autora). - **Odůvodnění:** Uživatelská aktivita zahrnuje tvorbu kvízů, přičemž každý kvíz musí mít záznam o svém autorovi. #### **USER - QUIZ RESULT** {#user---quiz-result.ListParagraph} - **Relace:** achieves - **Kardinálnost:** 1:N - Jeden uživatel může mít více výsledků kvízů. - Každý výsledek patří jednomu uživateli. - **Optionalita:** - **USER:** 0:N (uživatel nemusí mít žádné výsledky). - **QUIZ RESULT:** 1 (každý výsledek je spojen s uživatelem). - **Odůvodnění:** Výsledky kvízů jsou zaznamenávány pro každého uživatele a slouží k hodnocení jeho úspěšnosti. #### **USER - SYSTEM SETTING** {#user---system-setting.ListParagraph} - **Relace:** configure - **Kardinálnost:** 1:1 - Každý uživatel má své jedinečné systémové nastavení. - **Optionalita:** - **USER:** 1 (každý uživatel má nastavení). - **SYSTEM SETTINGS:** 1 (nastavení je spojeno s uživatelem). - **Odůvodnění:** Každý uživatel může přizpůsobit své systémové nastavení (např. jazyk, téma), přičemž nastavení je unikátní pro konkrétního uživatele. #### **DOCUMENT - CATEGORY** {#document---category.ListParagraph} - **Relace:** is categorized under - **Kardinálnost:** 1:N - Jeden dokument může být zařazen do více kategorií. - Jedna e spojena s jedním dokumentem. - **Optionalita:** - **DOCUMENT:** 0:N (dokument nemusí být zařazen do žádné kategorie). - **CATEGORY:** 0:N (kategorie může být prázdná, tj. bez přiřazených dokumentů). - **Odůvodnění:** Dokumenty lze zařazovat do různých kategorií podle obsahu (např. předměty, témata). Tento flexibilní vztah umožňuje efektivní organizaci dokumentů, aniž by byla narušena logika přiřazování kategorií. Každý dokument může být zařazen do několika kategorií, což umožňuje pokrytí různých studijních potřeb uživatelů. #### **DOCUMENT - KEY INFORMATION** {#document---key-information.ListParagraph} - **Relace:** contains - **Kardinálnost:** 1:N - Jeden dokument obsahuje více klíčových informací. - Klíčová informace je spojena s jedním dokumentem. - **Optionalita:** - **DOCUMENT:** 0:N (dokument nemusí obsahovat klíčové informace). - **KEY INFORMATION:** 1 (každá informace je spojena s dokumentem). - **Odůvodnění:** Dokumenty obsahují klíčové informace, které mohou být extrahovány pro rychlou orientaci uživatele. #### **DOCUMENT - NOTE** {#document---note.ListParagraph} - **Relace:** is added to - **Kardinálnost:** 1:N - Jeden dokument může obsahovat více poznámek. - Poznámka je spojena s jedním dokumentem. - **Optionalita:** - **DOCUMENT:** 0:N (dokument nemusí mít žádné poznámky). - **NOTE:** 1 (každá poznámka je přidružena k dokumentu). - **Odůvodnění:** Poznámky umožňují uživatelům přidávat dodatečné informace nebo komentáře k dokumentům. #### **QUIZ - QUESTION** {#quiz---question.ListParagraph} - **Relace:** contains - **Kardinálnost:** 1:N - Jeden kvíz obsahuje více otázek. - Otázka je přiřazena jednomu kvízu. - **Optionalita:** - **QUIZ:** 1:N (kvíz musí obsahovat alespoň jednu otázku). - **QUESTION:** 1 (každá otázka je přiřazena ke kvízu). - **Odůvodnění:** Kvízy jsou složeny z otázek, přičemž každá otázka musí být součástí konkrétního kvízu. #### {#section-1.ListParagraph} #### **QUESTION - QUESTION TYPE** {#question---question-type.ListParagraph} - **Relace:** has - **Kardinálnost:** 1:1 - Každá otázka má právě jeden typ. - **Optionalita:** - **QUESTION:** 1 (každá otázka musí mít typ). - **QUESTION TYPE:** 0:N (typ může existovat bez přiřazené otázky). - **Odůvodnění:** Typ otázky (např. multiple-choice, true/false) definuje její charakteristiku a způsob odpovědi. #### **QUESTION - ANSWER** {#question---answer.ListParagraph} - **Relace:** has - **Kardinálnost:** 1:N - Jedna otázka může mít více odpovědí. - Odpověď je přiřazena k jedné otázce. - **Optionalita:** - **QUESTION:** 1:N (otázka musí mít alespoň jednu odpověď). - **ANSWER:** 1 (každá odpověď musí být spojena s otázkou). - **Odůvodnění:** Odpovědi jsou klíčovou součástí otázek, protože určují možné volby nebo správné řešení. #### **QUIZ - QUIZ RESULT** {#quiz---quiz-result.ListParagraph} - **Relace:** evaluates - **Kardinálnost:** 1:N - Jeden kvíz může mít více výsledků. - Výsledek je přiřazen jednomu kvízu. - **Optionalita:** - **QUIZ:** 0:N (kvíz nemusí mít žádné výsledky). - **QUIZ RESULT:** 1 (každý výsledek musí být přiřazen ke kvízu). - **Odůvodnění:** Výsledky jsou generovány na základě hodnocení kvízu a slouží k analýze uživatelských znalostí. A diagram of a system Description automatically generated ### [Diskuze smyček] V mém konceptuálním schématu aplikace **StudyMates** (zahrnujícím entity **Uživatel**, **Dokument**, **Kategorie**, **Klíčová informace**, **Kvíz**, **Otázka**, **Odpověď**, **Výsledek kvízu**, **Poznámka** a **Systémové nastavení**) jsem se snažila vyhnout jakýmkoli smyčkám (cyklům), které by mohly vést k nekonzistenci dat nebo redundanci vztahů. - **Co je smyčka?**\ Smyčkou rozumíme situaci, kdy například existuje cesta A → B → C → A (nebo delší), která může způsobit dvojí (či vícenásobné) vyjádření stejné vazby a komplikovat pravidla pro vkládání, mazání nebo aktualizaci dat. - **Vyskytují se smyčky v mém schématu?** - **Ne**. Podle aktuálního návrhu (viz ERD) neexistuje cyklus typu „Uživatel odkazuje na Dokument, Dokument odkazuje na Kvíz a Kvíz znovu na Uživatel". Vztahy jsou spíše stromovitého (hierarchického) charakteru: - *Uživatel* ↔ *Dokument*, *Dokument* ↔ *Kategorie*, *Dokument* ↔ *Klíčová informace*, *Kvíz* ↔ *Otázka* ↔ *Odpověď*, atd. - Každá entita je vázaná cizími klíči logicky jedním směrem, takže krouživý vztah nevzniká. - **Možné problémy, kdyby tam smyčka byla**\ Kdyby v budoucnu do schématu někdo přidal vazbu, jež by vytvářela cyklus (například by *Kvíz* přímo odkazoval zpátky na *Dokument* a *Dokument* již odkazuje na *Uživatel* a *Uživatel* na *Kvíz*), mohly by vzniknout situace, kdy: - - - - **Závěr o smyčkách**\ Protože momentálně žádná smyčka není, **potenciální riziko datových nekonzistencí tohoto druhu nehrozí**. Pokud by se v budoucnu objevilo nutkání propojit entity do kruhu, je nutné důkladně zvážit, zda nepostačí už existující vazby, případně zda by se nemělo přidat speciální integritní omezení (např. triggery, CHECKy, anebo logika v aplikační vrstvě), aby data zůstala konzistentní. ### [ERDish věty] **ERDish** je způsob, jak v „přirozeném jazyce" popsat vztahy (relace) mezi entitami z obou stran, s uvedením volitelnosti a kardinality. Níže uvádím vybrané příklady (zkráceno, pro ilustraci): 1. **User -- Document** - Z pohledu uživatele: - Z pohledu dokumentu: 2. **Document -- Category** - Z pohledu dokumentu: - Z pohledu kategorie: 3. **Document -- Key Information** - Z pohledu dokumentu: - Z pohledu klíčové informace: 4. **Quiz -- Question** - Z pohledu kvízu: - Z pohledu otázky: 5. **Question -- Answer** - Z pohledu otázky: - Z pohledu odpovědi: 6. **User -- Quiz Result** - Z pohledu uživatele: - Z pohledu výsledku kvízu: 7. **Note -- Document** - Z pohledu dokumentu: - Z pohledu poznámky: 8. **System Setting -- User** - Z pohledu uživatele: - Z pohledu systémového nastavení: *(Poznámka: Čísla 0..N, 1..N apod. se v praxi upřesňují podle reálných procesů. Tam, kde píšu ‚může', je optionalita. Kde píšu ‚musí', tam je mandatory.)* ### **Normalizace a 3. NF** {#normalizace-a-3.-nf.ListParagraph} V konceptuální fázi jsem se již snažila přemýšlet o normalizaci. Každá entita představuje svébytný logický celek (například **Systémové nastavení** raději jako samostatnou entitu, než aby se polovina těchto údajů ukládala přímo do **Uživatel**). Tímto krokem zůstává databáze přehledná a lépe spravovatelná. - Pokud by se objevil příklad jako „stav objednávky" (ve stylu Objednávka v e-shopu) nebo „typ dokumentu" s mnoha volbami, mohla by se vytvořit zvláštní entita (číselník). To by dále posilovalo přehlednost a bránilo nekonzistenci v definicích. **Závěrem** lze říci, že ve schématu Studymates momentálně **není** cyklický vztah, proto nehrozí smyčky. Z hlediska ERDish jsme si vysvětlili vazby mezi entitami formou vět tak, aby bylo jasné, kdo s kým a jak. Normalizací se nám daří udržet data v čisté formě, takže by neměla vznikat duplicita atributů ani nadbytečná provázání. [Relační model dat] ------------------------------- #### **Tabulky relačního modelu** {#tabulky-relačního-modelu.ListParagraph} Tabulky v relačním modelu byly vytvořeny podle množných čísel názvů entit z konceptuálního modelu. Každá entita z konceptuálního modelu byla mapována na tabulku, přičemž klíčové atributy (PK) byly přeneseny jako primární klíče a atributy vztahující se k cizím klíčům (FK) byly přidány tam, kde byly vztahy 1:N nebo 1:1. #### **Popis jednotlivých tabulek** {#popis-jednotlivých-tabulek.ListParagraph} 1. **QUIZES** - **Primární klíč:** id\_quiz - **Atributy:** name, creationDate - **Popis:** Tabulka uchovává informace o všech kvízech vytvořených uživateli. Každý kvíz má jedinečné ID, název a datum vytvoření. 2. **QUESTIONS** - **Primární klíč:** id\_question - **Cizí klíč:** QUIZES\_id\_quiz - **Atributy:** text, typeQuestion - **Popis:** Obsahuje otázky patřící do kvízů. Typ otázky (např. multiple-choice nebo true/false) je definován pomocí sloupce typeQuestion. Bylo aplikováno omezení CHECK na hodnoty sloupce typeQuestion (M, T). 3. **ANSWERS** - **Primární klíč:** id\_answer - **Cizí klíč:** QUESTIONS\_id\_question - **Atributy:** text, isCorrect - **Popis:** Tabulka uchovává odpovědi patřící k otázkám. Každá odpověď je přiřazena právě jedné otázce. 4. **USERS** - **Primární klíč:** id\_user - **Atributy:** firstName, lastName, email, registrationDate, studyField - **Popis:** Tabulka uživatelů obsahuje informace o registrovaných uživatelích systému. Email má unikátní omezení (UNIQUE). 5. **DOCUMENTS** - **Primární klíč:** id\_document - **Cizí klíč:** USERS\_id\_user - **Atributy:** title, documentType, uploadDate, content, fileSize - **Popis:** Uchovává informace o nahraných dokumentech včetně jejich obsahu a metadat. Dokumenty jsou vždy spojeny s jedním uživatelem. 6. **CATEGORIES** - **Primární klíč:** id\_category - **Cizí klíč:** DOCUMENTS\_id\_document - **Atributy:** name - **Popis:** Tabulka kategorií obsahuje informace o různých kategoriích, do kterých jsou dokumenty zařazeny. 7. **KEY\_INFORMATIONS** - **Primární klíč:** id\_info - **Cizí klíč:** DOCUMENTS\_id\_document - **Atributy:** textInfo, position - **Popis:** Tabulka obsahuje klíčové informace extrahované z dokumentů. Každá informace je spojena právě s jedním dokumentem. 8. **NOTES** - **Primární klíč:** id\_note - **Cizí klíč:** DOCUMENTS\_id\_document - **Atributy:** text - **Popis:** Poznámky přidané k dokumentům, které poskytují další komentáře nebo poznámky uživatelů. 9. **QUIZ\_RESULTS** - **Primární klíč:** id\_result - **Cizí klíče:** USERS\_id\_user, QUIZES\_id\_quiz - **Atributy:** completionDate, score - **Popis:** Tabulka uchovává výsledky kvízů pro uživatele. Každý výsledek je spojen s uživatelem a konkrétním kvízem. 10. **SYSTEM\_SETTINGS** - **Primární klíč:** id\_setting - **Cizí klíč:** USERS\_id\_user - **Atributy:** theme, language, dateFormat - **Popis:** Tabulka uchovává systémová nastavení specifická pro každého uživatele. #### **Mapování vztahů** {#mapování-vztahů.ListParagraph} 1. **1:N vztahy** - Ve vztazích 1:N (např. USER - DOCUMENT, QUIZ - QUESTION) byl cizí klíč přidán do tabulky na straně \"N\" (např. DOCUMENTS\_id\_user). 2. **M:N vztahy** - Ve vztazích M:N (např. DOCUMENT - CATEGORY) byla vytvořena vazební tabulka (CATEGORIES\_DOCUMENTS) s primárními cizími klíči (DOCUMENTS\_id\_document, CATEGORIES\_id\_category) a případnými atributy podle potřeby. 3. **Supertyp/Subtyp (např. QUESTION\_TYPE)** - Subtyp byl řešen přidáním diskriminačního sloupce (typeQuestion) do tabulky QUESTIONS. Byl použit diskriminační přístup, který umožňuje rozlišení typu otázky (M pro multiple-choice, T pro true/false). #### {#section-2.ListParagraph} #### **Omezení (Integritní pravidla)** {#omezení-integritní-pravidla.ListParagraph} 1. **Primární klíče (PK):** Každá tabulka má svůj primární klíč. 2. **Cizí klíče (FK):** Každý cizí klíč zajišťuje referenční integritu mezi tabulkami. 3. **Omezení CHECK:** - QUESTIONS.typeQuestion má hodnoty omezené na M (multiple-choice) a T (true/false). 4. **Unikátní omezení (UNIQUE):** - Sloupec email v tabulce USERS je unikátní. 5. **Podniková pravidla:** - Dokument musí být vždy přiřazen k uživateli (DOCUMENTS.USERS\_id\_user je NOT NULL).  [Implementace databáze] =================================== [Fyzický model dat] ------------------------------- ### [Naplnění fyzického modelu dat -- postup] {#naplnění-fyzického-modelu-dat-postup.ListParagraph} V této kapitole popisuji, jak jsem svůj konceptuální model (viz obrázky výše) převedla do fyzického modelu v databázi Oracle. Zaměřím se na tyto klíčové oblasti: 1. Číselníky (code-listy), pokud se v projektu vyskytují, a jejich původ. 2. Sekvence -- jak jsem je použila a příklady jejich tvorby. 3. UNIQUE, indexy a další integritní omezení -- kde a proč jsem je aplikovala. 4. Implementace supertypu/subtypu (pokud v modelu existuje). 5. Slabé entity -- přístup k jejich implementaci. 6. PFK (potenciální cizí klíče) ve vazebních tabulkách -- jejich rozdíl oproti běžným FK. ### **Číselníky** {#číselníky.ListParagraph} V projektu **StudyMates** se číselníky (code-listy) vyskytují pouze v omezené míře. Mohly by být použity například v následujících případech: - **Typ dokumentu** (DOCUMENTTYPE v tabulce DOCUMENTS): Mohl by existovat číselník obsahující hodnoty jako „PDF", „DOCX", „PPT" atd. - **Jazyk uživatelského prostředí** (LANGUAGE v tabulce SYSTEM\_SETTINGS): Mohlo by se jednat o číselník obsahující hodnoty jako „CZ", „EN", „SK" atd. V této semestrální práci jsem však hodnoty ponechala jako volné textové pole (VARCHAR2(50)), aby byly flexibilní a nenáročné na implementaci. Pro produkční prostředí by bylo vhodné vytvořit samostatné tabulky (DOC\_TYPE\_LIST, LANGUAGE\_LIST) a použít cizí klíče. ### **Sekvence a jejich použití** {#sekvence-a-jejich-použití.ListParagraph} Primární klíče ve většině tabulek jsou typu INTEGER (například ID\_USER, ID\_DOCUMENT, ID\_QUIZ). Aby bylo zajištěno jejich unikátní generování, lze použít sekvence v kombinaci s triggery. Ukázková implementace pro tabulku USERS: #### [Vytvoření sekvence:] {#vytvoření-sekvence.ListParagraph} CREATE SEQUENCE S\_USERS START WITH 1 INCREMENT BY 1; #### [Vytvoření triggeru:] {#vytvoření-triggeru.ListParagraph} CREATE OR REPLACE TRIGGER T\_USERS\_ID BEFORE INSERT ON USERS FOR EACH ROW BEGIN IF :NEW.ID\_USER IS NULL THEN SELECT S\_USERS.NEXTVAL INTO :NEW.ID\_USER FROM DUAL; END IF; END; Tento přístup automaticky generuje unikátní hodnoty pro sloupec ID\_USER. V této semestrální práci jsem však primární klíče plnila ručně (např. 1, 2, 3...) kvůli lepší čitelnosti a kontrole při psaní INSERT skriptů. Pro reálné nasazení by však bylo vhodnější použít sekvence. ### **UNIQUE, indexy a další integritní omezení** {#unique-indexy-a-další-integritní-omezení.ListParagraph} - **UNIQUE**: - U tabulky USERS jsem přidala UNIQUE omezení na sloupec EMAIL, aby nebylo možné zadat duplicitní e-mailové adresy. - **Indexy**: - Primární klíče (ID\_USER, ID\_DOCUMENT) jsou v Oracle automaticky indexované. UNIQUE omezení rovněž vytváří implicitní indexy. - Neunikátní indexy jsem v tomto projektu explicitně nevytvářela. Pokud by to bylo potřeba, mohl by být například vytvořen index na sloupec STUDYFIELD v tabulce USERS: - **Další integritní omezení**: - **NOT NULL**: Použila jsem tam, kde to logicky dává smysl (například FIRSTNAME, LASTNAME a EMAIL v tabulce USERS). - **CHECK**: Příklad je u tabulky QUESTIONS, kde sloupec TYPEQUESTION může obsahovat pouze hodnoty \'M\' (multiple-choice) nebo \'T\' (true/false): ### **Implementace supertypu/subtypu** {#implementace-supertypusubtypu.ListParagraph} V mém modelu je supertyp QUESTIONS, který zahrnuje dva subtypy: - **Multiple-choice** (TYPEQUESTION = \'M\') - **True/false** (TYPEQUESTION = \'T\') Implementace: - Místo vytvoření dvou fyzických tabulek jsem použila jednu tabulku QUESTIONS se sloupcem TYPEQUESTION (CHAR(1)), který určuje typ otázky. - Tento přístup je jednodušší a efektivní, protože eliminuje potřebu synchronizovat dva různé subtypy. ### {#section-3.ListParagraph} ### **Slabé entity** {#slabé-entity.ListParagraph} Slabé entity v projektu přímo nemám, protože všechny tabulky mají své jednoznačné primární klíče. Pokud by se vyskytovala například tabulka závislá na jiné, její implementace by vypadala takto: - PK by byl složený z primárního klíče nadřazené tabulky + vlastního identifikátoru (například ID\_QUESTION a SUBQUESTION\_NUMBER). - Cizí klíč by odkazoval na nadřazenou tabulku, například: ### **PFK u vazebních tabulek** {#pfk-u-vazebních-tabulek.ListParagraph} PFK (potenciální cizí klíče) se používají zejména ve vazebních tabulkách u mnoho-na-mnoho vztahů. V mém modelu jsou vztahy spíše 1:N, například: - Tabulka CATEGORIES odkazuje na DOCUMENTS pomocí cizího klíče DOCUMENTS\_ID\_DOCUMENT. Kdybych implementovala vazbu M:N (například jeden dokument by mohl být ve více kategoriích), musela by vzniknout vazební tabulka DOCUMENTS\_CATEGORIES se složeným primárním klíčem: CREATE TABLE DOCUMENTS\_CATEGORIES ( DOCUMENT\_ID NUMBER NOT NULL, CATEGORY\_ID NUMBER NOT NULL, PRIMARY KEY (DOCUMENT\_ID, CATEGORY\_ID), CONSTRAINT FK\_DOC\_CAT\_DOCUMENT FOREIGN KEY (DOCUMENT\_ID) REFERENCES DOCUMENTS(ID\_DOCUMENT), CONSTRAINT FK\_DOC\_CAT\_CATEGORY FOREIGN KEY (CATEGORY\_ID) REFERENCES CATEGORIES(ID\_CATEGORY) ); V této tabulce by každý sloupec sloužil jako cizí klíč na svou nadřazenou tabulku, ale dohromady by tvořily primární klíč. ### **Závěr k fyzickému modelu** {#závěr-k-fyzickému-modelu.ListParagraph} - Pro primární klíče používám typ INTEGER a buď manuální plnění, nebo sekvence s triggery. - Cizí klíče (FK) vyžadují existenci referencované tabulky, přičemž používám ON DELETE CASCADE tam, kde je potřeba automaticky mazat závislé záznamy. - UNIQUE omezení jsem aplikovala například na sloupec EMAIL v tabulce USERS. - Subtypy MULTIPLE a TRUE\_FALSE jsou implementovány jednou tabulkou QUESTIONS s indikátorem typu (TYPEQUESTION). - Slabé entity nejsou v modelu přítomny, vše má svůj jednoznačný primární klíč. - Vazební tabulky s PFK by byly použity pro M:N vztahy, kdyby byly v modelu potřeba. Tímto přístupem jsem zajistila, že databáze je konzistentní, dobře strukturovaná a odpovídá konceptuálnímu i relačnímu modelu. ### [Integritní omezení (IO)] Integritní omezení zajišťují, že data v databázi zůstávají „správná" a v souladu s pravidly systému. V projektu **Studymates** jsem použila několik typů integritních omezení: **Primární klíče (PK)** - U každé tabulky mám definovaný primární klíč, který jednoznačně identifikuje každý řádek. - Například v tabulce USERS je primárním klíčem id\_user, v tabulce QUIZES je to id\_quiz, v tabulce DOCUMENTS id\_document atd. - Databáze si automaticky vytváří index pro tyto primární klíče. **Cizí klíče (FK)** - U tabulek, které odkazují na „rodičovskou" tabulku, mám definovaný cizí klíč. - Např. v tabulce QUIZES je sloupec USERS\_id\_user cizím klíčem do tabulky USERS(id\_user). - V tabulce QUESTIONS je cizí klíč QUIZES\_id\_quiz, v tabulce ANSWERS je cizí klíč QUESTIONS\_id\_question, atd. - Díky tomu se databáze stará o to, aby nedošlo k vložení záznamu s neexistujícími hodnotami FK (nebo kmazání, pokud je nastavené ON DELETE CASCADE / RESTRICT). **UNIQUE omezení** - V tabulce USERS mám sloupec email s omezením **UNIQUE**, aby se žádné dva uživatelé neregistrovali se stejným e-mailem. - UNIQUE lze použít i na kombinaci více sloupců (v projektu jsem to zatím nepotřebovala). **NOT NULL** - Některé sloupce logicky nesmí být prázdné, např. firstName, lastName, email v USERS, name v QUIZES, title v DOCUMENTS, atd. - To jsem vyjádřila klauzulí NOT NULL u definice sloupce. - Zajistí se tím, že databáze nepovolí vložit řádek bez hodnoty v těchto povinných sloupcích. **CHECK omezení** (příklad) - V tabulce QUESTIONS může být sloupec typeQuestion, který může nabývat pouze hodnot \'M\' (Multiple) nebo \'T\' (True/False). - Mohla bych na to dát **CHECK** jako: typeQuestion CHAR(1) CHECK (typeQuestion IN (\'M\',\'T\')) - Tím mám jistotu, že se mi do typeQuestion nedostane jiná hodnota. **Důsledky integritních omezení** - Udržují konzistenci a správnost dat. - **PK** zajišťují jednoznačnost a většinou se pojí s indexem, což urychluje vyhledávání. - **FK** brání „osiřelým" odkazům a mohou automatizovat mazání závislých záznamů. - **UNIQUE** brání duplicitě, např. dvou stejných e-mailů. - **NOT NULL** říká, že některé údaje musejí být vždy vyplněné. - **CHECK** omezení pak kontrolují logické podmínky, např. přípustné hodnoty v určitém sloupci. **Další IO (obchodní/kontextová pravidla)** - Některé složitější podmínky, které nelze jednoduše řešit v DDL (např. „datumVypracování kvízu nesmí být před datemVytvoření kvízu"), se mohou řešit buď: - **triggery** (na úrovni DB), - **procedurami** (v aplikační logice), - nebo **doplňkovými CHECK** (pokud je to jednoduché). - V rámci semestrálky jsem komplikovanější pravidla neimplementovala, ale naznačuji, že by se to dalo (např. trigger, který kontroluje, zda completionDate \>= creationDate). Tato **integritní omezení** dohromady garantují, že vložená či mazaná data vždy korespondují se stavem ostatních tabulek a že nedochází k porušování logických pravidel systému. **3.1.2 [Naplnění tabulek daty]** Při plnění tabulek pro projekt **StudyMates** jsem chtěla zajistit, aby nedošlo k problémům, jako je porušení referenční integrity (například „Cizí klíč odkazuje na neexistující záznam") nebo unikátních omezení (například „Duplicita hodnot ve sloupci s UNIQUE omezením"). Proto jsem si předem stanovila postup a pořadí, v jakém tabulky naplním, a pečlivě jsem kontrolovala vazby mezi nimi. ### **Tabulky bez cizích klíčů jako první** {#tabulky-bez-cizích-klíčů-jako-první.ListParagraph} Začala jsem tabulkami, které neobsahují cizí klíče nebo na které neodkazují jiné tabulky. V mém projektu to byly například tabulky **USERS** a **QUIZES**. Tyto tabulky jsou rodičovské pro většinu dalších tabulek, takže jsem je vytvořila a naplnila jako první. - Do tabulky **USERS** jsem vložila 20 uživatelů. - Primární klíč **ID\_USER** jsem nastavila manuálně (1, 2, 3,...), aby se mi snadno pracovalo s cizími klíči v dalších tabulkách a mohla jsem je lépe kontrolovat. ### **Tabulky s cizími klíči až poté** {#tabulky-s-cizími-klíči-až-poté.ListParagraph} Následně jsem se zaměřila na tabulky, které obsahují cizí klíče, například: - **QUIZ\_RESULTS** (obsahuje cizí klíče **USERS\_ID\_USER** a **QUIZES\_ID\_QUIZ**), - **DOCUMENTS** (obsahuje cizí klíč **USERS\_ID\_USER**), - **QUESTIONS** (odkazuje na **QUIZES** pomocí **QUIZES\_ID\_QUIZ**), - **ANSWERS** (odkazuje na **QUESTIONS** pomocí **QUESTIONS\_ID\_QUESTION**). Při plnění těchto tabulek jsem si vždy dávala pozor, aby hodnoty v cizích klíčích již existovaly v odpovídajících rodičovských tabulkách. Například jsem se ujistila, že v tabulce **QUIZ\_RESULTS** obsahuje sloupec **USERS\_ID\_USER** pouze hodnoty odkazující na uživatele z tabulky **USERS**. ### {#section-4.ListParagraph} ### **Dodržení UNIQUE omezení** {#dodržení-unique-omezení.ListParagraph} U tabulek, kde je definováno UNIQUE omezení (například **EMAIL** v tabulce **USERS**), jsem zajistila, že žádné hodnoty se nebudou opakovat. Každý uživatel dostal unikátní emailovou adresu, abych se vyhnula chybám spojeným s porušením této podmínky. ### **Postup pro závislé tabulky** {#postup-pro-závislé-tabulky.ListParagraph} Tabulky, které jsou závislé na jiných, jsem plnila až poté, co jejich nadřazené tabulky obsahovaly data. Například: - Tabulku **NOTES**, která odkazuje na **DOCUMENTS** pomocí **DOCUMENTS\_ID\_DOCUMENT**, jsem naplnila až po vložení dat do tabulky **DOCUMENTS**. - Podobně tabulku **KEY\_INFORMATIONS**, která také odkazuje na **DOCUMENTS**, jsem plnila až po dokončení plnění tabulky **DOCUMENTS**. ### {#section-5.ListParagraph} ### **Výhody tohoto postupu** {#výhody-tohoto-postupu.ListParagraph} Tento systematický přístup, kdy jsem nejprve plnila tabulky bez cizích klíčů a poté tabulky s vazbami na již existující záznamy, mi umožnil: - Vyhnout se problémům s referenční integritou (například odkaz na neexistující hodnotu v cizím klíči), - Zajistit, že UNIQUE omezení nebyla nikdy porušena, - Usnadnit si psaní INSERT skriptů díky manuálnímu nastavování primárních klíčů (např. ID uživatelů). ### **Souhrn postupu** {#souhrn-postupu.ListParagraph} 1. **USERS**: Vložila jsem 20 uživatelů (bez cizích klíčů, ID nastavováno ručně). 2. **QUIZES**: Tabulka odkazující na uživatele (vloženy kvízy s existujícími hodnotami **USERS\_ID\_USER**). 3. **QUIZ\_RESULTS, DOCUMENTS, QUESTIONS, ANSWERS**: Plněny v pořadí podle vazeb (např. FK v tabulce **QUIZ\_RESULTS** odkazují na data v **USERS** a **QUIZES**). 4. **NOTES, KEY\_INFORMATIONS**: Tabulky závislé na **DOCUMENTS** plněny až po vložení dat do tabulky **DOCUMENTS**. Tento strukturovaný přístup mi zajistil bezproblémové plnění dat do všech tabulek. [Výčet SQL dotazů] ============================== názvosloví tabulek: **Tabulka ANSWERS** - ID\_ANSWER - TEXT - ISCORRECT - QUESTIONS\_ID\_QUESTION **Tabulka CATEGORIES** - ID\_CATEGORY - NAME - DOCUMENTS\_ID\_DOCUMENT **Tabulka DOCUMENTS** - ID\_DOCUMENT - TITLE - DOCUMENTTYPE - UPLOADDATE - CONTENT - FILESIZE - USERS\_ID\_USER **Tabulka KEY\_INFORMATIONS** - ID\_INFO - TEXTINFO - POSITION - DOCUMENTS\_ID\_DOCUMENT **Tabulka NOTES** - ID\_NOTE - TEXT - DOCUMENTS\_ID\_DOCUMENT **Tabulka QUESTIONS** - ID\_QUESTION - TEXT - QUIZES\_ID\_QUIZ - TYPEQUESTION **Tabulka QUIZ\_RESULTS** - ID\_RESULT - COMPLETIONDATE - SCORE - USERS\_ID\_USER - QUIZES\_ID\_QUIZ **Tabulka QUIZES** - ID\_QUIZ - NAME - CREATIONDATE **Tabulka SYSTEM\_SETTINGS** - ID\_SETTING - THEME - LANGUAGE - DATEFORMAT - USERS\_ID\_USER **Tabulka USERS** - ID\_USER - FIRSTNAME - LASTNAME - EMAIL - REGISTRATIONDATE - STUDYFIELD {#section-6.ListParagraph} [DOTAZY]: **D1: Pozitivní dotaz nad spojením alespoň dvou tabulek** **Dotaz: Vyber seznam uživatelů a jejich nahraných dokumentů.** SELECT USERS.FIRSTNAME \|\| \' \' \|\| USERS.LASTNAME AS FULL\_NAME, DOCUMENTS.TITLE AS DOCUMENT\_TITLE, DOCUMENTS.UPLOADDATE FROM USERS JOIN DOCUMENTS ON USERS.ID\_USER = DOCUMENTS.USERS\_ID\_USER; **D2: Negativní dotaz nad spojením dvou tabulek** **Dotaz: Zobraz seznam uživatelů, kteří nenahráli žádné dokumenty.** SELECT USERS.FIRSTNAME \|\| \' \' \|\| USERS.LASTNAME AS FULL\_NAME FROM USERS LEFT JOIN DOCUMENTS ON USERS.ID\_USER = DOCUMENTS.USERS\_ID\_USER WHERE DOCUMENTS.ID\_DOCUMENT IS NULL; **D3: Vyber ty, kteří mají vztah pouze k určitému objektu** **Dotaz: Zobraz dokumenty, které jsou zařazeny pouze do jedné kategorie.** SELECT DOCUMENTS.TITLE AS DOCUMENT\_TITLE FROM DOCUMENTS JOIN CATEGORIES ON DOCUMENTS.ID\_DOCUMENT = CATEGORIES.DOCUMENTS\_ID\_DOCUMENT GROUP BY DOCUMENTS.ID\_DOCUMENT, DOCUMENTS.TITLE HAVING COUNT(CATEGORIES.ID\_CATEGORY) = 1; **D4: Vyber ty, kteří jsou ve vztahu se všemi** **Dotaz: Zobraz uživatele, kteří vyplnili všechny dostupné kvízy.** SELECT USERS.FIRSTNAME \|\| \' \' \|\| USERS.LASTNAME AS FULL\_NAME FROM USERS WHERE NOT EXISTS ( SELECT 1 FROM QUIZES WHERE NOT EXISTS ( SELECT 1 FROM QUIZ\_RESULTS WHERE QUIZ\_RESULTS.QUIZES\_ID\_QUIZ = QUIZES.ID\_QUIZ AND QUIZ\_RESULTS.USERS\_ID\_USER = USERS.ID\_USER ) ); **D5: Spojení - JOIN USING** **Dotaz: Vyber uživatele a jejich systémová nastavení.** SELECT USERS.FIRSTNAME, USERS.LASTNAME, SYSTEM\_SETTINGS.THEME, SYSTEM\_SETTINGS.LANGUAGE FROM USERS JOIN SYSTEM\_SETTINGS ON USERS.ID\_USER = SYSTEM\_SETTINGS.USERS\_ID\_USER; **D6: Spojení - JOIN ON** **Dotaz: Vyber názvy dokumentů a jejich kategorie.** SELECT DOCUMENTS.TITLE AS DOCUMENT\_TITLE, CATEGORIES.NAME AS CATEGORY\_NAME FROM DOCUMENTS JOIN CATEGORIES ON DOCUMENTS.ID\_DOCUMENT = CATEGORIES.DOCUMENTS\_ID\_DOCUMENT; **D7: Spojení - NATURAL JOIN** **Dotaz: Vyber kvízy a výsledky uživatelů.** SELECT NAME AS QUIZ\_NAME, SCORE FROM QUIZES NATURAL JOIN QUIZ\_RESULTS; **D8: Spojení - CROSS JOIN** **Dotaz: Zobraz všechny kombinace uživatelů a kvízů.** SELECT USERS.FIRSTNAME \|\| \' \' \|\| USERS.LASTNAME AS FULL\_NAME, QUIZES.NAME AS QUIZ\_NAME FROM USERS CROSS JOIN QUIZES; **D9: Polospojení (LEFT OUTER JOIN)** **Dotaz: Zobraz všechny kvízy a uživatele, kteří je vyplnili (včetně kvízů bez výsledků).** SELECT QUIZES.NAME AS QUIZ\_NAME, QUIZ\_RESULTS.SCORE, USERS.FIRSTNAME \|\| \' \' \|\| USERS.LASTNAME AS FULL\_NAME FROM QUIZES LEFT JOIN QUIZ\_RESULTS ON QUIZES.ID\_QUIZ = QUIZ\_RESULTS.QUIZES\_ID\_QUIZ LEFT JOIN USERS ON QUIZ\_RESULTS.USERS\_ID\_USER = USERS.ID\_USER; **D10: Polospojení (RIGHT OUTER JOIN)** **Dotaz: Vyber všechny uživatele a jejich systémová nastavení.** SELECT USERS.FIRSTNAME, USERS.LASTNAME, SYSTEM\_SETTINGS.THEME, SYSTEM\_SETTINGS.LANGUAGE FROM USERS RIGHT JOIN SYSTEM\_SETTINGS ON USERS.ID\_USER = SYSTEM\_SETTINGS.USERS\_ID\_USER; **D11: Plné (vnější) spojení - FULL JOIN** **Dotaz: Vyber dokumenty a jejich kategorie (včetně nezařazených dokumentů a kategorií bez dokumentů).** SELECT DOCUMENTS.TITLE AS DOCUMENT\_TITLE, CATEGORIES.NAME AS CATEGORY\_NAME FROM DOCUMENTS FULL JOIN CATEGORIES ON DOCUMENTS.ID\_DOCUMENT = CATEGORIES.DOCUMENTS\_ID\_DOCUMENT; **D12: Vnořený dotaz v klauzuli WHERE** **Dotaz: Vyber uživatele, kteří nahráli dokument s konkrétním názvem.** SELECT FIRSTNAME, LASTNAME FROM USERS WHERE ID\_USER IN ( SELECT USERS\_ID\_USER FROM DOCUMENTS WHERE TITLE = \'Biology Basics\' ); **D13: Vnořený dotaz v klauzuli FROM** **Dotaz: Vyber průměrné skóre za jednotlivé kvízy.** SELECT QUIZES.NAME AS QUIZ\_NAME, AVG(SCORE) AS AVERAGE\_SCORE FROM (SELECT QUIZES\_ID\_QUIZ, SCORE FROM QUIZ\_RESULTS) SUBQUERY JOIN QUIZES ON QUIZES.ID\_QUIZ = SUBQUERY.QUIZES\_ID\_QUIZ GROUP BY QUIZES.NAME; **D14: Vnořený dotaz v klauzuli SELECT** **Dotaz: Zobraz uživatele a počet nahraných dokumentů u každého z nich.** SELECT USERS.FIRSTNAME \|\| \' \' \|\| USERS.LASTNAME AS FULL\_NAME, (SELECT COUNT(\*) FROM DOCUMENTS WHERE DOCUMENTS.USERS\_ID\_USER = USERS.ID\_USER) AS DOCUMENT\_COUNT FROM USERS; **D15: Vztažený vnořený dotaz (EXISTS \| NOT EXISTS)** **Dotaz: Zobraz uživatele, kteří nemají žádné výsledky kvízů.** SELECT USERS.FIRSTNAME, USERS.LASTNAME FROM USERS WHERE NOT EXISTS ( SELECT 1 FROM QUIZ\_RESULTS WHERE QUIZ\_RESULTS.USERS\_ID\_USER = USERS.ID\_USER ); **D16: Množinové sjednocení - UNION** **Dotaz: Vyber všechny názvy dokumentů a kvízů v jedné tabulce.** SELECT TITLE AS NAME FROM DOCUMENTS UNION SELECT NAME FROM QUIZES; **D17: Množinový rozdíl -- MINUS** **Dotaz: Vyber názvy kvízů, které nemají žádné výsledky.** SELECT NAME FROM QUIZES MINUS SELECT QUIZES.NAME FROM QUIZ\_RESULTS JOIN QUIZES ON QUIZ\_RESULTS.QUIZES\_ID\_QUIZ = QUIZES.ID\_QUIZ; **D18: Množinový průnik - INTERSECT** **Dotaz: Vyber uživatele, kteří nahráli dokument a zároveň mají výsledek kvízu.** SELECT USERS.FIRSTNAME, USERS.LASTNAME FROM USERS JOIN DOCUMENTS ON USERS.ID\_USER = DOCUMENTS.USERS\_ID\_USER INTERSECT SELECT USERS.FIRSTNAME, USERS.LASTNAME FROM USERS JOIN QUIZ\_RESULTS ON USERS.ID\_USER = QUIZ\_RESULTS.USERS\_ID\_USER; **D19: Funkce pro práci s řetězci** **Dotaz: Zobraz emaily uživatelů převedené na malá písmena.** SELECT LOWER(EMAIL) AS LOWERCASE\_EMAIL FROM USERS; **D20: Funkce pro práci s čísly** **Dotaz: Zobraz velikost souborů zaokrouhlenou na celé kilobyty.** SELECT TITLE, ROUND(FILESIZE / 1024, 0) AS FILESIZE\_IN\_KB FROM DOCUMENTS; **D21: Funkce pro práci s datumy** **Dotaz: Zobraz názvy dokumentů a počet dnů od jejich nahrání.** SELECT TITLE, TRUNC(SYSDATE - UPLOADDATE) AS DAYS\_SINCE\_UPLOAD FROM DOCUMENTS; **D22: Agregační funkce (COUNT \| SUM \| MIN \| MAX \| AVG)** **Dotaz: Zobraz průměrné skóre uživatelů ve všech kvízech.** SELECT AVG(SCORE) AS AVERAGE\_SCORE FROM QUIZ\_RESULTS; **D23: Agregační funkce nad seskupenými řádky - GROUP BY (HAVING)** **Dotaz: Zobraz uživatele a počet kvízů, které vyplnili (jen ty, kteří vyplnili více než 2 kvízy).** SELECT USERS\_ID\_USER, COUNT(ID\_RESULT) AS QUIZ\_COUNT FROM QUIZ\_RESULTS GROUP BY USERS\_ID\_USER HAVING COUNT(ID\_RESULT) \> 2; **D24: Stejný dotaz ve třech různých formulacích SQL** **Dotaz: Vyber názvy dokumentů, které mají více než 1000 bajtů.** **Variant 1: Standardní SELECT s WHERE** SELECT TITLE FROM DOCUMENTS WHERE FILESIZE \> 1000; **Variant 2: Vnořený SELECT** SELECT TITLE FROM DOCUMENTS WHERE ID\_DOCUMENT IN ( SELECT ID\_DOCUMENT FROM DOCUMENTS WHERE FILESIZE \> 1000 ); **Variant 3: EXISTS** SELECT TITLE FROM DOCUMENTS d WHERE EXISTS ( SELECT 1 FROM DOCUMENTS WHERE d.ID\_DOCUMENT = ID\_DOCUMENT AND FILESIZE \> 1000 ); **D25: Všechny klauzule - SELECT FROM WHERE GROUP BY HAVING ORDER BY** **Dotaz: Vyber průměrnou velikost dokumentů nahraných uživateli, kteří mají více než dva dokumenty, seřazené podle uživatelského ID.** SELECT USERS\_ID\_USER, AVG(FILESIZE) AS AVERAGE\_FILESIZE FROM DOCUMENTS GROUP BY USERS\_ID\_USER HAVING COUNT(ID\_DOCUMENT) \> 2 ORDER BY USERS\_ID\_USER; **D26: Pohled VIEW** **Dotaz: Vytvoř pohled, který zobrazí uživatele a jejich poslední nahraný dokument.** CREATE OR REPLACE VIEW USER\_LAST\_DOCUMENT AS SELECT USERS.FIRSTNAME, USERS.LASTNAME, DOCUMENTS.TITLE AS LAST\_DOCUMENT FROM USERS JOIN DOCUMENTS ON USERS.ID\_USER = DOCUMENTS.USERS\_ID\_USER WHERE DOCUMENTS.UPLOADDATE = ( SELECT MAX(UPLOADDATE) FROM DOCUMENTS d WHERE d.USERS\_ID\_USER = USERS.ID\_USER ); **D27: Dotaz nad pohledem alespoň s podmínkou** **Dotaz: Vyber uživatele z pohledu USER\_LAST\_DOCUMENT, kteří nahráli dokument obsahující slovo „Biology".** SELECT \* FROM USER\_LAST\_DOCUMENT WHERE LAST\_DOCUMENT LIKE \'%Biology%\'; **D28: INSERT bez klauzule VALUES** **Dotaz: Rezervuj všechny kvízy pro uživatele s ID 1.** INSERT INTO QUIZ\_RESULTS (ID\_RESULT, COMPLETIONDATE, SCORE, USERS\_ID\_USER, QUIZES\_ID\_QUIZ) SELECT SEQ\_QUIZ\_RESULTS.NEXTVAL, SYSDATE, NULL, 1, ID\_QUIZ FROM QUIZES; **D29: UPDATE s vnořeným SELECT příkazem** **Dotaz: Aktualizuj skóre uživatelů, kteří mají více než dva dokončené kvízy, na hodnotu 100.** UPDATE QUIZ\_RESULTS SET SCORE = 100 WHERE USERS\_ID\_USER IN ( SELECT USERS\_ID\_USER FROM QUIZ\_RESULTS GROUP BY USERS\_ID\_USER HAVING COUNT(ID\_RESULT) \> 2 ); **D30: DELETE s vnořeným SELECT příkazem** **Dotaz: Smaž výsledky kvízů pro uživatele, kteří nevyplnili žádný dokument.** DELETE FROM QUIZ\_RESULTS WHERE USERS\_ID\_USER IN ( SELECT ID\_USER FROM USERS WHERE ID\_USER NOT IN ( SELECT USERS\_ID\_USER FROM DOCUMENTS ) ); {#section-7.Nadpis1-bezsla} [Závěr] {#závěr.Nadpis1-bezsla} =================== Když se ohlédnu za prací na této databázi, musím říct, že to byla pořádná výzva, ale i zkušenost, která mě hodně naučila. Snažila jsem se pokrýt všechna zadání a pravidla, která byla součástí této semestrální práce. Přesto si nejsem stoprocentně jistá, jestli jsem někde něco nevynechala nebo neudělala chybně -- občas už jsem měla pocit, že nevím ani, jak se jmenuju, natož jestli všechno odpovídá zadání. Nicméně jsem se na to snažila dívat prakticky a lidsky, aby všechno dávalo smysl nejen v teoretické rovině, ale i při reálném použití databáze. Databáze navržená pro aplikaci **StudyMates** splňuje svůj účel -- umožňuje efektivní správu uživatelských účtů, dokumentů, kategorií, poznámek, klíčových informací a kvízů. Zároveň nabízí flexibilitu, což umožňuje její rozšíření do budoucna. Myslím, že bych v praxi některé věci ještě mohla vylepšit nebo přizpůsobit podle toho, jak by se aplikace vyvíjela a jaké požadavky by přinesli uživatelé. Například bych mohla více optimalizovat dotazy nebo přidat další číselníky pro zlepšení konzistence dat. **Co jsem se naučila?**\ Práce s databázemi mě naučila logickému přemýšlení o datech a vztazích mezi nimi. Pochopila jsem, jak důležité je dobře promyslet strukturu už na začátku, protože každá změna v pozdější fázi může být složitá. Taky jsem si procvičila různé SQL dotazy -- od jednoduchých až po složitější, jako jsou vnořené dotazy, spojení tabulek. Určitě mi to pomohlo pochopit, jak databáze fungují a jak je lze využít v reálných projektech. **Jak znalosti využiju?**\ Myslím, že databáze se stane důležitou součástí mého budoucího studia i práce. Ať už bych pracovala na aplikacích podobných StudyMates, nebo na něčem úplně jiném, věřím, že znalosti, které jsem si teď osvojila, uplatním. Navíc mi to dalo jistotu v psaní SQL skriptů a navrhování databázových modelů. **Co bych ještě mohla zlepšit?**\ Možná bych mohla více experimentovat s optimalizací nebo přidáním dalších funkcionalit do databáze. Občas jsem měla pocit, že se některé věci opakují nebo že některé části by mohly být ještě lépe uspořádané. Pokud bych na projektu pracovala dál, určitě bych časem udělala několik úprav. **Závěrem**\ Mám radost, že jsem se na tento projekt vrhla, a věřím, že navržená databáze splňuje základní požadavky a je dobře použitelná. Věřím, že **StudyMates** může být skvělým pomocníkem pro studenty a že by je aplikace mohla motivovat k lepší organizaci a učení. Těším se, že budu v {#section-8.Nadpis1-bezsla} [Přílohy] {#přílohy.Nadpis1-bezsla} ===================== 1. DDL skript pro vytvoření tabulek, omezení, sekvencí, indexů, triggerů, apod. 2. DML skript pro naplnění tabulek daty. 3. Vygenerovaná dokumentace z Oracle SQL Developer Data Modeler (kapitola 3.1 Fyzický model dat) 4. Soubor.dmd včetně složky generované Oracle SQL Developer Data Modeler(Datový model musí souhlasit s DDL skriptem) 5. Obrázek konceptuálního modelu (ERD) vygenerovaný z Oracle SQL Developer Data Modeler. 6. Obrázek datového modelu vygenerovaný z Oracle SQL Developer Data Modeler. 7. Skript se všemi SQL dotazy (kapitola 4. Výčet SQL dotazů).