CREATE DATABASE B3_DVD_Kolekcija3
COLLATE Serbian_Latin_100_CI_AI;

GO

USE B3_DVD_Kolekcija3;
GO

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

INSERT INTO Glumac (GlumacID, Ime, Prezime, DatumRodjenja, MestoRodjenja)
VALUES 
(1, 'Jovan', 'Jovanovic', '1956-02-11', 'U.S.'),
(2, 'Natalie', 'Dormer', '1976-05-22', 'U.K.'),
(3, 'Robert', 'De Niro', '1966-03-12', 'New York'),
(4, 'Tom', 'Hanks', '1971-07-21', 'Francuska'),
(5, 'Velimir', 'Zivojinovic', '1933-06-05', 'Kraljevina Jugoslavija'),
(6, 'Ben', 'Aflek', '1972-08-15', 'Kalifornija'),
(7, 'Omar', 'Sharif', '1932-04-10', 'Aleksandrija'),
(8, 'Sylvester', 'Stalone', '0946-07-06', 'New York'),
(9, 'Helen', 'Hunt', '1963-06-15', 'Kalifornija'),
(10, 'Nicolas', 'Cage', '1964-01-07', 'Kalifornija'),
(11, 'Kevin', 'Costner', '1955-01-18', 'Lynwood'),
(12, 'Jovan', 'Jovanovic', '1936-12-02', 'Leskovac'),
(13, 'John', 'Miller', '1984-02-08', 'New York'),
(14, 'Sarah', 'Jhonson', '1988-04-20', 'London, U.K.'),
(15, 'Vladimir', 'Vladimirovich', '1966-10-12', 'Soviet Union'),
(16, 'Jhonny', 'Deep', '1963-06-09', 'U.S.'),
(17, 'Viggo', 'Mortensen', '1958-10-20', 'New York'),
(18, 'John', 'Goodman', '1952-06-20', 'Missouri, USA'),
(19, 'Mattew', 'Perry', '1974-01-24', 'Cleveland'),
(20, 'Sean', 'Penn', '1948-04-23', 'Cleveland'),
(21, 'John', 'Travolta', '1965-04-02', 'Montgomery'),
(22, 'Eric', 'Bana', '1956-09-24', NULL),
(23, 'Ingrid', 'Bergman', '1938-01-31', 'Miami'),
(24, 'Daniel', 'Day-Lewis', '1974-07-07', 'Fort Worth'),
(25, 'Arnold', 'Schwarzenegger', '1969-07-18', NULL);
GO

CREATE TABLE Producent (
  ProducentID INT PRIMARY KEY,
  Ime VARCHAR(50),
  Email VARCHAR(50)
);
GO

INSERT INTO Producent (ProducentID, Ime, Email)
VALUES 
(1, 'Centar Film', 'cxsc@dcasl.com'),
(2, 'Košutnjak film', 'dcsc@dcdsl.com'),
(3, 'Dinovizija - Video production', 'sxasx@films.com'),
(4, 'Four Star Television', 'xfsojp18@ovideos.uk'),
(5, 'Universal Pictures', 'csdA@yahoo.com'),
(6, 'DreamWorks', 'fdahc@nkjcjn.com'),
(7, 'Iervolino Entertainment', 'CKD@nsdjcb.cn'),
(8, 'BBC Films', NULL),
(9, '20th Century Fox', 'ndjsvn@ldmscokds.net'),
(10, 'Sony Pictures', 'csjd@shjb.nj'),
(17, 'American International Pictures', 'vsdvn@nkn.org'),
(18, 'Capitol Films', 'fdvsnlv@csdk.net'),
(19, 'Cinemation Industries', 'pnvcsdhj@kjsdnci.com'),
(20, 'Columbia TriStar Motion Picture Group', 'dscawsw@nncdslk.com');
GO


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

INSERT INTO Zanr (ZanrID, NazivZanra)
VALUES 
(1,'Dramski film'),
(2,'Kriminal'),
(3,'Avantura'),
(4,'Akcija'),
(5,'Komedija'),
(6,'Fantazija'),
(7, 'Triler'),
(8, 'Istorijski film');
GO


CREATE TABLE Film (
  FilmID INT PRIMARY KEY,
  Naziv VARCHAR(25),
  DatumIzlaska DATE,
  Trajanje INT,
  ZanrID INT,
  OpisRadnje VARCHAR(146),
  CONSTRAINT FK_Zanr FOREIGN KEY (ZanrID) REFERENCES Zanr(ZanrID)
);
GO


