====== 1 Skrypt tworzący bazę danych ====== {{:pl:dydaktyka:ztb:2011:projekty:trener:create_database.sql|}} Skrypt tworzy tabele, ustala dla nich klucze główne. Dodatkowo dodawane są do bazy przykładowe dane. ====== 2 Słownik danych ====== __Abstract__ - tabela abstrakcyjna po której dziedziczą wszystkie tabele w bazie danych: * created - integer - id osoby, która stworzyła wpis * created_time - date - data kiedy wpis został stworzony * modified - integer - id osoby która jako ostatnia modyfikowała wpis * modified_time - date - data kiedy wpis został ostatnio zmodyfikowany __Match_Set__ - tabela przechowująca statystyki z meczów: * mset_id - integer, NOT NULL - id statystyki meczu * mset_match - integer - id meczu * mset_player - integer - id zawodnika * mset_team - integer - id drużyny w której gra zawodnik * mset_position - character(1) - pozycja na jakiej gra zawodnik * mset_intime - integer - minuta w której zawodnik wszedł na boisko * mset_outtime - integer - minuta w której zawodnik zszedł z boiska __Notes__ - tabela przechowująca notatki trenera na temat zawodnika: * nt_id - integer, NOT NULL - id notatki * nt_date - date - data utworzenia notatki * nt_text - text - treść notatki * nt_usr_id - integer, NOT NULL - id trenera, który stworzył wpis __Matches__ - tabela przechowująca dane o rozegranych meczach: * mts_id - integer, NOT NULL - id meczu * mts_host - integer, NOT NULL - id drużyny gospodarzy * mts_guest - integer, NOT NULL - id drużyny gości * mts_city - character varying(40) - miasto w którym odbywa się mecz * mts_hscore - integer, NOT NULL - ilość bramek strzelonych przez gospodarzy * mts_gscore - integer, NOT NULL - ilość bramek strzelonych przez gości * mts_hposs - integer - procent posiadania piłki przez gospodarzy * mts_gposs - integer - procent posiadania piłki przez gości * mts_haccu - integer - skuteczność gospodarzy * mts_gaccu - integer - skuteczność gości __Players__ - tabela przechowująca dane na temat zawodników: * pl_id - integer, NOT NULL - id zawodnika * pl_birthdate - date - data urodzenia zawodnika * pl_city - character varying(30) - miejsca zamieszkania zawodnika * pl_footed - character(1) - zmienna opisująca czy zawodnik jest prawo czy lewonożny * pl_weight - integer - waga zawodnika * pl_speed - integer - szybkość zawodnika * pl_mot_perf - integer - osiągi motoryczne * pl_goals - integer - ilość bramek strzelonych przez zawodnika * pl_matches - integer - ilość meczów, które rozegrał zawodnik * pl_ycards - integer - ilość żółtych kartek zdobytych przez zawodnika * pl_rcards - integer - ilość czerwonych kartek zdobytych przez zawodnika * pl_usr_id - integer NOT NULL - id użytkownika * pl_tm_id - integer, NOT NULL - id drużyny __Roles__ - tabela przechowująca opis ról definiowanych w systemie: * rol_id - integer, NOT NULL - id roli * rol_name - character varying(255) - nazwa roli * rol_description - character varying(255) - opis roli __Stats__ - tabela przechowująca statystyki dotyczące zawodnika: * sts_id - integer, NOT NULL - id statystyki * sts_match - integer, NOT NULL - id meczu * sts_player - integer, NOT NULL - id zawodnika * sts_team - integer, NOT NULL - id drużyny * sts_event - character(1) - zmienna opisująca rodzaj wpisu (R - czerwona kartka itp) * sts_time - integer - minuta w której wydarzenie miało miejsce __Teams__ - tabela przechowująca informacje na temat drużyny: * tm_id - integer, NOT NULL - id drużyny * tm_name - character varying(40) - nazwa drużyny * tm_city - character varying(40) - nazwa miasta __Transfers__ - tabela przechowująca informacje na temat zmian drużyny przez zawodnika: * trf_id - integer, NOT NULL - id transferu * trf_player - integer, NOT NULL - id zawodnika * trf_seller - integer, NOT NULL - id poprzedniej drużyny * trf_buyer - integer, NOT NULL - id obecnej drużyny __User_roles__ - tabela przechowująca informacje na temat roli jaką posiada użytkownik: * urol_user_id - integer, NOT NULL - id użytkownika * urol_rol_id - integer, NOT NULL - id roli __Users__ - tabela przechowująca informacje na temat użytkowników: * usr_id - integer NOT NULL - id użytkownika * usr_login - character varying(255), NOT NULL - login użytkownika * usr_password - character varying(255), NOT NULL - hasło użytkownika * usr_firstname - character varying(255), NOT NULL - imię użytkownika * usr_lastname - character varying(255),NOT NULL - nazwisko użytkownika * usr_tm_id - integer NOT NULL - drużyna użytkownika ====== 3 Analiza zależności funkcyjnych i normalizacja tabel ====== **1FN** - Pierwsza postać normalna Elementy wszystkich tabel są atomami, atomy są elementarne, baza spełnia pierwsza postać normalną. **2FN** - Druga postać normalna Wszystkie tabele spełniają pierwszą postać normalną. Klucze główne składają się z jednego pola. Dodatkowo każdy atrybut w tabeli w pełni zależy od klucza głównego. Tabela fth_users_roles jest tabelą asocjacyjną łączącą id roli oraz id użytkownika, nie posiada klucza głównego. Baza spełnia druga postać normalną. **3FN** - Trzecia postać normalna Wszystkie tabele spełniają 2 i 3 FN oraz nie są zależne przechodnio. ====== 4 Funkcjonalność bazy danych ====== ==== Dodawanie użytkownika ==== insert into fth_users values(3,'Tue May 17 00:00:00 CEST 2011',3,'Tue May 17 00:00:00 CEST 2011',3,'rmat','admin','Radosław','Matios',1) ==== Dodawanie drużyny ==== insert into fth_teams values(3,'Tue May 17 00:00:00 CEST 2011',3,'Tue May 17 00:00:00 CEST 2011',3,'GTS','Łeba ') ==== Dodawanie meczu ==== insert into fth_matches values(5,'Tue May 17 00:00:00 CEST 2011',5,'Tue May 17 00:00:00 CEST 2011',5,17,8,'Świętochłowice ',4,0,98,2,8,21) ==== Edytowanie użytkownika ==== update fth_players set pl_weight=75, where pl_id = 27; ==== Dodawanie statystyki ==== insert into fth_stats values(11,'2011-05-17',11,'2011-05-17',11,1,84,6,'S',5); ==== Pobieranie informacji o zawodniku ==== select * from fth_users where usr_id = 123; ==== Pobieranie informacji o drużynie ==== select * from fth_teams where tm_id = 321; ==== Usuwanie meczu ==== update fth_players set pl_matches = pl_matches - 1, pl_goals = pl_goals - ( select count(*) from fth_stats where sts_match = 1 AND sts_player = 79 AND sts_event ='G'), pl_ycards = pl_ycards - ( select count(*) from fth_stats where sts_match = 1 AND sts_player = 79 AND sts_event ='Y'), pl_rcards = pl_rcards - ( select count(*) from fth_stats where sts_match = 1 AND sts_player = 79 AND sts_event ='R') where pl_id =79; delete from fth_match_set where mset_match = 1; delete from fth_stats where sts_match = 1; delete from fth_matches where mts_id = 1; ==== Dodawanie transferu ==== insert into fth_transfers values(9,'Tue May 17 00:00:00 CEST 2011',9,'Tue May 17 00:00:00 CEST 2011',9,9,10,1) ==== Dodawanie statystyki meczowej ==== insert into fth_match_set values(137,'2011-05-17',137,'2011-05-17',137,6,76,6,'O',0,90); ==== Dodawanie zawodnika ==== insert into fth_players values(10,'Tue May 17 00:00:00 CEST 2011',10,'Tue May 17 00:00:00 CEST 2011',10,'Sun Jun 14 00:00:00 CEST 1998','Nowogard ','L',76,14,0,0,0,0,0,10,1); ==== Dodawanie roli ==== insert into fth_roles values(51,'TRN','trainer');