-- FIRST APPROACH

DROP SCHEMA IF EXISTS lecture7ex1a CASCADE;
CREATE SCHEMA lecture7ex1a;
SET SCHEMA 'lecture7ex1a';

CREATE TABLE Player (
  pn INT PRIMARY KEY,
  name TEXT NOT NULL,
  position_ TEXT NOT NULL,
  skill TEXT NOT NULL
);

CREATE TABLE Team(
  name TEXT,
  city TEXT,
  coach TEXT,
--  coach TEXT UNIQUE,
  PRIMARY KEY (name,city)
);

CREATE TABLE PlaysFor(
  pn INT REFERENCES Player, 
  name TEXT,
  city TEXT, 
  PRIMARY KEY (pn),
  FOREIGN KEY (city, name) REFERENCES Team(city,name)
);



-- Insert 3 Players
INSERT INTO Player VALUES (1, 'Name1','Pos1','skill1');
INSERT INTO Player VALUES (2, 'Name2','Pos2','skill2');
INSERT INTO Player VALUES (3, 'Name3','Pos3','skill3');


-- Insert 4 Teams
INSERT INTO Team VALUES ('Team1','City1', 'Coach1');
INSERT INTO Team VALUES ('Team11','City1', 'Coach1');
INSERT INTO Team VALUES ('Team2','City2', 'Coach2');
INSERT INTO Team VALUES ('Team3','City3', 'Coach3');


-- It is weird to have the same coach for different teams: Uncomment the UNIQUE constraint
INSERT INTO Team VALUES ('Team4','City2', 'Coach2');

-- How to test: "A team’s name is only unique in their own city":
-- This works
-- This Fails
INSERT INTO Team VALUES ('Team1','City1');

-- How to test: "Each player belongs to maximum one team "
-- This Works --> Player 1 plays for Team1
INSERT INTO PlaysFor VALUES (1, 'Team1','City1');
-- This fails
INSERT INTO PlaysFor VALUES (1, 'Team1','City1');
INSERT INTO PlaysFor VALUES (1, 'Team11','City1');

