Projekt Logiczny
- 1. Projektowanie tabel, kluczy, kluczy obcych, powiązań między tabelami, indeksów, etc. w oparciu o zdefiniowany diagram ERD
Diagram ERD:
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");
SELECT * FROM Uzytkownicy;
SELECT Mecz_ID, Data_meczu,Druzyna_Dom, Druzyna_Wyjazd FROM Mecze WHERE Druzyna_Dom = "Druzyna";
SELECT * FROM Uzytkownicy_Zalogowani;