TSQL2
The Temporal Structured Query Language
Jaroslav Ciml
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
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 (transactiontime)
Bitemporální relace – příklad (1)
Zaměstnanci (Jméno, Oddělení)
Bitemporální relace – příklad (2)
Jméno
Jake
Jake
Oddělení
Nakládka
Vývoz
Bitemporální chronony
{(5, 10),…,(5,15),…(19, 10),…,(19, 15)}
{(U.C., 10),…,(U.C., 15)}
Definice schématu
CREATE TABLE Předpisy (Jméno CHAR(30),
Lékař CHAR(30), Lék CHAR(30),
Dávka CHAR(30), DobaUžívání INTERVAL MINUTE)
AS VALID STATE DAY AND TRANSACTION
VT – čas, kdy je lék užíván
TT – čas, kdy se informace dostala do databáze
6 typů tabulek
●
●
●
Snapshot relation – nemá podporu času platnosti
ani transakčního času
AS VALID nebo AS 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
SELECT (1)
Kdo užívá (užíval) léky?
SELECT SNAPSHOT Jméno
FROM Předpisy
Kdo užívá (užíval) Proventil?
SELECT SNAPSHOT Jméno
FROM Předpisy
WHERE Lék = 'Proventil'
Kdo užívá (užíval) léky a kdy?
SELECT Jméno
FROM Předpisy
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ě
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
klauzuli FROM
Kdo užíval stejný lék (celkově) déle než 6 měsíců?
SELECT Jméno, Lék
FROM Předpisy(Jméno, Lék) AS P
WHERE CAST(VALID(P) AS INTERVAL MONTH)
> INTERVAL '6' MONTH
Konstrukce VALID(P) vrací pro každý řádek relace P dobu platnosti
(množinu maximálních časových úseků)
Operátor CAST provede konverzi na typ INTERVAL MONTH tím, že
sečte časové úseky vrácené pomocí VALID(P)
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
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ž
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
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...
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
Kdo užíval stejný lék (celkově) déle než 6 měsíců?
SELECT Jméno, Lék
FROM Předpisy(Jméno, Lék) AS P
WHERE CAST(VALID(P) AS INTERVAL MONTH)
> INTERVAL '6' MONTH
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
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
Jaké léky užívala Melanie během roku 1996?
SELECT Lék
VALID INTERSECT(VALID(Předpisy), PERIOD '[1996]' DAY)
FROM Předpisy
WHERE Jméno = 'Melanie'
INSERT
INSERT INTO Předpisy
VALUES ('Melanie', 'Dr. Beren', 'Proventil', '100mg',
INTERVAL '8:00' MINUTE)
Vložení řádku se známou dobou platnosti
INSERT INTO Předpisy
VALUES ('Melanie', 'Dr. Beren', 'Proventil', '100mg',
INTERVAL '8:00' MINUTE)
VALID PERIOD '[1996-01-01 - 1996-06-30]'
Implicitní doba platnosti je
VALID PERIOD(CURRENT_TIMESTAMP,
NOBIND(CURRENT_TIMESTAMP))
DELETE
Zrušení předpisů pro Melanie z června 1996
DELETE FROM Př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
UPDATE
Změna dávkování Proventilu na 50 mg
UPDATE Předpisy
SET Dávka TO '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
UPDATE Předpisy
SET Dosage TO '50 mg'
VALID PERIOD '[1996-06-01 – 1996-05-30]'
WHERE Jméno = 'Melanie' AND Lék = 'Proventil'
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
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)
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
SELECT Lék
FROM Předpisy
WHERE Jméno = 'Melanie'
AND TRANSACTION(P) OVERLAPS DATE '1996-06-01'
Implicitně: TRANSACTION(P) OVERLAPS
CURRENT_TIMESTAMP
Podpora transakčního času
Kdy byla změněna informace o lécích předepsaných
na 1.červen 1996?
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'
AND TRANSACTION(P1) MEETS TRANSACTION(P2)
VALID(P1) OVERLAPS DATE '1996-06-01' – vybere pouze
řádky, kde čas platnosti obsahuje den 1.6.1996
TRANSACTION(P1) MEETS TRANSACTION(P2) – řádek
asociovaný s P1 byl opraven řádkem asociovaným s P2
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
SELECT COUNT(*)
FROM Předpisy
WHERE Jméno = 'Melanie'
Počet předpisů pro jednotlivé léky
SELECT Lék, COUNT(*)
FROM Předpisy
GROUP BY Lék
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
ALTER TABLE Předpisy
ADD COLUMN Identifier INTEGER
Aplikace může pracovat se starším schématem, pro které byla vytvořena
SET SCHEME DATE '1996-08-19'
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
Descargar

TSQL2 The Temporal Structured Query Languge