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

Úvod do Microsoft SQL Server

Modelovanie

Príklad

Úvod do T-SQL a skriptovania

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

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

• Prednáška: Relačná algebra (pdf)
• Relational algebra (en.wikipedia.org)
• Relational Algebra (www.tutorialspoint.com)
• Relational Algebra (www.javatpoint.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

• 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)
• Funkčné závislosti a normalizácia pre relačné databázy (dokumen.tips)

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 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)

• 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)

• Window funkcie (pdf)
• Window Functions (www.javatpoint.com)
• 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)

1. previerka

• 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)

• 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)

• 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)

• @@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

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

Systémové pohľady

• 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)

• 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)

• WITH (learn.microsoft.com)

Príklad

• CTE

Tranzitívny uzáver a rekurzia

• 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
• Rekurzia - Faktoriál - riešenie

Transakcie (TRANSACTION)

• Tvoria 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é)

• 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)

• 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)

• 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)

• 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)

B-strom 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-tree (en.wikipedia.org)
• B+ tree (en.wikipedia.org)
• R-tree (en.wikipedia.org)
• B-Tree (www.javatpoint.com)
• B+ Tree (www.javatpoint.com)
• 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)

Hlavnou výhodou indexu je možnosť rýchleho vyhľadávania záznamov podľa nejakej podmienky.

• 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)

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

• XML database (en.wikipedia.org)
• XQuery Tutorial (www.w3schools.com)
• XQuery Tutorial (www.tutorialspoint.com)
• A Guided Tour for XQuery Tutorial (www.progress.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)
• XML Schema (docstore.mik.ua)
• 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)

• 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)

• SQL Graph Architecture (learn.microsoft.com)

Priestorová databáza (Spatial database)

• 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)