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.

Poniżej zamieszczamy dziedziny i ograniczenia dla atrybutów poszczególnych relacji:

1. 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.
2. 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ę
3. 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
4. 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
5. 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
6. 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
7. 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
8. 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
9. 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
10. 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
11. 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
12. 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ł
13. 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
14. 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

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:

• events
• users
• categories
• event_comments
• event_marks
• event_external_participants
• new_event_in_location_notifications

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:

• Wszystkie niekluczowe atrybuty tabeli events:
  • name,
  • start_time,
  • stop_time,
  • location,
  • is_event_private,
  • max_nr_people,
  • min_age,
  • show_participants,
  • sign_in_deadline,
  • info (informacja o wydarzeniu)
  • repetition_time
  • creation_date
  • is_canceled

opisują konkretne wydarzenie, zależą od klucza event_id i nie mogą bez niego istnieć (nie mają sensu bez konretnego wydarzenia).

• W przypadku tabeli users:
  • email
  • is_active_account
  • currently_logged_in
  • blocked_account
  • creation_date
  • notify_about_changes_in_my_events
  • is_admin
  • age
  • is_man

są charakterystycze dla konta użytkownika i nie mogą bez niego istnieć

• Mark_events
  • creation_date - data dodania oceny
  • mark - ocena wydarzenia S

Oba strybuty są charakterystyczne dla konkretnej, wystawionej oceny i zależą od event_mark_id

• Event_comments
  • creaton_date - data dodania komentarza
  • comment - komentarz

charakterystyczne dla konretnego komentarza, zależa od event_comment_id

• New_event_in_loc_notifications
  • event_to_notify_location - polygon określający obszar (lokalizację). użytkownik o danym user_id chce być informowany o wydarzeniach w tej lokalizacji. Charakterystyczne dla danego powiadomienia. Jeden użytkownik może mieć tworzyć wiele powiadomień.
• External_event_participants
  • email - charakterystyczny i unikalny dla każdego participant_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.

• Tworzenie konta użytkownika:

  insert into users(email, notify_about_changes_in_my_events, age, is_man) values('zpsoassd@agh.edu.pl',true, 43, false);
  insert into users(email, age, is_man) values('agusia@student.agh.edu.pl', 107, false);

• Tworzenie konta administratora:

  insert into users( email, is_admin,age,is_man) values('dorota.wojtalow@gmail.com',true, 21, false);

• Tworzenie kategorii wydarzeń:

  insert into categories (category_name, user_id) values('siatkowka', (select user_id from users where email='ala@poczta.fm')); 

• Usuwanie kategorii:

  delete from categories where category_id=?;
  	delete from categories where category_name='siatkowka'; 

• Dodawanie wydarzeń:

  insert into 
  	events(name,start_time,stop_time, location, is_event_private, max_nr_of_people, 
  	required_gender, min_age, show_participants,sign_in_deadline, info, event_category, event_owner) 
  	values('wyjscie na turbacz','2011-06-19 6:00:00', '2011-06-19 20:00:00', point(100,324), 
  	true, 10, 'no', 15, true,'2011-06-16 20:00:00','Wycieczka na Turbacz', 
     (select category_id from categories where category_name='GORY'), (select user_id from users where email='ala@poczta.fm') );

• Wyszukanie wydarzeń wg. danych kryteriów np. z danej kategorii, w danej lokalizacji, w danym czasie, posortowane po dacie:

  select name, start_time, stop_time, location from events join categories on event_category=category_id 
  where 
  	category_name='siatkowka' and  
  	location <@ polygon '((0,0),(0,500),(500,500),(0,500))' and 
  	events.start_time>=now() and events.start_time<=now()+7 * interval '1 day' order by start_time asc; 

• Powiadomienie o wolnym miejscu dla danego wydarzenia:

   insert into free_places_watchers (event_id, user_id) values ( 4, 16 ); 

• Ocena wydarzenia:

   insert into event_marks(mark, marked_event_id, mark_author_id) values(3, 5,6); 

• Dodanie komentarza:

    insert into event_comments(comment, event_id, comment_author_id) values('Bardzo fajne wydarzenie', 5,7); 

• Zapisanie na wydarzenie prywatne (zaproszonego użytkownika):

   update participants_and_invited_users
  		set is_taking_part=true
  		where event_id=3 and user_id=5 and ((select sign_in_deadline from events) >= now()); 

• Zapisanie na wydarzenie publiczne:

   insert into participants_and_invited_users (event_id, user_id, is_taking_part) values (4,5,true); 

• Rezygnacja z wydarzenia prywatnego:

    update participants_and_invited_users
  		set is_taking_part=false 

• Rezygnacja z wydarzenia publicznego:

   delete from participants_and_invited_users where event_id=5 and user_id=5;  

• Zaproszenie użytkowników do wydarzenia:

   insert into participants_and_invited_users(event_id, user_id, is_taking_part) values (?,?,false); 

• Usunięcie użytkownika z listy gości:

    delete from participants_and_invited_users where event_id=? and user_id=?; 

• Usunięcie konkretnego wydarzenia:

   delete from events where event_id=5; 

• Usunięcie wszystkich wyd. użytkownika:

   delete from events where events.event_owner=(select user_id from users where email='dorota.wojtalow@gmail.com'); 

• Zmiana atrybutów wydarzenia:

   update events 
  	set max_nr_people=10,
  	min_age=20,
  	start_time='2011-06-23 16:00:00',
  	stop_time='2011-06-23 18:00:00'
  	where events.name='mecz siatkówki';

• Odwolanie wydarzenia:

  update events 
  	set is_canceled=true
  	where events.name='mecz siatkówki'; 

• Przeglądanie statystyk:
  • Ilość osób zarejestrowanych

     select count(user_id) from users; 

  • Ilość wydarzeń

     select count(event_id) from events; 

  • Ilość kategorii

     select count(category_id) from categories; 

  • Ilość wydarzeń w poszczególnych miesiącach

     select count(event_id), date_part('month',events.start_time) as month  from events group by  date_part('month',events.start_time); 

  • Wypisz 3 najlepszych pracowników

     select email, avg(event_marks.mark) as avarage_mark from users 
    		join events on events.event_owner=users.user_id 
    		join event_marks on event_marks.marked_event_id=events.event_id 
    		group by email order by avg(event_marks.mark) desc limit 3;  

• Ustawienia konta (powiadomienia o wydarzeniach):
  • Powiadom mnie o nowych wydarzeniach z kategorii

     insert into new_ev_in_cat_notifications (user_id, category_id) values (?,?); 

  • Powiadom mnie o nowych wydarzeniach z mojej okolicy

     insert into new_event_in_loc_notifications(events_to_notify_location, user_id) values (?, ?); 

  • Powiadom mnie o zmianach wydarzeń na które jestem zapisany

     update users
    	set notify_about_changes_in_my_events=true
    	where user_id=4; 

• Blokowanie uzytkowników:

    update users
  	set blocked_account = true
  	where user_id=4;  

• Deaktywacja konta:

    update users
  	set active_account=false
  	where user_id=34;   

• Pobranie informacji o użytkowniku:
  • Organizowane wydarzenia

    select name from events join users on events.event_owner=users.user_id where users.user_id=34;

  • Średnia ocen

     select avg(event_marks.mark) as 'sredna ocen' 
    	from event_marks join events on event_marks.marked_event_id=events.event_id 
    	join users on events.event_owner=users.user_id where user_id=32; 

  • Wydarzenia w których brał udział

     select events.name 
    	from events join participants_and_invited_users using(event_id) 
    	join users using(user_id) where users.user_id=342;