top of page

Database schema

Public·2 members
Back
Naveen Kumar

Movie Management System Database Schema

In this blog, we will learn about the movie management system and how to create the database schema and how to perform queries on these schemas -


First creating the database :


DROP DATABASE mt_db;
CREATE DATABASE mt_db;
USE mt_db;


Now we will be creating database tables


Creating Customer_master table

Create table CUSTOMER_MASTER
(
	CUSTOMER_ID Varchar(10),
	CUSTOMER_NAME Varchar(30) NOT NULL,
	CONTACT_NO BIGINT(10),
	CONTACT_ADD Varchar(20),
	DATE_OF_REGISTRATION Date NOT NULL,
	AGE Varchar(15)NOT NULL,
	Constraint MT_cts1 PRIMARY KEY(CUSTOMER_ID)
);

Creating LIBRARY_CARD_MASTER table

Create table LIBRARY_CARD_MASTER
(
	CARD_ID Varchar(10),
	DESCRIPTION Varchar(30) NOT NULL,
	AMOUNT	BIGINT(50),
	NUMBER_OF_YEARS bigint(10) NOT NULL,
	Constraint MT_cts2 PRIMARY KEY(CARD_ID)
);

Creating MOVIES_MASTER table

Create table MOVIES_MASTER
(
	MOVIE_ID Varchar(10),	
	MOVIE_NAME Varchar(50) NOT NULL,
	RELEASE_DATE Varchar(30) NOT NULL,
	LANGUAGE Varchar(30),
	RATING int(2),
	DURATION VARCHAR(10) NOT NULL,	
	MOVIE_TYPE Varchar(3),
	MOVIE_CATEGORY VARCHAR(20) NOT NULL,
	DIRECTOR VARCHAR(20) NOT NULL,		
	LEAD_ROLE_1 Varchar(3) NOT NULL,
	LEAD_ROLE_2 VARCHAR(4) NOT NULL,
	RENT_COST BIGINT(10),
	Constraint MT_cts4 PRIMARY KEY(MOVIE_ID)	
);

Creating CUSTOMER_CARD_DETAILS table

Create table CUSTOMER_CARD_DETAILS
(
	CUSTOMER_ID Varchar(10),
	CARD_ID VARCHAR(10),
	ISSUE_DATE DATE NOT NULL,
	Constraint MT_cts3 PRIMARY KEY(CUSTOMER_ID),
	Constraint MT_CTS41 FOREIGN KEY(CUSTOMER_ID) References CUSTOMER_MASTER(CUSTOMER_ID),
        Constraint MT_CTS42 FOREIGN KEY(CARD_ID) References LIBRARY_CARD_MASTER(CARD_ID)
);

Creating CUSTOMER_ISSUE_DETAILS table

Create table CUSTOMER_ISSUE_DETAILS
(
	ISSUE_ID Varchar(10) NOT NULL,
	CUSTOMER_ID Varchar(10) NOT NULL,
	MOVIE_ID VARCHAR(10),	
	ISSUE_DATE Date NOT NULL,
	RETURN_DATE Date NOT NULL,
        ACTUAL_DATE_RETURN Date NOT NULL,
	Constraint MT_cts5 PRIMARY KEY(ISSUE_ID),
        Constraint MT_Mem FOREIGN KEY(CUSTOMER_ID) References CUSTOMER_MASTER(CUSTOMER_ID),
        Constraint MT_Mem1 FOREIGN KEY(MOVIE_ID) References MOVIES_MASTER(MOVIE_ID)

);

Now we have insert value into the tables


Insert value into the CUSTOMER_MASTER

Insert into CUSTOMER_MASTER Values('CUS001', 'AMIT', 9876543210,'ADD1', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS002', 'ABDHUL', 8765432109,'ADD2', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS003', 'GAYAN', 7654321098,'ADD3', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS004', 'RADHA', 6543210987,'ADD4', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS005', 'GURU', NULL,'ADD5', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS006', 'MOHAN', 4321098765,'ADD6', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS007', 'NAME7', 3210987654,'ADD7', '2012-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS008', 'NAME8', 2109876543,'ADD8', '2013-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS009', 'NAME9', NULL,'ADD9', '2013-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS010', 'NAM10', 9934567890,'ADD10', '2013-02-12', '21');
Insert into CUSTOMER_MASTER Values('CUS011', 'NAM11', 9875678910,'ADD11', '2013-02-12', '21');

Insert value into the LIBRARY_CARD_MASTER

Insert into LIBRARY_CARD_MASTER Values('CR001', 'DES1', 200, 5);
Insert into LIBRARY_CARD_MASTER Values('CR002', 'DES2', 400, 9);
Insert into LIBRARY_CARD_MASTER Values('CR003', 'DES3', 600, 8);
Insert into LIBRARY_CARD_MASTER Values('CR004', 'DES4', 800, 7);
Insert into LIBRARY_CARD_MASTER Values('CR005', 'DES5', 1200, 6);

Insert value into the MOVIES_MASTER


