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;