ÚINF/DBS1b/15 Databázové systémy

Úvod do Microsoft SQL Server

Motivácia

Microsoft SQL Server je robustný RDBMS pre profesionálnu prácu s dátami, ktorý ponúka viac možností ako MySQL a výbornú integráciu s Microsoft nástrojmi.

Porovnanie

• Prednáška: Úvod do MS SQL Server

Modelovanie

Príklad

Úvod do T-SQL a skriptovania

• T-SQL (learn.microsoft.com)
• T-SQL Tutorial (www.tsql.info)

Motivácia

Transact-SQL (T-SQL) je programovací jazyk používaný na správu a manipuláciu s relačnými databázami. T-SQL je rozšírením štandardu SQL (Structured Query Language) a pridáva ďalšie funkcionality na správu dát a objektami databázy. Podporuje širokú škálu funkcionalít pre DDL, DQL, DML, DCL a TCL.

T-SQL ponúka oproti MySQL výrazne širšie možnosti, ktoré dávajú vývojárovi aj databáze väčšiu silu a kontrolu nad dátami. Okrem klasických SQL príkazov obsahuje plnohodnotné programovacie prvky ako premenné, podmienky, cykly a ošetrenie chýb pomocou TRY/CATCH, vďaka čomu sa dá komplexná biznis logika riešiť priamo na úrovni databázy. Má pokročilé analytické funkcie (CTE vrátane rekurzie, window funkcie, PIVOT/UNPIVOT, GROUPING SETS, MERGE, OUTPUT ...), ktoré výrazne zjednodušujú reportovanie a spracovanie dát vo veľkom rozsahu. Správa transakcií, vyššia úroveň bezpečnosti a výborná integrácia s nástrojmi ako Power BI či Azure robia z T-SQL ideálnu voľbu pre enterprise systémy, dátové sklady a profesionálne BI riešenia.

Porovnanie

Príklad

• Úvod do T-SQL a skriptovania

Databázové objekty

• sys.objects (learn.microsoft.com)
• Tabuľky (TABLE)
• U = Table (user-defined)
• S = System base table
• IT = Internal table
• ET = External Table
• Pohľady (VIEW)
• V = View
• Integritné obmedzenia (CONSTRAINT)
• D = DEFAULT (constraint or stand-alone)
• PK = PRIMARY KEY constraint
• F = FOREIGN KEY constraint
• UQ = UNIQUE constraint
• C = CHECK constraint
• Spúšťače (TRIGGER)
• TR = SQL DML trigger
• TA = Assembly (CLR) DML trigger
• Uložené procedúry (STORED PROCEDURE)
• P = SQL Stored Procedure
• RF = Replication-filter-procedure
• X = Extended stored procedure
• PC = Assembly (CLR) stored-procedure
• Funkcie (FUNCTION)
• FN = SQL scalar function
• IF = SQL inline table-valued function
• TF = SQL table-valued-function
• FS = Assembly (CLR) scalar-function
• FT = Assembly (CLR) table-valued function
• AF = Aggregate function (CLR)
• Iné
• PG = Plan guide
• R = Rule (old-style, stand-alone)
• SO = Sequence object
• SN = Synonym

Príklad

• Databázové objekty

Čo sa ešte naučím?

• DECLARE
• variables
• CURSOR
• DDL - Data Definition Language - CREATE, ALTER, DROP
• VIEW
• INDEX
• TRIGGER
• PROCEDURE
• FUNCTION
• DQL - Data Query Language - SELECT
• Common Table Expression (CTE), Recursive CTE
• PIVOT
• Window Functions
• TCL – Transaction Control Language - TRANSACTION
• COMMIT
• ROLLBACK
• Database Model
• XML
• JSON
• ...

Relačná algebra

Motivácia

Relačná algebra je základným nástrojom práce s relačnými databázami. Umožňuje presne a formálne vyjadriť dopyty, manipulovať s dátami a pochopiť vnútornú logiku databáz. Ovládanie týchto operácií poskytuje pevný teoretický základ pre efektívne využívanie databázových systémov a tvorbu optimalizovaných dopytov.

Návod

• Prednáška: Relačná algebra (pdf)
• Relational algebra (en.wikipedia.org)
• Relational Algebra (www.tutorialspoint.com)
• Introduction of Relational Algebra in DBMS (www.geeksforgeeks.org)
• Relational Algebra in DBMS: Operations with Examples (www.guru99.com)
• Relational Algebra (www.cbcb.umd.edu)
• Relational Algebra (infolab.stanford.edu)
• Vyhodnocovanie výrazov relačnej algebry v odpovedníkoch IS (Bakalárska práca) (is.muni.cz)

Relačná algebra - online nástroje

• Query-converter – SQL to Relational Algebra (www.grammaticalframework.org)
• RELAX - relational algebra calculator (nireas.iee.ihu.gr)
• How to use RELAX (Relational Algebra Calculator) (www.youtube.com)

Príklad

