CREATE DATABASE [DVD kolekcija]
GO
USE [DVD kolekcija]
GO

CREATE TABLE Glumac (
GlumacID INT PRIMARY KEY NOT NULL,
Ime NVARCHAR(30),
Prezime NVARCHAR(30),
DatumRodjenja DATE,
MestoRodjenja NVARCHAR(30)
);

CREATE TABLE Zanr (
ZanrID INT PRIMARY KEY NOT NULL,
NazivZanra NVARCHAR(20)
);

CREATE TABLE Film (
FilmID INT PRIMARY KEY NOT NULL,
Naziv NVARCHAR(30),
DatumIzlaska DATE,
Trajanje INT,
OpisRadnje TEXT,
ZanrID INT FOREIGN KEY REFERENCES Zanr(ZanrID)
);

CREATE TABLE Tip_uloge (
TipUlogeID INT PRIMARY KEY NOT NULL,
Tip NVARCHAR(20)
);

CREATE TABLE Uloga (
ImeLika NVARCHAR(30),
OpisLika TEXT,
FilmID INT FOREIGN KEY REFERENCES Film(FilmID),
GlumacID INT FOREIGN KEY REFERENCES Glumac(GlumacID),
TipUlogeID INT FOREIGN KEY REFERENCES Tip_uloge(TipUlogeID)
);

CREATE TABLE Nagrada (
NagradaID INT PRIMARY KEY NOT NULL,
Naziv NVARCHAR(30),
GodinaPocetka INT
);

CREATE TABLE Film_Nagrada (
GodinaDodele INT,
FilmID INT FOREIGN KEY REFERENCES Film(FilmID),
NagradaID INT FOREIGN KEY REFERENCES Nagrada(NagradaID)
);

CREATE TABLE Producent (
ProducentID INT PRIMARY KEY NOT NULL,
Ime NVARCHAR(50),
Email NVARCHAR(50),
VebSajt NVARCHAR(50)
);

CREATE TABLE Producirao (
FilmID INT FOREIGN KEY REFERENCES Film(FilmID),
ProducentID INT FOREIGN KEY REFERENCES Producent(ProducentID)
);

