Kod SQL tworzący kompletną strukturę danych wykorzystywaną w projekcie wygląda następująco:

CREATE DATABASE "INDECT"
  WITH OWNER = "postgres"
    ENCODING = 'UTF8';

CREATE TABLE "public"."Uzytkownicy" (
  "id_uzytkownicy" SERIAL, 
  "id_sluzby_porzadkowe" INTEGER, 
  "id_typy_uzytkownika" INTEGER, 
  "id_panstwa" INTEGER, 
  "imie" VARCHAR(20), 
  "nazwisko" VARCHAR(30), 
  "rok_urodzenia" INTEGER, 
  "email" VARCHAR(30), 
  "haslo" VARCHAR(50), 
  "data_rejestracji" DATE, 
  "data_ostatniego_logowania" DATE, 
  "miasto" VARCHAR(30), 
  "ulica" VARCHAR(50), 
  "wsp_zaufania" INTEGER, 
  "ulubiony_dlugosc" DOUBLE PRECISION,
  "ulubiony_szerokosc" DOUBLE PRECISION,
  "ulubiony_zoom" DOUBLE PRECISION,
  CONSTRAINT "Uzytkownicy_pkey" PRIMARY KEY("id_uzytkownika")
) WITH OIDS;

CREATE TABLE "public"."Panstwa" (
  "id_panstwa" SERIAL NOT NULL, 
  "nazwa" VARCHAR(30), 
  PRIMARY KEY("id_panstwa")
) WITH OIDS;

ALTER TABLE "public"."Uzytkownicy"
  ADD CONSTRAINT "Uzytkownicy_fk" FOREIGN KEY ("id_panstwa")
    REFERENCES "public"."Panstwa"("id_panstwa")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

CREATE TABLE "public"."Typy_uzytkownika" (
  "id_typy_uzytkownika" SERIAL, 
  "nazwa" VARCHAR(30), 
  CONSTRAINT "Typy_uzytkownika_pkey" PRIMARY KEY("id_typy_uzytkownika")
) WITH OIDS;

ALTER TABLE "public"."Uzytkownicy"
  ADD CONSTRAINT "Uzytkownicy_fk1" FOREIGN KEY ("id_typy_uzytkownika")
    REFERENCES "public"."Typy_uzytkownika"("id_typy_uzytkownika")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

CREATE TABLE "public"."Opinie" (
  "id_opinie" SERIAL NOT NULL, 
  "id_zdarzenia" INTEGER, 
  "id_uzytkownicy" INTEGER, 
  "data_dodania" DATE, 
  "ocena" INTEGER, 
  "komentarz" VARCHAR(1000), 
  "zgloszenie_naduzycia" BOOLEAN DEFAULT FALSE, 
  PRIMARY KEY("id_opinie")
) WITH OIDS;

ALTER TABLE "public"."Opinie"
  ADD CONSTRAINT "Opinie_fk" FOREIGN KEY ("id_uzytkownicy")
    REFERENCES "public"."Uzytkownicy"("id_uzytkownicy")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

CREATE TABLE "public"."Zdarzenia" (
  "id_zdarzenia" SERIAL NOT NULL, 
  "id_nazwy_zdarzen" INTEGER, 
  "id_uzytkownicy" INTEGER, 
  "szerokosc_geograficzna" DOUBLE PRECISION, 
  "dlugosc_geograficzna" DOUBLE PRECISION, 
  "data_dodania" DATE, 
  "opis" VARCHAR(500), 
  "adres_zdjecia" VARCHAR(200), 
  "stan" INTEGER, 
  "waznosc" INTEGER, 
  PRIMARY KEY("id_zdarzenia")
) WITH OIDS;

ALTER TABLE "public"."Opinie"
  ADD CONSTRAINT "Opinie_fk1" FOREIGN KEY ("id_zdarzenia")
    REFERENCES "public"."Zdarzenia"("id_zdarzenia")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

ALTER TABLE "public"."Zdarzenia"
  ADD CONSTRAINT "Zdarzenia_fk" FOREIGN KEY ("id_uzytkownicy")
    REFERENCES "public"."Uzytkownicy"("id_uzytkownicy")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

CREATE TABLE "public"."Sluzby_porzadkowe" (
  "id_sluzby_porzadkowe" SERIAL NOT NULL, 
  "nazwa" VARCHAR(30), 
  PRIMARY KEY("id_sluzby_porzadkowe")
) WITH OIDS;

