/* Day 9: Normalization
Tim Pierson, Dartmouth CS61, Spring 2026
*/

-- SLIDE 42
USE nyc_data;
SELECT RestaurantName, InspectionDate, Grade, iv.ViolationCode, vc.ViolationCodeDescription
	FROM Restaurants r JOIN Inspections i ON r.RestaurantID = i. RestaurantID
		JOIN InspectionViolations iv ON iv.InspectionID = i.InspectionID
        JOIN ViolationCodes vc ON vc.ViolationCode = iv.ViolationCode
	WHERE r.RestaurantName LIKE 'Morris Park Bake%'
    ORDER BY i.InspectionDate DESC;
    
/***************************************************
* Practice SLIDE 43
***************************************************/  
-- from soccer_normalized.mwb forward engineer

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema Soccer
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `soccer` ;

-- -----------------------------------------------------
-- Schema Soccer
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `soccer` DEFAULT CHARACTER SET utf8 ;
USE `soccer` ;

-- -----------------------------------------------------
-- Table `Unnormalized`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Unnormalized` ;

CREATE TABLE IF NOT EXISTS `Unnormalized` (
  `PlayerID` INT NOT NULL AUTO_INCREMENT,
  `PlayerName` VARCHAR(45) NULL,
  `TeamName` VARCHAR(45) NULL,
  `TeamPhone` VARCHAR(45) NULL,
  `Position1` VARCHAR(45) NULL,
  `Position2` VARCHAR(45) NULL,
  `Position3` VARCHAR(45) NULL,
  PRIMARY KEY (`PlayerID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Teams`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Teams` ;

CREATE TABLE IF NOT EXISTS `Teams` (
  `TeamName` VARCHAR(45) NOT NULL,
  `TeamPhone` VARCHAR(45) NULL,
  PRIMARY KEY (`TeamName`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Players`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Players` ;

CREATE TABLE IF NOT EXISTS `Players` (
  `PlayerID` INT NOT NULL AUTO_INCREMENT,
  `PlayerName` VARCHAR(45) NOT NULL,
  `TeamName` VARCHAR(45) NULL,
  PRIMARY KEY (`PlayerID`),
  INDEX `fk_Players_Teams_idx` (`TeamName` ASC) VISIBLE,
  CONSTRAINT `fk_Players_Teams`
    FOREIGN KEY (`TeamName`)
    REFERENCES `Teams` (`TeamName`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Positions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Positions` ;

CREATE TABLE IF NOT EXISTS `Positions` (
  `PositionName` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`PositionName`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `PlayerPositions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `PlayerPositions` ;

CREATE TABLE IF NOT EXISTS `PlayerPositions` (
  `PlayerID` INT NOT NULL,
  `PositionName` VARCHAR(45) NOT NULL,
  `PlayerPositionNumber` INT NULL,
  PRIMARY KEY (`PlayerID`, `PositionName`),
  INDEX `fk_PlayerPositions_Positions1_idx` (`PositionName` ASC) VISIBLE,
  CONSTRAINT `fk_PlayerPositions_Players1`
    FOREIGN KEY (`PlayerID`)
    REFERENCES `Players` (`PlayerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_PlayerPositions_Positions1`
    FOREIGN KEY (`PositionName`)
    REFERENCES `Positions` (`PositionName`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `Unnormalized`
-- -----------------------------------------------------
START TRANSACTION;
USE `Soccer`;
INSERT INTO `Unnormalized` (`PlayerID`, `PlayerName`, `TeamName`, `TeamPhone`, `Position1`, `Position2`, `Position3`) VALUES (DEFAULT, 'Pessi', 'Argentina', '54-11-1000-1000', 'Striker', 'Forward', NULL);
INSERT INTO `Unnormalized` (`PlayerID`, `PlayerName`, `TeamName`, `TeamPhone`, `Position1`, `Position2`, `Position3`) VALUES (DEFAULT, 'Ricardo', 'Portugal', '351-2-7777-7777', 'Right Midfielder', 'Defending Midfielder', NULL);
INSERT INTO `Unnormalized` (`PlayerID`, `PlayerName`, `TeamName`, `TeamPhone`, `Position1`, `Position2`, `Position3`) VALUES (DEFAULT, 'Neumann', 'Brazil', '55-21-4040-2020', 'Forward', 'Left Fullback', 'Rignt Fullback');
INSERT INTO `Unnormalized` (`PlayerID`, `PlayerName`, `TeamName`, `TeamPhone`, `Position1`, `Position2`, `Position3`) VALUES (DEFAULT, 'Baily', 'Wales', '44-29-1876-1876', 'Defending Midfielder', 'Striker', NULL);
INSERT INTO `Unnormalized` (`PlayerID`, `PlayerName`, `TeamName`, `TeamPhone`, `Position1`, `Position2`, `Position3`) VALUES (DEFAULT, 'Marioso', 'Argentina', '54-11-1000-1000', 'Sweeper', 'Defending Midfielder', 'Striker');
INSERT INTO `Unnormalized` (`PlayerID`, `PlayerName`, `TeamName`, `TeamPhone`, `Position1`, `Position2`, `Position3`) VALUES (DEFAULT, 'Pare', 'Brazil', '55-21-4040-2020', 'Goalkeeper', NULL, NULL);

COMMIT;

/*
Normalize the Unnormalized Table
* PK is PlayerID
* Identify entities
      * Players
      * Teams
      * Positions
* Business rules: 
      * One player plays for one team, many players play for one team (1:M)
      * One player can play many positions, one position can be played by many players (M:N)

Following business rules, create tables:
* Players (PlayerID, PlayerName, TeamName)
* Teams (TeamName, TeamPhone)
* Positions (PositionName)

Create relationships between tables:
* 1:M Team:Players
* M:N Player:Position
      * Create joining table 
      * PlayerPositions (PlayerID, PositionName, PositionNumber)
      * PositionNumber tracks the player’s position preference (1 = first choice)

Check normalization
1NF: table form, no repeating groups — all check
2NF: no partial dependencies (attribute depends on part of composite PK) — all check
3NF: no transitive dependencies (attribute depends on non-key attribute) — all check

PlayerPositionNumber tracks the order of positions
* Player’s favorite position has value 1
* Player’s second favorite position has value 2
* Player’s third favorite position has value 3
*/
