TSQL2 The Temporal Structured Query Language Jaroslav Ciml

1. TSQL2 The Temporal Structured Query Language Jaroslav Ciml

2. Struktury pro čas v TSQL2 • Diskrétní časová přímka skládající se z atomických částí - chronons • Souvislá posloupnost chrononů se může seskupit – lze klást dotazy v různých granularitách (vteřiny, hodiny, dny,...) • Temporální typy v TSQL2: DATE, TIME, TIMESTAMP, INTERVAL (zděděné z SQL) + nový typ PERIOD

3. BCDM(Bitemporal Conceptual Data Model) • v relaci je každé n-tici přiřazena množina chrononů (bitemporal chronons) • Tuto množinu lze reprezentovat obdélníky v dvojrozměrném prostoru – osy: čas platnosti (valid-time) a transakční čas (transaction-time)

4. Bitemporální relace – příklad (1) Zaměstnanci (Jméno, Oddělení)

5. Bitemporální relace – příklad (2)

6. Definice schématu CREATETABLEPředpisy (JménoCHAR(30), LékařCHAR(30), LékCHAR(30), DávkaCHAR(30), DobaUžíváníINTERVALMINUTE) AS VALID STATE DAY AND TRANSACTION VT – čas, kdy je lék užíván TT – čas, kdy se informace dostala do databáze

7. 6 typů tabulek • Snapshot relation – nemá podporu času platnosti ani transakčního času • AS VALIDneboAS VALID STATE – podpora času platnosti • AS VALID EVENT – event relation, relace uchovává informace platné v určitý okamžik (nikoli platné po určitou dobu) • AS TRANSACTION – podpora transakčního času • AS VALID STATE AND TRANSACTION • AS VALID EVENT AND TRANSACTION typ tabulky lze změnit pomocí ALTER TABLE

8. SELECT SNAPSHOT Jméno FROMPředpisy WHERELék = 'Proventil' SELECT Jméno FROMPředpisy SELECT (1) Kdo užívá (užíval) léky? SELECT SNAPSHOT Jméno FROM Předpisy Kdo užívá (užíval) Proventil? Kdo užívá (užíval) léky a kdy?

9. SELECT (2) Spolu s kterými léky byl užíván Proventil? SELECT P1.Jméno, P2.Lék FROM Předpisy AS P1, Předpisy AS P2 WHERE P1.Lék = 'Proventil' AND P2.Lék <> 'Proventil' AND P1.Jméno = P2.Jméno Výsledkem jsou řádky obsahující dvojice (jméno pacienta, název léku) spolu s množinou maximálních časových úseků, kdy byly oba léky předepsány pacientovi současně

10. Restrukturalizace K srůstání časových úseků ve výsledku dochází automaticky, restrukturalizace umožňuje, aby k srůstání docházelo také v klauzuliFROM SELECT Jméno, Lék FROM Předpisy(Jméno, Lék) AS P WHERE CAST(VALID(P) AS INTERVAL MONTH) > INTERVAL '6' MONTH Kdo užíval stejný lék (celkově) déle než 6 měsíců? Konstrukce VALID(P) vrací pro každý řádek relace P dobu platnosti (množinu maximálních časových úseků) OperátorCASTprovede konverzi na typINTERVAL MONTHtím, že sečte časové úseky vrácené pomocíVALID(P)

11. Spojení Kdo užíval Proventil po celou dobu své léčby? SELECT SNAPSHOT P1.Jméno FROM Předpisy(Jméno) AS P1, P1(Lék) AS P2 WHERE P2.Lék = 'Proventil' AND VALID(P2)= VALID(P1) P1 – projekce relace Předpisy na sloupec Jméno P2 – projekce na sloupce Jméno a Lék (v obou případech dochází k srůstání) výsledkem klauzule FROM je přirozené spojení P1 a P2