ALTER TABLE "public"."Uzytkownicy"
  ADD CONSTRAINT "Uzytkownicy_fk2" FOREIGN KEY ("id_sluzby_porzadkowe")
    REFERENCES "public"."Sluzby_porzadkowe"("id_sluzby_porzadkowe")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

CREATE TABLE "public"."Nazwy_zdarzen" (
  "id_nazwy_zdarzen" SERIAL NOT NULL, 
  "id_sluzby_porzadkowe" INTEGER, 
  "nazwa" VARCHAR(40), 
  PRIMARY KEY("id_nazwy_zdarzen")
) WITH OIDS;

ALTER TABLE "public"."Nazwy_zdarzen"
  ADD CONSTRAINT "Nazwy_zdarzen_fk" FOREIGN KEY ("id_sluzby_porzadkowe")
    REFERENCES "public"."Sluzby_porzadkowe"("id_sluzby_porzadkowe")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

ALTER TABLE "public"."Zdarzenia"
  ADD CONSTRAINT "Zdarzenia_fk1" FOREIGN KEY ("id_nazwy_zdarzen")
    REFERENCES "public"."Nazwy_zdarzen"("id_nazwy_zdarzen")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

CREATE TABLE "public"."Definicje_zdarzen" (
  "id_definicje_zdarzen" SERIAL NOT NULL, 
  "id_nazwy_zdarzen" INTEGER, 
  "id_kategorie_zdarzen" INTEGER, 
  PRIMARY KEY("id_definicje_zdarzen")
) WITH OIDS;

ALTER TABLE "public"."Definicje_zdarzen"
  ADD CONSTRAINT "Definicje_zdarzen_fk" FOREIGN KEY ("id_nazwy_zdarzen")
    REFERENCES "public"."Nazwy_zdarzen"("id_nazwy_zdarzen")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

CREATE TABLE "public"."Kategorie_zdarzen" (
  "id_kategorie_zdarzen" SERIAL NOT NULL, 
  "id_typy_kategorii_zdarzen" INTEGER, 
  "nazwa" VARCHAR(40), 
  PRIMARY KEY("id_kategorie_zdarzen")
) WITH OIDS;

ALTER TABLE "public"."Definicje_zdarzen"
  ADD CONSTRAINT "Definicje_zdarzen_fk1" FOREIGN KEY ("id_kategorie_zdarzen")
    REFERENCES "public"."Kategorie_zdarzen"("id_kategorie_zdarzen")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

CREATE TABLE "public"."Typy_kategorii_zdarzen" (
  "id_typy_kategorii_zdarzen" SERIAL, 
  "nazwa" VARCHAR(40), 
  PRIMARY KEY("id_typy_kategorii_zdarzen")
) WITH OIDS;

ALTER TABLE "public"."Kategorie_zdarzen"
  ADD CONSTRAINT "Kategorie_zdarzen_fk" FOREIGN KEY ("id_typy_kategorii_zdarzen")
    REFERENCES "public"."Typy_kategorii_zdarzen"("id_typy_kategorii_zdarzen")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

Powyższy kod tworzy strukturę bazy danych zgodną z opisem zawartym w części konceptualnej dokumentacji.

W projekcie znajduje się pięć tabel słownikowych.

• Panstwa

W tabeli znajdują się nazwy państw. Struktura jest wykorzystywana przy gromadzeniu danych o użytkowniku - eliminuje możliwość podania błędnego państwa w formularzu.

• Typy_uzytkownika

Tabela przechowuje dane dotyczące rodzajów użytkowników.

• Sluzby_porzadkowe

Tabela zawiera informacje o typach służb porządkowych. Pozwala na zdefiniowanie służb, które będą korzystać z systemu.

• Typy_kategorii_zdarzen

Tabela definiuje nazwy, które odpowiadają typom kategorii zdarzeń, czyli „kategoriom kategorii”.

• Kategorie_zdarzen

Tabela przechowuje dane o kategoriach zdarzeń. Struktura jest powiązana z tabelą Typy_kategorii_zdarzen.

Teoria normalizacji baz danych definiuje następujące postacie normalne:

• 1NF

Relacja jest w pierwszej postaci normalnej wtedy i tylko wtedy gdy wszystkie dane są atomiczne. Jest to niezbędne dla rachunku relacyjnego oraz języków zapytań.

• 2NF

Relacja jest w drugiej postaci normalnej wtedy i tylko wtedy gdy jest w pierwszej postaci normalnej oraz wszystkie atrybuty niekluczowe są w pełni funkcjonalnie zależne od wszystkich kluczy. Naruszenie 2NF polega na istnieniu zależności funkcyjnej od fragmentu klucza właściwego.

• 3NF

