/* Day 16: Soccer normalization
Tim Pierson, Dartmouth CS61, Spring 2020 
Uses soccer_unnormalized.mwb to forward engineer data and tables
*/
USE Soccer;

-- fill team table
INSERT INTO Teams
SELECT DISTINCT TeamName, TeamPhone FROM Unnormalized;
-- check that it worked
SELECT * FROM Teams; -- should have 4 teams, Argentina, Portugal, Brazil, Wales

-- fill players table
INSERT INTO Players
SELECT PlayerID, PlayerName, TeamName FROM  Unnormalized;
-- check that it worked
SELECT * FROM Players; -- should have six players and their teams

-- fill positions table
INSERT INTO Positions
SELECT DISTINCT Position1 FROM Unnormalized;
-- check that it worked
SELECT * FROM Positions; -- 6 entries

-- there might be new positions in Position 2 or 3 columns
-- use INSERT IGNORE to add these too
-- INSERT IGNORE will ignore duplciates
INSERT IGNORE INTO Positions
SELECT DISTINCT Position2 FROM Unnormalized;
-- check that it worked
SELECT * FROM Positions; -- 8 entries

-- same for position 3
INSERT IGNORE INTO Positions
SELECT DISTINCT Position3 FROM Unnormalized;
-- check that it worked
SELECT * FROM Positions; -- 9 entries

-- now fill PlayerPositions
-- NOTE: List Player's favorite position as PlayerPositionNumber 1
INSERT INTO PlayerPositions
SELECT PlayerID, Position1, 1 FROM Unnormalized;
-- check 
SELECT * FROM PlayerPositions; -- 6 entries

-- Now from Position2, ignore if NULL
-- NOTE: List Player's second favorite position as PlayerPositionNumber 2
INSERT INTO PlayerPositions
SELECT PlayerID, Position2, 2 
FROM Unnormalized
WHERE Position2 IS NOT NULL;
-- check 
SELECT * FROM PlayerPositions; -- 11 entries

-- Now from Position3, ignore if NULL
-- NOTE: List Player's third favorite position as PlayerPositionNumber 3
INSERT INTO PlayerPositions
SELECT PlayerID, Position3, 3 
FROM Unnormalized
WHERE Position3 IS NOT NULL;
-- check 
SELECT * FROM PlayerPositions; -- 13 entries

-- now check players and positions
SELECT PlayerID, PlayerName, PositionName, TeamName
FROM Players NATURAL JOIN PlayerPositions  JOIN Positions USING (PositionName) JOIN Teams USING (TeamName)
Order BY PlayerID, PlayerPositionNumber; 

