CREATE DATABASE DVD_KOLEKCIJA;
GO

USE DVD_KOLEKCIJA;

CREATE TABLE Tip_Uloge (
	TipUlogeID INT PRIMARY KEY,
	Tip VARCHAR(50)
);

CREATE TABLE Glumac 
(
	GlumacID INT PRIMARY KEY,
	Ime VARCHAR(255),
	Prezime VARCHAR(255),
	DatumRodjenja DATE,
	MestoRodjenja VARCHAR(255)
);

CREATE TABLE Producent
(
	ProducentID INT PRIMARY KEY,
	Ime VARCHAR(255),
	Email VARCHAR(25),
	WebSite VARCHAR(255)
);

CREATE TABLE Zanr
(
	ZanrID INT PRIMARY KEY,
	NazivZanra VARCHAR(250)
);
GO

CREATE TABLE Film
(
	FilmID INT PRIMARY KEY,
	Naziv VARCHAR(50),
	DatumIzlaska DATE,
	Trajanje TIME,
	ZanrID INT,
	OpisRadnje VARCHAR(255),
    CONSTRAINT fk_film_zanr FOREIGN KEY (ZanrID) REFERENCES Zanr(ZanrID)
);
GO

CREATE TABLE Producirao
(
	FilmID INT,
	ProducentID INT,
    CONSTRAINT fk_Producirao_Film FOREIGN KEY (FilmID) REFERENCES Film(FilmID),
    CONSTRAINT fk_Producirao_Producant FOREIGN KEY (ProducentID) REFERENCES Producent(ProducentID) 
);
CREATE TABLE Uloga
(
	FilmID INT,
	GlumacID INT,
	TipUlogeID INT,
	ImeLika VARCHAR(25),
	OpisLika VARCHAR(255),
	CONSTRAINT pk_uloga PRIMARY KEY (FilmID,GlumacID,ImeLika),
    CONSTRAINT fk_uloga_film FOREIGN KEY (FilmID) REFERENCES Film(FilmID),
    CONSTRAINT fk_uloga_glumac FOREIGN KEY (GlumacID) REFERENCES Glumac(GlumacID),
    CONSTRAINT fk_uloga_tipUl FOREIGN KEY (TipUlogeID) REFERENCES Tip_Uloge(TipUlogeID)
);
CREATE TABLE Nagrada
(
	NagradaID INT PRIMARY KEY,
	Naziv VARCHAR(50),
	GodinaPocetka DATE
);
GO

CREATE TABLE Film_Nagrada
(
	FilmID INT,
	NagradaID INT,
	GodinaDodele DATE,
	CONSTRAINT pk_film_nagrada PRIMARY KEY (FilmID,NagradaID),
	CONSTRAINT fk_FN_Film FOREIGN KEY (FilmID) REFERENCES Film(FilmID),
	CONSTRAINT fk_FN_Nagrada FOREIGN KEY (NagradaID) REFERENCES Nagrada(NagradaID)
);
GO


INSERT INTO tip_uloge(TipUlogeID, Tip)
 VALUES (1, 'Glavna'), (2, 'Sporedna'), (3, 'Dodatak');

INSERT INTO Glumac(GlumacID, Ime, Prezime, DatumRodjenja, MestoRodjenja)
 VALUES (1, 'Đorđe', 'Timotijev', null, null),
     	(2, 'Vladimir', 'Vladimirovič', '1966-10-12', 'SSSR'),
        (3, 'Johny', 'Deep', '1963-06-09', 'US'),
        (4, 'Vigo', 'Mortensen', '1958-10-20', 'New York'),  
        (5, 'Jovan', 'Jovanović', '1956-02-11', 'US'),
        (6, 'Natalie', 'Dormer', '1976-05-22', 'UK'),
        (7, 'Robert', 'De Niro', '1966-03-12', 'New York'),
        (8, 'Tom', 'Hanks', '1971-07-21', 'Francuska'),
        (9, 'Velimir', 'Živojinović', '1933-06-05', 'Kraljevina Jugoslavija'),
        (10, 'Jovan', 'Jovanović', '1936-12-02', 'Leskovac'),
        (111, 'John', 'Miler', '1984-02-08', 'New York'),
        (12, 'Sarah', 'Johnson', '1988-04-20', 'London, UK');


