h3TRmZdU

1drop database if exists movies;
2create database movies;
3USE movies;
4
5
6create table studios
7(
8id int auto_increment primary key,
9namestudio varchar(250) not null,
10bulstrat varchar(250) not null,
11address varchar(250) not null
12
13);
14
15create table producers
16(
17id int auto_increment primary key,
18name varchar(250) not null,
19address varchar(250) not null,
20bulstrat varchar(250) not null
21);
22
23create table movie
24(
25
26id int auto_increment primary key,
27title varchar(250) not null,
28
29filmyear year not null,
30duration datetime default null, 
31
32budget int not null,
33producer_id int not null ,
34constraint foreign key (producer_id) references producers(id),
35studio_id int not null,
36constraint foreign key (studio_id) references studios(id) 
37
38);
39
40create table actors
41(
42
43id int auto_increment primary key,
44birthday date,
45actorname varchar(200) not null,
46gender enum ('male','female'),
47address varchar(200) not null
48
49);
50
51
52create table movieactors
53(
54  movie_id int not null,
55  actor_id int not null,
56  CONSTRAINT FOREIGN KEY(movie_id) REFERENCES movie(id) ,
57  CONSTRAINT FOREIGN KEY(actor_id) REFERENCES actors(id), 
58	PRIMARY KEY(movie_id, actor_id)
59);
60
61insert into actors
62values (null,'1978-08-19','Brad Pitt','male','Malibu beach'),
63        (null,'1979-08-12','Angeline Jolie','female','Malibu beach'),
64        (null,'1984-05-13','Jenifer Aninston','female','Sofia'),
65         (null,'2000-11-07','Kit Harrington','male','Malibu beach'),
66         (null,'1989-09-11','Dwayne Jonson','male','Malibu beach');
67         
68Insert into studios(address,namestudio,bulstrat)
69VALUES ('Sofia, Boris str. 56', 'Studio Diana', '1112221'),
70('London, Everest str. 355', 'Studio Express', '1457458'),
71('Berlin, Kernigan str. 455', 'IFS-200', '1114452');
72
73insert into producers(address,bulstrat,name)
74VALUES('London, Safary str. 477', '12124545', 'John Smith'),
75('Sofia, Opalchenska str. 78', '12541250','Erna & Dina EOOD'),
76('Paris, Earoh str. 626', '14514520','Exctravaganzza Trading');
77
78insert into movie (title,filmyear,budget,duration,producer_id,studio_id)
79values ('The fate of Furious',1997,50000,'2:14:22',2,1),
80	('GI JOE',1994,25000,'03:18:22',1,2),
81    ('Pain and Gain',2013,25000,'2:43:22',2,2),
82    ('American pie',1999,30000,'"1:46:28',3,3);
83
84   
85INSERT INTO movieactors(movie_id,actor_id)
86VALUES(1,1),
87(2,2),
88(1,2),
89(3,3);