schema.sql

-- CREATING DATABASE
CREATE PLUGGABLE DATABASE mon_peacock_leagueManagementSystem
ADMIN USER mon_peacock IDENTIFIED BY peacock
FILE_NAME_CONVERT = ('E:\oracle\oradata\ORCL\pdbseed', 'E:\plsql\pdbs\peacock');


GRANT DBA TO mon_peacock;



-- Stadiums Table
CREATE TABLE Stadiums (
    stadium_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    stadium_name VARCHAR2(100),
    city VARCHAR2(50),
    capacity NUMBER,
    established_year NUMBER(4)
);

-- Coaches Table
CREATE TABLE Coaches (
    coach_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    nationality VARCHAR2(50),
    experience_years NUMBER
);

-- Referees Table
CREATE TABLE Referees (
    referee_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    nationality VARCHAR2(50),
    experience_years NUMBER
);


-- Teams Table
CREATE TABLE Teams (
                       team_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
                       team_name VARCHAR2(100),
                       founded_year NUMBER(4),
                       city VARCHAR2(50),
                       stadium_id NUMBER,
                       manager VARCHAR2(50),
                       coach_id NUMBER
);

-- Players Table
CREATE TABLE Players (
                         player_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
                         first_name VARCHAR2(50),
                         last_name VARCHAR2(50),
                         date_of_birth DATE,
                         nationality VARCHAR2(50),
                         position VARCHAR2(20),
                         current_team_id NUMBER,
                         height NUMBER(5, 2),
                         weight NUMBER(5, 2)
);

-- Transfers Table
CREATE TABLE Transfers (
                           transfer_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
                           player_id NUMBER,
                           from_team_id NUMBER,
                           to_team_id NUMBER,
                           transfer_date DATE,
                           transfer_fee NUMBER(12, 2)
);

-- League Seasons Table
CREATE TABLE LeagueSeasons (
                               season_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
                               season_name VARCHAR2(50),
                               start_date DATE,
                               end_date DATE,
                               champion_team_id NUMBER
);

-- Matches Table
CREATE TABLE Matches (
                         match_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
                         home_team_id NUMBER,
                         away_team_id NUMBER,
                         stadium_id NUMBER,
                         match_date DATE,
                         league_season_id NUMBER,
                         referee_id NUMBER,
                         home_team_score NUMBER DEFAULT 0,
                         away_team_score NUMBER DEFAULT 0
);

-- TeamPlayers Table
CREATE TABLE TeamPlayers (
                             team_player_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
                             team_id NUMBER,
                             player_id NUMBER,
                             join_date DATE,
                             leave_date DATE
);




-- League Seasons Table Constraints
ALTER TABLE LeagueSeasons ADD PRIMARY KEY (season_id);
ALTER TABLE LeagueSeasons ADD CONSTRAINT fk_season_champion_team FOREIGN KEY (champion_team_id) REFERENCES Teams (team_id);

-- TeamPlayers Table Constraints
ALTER TABLE TeamPlayers ADD PRIMARY KEY (team_player_id);
ALTER TABLE TeamPlayers ADD CONSTRAINT fk_team_player_team FOREIGN KEY (team_id) REFERENCES Teams (team_id);
ALTER TABLE TeamPlayers ADD CONSTRAINT fk_team_player_player FOREIGN KEY (player_id) REFERENCES Players (player_id);
-- Add unique constraint to Transfers table
ALTER TABLE Transfers
ADD CONSTRAINT unique_player_transfer UNIQUE (player_id, transfer_date);

-- Add check constraint to LeagueSeasons table
ALTER TABLE LeagueSeasons
ADD CONSTRAINT check_season_dates CHECK (start_date < end_date);

-- Create composite index on Matches for league_season_id and stadium_id
CREATE INDEX idx_matches_season_stadium ON Matches (league_season_id, stadium_id);

