VjajKgd6

1DROP TABLE IF EXISTS Teams;
2DROP TABLE IF EXISTS Organizations;
3DROP TABLE IF EXISTS Employers;
4DROP TABLE IF EXISTS Employee_cooperation;
5DROP TABLE IF EXISTS Jobs;
6DROP TABLE IF EXISTS Sponsors;
7DROP TABLE IF EXISTS Sponsors_organizations;
8DROP TABLE IF EXISTS Games;
9DROP TABLE IF EXISTS Regions;
10DROP TABLE IF EXISTS Countries;
11DROP TABLE IF EXISTS Players;
12DROP TABLE IF EXISTS Players_in_teams;
13DROP TABLE IF EXISTS Tournaments;
14DROP TABLE IF EXISTS Teams_into_tournaments;
15
16CREATE TABLE Games();
17CREATE TABLE Regions();
18CREATE TABLE Countries();
19CREATE TABLE Players();
20CREATE TABLE Players_in_teams();
21CREATE TABLE Tournaments();
22CREATE TABLE Teams_into_tournaments();
23
24
25CREATE TABLE Teams(
26    TEAM_ID SERIAL NOT NULL PRIMARY KEY,
27    NAME TEXT NOT NULL,
28    ORGANIZATION_ID INTEGER NOT NULL,
29    FOREIGN KEY (ORGANIZATION_ID) REFERENCES Organizations,
30    GAME_ID INTEGER NOT NULL,
31    FOREIGN KEY (GAME_ID) REFERENCES Games,
32    TOTAL_WIN INT,
33    REGION_ID INTEGER NOT NULL,
34    FOREIGN KEY (REGION_ID) REFERENCES Regions
35);
36
37CREATE TABLE Organizations(
38    ORGANIZATION_ID SERIAL NOT NULL PRIMARY KEY,
39    NAME TEXT NOT NULL,
40    DESCRIPTION TEXT
41);
42
43CREATE TABLE Employers(
44    EMPLOYEE_ID SERIAL NOT NULL PRIMARY KEY,
45    FS_NAME TEXT NOT NULL,
46    SC_NAME TEXT NOT NULL,
47    JOD_ID INTEGER NOT NULL,
48    FOREIGN KEY (JOD_ID) REFERENCES Jobs,
49    ORGANIZATION_ID INTEGER NOT NULL,
50    FOREIGN KEY (ORGANIZATION_ID) REFERENCES Organizations,
51    AGE INT CHECK ( AGE BETWEEN 18 AND 99)
52);
53
54CREATE TABLE Employee_cooperation(
55    COOPERATION_ID SERIAL NOT NULL PRIMARY KEY,
56    EMPLOYEE_ID INTEGER NOT NULL,
57    FOREIGN KEY (EMPLOYEE_ID) REFERENCES Employers,
58    ORGANIZATION_ID INTEGER NOT NULL,
59    FOREIGN KEY (ORGANIZATION_ID) REFERENCES Organizations
60);
61
62CREATE TABLE Jobs(
63    JOB_ID SERIAL NOT NULL PRIMARY KEY,
64    DESCRIPTION TEXT,
65    SALARY BIGINT
66);
67
68CREATE TABLE Sponsors(
69    SPONSOR_ID SERIAL NOT NULL PRIMARY KEY,
70    NAME TEXT NOT NULL,
71    DESCRIPTION TEXT
72);
73
74CREATE TABLE Sponsors_organizations(
75    START_DATE DATE NOT NULL PRIMARY KEY,
76    SPONSOR_ID INTEGER NOT NULL,
77    FOREIGN KEY (SPONSOR_ID) REFERENCES Sponsors,
78    ORGANIZATION_ID INTEGER NOT NULL,
79    FOREIGN KEY (ORGANIZATION_ID) REFERENCES Organizations,
80    END_DATE DATE NOT NULL PRIMARY KEY,
81    BUDGET INTEGER NOT NULL,
82);
83
84
85