• Definujte:
• operand (operand) - hodnota alebo premenná
• operátor (operator)
• operácia (operation)
• term
• relácia (relation)
• relačná schéma (relation schema)
• relačná algebra (relational algebra)
• Definujte základné operátory v relačnej algebre:
• selekcia (selection)
• projekcia (projection)
• premenovanie stĺpcov (rename)
• množinové operácie - union, intersection, difference
• karteziánsky súčin (cartesian product), theta a natural join
• Definujte dopĺňujúce operátory v relačnej algebre:
• outer join - left outer join, right outer join, full outer join
• agregácia (aggregation)
• tranzitívny uzáver (transitive closure)
• Pre daný SQL dopyt vytvorte plán vykonávania dopytu (query execution plan) pomocou stromu relačných operátorov (triangle query) a pomocou postupnosti krokov na prevod do relačnej algebry. Ako vyzerá zápis v relačnej algebre?

                                         
        SELECT
        p.krstne AS Pacient,
        l.krstne AS Lekar,
        SUM(n.poplatok) AS Poplatok
        FROM navstevy n
        JOIN lekari l ON l.idL = n.idL
        JOIN pacienti p ON p.idP = n.idP
        WHERE
        n.idP <= 4
        GROUP BY p.krstne, l.krstne
        ORDER BY Poplatok DESC
                                         
                                      

  Pomôcka: Query-converter – SQL to Relational Algebra (www.grammaticalframework.org)

Funkčné závislosti, funkčný a atribútový uzáver, bezstrátová dekompozícia

Motivácia

Správna štruktúra databáz je kľúčová pre spoľahlivé uchovávanie a efektívne spracovanie dát. Funkčné závislosti umožňujú pochopiť vzťahy medzi atribútmi a odhaliť redundanciu v databázach. Konzistentné používanie funkčného a atribútového uzáveru poskytuje systematický nástroj na odvodzovanie všetkých relevantných závislostí a podporuje návrh správnych schém. Bezstrátová dekompozícia zaručuje, že rozklad tabuľky na menšie časti nestratí žiadne informácie, čím sa zvyšuje integrita dát a znižuje redundancia. Ovládanie týchto konceptov je preto nevyhnutné pre návrh efektívnych, spoľahlivých a logicky konzistentných databázových systémov.

Návod

• Prednáška: Funkčná závislosť, uzávery a bezstratová dekompozícia (pdf)
• Prednáška: Normalizácia relačných databáz (pdf)
• Navrhovanie databáz (www.dcs.fmph.uniba.sk)
• Algoritmy formálneho návrhu databáz (Bakalárska práca) (is.muni.cz)

Príklad

• Definujte:
• normalizácia (normalization)
• denormalizácia (denormalization)
• dekompozícia (decomposition)
• relačná schéma (relation schema)
• relácia (relation)
• funkčná závislosť (functional dependency)
• triviálna funkčná závislosť (trivial functional dependency)
• kľúčový atribút (key attribute)
• nekľúčový atribút (non-key attribute)
• kandidátny kľúč (candidate key)
• kompozitný kľúč (composite key)
• primárny kľúč (primary key)
• nadkľúč (super-key)
• podkľúč (sub-key)
• Analyzujte tabuľku Osoba(id, meno, pohlavie). Určte funkčné závislosti. Čo je kandidátny/nad/pod-kľúč?
• Pomocou Armstrongových pravidiel a pomocou definície dokážte:
• (x → y) ∧ (x → z) ⇒ x → yz
• (x → y) ∧ (wy → z) ⇒ wx → wz
• (x → y) ∧ (z ⊆ y) ⇒ x → z
• Uvažujme relačnú schému ℛ(𝒜, ℱ), kde 𝒜 = { A, B, C, D, E, F }, ℱ = { AB → C, BC → AD, D → E, CF → B }.
• Určte {AB}⁺
• Vyplývajú z ℱ funkčné závislosti: AB → D a D → A? Prečo?
• Je AB kandidátný kľúč? Prečo?
• Vymenujte kandidátne kľúče?
• Je AB primárny kľúč? Prečo?
• Je AB kompozitný kľúč? Prečo?
• Vymenujte nadkľúče ABF
• Vymenujte podkľúče ABF
• Určte kľúčové a nekľúčové atribúty
• Uvažujme tabuľku ABC.
• Navrhnite Lossless/bezstratovu dekompoziciu tabulky ABC.
• Navrhnite Lossy/stratovu dekompoziciu tabulky ABC.
• Uvažujme rozklad {T1,T2} tabuľky T.
• Presvedčte sa, že rozklad bude stratový (SQL kódom a potom aj pomocou kritéria).
• Zmeňte jednu hodnotu v tabuľke T, aby daný rozklad bol bezstratový (je prienik NK?).
• Interpretujte R₁ ∪ R₂, R₁ × R₂ a R₁ ⋈ R₂.

Essential Tuple Normal Form (ETNF)

Motivácia

Essential Tuple Normal Form (ETNF) predstavuje pokročilú normalizačnú formu, ktorá rieši špecifické problémy redundancie a anomálií v relačných databázach, ktoré nie sú úplne ošetrené bežnými normálnymi formami. ETNF umožňuje identifikovať a izolovať kľúčové atribúty a podmnožiny údajov, čím sa zabezpečí, že každá relačná tabuľka zachováva integritu dát bez zbytočného duplikovania informácií. Ovládanie ETNF je dôležité pri návrhu komplexných databázových systémov, kde je potrebné dosiahnuť vysokú konzistenciu dát pri minimalizácii redundancie.

