/* Day 8: ER Models
Tim Pierson, Dartmouth CS61, Spring 2026
Updates nyc_data to a normalized form.
*/

-- SLIDE 9
DROP SCHEMA IF EXISTS Hotels;
CREATE SCHEMA Hotels;
USE Hotels;
CREATE TABLE Hotels (
    HotelID INT PRIMARY KEY AUTO_INCREMENT,
    HotelName VARCHAR(100) NOT NULL,
    Location VARCHAR(200)
);
INSERT INTO Hotels (HotelID, HotelName, Location)
VALUES 
    (1, 'Grand Plaza', 'New York'),
    (2, 'Ocean View', 'Miami');

-- SLIDE 10
CREATE TABLE Rooms (
    HotelID INT,
    RoomNumber INT,
    RoomType VARCHAR(50),
    Price DECIMAL(10,2),
    PRIMARY KEY (HotelID, RoomNumber),
    FOREIGN KEY (HotelID) 
	REFERENCES Hotels(HotelID)
        ON DELETE CASCADE
);

-- Insert rooms
INSERT INTO Rooms (HotelID, RoomNumber, RoomType, Price)
VALUES
    (1, 101, 'Suite', 299.99),
    (1, 102, 'Standard', 149.99),
    (2, 101, 'Deluxe', 199.99),    -- Same room #s, different hotel!
    (2, 102, 'Standard', 129.99);
    
-- test
SELECT h.*, r.*
	FROM Hotels h JOIN Rooms r ON h.HotelID = r.HotelID
    ORDER BY HotelName, RoomNumber;
    
    
-- SLIDE 13 add surrogate key for weak entity Rooms
DROP TABLE Rooms;
CREATE TABLE Rooms (
    RoomID INT AUTO_INCREMENT,
    HotelID INT,
    RoomNumber INT,
    RoomType VARCHAR(50),
    Price DECIMAL(10,2),
    PRIMARY KEY (RoomID),
    FOREIGN KEY (HotelID) 
	REFERENCES Hotels(HotelID)
        ON DELETE CASCADE
);

-- Insert rooms
INSERT INTO Rooms (HotelID, RoomNumber, RoomType, Price)
VALUES
    (1, 101, 'Suite', 299.99),
    (1, 102, 'Standard', 149.99),
    (2, 101, 'Deluxe', 199.99),    -- Same room #s, different hotel!
    (2, 102, 'Standard', 129.99);

-- test
SELECT h.*, r.*
	FROM Hotels h JOIN Rooms r ON h.HotelID = r.HotelID
    ORDER BY HotelName, RoomID;


-- DONE WITH HOTELS NOW, DROP IT to not clutter database too much
DROP SCHEMA Hotels;




/***********************************************
*  Restaurant Inspections
************************************************/
-- make inspections table with compound key
-- make new Inspections 2 with compound key: RestaurantID, InspectionDate, InspectionType
-- SLIDE 18
use nyc_data;
CREATE TABLE Inspections2 (
  RestaurantID BIGINT NOT NULL, -- PK
  InspectionDate DATE NOT NULL, -- PK
  InspectionType VARCHAR(100) NOT NULL, -- PK
  ViolationCode VARCHAR(10) NOT NULL, -- PK
  ViolationDescription TEXT DEFAULT NULL,
  ActionDescription VARCHAR(200) DEFAULT NULL,
  Score int DEFAULT NULL,
  CriticalFlag VARCHAR(4) DEFAULT NULL, -- convert text into Y or N
  Grade varchar(4) DEFAULT NULL,
  GradeDate date DEFAULT NULL,
  PRIMARY KEY (RestaurantID, InspectionDate, InspectionType, ViolationCode),
  CONSTRAINT restaurant_inspections FOREIGN KEY (RestaurantID) REFERENCES Restaurants (RestaurantID)
	ON DELETE CASCADE
    ON UPDATE CASCADE
);

INSERT IGNORE INTO Inspections2 (RestaurantID, InspectionDate, InspectionType, ViolationCode,
	ViolationDescription, ActionDescription, Score, CriticalFlag, 
    Grade, GradeDate)
    SELECT DISTINCT CAMIS, STR_TO_DATE(`inspection date`,'%m/%d/%Y'),`inspection type`, `violation code`,
		`violation description`, `action`, score, IF(`critical flag` = 'Critical','Y','N'), 
        grade, STR_TO_DATE(NULLIF(`grade date`,''),'%m/%d/%Y')
        FROM restaurant_inspections
        WHERE `inspection date` <> '';
 