Relacja jest w trzeciej postaci normalnej wtedy i tylko wtedy gdy jest w drugiej postaci normalnej oraz wszystkie atrybuty niekluczowe są bezpośrednio zależne od wszystkich kluczy. Możliwe naruszenie 3NF może być spowodowane istnieniem zależności tranzytywnej od klucza właściwego.

• BCNF

Relacja jest w postaci normalnej Boyce’a-Codda wtedy i tylkow wtedy gdy wszystkie zależności kluczowe są zależnościami od kluczy. W BCNF nie istnieją zależności tranzytywne. Sprowadzenie schematu do postaci BCNF z zachowaniem zależności nie zawsze jest możliwe. Często brak spełnienia normy BCNF nie jest istotny z punktu widzenia projektu.

• 4NF

Relacja jest w czwartej postaci normalnej jeśli każda nietrywialna zależność wielowartościowa jest zależnością od klucza. Relacja w czwartej postaci normalnej musi również spełniać kryteria normy Boyce’a-Codda.

Wszystkie tabele bazy danych projektu spełniają co najmniej normę 3NF. Co więcej, niektóre tabele spełniają normy BCNF oraz 4NF.

W projekcie nie przewidujemy denormalizacji.

Poniżej zostaną zamieszczone zapytania do bazy danych w języku SQL wykorzystywane w projekcie. Zapytania wysyłane są z poziomu języka Java, wyniki zapytań obsługiwane, a następnie przesyłane do warstwy prezentacji aplikacji.

• Dodawanie do bazy nowego użytkownika.

INSERT INTO Uzytkownicy ( id_typy_uzytkownika, id_panstwa, imie, nazwisko, rok_urodzenia, email, haslo, data_rejestracji,
data_ostatniego_logowania, misto, ulica, wsp_zaufania, ulubiony_dlugosc, ulubiony_szerokosc, ulubiony_zoom) 
VALUES(‘pmIdTypUzytkownika’, ‘pmIdPanstwa’, ‘pmImie’, ‘pmNazwisko’, ‘pmRokUrodzenia’, ‘pmEmail’, ‘pmHaslo’, date(„Y-m-d”), 
date(„Y-m-d”), ‘pmMiasto’, ‘pmUlica’, ‘30’, ‘pmUlubionyDlugosc’, ‘pmUlubionySzerokosc’, ‘pmUlubionyZoom)

• Pobieranie listy służb specjalnych.

SELECT UZ.id_uzytkownicy, UZ.id_typy_uzytkownika, UZ.id_panstwa, Uzytkownicy.imie, UZ.nazwisko, UZ.rok_urodzenia, UZ.email, 
UZ.haslo, UZ.data_rejestracji, UZ.data_ostatniego_logowania, UZ.misto, UZ.ulica, UZ.ulubiony_dlugosc, UZ.ulubiony_szerokosc, 
UZ.ulubiony_zoom, SP.nazwa FROM Uzytkownicy UZ
LEFT OUTER JOIN Słuzby_porzadkowe SP ON UZ.id_sluzby_porzadkowe = SP. id_sluzby_porzadkowe
WHERE UZ.id_sluzby_porzadkowe IS NOT NULL;

• Pobieranie listy służb specjalnych.

SELECT UZ.id_uzytkownicy, UZ.id_typy_uzytkownika, UZ.id_panstwa, Uzytkownicy.imie, UZ.nazwisko, UZ.rok_urodzenia, UZ.email, 
UZ.haslo, UZ.data_rejestracji, UZ.data_ostatniego_logowania, UZ.misto, UZ.ulica, UZ.ulubiony_dlugosc, UZ.ulubiony_szerokosc, 
UZ.ulubiony_zoom, SP.nazwa FROM Uzytkownicy UZ
LEFT OUTER JOIN Słuzby_porzadkowe SP ON UZ.id_sluzby_porzadkowe = SP. id_sluzby_porzadkowe
WHERE UZ.id_sluzby_porzadkowe IS NOT NULL;

• Edycja danych uzytkownika.

UPDATE Uzytkownicy SET id_sluzby_porzadkowe=’pmSluzbyPorzadkowe’, id_panstwa=’pmIdPanstwa’, imie=’pmImie’, nazwisko=’pmNazwisko’, 
rok_urodzenia=’pmRokUrodzenia’, miasto=’pmMiasto’, ulica=’pmUlica’ WHERE id_uzytkownicy=’pmIdUzytkownicy’;

• Pobieranie listy służb specjalnych.

