164 lines
4.8 KiB
SQL
Executable File
164 lines
4.8 KiB
SQL
Executable File
CREATE TABLE footballZones (
|
|
zoneID VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
|
|
PRIMARY KEY(zoneID),
|
|
UNIQUE(name)
|
|
)
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE footballChamps (
|
|
champID VARCHAR(255) NOT NULL,
|
|
zoneID VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
|
|
PRIMARY KEY(champID),
|
|
UNIQUE(zoneID, name),
|
|
|
|
FOREIGN KEY (zoneID) REFERENCES footballZones(zoneID)
|
|
)
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
|
|
CREATE TABLE footballTeams (
|
|
teamID VARCHAR(255) NOT NULL,
|
|
zoneID VARCHAR(255) NOT NULL,
|
|
canonicalName VARCHAR(255) NOT NULL,
|
|
wasLinked BOOL NOT NULL,
|
|
|
|
PRIMARY KEY(teamID),
|
|
--UNIQUE(zoneID, canonicalName),
|
|
|
|
FOREIGN KEY (zoneID) REFERENCES footballZones(zoneID)
|
|
)
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
|
|
CREATE TABLE footballTeamAliases (
|
|
aliasID INT NOT NULL AUTO_INCREMENT,
|
|
teamID VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
|
|
PRIMARY KEY(aliasID),
|
|
UNIQUE(teamID, name),
|
|
|
|
FOREIGN KEY (teamID) REFERENCES footballTeams(teamID)
|
|
)
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
|
|
CREATE TABLE footballMatches (
|
|
matchID VARCHAR(255) NOT NULL,
|
|
startTime INT NOT NULL,
|
|
champID VARCHAR(255) NOT NULL,
|
|
homeTeamID VARCHAR(255) NOT NULL,
|
|
awayTeamID VARCHAR(255) NOT NULL,
|
|
homeGoals INT NOT NULL,
|
|
awayGoals INT NOT NULL,
|
|
hasStats BOOL NOT NULL,
|
|
p1HomeGoals INT NOT NULL,
|
|
p1AwayGoals INT NOT NULL,
|
|
p2HomeGoals INT NOT NULL,
|
|
p2AwayGoals INT NOT NULL,
|
|
|
|
PRIMARY KEY(matchID),
|
|
|
|
FOREIGN KEY (champID) REFERENCES footballChamps(champID),
|
|
FOREIGN KEY (homeTeamID) REFERENCES footballTeams(teamID),
|
|
FOREIGN KEY (awayTeamID) REFERENCES footballTeams(teamID)
|
|
)
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
--INSERT INTO footballChamps VALUES (NULL, "Seria A (Italy)");
|
|
--INSERT INTO footballChamps VALUES (NULL, "Bundesliga (Germany)");
|
|
--INSERT INTO footballChamps VALUES (NULL, "Premier League (England)");
|
|
|
|
delete from footballTeamAliases;
|
|
delete from footballMatches;
|
|
delete from footballTeams;
|
|
delete from footballChamps;
|
|
delete from footballZones;
|
|
|
|
|
|
-- HANDBALL
|
|
|
|
CREATE TABLE handballZones (
|
|
zoneID VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
|
|
PRIMARY KEY(zoneID),
|
|
UNIQUE(name)
|
|
)
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
CREATE TABLE handballChamps (
|
|
champID VARCHAR(255) NOT NULL,
|
|
zoneID VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
|
|
PRIMARY KEY(champID),
|
|
UNIQUE(zoneID, name),
|
|
|
|
FOREIGN KEY (zoneID) REFERENCES handballZones(zoneID)
|
|
)
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
|
|
CREATE TABLE handballTeams (
|
|
teamID VARCHAR(255) NOT NULL,
|
|
zoneID VARCHAR(255) NOT NULL,
|
|
canonicalName VARCHAR(255) NOT NULL,
|
|
wasLinked BOOL NOT NULL,
|
|
|
|
PRIMARY KEY(teamID),
|
|
|
|
FOREIGN KEY (zoneID) REFERENCES handballZones(zoneID)
|
|
)
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
|
|
CREATE TABLE handballTeamAliases (
|
|
aliasID INT NOT NULL AUTO_INCREMENT,
|
|
teamID VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
|
|
PRIMARY KEY(aliasID),
|
|
UNIQUE(teamID, name),
|
|
|
|
FOREIGN KEY (teamID) REFERENCES handballTeams(teamID)
|
|
)
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
|
|
CREATE TABLE handballMatches (
|
|
matchID VARCHAR(255) NOT NULL,
|
|
startTime INT NOT NULL,
|
|
champID VARCHAR(255) NOT NULL,
|
|
homeTeamID VARCHAR(255) NOT NULL,
|
|
awayTeamID VARCHAR(255) NOT NULL,
|
|
homeGoals INT NOT NULL,
|
|
awayGoals INT NOT NULL,
|
|
hasStats BOOL NOT NULL,
|
|
p1HomeGoals INT NOT NULL,
|
|
p1AwayGoals INT NOT NULL,
|
|
p2HomeGoals INT NOT NULL,
|
|
p2AwayGoals INT NOT NULL,
|
|
|
|
PRIMARY KEY(matchID),
|
|
|
|
FOREIGN KEY (champID) REFERENCES handballChamps(champID),
|
|
FOREIGN KEY (homeTeamID) REFERENCES handballTeams(teamID),
|
|
FOREIGN KEY (awayTeamID) REFERENCES handballTeams(teamID)
|
|
)
|
|
ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
|
|
|
|
--INSERT INTO footballChamps VALUES (NULL, "Seria A (Italy)");
|
|
--INSERT INTO footballChamps VALUES (NULL, "Bundesliga (Germany)");
|
|
--INSERT INTO footballChamps VALUES (NULL, "Premier League (England)");
|
|
|
|
delete from handballTeamAliases;
|
|
delete from handballMatches;
|
|
delete from handballTeams;
|
|
delete from handballChamps;
|
|
delete from handballZones;
|