-- FIRST APPROACH

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

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

CREATE TABLE Player (
  pn INT PRIMARY KEY,
  name TEXT NOT NULL,
  position_ TEXT NOT NULL,
  skill TEXT NOT NULL,
  team_name TEXT,
  team_city TEXT, 
  FOREIGN KEY (team_city, team_name) REFERENCES Team(city,name)
);

-- 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');

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


-- 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 "
-- By definition there can only exist ONE Primary Key.
-- This fails
INSERT INTO Player VALUES (3, 'Name3','Pos3','skill3','Team3','City3');
INSERT INTO Player VALUES (3, 'Name3','Pos3','skill3','Team3','City2');
INSERT INTO Player VALUES (3, 'Name3','Pos3','skill3','Team2','City2');
--
--
INSERT INTO Player VALUES (4, 'Name3','Pos3','skill3',NULL,NULL);
UPDATE Player SET team_name='Team1', team_city='City1' WHERE pn=4;

-- This fails
UPDATE Player SET team_name='Team4', team_city='City1' WHERE pn=4;