Prostorové databáze - cvičení

Požadavky na zápočet

Zápočet (33 bodů) je rozdělen následujícím způsobem:

1. Písemný test - 9 bodů - v průběhu semestru
2. Semestrální projekt - úlohy s prostorovými operátory - 24 bodů

Zadání zápočtového projektu je uvedeno na stránce s popisem úkolů.

Cvičení 1 - Instalace prostorové databáze a vizualizačního nástroje

Instalace image v prostředí Virtual PC, XE server, SQLDeveloper, GeoRaptor

Cvičení 2 - Přístupy k databázi, prostředí klienta

"Zprovoznění" uživatele HR, vyzkoušení přístupů k databázi - cmd, APEX, SQLDeveloper

Tutoriál SQLDeveloper

Tvorba tabulky

Příkazem jazyka DDL pro vytvoření nové tabulky je CREATE TABLE. V příkazu pojmenujeme tabulku (dle významu obashu dat, předchozí analýzy), její sloupce (atributy), ke kterým nadefinujeme datové typy.

Syntaxe příkazu CREATE TABLE:

CREATE TABLE [nazev_databaze.]nazev_tabulky
   (<nazev sloupce> <datovy typ>
      [DEFAULT <konstantni vyraz>]
      [NULL | NOT NULL]
      [<omezeni pro sloupec>]
      |[<omezeni pro tabulku>]
      [,...n]
   )

V [] jsou uvedeny nepovinné údaje - například [nazev_databaze.]. Za () je uvedena povinná část <nazev sloupce> <datovy typ> s možným opakováním až n-krát [,...n].

Datové typy

Kompletní popis datových typů je možno nalézt v dokumentaci Oracle.

Cvičení 3 - SQL I.

Dotazování nad jednou tabulkou

Structured Query Language (SQL) představuje sadu příkazů, které umožňují pracovat s databází. Základem jazyka SQL je příkaz SELECT. Popis jeho plné notace je uveden na stránce Vyhledávání.

Opakování budeme provádět nad databází HR, ke které jsme získali přístup aktivací uživatele hr. Schema databáze je uvedeno na obrázku Schema HR.

Nejjednodušší forma dotazu - získáme obsah celé tabulky Zamestnanci:

SELECT * FROM employees;

V ORACLE se nerozlišuje psaní malými a velkými písmeny, takže název tabulky i klíčová slova lze psát různým způsobem - employees x EMPLOYEES, select x SELECT.

Výběr některých sloupců dosáhneme použitím projekce, tj. uvedením názvů sloupců za klíčové slovo SELECT. Zde je uveden dotaz, jehož výsledkem je jmenný seznam zaměstnanců:

SELECT FIRST_NAME, LAST_NAME FROM employees; 

V případě, že bychom chtěli pouze seznam příjmení všech zaměstanců bez opakování stejných jmen, použijeme klíčové slovo DISTINCT, které eliminuje výstup - bude bez opakování stejných řádků:

SELECT DISTINCT LAST_NAME FROM employees;

Budeme-li chtít ve výsledku změnit číselné hodnoty nějakého sloupce, lze je přímo dopočítat a na výstupu sloupec také přejmenovat pomocí tzv. alias, za slovem AS:

SELECT LAST_NAME, SALARY + 100 AS "Vyplata s odmenou" FROM employees;

Pro výběr záznamů z tabulky, které vyhovují určité podmínce, použijeme operaci selekce. Zapisujeme ji za klíčové slovo WHERE a podle datového typu atributu volíme zápis podmínky. Pro jmenný seznam zaměstnanců z oddělení 80 pak příkaz vypadá následně:

SELECT FIRST_NAME, LAST_NAME FROM employees WHERE DEPARTMENT_ID=80;

V podmínce je možno použít operátory:

=   rovná se                        AND       a současně
<   je menší než                    OR        nebo
>   je větší než                    NOT       negace
<=  je menší nebo rovno             BETWEEN   pro intervalové hodnoty
>=  je větší nebo rovno             IS (NOT)  ne/shoda s hodnotou NULL
<>  není rovno (dle standardu ISO)  LIKE      shoda s maskou v řetězci

Například vyhledání všech zaměstanců, jejichž jméno začíná na K využije operátor LIKE pro porovnání s maskou řetězce a navíc zástupný znak % za jakékoli jiné znaky (i žádný znak).

SELECT LAST_NAME AS "Zamestnanci s příjemním na 'K'" FROM employees WHERE last_name LIKE 'K%' ;

Pro vyhledání zaměstnanců, kteří nejsou přiděleni k žádnému oddělení, použijeme podmínku, kde zjistíme hodnotu NULL u atributu DEPARTMENT_ID.

SELECT FIRST_NAME, LAST_NAME FROM employees WHERE DEPARTMENT_ID IS NULL;

