AAAAHHH

DROP TABLE IF EXISTS Awarded_Movies
DROP TABLE IF EXISTS Awards
DROP TABLE IF EXISTS Nominated

CREATE TABLE Nominated (
    nominatedid CHAR(1) NOT NULL,
    nominated VARCHAR(255) NOT NULL,
    CONSTRAINT pk_Nominated PRIMARY KEY (nominatedid)
)

CREATE TABLE Awards (
    award VARCHAR(255) NOT NULL,
    YEAR INTEGER NOT NULL,
    country VARCHAR(50) NOT NULL,
    CONSTRAINT pk_Awards PRIMARY KEY (award),
    CONSTRAINT ak_Awards UNIQUE (award, YEAR),
    CONSTRAINT fk_Awards_Country FOREIGN KEY (country)
    REFERENCES Country(country_name)
)

CREATE TABLE Awarded_Movies (
    award VARCHAR(255) NOT NULL,
    YEAR INTEGER NOT NULL,
    category VARCHAR(255) NOT NULL,
    movie_id INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    nominatedid CHAR(1) NOT NULL,
    CONSTRAINT pk_Awarded_Movies PRIMARY KEY (award, YEAR, category, movie_id, person_id),
    CONSTRAINT fk_award FOREIGN KEY (award)
    REFERENCES Awards(award),
    CONSTRAINT fk_Award_Movie_Id FOREIGN KEY (movie_id)
    REFERENCES Movie(movie_id),
    CONSTRAINT fk_Award_Person_Id FOREIGN KEY (person_id)
    REFERENCES Person(person_id),
    CONSTRAINT fk_Award_NominatedId FOREIGN KEY (nominatedid)
    REFERENCES Nominated(nominatedid)
)

INSERT INTO Person(person_id, lastname, firstname)
 VALUES (950001, 'Rubel', 'Paul'),
        (950002, 'Hymns', 'Richard'),
        (950003, 'Davis', 'Dane' ),
        (950004, 'Gaeta', 'John'),
        (950005, 'Courley', 'Steve'),
        (950006, 'Thum', 'Jon'),
        (950007, 'Lee', 'David'),
        (950008, 'Reitz' ,'John'),
        (950009, 'Rudloff', 'Gregg'),
        (950010, 'Campbell', 'David'),
        (950011, 'Davis', 'Dane' ),
        (950012, 'Bellfort', 'Tom' ),
        (950013, 'Beemer', 'Bob'),
        (950014, 'Pope','Bill'),
        (950015, 'Hall', 'Conrad'),
        (950016, 'Anwar', 'Tariq')


INSERT INTO Nominated VALUES ('N', 'Nominated'),
('W', 'Won')

INSERT INTO Awards VALUES ('Academy Awards', 2000, 'Netherlands'),
('BAFTA Awards', 2000, 'New Zealand')

INSERT INTO Awarded_Movies VALUES ('Academy Awards', 2000, 'Best Film Editing', 207992, 550813, 'W'),
('Academy Awards', 2000, 'Best Film Editing', 160492, 276884, 'N'),
('Academy Awards', 2000, 'Best Film Editing', 160492, 950001, 'N'),
('Academy Awards', 2000, 'Best Sound Effects Editing', 160492, 68042, 'N'),
('Academy Awards', 2000, 'Best Sound Effects Editing', 303564, 54796, 'N'),
('Academy Awards', 2000, 'Best Sound Effects Editing', 112290, 351397, 'N'),
('Academy Awards', 2000, 'Best Visual Effects', 112290, 950002, 'N'),
('Academy Awards', 2000, 'Best Visual Effects', 207992, 950003, 'W'),
('Academy Awards', 2000, 'Best Visual Effects', 313474, 165453, 'N'),
('Academy Awards', 2000, 'Best Visual Effects', 313474, 351981, 'N'),
('Academy Awards', 2000, 'Best Visual Effects', 313474, 191712, 'N'),
('Academy Awards', 2000, 'Best Visual Effects', 207992, 950004, 'W'),
('Academy Awards', 2000, 'Best Visual Effects', 207992, 540853, 'W'),
('Academy Awards', 2000, 'Best Visual Effects', 207992, 950005, 'W'),
('Academy Awards', 2000, 'Best Visual Effects', 207992, 950006, 'W'),
('BAFTA Awards', 2000, 'Best Sound', 207992, 950007, 'W'),
('BAFTA Awards', 2000, 'Best Sound', 207992, 950008, 'W'),
('BAFTA Awards', 2000, 'Best Sound', 207992, 950009, 'W'),
('BAFTA Awards', 2000, 'Best Sound', 207992, 950010, 'W'),
('BAFTA Awards', 2000, 'Best Sound', 207992, 950011, 'W'),
('BAFTA Awards', 2000, 'Best Sound', 313474, 165453, 'N'),
('BAFTA Awards', 2000, 'Best Sound', 313474, 950012, 'N'),
('BAFTA Awards', 2000, 'Best Sound', 13789, 950013, 'N'),
('BAFTA Awards', 2000, 'Best Cinematography', 207992, 950014, 'N'),
('BAFTA Awards', 2000, 'Best Cinematography', 13789, 950015, 'W'),
('BAFTA Awards', 2000, 'Best Production Design', 207992, 464716, 'N'),
('BAFTA Awards', 2000, 'Best Production Design', 304862, 303143, 'W'),
('BAFTA Awards', 2000, 'Best Editing', 207992, 550813, 'N'),
('BAFTA Awards', 2000, 'Best Editing', 13789, 950016, 'W'),
('BAFTA Awards', 2000, 'Best Editing', 13789, 283753, 'W')