-- SLIDE 18 reverse engineer database 
-- From MySQL Workbench choose Database->Reverse Engineer

-- SLIDE 21
-- we are done with Inspections2 to reduce database clutter
DROP TABLE Inspections2;

  



-- SLIDE 22
/*
Transform restaurant_inspections into tables with entities for
	Restaurants
	Inspections
	Cuisine 
    Inspection Violations
    ViolationCode
    Inspection Types
    Actions

This script has three parts:
1) create tables -- based on forward engineering nyc_inspections_forward_engineer2.mwb EER
2) load data into tables -- handcrafted scripts to load data
3) drop old tables, stored procedures/functions, views, triggers -- remove unneeded clutter
These steps will result in the same database as database_during_day8.sql
*/

/********************************************
*											*
*             1) create tables             	*
*											*
*********************************************/

-- MySQL Workbench Forward Engineering



USE nyc_data;

-- -----------------------------------------------------
-- Table `Actions`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Actions` (
  `ActionID` INT NOT NULL AUTO_INCREMENT,
  `ActionDescription` VARCHAR(150) NULL DEFAULT NULL,
  PRIMARY KEY (`ActionID`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Cuisine`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Cuisine` (
  `CuisineID` INT NOT NULL AUTO_INCREMENT,
  `CuisineDescription` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`CuisineID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `InspectionTypes`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `InspectionTypes` (
  `InspectionTypeID` INT NOT NULL AUTO_INCREMENT,
  `InspectionTypeDescription` VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (`InspectionTypeID`))
ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table `Inspections`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Inspections` (
  `InspectionID` INT NOT NULL AUTO_INCREMENT,
  `RestaurantID` BIGINT NOT NULL,
  `InspectionDate` DATE NULL DEFAULT NULL,
  `Score` INT NULL DEFAULT NULL,
  `Grade` VARCHAR(4) NULL DEFAULT NULL,
  `GradeDate` DATE NULL DEFAULT NULL,
  `ActionID` INT NULL,
  `InspectionTypeID` INT NULL,
  PRIMARY KEY (`InspectionID`),
  INDEX `fk_Inspections_Restaurants_idx` (`RestaurantID` ASC) VISIBLE,
  INDEX `fk_Inspections_Actions1_idx` (`ActionID` ASC) VISIBLE,
  INDEX `fk_Inspections_InspectionTypes1_idx` (`InspectionTypeID` ASC) VISIBLE,
  CONSTRAINT `fk_Inspections_Restaurants`
    FOREIGN KEY (`RestaurantID`)
    REFERENCES `Restaurants` (`RestaurantID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Inspections_Actions1`
    FOREIGN KEY (`ActionID`)
    REFERENCES `Actions` (`ActionID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Inspections_InspectionTypes1`
    FOREIGN KEY (`InspectionTypeID`)
    REFERENCES `InspectionTypes` (`InspectionTypeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Restaurants`
-- -----------------------------------------------------
-- ALTER TABLE Restaurants DROP COLUMN CuisineID;
ALTER TABLE Restaurants ADD COLUMN CuisineID INT NULL DEFAULT NULL;

-- -----------------------------------------------------
-- Table `ViolationCodes`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `ViolationCodes` (
  `ViolationCode` VARCHAR(10) NOT NULL,
  `ViolationCodeDescription` VARCHAR(400) NULL DEFAULT NULL,
  `CriticalFlag` VARCHAR(15) NULL DEFAULT NULL,
  PRIMARY KEY (`ViolationCode`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `InspectionViolations`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `InspectionViolations` (
  `InspectionID` INT NOT NULL,
  `ViolationCode` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`InspectionID`, `ViolationCode`),
  INDEX `fk_InspectionViolations_Inspections1_idx` (`InspectionID` ASC) VISIBLE,
  INDEX `fk_InspectionViolations_ViolationCodes1_idx` (`ViolationCode` ASC) VISIBLE,
  CONSTRAINT `fk_InspectionViolations_Inspections1`
    FOREIGN KEY (`InspectionID`)
    REFERENCES `Inspections` (`InspectionID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_InspectionViolations_ViolationCodes1`
    FOREIGN KEY (`ViolationCode`)
    REFERENCES `ViolationCodes` (`ViolationCode`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;





/********************************************
*											*
*             2) load data		           	*
*											*
*********************************************/
/* fill forward engineered tables from nyc_inspections_forward_engineer2.mwb */
use nyc_data;
SET SQL_SAFE_UPDATES = 0;


-- Fill Cuisine table
TRUNCATE TABLE Cuisine;
ALTER TABLE Cuisine AUTO_INCREMENT = 1;
INSERT INTO Cuisine (CuisineDescription)
	SELECT DISTINCT CuisineDescription 
		FROM Restaurants WHERE CuisineDescription <> ''; 
    
-- Fill Restaurant cuisine ID    
UPDATE Restaurants r
	SET CuisineID = (SELECT CuisineID FROM Cuisine c WHERE r.CuisineDescription = c.CuisineDescription);
-- ADD Foreign Key on CuisineID into Cuisine
ALTER TABLE Restaurants 
	ADD CONSTRAINT fk_Restaurant_Cuisine1
	FOREIGN KEY (CuisineID)
	REFERENCES Cuisine(CuisineID)
	ON DELETE SET NULL
	ON UPDATE CASCADE;
-- Drop Restaurant CuisineDescription, use CuisineID to look up description in Cuisine
ALTER TABLE Restaurants DROP COLUMN CuisineDescription;
    
-- Fill InspectionTypes table
TRUNCATE TABLE InspectionTypes;
ALTER TABLE InspectionTypes AUTO_INCREMENT = 1;
INSERT INTO InspectionTypes (InspectionTypeDescription)
	SELECT DISTINCT `inspection type` as InspectionTypeDescription
		FROM restaurant_inspections
		WHERE `inspection type` IS NOT NULL AND `inspection type` <> '';
        
-- Fill Actions table
TRUNCATE TABLE Actions;
ALTER TABLE Actions AUTO_INCREMENT = 1;
INSERT INTO Actions (ActionDescription)
	SELECT DISTINCT `Action` AS ActionDescription
		FROM restaurant_inspections
		WHERE `Action` IS NOT NULL AND `Action` <> '';


-- Fill Inspections table
-- note: inspections data '01/01/1900' have only nulls for inspection details, skip them
TRUNCATE TABLE Inspections;
ALTER TABLE Inspections AUTO_INCREMENT = 1;
INSERT INTO Inspections (RestaurantID, InspectionDate, Score, Grade, GradeDate, ActionID, InspectionTypeID)
	WITH tempVals AS (
		SELECT r.*, a.*, t.* 
        FROM nyc_data.restaurant_inspections r LEFT JOIN Actions a ON r.`action` = a.ActionDescription
			LEFT JOIN InspectionTypes t ON t.InspectionTypeDescription = r.`inspection type`
		WHERE r.`inspection date` <> '01/01/1900' AND r.`grade date` <> ''
	)
	SELECT DISTINCT camis AS RestaurantID, 
		-- str_to_date(`inspection date`,'%m/%d/%Y') AS InspectionDate,
        InspectionDate,
		`score` AS Score, 
		`grade` AS Grade, 
		-- str_to_date(`grade date`, '%m/%d/%Y') AS GradeDate,
        GradeDate,
        ActionID AS ActionID,
        InspectionTypeID AS InspectionTypeID
		FROM tempVals;

-- fill ViolationCodes table
TRUNCATE TABLE ViolationCodes;
ALTER TABLE ViolationCodes AUTO_INCREMENT = 1;
INSERT IGNORE INTO ViolationCodes(ViolationCode,ViolationCodeDescription,CriticalFlag)
SELECT DISTINCT `violation code`,`violation description`,`critical flag`
	FROM nyc_data.restaurant_inspections
	WHERE `violation code` IS NOT NULL;



-- fill InspectionViolations table
TRUNCATE TABLE InspectionViolations;
ALTER TABLE InspectionViolations AUTO_INCREMENT = 1;
INSERT INTO InspectionViolations(InspectionID, ViolationCode)
	SELECT i.InspectionID, r.`violation code`
		FROM Inspections i, restaurant_inspections r, InspectionTypes t
        WHERE i.RestaurantID = r.camis
			AND i.InspectionDate = r.InspectionDate
            AND i.score = r.score
            AND i.grade = r.GRADE
            AND i.GradeDate = i.GradeDate
            AND i.InspectionTypeID = t.InspectionTypeID
            AND t.InspectionTypeDescription = r.`inspection type`;
    
  
/********************************************
*											*
*             3) DROP Clutter             	*
*											*
*********************************************/

-- DROP old views, triggers, stored procedures/functions that are no longer relevant
-- use information_schema to find them
-- information_schema is a standard SQL schema that is always present in SQL databases
 
SET @schemaName = 'nyc_data'; 

USE information_schema;

-- see what information this schema contains
SELECT * 
	FROM information_schema.tables;
    -- NOTICE: 
    -- 	ROUTINES (Stored procedures and functions)
    -- 	TABLES (tables)
    -- 	TRIGGERS (triggers)
    -- 	SCHEMATA (schemas)
    -- 	VIEWS (views)

-- DELETE OLD VIEWS (from prior class days)
SELECT *
	FROM information_schema.VIEWS
    WHERE table_schema = @schemaName;
    
-- create commands to drop all view
SELECT CONCAT('DROP VIEW IF EXISTS `', table_name, '`;') 
	FROM information_schema.VIEWS 
	WHERE table_schema = @schemaName;

USE nyc_data;    
DROP VIEW IF EXISTS `lastinspected`;
DROP VIEW IF EXISTS `mapdata`;

USE information_schema;

-- DELETE OLD TRIGGERS (from prior class days)
SELECT *
	FROM information_schema.TRIGGERS;
    -- WHERE trigger_schema = @schemaName;
    
-- create commands to drop all view
SELECT CONCAT('DROP TRIGGER IF EXISTS `', trigger_name, '`;') 
	FROM information_schema.TRIGGERS 
	WHERE trigger_schema = @schemaName;
    
USE nyc_data;
DROP TRIGGER IF EXISTS `UpdateInspectionStatsOnDelete`;
DROP TRIGGER IF EXISTS `UpdateInspectionScoreAudit`;
DROP TRIGGER IF EXISTS `UpdateInspectionStatsOnUpdate`;
DROP TRIGGER IF EXISTS `UpdateInspectionStatsOnInsert`;

USE information_schema;

-- DELETE OLD STORED PROCEDURES (from prior class days)
SELECT *
	FROM information_schema.ROUTINES;
    
-- create commands to drop all stored procedures
SELECT CONCAT('DROP PROCEDURE IF EXISTS `', ROUTINE_SCHEMA, '`.`', ROUTINE_NAME, '`;') 
	FROM information_schema.ROUTINES
	WHERE routine_type = 'PROCEDURE' 
		AND routine_schema = @schemaName;

    
USE nyc_data;
DROP PROCEDURE IF EXISTS GetManhattanRestaurants;
DROP PROCEDURE IF EXISTS GetRestaurantsByBoro;
DROP PROCEDURE IF EXISTS InspectionSummary;
DROP PROCEDURE IF EXISTS NewRestaurantInspection;
DROP PROCEDURE IF EXISTS RestaurantInspectionCategory;   

USE information_schema;
-- DELETE OLD STORED FUNCTIONS (from prior class days)
SELECT *
	FROM information_schema.ROUTINES;

-- create commands to drop all stored procedures
SELECT CONCAT('DROP FUNCTION IF EXISTS `', ROUTINE_SCHEMA, '`.`', ROUTINE_NAME, '`;') 
	FROM information_schema.ROUTINES
	WHERE routine_type = 'FUNCTION' 
		AND routine_schema = @schemaName;

USE nyc_data;
DROP FUNCTION IF EXISTS isBoro;
DROP FUNCTION IF EXISTS RestaurantInspectionFrequency;

-- DROP OLD TABLES
-- DROP TABLE IF EXISTS restaurant_inspections;


-- turn safe mode back on
SET SQL_SAFE_UPDATES = 1;



    
    
/***************************************************
* Practice Forward Engineer Artist Painting Gallery SLIDE 48
* one artist per painting
***************************************************/    
    
-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema painting_gallery1
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `painting_gallery1` ;

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

-- -----------------------------------------------------
-- Table `painting_gallery1`.`Artists`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `painting_gallery1`.`Artists` (
  `ArtistID` INT(11) NOT NULL AUTO_INCREMENT,
  `ArtistFirstName` VARCHAR(45) NULL DEFAULT NULL,
  `ArtistLastName` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`ArtistID`))
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `painting_gallery1`.`Galleries`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `painting_gallery1`.`Galleries` (
  `GalleryID` INT(11) NOT NULL AUTO_INCREMENT,
  `GalleryName` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`GalleryID`))
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `painting_gallery1`.`Paintings`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `painting_gallery1`.`Paintings` (
  `PaintingID` INT(11) NOT NULL AUTO_INCREMENT,
  `PaintingName` VARCHAR(45) NULL DEFAULT NULL,
  `PaintingYear` INT(11) NULL DEFAULT NULL,
  `ArtistID` INT(11) NOT NULL,
  `GalleryID` INT(11) NULL,
  PRIMARY KEY (`PaintingID`),
  INDEX `fk_Paintings_Artists_idx` (`ArtistID` ASC) VISIBLE,
  INDEX `fk_Paintings_Galleries1_idx` (`GalleryID` ASC) VISIBLE,
  CONSTRAINT `fk_Paintings_Artists`
    FOREIGN KEY (`ArtistID`)
    REFERENCES `painting_gallery1`.`Artists` (`ArtistID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Paintings_Galleries1`
    FOREIGN KEY (`GalleryID`)
    REFERENCES `painting_gallery1`.`Galleries` (`GalleryID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = utf8;











