POLITECHNIKA WROCŁAWSKA

WYDZIAŁ ELEKTRONIKI

Kierunek: Automatyka i Robotyka (AIR)

Specjalność: Komputerowe Sieci Sterowania (ARK)

PROJEKT BAZY DANYCH

Rozgrywki sportowe

Autorzy:

Mateusz Gąsiorek

Paweł Ruciński

Prowadzący prace:

dr inż. Grzegorz Mzyk

WROCŁAW 2012

Projekt został wykonany w programie Oracle Client Express 11 - Application Express 4.0.2.00.09

Temat:, Jako przykład do implementacji wzięto pod uwagę niepełną (10 zespołów) polską ligę piłki nożnej (Ekstraklasa).

Baza składa się z dwóch tablic:

• TABELA: TEAMS odpowiadająca za numer ID drużyny (wyjątkowy dla każdej) oraz jej nazwę zapisaną w kolumnie odpowiadającej danej ID. Obie kolumny nie mogą przyjmować wartości NULL, już przy wpisywaniu, aby uniknąć sytuacji drużyny bez nazwy.

• TABELA: GAMES odpowiada za rozgrywane mecze, składa się z siedmiu kolumn odpowiadających kolejno, numerowi meczu (unikatowy), ID drużyny gospodarzy, ID drużyny gości, bramki zdobyte przez gospodarzy, bramki zdobyte przez gości, datę meczu (wykorzystując gotowy schemat DATE), oraz numer kolejki, w której dany mecz był rozgrywany. Kolumny odpowiadające bramką gości, oraz bramką gospodarzy mogą przyjmować wartość NULL, aby przy wprowadzaniu terminarza nie uzyskiwać błędów.

W projekcie starano się o jak najbardziej optymalna wersję programu rozgrywek sportowych stąd implementacja dwóch tablic. Takie ustawienie tabel pozwala na wyciągnięcie wszystkich podstawowych statystyk, które można zgromadzić w tabeli końcowej rozgrywek sportowych.

Wstawianie elementów odbywało się poprzez administratora, który posiada dostęp do całe bazy danych, dodano elementy odpowiadające pomniejszonej ekstraklasie polskiej. Tabela zawiera pierwsze 10 drużyn z tabeli końcowej T-mobile ekstraklasy polskiej sezonu 2011/2012.

• TABELA TEAMS

• TABELA GAMES

W następnym kroku stworzono powiązania pomiędzy tablicami wykorzystując wbudowaną opcję „query builder”:

Rezultat:

Wykorzystano takie połączenie, ponieważ zapewnia ona możliwość przykładowej zmiany nazwy drużyny w następnym sezonie (w przypadku awansu lub spadku któreś z drużyn), bez konieczności przerabiania wszystkich meczy.

APLIKACJA:

Tworzymy aplikację wykorzystując gotowego kreatora:

Numer aplikacji, który nadajemy jest konieczny, aby użytkownik mógł uruchomić aplikację (podajemy ją w adresie URL). Jest możliwość zmiany poprzez tworzenie aliasów jednak na potrzeby tego projektu adres pozostaje lokalny

Logowanie do aplikacji odbywa się poprzez panel usera:

Na stronie znajdują się cztery podstrony:

• TERMINARZ (interaktywna tablica z możliwością sortowania po dowolnej kolumnie)

• TABELA (klasyczna tablica posortowana po ilości punktów)

• WPISZ WYNIK (strona zabezpieczona, która poprzez panel logowania przenosi do formularza wpisywania wyniku)

• STATYSTYKI (klasyczna tablica posortowana po stosunku meczy wygranych do przegranych)

OPIS TWORZENIA POSZCZEGÓLNYCH STRON:

TABELA:

Rezultat:

