create database DVD;

use DVD;

create table movie(
	id int auto_increment primary key,
	movie_name varchar(100) unique not null,
	release_year int
)engine=innodb;

insert into movie(movie_name,release_year) values 	('The King\'s Speech',2010),
							('The Hurt Locker',2009),
							('Slumdog Millionaire',2008);


create table participant_role(
	id int auto_increment primary key,
	role varchar(20) unique not null
)engine=innodb;


insert into participant_role(role)	values	('Director'),
						('Actor'),
						('Actress');


create table movie_participant(
	id int auto_increment primary key,
	role_id int,
	participant_name varchar(100),
	movie_id int,
	constraint movie_participant_fk1 foreign key (role_id) references participant_role(id),
	constraint movie_participant_fk2 foreign key (movie_id) references movie(id)
)engine=innodb;


insert into movie_participant(role_id,movie_id,participant_name) values (1,1,'Tom Hooper'),
									(1,2,'Colin Firth'),
									(1,3,'Helena Bonham Carter'),
									(2,1,'Kathryn Bigelow'),
									(2,2,'Jeremy Renner'),
									(2,3,'Sandra Bullock'),
									(3,1,'Danny Boyle'),
									(3,2,'Dev Patel'),
									(3,3,'Freida Pinto');



create table dvd (
	id int auto_increment primary key,
	dvd_no int unique not null,
	movie_id int,
	availability_status int,
	constraint dvd_fk1 foreign key(movie_id) references movie(id)
)engine=innodb;


insert into dvd(dvd_no,movie_id,availability_status) values	(101,1,1),
								(102,1,1),
								(103,1,1),
								(104,1,1),
								(105,1,1),
								(201,2,1),
								(202,2,1),
								(203,2,1),
								(204,2,1),
								(205,2,1),
								(301,3,1),
								(302,3,1),
								(303,3,1),
								(304,3,1),
								(305,3,1);


create table employee (
	id int auto_increment primary key,
	employee_no int unique not null,
	employee_name varchar(100),
	address text
)engine=innodb;


insert into employee(employee_no, employee_name, address ) values 	(1,'Albert','Cochin'),
									(2,'Choudhari','Calcutta'),
									(3,'Chidambaram','Chennai');


create table customer(
	id int auto_increment primary key,
	customer_no int unique not null,
	customer_name varchar(100),
	address text
)engine=innodb;


insert into customer(customer_no, customer_name, address) values 	(1,'George','Calicut'),
									(2,'Priya','Trichur'),
									(3,'Aniya','Kannur');



create table rent(
	id int auto_increment primary key,
	customer_id int,
	employee_id int,
	rent_date date,
	due_date date,
	dvd_id int ,
	constraint rent_fk1 foreign key(customer_id) references customer(id),
	constraint rent_fk2 foreign key(employee_id) references employee(id),
	constraint rent_fk3 foreign key(dvd_id) references dvd(id)
)engine=innodb;


create table return_dvd(
	id int auto_increment primary key,
	rent_id int,
	employee_id int ,
	return_date date,
	constraint return_dvd_fk1 foreign key(employee_id) references employee(id),
	constraint return_dvd_fk2 foreign key(rent_id) references rent(id)
)engine=innodb;

http://www.gladsys.in/blog/stored-procedures-in-mysql/

Schema for the database DVD

database

SQL