INSERT INTO producent(ProducentID, Ime, Email)
 VALUES ( 1, 'Jovan', 'jovan@acme.com'),
 		( 2, 'Milan', 'milan@acme.com'),
        ( 3, 'Katarina', 'katarina@acme.com'),
        ( 4, 'Nikola', 'nikola@acme.com'),
        ( 5, 'Ana', 'ana@acme.com');
        
 INSERT INTO producent(ProducentID, Ime, Email)
 VALUES (6, 'Four Star Television','ummi7@mail.org'), 
		(7, '20th Century Fox', 'filmjtk@outlook.net'), 
		(8, 'BBC Films', 'adresa@gmail.com'),
		(9, 'DreamWorks', 'dschs@outlook.com'), 
		(17, 'American International Pictures', 'kmcsnk21@hotmail.com'), 
		(18, 'Capitol Films', 'ndcjds34@gmail.com'), 
		(19, 'Cinemation Industries', 'cdscejn@outlook.com'), 
		(20, 'Columbia TriStar Motion Picture Group', 'vbhj12@hotmail.com');
        

INSERT INTO zanr(ZanrID, NazivZanra)
	VALUES 	( 1, 'Drama'),
    		( 2, 'Krimi'),
            ( 3, 'Avantura'),
            ( 4, 'Akcija'),
            ( 5, 'Komedija'),
            ( 6, 'Fantazija');

INSERT INTO film (FilmID, Naziv, DatumIzlaska, Trajanje, ZanrID, OpisRadnje) 

VALUES ('3', 'Kum', '1972-04-15', '02:33:29', '1', 'Priča filma pokriva period od 10 godina (1945-1955) i prati italijansko-američku porodicu Korleone. '),
       ('4', 'Gospodar prstenova', '2006-12-17', '03:02:46', '6', 'filmski serijal od tri epska avanturistička filma u režiji Pitera Džeksona, zasnovana na istoimenom romanu Dž. R. R. Tolkina. '),
	   ('5', 'Valter brani Sarajevo', '1972-11-24', '02:22:32', '2', NULL),
	   ('6', 'Clash of Titans', '2012-04-14', '01:05:23', '4', NULL),
	   ('7', 'Pirates of the Carebbean', '2012-04-14', '2:17:03', '3', NULL);
	   
INSERT INTO film (FilmID, Naziv, DatumIzlaska, Trajanje, ZanrID, OpisRadnje) 

