Tuesday 29 July 2014
10:42
Unknown
DBMS Lab5 [MySQL]
Students are advised to learn to create their own tables
Exercise: 5
Data
requirements of movie industry are captured. Each movie is identified by title
and
year
of release. Each movie has length in minutes and classified under one genres
(like action, horror etc.). Each movie has a plot outline. Production companies
are identified by name and each has an address. A production company produces
one or more movies. Actors are identified by id. Other details like name and
date of birth ofactors are also stored. Each actor acts in one or more movies.
Each actor has a role in movie. Directors are identified by id. Other details
like name and date of birth of directors are also stored. Each director directs
one or more movies. Each movie has one or more actors and one or more directors
and is produced by a production company.
ER-Diagram:
Relational
schema:
Tables
mysql> create table
movie(m_no int primary key,title varchar(20),yor int,lenght int,genres
varchar(10));
Query OK, 0 rows
affected (0.09 sec)
mysql> insert into
movie values(
001,"akasmika",1992,180,"action");
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
movie values( 002,"six-fivetwo",2013,150,"horror");
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
movie values( 003,"kalla-malla-sulla",2012,150,"comedy")
Query OK, 1 row
affected (0.04 sec)
mysql> insert into
movie values( 004,"super",2010,150,"action");
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
movie values( 005,"nagarahavu",1991,150,"mystery");
Query OK, 1 row
affected (0.03 sec)
mysql> select * from
movie;
mysql> create table
production(p_no int primary key,name varchar(20),address varchar(20));
Query OK, 0 rows
affected (0.10 sec)
mysql> insert into
production values(121,"vajreshwari","bangalore");
Query OK, 1 row
affected (0.02 sec)
mysql> insert into
production values( 122,"old_films","madras");
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
production values( 123,"raj_movies","madras");
Query OK, 1 row
affected (0.02 sec)
mysql> insert into
production values( 124,"sri_movies","bangalore");
Query OK, 1 row affected
(0.03 sec)
mysql> insert into
production values( 125,"golden_movies","bangalore");
Query OK, 1 row
affected (0.04 sec)
mysql> select * from
production;
mysql> create table
produced(p_no int,m_no int,foreign key(p_no) references production(p_no),foreign
key(m_no) references movie(m_no));
Query OK, 0 rows
affected (0.12 sec)
mysql> insert into
produced values(121,001);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
produced values(122,002);
Query OK, 1 row
affected (0.02 sec)
mysql> insert into produced
values(123,003);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
produced values(124,004);
Query OK, 1 row
affected (0.02 sec)
mysql> insert into
produced values(125,005);
Query OK, 1 row
affected (0.03 sec)
mysql> select * from
produced;
mysql> create table
actor(a_id int primary key,name varchar(10),dob date);
Query OK, 0 rows
affected (0.09 sec)
mysql> insert into
actor values(101,"raj_kumar",19470424);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
actor values(102,"shankar_nag",19470602);
Query OK, 1 row
affected, 1 warning (0.02 sec)
mysql> insert into
actor values(103,"vishnuvardhan",19620505);
Query OK, 1 row
affected, 1 warning (0.02 sec)
mysql> insert into
actor values(104,"ambareesh",19600525);
Query OK, 1 row
affected (0.04 sec)
mysql> insert into
actor values(105,"upendra",19700515);
Query OK, 1 row
affected (0.04 sec)
mysql> insert into
actor values(106,"srk",19800516);
Query OK, 1 row
affected (0.04 sec)
mysql> select * from
actor;
mysql> create table
acts(a_id int,m_no int,foreign key(a_id) references actor(a_id),foreign
key(m_no) references movie(m_no));
Query OK, 0 rows
affected (0.11 sec)
mysql> insert into
acts values(101,1);
Query OK, 1 row
affected (0.02 sec)
mysql> insert into
acts values(102,2);
Query OK, 1 row
affected (0.02 sec)
mysql> insert into
acts values(103,3);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
acts values(104,4);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
acts values(105,5);
Query OK, 1 row affected
(0.03 sec)
mysql>insert into
acts values(106,5);
Query OK, 1 row
affected (0.03 sec)
mysql> select * from
acts;
mysql> create table
director(d_id int primary key,name varchar(10),dob date);
Query OK, 0 rows
affected (0.08 sec)
mysql> insert into director
values(333,"puttanna",19700424);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
director values(334,"siddayya",19700424);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
director values(335,"bhargava",19700425);
Query OK, 1 row affected
(0.03 sec)
mysql> insert into
director values(336,"kumar",19700525);
Query OK, 1 row
affected (0.02 sec)
mysql> insert into
director values(337,"bangaru",19700516);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
director values(338,"upendra",19890516);
Query OK, 1 row
affected (0.02 sec)
mysql> select * from
director;
mysql> create table
directs(d_id int,m_no int,foreign key(d_id) references director(d_id),foreign
key(m_no) references movie(m_no));
Query OK, 0 rows
affected (0.12 sec)
mysql> insert into
directs values(333,1);
Query OK, 1 row
affected (0.04 sec)
mysql> insert into
directs values(334,2);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
directs values(335,3);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
directs values(336,4);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
directs values(337,5);
Query OK, 1 row
affected (0.03 sec)
mysql> insert into
directs values(338,4);
Query OK, 1 row
affected (0.02 sec)
mysql> select * from
directs;
Queries:
a)List
the details of horror movies released in 2012 and directed by more than 2
directors.
mysql> select distinct m.* from
movie m, directs d where m.genres="horror" and yor=2012 and
m.m_no=d.m_no having
count(d.m_no)>=2;
b)
List the details of actors who acted in movies having same titles but released
before 2000 and after 2010.
mysql> select a.* from actor
a,acts ac1,acts ac2,movie m1,movie m2
where m1.m_no=m2.m_no and
m1.yor<=2000 and m2.yor>=2010 and
m1.m_no=ac1.m_no and m2.m_no=ac2.m_no;
c)
List the details of production companies producing maximum movies.
mysql> select * from production
where p_no=(select p_no from produced where m_no=(select max(m_no) from
produced));
d)
List the details of movies where director and actor have same date of birth.
mysql>
select m.* from movie m,actor a,acts ac,director d where a.a_id=ac.a_id and
m.m_no=ac.m_no and a.dob=d.dob;
e) Retrieve
the names of
directors directed all the
movies produced by
any one production company.
mysql>
select name from director where d_id=(select d.d_id from production p,produced
p1,directs d where p.name="vajreshwari" and p.p_no=p1.p_no and
p1.m_no=d.m_no);