Budeme-li chtít získat jmenný seznam zaměstnanců oddělení 80, využijeme možnosti setřídít výsledek podle příjmení, případně i podle dvou atributů současně, nejprve dle příjmení a pak křestního jména:

SELECT FIRST_NAME, LAST_NAME FROM employees WHERE DEPARTMENT_ID=80 ORDER BY LAST_NAME;

SELECT FIRST_NAME, LAST_NAME FROM employees WHERE DEPARTMENT_ID=80 ORDER BY LAST_NAME, FIRST_NAME;

Agregační funkce

Agregační funkce představují možnost sloučit záznamy či hodnoty do skupin a nad nimi provézt aritmetickou či statistickou operaci. Výsledkem jsou například počty záznamů (COUNT), součet hodnot (SUM), maximální hodnota (MAX) ze sady numerických hodnot apod. Seznam a význam agregačních funkcí:

COUNT(*) navrací počet počet řádků ve výsledku dotazu
COUNT(DISTINCT)  počet jedinečných výskytů hodnot 
SUM()            součet numerických hodnot ze zadaného sloupce/pole hodnot
AVG()            aritmetický průměr z numerických platných hodnot 
MIN()            minimální hodnotu z numerických platných hodnot 
MAX()            maximální hodnota z numerických platných hodnot
STD()            směrodatnou odchylku
GROUP BY sloučí záznamy se stejnými hodnotami určeného atributu a vytvoří souhrnnou hodnotu

Chceme-li zjistit počet všech zaměstnanců, použijeme operaci COUNT:

SELECT COUNT(*) FROM employees;

Chceme-li zjistit kolik zaměstnanců dostává provizi, použijeme operaci COUNT s názvem sloupce:

SELECT COUNT(commission_pct) FROM employees;

Počet je jiný, protože ve sloupci commission_pct jsou i hodnoty NULL, které funkce nezapočítává.

Jaký je nejvyšší plat v oddělení 80? SELECT MAX(salary) FROM employees WHERE DEPARTMENT_ID=80;

Agregační funkce nelze použít v podmínce za WHERE, ale je možné je použít při seskupování za klauzulí GROUP BY. Chceme-li zjistit minimální platy v každém oddělení, seskupíme palty podle oddělení a v každé skupině je pak zjištěno minimum:

SELECT MIN(salary), department_id FROM employees GROUP BY department_id;

Samostatný úkol

V prostředí SQLDevelopera sestavte dotazy, kterými si vyzkoušíte své znalosti. Zadání dotazů je uvedeno na Opakování SQL. Výsledky své práce ukažte pedagogovi.

Cvičení 4 - SQL II

Dotazování nad více tabulkami

Cvičení 5 - Principy práce s prostorovými daty

Princip práce s prostorovými daty v prostředí SŘBD Oracle.

SDO_Geometry

Databázový server Oracle je rozšířen o objektový prostorový datový typ s názvem SDO_GEOMETRY. Prefix SDO pochází z doby, kdy Oracle spolupracoval s Canadian Hydrographic Service a tehdejší změny v jádru Oracle vedly k označení "Spatial Data Option" (SDO). Jedním z atributů tohoto objektu je geometrie prostorového prvku, která může být tvořena základním geometrickým elementem - bodem, úsečkou, křivkou, kruhem nebo polygonem, případně jejich kombinacemi. V dané tabulce je možné mít neomezený počet sloupců s prostorovými daty.

Oracle Spatial definuje objektový typ SDO_GEOMETRY s následujícími atributy:

CREATE TYPE sdo_geometry AS OBJECT (
 SDO_GTYPE NUMBER, 
 SDO_SRID NUMBER,
 SDO_POINT SDO_POINT_TYPE,
 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
 SDO_ORDINATES SDO_ORDINATE_ARRAY);

Struktura a základní metody u objektu SDO_Geometry je vidět na třídním diagramu .

Je nutné vnímat "skladbu" tohoto objektu. Při zadávání dat potřebujeme znát význam atributů, které určují geometrii či koordinační (souřadnicový) systém. Dále jsou definovány datové typy SDO_POINT_TYPE, SDO_ELEM_INFO_ARRAY, SDO_ORDINATE_ARRAY použité v definici SDO_GEOMETRY:

CREATE TYPE sdo_point_type AS OBJECT (
  X NUMBER,
  Y NUMBER,
  Z NUMBER);

CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;

CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;

Příklad vytvoření tabulky s prostorovými daty, včetně tvorby prostorového indexu: příklad Parcely

Cvičení 10 - Zpracování vlastního projektu

---

Výuková podpora pro cvičení z předmětu Objektově orientované technologie vznikla za podpory projektu Inovace bakalářských a magisterských studijních oborů na Hornicko-geologické fakultě VŠB-TUO pod číslem CZ.1.07/2.2.00/28.0308. Tento projekt je realizován za spoluúčasti EU.