Untitled

-- Teams
CREATE TABLE Teams (
                       TeamID int PRIMARY KEY IDENTITY(1,1),
                       TeamName NVARCHAR(100) not null,
                       City NVARCHAR(100)
);
CREATE TABLE PlayerPosition (
                                PositionID int PRIMARY KEY IDENTITY(1,1),
                                PositionName NVARCHAR(50) not null
);
-- Players
-- 1-n o echipa are mai multi jucatori dar un jucatro are mai multe echipe
CREATE TABLE Players (
                         PlayerID int PRIMARY KEY IDENTITY(1,1),
                         PositionID int,
                         PlayerName NVARCHAR(100) not null,
                         BirthDate Date,
                         TeamID int,
                         FOREIGN KEY(TeamID) REFERENCES Teams(TeamID),
                         FOREIGN KEY(PositionID) REFERENCES PlayerPosition(PositionID)
);

CREATE TABLE Competitions (
                              CompetitionID int PRIMARY KEY IDENTITY(1,1),
                              CompetitionName NVARCHAR(100) not null,
                              Year int not null
);

CREATE TABLE Stadiums (
                          StadiumID int PRIMARY KEY IDENTITY(1,1),
                          StadiumName NVARCHAR(100),
                          Capacity int
);

CREATE TABLE Referees (
                          RefereeID int PRIMARY KEY IDENTITY(1,1),
                          RefereeName NVARCHAR(100),
                          ExperienceYears int
);

CREATE TABLE Matches (
                         MatchID int PRIMARY KEY IDENTITY(1,1),
                         HomeTownID int,
                         AwayTeamID int,
                         CompetitionID int,
                         MatchDate date,
                         StadiumID int,
                         RefereeID int,
                         FOREIGN KEY(HomeTownID) REFERENCES Teams(TeamID),
                         FOREIGN KEY(AwayTeamID) REFERENCES Teams(TeamID),
                         FOREIGN KEY(CompetitionID) REFERENCES Competitions(CompetitionID),
                         FOREIGN KEY(StadiumID) REFERENCES Stadiums(StadiumID),
                         FOREIGN KEY(RefereeID) REFERENCES Referees(RefereeID)
);

CREATE TABLE PlayerMatchStats (
                                  PlayerID int,
                                  MatchID int,
                                  GoalsScored int,
                                  Assists int,
                                  PRIMARY KEY (PlayerID, MatchID),
                                  FOREIGN KEY(PlayerID) REFERENCES Players(PlayerID),
                                  FOREIGN KEY(MatchID) REFERENCES Matches(MatchID)
);

-- o competitie are mai multi jucatori si un jucator e la mai multe competitii
-- n - m
CREATE TABLE PlayerCompetitions (
                                    PlayerID int,
                                    CompetitionID int,
                                    PRIMARY KEY (PlayerID, CompetitionID),
                                    FOREIGN KEY(PlayerID) REFERENCES Players(PlayerID),
                                    FOREIGN KEY(CompetitionID) REFERENCES Competitions(CompetitionID)
);

-- Fiecare coach are o echipa 1-1
CREATE TABLE Coaches (
                         CoachID int PRIMARY KEY IDENTITY(1,1),
                         CoachName NVARCHAR(100),
                         TeamID int UNIQUE ,
                         FOREIGN KEY(TeamID) REFERENCES Teams(TeamID)
);

-- fiecare player are 1-1
CREATE TABLE PlayerContracts (
                                 ContractID int PRIMARY KEY IDENTITY(1,1),
                                 PlayerID int UNIQUE,
                                 ContractDetails NVARCHAR(200),
                                 StartDate DATE,
                                 EndDate DATE,
                                 FOREIGN KEY(PlayerID) REFERENCES Players(PlayerID)
);