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.
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:
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:
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.
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);
insert into users( email, is_admin,age,is_man) values('dorota.wojtalow@gmail.com',true, 21, false);
insert into categories (category_name, user_id) values('siatkowka', (select user_id from users where email='ala@poczta.fm'));
delete from categories where category_id=?; delete from categories where category_name='siatkowka';
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') );
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;
insert into free_places_watchers (event_id, user_id) values ( 4, 16 );
insert into event_marks(mark, marked_event_id, mark_author_id) values(3, 5,6);
insert into event_comments(comment, event_id, comment_author_id) values('Bardzo fajne wydarzenie', 5,7);
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());
insert into participants_and_invited_users (event_id, user_id, is_taking_part) values (4,5,true);
update participants_and_invited_users set is_taking_part=false
delete from participants_and_invited_users where event_id=5 and user_id=5;
insert into participants_and_invited_users(event_id, user_id, is_taking_part) values (?,?,false);
delete from participants_and_invited_users where event_id=? and user_id=?;
delete from events where event_id=5;
delete from events where events.event_owner=(select user_id from users where email='dorota.wojtalow@gmail.com');
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';
update events set is_canceled=true where events.name='mecz siatkówki';
select count(user_id) from users;
select count(event_id) from events;
select count(category_id) from categories;
select count(event_id), date_part('month',events.start_time) as month from events group by date_part('month',events.start_time);
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;
insert into new_ev_in_cat_notifications (user_id, category_id) values (?,?);
insert into new_event_in_loc_notifications(events_to_notify_location, user_id) values (?, ?);
update users set notify_about_changes_in_my_events=true where user_id=4;
update users set blocked_account = true where user_id=4;
update users set active_account=false where user_id=34;
select name from events join users on events.event_owner=users.user_id where users.user_id=34;
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;
select events.name from events join participants_and_invited_users using(event_id) join users using(user_id) where users.user_id=342;
