DROP SCHEMA IF EXISTS HW_SCIENTIFIC_RESEARCH;

CREATE SCHEMA HW_SCIENTIFIC_RESEARCH;

USE HW_SCIENTIFIC_RESEARCH;

CREATE TABLE SCIENTIST (
  SSN INT PRIMARY KEY,
  Name VARCHAR(30) NOT NULL
);

CREATE TABLE PROJECT (
  Code CHAR(4) PRIMARY KEY,
  Name VARCHAR(150) NOT NULL
);

CREATE TABLE CONTRIBUTESTO (
  Scientist INT,
  Project CHAR(4),
  Hours INT NOT NULL CHECK (Hours > 0),
  PRIMARY KEY (Scientist, Project),
  FOREIGN KEY (Scientist) REFERENCES SCIENTIST (SSN),
  FOREIGN KEY (Project) REFERENCES PROJECT (Code) ON DELETE
    CASCADE ON UPDATE CASCADE
);

CREATE TABLE FUNDINGAGENCY (
  Name VARCHAR(150) PRIMARY KEY,
  TYPE ENUM ("State", "Federal", "Foundation"),
  Creation YEAR
);

CREATE TABLE FUNDS (
  Agency VARCHAR(150),
  Project CHAR(4),
  Amount DECIMAL(12, 2),
  FOREIGN KEY (Agency) REFERENCES FUNDINGAGENCY (NAME) ON
    UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (Project) REFERENCES PROJECT (Code)
);

INSERT INTO SCIENTIST
VALUES (
  "000000000",
  "Mike"), -- S.1
(
  "000000001", "Sabine"), -- S.2
(
  "000000002", "James"), -- S.3
(
  "000000003", "Emily"), -- S.4
(
  "000000004", "Claire");

-- S.5
INSERT INTO PROJECT
VALUES (
  "AA",
  "Advancing Airplanes"), -- P.1
(
  "BA", "Better Airplanes"), -- P.2
(
  "BB", "Better Buildings"), -- P.3
(
  "CC", "Creative Creation");

-- P.4
INSERT INTO CONTRIBUTESTO
VALUES (
  "000000001",
  "AA",
  12), -- C.1
(
  "000000001", "BB", 10), -- C.2
(
  "000000002", "AA", 5), -- C.3
(
  "000000003", "BA", 3), -- C.4
(
  "000000000", "BB", 1), -- C.5
(
  "000000000", "AA", 1);

-- C.6
INSERT INTO FUNDINGAGENCY
VALUES (
  "National Science Foundation",
  "Federal",
  1950), -- FA.1
(
  "French-American Cultural Exchange", "Foundation", 2017);

-- FA.2
INSERT INTO FUNDS
VALUES (
  "National Science Foundation",
  "AA",
  100000), -- F.1
(
  "French-American Cultural Exchange", "CC", 10000);

-- F.2
-- start snippet solution
/* code/sql/HW_ScientificResearchSol.sql */
-- List the rows affected (updated or deleted) by the
--	 following commands.
--	  If no rows are affected because the command would
--      would
--	 violate the entity integrity constraint, the
--      referential
--	 integrity constraint, or if there would be some
--   other
--	kind
--	 of error, please indicate it.
START TRANSACTION;


/*
UPDATE
 SCIENTIST
SET SSN = "000000001"
WHERE Name = "Claire";
 */
-- ERROR 1062 (23000) at line 106: Duplicate entry '1'
-- for
--	 key 'PRIMARY'
ROLLBACK;

START TRANSACTION;

UPDATE
  FUNDINGAGENCY
SET Name = "NSF"
WHERE Name = "National Science Foundation";

SELECT *
FROM FUNDINGAGENCY;

-- FA. 1
SELECT *
FROM FUNDS;

-- F.1
ROLLBACK;

START TRANSACTION;


/*
DELETE FROM FUNDINGAGENCY
WHERE Name = "French-American Cultural Exchange";
 */
-- ERROR 1451 (23000): Cannot delete or update a parent row:
--	 a foreign key constraint fails
--	 (`HW_SCIENTIFIC_RESEARCH`.`FUNDS`, CONSTRAINT
--	 `FUNDS_ibfk_1` FOREIGN KEY (`Agency`) REFERENCES
--	 `FUNDINGAGENCY` (`Name`) ON UPDATE CASCADE)
ROLLBACK;

-- List the name of the funding agencies created after 2000
--	 ("French-American Cultural Exchange")
SELECT Name
FROM FUNDINGAGENCY
WHERE Creation >= 2000;

-- List the code of the projects that contains the word
--	 "Airplanes" ("AA", "BA")
SELECT CODE
FROM PROJECT
WHERE Name LIKE ("%Airplanes%");

-- List the number of hours scientists contributed to the
--	 project "AA" (18)
SELECT SUM(Hours)
FROM CONTRIBUTESTO
WHERE Project = "AA";

-- List the code of the projects to which the scientist named
--	 Sabine contributed ("AA", "BB")
SELECT Project
FROM CONTRIBUTESTO,
  SCIENTIST
WHERE SCIENTIST.Name = "Sabine"
  AND SCIENTIST.SSN = CONTRIBUTESTO.Scientist;

-- Give the name of the projects who benefited from federal
--	 funds ("Advancing Airplanes")
SELECT PROJECT.Name
FROM PROJECT,
  FUNDS,
  FUNDINGAGENCY
WHERE FUNDINGAGENCY.Type = "Federal"
  AND FUNDINGAGENCY.Name = FUNDS.Agency
  AND FUNDS.Project = PROJECT.Code;

-- Give the name of the scientist who contributed to the same
--	 project as Mike ("Sabine", "James")
SELECT DISTINCT (Fellow.Name) AS "Mike's fellow"
FROM SCIENTIST AS Mike,
  SCIENTIST AS Fellow,
  CONTRIBUTESTO AS A,
  CONTRIBUTESTO AS B
WHERE Mike.Name = "Mike"
  AND Mike.SSN = A.Scientist
  AND A.Project = B.Project
  AND B.Scientist = Fellow.SSN
  AND NOT Fellow.Name = "Mike";

-- List the name of the projects that are not funded by an
--	 agency ("Better Airplanes", "Better Buildings")
SELECT DISTINCT (PROJECT.Name)
FROM PROJECT,
  FUNDS
WHERE NOT PROJECT.Code IN (
    SELECT FUNDS.Project
    FROM FUNDS);

-- Give the name of the scientist who contributed the most
--	 (in terms of hours) to the project named
-- "Advancing
--	 Airplanes" (Sabine)
SELECT SCIENTIST.Name
FROM SCIENTIST,
  CONTRIBUTESTO
WHERE CONTRIBUTESTO.Hours >= (
    SELECT MAX(Hours)
    FROM CONTRIBUTESTO,
      PROJECT
    WHERE PROJECT.Name = "Advancing Airplanes"
      AND PROJECT.Code = CONTRIBUTESTO.Project)
  AND CONTRIBUTESTO.Scientist = SCIENTIST.SSN;

-- end snippet solution