/***************************************************
* Practice Forward Engineer Artist Painting Gallery SLIDE 49
* Multiple artists can work on one painting
***************************************************/ 
-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema painting_gallery2
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `painting_gallery2` ;

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

-- -----------------------------------------------------
-- Table `Artists`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Artists` (
  `ArtistID` INT(11) NOT NULL AUTO_INCREMENT,
  `ArtistFirstName` VARCHAR(45) NULL DEFAULT NULL,
  `ArtistLastName` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`ArtistID`))
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `Galleries`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Galleries` (
  `GalleryID` INT(11) NOT NULL AUTO_INCREMENT,
  `GalleryName` VARCHAR(45) NULL DEFAULT NULL,
  PRIMARY KEY (`GalleryID`))
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `Paintings`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `Paintings` (
  `PaintingID` INT(11) NOT NULL AUTO_INCREMENT,
  `PaintingName` VARCHAR(45) NULL DEFAULT NULL,
  `PaintingYear` INT(11) NULL DEFAULT NULL,
  `GalleryID` INT(11) NULL,
  PRIMARY KEY (`PaintingID`),
  INDEX `fk_Paintings_Galleries1_idx` (`GalleryID` ASC) VISIBLE,
  CONSTRAINT `fk_Paintings_Galleries1`
    FOREIGN KEY (`GalleryID`)
    REFERENCES `Galleries` (`GalleryID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `ArtistPaintings`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `ArtistPaintings` (
  `PaintingID` INT NOT NULL,
  `ArtistID` INT NOT NULL,
  PRIMARY KEY (`PaintingID`, `ArtistID`),
  INDEX `artist_fk_idx` (`ArtistID` ASC) VISIBLE,
  CONSTRAINT `artist_fk`
    FOREIGN KEY (`ArtistID`)
    REFERENCES `Artists` (`ArtistID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `painting_fk`
    FOREIGN KEY (`PaintingID`)
    REFERENCES `Paintings` (`PaintingID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;




-- insert some data
-- artists
INSERT INTO Artists (`ArtistID`,`ArtistFirstName`,`ArtistLastName`) VALUES (5,'Michangelo',NULL);
INSERT INTO Artists (`ArtistID`,`ArtistFirstName`,`ArtistLastName`) VALUES (6,'Ralphael',NULL);

-- galleries
INSERT INTO Galleries (`GalleryID`,`GalleryName`) VALUES (5,'Gallery 1');
INSERT INTO Galleries (`GalleryID`,`GalleryName`) VALUES (6,'Gallery 2');

-- paintings
INSERT INTO Paintings (`PaintingID`,`PaintingName`,`PaintingYear`,`GalleryID`) VALUES (8,'Sistine Chapel',1600,5);
INSERT INTO Paintings (`PaintingID`,`PaintingName`,`PaintingYear`,`GalleryID`) VALUES (9,'Self portrait',1800,6);
INSERT INTO Paintings (`PaintingID`,`PaintingName`,`PaintingYear`,`GalleryID`) VALUES (10,'Together',1700,6);

-- artistpaintings
INSERT INTO ArtistPaintings (`PaintingID`,`ArtistID`) VALUES (8,5);
INSERT INTO ArtistPaintings (`PaintingID`,`ArtistID`) VALUES (10,5);
INSERT INTO ArtistPaintings (`PaintingID`,`ArtistID`) VALUES (9,6);
INSERT INTO ArtistPaintings (`PaintingID`,`ArtistID`) VALUES (10,6);

-- test
Use painting_gallery2;
SELECT PaintingName, ArtistFirstName, GalleryName
	FROM paintings p JOIN ArtistPaintings ap ON p.PaintingID = ap.PaintingID
		JOIN Artists a ON a.ArtistID = ap.ArtistID
		JOIN Galleries g ON g.GalleryID = p.GalleryID
	ORDER BY PaintingName;

