-
Notifications
You must be signed in to change notification settings - Fork 1
/
CreateDB.sql
91 lines (82 loc) · 3.24 KB
/
CreateDB.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
CREATE TABLE Users (
userId INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
email VARCHAR(63) NOT NULL UNIQUE,
passHash VARCHAR(64),
firstName VARCHAR(31),
lastName VARCHAR(31),
active BOOLEAN
);
CREATE TABLE Teams (
teamId INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
name VARCHAR(63) UNIQUE,
capId INTEGER,
CONSTRAINT Teams_capId_fk FOREIGN KEY (capId) REFERENCES Users(userId)
);
CREATE TABLE Disciplines (
discId INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
discName VARCHAR(31) UNIQUE
);
CREATE TABLE TournamentModes (
modeId INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
modeName VARCHAR(31) UNIQUE
);
CREATE TABLE Tournaments (
tourId INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
name VARCHAR(63) UNIQUE,
startingDate DATE,
endingDate DATE,
adminId INTEGER NOT NULL,
modeId INTEGER,
discId INTEGER,
TeamSize INTEGER,
finished BOOLEAN,
CONSTRAINT Tournaments_adminId_fk FOREIGN KEY (adminId) REFERENCES Users(userId),
CONSTRAINT Tournaments_modeId_fk FOREIGN KEY (modeId) REFERENCES TournamentModes(modeId),
CONSTRAINT Tournaments_discId_fk FOREIGN KEY (discId) REFERENCES Disciplines(discId)
);
CREATE TABLE Matches (
matchId INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
tourId INTEGER NOT NULL,
matchDate DATE,
finished BOOLEAN,
winner INTEGER,
team1Id INTEGER,
team2Id INTEGER,
CONSTRAINT Matches_tourId_fk FOREIGN KEY (tourId) REFERENCES Tournaments(tourId),
CONSTRAINT Matches_winner_fk FOREIGN KEY (winner) REFERENCES Teams(teamId),
CONSTRAINT Matches_Team1Id_fk FOREIGN KEY (team1Id) REFERENCES Teams(teamId),
CONSTRAINT Matches_Team2Id_fk FOREIGN KEY (team2Id) REFERENCES Teams(teamId)
);
CREATE TABLE PlayersInTeams (
teamId INTEGER NOT NULL,
userId INTEGER NOT NULL,
joinDate DATE,
CONSTRAINT PlayersInTeams_pk PRIMARY KEY (teamId, userId),
CONSTRAINT PlayersInTeams_teamId_fk FOREIGN KEY (teamId) REFERENCES Teams(teamId),
CONSTRAINT PlayersInTeams_userId_fk FOREIGN KEY (userId) REFERENCES Users(userId)
);
CREATE TABLE TeamsInTournaments (
tourId INTEGER NOT NULL,
teamId INTEGER NOT NULL,
joinDate DATE,
points SMALLINT,
eliminated BOOLEAN,
groupNr SMALLINT,
CONSTRAINT TeamsInTournaments_pk PRIMARY KEY (tourId, teamId),
CONSTRAINT TeamsInTournaments_tourId_fk FOREIGN KEY (tourId) REFERENCES Tournaments(tourId),
CONSTRAINT TeamsInTournaments_teamId_fk FOREIGN KEY (teamId) REFERENCES Teams(teamId)
);
CREATE TABLE UsersInDisciplines (
userId INTEGER NOT NULL,
discId INTEGER NOT NULL,
CONSTRAINT UsersInDisciplines_pk PRIMARY KEY (userId, discId),
CONSTRAINT UsersInDisciplines_userId_fk FOREIGN KEY (userId) REFERENCES Users(userId),
CONSTRAINT UsersInDisciplines_discId_fk FOREIGN KEY (discId) REFERENCES Disciplines(discId)
);
CREATE TABLE TeamsInDisciplines (
teamId INTEGER NOT NULL,
discId INTEGER NOT NULL,
CONSTRAINT TeamsInDisciplines_pk PRIMARY KEY (teamId, discId),
CONSTRAINT TeamsInDisciplines_teamId_fk FOREIGN KEY (teamId) REFERENCES Teams(teamId),
CONSTRAINT TeamsInDisciplines_discId_fk FOREIGN KEY (discId) REFERENCES Disciplines(discId)
);