/* SQLite */

/* Create tables */
/* Done */
CREATE TABLE Movie(
    MID INT NOT NULL,
    Title VARCHAR(80) NOT NULL,
    Year INT NOT NULL,
    Budget INT,
    Runtime INT NOT NULL,
    PRIMARY KEY (MID)
);

/* Done */
CREATE TABLE Person(
    PID INT NOT NULL,
    Fname VARCHAR(80) NOT NULL,
    Lname VARCHAR(80) NOT NULL,
    Gender VARCHAR(20) NOT NULL,
    PRIMARY KEY (PID)
);

/* Done */
CREATE TABLE Award(
    AID INT NOT NULL,
    Giver VARCHAR(80) NOT NULL,
    Title VARCHAR(80) NOT NULL,
    Role VARCHAR(80) NOT NULL,
    PRIMARY KEY(AID)
);

/* Done */
CREATE TABLE Song(
    Movie INT NOT NULL,
    Name VARCHAR(80) NOT NULL,
    Year INT NOT NULL,
    Original INT NOT NULL CHECK (Original >= 0),
    FOREIGN KEY (Movie) REFERENCES Movie(MID),
    PRIMARY KEY (Movie, Name, Year)
);

/* Done */
CREATE TABLE Directs(
    Director INT NOT NULL,
    Movie INT NOT NULL,
    PRIMARY KEY (Director, Movie),
    FOREIGN KEY (Director) REFERENCES Person(PID),
    FOREIGN KEY (Movie) REFERENCES Movie(MID)
);

/* Done */
CREATE TABLE Writes(
    Writer INT NOT NULL,
    Movie INT NOT NULL,
    PRIMARY KEY (Writer, Movie),
    FOREIGN KEY (Writer) REFERENCES Person(PID),
    FOREIGN KEY (Movie) REFERENCES Movie(MID)
);

/* Done */
CREATE TABLE Stars(
    Star INT NOT NULL,
    Movie INT NOT NULL,
    PRIMARY KEY (Star, Movie),
    FOREIGN KEY (Star) REFERENCES Person(PID),
    FOREIGN KEY (Movie) REFERENCES Movie(MID)
);

/* Done */
CREATE TABLE Nominated(
    Award INT NOT NULL,
    Movie INT NOT NULL,
    Won INT NOT NULL CHECK (Won >= 0),
    Year INT NOT NULL,
    PRIMARY KEY (Award, Movie),
    FOREIGN KEY (Award) REFERENCES Award(AID),
    FOREIGN KEY (Movie) REFERENCES Movie(MID)
);

/* Insert The Room */
INSERT INTO Movie(MID, Title, Year, Budget, Runtime)
    VALUES(1, "The Room", 2003, 6000000, 102);

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(1, "Tommy", "Wiseau", "Male");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(2, "Greg", "Sestero", "Male");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(3, "Juliette", "Danielle", "Female");

INSERT INTO Directs(Director, Movie)
    VALUES(1, 1);

INSERT INTO Writes(Writer, Movie)
    VALUES(1, 1);

INSERT INTO Stars(Star, Movie)
    VALUES(1, 1);

INSERT INTO Stars(Star, Movie)
    VALUES(2, 1);

INSERT INTO Stars(Star, Movie)
    VALUES(3, 1);

INSERT INTO Song(Movie, Name, Year, Original)
    VALUES(1, "You're My Rose", 2003, 1);

/* Insert Eraserhead */

INSERT INTO Movie(MID, Title, Year, Budget, Runtime)
    VALUES(2, "Eraserhead", 1977, 10000, 89);

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(4, "David", "Lynch", "Male");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(5, "Jack", "Nance", "Male");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(6, "Charlotte", "Stewart", "Female");

INSERT INTO Directs(Director, Movie)
    VALUES(4, 2);

INSERT INTO Writes(Writer, Movie)
    VALUES(4, 2);

INSERT INTO Stars(Star, Movie)
    VALUES(5, 2);

INSERT INTO Stars(Star, Movie)
    VALUES(6, 2);

