[[
✎ pl:dydaktyka:sbd:2009:projekty:indect:projekt_logiczny
]]
aiWiki
Pokaż stronę
Ostatnie zmiany
Indeks
Zaloguj
Ta strona jest tylko do odczytu. Możesz wyświetlić źródła tej strony ale nie możesz ich zmienić.
====== Projekt logiczny ====== ==== -. Projektowanie tabel. ==== ---- Kod SQL tworzący kompletną strukturę danych wykorzystywaną w projekcie wygląda następująco: <code> 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; </code> Powyższy kod tworzy strukturę bazy danych zgodną z opisem zawartym w części konceptualnej dokumentacji. == == ==== -. Słownki danych. ==== ---- 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. == == ==== -. Analiza zależności funkcyjnych i normalizacja tabel. ==== ---- 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. == == ==== -. Denormalizacja struktury tabel. ==== ---- W projekcie nie przewidujemy denormalizacji. == == ==== -. Projektowanie operacji na danych. ==== ---- 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. <code> 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) </code> * Pobieranie listy służb specjalnych. <code> 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; </code> * Pobieranie listy służb specjalnych. <code> 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; </code> * Edycja danych uzytkownika. <code> 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’; </code> * Pobieranie listy służb specjalnych. <code> 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; </code> * Zmiana hasła. <code> UPDATE Uzytkownicy SET haslo=’pmHaslo’ WHERE id_uzytkownicy=’pmIdUzytkownicy’; </code> * Pobranie wszystkich danych użytkownika. <code> SELECT * FROM Uzytkownicy WHERE id_uzytkownicy=’pmIdUzytkownicy’; </code> * Pobranie wszystkich danych użytkowników. <code> SELECT * FROM Uzytkownicy </code> * Pobranie listy służb specjalnych. <code> 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; </code> * Pobranie danych użytkownika na podstawie loginu i hasła. <code> SELECT * FROM Uzytkownicy UZ WHERE upper(UZ.email)=upper(‘pmEmail’) AND UZ.haslo=’pmHaslo’; </code> * Dodanie nowej opinii/oceny do zdarzenia. <code> INSERT INTO Opinie (id_zdarzenia, id_uzytkownicy, data_dodania, ocena, komentarz) VALUES (pmIdZdarzenia, pmIdUzytkownicy, date(„Y-m-d”), pmOcena, pmKomentarz) </code> * Pobranie wszystkich aktualnych opinii do zdarzenia. <code> SELECT * FROM Opinie OP WHERE OP.id_zdarzenia=’pmIdZdarzenia’ AND OP.aktualne=’TRUE’ </code> * Zarchiwizowanie opinii o zdarzeniu. <code> UPDATE Opinie OP SET OP.aktualne=’FALSE’ WHERE OP.id_opinie=’pmIdOpinie’ </code> * Dodawanie nowego zdarzenia. <code> 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) </code> * Pobranie informacji o zdarzeniu. <code> SELECT * FROM Zdarzenia ZD WHERE ZD.id_zdarzenia=’pmIdZdarzenia’ </code> * Zarchiwizowanie zdarzenia. <code> UPDATE Zdarzenia ZD SET ZD.aktualne=’FALSE’ WHERE ZD.id_zdarzenia=’pmIdZdarzenia’ </code> * Pobranie wszystkich punktów. <code> SELECT * FROM Zdarzenia ZD WHERE (ZD.szerokosc_geograficzna > pmWspolrzednaPoludnie AND ZD.szerokosc_geograficzna < pmWspolrzednaPolnoc AND ZD.dlugosc_geograficzna > pmWspolrzednaZachod AND ZD.dlugosc_geograficzna < pmWspolrzednaWschod; </code> * Pobranie kategorii. <code> 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; </code> == ==
pl/dydaktyka/sbd/2009/projekty/indect/projekt_logiczny.txt
· ostatnio zmienione: 2019/06/27 15:50 (edycja zewnętrzna)
Pokaż stronę
Poprzednie wersje
Menadżer multimediów
Do góry