====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. {{:pl:dydaktyka:ztb:2011:projekty:meetus:final_3.png|}} Poniżej zamieszczamy link do skryptu wygenerowanego z powyższego schematu przy użyciu narzędzia Toad. {{:pl:dydaktyka:ztb:2011:projekty:meetus: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: * 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. === Projektowanie operacji na danych. === * 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;