====== Projekt Logiczny ====== ===== - 1. Projektowanie tabel, kluczy, kluczy obcych, powiązań między tabelami, indeksów, etc. w oparciu o zdefiniowany diagram ERD ===== ===== Diagram ERD: ===== [[http://ai.ia.agh.edu.pl/wiki/pl:dydaktyka:ztb:2011:projekty:futsalteam:start:prokekt_logiczny:diagram_ERD|diagram ERD]] ===== Kod SQL: ===== [[http://ai.ia.agh.edu.pl/wiki/pl:dydaktyka:ztb:2011:projekty:futsalteam:start:prokekt_logiczny:kod_sql|kod SQL]] ====== - 2. Słownik danych ====== Uzytkownicy – tabela przechowująca dane o użytkownikach systemu * Uzytkownik_ID - id użytkownika, klucz główny, integer , NOT NULL, * Login – login użytkownika, varchar(20), NOT NULL, * Haslo_U – hasło użytkownika, varchar(20), NOT NULL, * Imie – imię użytkownika, varchar(20), NOT NULL, * Nazwisko – nazwisko użytkownika, varchar(30), NOT NULL, * E_mail – adres e-mail użytkownika, varchar(30), NOT NULL, * Data_Rejestracji – data rejestracji użytkownika, timestamp, NOT NULL, * Data_Ost_Zalogowania – data ostatniego zalogowania użytkownika, timestamp, NOT NULL, * Status – status użytkownika, smallint, NOT NULL Druzyny - tabela przechowująca dane o drużynach * Druzyna_ID - id drużyny, klucz główny, integer, NOT NULL, * Druzyna - nazwa druzyny, varchar(30), NOT NULL, * Domena - nazwa domeny dla druzyny, varchar(30), NOT NULL, * Miejscowosc - miejscowość z której jest druzyna, varchar(30), NOT NULL, * Wojewodztwo - województwo z której jest druzyna, varchar(20), NULL, * Powiat - powiat z której jest druzyna,varchar(20), NULL Uzytkownicy_Druzyna - tabela przechowująca informacje o przyporządkowaniu użytkowników do drużyn * Uzytkownik_ID - id uzytkownika,klucz obcy wchodzacy w skład klucza głównego, integer, NOT NULL, * Druzyna_ID - id druzyny, klucz obcy wchodzacy w skład klucza głównego, integer, NOT NULL Druzyna_Rozgrywki - tabela przechowująca informacje o przyporządkowaniu drużyn do rozgrywek * Druzyna_ID - id druzyny,klucz obcy wchodzacy w skład klucza głównego, integer NOT NULL, * Rozgrywki_ID - id rozgrywek,klucz obcy wchodzacy w skład klucza głównego, integer NOT NULL Mecze - tabela przechowująca informacje o meczach danych drużyn i w danych rozgrywkach * Mecz_ID - id meczu, klucz główny, integer, NOT NULL, * Rozgrywki_ID - id rozgrywek, klucz obcy, integer, NOT NULL, * Druzyna_Dom - drużyna która gra mecz u siebie, varchar(30), NOT NULL, * Druzyna_Wyjazd - drużyna która gra mecz na wyjeździe, varchar(30), NOT NULL, * Bramki_Dom - ilość bramek strzelona przez drużynę która gra jako gospodarz, smallint, * Bramki_Wyjazd - ilość bramek strzelona przez drużynę która gra jako gość, smallint, * Data_meczu - data rozgrywania meczu, timestamp, * Strzelcy_Bramek, - zawodnicy którzy zdobyli bramki w meczu, varchar(30) Rozgrywki - tabela przechowująca informacje o rozgrywkach * Rozgrywki_ID - id rozgrywek, klucz główny, integer, NOT NULL, * Rzogrywki_Nazwa - nazwa rozgrywek, varchar(30), NOT NULL Statystyki - tabela przechowująca informacje o statystykach danych drużyn w danych rozgrywkach * Statystyki_ID - id statystyk, klucz główny, integer, NOT NULL * Druzyna_ID - id druzyny, integer, NOT NULL * Wygrane - liczba wygranych danej druzyny w danych rozgrywkach,smallint * Remisy - liczba remisów danej druzyny w danych rozgrywkach,smallint * Porazki - liczba porażek danej druzyny w danych rozgrywkach,smallint * Punkty - liczba punktów danej druzyny w danych rozgrywkach,smallint Artykuly - tabela przechowująca informacje na temat artykułów na stronie * Artykul_ID - id artykułu, klucz główny, integer, NOT NULL * Login - login użytkownika który napisał artykuł, varchar(20) NOT NULL * Druzyna_ID - id drużyny do której przyporządkowany jest artykuł, integer * Tytul_A - tytuł artykułu, varchar(30) * Tekst_A - tekst artykułu, varchar(1000) * Data_A timestamp - data napisania artykułu, NOT NULL Cssy - tabela przechowująca informacje o stylach css * Css_ID - id stylu css, klucz główny, integer NOT NULL * Css_Nazwa - nazwa stylu css, varchar(20) NOT NULL Komentarze - tabela przechowująca informacje o komentarzach do artykułów * Komentarz_ID - id komentarza, klucz główny, integer NOT NULL, * Login - login uzytkownika piszacego komentarz, varchar(20) NOT NULL, * Artykul_ID - id artykułu, integer NOT NULL, * Tekst_K - tekst komentarza, varchar(1000) * Data_K timestamp - data dodania komentarza, NOT NULL Style - tabela przechowująca informacje o stylu dla danej drużyny * Styl_ID - id stylu, klucz główny, integer NOT NULL, * Druzyna_ID - id drużyny, integer NOT NULL, * Css_ID - id CSSa, integer NOT NULL Trenerzy - tabela przechowująca informacje o trenerach * Trener_ID - id trenera, klucz główny, integer NOT NULL, * Imie_T - imię trenera, varchar(20) NOT NULL, * Nazwisko_T - nazwisko trenera, varchar(30) NULL, * Druzyna_ID - id druzyny którą trener trenuje, integer NOT NULL Zawodnicy - tabela przechowująca informacje o zawodnikach * Zawodnik_ID - id zawodnika, klucz główny, integer NOT NULL, * Imie_Z - imię zawodnika, varchar(20) NOT NULL, * Nazwisko_Z - nazwisko zawodnika, varchar(30) NOT NULL, * Druzyna_ID - id druzyny w której gra zawodnik, integer NOT NULL, * Numer - numer na koszulce zawodnika, smallint * Bramki - liczba strzelonych przez zawodnika bramek, smallint Uzytkownicy_Zalogowani - tabela przechowująca informacje o zalogowanych uzytkownikach * Uzytkownicy_Zalogowani_ID - id zalogowanego uzytkownika, klucz główny, integer NOT NULL, * Login - login uzytkownika varchar(20) NOT NULL, * Adres_IP - adres IP uzytkownika, varchar(20) ====== - 3. Analiza zależności funkcyjnych i normalizacja tabel ====== __1NF - Pierwsza postać normalna__ Baza danych jest w pierwszej postaci normalnej, ponieważ wszystkie atrybuty w tabelach bazy danych przyjmują wartość atomiczne. __2NF - Druga postać normalna__ Poza tabelami: //Uzytkownicy_Druzyna// i //Druzyna_Rozgrywki// wszystkie tabele w bazie danych mają proste klucze główne. Natomiast tabele: //Uzytkownicy_Druzyna// i //Druzyna_Rozgrywki// są tabelemi asocjacyjnymi i nie posiadają niekluczowych atrybutów. Ponadto baza spełnia warunek 1NF. Można zatem stwierdzić, że baza jest w drugiej postaci normalnej. __3NF - Trzecia postać normalna __ Baza spełnia warunek 1NF i 2NF. Ponadto wszystkie atrybuty w tabelach wchodzących w skład bazy danych zależą w sposób bezpośredni od klucza głównego, czyli nie występują relacje przechodnie. Na tej podstawie można stwierdzić, że baza jest w trzeciej postaci normalnej. ====== - 4. Projektowanie operacji na danych ====== ===== Kwerendy w języku SQL dla realizacji funkcji w projekcie: ===== ==== Rejestracja użytkownika ==== INSERT INTO Uzytkownicy VALUES (1,"login","haslo","Imie","Nazwisko","e-mail@gmail.com","2011-06-17","2011-06-17",1) ==== Rejestracja drużyny ==== INSERT INTO Druzyny VALUES (1,"Druzyna","domena","Miejscowosc","Wojewodztwo","Powiat") ==== Usuwanie uzytkownika ==== DELETE FROM Uzytkownicy WHERE Login = "login"; ==== Usuwanie druzyny ==== DELETE FROM Druzyny WHERE Druzyna = "Druzyna"; ==== Edycja profilu uzytkownika ==== UPDATE Uzytkownicy SET Imie = "Inne_Imie", E-mail = "inny_email@gmail.com" WHERE Login = "login"); ==== Logowanie użytkownika ==== UPDATE Uzytkownicy SET Data_Ost_Zalogowania = "2011-06-19" WHERE Login = "login"); INSERT INTO Zalogowani_Uzytkownicy VALUES(1,"login","123.123.123.123") ==== Wylogowanie użytkownika ==== DELETE FROM Zalogowani_Uzytkownicy WHERE Login = "login"; ==== Dodawanie rozgrywek ==== INSERT INTO Rozgrywki VALUES (4,"Rozgrywki4"); ==== Dodawanie meczu ==== INSERT INTO Mecze(Mecz_ID,Rozgrywki_ID,Druzyna_Dom,Druzyna_Wyjazd,Data_meczu) VALUES (9,4,"Druzyna1","Druzyna2","2011-06-22"); ==== Edytowanie wyniku meczu ==== UPDATE Mecze SET Bramki_Dom = 2,Bramki_Wyjazd = 1 WHERE Mecz_ID = 9; ==== Dodawanie druzyny do statystyk ==== INSERT INTO Statystyki VALUES(4,0,0,0,0); ==== Edytowanie statystyk dla druzyny ==== UPDATE Statystyki SET Wygrane = 1, Punkty = 3 WHERE Druzyna_ID = 9; ==== Przyporzadkowanie stylu css dla druzyny ==== INSERT INTO Style VALUES(1,4,2); ==== Wprowadzanie zawodnikow do druzyny ==== INSERT INTO Zawodnicy VALUES(1,"Imie_Zawodnika","Nazwisko_Zawodnika",4,11,0); ==== Dodanie trenera do druzyny ==== INSERT INTO Trenerzy VALUES(1,"Imie_Trenera","Nazwisko_Trenera",4); ==== Dodanie artykułu ==== INSERT INTO Artykuły VALUES(1,"login",4,"Tytuł","Tekst artykułu","2011-06-19"); ==== Dodanie komentarza do artykułu ==== INSERT INTO Komentarze VALUES(1,"login",1,"Tekst komentarza","2011-06-19"); ==== Pobieranie informacji o użytkownikach ==== SELECT * FROM Uzytkownicy; ==== Pobieranie wybranych informacji o meczach danej drużyny rozgrywanych jako gospodarz ==== SELECT Mecz_ID, Data_meczu,Druzyna_Dom, Druzyna_Wyjazd FROM Mecze WHERE Druzyna_Dom = "Druzyna"; ==== Pobieranie informacji jacy użytkownicy są zalogowani ==== SELECT * FROM Uzytkownicy_Zalogowani;