/*
 CSCI 3410 -- Project #1
 Name: -- Fill here
 Date: -- Fill here
 */
/* 
 DO NOT EDIT BELOW 
 */
DROP SCHEMA IF EXISTS HW_Vaccine;

CREATE SCHEMA HW_Vaccine;

USE HW_Vaccine;

-- start snippet setup
/* code/sql/HW_Vaccine.sql */
CREATE TABLE COMPANY (
  Name VARCHAR(50) PRIMARY KEY,
  Website VARCHAR(255) CHECK (Website LIKE "https://%")
);

CREATE TABLE DISEASE (
  Name VARCHAR(50) PRIMARY KEY,
  Communicable BOOL,
  -- Whether the disease can be transmitted from a human to
  --      another.
  TYPE ENUM ("infectious", "deficiency", "hereditary")
);

CREATE TABLE VACCINE (
  Name VARCHAR(50) PRIMARY KEY,
  Manufacturer VARCHAR(50) NOT NULL,
  FOREIGN KEY (Manufacturer) REFERENCES COMPANY (NAME) ON
    UPDATE CASCADE
);

CREATE TABLE EFFICACY (
  DiseaseName VARCHAR(50),
  VaccineName VARCHAR(50),
  Efficacy DECIMAl(5, 2),
  PRIMARY KEY (DiseaseName, VaccineName),
  FOREIGN KEY (DiseaseName) REFERENCES DISEASE (NAME),
  FOREIGN KEY (VaccineName) REFERENCES VACCINE (NAME)
);

INSERT INTO COMPANY
VALUES (
  "Moderna",
  "https://www.modernatx.com/");

INSERT INTO DISEASE
VALUES (
  "Coronavirus disease 2019",
  TRUE,
  "infectious");

INSERT INTO VACCINE
VALUES (
  "mRNA-1273",
  "Moderna");

INSERT INTO EFFICACY
VALUES (
  "Coronavirus disease 2019",
  "mRNA-1273",
  94.1);

-- end snippet setup
/*
 START EDITING
 */
/*

I. Short Questions (3 pts.)

Answer the following short questions. In our implementation…

1. … can two companies have exactly the same name?

2. … can two companies have the same website?

3. … can a company not have a website?

4. … can the same vaccine be manufactured by multiple companies?

5. … can a vaccine not have a manufacturer?

6. … can a disease being neither communicable nor not communicable?

7. … can the same vaccine have different efficacies for different diseases?
 */
/*

II. Longer Questions (6 pts.)

Answer the following questions:

1. What does `CHECK (Website LIKE "https://*")` do?

2. Why did we picked the `DECIMAl(5,2)` datatype?

3. What is the benefit / are the benefits of having a separate EFFICACY table over having something like

CREATE TABLE VACCINE(
 Name VARCHAR(50) PRIMARY KEY,
 Manufacturer VARCHAR(50),
 Disease VARCHAR(50),
 Efficacy DECIMAl(5,2),
 FOREIGN KEY (Manufacturer) REFERENCES COMPANY (Name)
);

?
 */
/*

III. Relational Model (6 pts.)

Draw the relational model corresponding to this code.
You can hand-draw it and join a scan or a picture, or simply hand me back a sheet.
 */
/*

IV. Simple Commands (5 pts.)

Below, you are asked to write commands that perform various actions.
Please, leave them uncommented, unless
 - you can not write them correctly, but want to share your attempt,
 - it is specified that it should return an error.

The first question is answered as an example.
 */
-- 0. Write a command that list the names of
--	all the diseases.
SELECT Name
FROM DISEASE;

-- 1. Write a command that insert "Pfizer" in the
--	 COMPANY table (you can make up the website or look
--    it)
--     2. Write a command that insert the "Pfizer-BioNTech
--	COVID-19 Vaccine" in the VACCINE table, and a
--  command
--	that store the efficacy of that vaccine against
--	the "Coronavirus disease 2019" disease
--	(you can make up the values or look them up).
--      3. Write a command that updates the name of the
--	 company "Moderna" to "Moderna, Inc." everywhere.
--      4. Write a command that lists the name of all the
--	 companies.
--      5. Write a command that deletes the "Coronavirus
--   disease
--	 2019" entry from the DISEASE table (if only!).
--      This command should return an error. Explain it and
--    leave
--       the command commented.
--       6. Write two commands: one that adds "physiological"
--   to
--	  the possible types of diseases, and one that
--   inserts
--	  a physiological disease in the DISEASE table.
--      7 (difficult). Write a command that return the list
-- of
--		     all the companies that manufacture a
--		     vaccine against "Coronavirus disease
--    2019".