VALUES ('61', 'The Rifleman', '1958-04-14', '0:30:03', 4, 'western, family'), 
		('62', 'The Big Valley ', '1965-04-14', '1:00:03', 4, 'western, family'),
		 ('68', 'Wanted: Dead or Alive  ', '1958-04-14', '0:30:03', 4, 'western, family'),
		 ('69', 'Stagecoach West ', '1960-04-14', '1:00:03', 4, 'western'),
		 ('60', 'Off to See the Wizard ', '1967-04-14', '1:00:03', 6, 'Animation, Family, Fantasy '),
		 ('71', 'Titan A.E.', '2000-06-16', '1:34:03', 6, ' Fantasy '),
		 ('72', 'X-Men', '2000-04-14', '1:00:03', 6, 'Fantasy '),
		 ('73', 'Kiss of the Dragon', '1967-04-14', '1:00:03', 4, 'Action'),
		 ('74', 'Ice Age', '2001-07-14', '1:00:03', 3, 'Animation, Family, Fantasy '),
		 ('75', 'Minority Report', '2002-04-14', '1:00:03', 1, 'Triler'),
		 ('76', 'The Transporter', '2002-04-14', '1:00:03', 2, 'Action, Family,'),
		 ('77', 'Solaris', '2002-11-11', '1:00:03', 6, 'Fantasy '),
		 ('81', 'Coriolanus', '2012-04-14', '1:00:03', 1, 'History '),
		 ('80', 'Dancer', '2017-04-14', '1:00:03', '3', 'Balet'),
		 ('82', 'Great Expectations', '2012-11-14', '1:00:03', 2, 'Dickens Classics'),
		 ('90', 'Antz', '1998-04-14', '1:00:03', 3, 'Animation, Family'),
		 ('91', 'The Prince of Egypt', '1998-04-14', '1:00:03', 3, 'Animation, Family'),
		 ('92', 'The Road to El Dorado', '1998-04-14', '1:00:03', 3, 'Animation, Family'),
		 ('93', 'Chicken Run', '1998-04-14', '1:00:03', 3, 'Animation, Family'),
		 ('94', 'Shrek', '1998-04-14', '1:00:03', 3, 'Animation, Family'),
		 ('95', 'Shark Tale', '1998-04-14', '1:00:03', 3, 'Animation, Family'),
		 ('96', 'Madagascar', '1998-04-14', '1:00:03', 5, 'Animation, Family'),
		 ('97', 'Bee Movie', '1998-04-14', '1:00:03', 5, 'Animation, Family'),
		 ('98', 'Kung Fu Panda', '1998-04-14', '1:00:03', 5, 'Animation, Family'),
		 ('99', 'Puss in Boots', '1998-04-14', '1:00:03', 5, 'Animation, Family'),
		 ('900', 'Rise of the Guardians', '1998-04-14', '1:00:03', 4, 'Animation, Family'),
		 ('906', 'Abominable', '1998-04-14', '1:00:03', 1, 'Animation, Family'),
		 ('916', 'Spirit Untamed', '1998-04-14', '1:00:03', 2, 'Animation, Family'),
		 ('170', 'Abby', '1974-04-14', '1:00:03', 3, 'Animation, Family, Fantasy '),
		 ('171', 'Apache Woman', '1955-04-14', '1:00:03', 4, 'Animation, Family, Fantasy '),
		 ('172', 'The Bat People', '1974-04-14', '1:00:03', 5, 'Blacula'),
		 ('173', 'Blacula', '1972-04-14', '1:00:03', 6, 'Blacula'),
		 ('174', 'Killer Force', '1976-04-14', '1:00:03', 1, 'Animation, Family, Fantasy '),
		 ('175', 'Erik the Conqueror', '1961-04-14', '1:00:03', 2, 'Animation, Family, Fantasy '),
		 ('176', 'Operation Bikini', '1963-04-14', '1:00:03', 3, 'Animation, Family, Fantasy '),
		 ('177', 'Our Man in Marrakesh', '1966-04-14', '1:00:03', 4, 'Animation, Family, Fantasy '),
		 ('18', 'Dok đavo ne sazna da si mrtav', '2007-04-14', '1:57:03', 5, 'Animation, Family, Fantasy '),
		 ('180', 'Alpha Dog', '2006-04-14', '2:00:03', 1, 'Biography, Crime, Drama '),
		 ('181', 'Jeepers Creepers ', '2001-04-14', '1:30:03', 1, 'Horror, Mystery '),
		 ('182', 'Gosford Park ', '2001-04-14', '2:17:03', 5, 'Comedy, Drama, Mystery '),
		 
		 ('19', 'Fritz the Cat', '1972-04-14', '1:17:03', 5, 'Animation, Comedy, Drama'),
		 ('190', 'Johnny Got His Gun ', '1971-04-14', '1:51:03', 4, 'Drama, War '),
		 ('191', 'Turkish Delight ', '1971-04-14', '2:17:03', 3, 'Comedy, Drama, Mystery '),
		 ('192', 'Baby Doll', '1971-04-14', '2:17:03', 2, 'Comedy, Drama, Mystery '),
		 ('193', 'A Dogs Life ', '1971-04-14', '2:17:03', 1, 'Comedy, Drama, Mystery '),
		 ('194', 'Devil Times Five', '1971-04-14', '2:17:03', 1, 'Comedy, Drama, Mystery '),
		 ('195', 'Top Sensation', '1971-04-14', '2:17:03', 2, 'Comedy, Drama, Mystery '),
		 
		 ('20', 'Renegades', '1946-06-06', '2:17:03', 3, 'Comedy, Drama, Mystery '),
		 ('200', 'The Unknown', '1946-06-06', '2:17:03', 3, 'Comedy, Drama, Mystery '),
		 ('201', 'The Jolson Story', '1946-06-06', '2:17:03', 3, 'Comedy, Drama, Mystery '),
		 ('202', 'The Secret of the Whistler', '1946-06-06', '2:17:03', 4, 'Comedy, Drama, Mystery '),
		 ('203', 'The Fighting Frontiersman', '1946-06-06', '2:17:03', 4, 'Comedy, Drama, Mystery '),
		 ('204', 'Alias Mr. Twilight', '1946-06-06', '2:17:03', 4, 'Comedy, Drama, Mystery '),
		 ('205', 'Dead Reckoning', '1947-06-06', '2:17:03', 4, 'Comedy, Drama, Mystery '),
		 ('206', 'The Thirteenth Hour', '1947-06-06', '2:17:03', 4, 'Comedy, Drama, Mystery '),
		 ('207', 'Gunfighters', '1947-06-06', '2:17:03', 4, 'Comedy, Drama, Mystery '),
		 ('208', 'Down to Earth', '1947-06-06', '2:17:03', 5, 'Comedy, Drama, Mystery '),
		 ('209', 'The Swordsman', '1946-06-06', '2:17:03', 5, 'Comedy, Drama, Mystery '),
		 ('210', 'Adventures in Silverado', '1946-06-06', '2:17:03', 5, 'Comedy, Drama, Mystery '),
		 ('219', 'Best Man Wins', '1946-06-06', '2:17:03', 6, 'Comedy, Drama, Mystery '),
		 ('229', 'The Lady from Shanghai', '1946-06-06', '2:17:03', 6, 'Comedy, Drama, Mystery '),
		 ('239', 'Coroner Creek', '1946-06-06', '2:17:03', 1, 'Comedy, Drama, Mystery '),
		 ('249', 'The Return of October', '1946-06-06', '2:17:03', 2, 'Comedy, Drama, Mystery '),
		 ('259', 'Jungle Jim', '1946-06-06', '2:17:03', 3, 'Comedy, Drama, Mystery '),
		 ('269', 'Knock on Any Door', '1946-06-06', '2:17:03', 4, 'Comedy, Drama, Mystery ');	   

