DROP TABLE vod_film;
DROP TABLE vod_classification;

CREATE TABLE vod_classification (
dbClassID CHAR(3) NOT NULL,
dbDescription VARCHAR2(255) NOT NULL,
CONSTRAINT vod_classification_PK PRIMARY KEY (dbClassID)
);

CREATE TABLE vod_film(
dbFilmID                NUMBER(4) NOT NULL,
dbTitle                 VARCHAR2(100) NOT NULL,
dbDirector_firstname    VARCHAR2(50) NOT NULL,
dbDirector_lastname     VARCHAR2(50) NOT NULL,
dbGenre                 VARCHAR2(20),
dbUK_release_date       DATE,
dbFileName              VARCHAR2(50),
dbRuntime               NUMBER(4),
dbClass                 CHAR(3),
CONSTRAINT vod_film_PK PRIMARY KEY (dbFilmID)
REFERENCES vod_classification (dbClassID)
ON DELETE SET NULL
);


INSERT INTO vod_classification (dbClassID, dbDescription)
VALUES ('U', 'Universal: Suitable for all');

INSERT INTO vod_classification (dbClassID, dbDescription)
VALUES ('PG', 'Parental Guidance: General viewing but some scenes may be unsuitable for young
children');

INSERT INTO vod_classification (dbClassID, dbDescription)
VALUES ('12', 'Suitable only for persons of 12 years or over');

INSERT INTO vod_classification (dbClassID, dbDescription)
VALUES ('15', 'Suitable only for persons of 15 years or over');

INSERT INTO vod_classification (dbClassID, dbDescription)
VALUES ('18', 'Suitable only for persons of 18 years or over');






INSERT INTO vod_film (dbFilmID, dbTitle, dbDirector_firstname, dbDirector_lastname, dbGenre, dbUK_release_date, dbFileName, dbRuntime, dbClass)
VALUES ('1', 'Toy Story 3', 'lee', 'unkrich', 'Comedy', '19-JUL-2010', 'ToyStory3.mpg', '103', 'U');

INSERT INTO vod_film (dbFilmID, dbTitle, dbDirector_firstname, dbDirector_lastname, dbGenre, dbUK_release_date, dbFileName, dbRuntime, dbClass)
VALUES ('2', 'Lord of the Rings: Fellowship of the Lord', 'Peter', 'Jackson', 'Fantasy', '19-DEC-2001', 'Fellowship.mpg', '178', '12');

INSERT INTO vod_film (dbFilmID, dbTitle, dbDirector_firstname, dbDirector_lastname, dbGenre, dbUK_release_date, dbFileName, dbRuntime, dbClass)
VALUES ('3', 'Lord of the Rings: Two Towers', 'Peter', 'Jackson', 'Fantasy', '18-DEC-2002', 'TwoTowers.mpg', '179', '12');

INSERT INTO vod_film (dbFilmID, dbTitle, dbDirector_firstname, dbDirector_lastname, dbGenre, dbUK_release_date, dbFileName, dbRuntime, dbClass)
VALUES ('4', 'Lord of the Rings: Return of the King', 'Peter', 'Jackson', 'Fantasy', '17-DEC-2003', 'KingReturns.mpg', '201', '12');

INSERT INTO vod_film (dbFilmID, dbTitle, dbDirector_firstname, dbDirector_lastname, dbGenre, dbUK_release_date, dbFileName, dbRuntime, dbClass)
VALUES ('5', 'Face/Off', 'John', 'Woo', 'Action', '7-NOV-1997', 'FaceOff.mpg', '138', '18');

INSERT INTO vod_film (dbFilmID, dbTitle, dbDirector_firstname, dbDirector_lastname, dbGenre, dbUK_release_date, dbFileName, dbRuntime, dbClass)
VALUES ('6', 'The Nutty Professor', 'Tom', 'Shadyac', 'Comedy', '4-OCT-1996', 'NuttyProf.mpg', '95', '12');