SELECT UZ.id_uzytkownicy, UZ.id_typy_uzytkownika, UZ.id_panstwa, Uzytkownicy.imie, UZ.nazwisko, UZ.rok_urodzenia, UZ.email, 
UZ.haslo, UZ.data_rejestracji, UZ.data_ostatniego_logowania, UZ.misto, UZ.ulica, UZ.ulubiony_dlugosc, UZ.ulubiony_szerokosc, 
UZ.ulubiony_zoom, SP.nazwa FROM Uzytkownicy UZ
LEFT OUTER JOIN Słuzby_porzadkowe SP ON UZ.id_sluzby_porzadkowe = SP. id_sluzby_porzadkowe
WHERE UZ.id_sluzby_porzadkowe IS NOT NULL;

• Zmiana hasła.

UPDATE Uzytkownicy SET haslo=’pmHaslo’ WHERE id_uzytkownicy=’pmIdUzytkownicy’;

• Pobranie wszystkich danych użytkownika.

SELECT * FROM Uzytkownicy WHERE id_uzytkownicy=’pmIdUzytkownicy’;

• Pobranie wszystkich danych użytkowników.

SELECT * FROM Uzytkownicy

• Pobranie listy służb specjalnych.

SELECT UZ.id_uzytkownicy, UZ.id_typy_uzytkownika, UZ.id_panstwa, Uzytkownicy.imie, UZ.nazwisko, UZ.rok_urodzenia, UZ.email, 
UZ.haslo, UZ.data_rejestracji, UZ.data_ostatniego_logowania, UZ.misto, UZ.ulica, UZ.ulubiony_dlugosc, UZ.ulubiony_szerokosc, 
UZ.ulubiony_zoom, SP.nazwa FROM Uzytkownicy UZ
LEFT OUTER JOIN Słuzby_porzadkowe SP ON UZ.id_sluzby_porzadkowe = SP. id_sluzby_porzadkowe
WHERE UZ.id_sluzby_porzadkowe IS NOT NULL;

• Pobranie danych użytkownika na podstawie loginu i hasła.

SELECT * FROM Uzytkownicy UZ WHERE upper(UZ.email)=upper(‘pmEmail’) AND UZ.haslo=’pmHaslo’;

• Dodanie nowej opinii/oceny do zdarzenia.

INSERT INTO Opinie (id_zdarzenia, id_uzytkownicy, data_dodania, ocena, komentarz) 
VALUES (pmIdZdarzenia, pmIdUzytkownicy, date(„Y-m-d”), pmOcena, pmKomentarz)

• Pobranie wszystkich aktualnych opinii do zdarzenia.

SELECT * FROM Opinie OP WHERE OP.id_zdarzenia=’pmIdZdarzenia’ AND OP.aktualne=’TRUE’

• Zarchiwizowanie opinii o zdarzeniu.

UPDATE Opinie OP SET OP.aktualne=’FALSE’ WHERE OP.id_opinie=’pmIdOpinie’

• Dodawanie nowego zdarzenia.

INSERT INTO Zdarzenia (id_nazwy_zdarzen, id_uzytkownicy, szerokość_geograficzna, długość_geograficzna, data_dodania, opis, 
adres_zdjecia, stan, waznosc) VALUES (pmIdNazwyZdarzen, pmIdUzytkownicy, pmSzerokoscGeograficzna, pmDlugoscGeograficzna, 
date(„Y-m-d”), pmOpis, pmAdresZdjecia, ‘1’, pmWaznosc)

• Pobranie informacji o zdarzeniu.

SELECT * FROM Zdarzenia ZD WHERE ZD.id_zdarzenia=’pmIdZdarzenia’

• Zarchiwizowanie zdarzenia.

UPDATE Zdarzenia ZD SET ZD.aktualne=’FALSE’ WHERE ZD.id_zdarzenia=’pmIdZdarzenia’

• Pobranie wszystkich punktów.

SELECT * FROM Zdarzenia ZD WHERE (ZD.szerokosc_geograficzna > pmWspolrzednaPoludnie  AND ZD.szerokosc_geograficzna < pmWspolrzednaPolnoc 
AND ZD.dlugosc_geograficzna > pmWspolrzednaZachod AND ZD.dlugosc_geograficzna < pmWspolrzednaWschod;

• Pobranie kategorii.

SELECT TKZ.nazwa, TKZ.id_typy_kategorii, KZ.nazwa, KZ.id_kategorie_zdarzen FROM Kategorie_zdarzen KZ LEFT OUTER JOIN 
Typy_kategorii_zdarzen TKZ ON KZ.id_typy_kategorii = TKZ.id_typy_kategorii;