Návod

• Prednáška: Normálne formy na základe funkčných a spojovacích závislostí (pdf)
• ETNF - A Normal Form for Preventing Redundant Tuples in Relational Databases (web.archive.org)
• Database normalization (en.wikipedia.org)

Príklad

• Uvažujme relačnú schému ℛ(𝒜, ℱ, 𝒥), kde 𝒜 = { A, B, C, D }, ℱ = { A → BCD, BC → AD }, 𝒥 = { ⋈{ABC, CD, BD} }.
• Dokážte, že R je v HBCNF.
• Nech X → Y je ľubovoľná netriviálna FZ, kde X je ľubovoľná podmnožina 𝒜. Máme ukázať, že determinant X je nadkľúč. Stačí ukázať tieto 4 prípady:
• Predpokladajme, že determinant X obsahuje 3 atribúty
• Predpokladajme, že determinant X obsahuje 2 atribúty
• Predpokladajme, že determinant X obsahuje 1 atribút
• Predpokladajme, že determinant X je prázdna množina
• Uvažujme relačnú schému ℛ(𝒜, 𝒥), 𝒜 = { D, S, P }, 𝒥 = { ⋈{DS, SP, PD} }, kde D je dodávateľ, S je súčiastka, P je projekt.
• Dokážte, že v inštancii r relačnej schémy ℛ informácia o n-tici (d, s, p) je dvakrát, teda (d, s, p) je redundantná/nadbytočná.
• Uvažujme relačnú schému ℛ(𝒜, ℱ, 𝒥), 𝒜 = { D, S, P }, ℱ = { DS → P }, 𝒥 = { ⋈{DS, SP, PD} }, kde D je dodávateľ, S je súčiastka, P je projekt. Dokážte:
• ℛ je v HBCNF.
• Veta 3. Nech ℛ je ľubovoľná schéma. Potom ℛ je v HBCNF vtedy a len vtedy, ak žiadna inštancia ℛ nemá čiastočne redundantnú n-ticu.
• Žiadna inštancia ℛ nemá čiastočne redundantnú n-ticu.
• Chase (algorithm) (en.wikipedia.org)
• Žiadna inštancia ℛ nemá plne redundantnú n-ticu.
• ℛ je v ETNF.
• Definícia. Relačná schéma ℛ je v normálnej forme nevyhnutných n-tíc (essential tuple normal form - ETNF), ak každá n-tica ľubovoľnej inštancie ℛ je nevyhnutná.
• Definícia. Nech ℛ je relačná schéma, r relácia ako inštancia ℛ a t n-tica z r. Potom n-tica je nevyhnutná (essential), ak nie je ani čiastočne ani plne redundantná.

Window funkcie (Window functions)

Motivácia

Window funkcie (Window functions) sú špeciálne funkcie v SQL, ktoré umožňujú vykonávať výpočty nad skupinou riadkov (oknom) súvisiacich s aktuálnym riadkom, bez toho, aby sa zmenila štruktúra výslednej množiny riadkov. Na rozdiel od agregačných funkcií, ktoré zoskupujú riadky do jednej hodnoty, window funkcie vracajú hodnotu pre každý riadok v pôvodnej množine. Window funkcie sú veľmi užitočné pre reporting, analýzu trendov a business intelligence, kde sa často pracuje s agregovanými alebo porovnávanými hodnotami v rámci skupín.

Návod

• Prednáška: Window funkcie (pdf)
• Window Functions (www.mssqltips.com)
• Window Functions (www.sqlservertutorial.net)
• SELECT - OVER Clause (learn.microsoft.com)

Príklad

• Window funkcie (1. časť)
• Window funkcie (2. časť)
• Window funkcie (3. časť)
• Window funkcie (Ďalšie príklady)

Previerka z teoretickej časti

• Relačná algebra
• Armstrongove pravidlá
• Funkčné závislosti
• Funkčný a atribútový uzáver
• Bezstrátová dekompozícia
• Normálne formy

Procedúry (PROCEDURE)

Motivácia

Procedúra (PROCEDURE), často nazývaná uložená procedúra (Stored Procedure), je preddefinovaný blok SQL príkazov, ktorý sa ukladá v databáze a môže byť opakovane volaný. Procedúry umožňujú automatizovať opakujúce sa úlohy, zlepšiť výkon a zjednodušiť správu databázy. Procedúry sú vhodné pre automatizované operácie, reporting, validáciu dát...

Návod

• System stored procedures (learn.microsoft.com)
• EXECUTE (learn.microsoft.com)
• sys.procedures (learn.microsoft.com)
• CREATE PROCEDURE (learn.microsoft.com)
• ALTER PROCEDURE (learn.microsoft.com)
• DROP PROCEDURE (learn.microsoft.com)

Príklad

• Procedúry

Funkcie (FUNCTION)

Motivácia

