· 6 years ago · Oct 29, 2019, 07:30 AM
1drop table if exists request;
2drop table if exists game_event;
3drop table if exists equipment_item;
4drop table if exists Player;
5drop table if exists sponsor;
6drop table if exists team;
7drop table if exists calendar_item;
8drop table if exists game_ground;
9
10create table game_ground(
11 ground_id serial primary key,
12 address text NOT NULL,
13 capacity integer,
14 ground_name text);
15
16create table calendar_item(
17 calitem_id serial primary key,
18 cdate date NOT NULL );
19
20create table team(
21 team_id serial primary key,
22 teamName char(32) NOT NULL,
23 capitan char(32),
24 numMember integer,
25 emblem text,
26 teamDescription text);
27
28create table sponsor(
29 sponsor_id serial primary key,
30 firstName char(32),
31 secondName char(32),
32 company_name char(32),
33 information text,
34 emblem text);
35
36create table Player(
37 player_id serial primary key,
38 login text,
39 ppassword char(32),
40 firstName char(32),
41 secondName char(32),
42 dateOfBirth date,
43 nickname char(32),
44 plocation char(32),
45 photo text,
46 email char(32),
47 registrationDate date,
48 team_id serial references team(team_id)
49 );
50
51create table equipment_item(
52 item_id serial primary key,
53 equipmentType char(32),
54 equipmentName char(32),
55 equipmentPhoto text,
56 player_id serial references Player(player_id)
57 );
58
59create table game_event(
60 game_id serial primary key,
61 gameName char(64),
62 sponsor_id serial references sponsor(sponsor_id),
63 gameTime time without time zone,
64 gameType char(32),
65 ground_id serial references game_ground(ground_id),
66 max_player_number integer,
67 game_cost integer,
68 game_information text,
69 calitem_id serial references calendar_item(calitem_id)
70 );
71
72create table request(
73 request_id serial primary key,
74 request_type char(32),
75 player_id serial references Player(player_id),
76 game_id serial references game_event(game_id)
77);
78
79insert into game_ground values(1, 'Pylkovo-3', 30, 'Пространство активного отдыха Пулково-3');
80insert into game_ground values(2, 'Repino', 80, 'ТАИ');
81insert into game_ground values(3, 'СПб', 40, 'Красная жара');
82
83insert into calendar_item values(1, '2019-11-17');
84insert into calendar_item values(2, '2019-10-30');
85
86insert into team values(1,'N.A.M.A.D.', 'Pank', 8, '', 'team NAMAD');
87
88insert into sponsor values(1, 'Alexander', 'Antonov', 'AAA', 'Первая компания на планете ААА', '');
89
90insert into Player values(1, 'Fynduk', 'qwerty', 'German', 'Duk', '1998-09-15', 'Duker', 'SPB', 'https://sun9-11.userapi.com/c853428/v853428714/110e07/bPMeN_rYKTI.jpg', 'ger-duk@mail.ru', '2018-05-01', 1);
91insert into Player values(2, 'r144yh', 'root', 'Natali', 'Salova', '1998-11-17', 'Popyg', 'SPB', '', 'natali144@yandex.ru', '2019-09-15', 1);
92
93insert into equipment_item values(1, 'helmet', 'Шлемак дяди Пети' , '', 1);
94insert into equipment_item values(2, 'armor', 'TV-110', '', 1);
95
96insert into game_event values(1, 'Захолустье', 1, '20:00:00', 'Ролевой проект', 2, 80, 2500, 'Игра по киберпанку', 1);
97insert into game_event values(2, 'Метро 2033', 1, '12:00:00', 'Ролевой проект', 3, 40, 3000, 'Игра по вселенной Метро 2033', 2);
98
99insert into request values(1, 'Командная заявка', 1, 1);