INSERT INTO Film (FilmID, Naziv, DatumIzlaska, Trajanje, ZanrID, OpisRadnje)
VALUES 
(1, 'Avijaticar', '2004-01-01', 169, 1, 'Film o Hauardu Hjuzu'),
(2, 'Čarlijevi anđeli', '2000-10-10', 200, 4, 'lepotice špijuni'),
(3,'Kum','1972-04-15',153,1,'Ikona u svetu folma o jednom Mafia Boss-u'),
(4,'Gospodar Prstenova','2006-12-17',182,6,'Gandalf i Aragorn sa drugima a protiv Sauronove vojske ....'),
(5,'Valter bran Sarajevo','1972-11-24',142,2,'Partizanski film'),
(6,'Clash of Titans','2012-04-14',65,4,null),
(7,'Pirates of the Carebbean','2012-08-11',137,3,null),
(8, 'Elizabeth', '1998-12-12', 123, 8, 'o jednoj kraljici'),
(9, 'Hrabro srce', '1995-02-20', 185, 7, NULL),
(10, 'Partizanska eskadrila', '1979-07-16', '124', '4', NULL),
(11, 'Moderna vremena', '1936-02-02', '87', '1', NULL),
(12, 'Casablanca', '1942-12-02', '104', '1', NULL),
(13, 'Titanic', '1997-12-02', '210', '1', NULL),
(14, 'Diplomac', '1967-08-21', '201', '1', NULL);
GO

CREATE TABLE Producirao (
  FilmID INT,
  ProducentID INT,
  CONSTRAINT PK_Producirao PRIMARY KEY (FilmID, ProducentID),
  CONSTRAINT FK_PRODUCENT FOREIGN KEY (ProducentID) REFERENCES Producent(ProducentID),
  CONSTRAINT FK_Film FOREIGN KEY (FilmID) REFERENCES Film(FilmID)
);
GO
INSERT INTO producirao (FilmID, ProducentID) VALUES
(1, 18),
(2, 9),
(2, 18),
(2, 20),
(3, 6),
(3, 9),
(4, 5),
(4, 18),
(5, 1),
(5, 2),
(5, 7),
(6, 17),
(6, 18),
(7, 5),
(7, 10),
(7, 20),
(8, 8),
(8, 18),
(9, 4),
(9, 8),
(10, 1),
(10, 2),
(10, 3),
(11, 5),
(12, 9),
(13, 5),
(13, 6),
(13, 10),
(14, 9);

GO

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

GO

INSERT INTO Tip_Uloge (TipUlogeID, Tip)
VALUES 
(1, 'Glavna uloga'),
(2, 'Sporedna uloga'),
(3, 'Epizodna uloga'),
(4, 'Guest Star');
GO

CREATE TABLE Uloga (
  FilmID INT,
  GlumacID INT,
  TipUlogeID INT,
  ImeLika VARCHAR(25),
  OpisLika VARCHAR(250),
  CONSTRAINT PK_Uloga PRIMARY KEY (FilmID, GlumacID, ImeLika),
  CONSTRAINT FK_FilmUloga FOREIGN KEY (FilmID) REFERENCES Film(FilmID),
  CONSTRAINT FK_Glumac FOREIGN KEY (GlumacID) REFERENCES Glumac(GlumacID),
  CONSTRAINT FK_TipUloge FOREIGN KEY (TipUlogeID) REFERENCES Tip_Uloge(TipUlogeID)
) ;

GO

INSERT INTO Uloga (FilmID, GlumacID, TipUlogeID, ImeLika, OpisLika)
VALUES 
(1, 6, 4, 'Some Gay', NULL),
(1, 18, 4, 'John Star', NULL),
(2, 7, 4, 'seik', NULL),
(2, 8, 4, 'Some Gay', NULL),
(3, 1, 1, 'Christopher', 'Magia Boss'),
(3, 24, 1, 'Sima', NULL),
(4, 13, 2, 'Hurin', ''),
(4, 17, 1, 'Aragorn', 'Dunadain Ranger'),
(5, 5, 1, 'Gladius', ''),
(5, 15, 2, 'nemac', NULL),
(6, 13, 1, 'Zeus', ''),
(6, 19, 3, 'Sone', NULL),
(7, 16, 1, 'Kapetan', NULL),
(7, 23, 3, 'dancer', NULL),
(8, 9, 4, 'maid', NULL),
(8, 23, 4, 'Princess', NULL),
(9, 10, 4, 'Guest', NULL),
(9, 11, 4, 'Smith', NULL),
(10, 5, 1, 'pilot', NULL),
(10, 17, 2, 'komesar', NULL),
(11, 3, 1, 'Brad Schulz', NULL),
(11, 7, 2, 'negativac', NULL),
(11, 23, 2, 'mama', NULL),
(12, 3, 3, 'Raquel Murillo', NULL),
(12, 13, 2, 'dancer', NULL),
(12, 18, 2, 'gay 2', NULL),
(12, 21, 2, 'gay', NULL),
(13, 3, 3, 'Megan Mathis', NULL),
(13, 9, 1, 'maid', NULL),
(13, 19, 1, 'young gay', NULL),
(13, 20, 4, 'waiter', NULL),
(14, 3, 2, 'Marisol Rangel', NULL),
(14, 4, 2, 'rider', NULL),
(14, 12, 2, 'konobar', NULL);
GO