Semestr´ aln´ı pr´ ace na X36DB2 Michal Rezler Jan Zm´ atl´ık
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u 8. prosinec 2011
Obsah
1
Zad´ an´ı . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.1 Struˇcn´ y popis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2 ER diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.3 Integritn´ı omezen´ı . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Realizace datab´ aze . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.1 Uˇzivatelsk´ y datov´ y typ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.2 Relace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.3 SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.4 Funkce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.5 Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Realizace programu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ´ 3.1 Uprava vygenerovan´eho k´odu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Dotazy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.1 Mˇeˇren´e charakteristiky . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.2 Dotazy s pˇr´ıkazem EXPLAIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.3 Indexy a pˇr´ıkaz EXPLAIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Pˇr´ılohy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5.1 SQL skript . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Pouˇzit´e zdroje . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3 3 3 4 4 4 4 4 5 5 5 5 5 7 8 8 11 11 14
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u
1
3
Zad´ an´ı
1.1
Struˇ cn´ y popis
C´ılem je vytvoˇren´ı fotbalov´e tabulky stˇrelc˚ u a poˇrad´ı t´ ym˚ u podle bod˚ u vztahuj´ıc´ı se k dan´ ym soutˇeˇz´ım. Eviduj´ı se registrovan´e osoby federace a ty jsou rozdˇeleny do druˇzstev. Registrovan´ a osoba obsahuje jm´eno a pˇr´ıjmen´ı a m˚ uˇze j´ı b´ yt: – Tren´er - u nˇejˇz se eviduje stupeˇ n z´ıskan´e licence – Hr´ aˇc - u nˇejˇz se eviduje, jakou nohu preferuje (prav´a, lev´a) a samozˇrejmˇe branky, kter´e vstˇrelil – Kapit´ an – specializace hr´aˇce, ˇr´ıd´ı t´ ym na hˇriˇsti, obsahuje stupeˇ n respektu a rovnˇeˇz vstˇrelen´e branky Kaˇzd´ y evidovan´ y z´ apas obsahuje dva t´ ymy (dom´ac´ı, host´e) hraj´ıc´ı utk´an´ı, d´ ale m´ısto, kde se utk´ an´ı odehraje a soutˇeˇz, do kter´e utk´an´ı spad´a. Z´aroveˇ n je potˇreba z´ apasy a vstˇrelen´e branky archivovat podle sez´on. To lze zaˇr´ıdit tak, ˇze kaˇzd´e soutˇeˇzi pˇriˇrad´ıme sez´ onu, ve kter´e prob´ıh´a. 1.2
ER diagram
Jako modelovac´ı n´ astroj pro Entitnˇe-Relaˇcn´ı model, jsme vyuˇzili DbDesigner (MySQL WorkBench 5.0.x). E-R model modeluje situaci uvedenou v sekci Popis a nach´ az´ı se v 3NF.
Obr´ azek 1. ER diagram
4
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u
1.3
Integritn´ı omezen´ı
– V tabulce player“ evidujeme hr´aˇcovu lepˇs´ı nohu, tento atribut m˚ uˇze obsa” hovat pouze slova lev´ a“ a prav´a“. ” ” – U kapit´ ana evidujeme jeho respekt v˚ uˇci hr´aˇc˚ um. Tato hodnota nab´ yv´a hodnot 1-10.
2
Realizace datab´ aze
Pro u ´ˇcely t´eto semestr´ aln´ı pr´ace jsme si nejprve zaloˇzili verzovac´ı syst´em SVN, kter´ y je dostupn´ y z URL http://xp-dev.com/svn/db2project/ . Naˇse datab´ aze bude fyzicky uloˇzena na datab´azov´em stroji PostgreSQL, konkr´etnˇe ve verzi 9.1. Veˇsker´e n´ıˇze uveden´e SQL pˇr´ıkazy jsou tedy syntakticky urˇcen´e pr´ avˇe pro tento stroj. 2.1
Uˇ zivatelsk´ y datov´ y typ
V tabulk´ ach place“ a person“ evidujeme adresu (ulice, mˇesto, st´at), pro jej´ıˇz ” ” uloˇzen´ı jsme si definovali sv˚ uj datov´ y typ vytvoˇren´ y n´asledovnˇe: CREATE TYPE address AS ( street VARCHAR(255), city VARCHAR(255), country VARCHAR(255) ); 2.2
Relace
TEAM( idteam, name ) PLACE( idplace, place address, map url ) SEASON( idseason, year from, year to ) LEAGUE ( idleague, season idseason, name ) MATCH ( idmatch, home idteam, away idteam, place idplace, league idleague, season idseason, name ) PERSON ( idperson, firstname, lastname, team id team, person address ) PLAYER ( idplayer, idperson, prefered leg ) COACH ( idcoach, idperson, licence level ) CAPTAIN ( idcaptain, idplayer, idperson, respect ) GOAL ( idgoal, match idmatch, player idplayer ) 2.3
SQL
Vˇsechny SQL pˇr´ıkazy pro vytvoˇren´ı jednotliv´ ych tabulek jsou uvedeny v pˇr´ıloze 5.1. Tato sada SQL pˇr´ıkaz˚ u je plnˇe funkˇcn´ı, pro vytvoˇren´ı kompletn´ı datab´aze ji tedy m˚ uˇzeme rovnou pouˇz´ıt.
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u
2.4
5
Funkce
V datab´ azi jsme si definovali funkci, kter´a n´am pro zad´an´ı jm´ena klubu vr´at´ı poˇcet hr´ aˇc˚ u, kteˇr´ı v klubu hraj´ı. Zdrojov´ y k´od funkce vypad´a n´asledovnˇe: CREATE FUNCTION GET_COUNT_OF_PLAYERS (CLUB_NAME VARCHAR(100)) RETURNS INT AS $$ DECLARE count_of_players INT; BEGIN SELECT COUNT(*) INTO count_of_players FROM TEAM t JOIN PERSON p ON t.idteam = p.team_idteam WHERE t.name = $1 ; RETURN count_of_players; END; $$ LANGUAGE plpgsql; 2.5
Data
Pro naplnˇen´ı datab´ aze daty jsme si vytvoˇrili vlastn´ı gener´ator, kter´ y n´am vygeneruje napˇr. jednotliv´e fotbalov´e t´ ymy, kter´ ych m´ame 1200. Program generuje data ve form´ atu sekvence SQL pˇr´ıkaz˚ u Insert .
3
Realizace programu
Vytvoˇren´ y program demonstruj´ıc´ı komunikaci s datab´az´ı je naps´an v jazyce Java. Pro ORM jsme pouˇzili technologii JPA 2.0, konkr´etnˇe implementaci EclipseLink. Za pouˇzit´ı v´ yvojov´eho prostˇredn´ı NetBeans se n´am vytvoˇrily oanotovan´e tˇr´ıdy reprezentuj´ıc´ı relace v datab´azi. 3.1
´ Uprava vygenerovan´ eho k´ odu
Vygenerovan´ y k´ od nebyl zcela optim´aln´ı a pˇrehledn´ y, a tak jsme jej d´ale modifikovali. Konkr´etnˇe jsme v odpov´ıdaj´ıc´ıch tˇr´ıd´ach upravili dˇediˇcnost (podle strategie InheritanceType.JOINED) a uˇzivatelsk´ y datov´ y typ (adresu) jsme defiovali jako @Embededded, respektive pˇr´ımo tuto tˇr´ıdu jsme opatˇrili anotac´ı @Embeddable. Datab´ azi jsme pot´e znovu pˇregenerovali podle novˇe upraven´eho k´odu v Javˇe. D´ ale jsme do tˇr´ıdy Person pˇridali jeden atribut, pˇred kter´ y jsme pˇridali anotaci @Version. Atribut slouˇz´ı ke kontrole editovan´ ych verz´ı, kter´e prob´ıh´a pˇri pouˇzit´ı optimistick´eho zamyk´an´ı bˇehem operace UPDATE .
4
Dotazy
Dalˇs´ım naˇsim u ´kolem bylo vymyslet nˇekolik sloˇzit´ ych dotaz˚ u, kter´e budeme pozdˇeji optimalizovat. Vytvoˇrili jsme 3 dotazy, pˇriˇcemˇz jsme se snaˇzili, aby se tabulky musely co nejv´ıc spojovat a bylo d´ano co nejv´ıce omezen´ı.
6
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u
1. Vyber a seˇrad’ vˇsechny alb´ansk´e t´ ymy podle poˇctu vstˇrelen´ ych branek v sezonˇe 2011/2012.
SELECT t."name" AS "teamName", count(t."name") AS "goalCount" FROM goal g JOIN "match" m ON m.idmatch = g.match_idmatch JOIN league l ON m.league_idleague = l.idleague JOIN season s ON s.idseason = l.season_idseason JOIN person p ON p.idperson = g.player_idplayer JOIN team t ON t.idteam = p.team_idteam WHERE l."name" = ’Albania’ AND s.year_from = 2011 GROUP BY t."name" ORDER BY count(t."name");
2. Vypiˇs jm´eno a pˇr´ıjmen´ı kapit´ana, kter´ y dal v bˇehem jedn´e sezony ve v´ıce neˇz jednom z´ apase 3 a v´ıce g´ol˚ u.
SELECT p.lastname, p.firstname FROM captain c JOIN person p ON p.idperson = c.idperson JOIN goal g ON g.player_idplayer = c.idperson JOIN "match" m ON m.idmatch = g.match_idmatch JOIN league l ON l.idleague = m.league_idleague JOIN season s ON s.idseason = l.season_idseason WHERE (p.team_idteam = m.home_idteam OR p.team_idteam = m.away_idteam) AND (s.year_from = 2000) GROUP BY p.idperson HAVING count(g.idgoal) > 2 AND count(m.idmatch) > 1; 3. Vyber t´ ym, kter´ y hraje v lize Primera Divisi´on a jehoˇz kapit´an m´a nejvyˇsˇs´ı respekt od spoluhˇr´ aˇc˚ u a souˇcasnˇe je to lev´ak.
SELECT t FROM Person p JOIN Player pl ON p.idperson = pl.idperson JOIN Captain c ON p.idperson = c.idperson JOIN Team t ON t.idteam = p.team_idteam JOIN Goal g ON g.player_idplayer = p.idperson JOIN "match" m ON m.idmatch = g.match_idmatch JOIN League l ON l.idleague = m.league_idleague WHERE c.respect = (
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u
7
SELECT MAX(respect) FROM captain ) AND pl.prefered_leg = ’leva’ AND l."name" = ’Primera Divisi´ on’;
4.1
Mˇ eˇ ren´ e charakteristiky
Pro urˇcen´ı nˇekter´ ych charakteristik jsme si museli zjistit nebo odhadnout nˇekter´e ˇ o velikost datab´azov´eho u ´daje, kter´e jsme v naˇsich v´ ypoˇctech d´ale pouˇz´ıvali. Slo bloku a velikost hlaviˇcky DB bloku. velikost DB bloku = 8 kB = 8192 B hlaviˇcka DB bloku = 500 B Dalˇs´ım parametrem, kter´ ym jsme museli pro kaˇzdou tabulku odhadnout je parametr plnˇen´ı bloku P CT F REE, kter´ y z´avis´ı na variabilitˇe d´elky n-tice dan´e tabulky. Nˇekter´e datab´ azov´e stroje (napˇr. Oracle) pˇr´ımo poskytuje pˇr´ıkaz SELECT PCTFREE ... Z´ aleˇz´ı tedy pˇredevˇs´ım na d´elce atribut˚ u typu V ARCHAR. D´elka atribut˚ u datov´ ych typ˚ u napˇr. IN T se samozˇrejmˇe nemˇen´ı. Pokud tedy m´ a nˇekter´ a tabulka pouze atributy typu IN T a jsou tyto atributy povinn´e, pak P CT F REE = 0. Nˇekter´e charakteristiky (blokovac´ı faktor, poˇcet str´anek potˇrebn´ ych pro naˇcten´ı) jsme museli na z´ akladˇe ostatn´ıch dopoˇc´ıtat. Zde uv´ad´ıme jejich vzoreˇcky: Br =
DB BLOCK SIZE−HEADER SIZE ROW SIZEAV G
∗ (1 − P CT F REE)
Pr = Nr /Br Data jsou v bloku ukl´ ad´ ana vˇzdy tak, ˇze na prvn´ım m´ıstˇe je identifik´ator ˇr´adky (1B), n´ asleduje d´elka atributu (1B) a data atributu.
Tabulka Team Person Match Goal League
Nr ROW SIZEAV G 1073 27 7200 75 40390 35 66584 16 8078 32
Br 228 51,3 220 481 48
Pr P CT F REE 4,7 20% 140 50% 184 0% 138 0% 168 80%
Tabulka 1. Charakteristiky vybran´ ych tabulek
8
4.2
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u
Dotazy s pˇ r´ıkazem EXPLAIN
Analyzovali jsme naˇse 3 dotazy pomoc´ı pˇr´ıkazu EXPLAIN, kter´ y zobraz´ı execution plan kaˇzd´eho z nich. Z´ aroveˇ n jsme je spouˇstˇeli s pˇr´ıkazem ANALYZE, kter´ y jednotliv´e ˇc´ asti execution pl´ anu prov´ad´ı a vr´at´ı jejich ˇcas proveden´ı i celkov´ y ˇcas proveden´ı. Protoˇze v´ ystupy jsou pomˇernˇe rozs´ahl´e, vloˇzili jsme je do pˇriloˇzen´eho souboru explain.sql. 4.3
Indexy a pˇ r´ıkaz EXPLAIN
Po anal´ yze pomoc´ı pˇr´ıkazu EXPLAIN popsan´e v pˇredchoz´ı sekci ˇc. 4.2, jsme se pustili do optimalizace tˇechto dotaz˚ u. Optimalizovali jsme spotˇrebovan´e zdroje (cenu) a ˇcas, za kter´e dotaz probˇehl. Jako dotazy jsme pouˇzili naˇse 3 sloˇzit´e dotazy. Pro optimalizaci jsme zvolili vytvoˇren´ı INDEX˚ u pro potˇrebn´e sloupce. Podrobn´e rozbory dotaz˚ u, ˇcasy a ceny jsou v souborech explain.X.query.txt, kde X je cislo dotazu {1..3}. Pˇred spuˇstˇen´ım dotaz˚ u jsme v konfiguraˇcn´ım souboru PostgreSQL zakomentovali metody pro optimalizaci dotazu. Konkr´etnˇe parametry pro Planner Method Configuration, Planner Cost Constants, Genetic Query Optimizer a ostatn´ı pl´ anovac´ı konstanty. Bohuˇzel se n´am dotazy pˇr´ıliˇs vylepˇsit nepodaˇrilo. Zapnut je moˇzn´ a nˇejak´ y intern´ı optimaliz´ator, kter´ y nejde vypnout(alespoˇ n po hodin´ach hled´ an´ı sme nic nenalezli). Prvn´ı dotaz vybere a seˇrad´ı vˇsechny alb´ansk´e t´ ymy podle poˇctu vstˇrelen´ ych branek v sez´ onˇe 2011/2012. Pracuje se tedy hojnˇe s tabulkou Team a sloupcem Name. Podle sloupce Name tabulky Team se dotaz sortuje, grupuje a figuruje i v podmince WHERE. Pouˇzili jsme tedy INDEX na tento sloupec. D´ale sme zvolili index na sloupec YearsFrom tabulky Goal, kter´ y figuruje v podm´ınce dotazu. Velkou ˇc´ ast dotazu pˇredstavuje JOIN tabulek a agregaˇcn´ı funkce count nad sloupcem Name tabulky Team. Indexy nad agregaˇcn´ımi funkcemi nejsou povoleny a jako sortovac´ı algoritmus byl zvolen quicksort. Zde se tedy moc optimalizovat nedalo. Na zkouˇsku sme zkusili i nˇejak´e INDEXy nad prim´arn´ımi a ciz´ımi kl´ıˇci tabulek, jeˇz se nal´ezaj´ı v JOINech. Pˇred pouˇzit´ım index˚ u se dotaz provedl v ˇcase 20.1 ms. Po pouˇzit´ı index˚ u se pr˚ umˇernˇe prov´ ad´ı v ˇcase 16-17 ms. Pouˇzit´e indexy: CREATE CREATE CREATE CREATE CREATE
INDEX INDEX INDEX INDEX INDEX
league_idx ON league (idleague, name, season_idseason); team_idx ON team (idteam, name); season_idx ON season (idseason, year_from); match_idx ON match (idmatch, league_idleague); goals_idx ON goal (idgoal, match_idmatch, player_idplayer);
Namˇeˇren´e hodnoty po proveden´ı pˇr´ıkazu EXPLAIN prvn´ıho dotazu, jsou uvedeny n´ıˇze.
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u
9
ˇ Operace Cena Cas Sort 1507.08 19.990 HashAggregate 1507.04 19.978 Nested Loop 1507.00 19.958 Nested Loop 1506.13 19.899 Nested Loop 1505.27 19.822 Nested Loop 503.60 4.146 Nested Loop 151.75 1.919 Tabulka 2. Tabulka cen a ˇcas˚ u prvn´ıho dotazu pˇred optimalizac´ı ˇ Operace Cena Cas Sort 1479.62 18.219 HashAggregate 1479.59 18.209 Nested Loop 1479.55 18.189 Nested Loop 1478.68 18.132 Nested Loop 1477.81 18.067 Nested Loop 476.17 3.415 Nested Loop 151.75 1.738 Tabulka 3. Tabulka cen a ˇcas˚ u prvn´ıho dotazu po optimalizaci
Druh´ y dotaz vrac´ı jm´eno a pˇr´ıjmen´ı kapit´ana druˇzstva, jeˇz dal bˇehem jedn´e sez´ ony (2010/2011) v´ıce jak 3 branky ve v´ıce neˇz 1 z´apase. V´ ysledek je tedy projekce sloupc˚ u jm´ena a pˇr´ıjmen´ı tabulky Person. Na tyto sloupce jsme tedy pouˇzili INDEXy. Dotaz opˇet pouˇz´ıv´a agragaˇcn´ı funkce, zkusili jsme ale zda pouˇzit´ı unik´ atn´ıho INDEXu na ID tabulky PERSON zlepˇs´ı pr˚ ubˇeh prov´adˇen´ı dotazu. Nesm´ıme zapomenout, ˇze INDEXy vytvoˇren´e pˇri prvn´ım dotazu, plat´ı i pro druh´ y dotaz. Pˇred pouˇzit´ım index˚ u se dotaz provedl v ˇcase 48.9 ms. Po pouˇzit´ı index˚ u se pr˚ umˇernˇe prov´ ad´ı v ˇcase 35-37 ms. Pouˇzit´e indexy: -- + INDEXy z 1. dotazu CREATE INDEX person_idx ON person (lastname, firstname); CREATE UNIQUE INDEX person_uniq_idx ON person (idperson); Namˇeˇren´e hodnoty po proveden´ı pˇr´ıkazu EXPLAIN druh´eho dotazu, jsou uvedeny n´ıˇze. Tˇret´ı dotaz vrac´ı t´ ym, kter´ y hraje v Primera Divisi´on a jehoˇz kapit´an m´a u sv´ ych spoluhr´ aˇc˚ u nejvyˇsˇs´ı respekt (jako kapit´an je nejlepˇs´ı) a nav´ıc je to lev´ak. V tomto dotazu (kromˇe JOIN˚ u) filtrujeme data podle jm´ena ligy, podle respektu kapit´ ana a velkou roli zde hraje atributy tabulky Player - konkr´etnˇe atributy prefered leg a ciz´ı kl´ıˇc idperson. Na z´akladˇe tˇechto informac´ı jsme volili pouˇzit´ı index˚ u. Definovali jsme n´ asleduj´ıc´ı indexy: CREATE INDEX player_idx ON player (idperson, prefered_leg);
10
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u ˇ Operace Cena Cas HashAggregate 2334.83 48.669 Nested Loop 2334.81 48.169 Hash Join 2272.20 46.221 Hash Join 2236.56 45.167 Hash Join 940.27 18.146 Hash Join 162.29 2.711 Tabulka 4. Tabulka cen a ˇcas˚ u druh´eho dotazu pˇred optimalizac´ı ˇ Operace Cena Cas HashAggregate 2334.83 35.044 Nested Loop 2334.81 34.778 Hash Join 2272.20 33.733 Hash Join 2236.56 32.896 Hash Join 940.27 17.902 Hash Join 162.29 2.704 Tabulka 5. Tabulka cen a ˇcas˚ u druh´eho dotazu po optimalizaci
CREATE CREATE CREATE CREATE
INDEX INDEX INDEX INDEX
captain_idx ON captain (idperson, respect); league_idx ON league (idleague, name); match_idx ON match (idmatch, league_idleague); goals_idx ON goal (player_idplayer);
Namˇeˇren´e hodnoty po proveden´ı pˇr´ıkazu EXPLAIN tˇret´ıho dotazu, jsou uvedeny n´ıˇze. Operace Cena Nested Loop 1730.06 Nested Loop 1709.31 Nested Loop 1602.11 Nested Loop 1484.82 Hash Join 335.79 Hash Join 139.76 Tabulka 6. Tabulka cen a ˇcas˚ u tˇret´ıho
ˇ Cas 88.576 88.293 71.253 59.400 52.767 19.785 dotazu pˇred optimalizac´ı
D´ıky tˇemto index˚ um se sn´ıˇzila cena i pr˚ umˇern´ y ˇcas prov´adˇen´ı tohoto dotazu (pˇri 10-ti spuˇstˇen´ı) z 89ms na 77ms.
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u ˇ Operace Cena Cas Nested Loop 1719.38 133.084 Nested Loop 1698.63 132.750 Nested Loop 1591.53 111.552 Nested Loop 1480.60 91.231 Hash Join 335.79 80.798 Hash Join 139.76 17.228 Tabulka 7. Tabulka cen a ˇcas˚ u tˇret´ıho dotazu po optimalizaci
5 5.1
Pˇ r´ılohy SQL skript
CREATE TYPE address AS ( street VARCHAR(255), city VARCHAR(255), country VARCHAR(255) ); CREATE TABLE team ( idteam SERIAL NOT NULL PRIMARY KEY , name VARCHAR(100) NOT NULL ); CREATE TABLE place ( idplace SERIAL NOT NULL PRIMARY KEY , place_address address, map_url TEXT NULL ); CREATE TABLE season ( idseason SERIAL NOT NULL year_from INT NOT NULL , year_to INT NOT NULL );
PRIMARY KEY,
CREATE TABLE league ( idleague SERIAL NOT NULL UNIQUE , season_idseason INT NOT NULL , name VARCHAR(255) NOT NULL , PRIMARY KEY (idleague, season_idseason) , CONSTRAINT fk_league_season FOREIGN KEY (season_idseason ) REFERENCES season (idseason )
11
12
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u
ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE match ( idmatch SERIAL NOT NULL UNIQUE , home_idteam INT NOT NULL , away_idteam INT NOT NULL , place_idplace INT NOT NULL , league_idleague INT NOT NULL , time TIMESTAMP NOT NULL , PRIMARY KEY (idmatch, home_idteam, away_idteam, place_idplace, league_idleague) , CONSTRAINT fk_match_team FOREIGN KEY (home_idteam ) REFERENCES team (idteam ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_match_team1 FOREIGN KEY (away_idteam ) REFERENCES team (idteam ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_match_place FOREIGN KEY (place_idplace ) REFERENCES place (idplace ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_match_league FOREIGN KEY (league_idleague ) REFERENCES league (idleague ) ON DELETE NO ACTION ON UPDATE NO ACTION ); CREATE TABLE person ( idperson SERIAL NOT NULL PRIMARY KEY, firstname VARCHAR(150) NOT NULL , lastname VARCHAR(100) NOT NULL , team_idteam INT NOT NULL , person_address address NOT NULL , CONSTRAINT fk_person_team FOREIGN KEY (team_idteam ) REFERENCES team (idteam ) ON DELETE NO ACTION ON UPDATE NO ACTION
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u
13
); CREATE TABLE player ( idplayer SERIAL NOT NULL PRIMARY KEY , prefered_leg VARCHAR(10) NOT NULL , CONSTRAINT player_isa FOREIGN KEY (idplayer) REFERENCES person(idperson), CONSTRAINT pref_leg CHECK (prefered_leg = ’lev´ a’ OR prefered_leg = ’prav´ a’) ); CREATE TABLE coach ( idcoach SERIAL NOT NULL PRIMARY KEY , licence_level INT NOT NULL , CONSTRAINT coach_isa FOREIGN KEY (idcoach) REFERENCES person(idperson) ); CREATE TABLE captain ( idcaptain SERIAL NOT NULL PRIMARY KEY, respect INT NOT NULL , CONSTRAINT captain_isa FOREIGN KEY (idcaptain) REFERENCES player(idplayer), CONSTRAINT respect_constraint CHECK (respect > 0 AND respect < 11) ); CREATE TABLE goal ( idgoal SERIAL NOT NULL , match_idmatch INT NOT NULL , player_idplayer INT NOT NULL , PRIMARY KEY (idgoal, match_idmatch, player_idplayer) , CONSTRAINT fk_goal_match FOREIGN KEY (match_idmatch ) REFERENCES match (idmatch ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_goal_player FOREIGN KEY (player_idplayer ) REFERENCES player (idplayer ) ON DELETE NO ACTION ON UPDATE NO ACTION );
14
6
Tabulka fotbalov´e ligy a tabulka stˇrelc˚ u
Pouˇ zit´ e zdroje
Reference ˇ [1] Pokorn´ y, Halaˇska, Datab´ azov´e syst´emy. skripta FEL CVUT, 2. vyd´ an´ı, 2003. [2] PostgreSQL manual http://www.postgresql.org/docs/8.0/static/tutorial.html. online, 8.12.2011. [3] JPA tutori´ al http://docs.oracle.com/javaee/6/tutorial/doc/bnbpz.html. online, 8.12.2011.
