
/*
 * Part I
 */

CREATE SCHEMA HW_CONSTRAINTS_PART1;
USE HW_CONSTRAINTS_PART1;

CREATE TABLE HURRICANE(
    Name VARCHAR(25) PRIMARY KEY,
    WindSpeed INT DEFAULT 76,
    Above VARCHAR(25)
);
--  WindSpeed INT CHECK (WindSpeed > 74 AND WindSpeed < 500),
    
CREATE TABLE STATE(
    Name VARCHAR(25) UNIQUE,
    Postal_abbr CHAR(2) NOT NULL
);

-- Adding a primary key:
ALTER TABLE STATE ADD PRIMARY KEY (Name); 

-- Adding a UNIQUE constraint
ALTER TABLE STATE ADD UNIQUE (Postal_abbr);

-- Drop the NOT NULL constraint
ALTER TABLE STATE MODIFY Postal_abbr CHAR(2);

-- Drop the UNIQUE constraint
ALTER TABLE STATE DROP INDEX Name;

-- Changing the default value
ALTER TABLE HURRICANE ALTER COLUMN WindSpeed SET DEFAULT 74;

--Adding a foreign key constraint
ALTER TABLE HURRICANE ADD FOREIGN KEY (Above) REFERENCES STATE(Name); 

/*
 *  Part II
 */

CREATE SCHEMA HW_CONSTRAINTS_PART2;
USE HW_CONSTRAINTS_PART2;

CREATE TABLE STORM(
    Name VARCHAR(25) PRIMARY KEY,
    Kind ENUM('Tropical Storm', 'Hurricane'),
    WindSpeed INT,
    Creation DATE
    );

-- Changing the enumerated datatype:
ALTER TABLE STORM MODIFY Kind ENUM('Tropical Storm', 'Hurricane', 'Typhoon');
    
CREATE TABLE STATE(
    Name VARCHAR(25) UNIQUE,
    Postal_abbr CHAR(2) PRIMARY KEY,
    Affected_by VARCHAR(25),
    FOREIGN KEY (Affected_by) REFERENCES STORM(Name)
        ON DELETE SET NULL
        ON UPDATE CASCADE
    );


/*
 *  Part III
 */

CREATE SCHEMA HW_CONSTRAINTS_PART3;
USE HW_CONSTRAINTS_PART3;

CREATE TABLE F_Key(
    Attribute VARCHAR(25) PRIMARY KEY
    );

CREATE TABLE Table_default(
    Attribute1 VARCHAR(25) PRIMARY KEY,
    Attribute2 VARCHAR(25),
    FOREIGN KEY (Attribute2) REFERENCES F_Key(Attribute)
    );

    
CREATE TABLE Table_restrict(
    Attribute1 VARCHAR(25) PRIMARY KEY,
    Attribute2 VARCHAR(25),
    FOREIGN KEY (Attribute2) REFERENCES F_Key(Attribute)
        ON DELETE RESTRICT
        ON UPDATE RESTRICT
    );
    
    
CREATE TABLE Table_cascade(
    Attribute1 VARCHAR(25) PRIMARY KEY,
    Attribute2 VARCHAR(25),
    FOREIGN KEY (Attribute2) REFERENCES F_Key(Attribute)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    );
    
CREATE TABLE Table_set_null(
    Attribute1 VARCHAR(25) PRIMARY KEY,
    Attribute2 VARCHAR(25),
    FOREIGN KEY (Attribute2) REFERENCES F_Key(Attribute)
        ON DELETE SET NULL
        ON UPDATE SET NULL 
    );