Funkcia (FUNCTION) je uložený blok SQL kódu, ktorý prijíma vstupné parametre, vykonáva určité operácie a vracia výsledok. Na rozdiel od procedúry, funkcia vždy vracia hodnotu a môže byť použitá priamo v SQL dopytoch, napríklad v SELECT, WHERE alebo ORDER BY. Funkcie sú vhodné na opakovateľné výpočty, transformáciu dát a zjednodušenie SQL dopytov.

Návod

• Functions (learn.microsoft.com)
• sys.sql_modules (learn.microsoft.com)
• CREATE FUNCTION (learn.microsoft.com)
• ALTER FUNCTION (learn.microsoft.com)
• DROP FUNCTION (learn.microsoft.com)

Príklad

• Funkcie

Ošetrenie chýb (ERROR, TRY ... CATCH)

Motivácia

Ošetrenie chýb umožňuje predchádzať nežiadaným následkom pri vykonávaní SQL kódu a zabezpečuje spoľahlivosť databázových aplikácií. Pomocou mechanizmov ako ERROR a TRY…CATCH je možné zachytiť a spracovať chyby, logovať ich a reagovať vhodným spôsobom bez prerušenia celého procesu. Toto zvyšuje robustnosť, bezpečnosť a predvídateľnosť spracovania dát a je nevyhnutné pri návrhu profesionálnych databázových riešení. Ošetrenie chýb pomocou TRY…CATCH je základným nástrojom pre tvorbu robustných procedúr, funkcií a T-SQL skriptov, kde je potrebná kontrola nad neočakávanými situáciami.

Návod

• @@ERROR (learn.microsoft.com)
• @@ROWCOUNT (learn.microsoft.com)
• TRY...CATCH (learn.microsoft.com)
• RAISERROR (learn.microsoft.com)
• sys.messages (learn.microsoft.com)
• sp_addmessage (learn.microsoft.com)
• sp_dropmessage (learn.microsoft.com)

Príklad

• Ošetrenie chýb

Common Language Runtime (CLR)

Motivácia

Common Language Runtime (CLR) umožňuje integráciu .NET kódu priamo do SQL Servera, čím rozširuje možnosti spracovania dát nad rámec štandardného T-SQL. Vďaka CLR je možné vytvárať používateľom definované funkcie, procedúry, triggre a typy s plnou podporou programovacích jazykov .NET, čo zvyšuje flexibilitu, výkon a možnosti automatizácie komplexných úloh priamo na úrovni databázy. CLR tak poskytuje moderný a robustný nástroj pre rozšírené spracovanie dát v podnikových databázach. CLR sa používa najmä v prípadoch, keď T-SQL nestačí alebo nie je efektívny, a je potrebná silnejšia programovacia logika priamo v databáze.

Návod

• Common Language Runtime Integration (learn.microsoft.com)
• Get started with CLR integration (learn.microsoft.com)

Systémové pohľady

Motivácia

Systémové pohľady sú špeciálne databázové pohľady, ktoré poskytujú informácie o štruktúre databázy, jej objektoch, konfigurácii a stave SQL Servera. Umožňujú efektívne monitorovať, spravovať a analyzovať stav databázového systému bez priameho zásahu do dát, čím zvyšujú kontrolu, bezpečnosť a prehľadnosť správy databáz. Sú určené najmä pre administrátorov a vývojárov na monitorovanie, správu a analýzu databázového systému. Systémové pohľady sú základným nástrojom pre administráciu, audit, monitoring a optimalizáciu SQL Servera a umožňujú nahliadnuť do vnútra databázového systému.

Návod

• Views (learn.microsoft.com)
• System Information Schema Views (learn.microsoft.com)
• Using Information Schema Views (www.red-gate.com)
• Object Catalog Views (learn.microsoft.com)
• sys.objects (learn.microsoft.com)

Príklad

• Systémové pohľady

Pohľady (VIEW)

Motivácia

Pohľad (VIEW) je virtuálna tabuľka, ktorá je definovaná pomocou SQL dopytu. Neuchováva vlastné dáta, ale zobrazuje údaje z jednej alebo viacerých tabuliek (prípadne iných pohľadov) podľa definície. Výsledok pohľadu sa vytvára dynamicky pri jeho použití. Pohľady umožňujú zjednodušiť prístup k dátam a skryť zložitosť databázovej štruktúry, opakovane využívať definované dopyty, zvyšujú prehľadnosť práce s databázou a podporujú bezpečnosť tým, že obmedzujú prístup len k vybraným údajom. Pohľady sa používajú najmä pri reportingu, bezpečnostných obmedzeniach a zjednodušení práce s databázou.

Návod

• Views (learn.microsoft.com)
• sys.views (learn.microsoft.com)
• CREATE VIEW (learn.microsoft.com)
• ALTER VIEW (learn.microsoft.com)
• DROP VIEW (learn.microsoft.com)

Príklad

• VIEW

Common Table Expression (CTE)

Motivácia