Tworzymy stronę `report’, jako kod strony podajemy:

select TEAM_NAME, ( NVL( (select count(*) from GAMES where (HOME_ID=TEAM_ID) AND (HOME_SCORE>=0)),0) + NVL( (select count(*) from GAMES where (AWAY_ID=TEAM_ID) AND (AWAY_SCORE>=0)),0) ) as MECZE, ( NVL( (select sum(HOME_SCORE) from GAMES where HOME_ID=TEAM_ID),0) + NVL( (select sum(AWAY_SCORE) from GAMES where AWAY_ID=TEAM_ID),0) ) as STRZELONE, ( NVL( (select sum(AWAY_SCORE) from GAMES where HOME_ID=TEAM_ID),0) + NVL( (select sum(HOME_SCORE) from GAMES where AWAY_ID=TEAM_ID),0) ) as STRACONE, ( NVL( (select count(*) from GAMES where (HOME_SCORE > AWAY_SCORE) AND (HOME_ID=TEAM_ID)),0) + NVL( (select count(*) from GAMES where (HOME_SCORE < AWAY_SCORE) AND (AWAY_ID=TEAM_ID)),0) ) as WYGRANE, ( NVL( (select count(*) from GAMES where (HOME_SCORE < AWAY_SCORE) AND (HOME_ID=TEAM_ID)),0) + NVL( (select count(*) from GAMES where (HOME_SCORE > AWAY_SCORE) AND (AWAY_ID=TEAM_ID)),0) ) as PRZEGRANE, ( NVL( (select count(*) from GAMES where (HOME_SCORE = AWAY_SCORE) AND (HOME_ID=TEAM_ID)),0) + NVL( (select count(*) from GAMES where (HOME_SCORE = AWAY_SCORE) AND (AWAY_ID=TEAM_ID)),0) ) as REMISY, ( ( NVL(( select count(*) from GAMES where ( HOME_SCORE > AWAY_SCORE ) AND ( HOME_ID=TEAM_ID ) ),0)*3 ) + ( NVL(( select count(*) from GAMES where ( HOME_SCORE < AWAY_SCORE ) AND ( AWAY_ID=TEAM_ID ) ),0)*3 ) + ( NVL(( select count(*) from GAMES where ( HOME_SCORE = AWAY_SCORE ) AND ( AWAY_ID=TEAM_ID ) ),0) ) + ( NVL(( select count(*) from GAMES where ( HOME_SCORE = AWAY_SCORE ) AND ( HOME_ID=TEAM_ID ) ),0) ) ) as PUNKTY from TEAMS order by PUNKTY desc

Kod programu w pierwszej kolejności generuje liczbę meczy, sumując mecze, w których gra dana drużyna u siebie, oraz na wyjeździe. Dodatkowo sprawdza czy mecz się odbył.

Następnie wylicza bramki strzelone, stracone, jako sumę pól odpowiedzialnych za wynik.

Liczbę meczy wygranych, oraz przegranych (tam gdzie pole bramki drużyny jest większe od pola bramki rywale)

Na sam koniec wylicza punkty, mnożąc, przez 3 jeżeli bramki są większe któreś z drużyn, oraz dodając, 1 gdy bramki są sobie równe (remis)

TERMINARZ:

Rezultat:

Tworzymy stronę `report’, jako kod strony podajemy:

select "GAMES"."MATCH_ID" as "MATCH_ID", "TEAMS"."TEAM_NAME" as "TEAM_NAME", "TEAMS_1"."TEAM_NAME" as "TEAM1_NAME", "GAMES"."HOME_SCORE" as "HOME_SCORE", "GAMES"."AWAY_SCORE" as "AWAY_SCORE", "GAMES"."MATCH_DATE" as "MATCH_DATE", "GAMES"."FIXTURE" as "FIXTURE" from "TEAMS" "TEAMS_1", "TEAMS" "TEAMS", "GAMES" "GAMES" where "GAMES"."AWAY_ID"="TEAMS_1"."TEAM_ID"(+) and "GAMES"."HOME_ID"="TEAMS"."TEAM_ID"(+) order by FIXTURE asc

Zaznaczamy dodatkowo opcję interaktywny, dzięki czemu użytkownik może wyszukiwać odpowiedni element, sortować według potrzebnych do uzyskania danych. Ustawieniem podstawowym jest sortowanie po kolejce, w której odbył się mecz.

STATYSTYKI:

Rezultat:

select TEAM_NAME, NVL(( select count(*) from GAMES where ( HOME_SCORE = 0 ) AND ( HOME_ID=TEAM_ID ) ),0) as CZYSTE_KONTO_GOSP, NVL(( select count(*) from GAMES where ( HOME_SCORE = 0 ) AND ( HOME_ID=TEAM_ID ) ),0) as CZYSTE_KONTO_GOSC, NVL(( select count(*) from GAMES where ( HOME_SCORE > 3 ) AND ( HOME_ID=TEAM_ID ) ),0) as WIECEJ_3_GOSP, NVL(( select count(*) from GAMES where ( AWAY_SCORE > 3 ) AND ( AWAY_ID=TEAM_ID ) ),0) as WIECEJ_3_GOSC, NVL(( select count(*) from GAMES where ( HOME_SCORE < 3 ) AND ( HOME_ID=TEAM_ID ) ),0) as MNIEJ_3_GOSP, NVL(( select count(*) from GAMES where ( AWAY_SCORE < 3 ) AND ( AWAY_ID=TEAM_ID ) ),0) as MNIEJ_3_GOSC, NVL(( select count(*) from GAMES where ( HOME_ID=TEAM_ID ) AND ( HOME_SCORE >=0 ) AND ( HOME_ID=TEAM_ID ) ),0) as MECZ_DOM, NVL(( select count(*) from GAMES where ( AWAY_ID=TEAM_ID ) AND ( AWAY_SCORE >=0 ) AND ( AWAY_ID=TEAM_ID ) ),0) as MECZ_WYJAZD, ( ROUND(NVL( (select count(*) from GAMES where (HOME_SCORE > AWAY_SCORE) AND (HOME_ID=TEAM_ID)),0) / ( NVL( (select count(*) from GAMES where (HOME_ID=TEAM_ID) AND (HOME_SCORE>=0)),0) + NVL( (select count(*) from GAMES where (AWAY_ID=TEAM_ID) AND (AWAY_SCORE>=0)),1) )*100,2) ) as "WYGRANE-PRZEGRANE_%" from TEAMS order by "WYGRANE-PRZEGRANE_%" desc

