Midterm_review_Solution
pdf
keyboard_arrow_up
School
İhsan Doğramacı Bilkent University *
*We aren’t endorsed by this school
Course
281
Subject
Information Systems
Date
Nov 24, 2024
Type
Pages
5
Uploaded by artifank
Consider a MOVIE database (like IMDB database) in which data is recorded about the movie industry. The data requirements are summarized as follows: •
Each movie has a unique id, title, year produced, length in minutes and genre. Movies may have more than one genre (action, comedy, ..). •
Actors are uniquely identified by SSN and have name, gender, address. •
Each actor plays in movies and movies have actors. •
Each studio has a ID, name and address. •
Directors have a SSN , gender and address. Directors direct one or more movies. Each movie has exactly one director. •
For each movie, there is exactly one studio that owns it. Each studio owns a set of movies (zero or more). •
For each movie, movie-goers can enter comments. Each comment has an id, comment text and rating. When a movie is deleted, all comments about the movie are also deleted. 1) Draw the ER diagram according to given set of data requirements
2) Translate the E/R diagram into the relational model. Genre(Gid, gname) Actor ( ASSN, aname, aaddress,agender) Studio ( SID, sname, saddress) Director (DSSN, dname, daddress,dgender) Movie( MID, title, year, length, SID, DSSN) FK(SID) references Studio FK(DSSN) references Director hasGenre(MID, Gid) FK(MID) references Movie FK(GID) references Genre Play(ASSN, MID) FK(ASSN) references Actor FK(MID) references Movie Comment(MID, CID, rating,Ctext) FK(MID) references Movie ON DELETE CASCADE 3) Answer the following queries using Relational Algebra i.
Find title of movies in which Jennifer Lawrence has acted. π
title
(
σ
a
name=’J.Lawrence’
Actor ⋈
Play
⋈
Movie
) ii.
Find title of movies in which are directed by a female director in 2016 π
title
(
σ
dgender=’F’
Director ⋈
σ
year=2016
Movie
) iii.
Find name of actors who acted on an action film produced by Warner Bros studios ρ(
AWB, π
MID
(
σ
sname=’W.Bros’
Studio ⋈
Movie ⋈
hasGenre
⋈
σ
gname=’Action’
Genre
)) π
aname
(AWB ⋈
Play
⋈
Actor )
iv.
Find name of actors who have played both in action and romantic comedy film(s) ρ(
Action, π
ASSN
(
σ
gname=’Action’
Genre
⋈
hasGenre ⋈
Play
)) ρ(
RomCom, π
ASSN
(
σ
gname=’RomCom’
Genre
⋈
hasGenre ⋈
Play
)) π
aname
((Action
∩
RomCom) ⋈
Actor)
v.
Find name of studios which did not produce any comedy movie in 2014 ρ(
ComedyIn2014, π
SID
(
σ
year=2014
Movie ⋈
hasGenre ⋈
σ
gname=’Comedy’
Genre
)) π
sname
((
π
SID
Studio - ComedyIn2014) ⋈
Studio) vi.
Find name of actors who played in all genre types π
aname
((
π
ASSN,GID
(Play
⋈
hasGenre) /
π
GID
Genre) ⋈
Actor) vii.
Find title of drama movies directed by Steven Spielberg in years 2000-2020 π
title
(
σ
dname=’S.Spielberg’
Director ⋈
σ
year>=2000
Movie
⋈
hasGenre ⋈
σ
gname=’Drama’
Genre
) and year<=2020
viii.
Find title of movies which are produced by a studio located in New York π
title
(
σ
saddress=’NY’
Studio ⋈
Movie
)
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
4) Answer the following queries using SQL 1.
Find number of movies acted for each actor Select assn,aname, count(*) From play p , actor a Where p.assn=a.assn Group by assn, aname 2.
Find title of movies only male actors acted. Select title From movie where mid not in (Select p.mid From actor a, play p Where a.agender=’F’
And a.assn=p.assn) 3.
Find name of actors who played in some movie with Tom Hanks Select aname From play p, actor a Where p.assn=a.assn And p.mid in (Select mid From actor a, play p Where a.aname=’ Tom Hanks’ a.assn=p.assn ) and a.aname <> ’ Tom Hanks’
4.
Find title of movies in which Jennifer Lawrence and Bradley Cooper acted together Select m.mid, m.title From actor a, play p, movie m Where a.aname=’ Jennifer Lawrence’ And a.assn=p.assn and p.mid =m.mid INTERSECT Select m.mid, m.title From actor a, play p, movie m Where a.aname=’ Bradley Cooper ‘ And a.assn=p.assn and p.mid =m.mid
5.
Find title and average rating for all movies, order the list in descending average rating Select c.mid, title, avg(rating) From comment c , movie m Where c.mid=m.mid Group by c.mid , title Order by avg(rating) desc 6.
Find name of actors who did not play in any action movie Select aname from actor where assn NOT IN ( Select assn From play p , hasGenre h, Genre g Where p.mid=h.mid and h.gid=g.gid and gname=’Action’ )