/* Insert Twin Peaks: Fire Walk With Me */

INSERT INTO Movie(MID, Title, Year, Budget, Runtime)
    VALUES(3, "Twin Peaks: Fire Walk With Me", 1992, 10000000, 135);

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(7, "Sheryl", "Lee", "Female");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(8, "Ray", "Wise", "Male");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(9, "Robert", "Engels", "Male");

INSERT INTO Directs(Director, Movie)
    VALUES(4, 3);

INSERT INTO Writes(Writer, Movie)
    VALUES(4, 3);

INSERT INTO Writes(Writer, Movie)
    VALUES(9, 3);

INSERT INTO Stars(Star, Movie)
    VALUES(7, 3);

INSERT INTO Stars(Star, Movie)
    VALUES(8, 3);

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(1, "Cannes Film Festival", "Palme d'Or", "Director");

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(1, 3, 0, 1992);

/* Insert Mulholland Drive */

INSERT INTO Movie(MID, Title, Year, Budget, Runtime)
    VALUES(4, "Mulholland Dr.", 2001, 15000000, 146);

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(10, "Naomi", "Watts", "Female");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(11, "Laura", "Harring", "Female");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(12, "Justin", "Theroux", "Male");

INSERT INTO Directs(Director, Movie)
    VALUES(4, 4);

INSERT INTO Writes(Writer, Movie)
    VALUES(4, 4);

INSERT INTO Stars(Star, Movie)
    VALUES(10, 4);

INSERT INTO Stars(Star, Movie)
    VALUES(11, 4);

INSERT INTO Stars(Star, Movie)
    VALUES(12, 4);

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(2, "Academy Awards", "Best Director", "Director");

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(3, "Golden Globes", "Best Motion Picture - Drama", "Director");

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(4, "Golden Globes", "Best Director - Motion Picture", "Director");

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(5, "Golden Globes", "Best Screenplay - Motion Picture", "Writer");

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(6, "Golden Globes", "Best Original Score - Motion Picture", "Composer");

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(7, "Cannes Film Festival", "Best Director", "Director");

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(2, 4, 0, 2002);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(3, 4, 0, 2002);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(4, 4, 0, 2002);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(5, 4, 0, 2002);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(6, 4, 0, 2002);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(7, 4, 1, 2001);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(1, 4, 0, 2001);

/* Insert Taxi Driver */

INSERT INTO Movie(MID, Title, Year, Budget, Runtime)
    VALUES(5, "Taxi Driver", 1976, 1300000, 114);

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(13, "Martin", "Scorsese", "Male");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(14, "Paul", "Schrader", "Male");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(15, "Robert", "De Niro", "Male");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(16, "Jodie", "Foster", "Female");

INSERT INTO Person(PID, Fname, Lname, Gender)
    VALUES(17, "Cybill", "Shepherd", "Female");

INSERT INTO Directs(Director, Movie)
    VALUES(13, 5);

INSERT INTO Writes(Writer, Movie)
    VALUES(14, 5);

INSERT INTO Stars(Star, Movie)
    VALUES(15, 5);

INSERT INTO Stars(Star, Movie)
    VALUES(16, 5);

INSERT INTO Stars(Star, Movie)
    VALUES(17, 5);

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(8, "Academy Awards", "Best Picture", "Director");

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(9, "Academy Awards", "Best Actor in a Leading Role", "Actor");

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(10, "Academy Awards", "Best Actress in a Supporting Role", "Actress");

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(11, "Academy Awards", "Best Music, Original Score", "Composer");

INSERT INTO Award(AID, Giver, Title, Role)
    VALUES(12, "Golden Globes", "Best Actor in Motion Picture - Drama", "Actor");

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(8, 5, 0, 1977);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(9, 5, 0, 1977);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(10, 5, 0, 1977);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(11, 5, 0, 1977);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(12, 5, 0, 1977);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(5, 5, 0, 1977);

INSERT INTO Nominated(Award, Movie, Won, Year)
    VALUES(1, 5, 1, 1976);