Kod ten wylicza mecze, w których drużyna nie straciła bramki u siebie, nie straciła bramki na wyjeździe, strzeliła więcej niż 3 bramki u siebie, więcej niż 3 bramki na wyjeździe, mniej niż 3 bramki u siebie i na wyjeździe, ile meczy rozegrała u siebie, ile meczy rozegrała na wyjeździe, oraz wylicza stosunek meczy wygranych do przegranych w procentach, zaokrąglając wynik.

Sortowanie odbywa się po stosunku wygranych do przegranych

WPISZ WYNIK:

Po naciśnięciu na przycisk WPISZ WYNIK, zostajemy przeniesieni do panelu logowania, gdzie sprawdzana jest nazwa kapitana oraz jego hasło.

Jest to zabezpieczenie wynikające z odpowiednich przekierowań

Naciskając przycisk ‘WPISZ WYNIK’ jesteśmy przekierowani na stronę z numerem 3 (jest to strona, pod którą znajduje się formularz do wpisywania wyniku), jest to jednak strona zabezpieczona, wymagająca autoryzacji konta:

Po zalogowaniu się dostajemy dostęp do edycji naszej tabeli:

select * from (select "TEAMS"."TEAM_NAME" as "HOME", "TEAMS_1"."TEAM_NAME" as "AWAY", "GAMES"."HOME_ID" as "ID", GAMES.MATCH_ID, "GAMES"."HOME_SCORE" as "HOME_SCORE", "GAMES"."AWAY_SCORE" as "AWAY_SCORE", "GAMES"."FIXTURE" as "FIXTURE", "GAMES"."MATCH_DATE" as "DATA" from "TEAMS" "TEAMS_1", "GAMES" "GAMES", "TEAMS" "TEAMS" where "TEAMS"."TEAM_ID"(+) ="GAMES"."HOME_ID" and "TEAMS_1"."TEAM_ID"(+) ="GAMES"."AWAY_ID" ) where ID = (select CAPTAIN_ID from CAPTAIN where upper(CAPTAIN_NAME) = upper(:APP_USER)) or upper(:APP_USER) = 'SPORT' order by FIXTURE

Po kliknięciu na edycję zostajemy przeniesieni do następnej strony

gdzie wpisujemy wynik i zatwierdzamy

UŻYTKOWNICY:

Tworzymy użytkowników wykorzystując wbudowany panel administratora, dodajemy użytkowników w postaci: nazwa (nazwa drużyny), oraz hasło (nazwa drużyny). Dodatkowo stworzony automatycznie jest użytkownik SPORT, który jest administratorem strony i ma dostęp do wszystkich możliwych edycji aplikacji.

Dodatkowo stworzono tablicę CAPTAIN, w której zapisywany jest numer ID kapitana oraz nazwa klubu, którego kapitanem jest.

Nazwa kapitana jest taka sama jak nazwa klubu, którego kapitanem jest ( wymagane przy wpisywaniu wyników tylko swojej drużyny).

Taka opcja pozwala na stworzenie dodatkowego użytkownika dla danej drużyny (w sytuacji, kiedy kapitan nie jest w stanie wprowadzić swojego wyniku). Można również pobierać ID drużyny, jednak wtedy przy chęci stworzenia dodatkowego użytkownika np. legia2 jesteśmy zmuszeni przerobić tablice.

Dodatkowo stworzona została druga aplikacja, jako osobna liga, w której niezależnie odbywają się inne mecze.

WNIOSKI:

1. Stworzenie 2 tablic pozwala na edycję nazw drużyn (w przypadku awansu lub spadku) bez konieczności zmiany terminarza oraz pozostałych stron

2. Stworzenie dodatkowej tablicy kapitanowie pozwala na stworzenie dodatkowego użytkownika, który może wprowadzić wynik za kapitana (przykładowo, gdy jest on na zgrupowaniu reprezentacji i w meczu nie uczestniczy)

3. Stworzenie lig wiąże się z brakiem uniwersalności z terminarzem. Dodając kolumnę „liga” zmuszeni jesteśmy w stanie zrobić dodatkowe zbędne wiersze w tablicy terminarz

4. Przełączanie aplikacji odbywa się również z zabezpieczeniami.

5. Wpisywanie wyniku jest zabezpieczone, tak, aby użytkownik nie mógł wpisać niepoprawnego wyniku (np. ujemnego)

6. Dodatkowo można stworzyć panel administratora do wpisywania terminarzu, jednak jest on generowany w sposób dynamiczny z zewnątrz, i zabezpieczony przez usunięcie wierszy, w których pola gospodarza oraz gościa są takie same.

BIBLIOGRAFIA:

• http://ekstraklasa.net/ekstraklasa/tabela/ - wykorzystano tabelę końcową ekstraklasy polskiej

• http://st-curriculum.oracle.com/tutorial/DBXETutorial/index.htm - wykorzystano elementy ukazujące obsługę programu oraz jego podstawowych funkcji

• http://andrzejklusiewicz.blogspot.com/2010/11/kurs-oracle-plsql.html - zaczerpnięto informację o pisaniu procedury, oraz ich wywoływaniu w APEXIE