DROP SCHEMA IF EXISTS HW_CAPSTONE;

CREATE SCHEMA HW_CAPSTONE;

USE HW_CAPSTONE;

CREATE TABLE STUDENT (
  FName VARCHAR(50),
  Id CHAR(13) PRIMARY KEY,
  GraduationYear INT,
  GraduationSemester ENUM ("Fall", "Spring", "Summer")
);

CREATE TABLE PROGRAMMING_LANGUAGE (
  NAME VARCHAR(50) PRIMARY KEY,
  Licence VARCHAR(50)
);

CREATE TABLE PROJECT (
  CodeName VARCHAR(50),
  Leader CHAR(13),
  PRIMARY KEY (CodeName, Leader),
  FOREIGN KEY (Leader) REFERENCES STUDENT (Id)
);

CREATE TABLE USED_LANGUAGE (
  ProjectCodeName VARCHAR(50),
  ProjectLeader CHAR(13),
  UsedLanguage VARCHAR(50),
  PRIMARY KEY (ProjectCodeName, ProjectLeader, UsedLanguage),
  FOREIGN KEY (ProjectCodeName, ProjectLeader) REFERENCES
    PROJECT (CodeName, Leader),
  FOREIGN KEY (UsedLanguage) REFERENCES PROGRAMMING_LANGUAGE (NAME)
);


/*
 The meaning of the USED_LANGUAGE table is that a tuple  < N, L, U> represents the fact that the project whose code name is N and whose leader is L uses the programming language U.
 */
INSERT INTO STUDENT
VALUES (
  "Mary",
  "0123456789100",
  2025,
  "Summer"),
(
  "Steve",
  "0000000000000",
  2025,
  "Fall"),
(
  "Claude",
  "9999999999999",
  2024,
  "Fall"),
(
  "Meghan",
  "0987654321098",
  2023,
  "Spring");

INSERT INTO PROGRAMMING_LANGUAGE
VALUES (
  "Rust",
  "MIT"),
(
  ".NET Core",
  "MIT"),
(
  "Racket",
  "LGPL"),
(
  "Python",
  "PSF");

-- Taken from
-- https://en.wikipedia.org/wiki/Comparison_of_open-source_programming_language_licensing
INSERT INTO PROJECT
VALUES (
  "Brick Break",
  "0123456789100"),
(
  "Brick Break",
  "0000000000000"),
(
  "Grade Calculator",
  "0123456789100"),
(
  "Undecided",
  "9999999999999");

INSERT INTO USED_LANGUAGE
VALUES (
  "Brick Break",
  "0123456789100",
  "Rust"),
(
  "Brick Break",
  "0000000000000",
  ".NET Core"),
(
  "Brick Break",
  "0000000000000",
  "Python"),
(
  "Grade Calculator",
  "0123456789100",
  "Racket");


/*
 * You can start editing starting here.
 */
--  start snippet solution
/*
code/sql/HW_CapstoneSol.sql
 */
/*

I. Short Questions (6 pts)

Answer the following short questions based on the model implemented above.
You can simply answer "True" or "False", or justify your reasoning (e.g. with code).
 */
-- 1. Can a project uses multiple programming languages?
--		Yes.
--       2. Can a student be the leader of multiple
--	  projects?
--		Yes.
--       3. Can multiple projects have the same code name?
--		Yes.
--       4. Could Claude simply enter NULL for the value
--	  of his pproject's code name, since he's undecided?
--       No.
--       5. Can a project be created without project
--	  leader?
--       No.
--      6. Can we know who is working on a project
--	 without being its leader?
--       No.
/*

II. 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 the sheet where you drew it.
 */
/*

III. Simple Commands (8 pts.)

Below, you are asked to write commands that perform various actions.
Please, leave them uncommented, unless you can't write them correctly, in which case it's ok to leave them commented.
The first question is answered as an example.
 */
-- 0. Write a command that list all the names of the
--	       programming languages.
SELECT Name
FROM PROGRAMMING_LANGUAGE;

-- 1. Write a command that insert a new student in the
--	       STUDENT table.
--		(You should invent the values).
INSERT INTO STUDENT
VALUES (
  "Bob",
  "0987654321234",
  NULL,
  NULL);

-- 2. Write a command that updates the code name of the
--	  project ("Undecided", "9999999999999") to "VR in
--	  ER".
UPDATE
  PROJECT
SET CodeName = "VR in ER"
WHERE CodeName = "Undecided"
  AND Leader = "9999999999999";

-- 3. Write a command that updates the graduation year of the
--	  student whose id is "0987654321098" to 2024, and
--	  the semester to "Fall".
UPDATE
  STUDENT
SET GraduationYear = 2024,
  GraduationSemester = "Fall"
WHERE id = "0987654321098";

-- 4. Write a command that changes the STUDENT table to make
--	  it impossible to enter NULL for the first name of
--	  a student, without changing the primary key.
ALTER TABLE STUDENT MODIFY FName VARCHAR(50) NOT NULL;

-- 5. Write a command that changes the datatype of
--	  GraduationYear to SMALLINT.
ALTER TABLE STUDENT MODIFY GraduationYear SMALLINT;

-- 6. Write a command that adds an attribute "ReleaseDate" to
--	  the PROJECT table.
ALTER TABLE PROJECT
  ADD COLUMN ReleaseDate DATE;

-- 6.bis If you managed to write the previous command
--	 correctly, write a command that sets the release
--	 date of the project ("Brick Break",
--  "0123456789100")
--     to
--	 the 26th of November 2022.
UPDATE
  PROJECT
SET ReleaseDate = DATE "20221126"
WHERE CodeName = "Brick Break"
  AND Leader = "0123456789100";

-- 7. Write a command that makes it impossible for a student
--	  to be the leader in more than one project
--	  (This command should return an error)
--	  ALTER TABLE PROJECT ADD UNIQUE (Leader);