0gWNihq5

1drop table if exists public.location cascade; 
2create table public.location
3(
4    location_id serial primary key,
5    land_kontinent character varying not null
6);
7 
8
9drop table if exists public.teams cascade; 
10create table public.teams
11(
12    team_id serial primary key,
13    name character varying(50) not null
14    location_id text not null
15    constraint location_id foreign key (location_id)
16);
17
18 drop table if exists hauptattribut cascade; 
19 create table hauptattribut(
20     nr int primary key,
21     name text);
22     
23insert into hauptattribut values (1, 'Stärke');
24insert into hauptattribut values (2, 'Geschicklichkeit');
25insert into hauptattribut values (3, 'Intelligenz');
26 
27drop table if exists angriffsart cascade; 
28 create table angriffsart(
29     nr int primary key,
30     name text);
31     
32insert into hauptattribut values (1, 'Nahkampf');
33insert into hauptattribut values (2, 'Fernkampf');
34
35
36drop table if exists public.held cascade; 
37create table public.held
38(
39    helden_id serial primary key,
40    name character varying(30) not null,
41    hauptattribut integer not null references hauptattribut (nr)
42	angriffsart integer not null references angriffsart (nr)
43);
44 
45
46drop table if exists public.serie cascade; 
47create table public.serie
48(
49    serien_id text primary key,
50    startzeitpunkt timestamp without time zone not null
51);
52 
53drop table if exists public.spiel cascade; 
54create table public.spiel
55(
56    spiel_id text not null,
57    spiellaenge interval(6) not null,
58    serien_id integer not null,
59    team_id integer not null,
60    constraint spiel_pkey primary key (spiel_id),
61    constraint serien_id foreign key (serien_id)
62        references public.serie (serien_id) match simple
63        on update no action
64        on delete no action,
65    constraint team_id foreign key (team_id)
66        references public.teams (team_id) match simple
67        on update no action
68        on delete no action
69);
70 
71
72drop table if exists seite cascade; 
73 create table seite(
74     nr int primary key,
75     name text);
76     
77insert into hauptattribut values (1, 'Dire');
78insert into hauptattribut values (2, 'Radiant');
79
80 drop table if exists public.spielen_in cascade; 
81create table public.spielen_in
82(
83    spiel_id text references spiel,
84    spieler_id integer references spieler,
85    helden_id integer references held,
86    kills integer not null,
87    deaths integer not null,
88    assists integer not null,
89    networth integer not null,
90    lasthits integer not null,
91    denies integer not null,
92    gpm integer not null,
93    xpm integer not null,
94    dmg integer not null,
95    bld integer not null,
96seite integer not null,	
97    primary key (spiel_id, spieler_id),
98    unique (spiel_id, held_id)
99);
100 
101 
102 drop table if exists public.spieler cascade; 
103create table public.spieler
104(
105    spieler_id numeric(3) not null,
106    nickname character varying not null,
107    name character varying(50) not null,
108    geburtsdatum date,
109    rolle character varying(20) not null,
110location_id int not null
111    constraint spieler_pkey primary key (spieler_id)
112);
113 
114 
115
116 drop table if exists public.spielen cascade; 
117create table public.spielen
118(
119    team_id integer not null,
120    serien_id integer not null,
121    constraint spielen_pkey primary key (team_id, serien_id),
122    constraint serien_id foreign key (serien_id)
123        references public.serie (serien_id) match simple
124        on update no action
125        on delete no action,
126    constraint team_id foreign key (team_id)
127        references public.teams (team_id) match simple
128        on update no action
129        on delete no action
130);