Projekt logiczny
Doprecyzowanie struktury bazy.
Doprecyzowane bazy nastąpiło poprzez stworzenie z programie Toad fizycznego modelu bazy dla bazy PostgreSQL 8.4. Fizyczny model jest reprezetowany przez diagram ERD, na podstawie którego
został wygenerowany skrypt SQL tworzący bazę. Poniższy rysunek przedstawia końcowy schemat bazy.
Poniżej zamieszczamy link do skryptu wygenerowanego z powyższego schematu przy użyciu narzędzia Toad.
create_db_scrip_2.sql
Jak można łatwo zauważyć wygenerowany skrypt nie jest optymalny. Tworzenie kluczy i indexów nie jest częścią kwerendy „Create table” lecz następuje w wyniku wykonania „Alter table” na już
istniejącej tabeli. Wynika to ze sposobu projektowania modelu - najpierw tworzone są tabele (generowany jest skrpyt Create table), następnie dodawane są do nich relacje (istniejące tabele są
modyfikowane przy użyciu Alter Table).
Dodatkowo wygenerowany skrypt wymagał ręcznej modyfikacji polegającej na zmianie kolejności instrukcji. Tworzenie sekwencji zostało pierwotnie wygenerowane po wykorzystujących je zapytaniach przez co skrypt nie mógł być poprawnie wykonany.
Słownik danych.
Poniżej zamieszczamy dziedziny i ograniczenia dla atrybutów poszczególnych relacji:
Events
event_id - not null, uniqie, pole generowane automatycznie, liczba całkowita > 0, unikalny identyfikator wydarzenia
name - not null, unique, ciąg znaków dł. <50, nazwa wydarzenia
start_time - not null, data + czas z przyszłości, czas rozpoczęcia wyd.
stop_time - not null, data + czas nie wcześniej niż start_time, czas zakończenia wyd.
location - not null, Point, lokalizacja wyd.
is_event_private - not null, bool, określenie czy wydarzenie jest prywatne czy publicczne
max_nr_peopl - liczba całkowita > 0, max ilość osób mogących brać udział w wydarzeniu
required_gender - not null, jeden ze stringów: „man”, „woman”, „no”, wymagana płeć uczestników
min_age - liczba całkowita > 0, wymagany minimalny wiek
sign_in_dealine - data + czas z przyszłości, nie później niż start_time, ostateczny termin zapisu
show_participants - not null, bool, wartość określająca czy należy wyświetlać zuczestników
info - text, dodatkowe informacje o wyarzeniu
repetition_time - liczba całkowita - wartość różna od null oznacza ilość dni, po których nastepuje automatyczne powtórne założenie wydarzenia. czas powtórzenia wydarzenia
creation_date - not null, pole automatyczne, data założenia wydarzenia, data utworzenia wyd.
is_canceled - not null, bool, wartość określająca czy wyd. zostało anulowane
event_category - not null, liczba całkowita - id kategorii, kategoria wyd.
event_owner - not null, liczba całkowita - id uzytkownika, id osoby tworzącej wyd.
Event_marks
creation_date - not null, pole automatyczne, data + czas, data wystawienia oceny
mark - not null, liczba całkowita z przedziału [1-10], ocena wyd.
event_mark_id - not null, pole automatyczne, dodatnia liczba całkowita, id oceny
marked_event_d - not null, dodatnia liczba całkowita, id ocenianego wydarzenia
mark_author_id - not null, dodatnia liczba całkowita, id użytkownika wystawiającego ocenę
Event_comments
creation_date - not null, pole automatyczne, data + czas, data wystawienai komentarza
comment - not null, pole tekstowe, treść komentarza
comment_id - not null, dodatnia liczba całkowita, pole automatyczne. id komentarza
event_id -not null, dodatnia liczba całkowita, id komentowanego wydarzenia
comment_author_id - not null, dodatnia liczba całkowita, id autora komentarza
Users
email - not null, unique, ciąg znaków dł. <50, adres email uzytkownika (login)
active_account - not null, bool, wartość określająca czy konto użytkownika jest aktywne
currently_logged_in - not null, bool, wartość określająca czy użytkownik aktualnie jest zalogowany
blocked_account - not null, bool, wartość określająca czy użytkownik jest zablokowany
creation_date - not null, pole automatyczne, data + czas, data założenia konta
notify_about_changes_in_my_events - not null, bool, wartość określająca czy należy powiadamiać użytkownika o zmiana w wydarzeniach
user_id - not null, pole automatyczne, doatnia liczba całkowita, identyfikator użytkownika
is_admin - not null, bool, wartość określająca czy użytkownik jest adminem
age - not null, doatnia liczba całkowita, wiek
is_man - not null, bool, wartość określająca opłec uzytkownika
Categories
category_name - not null, unikalny ciąg znaków, dł < 50, not null, nazwa kategorii
category_id - not null, unique, dodatnia liczba całkowita, identyfikator kategorii
user_id - not null,dodatnia liczba całkowitu, id użytkownika dodającego kategorię
creation_date - not null, pole automatyczne, data + czas, data dodania kategorii
New_Event_In_Location_Notifications
events_to_notify_location - not null, Polygon, jeśli zostanie założone wydarzenie z lokalizacją wewnątrz polygona przesyłane jest powiadomienie
notification_id - unique, not null, pole automatyczne, całkowita liczba dodatnia, identyfikator notyfikacji
user_id - not null, całkowita liczba dodatnia, id użytkownika do którego przesyłane jest wydarzenie
External_event_participants
participant_id - not null, unique, pole automatyczne, całkowita liczba dodatnia, id osoby niezarejestrowanej w portalu ale zaproszonej na jedno z wydarzeń. (możliwość zapraszania osób spoza portalu na wydarzenia)
email - not null, unique, ciąg znaków dł. < 50
New_event_in_category_notifications
user_id - not null, dodatnia liczba całkowita, id użytkownika powiadamianego
category_id - not null, dodatnia liczba całkowita, id kategorii o wydarzeniach z której użytkownik jet powiadamiany
New_event_by_user_notifications
event_organizer_id - not null, dodatnia liczba calkowita, id użytkownika o wydarzeniach którego są wysyłane powiadomienia
user_id - not null, dodatnia liczba całkowita, id użytkownika, któremu są wysyłane powiadomienia
Event_black_list
event_id - not null, dodatnia liczba całkowita, id wydarzenia
user_id - not null, dodatnia liczba całkowita, id użytkownika, który nie ma wstępu na wydarzenie o event_id
Invitations
event_id - not null, dodatnia liczba całkowita, id wydarzenia na które ktoś jest zapraszany
user_id - not null, dodatnia liczba całkowita, id zapraszanego użytkownika
Participants_and_invited_users
event_id - not null, dodatnia l. całk, id wydarzenia
user_id - not null, dodatnia l. całw, id użytkownika portalu meet_us zaproszonego na wydarzenie o event_id (w przypadku wydarzeń prywatnych wszyscy zaproszeni użytkownicy są dodawani do tej relacji, w przypadku wydarzeń pubicznych tylko użytownicy którzy zapisani na wydarzenie)
is_taking_part - not null, gdy false - oznacza, że event_id jest wydarzeniem prywatnym a user_id został na nie zaproszony ale nie potwierdził uczestnictwa, gdy true - oznacza, że event_id jest wydarzeniem pubicznym, a user_id bierze w nim udział lub event_id jest wydarzeniem prywatnym a user_id został na nie zaproszony i bierze w nim udział
Invited_eternal_users
participant_id - not null, dodatnia l. całk. id osoby nie będącej użytkownikiem portalu ale zaproszonej na jedno z wydarzeń
event_id - not null, dodatnia l.całk - id wydarzenia
Free_places_watchers
event_id - not null, dodatnia l. calk. id obserwowanego wyd.
user_id - not null, dodatnia l. całk. id obserwatora powiadamianego gdy zwolnią się jakieś miejsca
Analiza zależności funkcyjnych i normalizacja.
Aby sprawdzić, że baza jest w 3NF należy sprawdzić, że jest w 2NF oraz wszystkie niekluczowe pola zależą tylko od atrybutów kluczowych.
Innymi słowy, nie ma w relacji atrybutów niekluczowych, zależących od czegoś innego niż klucz główny (w szczególności mogących istnieć niezależnie, bez klucza danej tabeli - w takim przypadku
należy dokonać dekompozycji tabeli).
Aby sprawdzić, że baza jest w 2NF należy sprawdzić, że wszystkie atrybuty niekluczowe zależą od całego klucza.
Jest to istotne w przypadku kluczy zlożonych.
W przypadku kluczy prostych, gdzie cały klucz stanowi jeden atrybut, 2NF dostajemy „za darmo”.
Sprawdzenie 2NF:
W opracowanym schemacie bazy przedstawionym na pierwszym diagramie tabele:
posiadają prosty klucz główny zatem wszystkie atrybuty zależą od całego klucza głównego.
Relacjami posiadającymi złożony klucz główny są tabele:
new_ev_in_cat_notifications
new_ev_by_user_notifications
event_black_lists
invitations
participants_and_invited_users
invited_external_users
free_places_watchers
Tabele te nie posiadają żadnych atrybutów nie-kluczowych, dlatego są również w 2NF.
Sprawdzenie 3NF:
opisują konkretne wydarzenie, zależą od klucza event_id i nie mogą bez niego istnieć (nie mają sensu bez konretnego wydarzenia).
są charakterystycze dla konta użytkownika i nie mogą bez niego istnieć
Oba strybuty są charakterystyczne dla konkretnej, wystawionej oceny i zależą od event_mark_id
charakterystyczne dla konretnego komentarza, zależa od event_comment_id
W podany sposób zostało sprawdzone, że otrzymana struktura jest w 3NF. We wczesnych fazach pojektowania, jednen z pierwszych powstałych diagramów ERD był w 1NF.
Część tabel została zdekomponowana, klucze złożone zostały zamienione na proste poprzez wprowadzenie id.
Projektowanie operacji na danych.