65 lines
1.8 KiB
SQL
65 lines
1.8 KiB
SQL
-- Datenbank erstellen
|
|
CREATE DATABASE IF NOT EXISTS champions_league
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE champions_league;
|
|
|
|
-- USERS (Login / Rollen)
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
user_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('admin','user') NOT NULL DEFAULT 'user'
|
|
);
|
|
|
|
-- SEASONS (Saison)
|
|
CREATE TABLE IF NOT EXISTS seasons (
|
|
season_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(50) NOT NULL
|
|
);
|
|
|
|
-- TEAMS (Teilnehmer)
|
|
CREATE TABLE IF NOT EXISTS teams (
|
|
team_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
country VARCHAR(50),
|
|
group_name CHAR(1)
|
|
);
|
|
|
|
-- PLAYERS (Spieler eines Teams)
|
|
CREATE TABLE IF NOT EXISTS players (
|
|
player_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
position VARCHAR(30),
|
|
team_id INT NOT NULL,
|
|
FOREIGN KEY (team_id) REFERENCES teams(team_id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- MATCHES (Spiele)
|
|
CREATE TABLE IF NOT EXISTS matches (
|
|
match_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
season_id INT NOT NULL,
|
|
home_team_id INT NOT NULL,
|
|
away_team_id INT NOT NULL,
|
|
match_date DATE,
|
|
FOREIGN KEY (season_id) REFERENCES seasons(season_id),
|
|
FOREIGN KEY (home_team_id) REFERENCES teams(team_id),
|
|
FOREIGN KEY (away_team_id) REFERENCES teams(team_id),
|
|
CHECK (home_team_id <> away_team_id)
|
|
);
|
|
|
|
-- GOAL_EVENTS (Tore + Assists)
|
|
CREATE TABLE IF NOT EXISTS goal_events (
|
|
goal_id INT AUTO_INCREMENT PRIMARY KEY,
|
|
match_id INT NOT NULL,
|
|
scorer_id INT NOT NULL,
|
|
assist_id INT,
|
|
minute INT NOT NULL,
|
|
FOREIGN KEY (match_id) REFERENCES matches(match_id) ON DELETE CASCADE,
|
|
FOREIGN KEY (scorer_id) REFERENCES players(player_id),
|
|
FOREIGN KEY (assist_id) REFERENCES players(player_id)
|
|
);
|
|
|
|
|