137 lines
4.2 KiB
SQL
137 lines
4.2 KiB
SQL
CREATE TABLE organizer (
|
|
id_organizer SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
logo VARCHAR(1024)
|
|
);
|
|
|
|
CREATE TABLE judge (
|
|
id_judge SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
surname VARCHAR(100) NOT NULL,
|
|
patronymic VARCHAR(100) NOT NULL,
|
|
category VARCHAR(100) NOT NULL,
|
|
region VARCHAR(100),
|
|
federation VARCHAR(100)
|
|
);
|
|
|
|
CREATE TABLE sportsman (
|
|
id_sportsman SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
surname VARCHAR(100) NOT NULL,
|
|
patronymic VARCHAR(100),
|
|
gender VARCHAR(10) NOT NULL,
|
|
birthday DATE,
|
|
region VARCHAR(100),
|
|
club VARCHAR(100),
|
|
federation VARCHAR(100),
|
|
category VARCHAR(100)
|
|
);
|
|
|
|
CREATE TABLE division (
|
|
id_division SERIAL PRIMARY KEY,
|
|
title VARCHAR(100) NOT NULL,
|
|
gender VARCHAR(10) NOT NULL,
|
|
bow_type VARCHAR(100) NOT NULL,
|
|
distance SMALLINT NOT NULL,
|
|
msmk SMALLINT NOT NULL,
|
|
ms SMALLINT NOT NULL,
|
|
kms SMALLINT NOT NULL,
|
|
category_1 SMALLINT NOT NULL,
|
|
category_2 SMALLINT NOT NULL,
|
|
category_3 SMALLINT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE competition (
|
|
id_competition SERIAL PRIMARY KEY,
|
|
title VARCHAR(100) NOT NULL,
|
|
address VARCHAR(100),
|
|
logo VARCHAR(1024),
|
|
start_date DATE,
|
|
end_date DATE,
|
|
id_organizer INTEGER,
|
|
FOREIGN KEY (id_organizer) REFERENCES organizer(id_organizer)
|
|
);
|
|
|
|
CREATE TABLE division_in_competition (
|
|
id_division_in_competition SERIAL PRIMARY KEY,
|
|
id_division INTEGER NOT NULL,
|
|
id_competition INTEGER NOT NULL,
|
|
FOREIGN KEY (id_division) REFERENCES division(id_division),
|
|
FOREIGN KEY (id_competition) REFERENCES competition(id_competition)
|
|
);
|
|
|
|
CREATE TABLE protocol (
|
|
id_protocol SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
date DATE NOT NULL,
|
|
file VARCHAR(1024) NOT NULL,
|
|
id_competition INTEGER NOT NULL,
|
|
FOREIGN KEY (id_competition) REFERENCES competition(id_competition)
|
|
);
|
|
|
|
CREATE TABLE participant_request (
|
|
id_participant_request SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
surname VARCHAR(100) NOT NULL,
|
|
patronymic VARCHAR(100),
|
|
gender VARCHAR(10) NOT NULL,
|
|
birthday DATE,
|
|
region VARCHAR(100),
|
|
club VARCHAR(100),
|
|
federation VARCHAR(100),
|
|
category VARCHAR(100),
|
|
is_registered BOOLEAN NOT NULL,
|
|
id_competition INTEGER NOT NULL,
|
|
id_sportsman INTEGER NOT NULL,
|
|
id_division INTEGER NOT NULL,
|
|
FOREIGN KEY (id_competition) REFERENCES competition(id_competition),
|
|
FOREIGN KEY (id_sportsman) REFERENCES sportsman(id_sportsman),
|
|
FOREIGN KEY (id_division) REFERENCES division(id_division)
|
|
);
|
|
|
|
CREATE TABLE competition_stage (
|
|
id_competition_stage SERIAL PRIMARY KEY,
|
|
title VARCHAR(100) NOT NULL,
|
|
count_of_series SMALLINT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE result_in_stage (
|
|
id_result_in_stage SERIAL PRIMARY KEY,
|
|
score SMALLINT NOT NULL,
|
|
shield_index SMALLINT NOT NULL,
|
|
target_index CHAR(1) NOT NULL,
|
|
id_participant_request INTEGER NOT NULL,
|
|
id_division INTEGER NOT NULL,
|
|
id_competition_stage INTEGER NOT NULL,
|
|
FOREIGN KEY (id_participant_request) REFERENCES participant_request(id_participant_request),
|
|
FOREIGN KEY (id_division) REFERENCES division(id_division),
|
|
FOREIGN KEY (id_competition_stage) REFERENCES competition_stage(id_competition_stage)
|
|
);
|
|
|
|
CREATE TABLE judge_request (
|
|
id_judge_request SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
surname VARCHAR(100) NOT NULL,
|
|
patronymic VARCHAR(100) NOT NULL,
|
|
category VARCHAR(100) NOT NULL,
|
|
region VARCHAR(100),
|
|
federation VARCHAR(100),
|
|
is_registered BOOLEAN NOT NULL,
|
|
id_competition INTEGER NOT NULL,
|
|
id_judge INTEGER NOT NULL,
|
|
FOREIGN KEY (id_competition) REFERENCES competition(id_competition),
|
|
FOREIGN KEY (id_judge) REFERENCES judge(id_judge)
|
|
);
|
|
|
|
CREATE TABLE shot_series (
|
|
id_shot_series SERIAL PRIMARY KEY,
|
|
score INTEGER NOT NULL,
|
|
photo VARCHAR(1024) NOT NULL,
|
|
id_participant_request INTEGER NOT NULL,
|
|
id_result_in_stage INTEGER NOT NULL,
|
|
id_judge_request INTEGER NOT NULL,
|
|
FOREIGN KEY (id_participant_request) REFERENCES participant_request(id_participant_request),
|
|
FOREIGN KEY (id_result_in_stage) REFERENCES result_in_stage(id_result_in_stage),
|
|
FOREIGN KEY (id_judge_request) REFERENCES judge_request(id_judge_request)
|
|
);
|