Common Table Expression (CTE) je dočasná pomenovaná výsledková množina dát, ktorá existuje len počas vykonávania jedného SQL dopytu. CTE nie je uložený databázový objekt (ako tabuľka alebo pohľad), ale logická konštrukcia, ktorá je definovaná a použitá v rámci jedného dopytu. Používa sa na zjednodušenie zložitých dopytov, zvýšenie čitateľnosti kódu a prácu s rekurzívnymi dátami. CTE je silný nástroj na tvorbu prehľadných, modulárnych a zrozumiteľných SQL dopytov, najmä pri práci s komplexnou logikou alebo hierarchickými dátami.

Návod

• WITH (learn.microsoft.com)

Príklad

• CTE

Tranzitívny uzáver a rekurzia

Motivácia

Tranzitívny uzáver a rekurzia rozširujú možnosti o spracovanie hierarchických, stromových a vzťahovo prepojených dát pomocou opakovaného (rekurzívneho) vykonávania dopytu. Umožňujú elegantne a prehľadne pracovať so stromovými štruktúrami, ako sú organizačné hierarchie, kategórie či závislosti medzi záznamami, bez potreby zložitých a neprehľadných dopytov. Typicky sa používajú na prácu s organizačnými štruktúrami, kategóriami, stromami adresárov alebo grafmi typu rodič–potomok. Rekurzívne CTE je silný nástroj na spracovanie stromových štruktúr v SQL, ktorý umožňuje riešiť zložité hierarchie prehľadným a efektívnym spôsobom. Taktiež predstavujú efektívny nástroj na riešenie komplexných problémov priamo v SQL.

Návod

• Hierarchical and recursive queries in SQL (en.wikipedia.org)
• WITH common_table_expression (learn.microsoft.com)
• SQL Server CTE (www.sqlservertutorial.net)
• SQL Server Recursive CTE (www.sqlservertutorial.net)
• A Definitive Guide To MySQL Recursive CTE (www.mysqltutorial.org)
• What Is a Recursive CTE in SQL? (learnsql.com)
• Recursive WITH Common Table Expression (dev.mysql.com)
• WITH Common Table Expressions (docs.oracle.com)
• The WITH Clause (www.sqlite.org)
• CTEReadme (wiki.postgresql.org)
• WITH Queries Common Table Expressions (www.postgresql.org)
• Recursive query example (www.ibm.com)
• Intro to Common Table Expressions (gist.github.com)
• Transitive Closure in SQL (dwhoman.com)

Príklad

• PotomokPredok
• Tranzitívny uzáver - PotomokPredok
• Tranzitívny uzáver - PotomokPredok s cestou
• Rekurzia - PotomokPredok
• Rekurzia - Faktoriál

Transakcie (TRANSACTION)

Motivácia

Transakcia (TRANSACTION) je logická jednotka práce v databáze, ktorá zahŕňa jeden alebo viac SQL príkazov vykonaných ako nedeliteľný celok. Buď sa vykonajú všetky operácie úspešne, alebo sa v prípade chyby žiadna zmena neuloží. Transakcie zabezpečujú konzistenciu a spoľahlivosť dát. Transakcie sú základným mechanizmom pre bezpečnú a spoľahlivú prácu s databázou, najmä pri aktualizáciách, ktoré musia prebehnúť ako jeden celok.

Transakcia tvorí základ pre konkurentné vykonávanie a zotavenie sa po zlyhaní systému.
4 dôležité vlastnosti, ktoré transakcia musí mať (kvôli konkurentným prístupom a možnému zlyhaniu systému):

• Atomic - Atomickosť (buď sa vykoná celá alebo sa databáza musí dostať do stavu, akoby sa nevykonala vôbec)
• Consistency - Konzistentnosť (po transakcii musí byť DB znova konzistentná – zachované integritné obmedzenia)
• Isolation - Nezávislosť (sú nezávislé od iných transakcií)
• Durability - Trvanlivosť (po potvrdení vykonania sú zmeny uložené)

Návod

• Transactions (learn.microsoft.com)
• @@TRANCOUNT (learn.microsoft.com)
• What Is MDF and LDF in SQL Server (www.c-sharpcorner.com)
• Different methods to attach SQL Server MDF files (www.sqlshack.com)

Príklad

• Transakcie (1. časť)
• Transakcie (2. časť)

Kurzory (CURSOR)

Motivácia

Kurzor (CURSOR) je databázový objekt uložený v dočasnej pamäti, ktorý umožňuje postupné spracovanie riadkov výsledku SQL dopytu riadok po riadku a tým poskytuje kontrolu nad priebehom spracovania dát a umožňuje implementovať zložitejšiu logiku priamo v databáze. Na rozdiel od bežných SQL dopytov, ktoré pracujú s celými množinami dát naraz, kurzor umožňuje postupné čítanie a spracovanie jednotlivých riadkov. Hoci kurzory môžu mať výkonostné obmedzenia, ich správne použitie je dôležité pri riešení špecifických úloh v databázových aplikáciách.

Návod

• Cursors (SQL Server) (learn.microsoft.com)
• Cursors (Transact-SQL) (learn.microsoft.com)
• DECLARE CURSOR (learn.microsoft.com)
• What is a Cursor in SQL? And when to use a Cursor? (www.c-sharpcorner.com)
• Cursors In SQL Server (www.scholarhat.com)

