08.71.31 Gagnasafnsfræði, Fall 2007
Project 1
Due: Monday october 1st, 2007
Description
The Nerd Association of Iceland (NAI) has decided to hold a soccer tournament to determine once and for all which nerds are best in soccer. As NAI consists of people with considerable knowledge of computers the idea soon came up to build a database for the information associated with the tournament. As the NAI knew that a course is being taught on Databases at the University of Iceland it was perfect to have the students there design the database and implement it in PostgreSQL.
We sat down with representatives from NAI and got the following information on things to keep in mind when designing the database:
Players are identified by kennitala, other data is name, position, gender anf birthday.
By position we mean which position they play on the field. Even though a player can play more than one position we only store the one that he usually playes.
Teams are identified by their name, other data is stadium and town.
Referees are identified by kennitala, other data is their names and birthday.
Each player belongs to one team and each team can have many players.
Games are played between two teams and and the games are identified by a gamenumber. Other data is the date of the game, stadium and the results.
For each game we need to store which players from each team played the game and what position they played int and what number they had on their shirt.
For each game we need to store what players were substituted, on what minute that happened and which player came on. Also we need to store who was the team captain (note that a captain can be substituted and then we need to store who took over as captain).
For each game we need to store the goals scored, which player scored the goal, for which team and on what minute.
For each game we need to store any cards that players got, the color of the card (yellow or red), which player got the card and on what minute.
For each game we need to store which referees worked the game and in what role the referee was (referee, assistant referee, etc.).
The project
- Show a entity-relationship model that fits the information above. Justify your decisions.
- Implement the database in PostgreSQL. Hand in the SQL statements that build the database.
- Below are a few queries that the NAI wants to be able to do on the database.
Note: You need to input the test data at the back of this document to be able to test if the queries work correctly
Hand in the SQL code for each query and what it gave on the test data.
Do four of the following queries:
Which player or players have scored the most number of goals
Which player or players have gotten the most number of cards
Which referee or referees have issued the most number of cards
Average age of the players in each team
Which team or teams make the most number of draws
Which team uses on the average the most number of players in its games
Do two of the following queries:
What is the average number of goals in the team's games, i.e. how many goals are on the average scored in the games of Bitarnir, how many goals on average in the games of Nördarnir, etc.
Players in which position score the most number of goals (Framherji, Miðjumaður, Hægri kantmaður, etc.)
How many goals does each referee issue on the average in each game. Note that it is only the main referee of the game that issues cards, not the assistant referees.
Test data
Teams:
Bitarnir, Reykjavík, Laugardalsvöllur
Númer / Nafn / Kennitala / Staða / Kyn1 / Árni Snær Ólafsson / 160891-3279 / Markvörður / karl
22 / Snorri Eldjárn Hauksson / 050291-3679 / Markvörður / karl
10 / Andri Rafn Yeoman / 180492-2169 / Framherji / karl
11 / Davið Ásbjörnsson / 240292-2389 / Framherji / karl
23 / Margrét Lára Viðarsdóttir / 210991-2989 / Framherji / kona
6 / Zlatko Krikic / 290192-3749 / Miðjumaður / karl
7 / Jón Heiðar Magnússon / 050291-3409 / Miðjumaður / karl
25 / Aðalsteinn Jóhann Friðriksson / 090691-2959 / Miðjumaður / karl
2 / Kristinn Freyr Sigurðsson / 251291-2849 / Vinstri bakvörður / karl
24 / Aron Freyr Eiriksson / 030891-2289 / Vinstri bakvörður / karl
26 / Þórir Guðjónsson / 070491-2659 / Hægri bakvörður / karl
3 / Hafsteinn Briem / 280291-2859 / Hægri bakvörður / karl
5 / Sigurbergur Elísson / 100692-3849 / Vinstri kantur / karl
27 / Sigurður Kristjánsson / 051091-2999 / Vinstri kantur / karl
4 / Guðmundur Magnússon / 100691-2809 / Miðvörður / karl
21 / Egill Jónsson / 150291-2169 / Miðvörður / karl
9 / Ólafur Karl Finsen / 300392-3349 / Hægri kantur / karl
8 / Haukur Örn Harðarson / 180491-2019 / Hægri kantur / karl
Nördarnir, Kópavogur, Fífan
1 / Ásgeir Þór Magnússon / 220591-2209 / Markvörður / karl22 / Arnar Darri Pétursson / 160391-2199 / Markvörður / karl
3 / Vilhjálmur Darri Einarsson / 060591-2109 / Hægri bakvörður / karl
23 / Davíð Már Stefánsson / 291091-3509 / Hægri bakvörður / karl
4 / Finnur Orri Margeirsson / 080391-2509 / Miðvörður / karl
2 / Einar Þórmundsson / 240591-2789 / Miðvörður / karl
26 / Gísli Páll Helgason / 080491-2209 / Miðvörður / karl
6 / Garðar Ingi Leifsson / 050691-3149 / Miðjumaður / karl
7 / Brynjar Gauti Guðjónsson / 270292-3199 / Miðjumaður / karl
27 / Stefán Ragnar Guðlaugsson / 190391-2999 / Miðjumaður / karl
9 / Brynjar Ásgeir Guðmundsson / 220692-2139 / Vinstri kantur / karl
29 / Einar Logi Einarsson / 241191-2659 / Vinstri kantur / karl
8 / Brynjar Kristmundsson / 190592-2009 / Hægri kantur / karl
28 / Andri Ólafsson / 220491-2270 / Hægri kantur / karl
10 / Otto Reynisson / 110291-3029 / Framherji / karl
11 / Andri Fannar Stefánsson / 220491-2269 / Framherji / karl
24 / Gísli Freyr Ragnarsson / 090591-3209 / Framherji / karl
5 / Arnar Sveinn Geirsson / 300891-4119 / Vinstri bakvörður / karl
25 / Ragnar Leósson / 200391-3499 / Vinstri bakvörður / karl
Vafrarar, Eskifjörður, Hallormsvollur
1 / Bjarki Páll Eysteinsson / 160887-3179 / Markvörður / karl2 / Björn Másson / 040290-3479 / Vinstri bakvörður / karl
3 / Casey Benjamin Jones / 190488-2869 / Hægri bakvörður / karl
4 / Daði Kristjánsson / 240390-2289 / Miðvörður / karl
5 / Daníel Laxdal / 201078-2989 / Miðvörður / karl
6 / Elvar Freyr Arnþórsson / 290180-3789 / Miðjumaður / karl
7 / Frank Michael Posch / 080891-1409 / Miðjumaður / karl
8 / Guðjón Baldvinsson / 190790-2059 / Hægri kantur / karl
9 / Harpa Þorsteinsdóttir / 251285-2859 / Vinstri kantur / kona
10 / Guðni Páll Viktorsson / 041089-2299 / Framherji / karl
11 / Halldór Orri Björnsson / 070489-5659 / Framherji / karl
12 / Jóhann Björn Sveinbjörnsson / 290391-2859 / Miðvörður / karl
13 / Jóhann Laxdal / 170682-3849 / Vinstri bakvörður / karl
14 / Jónmundur Grétarsson / 051181-2699 / Hægri bakvörður / karl
15 / Magnús Björgvinsson / 100686-2809 / Markvörður / karl
16 / Magnús Þormar / 150588-2269 / Miðjumaður / karl
17 / Mustafa Kalkan / 300386-3349 / Framherji / karl
18 / Rögnvaldur Már Helgason / 180487-2819 / Miðvörður / karl
Bloggarar, Reykjavík, Árbæjarvöllur
1 / Aðalsteinn Arnarson / 081690-3729 / Markvörður / karl2 / Davíð Logi Gunnarsson / 020588-3769 / Vinstri bakvörður / karl
3 / Einar Örn Einarsson / 040490-2619 / Miðvörður / karl
4 / Elinbergur Sveinsson / 020286-2839 / Hægri bakvörður / karl
5 / Fannar Þór Arnarsson / 211189-2899 / Vinstri kantur / karl
6 / Freyr Alexandersson / 300185-3479 / Hægri kantur / karl
7 / Guðlaugur Andri Axelsson / 050289-3409 / Miðjumaður / karl
8 / Gunnar Jarl Jónsson / 090691-2960 / Miðjumaður / karl
9 / Halldór Kristinn Halldórsson / 251283-2489 / Miðvörður / karl
10 / Helgi Óttarr Hafsteinsson / 040988-2829 / Framherji / karl
11 / Helgi Pétur Jóhannsson / 040779-2659 / Framherji / karl
12 / Hilmar Trausti Arnarsson / 220380-2589 / Markvörður / karl
13 / Jakob Spangsberg Jensen / 110682-3489 / Hægri kantur / karl
14 / Kristján Páll Jónsson / 151084-2989 / Vinstri kantur / karl
15 / Orri Rúnarsson / 160686-2889 / Miðvörður / karl
16 / Sif Atladóttir / 171288-2229 / Framherji / kona
17 / Steinarr Guðmundsson / 300489-3349 / Framherji / karl
18 / Sævar Ólafsson / 180587-2919 / Vinstri bakvörður / karl
19 / Tómas Michael Reynisson / 061087-2999 / Hægri bakvörður / karl
20 / Valur Gunnarsson / 250488-3349 / Miðjumaður / karl
Hakkarar, Hafnarfjörður, Actavis Leikvangur.
1 / Andri Bergmann Þórhallsson / 230580-2109 / Markvörður / karl2 / Andri Hjörvar Albertsson / 170381-2399 / Miðvörður / karl
3 / Andri Valur Ívarsson / 070582-2409 / Miðvörður / karl
4 / Andri Þór Magnússon / 301078-4509 / Miðvörður / karl
5 / Gísli Már Magnússon / 090377-2519 / Miðjumaður / karl
6 / Guðmundur Atli Steinþórsson / 230579-2689 / Miðjumaður / karl
7 / Halldór Hermann Jónsson / 090476-2309 / Vinstri kantur / karl
8 / Haukur Ingvar Sigurbergsson / 040675-3149 / Hægri kantur / karl
9 / Ingi Steinn Freysteinsson / 280277-3199 / Framherji / karl
10 / Ingi Þór Þorsteinsson / 180380-2999 / Framherji / karl
11 / Ingvar Rafn Stefánsson / 230678-2139 / Framherji / karl
12 / Jens Elvar Sævarsson / 251180-2659 / Markvörður / karl
13 / Jóhann Ingi Jóhannsson / 200581-4109 / Framherji / karl
14 / Jóhann Ragnar Benediktsson / 210476-2269 / Framherji / karl
15 / Jón Gunnar Eysteinsson / 120279-4029 / Miðjumaður / karl
16 / Srdjan Rajkovic / 230479-2269 / Hægri bakvörður / karl
17 / Eyrún Guðmundsdóttir / 080577-3209 / Vinstri bakvörður / karl
18 / William Geir Þorsteinsson / 290874-4219 / Miðvörður / karl
19 / Örn Kató Hauksson / 210374-3589 / Miðjumaður / karl
Referees :
Garðar Örn Hinriksson / 100464-2389Gunnar Sverrir Gunnarsson / 010268-3879
Hákon Þorsteinsson / 180470-1179
Kári Gunnlaugsson / 250369-3379
Árni Páll Hafþórsson / 220972-2989
Barði Páll Böðvarsson / 230273-4549
Björn Ingi Jónsson / 070565-3209
Hörður Jóhannsson / 100566-4959
Páll Óli Ólason / 251274-2749
Stefán Ragnar Guðlaugsson / 040770-3289
Games:
Bitarnir– Nördarnir 3:1 (2:0)
Margrét Lára Viðarsdóttir (26)Zlatko Krikic (40)
Guðmundur Magnússon (77)
-
Zlatko Krikic (útaf) - Guðmundur Magnússon (inná) (70)
-
Zlatko Krikic (55) / Brynjar Ásgeir Guðmundsson (56)
-
Otto Reynisson (útaf) – Gísli Páll Helgason (inná) (39)
Brynjar Ásgeir Guðmundsson (útaf) - Einar Logi Einarsson(inná) (85)
-
Einar Þórmundsson (32)
Einar Þórmundsson (39)
Referees:
Garðar Örn HinrikssonGunnar Sverrir Gunnarsson (lína)
Hákon Þorsteinsson (lína)
Kári Gunnlaugsson (eftirlit)
Bloggarar – Vafrarar 2:2 (2:0)
Gunnar Jarl Jónsson (3)Sif Atladóttir (15)
-
Sif Atladóttir (útaf) – Halldór Kristinn Halldórsson (75)
Davíð Logi Gunnarsson (útaf) – Orri Rúnarsson (inná) (45)
-
Davíð Logi Gunnarsson (26)
Aðalsteinn Arnarson (35)
Orri Rúnarsson (64)
Halldór Kristinn Halldórsson (83) / Guðjón Baldvinsson (56)
Jóhann Laxdal (86)
-
Rögnvaldur Már Helgason (útaf) - Daði Kristjánsson (inná) (50)
-
Rögnvaldur Már Helgason (32)
Jóhann Laxdal (45)
Frank Michael Posch (72)
Referees:
Björn Ingi JónssonHörður Jóhannsson (lína)
Páll Óli Ólason (lína)
Stefán Ragnar Guðlaugsson (eftirlit)
Hakkarar – Nördarnir2:3(0:3)
Srdjan Rajkovic (53)Jóhann Ingi Jóhannsson (75)
-
Srdjan Rajkovic (útaf) - Andri Þór Magnússon (inná) (60)
Jóhann Ingi Jóhannsson (útaf) - Jóhann Ragnar Benediktsson (inná) (80)
- / Brynjar Ásgeir Guðmundsson (5)
Brynjar Ásgeir Guðmundsson (9)
Brynjar Ásgeir Guðmundsson (26)
-
Brynjar Ásgeir Guðmundsson (útaf) - Gísli Páll Helgason (inná) (80)
-
Referees:
Garðar Örn HinrikssonGunnar Sverrir Gunnarsson (lína)
Björn Ingi Jónsson (lína)
Hörður Jóhannsson (eftirlit)
Vafrarar – Bitarnir1:5(1:0)
Magnús Þormar (26)-
Daníel Laxdal (útaf) - Mustafa Kalkan (inná) (70)
-
Magnús Þormar (26) / Margrét Lára Viðarsdóttir (46)
Margrét Lára Viðarsdóttir (50)
Guðmundur Magnússon (65)
Guðmundur Magnússon (70)
Margrét Lára Viðarsdóttir (89)
-
Margrét Lára Viðarsdóttir(útaf) - Andri Rafn Yeoman (inná) 90
-
Margrét Lára Viðarsdóttir (32)
Referees:
Barði Páll BöðvarssonBjörn Ingi Jónsson (lína)
Hörður Jóhannsson (lína)
Páll Óli Ólason (eftirlit)
Bloggarar – Hakkarar 2:2(2:2)
Gunnar Jarl Jónsson (26)Jakob Spangsberg Jensen (34)
-
-
Helgi Óttarr Hafsteinsson (39) / Andri Þór Magnússon (13)
Andri Þór Magnússon (37)
-
Jóhann Ingi Jóhannsson (útaf) - Andri Valur Ívarsson (inná) (39)
-
Andri Þór Magnússon (39)
Referees:
Gunnar Sverrir GunnarssonHákon Þorsteinsson (lína)
Kári Gunnlaugsson (lína)
Árni Páll Hafþórsson (eftirlit)