10. Dostososowywanie bazy danych - typy danych

Aby dostosować bazę danych do wykorzystania typów danych takich jak "hierarchy id", "współrzędne geograficzne (geography)", "geometry" oraz "dane XML", możemy zmodyfikować strukturę niektórych tabel oraz dodać nowe typy kolumn do przechowywania tych danych.

1. Renewable_Energy_Devices

CREATE TABLE Renewable_Energy_Devices (
    device_id NVARCHAR(26) PRIMARY KEY,
    device_status INT CHECK (device_status >= 0 AND device_status <= 6),
    device_power DECIMAL(18,0),
    company_id INT,
    device_location GEOGRAPHY, -- Nowa kolumna przechowująca współrzędne geograficzne urządzenia
    device_hierarchy HIERARCHYID -- Nowa kolumna przechowująca hierarchię urządzeń
);

Korzyści: Wykorzystanie typu danych "geography" umożliwi przechowywanie i operowanie współrzędnymi geograficznymi urządzeń, co może być przydatne np. w systemach monitoringu czy nawigacji. Z kolei typ danych "hierarchy id" pozwoli na hierarchiczne organizowanie urządzeń, co może być przydatne w przypadku drzewiastej struktury urządzeń, np. podłączonego do sieci energetycznej.

Korzystanie z powyższych typów danych umożliwia bardziej zaawansowane operacje na danych, takie jak obliczenia odległości między współrzędnymi geograficznymi, manipulowanie hierarchią danych, tworzenie zaawansowanych zapytań przestrzennych itp. Dzięki temu możemy tworzyć bardziej zaawansowane i wydajne aplikacje, które korzystają z danych geoprzestrzennych oraz hierarchicznych struktur danych.


Dla wykorzystania typu danych "geometry":

Przykładowa modyfikacja tabeli urządzeń OZE:

-- Usunięcie istniejącej tabeli
DROP TABLE IF EXISTS Renewable_Energy_Devices;

-- Tworzenie nowej tabeli urządzeń OZE z kolumną typu geometry
CREATE TABLE Renewable_Energy_Devices (
    device_id NVARCHAR(26) PRIMARY KEY,
    device_status INT CHECK (device_status >= 0 AND device_status <= 6),
	device_power DECIMAL(18,0),
	company_id INT,
    device_location GEOMETRY
);

W tej modyfikacji dodaliśmy kolumnę device_location o typie danych "geometry", która może przechowywać informacje o położeniu geograficznym urządzenia.


Dla wykorzystania typu danych "xml":

Przykładowa modyfikacja tabeli napraw:

-- Usunięcie istniejącej tabeli
DROP TABLE IF EXISTS Repairs;

-- Tworzenie nowej tabeli napraw z kolumną typu XML dla opisu naprawy
CREATE TABLE Repairs (
    repair_id INT PRIMARY KEY IDENTITY,
    device_id NVARCHAR(26),
    repair_start DATETIME,
    repair_end DATETIME,
    repair_description XML, -- Zmiana typu danych na XML
    FOREIGN KEY (device_id) REFERENCES Renewable_Energy_Devices(device_id)
);

W tej modyfikacji zmieniamy typ danych kolumny repair_description na "XML", co pozwoli przechowywać opis naprawy w formacie XML.

Korzyści z zastosowania tych typów danych:
Typ "geometry": Pozwala przechowywać informacje o położeniu geograficznym urządzenia w sposób bardziej precyzyjny i elastyczny. Możemy wykonywać operacje geograficzne, takie jak obliczanie odległości między urządzeniami, znajdowanie urządzeń w określonej odległości od punktu odniesienia itp.
Typ "XML": Umożliwia przechowywanie strukturalnych danych w formacie XML, co ułatwia analizę, przetwarzanie i wymianę danych. Możemy wykorzystać funkcje i metody wbudowane w język SQL do manipulowania danymi XML, takie jak zapytania XPath, wstawianie, aktualizowanie i usuwanie danych itp. Dodatkowo, XML jest formatem powszechnie używanym w integracji systemów, co ułatwia wymianę danych między różnymi aplikacjami i platformami.


Przykład zapytania i możliwości typu danych "xml":

-- Wstawianie danych z formatem XML do kolumny
INSERT INTO Repairs (device_id, repair_start, repair_end, repair_description)
VALUES ('device001', '2024-04-15 08:00:00', '2024-04-15 10:00:00',
        '<repair>
            <issue>Broken component</issue>
            <actions>
                <action>Replaced the component</action>
                <action>Tested functionality</action>
            </actions>
         </repair>');

-- Zapytanie wyświetlające opisy napraw w formacie XML
SELECT repair_description FROM Repairs;

-- Zapytanie z wykorzystaniem funkcji XQuery do wyodrębnienia danych z XML
SELECT
    repair_id,
    device_id,
    repair_start,
    repair_end,
    repair_description.value('(repair/issue/text())[1]', 'nvarchar(100)') AS issue,
    repair_description.value('(repair/actions/action)[1]', 'nvarchar(100)') AS action1,
    repair_description.value('(repair/actions/action)[2]', 'nvarchar(100)') AS action2
FROM Repairs;

W tym przykładzie dodano opis naprawy w formacie XML i wykorzystano funkcję value() wraz z zapytaniem XQuery, aby wyodrębnić konkretne informacje z XML.
Dzięki typowi danych "xml" możemy przechowywać strukturalne dane i wykonywać operacje na nich, takie jak wyszukiwanie, filtrowanie i przetwarzanie.




11. Kolumnę typu XML, możemy wykorzystać w zapytaniach przy użyciu polecenia FOR XML:

SELECT
    device_id,
    device_status,
    device_power,
    company_id,
    device_data_xml
FROM
    Renewable_Energy_Devices
FOR XML AUTO, ELEMENTS, ROOT('RenewableEnergyDevices');

To zapytanie zwróci wszystkie urządzenia OZE w formie XML, gdzie każde urządzenie będzie reprezentowane jako osobny element <RenewableEnergyDevice>. Każde pole w rekordzie będzie reprezentowane jako atrybut lub element wewnątrz tego elementu.