Príklad

• Kurzor
• Zálohovanie pomocou kurzora
• Práca s 2 kurzormi

Kontingenčné tabuľky (PIVOT)

Motivácia

Kontingenčná tabuľka (PIVOT) slúži na transformáciu riadkov na stĺpce a na agregáciu dát v SQL. Používa sa najmä pri tvorbe prehľadov a reportov, kde je potrebné zobraziť súhrnné údaje v prehľadnej tabuľkovej forme. Kontingenčné tabuľky (PIVOT) sú veľmi užitočné pri analýze dát a reportingu, kde je potrebné rýchlo získať súhrnný pohľad na údaje.

Návod

• FROM - Using PIVOT and UNPIVOT (learn.microsoft.com)
• QUOTENAME (learn.microsoft.com)
• STUFF (learn.microsoft.com)

Príklad

• Pivot tabuľka (excel)
• Pivot tabuľka (1. časť)
• Pivot tabuľka (2. časť)
• Pivot tabuľka (3. časť)

Spúšťače (TRIGGER)

Motivácia

Spúšťač (TRIGGER) je databázový objekt, ktorý sa automaticky vykoná ako reakcia na určitú udalosť v databáze. Najčastejšie ide o operácie INSERT, UPDATE alebo DELETE nad tabuľkou, resp. pohľadom. Trigger sa používa najmä na:

• Automatické vynucovanie databázových pravidiel a integrity dát
• Zaznamenávanie zmien (audit, logovanie)
• Automatické úpravy alebo dopĺňanie údajov
• Zabránenie neplatným operáciám nad dátami

Návod

• CREATE TRIGGER (learn.microsoft.com)
• ALTER TRIGGER (learn.microsoft.com)
• DROP TRIGGER (learn.microsoft.com)
• ENABLE TRIGGER (learn.microsoft.com)
• DISABLE TRIGGER (learn.microsoft.com)
• ALTER TABLE (learn.microsoft.com)
• sys.triggers (learn.microsoft.com)
• sys.sql_modules (learn.microsoft.com)
• DML Triggers (learn.microsoft.com)
• DDL Triggers (learn.microsoft.com)
• Logon Triggers (learn.microsoft.com)
• CLR triggers (learn.microsoft.com)
• Trigger Functions (learn.microsoft.com)
• COLUMNS_UPDATED (learn.microsoft.com)
• EVENTDATA (learn.microsoft.com)
• TRIGGER_NESTLEVEL (learn.microsoft.com)
• UPDATE - Trigger Functions (learn.microsoft.com)
• @@ROWCOUNT (learn.microsoft.com)
• SET NOCOUNT (learn.microsoft.com)
• Different Types of Triggers In SQL Server (www.scholarhat.com)
• SQL Server Triggers (www.sqlservertutorial.net)

Príklad

• Trigger 1 - INSTEAD OF DELETE
• Trigger 2 - @@ROWCOUNT
• Trigger 3 - FOR INSERT, UPDATE, DELETE
• Trigger 4 - UPDATE(priezvisko)
• Trigger 5 - UPDATE(dat_smrti)
• Trigger 6 - Následnosť triggerov
• Trigger 7 - Poliklinika

B-stromy (B-TREE)

Motivácia

B-strom (B-TREE) je samo vyvažujúca (self-balancing) stromová dátová štruktúra, vhodná na ukladanie a čítanie veľkých celkov dát a zabezpečuje vyhľadanie, vkladanie a vymazanie za logaritmický čas. B-strom je navrhnutý tak, aby minimalizoval počet prístupov na disk a umožnil rýchle operácie hľadania, vkladania a mazania hodnôt. B-strom sa používa hlavne na implementáciu indexov, pretože umožňuje rýchly prístup k záznamom a efektívne spracovanie veľkého množstva dát.

Návod

• B-tree (en.wikipedia.org)
• B+ tree (en.wikipedia.org)
• R-tree (en.wikipedia.org)
• Introduction of B-Tree (www.geeksforgeeks.org)
• Introduction of B+ Tree (www.geeksforgeeks.org)
• Introduction to R-tree (www.geeksforgeeks.org)
• Difference between B tree and B+ tree (www.geeksforgeeks.org)
• Visualization of B-Tree (www.cs.usfca.edu)
• Visualization of B+ Tree (www.cs.usfca.edu)

Príklad

• Vytvorte B-strom rádu 3 postupným vkladaním čísel
  10, 40, 50, 20, 60, 30, 70, 35, 25, 15, 27, 37, 23, 80, 90 a 65.
  
  • Visualization of B-Tree (www.cs.usfca.edu)
  • Visualization of B+ Tree (www.cs.usfca.edu)
• Zo stromu vytvoreného v predchádzajúcej úlohe vyhľadajte prvky
  60, 70, 90 a 65.
  
  • Visualization of B-Tree (www.cs.usfca.edu)
  • Visualization of B+ Tree (www.cs.usfca.edu)
• Zo stromu vytvoreného v predchádzajúcej úlohe odstráňte prvky
  60, 70, 90 a 65.
  
  • Visualization of B-Tree (www.cs.usfca.edu)
  • Visualization of B+ Tree (www.cs.usfca.edu)