Insert into MOVIES_MASTER Values('MV001', 'DIEHARD', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','ACTION','DIR1','L1','L2',100);
Insert into MOVIES_MASTER Values('MV002', 'THE MATRIX', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ACTION','DIR2','L1','L2',100);
Insert into MOVIES_MASTER Values('MV003', 'INCEPTION', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','ACTION','DIR3','L1','L2',100);
Insert into MOVIES_MASTER Values('MV004', 'DARK KNIGHT', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ACTION','DIR4','L1','L2',100);
Insert into MOVIES_MASTER Values('MV005', 'OFFICE S', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','COMEDY','DIR5','L1','L2',100);
Insert into MOVIES_MASTER Values('MV006', 'SHAWN OF DEAD', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','COMEDY','DIR6','L1','L2',100);
Insert into MOVIES_MASTER Values('MV007', 'YOUNG FRANKEN', '2012-05-13','ENGLISH', 4 , '2HRS', 'U/A','COMEDY','DIR7','L1','L2',100);
Insert into MOVIES_MASTER Values('MV008', 'CAS', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR8','L1','L2',100);
Insert into MOVIES_MASTER Values('MV009', 'GWW', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR9','L1','L2',100);
Insert into MOVIES_MASTER Values('MV010', 'TITANIC', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR10','L1','L2',100);
Insert into MOVIES_MASTER Values('MV011', 'THE NOTE BOOK', '2012-05-13','ENGLISH', 4 , '2HRS', 'A','ROMANCE','DIR11','L1','L2',100);

Insert value into the CUSTOMER_CARD_DETAILS

Insert into CUSTOMER_CARD_DETAILS Values('CUS001', 'CR001', '2012-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS002', 'CR002', '2012-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS003', 'CR002', '2013-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS004', 'CR003', '2013-05-13');
Insert into CUSTOMER_CARD_DETAILS Values('CUS005', 'CR003', '2012-05-13');

Insert value into the CUSTOMER_ISSUE_DETAILS

Insert into CUSTOMER_ISSUE_DETAILS Values ('IS001', 'CUS001', 'MV001', '2012-05-13', '2012-05-13','2012-05-13');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS002', 'CUS001', 'MV001', '2012-05-01', '2012-05-16','2012-05-16');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS003', 'CUS002', 'MV004', '2012-05-02', '2012-05-06','2012-05-16');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS004', 'CUS002', 'MV004', '2012-04-03', '2012-04-16','2012-04-20');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS005', 'CUS002', 'MV009', '2012-04-04', '2012-04-16','2012-04-20');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS006', 'CUS003', 'MV002', '2012-03-30', '2012-04-15','2012-04-20');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS007', 'CUS003', 'MV003', '2012-04-20', '2012-05-05','2012-05-05');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS008', 'CUS003', 'MV005', '2012-04-21', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS009', 'CUS003', 'MV001', '2012-04-22', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS010', 'CUS003', 'MV009', '2012-04-22', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS011', 'CUS003', 'MV010', '2012-04-23', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS012', 'CUS003', 'MV010', '2012-04-24', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS013', 'CUS003', 'MV008', '2012-04-25', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS014', 'CUS004', 'MV007', '2012-04-26', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS015', 'CUS004', 'MV006', '2012-04-27', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS016', 'CUS004', 'MV006', '2012-04-28', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS017', 'CUS004', 'MV001', '2012-04-29', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS018', 'CUS010', 'MV008', '2012-04-24', '2012-05-07','2012-05-25');
Insert into CUSTOMER_ISSUE_DETAILS Values ('IS019', 'CUS011', 'MV009', '2012-04-27', '2012-05-07','2012-05-25');

After this, we will be writing queries to perform the given task, here in the below section different types of queries, which is most important for any developer or professionals:


Query#1

Count the members who have gold cards


Solution

select count(customer_id) from customer_card_details where card_id in
(select card_id from library_card_master where description='gold card');

Query#2

Display the name of the member who issued movie and the count of the movies issued and display 0 for the member who has not issued any movie


Solution

select customer_name,count(movie_id) count from customer_issue_details a,customer_master b where a.customer_id=b.customer_id group by b.customer_id union
select customer_name,0 as count from customer_master where customer_id not in (select customer_id from customer_issue_details);

Query#3

Display the name of the person starting with letter 'r' and category is 'comedy'


Solution

select distinct a.customer_name from customer_master a,customer_issue_details b,movies_master c where a.customer_id=b.customer_id and b.movie_id=c.movie_id and
c.movie_category='comedy' and a.customer_name like 'r%';

Query#4

Display id, name & total rent of customers for movie issued


Solution

select a.customer_id,customer_name,count(a.movie_id)*c.rent_cost rent from customer_issue_details a,customer_master b ,(select movie_id,rent_cost from movies_master) c 
where 
a.customer_id=b.customer_id and a.movie_id=c.movie_id group by b.customer_id;

Query#5

Display id,name,card id,amount in $(amount/54.42) upto 0 decimals


Solution

select a.customer_id,customer_name,b.card_id,round(amount/54.42)
amount from customer_master a,customer_card_details b,library_card_master c
where 
a.customer_id=b.customer_id and b.card_id=c.card_id;

Query#6

Display id, name of customers who don't have a library card but still have issued the movie


Solution

select distinct b.customer_id,customer_name from customer_issue_details a,customer_master b 
where 
a.customer_id=b.customer_id and a.customer_id not in (select customer_id from customer_card_details);

Query#7

Display the no.of customers with first letter 'r' and have paid fine i.e actual return date is greater than the return date


Solution

select count(b.customer_id) count from (select customer_id from customer_issue_details
where return_date>actual_date_return group by customer_id) b,customer_master c
where
b.customer_id=c.customer_id and c.customer_name like 'r%';

Query#8

Display customer name, customer id who have issued max and min no.of movies issued

movies issued


Solution

select customer_name,a.customer_id from customer_issue_details a,customer_master b
where 
a.customer_id=b.customer_id group by a.customer_id having count(a.movie_id)=(select min(a.count) min from (select customer_id,count(movie_id) count from customer_issue_details group by customer_id) a) or count(a.movie_id)=(select max(b.count) min from (select customer_id,count(movie_id) count from customer_issue_details group by customer_id) b);

For more other queries which are want to ask related to movie Shema or other schemas, you can free to ask.

1935 Views

Members

See All Members (2)
bottom of page