DROP TRIGGER fkd_participates_movieid;

DROP TRIGGER fkd_participates_actorid;

DROP TABLE Participates;

DROP TABLE Movie;

DROP TABLE Actor;

CREATE TABLE Movie (
MovieID INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT,
MovieName VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Actor (
ActorID INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT,
ActorName VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Participates (
Movie_MovieID INTEGER NOT NULL,
Actor_ActorID INTEGER NOT NULL,
PRIMARY KEY (Movie_MovieID,Actor_ActorID)
);

CREATE TRIGGER fkd_participates_movieid
BEFORE DELETE ON Movie
FOR EACH ROW BEGIN 
  DELETE FROM Participates WHERE Movie_MovieID = OLD.MovieID;
END;

CREATE TRIGGER fkd_participates_actorid
BEFORE DELETE ON Actor
FOR EACH ROW BEGIN 
  DELETE FROM Participates WHERE Actor_ActorID = OLD.ActorID;
END;

INSERT INTO Actor (ActorID, ActorName) VALUES (1, 'Tom Hanks');
INSERT INTO Actor (ActorID, ActorName) VALUES (2, 'Denzel Washington');
INSERT INTO Actor (ActorID, ActorName) VALUES (3, 'Julia Roberts');
INSERT INTO Actor (ActorID, ActorName) VALUES (4, 'Samuli Edelmann');

INSERT INTO Movie (MovieID, MovieName) VALUES (1, 'Häjyt');
INSERT INTO Movie (MovieID, MovieName) VALUES (2, 'Forrest Gump');
INSERT INTO Movie (MovieID, MovieName) VALUES (3, 'Philadelphia');
INSERT INTO Movie (MovieID, MovieName) VALUES (4, 'Musta jää');

INSERT INTO Participates (Actor_ActorID, Movie_MovieID) VALUES (1, 2);
INSERT INTO Participates (Actor_ActorID, Movie_MovieID) VALUES (1, 3);
INSERT INTO Participates (Actor_ActorID, Movie_MovieID) VALUES (2, 3);
INSERT INTO Participates (Actor_ActorID, Movie_MovieID) VALUES (4, 1);