INSERT INTO uloga (FilmID, GlumacID, TipUlogeID, ImeLika, OpisLika) 
	VALUES ('3', '5', '1', 'Christopher', 'Magia Boss'),
	('4', '4', '1', 'Aragorn', 'Dunadain Ranger'),
	('4', '111', '2', 'Hurin', NULL),
	('5', '9', '1', 'Gladius', NULL),
	('6', '111', '1', 'Zeus', NULL);

INSERT INTO Producirao(FilmID, ProducentID)
VALUES (3,1),(4,2),(5,3),(6,4),(7,5),(60,6),(61,6),(62,6),(68,6),(69,6),(71,7), (72,7), (73,7), (74,7), (75,7), (76,7), (77,7), 
		(80,8),(81,8),(82,8),(90,9),(91,9),(92,9),(93,9),(94,9),(95,9),(96,9),(97,9),(98,9),(99,9),(900,9),(906,9),(916,9),
		(170,17),(171,17),(172,17),(173,17),(174,17),(175,17),(176,17),(177,17),
		(18,18),(180,18),(181,18),(182,18),
		(19,19),(190,19),(191,19),(192,19),(193,19),(194,19),(195,19),
		(20,20),(200,20),(201,20),(202,20),(203,20),(204,20),(205,20),(206,20),(207,20),(208,20),(209,20),
		(210,20),(219,20),(229,20),(239,20),(249,20),(259,20),(269,20);