
CREATE DATABASE s7777777;

DROP TABLE IF EXISTS Tournaments, Countries, Stadiums, Hosts, Groups, Teams, Clubs, Players, Matches, Goals;

CREATE TABLE Tournaments(
   TYear SMALLINT PRIMARY KEY,
   PtsWin SMALLINT NOT NULL   
);

CREATE TABLE Countries(
   Cid SERIAL PRIMARY KEY,
   Name VARCHAR(256) NOT NULL UNIQUE
);

CREATE TABLE Stadiums(
   Sid SERIAL PRIMARY KEY,
   Name VARCHAR(256) NOT NULL UNIQUE, 
   Cid INT REFERENCES Countries NOT NULL
);

CREATE TABLE Hosts(
   TYear SMALLINT REFERENCES Tournaments NOT NULL,
   Cid INT REFERENCES Countries NOT NULL,
   PRIMARY KEY(TYear, CID)
);

CREATE TABLE Groups(
   Gid SERIAL PRIMARY KEY,
   Name VARCHAR(64) NOT NULL,
   TYear SMALLINT REFERENCES Tournaments NOT NULL
);

CREATE TABLE Teams(
   Tid SERIAL PRIMARY KEY,
   Cid INT REFERENCES Countries NOT NULL,
   Gid INT REFERENCES Groups NOT NULL
);

CREATE TABLE Clubs(
   Ncid SERIAL PRIMARY KEY,
   Name VARCHAR(256) NOT NULL UNIQUE,
   Cid INT REFERENCES Countries NOT NULL
);

CREATE TABLE Players(
   Pid SERIAL PRIMARY KEY,
   Name VARCHAR(256) NOT NULL,
   Pos CHAR(2) NOT NULL CHECK(Pos IN('GK','DF','MF','FW')),
   JNum SMALLINT NOT NULL,
   Ncid INT REFERENCES Clubs,  
   Tid INT REFERENCES Teams NOT NULL,
   UNIQUE(Tid, JNum)
);

CREATE TABLE Matches(
   Mid INT PRIMARY KEY,
   HomeTid INT REFERENCES Teams NOT NULL,
   VisitTid INT REFERENCES Teams NOT NULL,
   HomeScore SMALLINT NOT NULL,
   VisitScore SMALLINT NOT NULL,
   MatchDate DATE NOT NULL, 
   MatchType VARCHAR(64) NOT NULL,
   Sid INT REFERENCES Stadiums NOT NULL
);

CREATE TABLE Goals(
   Gid SERIAL PRIMARY KEY,
   Mid INT REFERENCES Matches NOT NULL,
   Pid INT REFERENCES Players NOT NULL,
   GOwn BOOL NOT NULL,
   GTime SMALLINT NOT NULL
);