Indexy (INDEX)

Motivácia

Index je databázový objekt, ktorý zvyšuje rýchlosť vyhľadávania a triedenia dát v tabuľke, podobne ako obsahuje kniha obsah alebo register. Index umožňuje databáze rýchlo nájsť riadky bez potreby prechádzať celú tabuľku. Hlavnou výhodou indexu je možnosť rýchleho vyhľadávania záznamov podľa nejakej podmienky. Index je teda nástrojom na optimalizáciu výkonu databázy, najmä pri čítaní dát pri veľkých tabuľkách a častých dopytoch.

Návod

• Does a Clustered Index really physically store the rows in key order? (blog.waynesheffield.com)
• Partitioned tables and indexes (learn.microsoft.com)
• Indexes (learn.microsoft.com)
• Heaps - Tables without Clustered Indexes (learn.microsoft.com)
• Clustered and nonclustered indexes described (learn.microsoft.com)
• SQL Server and Azure SQL index architecture and design guide (learn.microsoft.com) - pozri obrázok
• Managing Space Used by Objects (learn.microsoft.com)
• CREATE INDEX (learn.microsoft.com)
• ALTER INDEX (learn.microsoft.com)
• DROP INDEX (learn.microsoft.com)
• sys.indexes (learn.microsoft.com)
• DBCC (learn.microsoft.com)
• Using DBCC PAGE to Examine SQL Server Table and Index Data (www.mssqltips.com)
• How to use DBCC PAGE (techcommunity.microsoft.com)
• Tutorial: Database Engine Tuning Advisor (learn.microsoft.com)

Návrh indexu

Výber indexu a jeho typu treba dôkladne premyslieť. Existujú všeobecné odporúčania, kedy (ne)použiť index a aký typ. SQL Server má index-tuning system: Database Tuning Advisor (DTA).

• Tutorial: Database Engine Tuning Advisor (learn.microsoft.com)

Dobrý index

• Ak tabuľka je viac dopytovaná ako modifikovaná
• Stĺpce, ktoré nie sú často modifikované
• Stĺpec/-ce s cudzím kľúčom
• Stĺpec, ktorý má veľa odlišných hodnôt
• Stĺpce vo WHERE klauzule
• Dopyty s ORDER BY / JOIN / agregačné

Zlý index

• Ak tabuľka je viac modifikovaná ako dopytovaná
• Tabuľka s malým počtom riadkov
• Stĺpec, ktorý má málo odlišných hodnôt
• Stĺpec, ktorý sa v dopyte nepoužíva
• Stĺpce s dátovými typmi: text, ntext, image, a bit

Treba mať na pamäti, že ak index sa skladá z viacerých atribútov, potom pri vkladaní nových riadkov alebo modifikovaní daných stĺpcov prebieha viac dodatočných úkonov, súvisiacich s údržbou indexu.

Výhody jedinečného/unique indexu

• Viacstĺpcové jedinečné indexy zaručujú, že žiadne dva riadky v tabuľke nemôžu mať rovnakú kombináciu hodnôt pre tieto stĺpce
• Jedinečné indexy zabezpečujú integritu údajov definovaných stĺpcov
• Jedinečné indexy poskytujú dodatočné informácie užitočné pre optimalizátor dotazov, ktorý môže vytvárať efektívnejšie plány vykonávania.

Príklad

• DBCC
• Index_0_index_Create
• Index_1_CrInd_Insert__Insert_CrInd
• Index_2NO_ClIndex_Ukladanie
• Index_3a_RAND_SEED_KERNEL
• Index_3b_RandTab_for_Search
• Index_3c_Search

Extensible Markup Language (XML)

Motivácia

XML (Extensible Markup Language) je formát na ukladanie a prenos pološtruktúrovaných (semi-structured) dát, podobný hierarchickej databáze, ktorý je čitateľný pre človeka aj pre počítač. XML je teda užitočný pre scenáre, kde hierarchické, variabilné, štruktúrované alebo pološtruktúrované (semi-structured) dáta nemôžu byť jednoducho uložené v klasických stĺpcoch tabuľky, Cez Transact-SQL (T-SQL) je možné pracovať s XML dátami priamo pomocou špeciálneho typu XML a množstva funkcií pre čítanie, manipuláciu a validáciu XML a tak ukladať, načítavať a spracovávať XML dáta priamo v databáze.

Návod

XML

• Extensible Markup Language (XML) (www.w3.org)
• XML Tutorial (www.w3schools.com)
• XML (en.wikipedia.org)
• XML data (learn.microsoft.com)
• XML (learn.microsoft.com)
• XML Validator (www.w3schools.com)

XPath

• XML Path Language (XPath) (www.w3.org)
• XPath Tutorial (www.w3schools.com)
• XPath Syntax (learn.microsoft.com)
• Sample XML File for XPath Syntax (inventory.xml) (learn.microsoft.com)
• Online XPATH Tester (www.online-toolz.com)
• XPath Tester - Evaluator (www.freeformatter.com)

XQuery