12. SELECT SNAPSHOT P1.Jméno FROM(SELECT Jméno FROM Předpisy)AS P1, (SELECT Jméno, Lék FROM Předpisy) AS P2 WHERE P2.Lék = 'Proventil' AND VALID(P2) = VALID(P1) AND P1.Jméno = P2.Jméno Spojení Kdo užíval Proventil po celou dobu své léčby? SELECT SNAPSHOT P1.Jméno FROM Předpisy(Jméno) AS P1, P1(Lék) AS P2 WHERE P2.Lék = 'Proventil' AND VALID(P2) = VALID(P1) Alternativní zápis téhož

13. Partitioning Kdo užíval stejný lék souvislou dobu delší než 6 měsíců? SELECT SNAPSHOT Jméno, Lék, VALID(P) FROM Předpisy(Jméno, Lék)(PERIOD) AS P WHERE CAST(VALID(P) AS INTERVAL MONTH) > INTERVAL '6' MONTH Relace Předpisy je nejprve restrukturalizována na sloupce Jméno a Lék, konstrukcí (PERIOD) se každá řádka, která vznikne restrukturalizací rozdělí na několik řádek – vzniká zvláštní řádka pro každý maximální interval doby platnosti Alternativní zápis: SELECT Jméno, Lék FROM...

14. SELECT Jméno, Lék FROM Předpisy(Jméno, Lék) AS P WHERECAST(VALID(P) AS INTERVAL MONTH) > INTERVAL '6' MONTH Partitioning Kdo užíval stejný lék souvislou dobu delší než 6 měsíců? SELECT SNAPSHOT Jméno, Lék, VALID(P) FROMPředpisy(Jméno, Lék)(PERIOD) AS P WHERECAST(VALID(P) ASINTERVALMONTH) > INTERVAL '6' MONTH Kdo užíval stejný lék (celkově) déle než 6 měsíců?

15. SELECT P1.Jméno, P2.Lék FROM Předpisy AS P1, Předpisy AS P2 WHERE P1.Lék = 'Proventil' AND P2.Lék <> 'Proventil' AND P1.Jméno = P2.Jméno Klauzule VALID Pro každý řádek dostáváme ve výsledku čas platnosti, který je implicitně vypočítán jako průnik času platnosti relací uvedených v klauzuli FROM Toto implicitní chování lze změnit klauzulí VALID SELECT Lék VALID INTERSECT(VALID(Předpisy), PERIOD '[1996]' DAY) FROM Předpisy WHERE Jméno = 'Melanie' Spolu s kterými léky byl užíván Proventil? Jaké léky užívala Melanie během roku 1996?

16. INSERT INTO Předpisy VALUES ('Melanie', 'Dr. Beren', 'Proventil', '100mg', INTERVAL '8:00' MINUTE) VALID PERIOD '[1996-01-01 - 1996-06-30]' INSERT INSERT INTO Předpisy VALUES ('Melanie', 'Dr. Beren', 'Proventil', '100mg', INTERVAL '8:00' MINUTE) Vložení řádku se známou dobou platnosti Implicitní doba platnosti je VALID PERIOD(CURRENT_TIMESTAMP, NOBIND(CURRENT_TIMESTAMP))

17. DELETE Zrušení předpisů pro Melanie z června 1996 DELETE FROMPředpisy WHERE Jméno = 'Melanie' VALID PERIOD '[1996-06-01 – 1996-06-30]' Řádkám, jejichž doba platnosti zasahuje do června pouze částečně, je doba platnosti zkrácena

18. UPDATE Předpisy SET Dosage TO '50 mg' VALID PERIOD '[1996-06-01 – 1996-05-30]' WHERE Jméno = 'Melanie' AND Lék = 'Proventil' UPDATE Změna dávkování Proventilu na 50 mg UPDATEPředpisy SETDávkaTO '50 mg' WHERE Jméno = 'Melanie' AND Lék = 'Proventil' Ovlivněny jsou pouze záznamy týkající se přítomnosti a budoucnosti Změna dávkování Proventilu na 50 mg od března do května

