fPUty665

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