-- Players Table Constraints
ALTER TABLE Players ADD PRIMARY KEY (player_id);
ALTER TABLE Players ADD CONSTRAINT fk_player_team FOREIGN KEY (current_team_id) REFERENCES Teams (team_id);
ALTER TABLE Players ADD CONSTRAINT chk_player_position CHECK (position IN ('Goalkeeper', 'Defender', 'Midfielder', 'Forward'));

-- Transfers Table Constraints
ALTER TABLE Transfers ADD PRIMARY KEY (transfer_id);
ALTER TABLE Transfers ADD CONSTRAINT fk_transfer_player FOREIGN KEY (player_id) REFERENCES Players (player_id);
ALTER TABLE Transfers ADD CONSTRAINT fk_transfer_from_team FOREIGN KEY (from_team_id) REFERENCES Teams (team_id);
ALTER TABLE Transfers ADD CONSTRAINT fk_transfer_to_team FOREIGN KEY (to_team_id) REFERENCES Teams (team_id);
ALTER TABLE Transfers ADD CONSTRAINT chk_transfer_teams CHECK (from_team_id <> to_team_id);

-- Matches Table Constraints
ALTER TABLE Matches ADD PRIMARY KEY (match_id);
ALTER TABLE Matches ADD CONSTRAINT fk_match_home_team FOREIGN KEY (home_team_id) REFERENCES Teams (team_id);
ALTER TABLE Matches ADD CONSTRAINT fk_match_away_team FOREIGN KEY (away_team_id) REFERENCES Teams (team_id);
ALTER TABLE Matches ADD CONSTRAINT fk_match_stadium FOREIGN KEY (stadium_id) REFERENCES Stadiums (stadium_id);
ALTER TABLE Matches ADD CONSTRAINT fk_match_season FOREIGN KEY (league_season_id) REFERENCES LeagueSeasons (season_id);
ALTER TABLE Matches ADD CONSTRAINT fk_match_referee FOREIGN KEY (referee_id) REFERENCES Referees (referee_id);
ALTER TABLE Matches ADD CONSTRAINT chk_match_teams CHECK (home_team_id <> away_team_id);


-- Stadiums Table Constraints

ALTER TABLE Stadiums ADD PRIMARY KEY (stadium_id);

ALTER TABLE Stadiums ADD CONSTRAINT unique_stadium_name UNIQUE (stadium_name);



-- Teams Table Constraints

ALTER TABLE Teams ADD PRIMARY KEY (team_id);

ALTER TABLE Teams ADD CONSTRAINT unique_team_name UNIQUE (team_name);

ALTER TABLE Teams ADD CONSTRAINT fk_team_stadium FOREIGN KEY (stadium_id) REFERENCES Stadiums (stadium_id);

ALTER TABLE Teams ADD CONSTRAINT fk_team_coach FOREIGN KEY (coach_id) REFERENCES Coaches (coach_id);



-- Referees Table Constraints

ALTER TABLE Referees ADD PRIMARY KEY (referee_id);

-- Matches Table Composite Index
CREATE INDEX idx_match_teams ON Matches (home_team_id, away_team_id);

-- Transfers Table Composite Index
CREATE INDEX idx_transfer_player_date ON Transfers (player_id, transfer_date);

-- Use EXPLAIN PLAN or SQL Trace for Query Optimization
-- Example: EXPLAIN PLAN FOR SELECT * FROM Matches WHERE home_team_id = 1;
-- Stadiums Table Indexes
CREATE INDEX idx_stadium_city ON Stadiums (city);
CREATE INDEX idx_stadium_capacity ON Stadiums (capacity);

-- Teams Table Indexes
CREATE INDEX idx_team_name ON Teams (team_name);
CREATE INDEX idx_team_coach_id ON Teams (coach_id);

-- Players Table Indexes
CREATE INDEX idx_player_position ON Players (position);
CREATE INDEX idx_player_team ON Players (current_team_id);