19. Event Relations Relace událostí obsahují ke každému řádku množinu časových razítek CREATE TBABLE Test (Jméno CHAR(30), Lékař CHAR(30), TestID INTEGER) AS VALID EVENT HOUR AND TRANSACTION

20. Ještě jednou restrukturalizace Který lékař objednával testy jedinému pacientovi? (a naopak také požadujeme, aby všechny testy pacienta prováděl stejný lékař) SELECT L1.Jméno, L2.Lékař FROM Test(Jméno) AS L1, L1(Lékař) AS L2, Test(Lékař) AS L3 WHERE VALID(L1) = VALID(L2) AND L2.Lékař = L3.Lékař AND VALID(L1) = VALID(L3)

21. SELECT Lék FROM Předpisy WHERE Jméno = 'Melanie' AND TRANSACTION(P) OVERLAPS DATE '1996-06-01' Podpora transakčního času Historie předepsaných léků pro Melanie SELECT Lék FROM Předpisy WHERE Jméno = 'Melanie' Historie předepsaných léků pro Melanie aktuální 1. června 1996 Implicitně:TRANSACTION(P) OVERLAPS CURRENT_TIMESTAMP

22. SELECT SNAPSHOT BEGIN(TRANSACTION(P2)) FROM Předpisy AS P1, P2 WHERE P1.Jméno = 'Melanie' AND P2.Jméno = 'Melanie' AND VALID(P1) OVERLAPS DATE '1996-06-01' AND VALID(P2) OVERLAPS DATE '1996-06-01' ANDTRANSACTION(P1) MEETSTRANSACTION(P2) Podpora transakčního času Kdy byla změněna informace o lécích předepsaných na 1.červen 1996? VALID(P1) OVERLAPS DATE '1996-06-01' – vybere pouze řádky, kde čas platnosti obsahuje den 1.6.1996 TRANSACTION(P1) MEETSTRANSACTION(P2) – řádek asociovaný s P1 byl opraven řádkem asociovaným s P2

23. SELECT COUNT(*) FROM Předpisy WHERE Jméno = 'Melanie' SELECT Lék, COUNT(*) FROM Předpisy GROUP BY Lék Agregační funkce Na temporální relace lze používat agregační funkce známé z SQL-92: MIN, MAX, COUNT, SUM, AVG Počet předpisů pro Melanie Počet předpisů pro jednotlivé léky

24. ALTER TABLE Předpisy ADD COLUMN Identifier INTEGER SET SCHEME DATE '1996-08-19' Vývoj schématu SQL dovoluje změnit schéma pomocí příkazu ALTER – původní schéma je ztraceno v TSQL2 se uchovají obě dvě verze schématu (pouze u relací s podporou transakčního času) data s podporou transakčního času nemohou být modifikována, lze pouze přidávat nová data TSQL2 podporuje proces zvaný vacuuming, který zajišťuje fyzické odstranění dat Přidání sloupce do tabulky Aplikace může pracovat se starším schématem, pro které byla vytvořena

25. Shrnutí • TSQL umožňuje vytvořit 6 druhů tabulek s různou podporou času platnosti a transakčního času • „obyčejnou“ relaci z relace podporující čas platnosti můžeme získat konstrukcí SELECT SNAPSHOT • Restrukturalizace umožňuje provést projekci na některé sloupce, na výsledku této projekce dochází k srůstání intervalů času platnosti, restrukturalizace se týká klauzule FROM, na výsledku dochází k srůstání intervalů automaticky • Partitioning je operace, která vytvoří zvláštní řádek pro každý (maximální) interval doby platnosti, v klauzuli FROM uvedeme (PERIOD) • V klauzuli WHERE lze specifikovat čas platnosti i transakční čas • Relace událostí přiřazují každému řádku množinu časových razítek