• XQuery Tutorial (www.w3schools.com)
• XQuery Tutorial (www.tutorialspoint.com)
• XQuery Language Reference (learn.microsoft.com)
• FunctX XQuery Functions (www.xqueryfunctions.com)
• XQuery output in HTML (www.iro.umontreal.ca)

XSD

• XML Schema (www.w3.org)
• XML Schema Tutorial (www.w3schools.com)
• Understanding W3C Schema Complex Types (www.xml.com)
• XML Validator - XSD (www.freeformatter.com)
• XML Schemas (learn.microsoft.com)
• Designing XML Schema Libraries (learn.microsoft.com)

Príklad

• MatrixAttributes.xml
• XML (1. časť)
• XML (2. časť)

JavaScript Object Notation (JSON)

Motivácia

JSON (JavaScript Object Notation) je formát na ukladanie a prenos pološtruktúrovaných (semi-structured) dát, ktorý je jednoduchší a čitateľnejší než XML. JSON je vhodný na ukladanie pološtruktúrovaných (semi-structured) dát, ktoré sa často menia alebo majú flexibilnú štruktúru, ako sú nastavenia aplikácií, konfigurácie alebo dynamické atribúty objektov. Často používa na výmenu dát medzi aplikáciami a databázami. Cez Transact-SQL (T-SQL) je možné pracovať s JSON dátami priamo pomocou množstva funkcií pre čítanie, manipuláciu a transformáciu JSON a tak ukladať, načítavať a spracovávať JSON dáta priamo v databáze.

Návod

• Introducing JSON (www.json.org)
• JSON - Introduction (www.w3schools.com)
• JSON (en.wikipedia.org)
• JSON data in SQL Server (learn.microsoft.com)
• How to parse JSON in SQL Server (www.sqlshack.com)

Príklad

• JSON

Grafová databáza (SQL Graph database)

Motivácia

Grafová databáza (SQL Graph database) rozširuje relačné databázy o schopnosť modelovať a spracovávať dáta vo forme grafov, kde uzly predstavujú entity a hrany vzťahy medzi nimi. Tento prístup umožňuje efektívne analyzovať komplexné vzťahy a prepojenia, ako sú sociálne siete, odporúčacie systémy, sieťové topológie alebo závislosti medzi objektmi, pričom využíva štandardné SQL nástroje. Je to výkonný a flexibilný spôsob, ako riešiť úlohy, ktoré by boli v klasických relačných databázach zložité alebo neprehľadné.

Návod

• SQL Graph Architecture (learn.microsoft.com)

Priestorová databáza (Spatial database)

Motivácia

Priestorová databáza (Spatial database) je databázový systém navrhnutý na uloženie, spracovanie a analýzu priestorových a geografických dát. Na rozdiel od klasických relačných databáz umožňuje pracovať s objektmi, ktoré majú polohu, tvar a priestorové vzťahy, napríklad body, línie alebo polygóny. Poskytujú špeciálne dátové typy a funkcie na meranie vzdialeností, zisťovanie priestorových vzťahov či vykonávanie geografických dopytov, čo je kľúčové pre aplikácie v GIS, mapovaní, logistike alebo urbanistickom plánovaní. Použitie priestorových databáz zvyšuje presnosť, efektívnosť a možnosti analýzy dát s priestorovým rozmerom. Priestorové databázy sa využívajú v navigačných systémoch, urbanistickom plánovaní, logistike, sledovaní dopravy alebo prírodných zdrojov.

Návod

• Spatial Data (learn.microsoft.com)

Ďalšie možnosti SQL

• DDL - Data Definition Language
• CREATE, ALTER, DROP
• DATABASE
• U = TABLE
• D, PK, F, UQ, C = CONSTRAINT
• INDEX
• V = VIEW
• TR = TRIGGER
• ENABLE/DISABLE
• P = PROCEDURE
• FN, IF, TF = FUNCTION
• SN = Synonym (learn.microsoft.com)
• SO = Sequence object (learn.microsoft.com)
• ...
• TRUNCATE TABLE
• DML - Data Manipulation Language, DQL - Data Query Language
• INSERT
• BULK INSERT (learn.microsoft.com)
• UPDATE
• DELETE
• MERGE (learn.microsoft.com)
• SELECT
• Querying data
• Sorting data
• Limiting rows
• Filtering data
• Joining tables
• Grouping data + ROLLUP, CUBE
• Subqueries
• Set Operators
• Common Table Expression (CTE), Recursive CTE
• PIVOT
• Window Functions
• CURSOR
• Multimodel - XML, JSON, Graph, Spatial
• DCL - Data Control Language
• GRANT, REVOKE
• SQL Server Administration (www.sqlservertutorial.net)
• TCL – Transaction Control Language
• COMMIT, ROLLBACK
• ...

!!! SQL Injection !!!

• SQL Injection (www.w3schools.com)
• SQL Injection (owasp.org)
• SQL Injection (portswigger.net)
• What is SQL Injection (SQLi) and How to Prevent It (www.acunetix.com)

Tips & Tricks

• www.sqlshack.com
• www.mssqltips.com
• www.sqlservertutorial.net
• MySQL Tips (www.mysqltutorial.org)