insert into Producent (ProducentID, Ime, Email) values (1, 'Universal Studios', 'universalstudios@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (2, 'Paramount Pictures Corporation', 'paramount@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (3, 'Warner Bros. Entertainment', 'warnerbros@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (4, 'Walt Disney Studios', 'waltdisneystudios@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (5, 'Sony Pictures', 'sony@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (6, 'BBC Films', 'bbcfilms@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (7, 'Vertigo Films', 'vertigofilms@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (8, 'Syncopy Inc.', 'syncopy@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (9, 'Warp Films', 'warpfilms@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (10, 'Apple Studios', 'apple@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (11, 'Blue Sky Studios', 'bluesky@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (12, 'DreamWorks Pictures', 'dreamworks@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (13, 'Netflix, Inc.', 'netflix@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (14, 'TriStar Pictures', 'tristarpictures@gmail.com');
insert into Producent (ProducentID, Ime, Email) values (15, '20th Century Studios', 'centurystudios@gmail.com');

insert into Film (FilmID) values (1);
insert into Film (FilmID) values (2);
insert into Film (FilmID) values (3);
insert into Film (FilmID) values (4);
insert into Film (FilmID) values (5);
insert into Film (FilmID) values (6);
insert into Film (FilmID) values (7);
insert into Film (FilmID) values (8);
insert into Film (FilmID) values (9);
insert into Film (FilmID) values (10);
insert into Film (FilmID) values (11);
insert into Film (FilmID) values (12);
insert into Film (FilmID) values (13);
insert into Film (FilmID) values (14);
insert into Film (FilmID) values (15);
insert into Film (FilmID) values (16);
insert into Film (FilmID) values (17);
insert into Film (FilmID) values (18);
insert into Film (FilmID) values (19);
insert into Film (FilmID) values (20);
insert into Film (FilmID) values (21);
insert into Film (FilmID) values (22);
insert into Film (FilmID) values (23);
insert into Film (FilmID) values (24);
insert into Film (FilmID) values (25);
insert into Film (FilmID) values (26);
insert into Film (FilmID) values (27);
insert into Film (FilmID) values (28);
insert into Film (FilmID) values (29);
insert into Film (FilmID) values (30);
insert into Film (FilmID) values (31);
insert into Film (FilmID) values (32);
insert into Film (FilmID) values (33);
insert into Film (FilmID) values (34);
insert into Film (FilmID) values (35);
insert into Film (FilmID) values (36);
insert into Film (FilmID) values (37);
insert into Film (FilmID) values (38);
insert into Film (FilmID) values (39);
insert into Film (FilmID) values (40);
insert into Film (FilmID) values (41);
insert into Film (FilmID) values (42);
insert into Film (FilmID) values (43);
insert into Film (FilmID) values (44);
insert into Film (FilmID) values (45);
insert into Film (FilmID) values (46);
insert into Film (FilmID) values (47);
insert into Film (FilmID) values (48);
insert into Film (FilmID) values (49);
insert into Film (FilmID) values (50);
insert into Film (FilmID) values (51);
insert into Film (FilmID) values (52);
insert into Film (FilmID) values (53);
insert into Film (FilmID) values (54);
insert into Film (FilmID) values (55);
insert into Film (FilmID) values (56);
insert into Film (FilmID) values (57);
insert into Film (FilmID) values (58);
insert into Film (FilmID) values (59);
insert into Film (FilmID) values (60);
insert into Film (FilmID) values (61);
insert into Film (FilmID) values (62);
insert into Film (FilmID) values (63);
insert into Film (FilmID) values (64);
insert into Film (FilmID) values (65);
insert into Film (FilmID) values (66);
insert into Film (FilmID) values (67);
insert into Film (FilmID) values (68);
insert into Film (FilmID) values (69);
insert into Film (FilmID) values (70);
insert into Film (FilmID) values (71);
insert into Film (FilmID) values (72);
insert into Film (FilmID) values (73);
insert into Film (FilmID) values (74);
insert into Film (FilmID) values (75);
insert into Film (FilmID) values (76);
insert into Film (FilmID) values (77);
insert into Film (FilmID) values (78);
insert into Film (FilmID) values (79);
insert into Film (FilmID) values (80);
insert into Film (FilmID) values (81);
insert into Film (FilmID) values (82);
insert into Film (FilmID) values (83);
insert into Film (FilmID) values (84);
insert into Film (FilmID) values (85);
insert into Film (FilmID) values (86);
insert into Film (FilmID) values (87);
insert into Film (FilmID) values (88);
insert into Film (FilmID) values (89);
insert into Film (FilmID) values (90);
insert into Film (FilmID) values (91);
insert into Film (FilmID) values (92);
insert into Film (FilmID) values (93);
insert into Film (FilmID) values (94);
insert into Film (FilmID) values (95);
insert into Film (FilmID) values (96);
insert into Film (FilmID) values (97);
insert into Film (FilmID) values (98);
insert into Film (FilmID) values (99);
insert into Film (FilmID) values (100);

insert into Producirao values (1,6);
insert into Producirao values (2,1);
insert into Producirao values (3,12);
insert into Producirao values (4,4);
insert into Producirao values (5,13);
insert into Producirao values (6,10);
insert into Producirao values (7,11);
insert into Producirao values (8,11);
insert into Producirao values (9,5);
insert into Producirao values (10,12);
insert into Producirao values (11,15);
insert into Producirao values (12,7);
insert into Producirao values (13,6);
insert into Producirao values (14,15);
insert into Producirao values (15,1);
insert into Producirao values (16,3);
insert into Producirao values (17,3);
insert into Producirao values (18,12);
insert into Producirao values (19,15);
insert into Producirao values (20,15);
insert into Producirao values (21,5);
insert into Producirao values (22,5);
insert into Producirao values (23,3);
insert into Producirao values (24,4);
insert into Producirao values (25,4);
insert into Producirao values (26,14);
insert into Producirao values (27,14);
insert into Producirao values (28,15);
insert into Producirao values (29,6);
insert into Producirao values (30,14);
insert into Producirao values (31,13);
insert into Producirao values (32,8);
insert into Producirao values (33,15);
insert into Producirao values (34,6);
insert into Producirao values (35,9);
insert into Producirao values (36,10);
insert into Producirao values (37,3);
insert into Producirao values (38,10);
insert into Producirao values (39,6);
insert into Producirao values (40,2);
insert into Producirao values (41,6);
insert into Producirao values (42,6);
insert into Producirao values (43,9);
insert into Producirao values (44,5);
insert into Producirao values (45,8);
insert into Producirao values (46,7);
insert into Producirao values (47,1);
insert into Producirao values (48,10);
insert into Producirao values (49,13);
insert into Producirao values (50,6);
insert into Producirao values (51,3);
insert into Producirao values (52,13);
insert into Producirao values (53,1);
insert into Producirao values (54,5);
insert into Producirao values (55,14);
insert into Producirao values (56,12);
insert into Producirao values (57,5);
insert into Producirao values (58,13);
insert into Producirao values (59,10);
insert into Producirao values (60,3);
insert into Producirao values (61,12);
insert into Producirao values (62,14);
insert into Producirao values (63,1);
insert into Producirao values (64,9);
insert into Producirao values (65,15);
insert into Producirao values (66,10);
insert into Producirao values (67,13);
insert into Producirao values (68,5);
insert into Producirao values (69,15);
insert into Producirao values (70,14);
insert into Producirao values (71,13);
insert into Producirao values (72,11);
insert into Producirao values (73,3);
insert into Producirao values (74,3);
insert into Producirao values (75,6);
insert into Producirao values (76,4);
insert into Producirao values (77,6);
insert into Producirao values (78,6);
insert into Producirao values (79,4);
insert into Producirao values (80,10);
insert into Producirao values (81,9);
insert into Producirao values (82,10);
insert into Producirao values (83,4);
insert into Producirao values (84,15);
insert into Producirao values (85,11);
insert into Producirao values (86,1);
insert into Producirao values (87,3);
insert into Producirao values (88,5);
insert into Producirao values (89,13);
insert into Producirao values (90,1);
insert into Producirao values (91,11);
insert into Producirao values (92,4);
insert into Producirao values (93,11);
insert into Producirao values (94,10);
insert into Producirao values (95,15);
insert into Producirao values (96,8);
insert into Producirao values (97,10);
insert into Producirao values (98,8);
insert into Producirao values (99,11);
insert into Producirao values (100,6);

    
        
        
    
    
        
CREATE DATABASE [Fudbalski stadioni]
GO
USE [Fudbalski stadioni]
GO

CREATE TABLE Drzava (
DrzavaID INT PRIMARY KEY NOT NULL,
Naziv NVARCHAR(30)
);

CREATE TABLE Grad (
GradID INT PRIMARY KEY NOT NULL,
Grad NVARCHAR(30),
PozivniBroj NVARCHAR(10),
PostanskiBroj NVARCHAR(10),
BrojStanovnika INT,
DrzavaID INT FOREIGN KEY REFERENCES Drzava(DrzavaID)
);

CREATE TABLE Stadion (
StadionID INT PRIMARY KEY NOT NULL,
Naziv NVARCHAR(50),
Adresa NVARCHAR(50),
Kapacitet INT,
BrojUlaza INT,
GradID INT FOREIGN KEY REFERENCES Grad(GradID)
);

CREATE TABLE Klub (
KlubID INT PRIMARY KEY NOT NULL,
NazivKluba NVARCHAR(30),
Email NVARCHAR(50),
Sajt NVARCHAR(50),
ZiroRacun NVARCHAR(30),
Amblem NVARCHAR(30),
StadionID INT FOREIGN KEY REFERENCES Stadion(StadionID)
);

CREATE TABLE Takmicenje (
TakmicenjeID INT PRIMARY KEY NOT NULL,
Naziv NVARCHAR(30)
);

CREATE TABLE Utakmica (
UtakmicaID INT PRIMARY KEY NOT NULL,
DatumIgranja DATE,
VremeIgranja TIME,
GolovaDomacin INT,
GolovaGost INT,
TakmicenjeID INT FOREIGN KEY REFERENCES Takmicenje(TakmicenjeID),
DomacinID INT FOREIGN KEY REFERENCES Klub(KlubID),
GostID INT FOREIGN KEY REFERENCES Klub(KlubID)
);

insert into Drzava (DrzavaID, Naziv) values (1, 'Engleska');
insert into Drzava (DrzavaID, Naziv) values (2, 'Italija');
insert into Drzava (DrzavaID, Naziv) values (3, 'Nemacka');
insert into Drzava (DrzavaID, Naziv) values (4, 'Spanija');
insert into Drzava (DrzavaID, Naziv) values (5, 'Francuska');

insert into Grad (GradID, Grad, DrzavaID) values (1, 'London', 1);
insert into Grad (GradID, Grad, DrzavaID) values (2, 'Mancester', 1);
insert into Grad (GradID, Grad, DrzavaID) values (3, 'Liverpul', 1);
insert into Grad (GradID, Grad, DrzavaID) values (4, 'Birmingem', 1);
insert into Grad (GradID, Grad, DrzavaID) values (5, 'Lester', 1);
insert into Grad (GradID, Grad, DrzavaID) values (6, 'Milano', 2);
insert into Grad (GradID, Grad, DrzavaID) values (7, 'Rim', 2);
insert into Grad (GradID, Grad, DrzavaID) values (8, 'Torino', 2);
insert into Grad (GradID, Grad, DrzavaID) values (9, 'Napulj', 2);
insert into Grad (GradID, Grad, DrzavaID) values (10, 'Dortmund', 3);
insert into Grad (GradID, Grad, DrzavaID) values (11, 'Minhen', 3);
insert into Grad (GradID, Grad, DrzavaID) values (12, 'Frankfurt', 3);
insert into Grad (GradID, Grad, DrzavaID) values (13, 'Berlin', 3);
insert into Grad (GradID, Grad, DrzavaID) values (14, 'Madrid', 4);
insert into Grad (GradID, Grad, DrzavaID) values (15, 'Barselona', 4);
insert into Grad (GradID, Grad, DrzavaID) values (16, 'Sevilja', 4);
insert into Grad (GradID, Grad, DrzavaID) values (17, 'Bilbao', 4);
insert into Grad (GradID, Grad, DrzavaID) values (18, 'Pariz', 5);
insert into Grad (GradID, Grad, DrzavaID) values (19, 'Marsej', 5);
insert into Grad (GradID, Grad, DrzavaID) values (20, 'Lion', 5);
insert into Grad (GradID, Grad, DrzavaID) values (21, 'Monako', 5);

insert into Stadion values (1, 'Stamford Bridge', 'Fulham Rd.', 40341, 8, 1);
insert into Stadion values (2, 'Old Trafford', 'Sir Matt Busby Way', 74310, 14, 2);
insert into Stadion values (3, 'Anfield', 'Anfield Rd', 61276, 12, 3);
insert into Stadion values (4, 'Villa Park', 'Trinity Road', 42640, 10, 4);
insert into Stadion values (5, 'King Power Stadium', 'Filbert Wy', 32261, 8, 5);
insert into Stadion values (6, 'San Siro', 'Piazzale Angelo Moratti', 75817, 14, 6);
insert into Stadion values (7, 'Stadio Olimpico', 'Viale dello Stadio Olimpico', 72698, 12, 7);
insert into Stadion values (8, 'Allianz Stadium', 'Via Giambattista Marino', 41507, 8, 8);
insert into Stadion values (9, 'Stadio Diego Armando Maradona', 'Corso Gaetano Scirea', 54726, 10, 9);
insert into Stadion values (10, 'Signal Iduna Park', 'Strobelallee 50', 81365, 18, 10);
insert into Stadion values (11, 'Allianz Arena', 'Werner-Heisenberg-Allee 25', 75024, 14, 11);
insert into Stadion values (12, 'Deutsche Bank Park', 'Mörfelder Landstraße 362', 58000, 10, 12);
insert into Stadion values (13, 'Olympiastadion Berlin', 'Olympischer Platz 3', 74475, 14, 13);
insert into Stadion values (14, 'Santiago Bernabéu Stadium', 'Av. de Concha Espina', 85000, 18, 14);
insert into Stadion values (15, 'Spotify Camp Nou', 'Les Corts', 99354, 22, 15);
insert into Stadion values (16, 'Ramon Sanchez-Pizjuan Stadium', 'C. Sevilla Fútbol Club', 43883, 8, 16);
insert into Stadion values (17, 'Estadio de San Mamés', 'P.º Rafael Moreno "Pitxitxi"', 53289, 10, 17);
insert into Stadion values (18, 'Parc des Princes', '24 Rue du Commandant Guilbaud', 48583, 10, 18);
insert into Stadion values (19, 'Orange Vélodrome', '3 Bd Michelet', 67000, 12, 19);
insert into Stadion values (20, 'Groupama Stadium', '10 Av. Simone Veil', 59186, 14, 20);
insert into Stadion values (21, 'Stade Louis II', '7 Av. des Castelans', 18523, 6, 21);

insert into Klub (KlubID, StadionID) values (1, 1);
insert into Klub (KlubID, StadionID) values (2, 2);
insert into Klub (KlubID, StadionID) values (3, 3);
insert into Klub (KlubID, StadionID) values (4, 4);
insert into Klub (KlubID, StadionID) values (5, 5);
insert into Klub (KlubID, StadionID) values (6, 6);
insert into Klub (KlubID, StadionID) values (7, 7);
insert into Klub (KlubID, StadionID) values (8, 8);
insert into Klub (KlubID, StadionID) values (9, 9);
insert into Klub (KlubID, StadionID) values (10, 10);
insert into Klub (KlubID, StadionID) values (11, 11);
insert into Klub (KlubID, StadionID) values (12, 12);
insert into Klub (KlubID, StadionID) values (13, 13);
insert into Klub (KlubID, StadionID) values (14, 14);
insert into Klub (KlubID, StadionID) values (15, 15);
insert into Klub (KlubID, StadionID) values (16, 16);
insert into Klub (KlubID, StadionID) values (17, 17);
insert into Klub (KlubID, StadionID) values (18, 18);
insert into Klub (KlubID, StadionID) values (19, 19);
insert into Klub (KlubID, StadionID) values (20, 20);
insert into Klub (KlubID, StadionID) values (21, 21);

insert into Takmicenje (TakmicenjeID) values (1);
insert into Takmicenje (TakmicenjeID) values (2);
insert into Takmicenje (TakmicenjeID) values (3);
insert into Takmicenje (TakmicenjeID) values (4);
insert into Takmicenje (TakmicenjeID) values (5);

insert into Utakmica (UtakmicaID, DomacinID) values (1, 1);
insert into Utakmica (UtakmicaID, DomacinID) values (2, 1);
insert into Utakmica (UtakmicaID, DomacinID) values (3, 1);
insert into Utakmica (UtakmicaID, DomacinID) values (4, 1);
insert into Utakmica (UtakmicaID, DomacinID) values (5, 1);
insert into Utakmica (UtakmicaID, DomacinID) values (6, 1);
insert into Utakmica (UtakmicaID, DomacinID) values (7, 1);
insert into Utakmica (UtakmicaID, DomacinID) values (8, 1);
insert into Utakmica (UtakmicaID, DomacinID) values (9, 1);
insert into Utakmica (UtakmicaID, DomacinID) values (10, 1);
insert into Utakmica (UtakmicaID, DomacinID) values (11, 2);
insert into Utakmica (UtakmicaID, DomacinID) values (12, 2);
insert into Utakmica (UtakmicaID, DomacinID) values (13, 2);
insert into Utakmica (UtakmicaID, DomacinID) values (14, 2);
insert into Utakmica (UtakmicaID, DomacinID) values (15, 2);
insert into Utakmica (UtakmicaID, DomacinID) values (16, 2);
insert into Utakmica (UtakmicaID, DomacinID) values (17, 5);
insert into Utakmica (UtakmicaID, DomacinID) values (18, 5);
insert into Utakmica (UtakmicaID, DomacinID) values (19, 6);
insert into Utakmica (UtakmicaID, DomacinID) values (20, 6);
insert into Utakmica (UtakmicaID, DomacinID) values (21, 6);
insert into Utakmica (UtakmicaID, DomacinID) values (22, 6);
insert into Utakmica (UtakmicaID, DomacinID) values (23, 6);
insert into Utakmica (UtakmicaID, DomacinID) values (24, 6);
insert into Utakmica (UtakmicaID, DomacinID) values (25, 6);
insert into Utakmica (UtakmicaID, DomacinID) values (26, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (27, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (28, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (29, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (30, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (31, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (32, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (33, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (34, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (35, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (36, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (37, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (38, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (39, 9);
insert into Utakmica (UtakmicaID, DomacinID) values (40, 10);
insert into Utakmica (UtakmicaID, DomacinID) values (41, 10);
insert into Utakmica (UtakmicaID, DomacinID) values (42, 10);
insert into Utakmica (UtakmicaID, DomacinID) values (43, 10);
insert into Utakmica (UtakmicaID, DomacinID) values (44, 10);
insert into Utakmica (UtakmicaID, DomacinID) values (45, 16);
insert into Utakmica (UtakmicaID, DomacinID) values (46, 16);
insert into Utakmica (UtakmicaID, DomacinID) values (47, 18);
insert into Utakmica (UtakmicaID, DomacinID) values (48, 18);
insert into Utakmica (UtakmicaID, DomacinID) values (49, 18);
insert into Utakmica (UtakmicaID, DomacinID) values (50, 18);
insert into Utakmica (UtakmicaID, DomacinID) values (51, 18);
insert into Utakmica (UtakmicaID, DomacinID) values (52, 18);
insert into Utakmica (UtakmicaID, DomacinID) values (53, 18);
insert into Utakmica (UtakmicaID, DomacinID) values (54, 18);
insert into Utakmica (UtakmicaID, DomacinID) values (55, 18);
insert into Utakmica (UtakmicaID, DomacinID) values (56, 18);
insert into Utakmica (UtakmicaID, DomacinID) values (57, 21);
insert into Utakmica (UtakmicaID, DomacinID) values (58, 21);
insert into Utakmica (UtakmicaID, DomacinID) values (59, 21);
insert into Utakmica (UtakmicaID, DomacinID) values (60, 21);